Como usar SUM() com OVER(PARTITION BY) no SQL

Descubra casos de uso reais da função SUM() com a cláusula OVER(PARTITION BY). Aprenda a sintaxe e confira 5 exemplos diferentes.

Usamos as funções de janela do SQL para realizar operações em grupos de dados. Essas operações incluem as funções matemáticas SUM(), COUNT(), AVG(), entre outras. Neste artigo, explicaremos o que SUM() com OVER(PARTITION BY) faz no SQL. Mostraremos os casos de uso mais comuns em aplicativos do mundo real para determinar a proporção entre o valor da linha individual e o valor total, calcular totais em execução e encontrar um total de pedido personalizado que inclua descontos para determinados produtos.

Para obter um guia detalhado sobre o uso do site SUM() com OVER() e outras funções de janela, assista ao nosso Funções de Janela (Window Functions) em SQL curso interativo. Ele explica detalhadamente todos os conceitos de função de janela SQL e tem mais de 200 exercícios.

Vamos começar.

A cláusula OVER() no SQL

No SQL, a cláusula OVER() é usada para introduzir funções de janela. A sintaxe geral é:

SELECT …
  <window function> OVER(...)
…

OVER() informa ao banco de dados que queremos usar funções de janela. A função de janela pode ser uma função agregada, como SUM(), ou outra função de janela.

Uma função de janela funciona em um "quadro de janela" ou em um conjunto de linhas relacionadas à linha atual. OVER() define o quadro de janela para cada linha. Uma cláusula OVER() vazia informa ao banco de dados que todo o conjunto de resultados é a moldura da janela.

Vamos dar uma olhada em um exemplo da sintaxe SUM() OVER(). Usaremos a tabela employees da tabela de dados de amostra fornecida pelo Oracle Live SQL. Essa tabela consiste nas seguintes colunas:

• emp_id é o identificador do funcionário.
• name é o nome do funcionário.
• job é o título do cargo.
• dept_id é o identificador do departamento.
• salary é o salário do funcionário.

Podemos usar a função SUM() com a cláusula OVER() para obter o salário total de todos os funcionários e exibir o salário total ao lado do salário de cada funcionário.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary, 
  SUM(salary) OVER() AS total_salary
FROM employees;

A função SUM() é executada para cada linha da tabela. Toda vez que é chamada, ela obtém as informações de salário de todas as linhas da tabela para calcular o valor total do salário. O salário total é exibido ao lado dos detalhes de cada linha individual. Os detalhes das linhas individuais são preservados e exibidos ao lado do salário total.

Neste exemplo, a moldura da janela (o conjunto de linhas em que o SUM() está operando) é o conjunto de dados inteiro. Você pode adicionar cláusulas adicionais em OVER() para alterar o quadro da janela.

Exemplo 1A: Cálculo da soma de cada grupo - SUM() com OVER(PARTITION BY ...)

A cláusula OVER() pode conter detalhes sobre a forma como queremos particionar os dados. Usamos a cláusula PARTITION BY em OVER() para dividir os dados em partições ou grupos. O uso de PARTITION BY é semelhante ao uso de GROUP BY, pois as linhas são particionadas em grupos com base no valor de algumas colunas. Quando usamos o SUM() OVER(PARTITION BY …), podemos calcular a soma dos valores de cada grupo ou partição de dados. Por exemplo, podemos calcular o salário total de cada departamento:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  SUM(salary) OVER(PARTITION BY dept_id) AS dept_total_salary
FROM employees;

Aqui está o resultado:

Usamos SUM(salary) OVER(PARTITION BY dept_id) para obter o salário total por departamento. A cláusula PARTITION BY divide as linhas em grupos com base na coluna dept_id. As linhas com dept_id igual a 10 são colocadas em um grupo (marcado em amarelo na tabela acima), as linhas com dept_id igual a 20 são colocadas em outro grupo (marcado em verde) e, finalmente, as linhas com dept_id igual a 30 são colocadas em outro grupo (marcado em vermelho). A função SUM() calcula a soma das linhas em cada grupo.

Observe que, ao usar SUM() OVER(PARTITION BY), você mantém os detalhes das linhas individuais. Você pode, por exemplo, ver os detalhes do funcionário chamado Ford: seu cargo, seu salário e como ele se compara ao total de salários em seu departamento.

Esse é o uso mais comum do site SUM() OVER(PARTITION BY): você calcula o valor da soma para cada grupo de dados e mantém os detalhes das linhas individuais. Vejamos um exemplo semelhante.

Exemplo 1B: Cálculo do salário para cada cargo

Podemos usar SUM(salary) OVER(PARTITION BY job) para obter o salário total por cargo. Vamos dar uma olhada:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  SUM(salary) OVER(PARTITION BY job) AS job_total_salary
FROM employees;

