Métodos de agregação de dados mensais e anuais usando a função COUNT no SQL

A função COUNT no SQL é uma ferramenta básica para agregar o número de registros em um banco de dados de forma eficiente. Neste artigo, vamos explicar como agregar dados mensalmente e anualmente com exemplos práticos. A agregação de dados é uma habilidade importante que serve como base para inteligência de negócios e criação de relatórios. Vamos explicar de forma clara, desde o básico até o avançado, utilizando exemplos com dados reais.

Uso básico da função COUNT

A função COUNT é uma função agregada do SQL que retorna o número de valores não nulos em uma coluna especificada. O uso básico permite obter o número total de registros em uma tabela ou o número de registros que atendem a uma condição específica. A seguir, mostramos a sintaxe básica e um exemplo simples.

Sintaxe básica

SELECT COUNT(column_name)  
FROM table_name  
WHERE condition;

Exemplo de uso

Por exemplo, para obter o número total de clientes na tabela de clientes (customers), você escreveria a consulta da seguinte forma:

SELECT COUNT(*)  
FROM customers;

Essa consulta retorna o número total de registros na tabela customers. Também é possível obter o número de registros com base em uma condição específica.

Exemplo com condição específica

Por exemplo, se você deseja obter o número de clientes ativos, faça da seguinte maneira:

SELECT COUNT(*)  
FROM customers  
WHERE status = 'active';

Essa consulta retorna o número de clientes cujo status é ‘active’. Isso facilita a agregação de dados com base em condições específicas.

Método de agregação de dados mensais

A agregação de dados mensais é essencial para entender as tendências de dados em um período específico. Aqui, explicaremos como agregar dados mensalmente usando a função COUNT e a cláusula GROUP BY no SQL.

Sintaxe básica

SELECT EXTRACT(YEAR FROM date_column) AS year, EXTRACT(MONTH FROM date_column) AS month, COUNT(*)  
FROM table_name  
GROUP BY EXTRACT(YEAR FROM date_column), EXTRACT(MONTH FROM date_column)  
ORDER BY year, month;

Exemplo de uso

Por exemplo, para obter o número de pedidos mensais a partir da tabela de pedidos (orders), a consulta seria escrita da seguinte forma:

SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)  
FROM orders  
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)  
ORDER BY year, month;

Essa consulta agrega o número de pedidos para cada ano e mês com base na coluna order_date da tabela orders, exibindo os resultados ordenados por ano e mês.

Exemplo avançado

Além disso, é possível agregar os pedidos mensais com uma condição específica, como um cliente específico.

SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)  
FROM orders  
WHERE customer_id = 123  
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)  
ORDER BY year, month;

Essa consulta agrega o número de pedidos mensais de um cliente específico (customer_id = 123), permitindo uma análise mais detalhada com base em condições específicas.

Método de agregação de dados anuais

A agregação de dados anuais é útil para entender tendências de dados de longo prazo. Explicaremos como agregar dados anualmente usando a função COUNT e a cláusula GROUP BY no SQL.

Sintaxe básica

SELECT EXTRACT(YEAR FROM date_column) AS year, COUNT(*)  
FROM table_name  
GROUP BY EXTRACT(YEAR FROM date_column)  
ORDER BY year;

Exemplo de uso

Por exemplo, para obter o número de pedidos anuais a partir da tabela de pedidos (orders), a consulta seria escrita da seguinte forma:

SELECT EXTRACT(YEAR FROM order_date) AS year, COUNT(*)  
FROM orders  
GROUP BY EXTRACT(YEAR FROM order_date)  
ORDER BY year;

Essa consulta agrega o número de pedidos anuais com base na coluna order_date da tabela orders, exibindo os resultados ordenados por ano.

Exemplo avançado

Com condições adicionais, é possível realizar uma agregação de dados anual mais detalhada. Por exemplo, para agregar o número de pedidos anuais de uma categoria de produto específica, faça o seguinte:

SELECT EXTRACT(YEAR FROM order_date) AS year, COUNT(*)  
FROM orders  
WHERE product_category = 'Electronics'  
GROUP BY EXTRACT(YEAR FROM order_date)  
ORDER BY year;

Essa consulta agrega o número de pedidos anuais para a categoria de produtos ‘Electronics’, permitindo obter insights mais específicos ao aplicar condições.

Agregação mais detalhada

Em alguns casos, é útil combinar a agregação anual e mensal. A consulta seria assim:

SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)  
FROM orders  
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)  
ORDER BY year, month;

Essa consulta agrega o número de pedidos para cada ano e mês, permitindo uma análise de dados mais detalhada.

