COUNT OVER PARTITION BY: Uma explicação com 3 exemplos práticos

No SQL, a combinação da função COUNT() com a cláusula OVER() e PARTITION BY abre a porta para uma maneira totalmente diferente de contar linhas. Neste artigo, você aprenderá a fazer vários COUNTs em uma única consulta e a calcular expressões com base no resultado da função COUNT.

Neste artigo, abordaremos como você pode usar a função COUNT() combinada com as cláusulas OVER() e PARTITION BY. Para saber mais sobre esse tópico, recomendo nosso curso interativo Funções de Janela (Window Functions) em SQL. Ele contém mais de 200 exercícios interativos sobre o uso da cláusula OVER() com funções de janela. Depois de concluir esse curso, você terá facilidade para abordar esse tópico e se sentirá confortável com o uso de funções de janela em bancos de dados SQL.

Exemplo nº 1: Introdução ao uso de COUNT OVER PARTITION BY

Vamos supor que temos uma tabela chamada order com um registro para cada pedido de venda recebido em um pet shop. A tabela tem colunas como order_id, order_date, customer_id, salesperson_id, ship_address, ship_state e amount_paid.

A consulta a seguir mostra os pedidos recebidos pela empresa durante o primeiro semestre de 2023. Observe que adicionamos uma coluna extra chamada orders_this_customer que mostra o número total de pedidos enviados por cada cliente durante esse período.

SELECT 
  order_id,
  order_date,
  customer_id,
  amount_paid,
 COUNT(*) OVER (PARTITION BY customer_id) AS orders_this_customer
FROM order
WHERE order_date >= '2023-01-01' AND order_date <= '2023-06-30';

No SQL, usamos a função COUNT() sozinha ou combinada com a cláusula GROUP BY para contar as linhas em um conjunto de resultados ou em um grupo de linhas. OVER() e PARTITION BY aplicam a função COUNT() a um grupo ou linhas definidas por PARTITION BY. Em nosso exemplo, o grupo é definido pelos valores da coluna customer_id. A função COUNT() conta o número de pedidos com o mesmo customer_id.

A combinação de COUNT() e OVER(PARTITION BY) é mais eficiente do que usar somente a função COUNT() porque nos permite obter o número de linhas para cada valor específico de uma coluna.

Ao usar OVER() e PARTITION BY, não é necessário usar a cláusula GROUP BY para agrupar registros; isso nos permite ter conjuntos de resultados no nível da linha. Cada linha do conjunto de resultados terá informações em nível de pedido, mas o valor da coluna orders_this_customer terá o número total de pedidos feitos por cada cliente.

Em outras palavras, estamos combinando dados no nível do relatório com dados no nível do cliente na mesma linha. E podemos adicionar outros dados de nível usando colunas diferentes na cláusula PARTITION BY. Abaixo está um resultado parcial dessa consulta:

Antes de terminar esta seção, gostaria de sugerir o artigo A função SQL Count Explicada Com 7 Exemplos, no qual é possível encontrar muitos exemplos de consultas usando a função COUNT(). Para os leitores que quiserem se aprofundar mais no assunto, recomendo o artigo Como usar o SQL PARTITION BY com OVER, no qual você encontrará uma explicação clara com exemplos das cláusulas OVER() e PARTITION BY.

Exemplo nº 2: Calcular totais com base em diferentes critérios

Antes de continuar, vamos mostrar a ordem completa da tabela. Cada linha da tabela representa um pedido recebido pelo pet shop. Uma linha de pedido tem um customer_id, um salesperson_id, um order_date, um ship_state, e um ship_city, entre outras colunas; as outras colunas são autoexplicativas. Aqui está uma visão parcial da tabela:

Vamos supor que o pet shop, no final do mês, queira ter um relatório com todos os pedidos. Para cada linha, eles também querem mostrar dois campos calculados: o número total de pedidos vendidos nesse dia e o número total de pedidos vendidos por esse vendedor. A consulta para obter esse relatório é:

SELECT 
  order_id,
  order_date,
  customer_id,
  salesperson_id,
  COUNT(1) OVER (PARTITION BY order_date) as orders_per_day,
  COUNT(1) OVER (PARTITION BY salesperson_id) as orders_per_salesperson
FROM order
WHERE order_date between '2023-06-01' AND '2023-06-30';

O resultado da consulta é mostrado abaixo:

Na consulta acima, usamos a expressão COUNT(1), que funciona da mesma forma que COUNT(*). A cláusula PARTITION BY indica os critérios para agrupar as linhas a serem contadas.

Para obter o campo orders_per_day, usamos a cláusula OVER (PARTITION BY order_date); para o campo orders_per_salesperson, usamos a cláusula OVER (PARTITION BY salesperson_id).