Aqui está o resultado:

Desta vez, as linhas são agrupadas com base no valor do cargo e não no ID do departamento. Os funcionários com o mesmo cargo são colocados em um grupo e calculamos o salário total das pessoas com esse cargo. A função SUM() é aplicada a todos os salários em cada grupo: o salário total do grupo "Manager" é a soma de 2450, 2850 e 2975, que são os salários dos três gerentes que estão em nossa tabela.

Exemplo 2: Calcular a proporção de um valor de uma única linha em relação ao total

Normalmente, queremos ver a comparação entre cada linha individual e a soma total. Vamos calcular a porcentagem de cada salário individual em relação ao total de salários em seu departamento.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2) 
           AS percentage_of_dept_total_salary
FROM employees;

Desta vez, as linhas são agrupadas com base no valor job em vez do ID do departamento. Os funcionários com o mesmo cargo são colocados em um grupo e calculamos o salário total das pessoas nesse cargo. A função SUM() é aplicada a todos os salários em cada grupo: o salário total do grupo "Manager" é a soma de 2450, 2850 e 2975, que são os salários dos três gerentes que estão em nossa tabela.

Exemplo 2: Calcular a proporção de um valor de uma única linha em relação ao total

Normalmente, queremos ver a comparação entre cada linha individual e a soma total. Vamos calcular a porcentagem de cada salário individual em relação ao total de salários em seu departamento.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2) 
           AS percentage_of_dept_total_salary
FROM employees;

Podemos ver, por exemplo, que o analista Scott tem um salário individual de 3.000; isso representa 27,59% do total de salários em seu departamento.

Aqui está um detalhamento das funções usadas para realizar essa tarefa:

• Pegamos cada salário individual e o dividimos pelo salário total do departamento:

salary / SUM(salary) OVER(PARTITION BY dept_id)

• Para obter uma porcentagem, multiplicamos por 100%:
  
  • 0 * salary / SUM(salary) OVER(PARTITION BY dept_id)
• Em seguida, usamos a função ROUND() para obter dois dígitos decimais:

ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2)

O cálculo da proporção entre a linha individual e a soma de um grupo é outro caso de uso muito comum de SUM() com OVER(PARTITION BY). De maneira semelhante, você poderia calcular a diferença entre o total do grupo e a linha individual.

Qual é a diferença entre SUM() com OVER(PARTITION BY …) e usar SUM() com GROUP BY?

A cláusula PARTITION BY realiza uma tarefa semelhante à da cláusula GROUP BY. Tanto OVER(PARTITION BY) quanto GROUP BY dividem o conjunto de dados em partições ou grupos. Ao usar a função SUM(), ambas as expressões calculam a soma de cada grupo. Então, qual delas você deve escolher?

Ao usar GROUP BY, os detalhes de linhas individuais são recolhidos. Dê uma olhada:

SELECT 
  job, 
  SUM(salary) AS total_salary
FROM employees
GROUP BY job;

Aqui, obtemos o valor total do salário para cada cargo sem considerar os salários individuais dos funcionários.

No entanto, se você quiser comparar o valor da soma total com os valores das linhas individuais (por exemplo, para calcular a proporção entre o valor individual e o total), o caminho a seguir é OVER(PARTITION BY …). Ele retorna todas as linhas individuais juntamente com o valor da soma total de cada linha. Esse valor de soma total pode ser diferente, dependendo da partição à qual a linha pertence.

SELECT 
  name, 
  job, 
  salary, 
  SUM(salary) OVER(PARTITION BY job) AS total_salary
FROM employees;

O valor total_salary corresponde aos resultados da consulta anterior com GROUP BY. Mas aqui, você também pode ver os salários individuais. Por exemplo, há dois analistas que ganham 6.000 no total; cada um deles ganha 3.000.

A regra geral é: se você quiser saber apenas o valor da soma de cada grupo e não estiver interessado nos detalhes de cada linha individual, deverá usar a cláusula GROUP BY. Se você estiver interessado tanto na soma de cada grupo quanto nos detalhes de cada linha individual, use SUM() OVER(PARTITION BY).

Exemplo 3: cálculo de um total em execução usando SUM() com OVER(PARTITION BY)

Outro uso comum da sintaxe SUM() OVER(...) é calcular o total em execução.

Um total em execução é a soma cumulativa dos números anteriores em uma coluna. Usamos totais em execução para calcular valores que se acumulam ao longo do tempo. Por exemplo, com um total em execução, você pode calcular o uso mensal de dados móveis adicionando o valor de cada dia subsequente à soma dos valores dos dias anteriores. Da mesma forma, é possível calcular como o número de usuários registrados aumenta a cada dia ou como a receita total aumenta a cada transação.