Combinação de COUNT e GROUP BY

A combinação da função COUNT com a cláusula GROUP BY é uma ferramenta poderosa para agregar dados no SQL. Vamos explicar como usar a cláusula GROUP BY para agregar dados por grupos específicos.

Sintaxe básica

SELECT column_name, COUNT(*)  
FROM table_name  
GROUP BY column_name  
ORDER BY COUNT(*) DESC;

Exemplo de uso

Por exemplo, para agregar o número de clientes por cidade a partir da tabela de clientes (customers), a consulta seria escrita da seguinte forma:

SELECT city, COUNT(*)  
FROM customers  
GROUP BY city  
ORDER BY COUNT(*) DESC;

Essa consulta agrega o número de clientes em cada cidade, exibindo os resultados ordenados pelo número de clientes em ordem decrescente.

Quando usar múltiplas colunas

Também é possível agregar dados com múltiplas colunas. Por exemplo, para agregar o número de clientes por cidade e ano, a consulta seria assim:

SELECT city, EXTRACT(YEAR FROM signup_date) AS year, COUNT(*)  
FROM customers  
GROUP BY city, EXTRACT(YEAR FROM signup_date)  
ORDER BY city, year;

Essa consulta agrega o número de clientes para cada cidade e ano, exibindo os resultados ordenados por cidade e ano.

Uso do HAVING

Com a cláusula HAVING, é possível filtrar grupos que atendem a uma condição específica. Por exemplo, para exibir apenas cidades com 50 ou mais clientes:

SELECT city, COUNT(*)  
FROM customers  
GROUP BY city  
HAVING COUNT(*) >= 50  
ORDER BY COUNT(*) DESC;

Essa consulta agrega o número de clientes por cidade, exibindo apenas aquelas com 50 ou mais clientes. O uso do HAVING permite aplicar condições nos resultados agrupados.

A combinação de COUNT e GROUP BY permite agregar e analisar informações em um banco de dados de forma eficaz.

Consultas de exemplo com dados reais

Para facilitar o entendimento, vamos apresentar consultas de exemplo usando dados reais, demonstrando o uso prático da função COUNT e da cláusula GROUP BY. Utilizaremos dados fictícios de pedidos para fornecer exemplos concretos de consultas.

Estrutura da tabela de pedidos

Primeiro, vamos verificar a estrutura da tabela de pedidos (orders) no banco de dados de exemplo. Esta tabela possui as seguintes colunas:

• order_id (ID do pedido)
• customer_id (ID do cliente)
• order_date (Data do pedido)
• product_category (Categoria do produto)
• amount (Valor do pedido)

Dados de exemplo

CREATE TABLE orders (  
    order_id INT,  
    customer_id INT,  
    order_date DATE,  
    product_category VARCHAR(50),  
    amount DECIMAL(10, 2)  
);  

INSERT INTO orders (order_id, customer_id, order_date, product_category, amount) VALUES  
(1, 101, '2023-01-15', 'Electronics', 299.99),  
(2, 102, '2023-02-20', 'Books', 19.99),  
(3, 103, '2023-01-22', 'Electronics', 149.99),  
(4, 104, '2023-03-15', 'Clothing', 79.99),  
(5, 105, '2023-03-30', 'Books', 9.99);

Consulta para agregar o número de pedidos mensais

A consulta a seguir agrega o número de pedidos mensais a partir da tabela de pedidos.

SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)  


FROM orders  
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)  
ORDER BY year, month;

Resultado da consulta

Consulta para agregar o valor total anual dos pedidos

Em seguida, mostramos a consulta para agregar o valor total dos pedidos anuais.

SELECT EXTRACT(YEAR FROM order_date) AS year, SUM(amount) AS total_amount  
FROM orders  
GROUP BY EXTRACT(YEAR FROM order_date)  
ORDER BY year;

Resultado da consulta

Consulta para agregar o número de pedidos por categoria de produto

Para agregar o número de pedidos por categoria de produto, a consulta é a seguinte:

SELECT product_category, COUNT(*)  
FROM orders  
GROUP BY product_category  
ORDER BY COUNT(*) DESC;

Resultado da consulta

Esses exemplos de consultas mostram como realizar agregações eficazes em um banco de dados real.

Exemplos avançados: agregação com múltiplas condições

Combinando múltiplas condições, é possível realizar análises de dados mais detalhadas. A seguir, mostramos exemplos avançados de agregação com condições múltiplas.

Consulta para agregar o número de pedidos por categoria de produto e mês

A consulta a seguir agrega o número de pedidos mensais por categoria de produto.