Aqui, observe que a única diferença é o campo que colocamos após a cláusula PARTITION BY. Esse campo define os critérios para a contagem de linhas, ou seja, todas as linhas com o mesmo valor nesse campo serão contadas juntas.

O efeito desses dois campos no resultado da consulta é que adicionamos dois campos de diferentes níveis de granularidade à linha; o campo orders_per_salesperson é um total agrupado por vendedor, enquanto o campo orders_per_day é um total agrupado por data do pedido.

Observação: COUNT(DISTINCT) não funciona com OVER(PARTITION BY)

Existem algumas variações da função COUNT(), como COUNT(*), COUNT(1), ou COUNT(DISTINCT). Se quiser saber mais sobre elas, consulte nosso artigo Qual é a diferença entre COUNT(*), COUNT(1), COUNT(nome da coluna) e COUNT(nome da coluna DISTINCT)? Você encontrará uma explicação clara sobre essas diferentes maneiras de usar a função COUNT().

Em alguns casos - por exemplo, se precisarmos contar quantas pessoas diferentes fizeram pedidos em um determinado dia -, podemos pensar em usar a expressão COUNT(DISTINCT customer_id) OVER (PARTITION BY order_date). No entanto, é importante esclarecer que o COUNT(DISTINCT) OVER(PARTITION BY) não é compatível com a maioria dos bancos de dados populares (como PostgreSQL, SQL Server e Snowflake, entre outros).

Exemplo nº 3: Uso de COUNT() com OVER em expressões

No próximo exemplo de consulta, calcularemos algumas porcentagens usando expressões aritméticas com dois COUNTs diferentes.

Vamos supor que o departamento de marketing queira direcionar especificamente a próxima campanha de marketing para um grupo preciso de clientes. Para isso, ele precisa de algumas métricas sobre os resultados comerciais: a porcentagem de pedidos recebidos de cada estado, a porcentagem de pedidos recebidos para cada família de produtos e a combinação de ambas as métricas (por exemplo, a porcentagem de pedidos recebidos do Texas para a família de produtos DOG). A consulta para obter esse relatório é a seguinte:

SELECT DISTINCT
  ship_state,
  product_family,
  COUNT(1) OVER () AS total_orders,
  COUNT(1) OVER (PARTITION BY ship_state) state_orders,
  COUNT(1) OVER (PARTITION BY ship_state) / COUNT(1) OVER () AS state_percentage,
  COUNT(1) OVER (PARTITION BY product_family) AS family_orders,
  COUNT(1) OVER (PARTITION BY product_family) / COUNT(1) OVER () AS family_percentage
FROM   order
ORDER BY ship_state, product_family

Na consulta acima, o campo total_orders usa a expressão COUNT(1) OVER () para calcular a quantidade total de pedidos recebidos pela empresa. Em seguida, no campo state_orders, a expressão COUNT() é usada para obter a quantidade de pedidos recebidos do estado da linha atual (por exemplo, se o valor ship_state da linha atual for TX (Texas), isso mostrará todos os pedidos do Texas); esse campo é muito semelhante ao que calculamos nos exemplos anteriores.

A parte interessante desse exemplo de consulta está na expressão do campo state_percentage, que usa dois COUNTs para calcular uma porcentagem. Em outras palavras, estamos dividindo o número de pedidos do estado atual (a expressão vermelha) pelo número total de pedidos (a expressão azul). Nos dois últimos campos, repetimos a mesma abordagem para o campo product_family.

O resultado da consulta está abaixo:

Uso de OVER PARTITION BY com COUNT() e outras funções

Neste artigo, abordamos a função COUNT() combinada com as cláusulas OVER e PARTITION BY. Entretanto, no SQL, há muitas outras funções que podem ser combinadas com PARTITION BY. Essas funções são chamadas de funções de janela e você pode encontrar um excelente material didático sobre elas em nosso curso Funções de Janela (Window Functions) em SQL . Ele tem mais de 200 exercícios interativos e oferece um tutorial completo sobre funções de janela.

Se você já conhece as funções de janela e está procurando mais prática, recomendo nosso Funções de Janela (Window Functions) em SQL Practice Set. Esse curso foi desenvolvido para oferecer prática abrangente de funções de janela em exemplos reais. Seus 100 exercícios em três conjuntos de dados diferentes se assemelham a problemas que você verá no mundo real.

Se estiver procurando mais recursos sobre funções de janela, confira nossa Folha de dicas sobre SQL Funções de Janela (Window Functions) em SQL e as 10 principais perguntas da entrevista sobre SQL Funções de Janela (Window Functions) em SQL , onde você pode encontrar algum material para se preparar para uma entrevista de emprego sobre SQL. Vá em frente, aprenda SQL e invista em você!