Usamos a sintaxe SUM() com OVER(PARTITION BY … ORDER BY …) para calcular o total em execução.

Vamos dar uma olhada em um exemplo. Usaremos a tabela orders de dados de amostra fornecidos pelo Oracle Live SQL. Essa tabela armazena dados históricos de pedidos. Cada pedido tem sua data (order_date), representante de vendas (sales_rep_id) e valor total (order_total).

Aqui, calculamos o valor total em execução para cada representante de vendas:

SELECT 
  order_date, 
  sales_rep_id, 
  order_total,
  SUM(order_total) OVER(PARTITION BY sales_rep_id ORDER BY order_date) 
          AS running_total
FROM orders;

Para isso, particionamos nossa tabela por representante de vendas e, em seguida, ordenamos cada partição por data:

	SUM(order_total) OVER(PARTITION BY sales_rep_id ORDER BY order_date)

O novo elemento de sintaxe aqui é ORDER BY. Ele define como as linhas são classificadas em cada quadro de janela. A função SUM() é aplicada a cada linha; ela soma o valor atual order_total ao valor anterior running_total; os totais em execução são calculados separadamente para cada partição (aqui, para cada ID de representante de vendas).

Vamos dar uma olhada mais de perto no representante de vendas com ID 153. Seu primeiro pedido foi feito em²⁹ de março e tinha um valor total de 1.794,6. Nesse momento, o total em execução (a soma) é igual ao valor do pedido. Seu segundo pedido foi em¹⁶ de agosto, no valor de 78279,6; agora o total em execução é igual à soma dos valores do primeiro e do segundo pedidos (10794,6 + 78279,6 = 89074,2). Após o terceiro pedido, o total em execução é igual ao total em execução anterior mais o valor do terceiro pedido (89074,2 + 129 = 89203,2). Esse processo é semelhante para o representante de vendas com o ID 154.

O cálculo de um total em execução é um padrão comum ao usar o SQL para análise de dados. Você pode ler como calcular um total em execução no SQL em outra parte do nosso blog.

Exemplo 4: Contagem de objetos em categorias personalizadas

A função SUM() é frequentemente combinada com a instrução CASE WHEN para contar objetos em categorias personalizadas. Por exemplo, talvez você queira computar o salário total dos funcionários em cargos de gerência em um departamento e exibi-lo ao lado dos detalhes de cada funcionário. Você pode fazer isso usando SUM() OVER(PARTITION BY) em combinação com CASE WHEN.

Vamos primeiro analisar a instrução CASE WHEN. Ela é semelhante à instrução if, comum em muitas linguagens de programação. Nós a usamos para definir o valor de uma expressão em diferentes situações ou casos.

No exemplo a seguir, usamos a instrução CASE WHEN para identificar cada funcionário como gerência (gerentes e presidentes) ou como funcionário comum (todos os outros cargos). Dê uma olhada:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN ‘MANAGEMENT’ 
    ELSE ‘REGULAR’ END AS emp_type
  salary
FROM employees;

O comando CASE avalia todas as condições do WHEN. Se encontrar a condição correspondente, retorna o valor no ramo THEN. Se não encontrar a condição correspondente, ele retornará o valor fornecido após ELSE. Em nosso exemplo, os funcionários no cargo de presidente ou gerente são rotulados como 'Management'. Todos os outros cargos recebem o rótulo "Regular". Aqui está o resultado da consulta:

Você pode ler mais sobre CASE em nosso artigo Como utilizar o CASO em SQL.

Você também pode usar a instrução CASE com SUM() para somar valores em categorias personalizadas. Aqui está a consulta:

SELECT
  dept_id,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN salary
    ELSE 0 END) 
    AS dept_management_salary,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN 0
    ELSE salary END) 
    AS dept_regular_salary
FROM employees
GROUP BY dept_id;

Para funcionários em cargos de gerência, a primeira instrução CASE retorna o valor da coluna salary. A função SUM(), combinada com GROUP BY, soma todos os salários dos funcionários de gerência com o mesmo dept_id. Dessa forma, calculamos o salário total de gerência por departamento. A segunda instrução CASE calcula o salário total do departamento para todos os funcionários regulares.

Você pode calcular o salário total dos funcionários em posição de gerência em um departamento e exibi-lo ao lado dos detalhes do funcionário usando SUM() com PARTITION BY:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN ‘MANAGEMENT’ 
    ELSE ‘REGULAR’ END AS emp_type
  salary,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN salary 
    ELSE 0 END) OVER(PARTITION BY dept_id) AS dept_management_salary
FROM employees;