SELECT product_category, EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)  
FROM orders  
GROUP BY product_category, EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)  
ORDER BY product_category, year, month;

Resultado da consulta

Consulta para agregar o valor total anual dos pedidos por cliente

Para agregar o valor total anual dos pedidos por cliente, a consulta seria assim:

SELECT customer_id, EXTRACT(YEAR FROM order_date) AS year, SUM(amount) AS total_amount  
FROM orders  
GROUP BY customer_id, EXTRACT(YEAR FROM order_date)  
ORDER BY customer_id, year;

Resultado da consulta

Agregação filtrada por condições específicas

Para realizar uma agregação mensal apenas para pedidos com valor superior a 100 dólares, a consulta seria a seguinte:

SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)  
FROM orders  
WHERE amount >= 100  
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)  
ORDER BY year, month;

Resultado da consulta

Combinação de diferentes métodos de agregação

Por fim, apresentamos um exemplo de combinação de diferentes métodos de agregação. A consulta a seguir agrega o número de clientes por cidade e a média de valor dos pedidos em cada cidade.

SELECT city, COUNT(customer_id) AS customer_count, AVG(amount) AS average_order_amount  
FROM customers  
JOIN orders ON customers.customer_id = orders.customer_id  
GROUP BY city  
ORDER BY customer_count DESC;

Resultado da consulta

Esses exemplos avançados mostram como realizar agregações detalhadas combinando várias condições.

Exercícios

Para aprofundar o entendimento, resolva os exercícios a seguir. Estes exercícios ajudarão a melhorar suas habilidades práticas em agregar dados com a função COUNT e a cláusula GROUP BY no SQL.

Exercício 1: Agregar o número de clientes anuais

A tabela de clientes (customers) contém o ID do cliente (customer_id) e a data de cadastro (signup_date). Crie uma consulta para agregar o número de novos clientes por ano.

-- Exemplo de resposta  
SELECT EXTRACT(YEAR FROM signup_date) AS year, COUNT(*)  
FROM customers  
GROUP BY EXTRACT(YEAR FROM signup_date)  
ORDER BY year;

Exercício 2: Agregar o valor total de pedidos por categoria de produto

A tabela de pedidos (orders) contém a categoria do produto (product_category) e o valor do pedido (amount). Crie uma consulta para agregar o valor total de pedidos por categoria de produto.

-- Exemplo de resposta  
SELECT product_category, SUM(amount) AS total_amount  
FROM orders  
GROUP BY product_category  
ORDER BY total_amount DESC;

Exercício 3: Agregar o número de pedidos mensais em um ano específico

Crie uma consulta para agregar o número de pedidos mensais no ano de 2023 a partir da tabela de pedidos (orders).

-- Exemplo de resposta  
SELECT EXTRACT(MONTH FROM order_date) AS month, COUNT(*)  
FROM orders  
WHERE EXTRACT(YEAR FROM order_date) = 2023  
GROUP BY EXTRACT(MONTH FROM order_date)  
ORDER BY month;

Exercício 4: Agregar o valor total anual de pedidos por cliente

Faça uma junção entre a tabela de clientes (customers) e a tabela de pedidos (orders) para agregar o valor total anual de pedidos por cliente.

-- Exemplo de resposta  
SELECT customers.customer_id, EXTRACT(YEAR FROM orders.order_date) AS year, SUM(orders.amount) AS total_amount  
FROM customers  
JOIN orders ON customers.customer_id = orders.customer_id  
GROUP BY customers.customer_id, EXTRACT(YEAR FROM orders.order_date)  
ORDER BY customers.customer_id, year;

Exercício 5: Exibir apenas categorias de produtos com 50 ou mais pedidos

Crie uma consulta para exibir apenas as categorias de produtos com 50 ou mais pedidos a partir da tabela de pedidos (orders).

-- Exemplo de resposta  
SELECT product_category, COUNT(*) AS order_count  
FROM orders  
GROUP BY product_category  
HAVING COUNT(*) >= 50  
ORDER BY order_count DESC;

Resolva esses exercícios para aprimorar suas habilidades em agregar dados no SQL utilizando COUNT e GROUP BY. Verifique se suas consultas estão corretas.

Conclusão

Neste artigo, detalhamos os métodos para agregar dados mensais e anuais usando a função COUNT no SQL. A combinação de COUNT e GROUP BY permite agregar e analisar dados de forma eficiente em um banco de dados. Através de exemplos básicos e avançados, além de exercícios práticos, você pôde desenvolver habilidades essenciais para inteligência de negócios e criação de relatórios. Utilize esses conhecimentos para obter insights valiosos em seus projetos.