As linhas são divididas em grupos com base na coluna dept_id. Há três grupos, um para cada departamento. A função SUM() é aplicada à expressão CASE WHEN. Em vez de calcular o salário total em cada departamento, calculamos o salário total dos funcionários em cargos de gerência no departamento. O CASE retorna 0 para os funcionários regulares (a soma não é aumentada) e o valor do salário para os funcionários gerenciais. Você pode usar esse padrão em muitas situações diferentes. De fato, na próxima seção, veremos outro exemplo de combinação de CASE WHEN com SUM() e OVER(PARTITION BY).

Exemplo 5: Cálculo de um total de pedido com descontos usando SUM() com CASE WHEN

Neste exemplo, queremos calcular o valor total de cada pedido, incluindo descontos para alguns itens do pedido. Usaremos a sintaxe SUM() OVER(PARTITION BY …) para somar todos os valores de itens por pedido. E para incorporar os descontos do produto, usaremos a instrução CASE WHEN.

Usamos a tabela order_items de dados de amostra fornecidos pelo Oracle Live SQL. Essa tabela armazena todos os itens do pedido (product_id) pertencentes a cada pedido (order_id). Ela contém informações sobre o preço unitário do produto (unit_price) e a quantidade do pedido (quantity).

Agora, queremos calcular os valores do pedido: um para cada produto do pedido e um total para o pedido; o total do pedido deve incluir quaisquer descontos aplicados aos produtos.

O total do pedido é igual ao preço unitário multiplicado pela quantidade do pedido. Entretanto, quando houver um desconto aplicado a um produto, usaremos um demonstrativo CASE WHEN para contabilizá-lo.

SELECT 
  order_id, 
  product_id, 
  unit_price, 
  quantity,   

  SUM(CASE 
     -- 20% discount for this product
     WHEN product_id = 3143 THEN unit_price*quantity*0.8
     -- 40% discount for this product
     WHEN product_id = 3106 THEN unit_price*quantity*0.6
     ELSE unit_price*quantity END)
  OVER(PARTITION BY order_id, product_id) 
        AS order_product_total_with_discount,

  SUM(CASE 
     -- 20% discount for this product
     WHEN product_id = 3143 THEN unit_price*quantity*0.8
     -- 40% discount for this product
     WHEN product_id = 3106 THEN unit_price*quantity*0.6
     ELSE unit_price*quantity END)
  OVER(PARTITION BY order_id) 
        AS order_total_with_discount    

FROM order_items;

Para calcular o total por produto em um pedido, usamos a seguinte sintaxe:

SUM(CASE 
       WHEN product_id=3143 THEN unit_price*quantity*0.8 -- 20% discount 
       WHEN product_id=3106 THEN unit_price*quantity*0.6 -- 40% discount 
       ELSE unit_price*quantity END)
OVER(PARTITION BY order_id, product_id) AS order_product_total_with_discount

Passamos a instrução CASE WHEN como um argumento para a função SUM(); se o ID do produto da linha atual for 3143, aplicaremos um desconto de 20%; para o ID do produto 3106, o desconto será de 40%. Em seguida, particionamos o conjunto de dados por ID do pedido e ID do produto para obter os valores totais de cada produto em um pedido.

Observe que usamos duas cláusulas PARTITION BY diferentes na consulta. Para calcular o valor total do produto, particionamos o conjunto de dados por ID do pedido e ID do produto. Para calcular o total por pedido, particionamos o conjunto de dados somente pelo ID do pedido. O valor total do pedido é igual para todas as linhas com o mesmo ID de pedido. Ou seja, se você somar todos os valores totais do produto de um determinado pedido, obterá o valor total do pedido. Por exemplo, para o ID do pedido de 2355, obtemos o seguinte: 9200 + 10545 + 16337.2 + 3572 + 3230 + 211.2 + 216.7 + 4975 + 46226.4 = 94513.5.

Aqui estão alguns exemplos da função SUM() com CASE WHEN.

Vá em frente e use SUM() com OVER() e PARTITION BY

Agora que você já sabe como usar a função de janela SUM() e suas várias opções de sintaxe, pode experimentá-la você mesmo.

Apresentamos exemplos de como calcular uma porcentagem de um valor de uma única linha em relação ao total da partição e mostramos como calcular o total em execução e o total personalizado da ordem. Recomendamos que você pratique com seus próprios dados.

Para saber mais, experimente nosso curso interativo Funções de Janela (Window Functions) em SQL curso interativo, que explica detalhadamente todos os conceitos das funções de janela. Ou confira nossa Folha de dicas Funções de Janela (Window Functions) em SQL se você quiser uma referência rápida e fácil sobre as funções de janela do SQL.

Boa sorte!