Funções da Janela SQL vs. Funções Agregadas SQL: Similitudes e diferenças

Se você não está familiarizado com as funções de janela SQL, você pode se perguntar como elas diferem das funções agregadas. Quando você deve usar as funções de janela? Neste artigo, revisaremos as funções de janela e as funções agregadas, examinaremos suas semelhanças e diferenças, e veremos qual delas escolheremos dependendo do que você precisa fazer.

Depois de ter abordado o SQL básico, você provavelmente vai querer entrar em algumas de suas funções mais avançadas. Isso é ótimo; essas funções facilitam a elaboração de relatórios e análises.

Em breve, porém, você se deparará com dois grupos misteriosos de funções: funções de janela e funções agregadas. O que elas fazem? Como elas são diferentes?

Você está prestes a descobrir.

O que são funções agregadas de SQL?

As funções agregadas operam em um conjunto de valores para retornar um único valor escalar. Estas são funções agregadas SQL:

• AVG() retorna a média dos valores especificados.
• SUM() calcula a soma de todos os valores do conjunto.
• MAX() e MIN() retornam o valor máximo e mínimo, respectivamente.
• COUNT() retorna o número total de valores no conjunto.

Usando a cláusula GROUP BY, é possível calcular um valor agregado para vários grupos em uma consulta.

Por exemplo, digamos que temos dados de transação de duas cidades, São Francisco e Nova Iorque:

Você pode usar as funções agregadas SQL para calcular o valor médio diário da transação para cada cidade. Você precisará agrupar os dados tanto por data como por cidade:

SELECT date, city, AVG(amount) AS avg_transaction_amount_for_city
FROM transactions
GROUP BY date, city;

Aqui está o resultado desta consulta:

Usando a função agregada AVG() e GROUP BY, obtemos resultados que são agrupados por data e cidade. Tivemos duas transações em Nova York em 2 de novembro e duas transações em São Francisco em 3 de novembro, mas o conjunto de resultados não inclui essas transações individuais; as funções agregadas colapsam as linhas individuais e apresentam o valor agregado (aqui, média) para todas as linhas do grupo.

O que são SQL Funções de Janela (Window Functions) em SQL?

Em SQL, as funções de janela operam em um conjunto de linhas chamado quadro de janela. Elas retornam um único valor para cada linha a partir da consulta subjacente.

A moldura de janela (ou simplesmente janela) é definida usando a cláusula OVER(). Esta cláusula também permite definir uma janela com base em uma coluna específica (semelhante a GROUP BY).

Para calcular os valores retornados, as funções de janela podem utilizar funções agregadas, mas as utilizarão com a cláusula OVER().

Voltemos aos nossos dados para São Francisco e Nova Iorque. Aqui está a tabela novamente:

Queremos adicionar outra coluna a esta tabela com o valor médio diário da transação para cada cidade. A consulta SQL a seguir usa uma função de janela para obter o resultado de que precisamos:

SELECT id, date, city, amount,
       AVG(amount) OVER (PARTITION BY date, city) AS  avg_daily_transaction_amount_for_city
FROM transactions
ORDER BY id;

Aqui está o resultado:

Note que as linhas não estão colapsadas; ainda temos uma linha para cada uma de nossas transações. Todas as médias calculadas são apresentadas na coluna avg_daily_transaction_amount_for_city.

Você pode saber mais sobre as funções de janela neste guia detalhado. Ele fornece vários exemplos, incluindo aplicações simples e mais avançadas. Além disso, a equipe LearnSQL.com.br preparou um grande SQL Funções de Janela (Window Functions) em SQL Cheat Sheet. Imprima-a e cole-a em sua mesa, especialmente se você é novo nas funções de janela.

Semelhanças e diferenças entre as funções de janela e agregar

Agora que vimos os dois tipos de funções, podemos resumir as semelhanças e diferenças entre elas.

Tanto as funções de janela como as funções agregadas:

• Operar sobre um conjunto de valores (filas).
• Podem calcular valores agregados (por exemplo, AVG(), SUM(), MAX(), MIN(), ou COUNT()) no conjunto.
• Pode agrupar ou particionar dados em uma ou mais colunas.

Asfunções agregadas com GROUP BY diferem das funções de janela, pois elas:

• Use GROUP BY() para definir um conjunto de linhas para agregação.
• Agrupar linhas com base nos valores das colunas.
• Recolher linhas com base nos grupos definidos.

As funções de janela diferem das funções agregadas usadas com GROUP BY na medida em que elas:

• Use OVER() ao invés de GROUP BY() para definir um conjunto de linhas.
• Podem usar muitas outras funções além dos agregados (por exemplo: RANK(), LAG(), ou LEAD()).
• Grupos de linhas no ranking da linha, percentil, etc., assim como seu valor de coluna.
• Não colapsar as linhas.
• Pode usar uma estrutura de janela deslizante (que depende da linha atual).

Vamos demonstrar essa última diferença com mais um exemplo. Neste exercício, queremos calcular a média de vendas dos dias anteriores e atuais para cada data (ou seja, uma média móvel de 2 dias).

Eu sugiro começar com uma expressão de tabela comum (CTE) para definir o daily_sales tabela, onde temos o total de vendas para cada dia. Em seguida, usamos uma função de janela com uma estrutura de janela deslizante para calcular a média do total de vendas para os dias atuais e anteriores. A consulta é a seguinte:

WITH daily_sales AS (
    SELECT date, SUM(amount) AS sales_per_day
    FROM transactions
    GROUP BY date)
SELECT date, 
   AVG(sales_per_day) OVER (ORDER BY date ROWS 1 PRECEDING) 
AS avg_2days_sales
FROM daily_sales
ORDER BY date; 

Aqui está o resultado definido:

Na primeira linha, a tabela mostra o total de vendas para 1º de novembro porque não há linha anterior para esta data. Depois, na segunda linha, temos a média de vendas para 1 e 2 de novembro; na terceira linha, a tabela inclui a média de vendas para 2 e 3 de novembro, e assim por diante.

As funções de janela são ótimas para calcular médias móveis - algo que você não pode fazer usando apenas funções agregadas e GROUP BY().

Vamos praticar Funções de Janela (Window Functions) em SQL!

A tabela a seguir resume todas as semelhanças e diferenças entre as funções agregadas do SQL e as funções de janela:

Mesmo que as funções de janela SQL sejam um tópico avançado, você pode praticá-las por conta própria. Este guia especial sobre a prática das funções de janela SQL dá algumas dicas úteis para aprender a sintaxe das funções de janela e escrever as consultas correspondentes.

LearnSQL preparou um curso abrangente em Funções de Janela (Window Functions) em SQL; nele, você pode praticar a criação de estruturas de janela sofisticadas com 218 exercícios interativos. Você aprenderá como aproveitar as funções de janela para calcular totais de execução e médias móveis, construir rankings, encontrar os melhores e piores desempenhos e investigar tendências ao longo do tempo. Você pode aprender mais sobre este curso em nossa entrevista com LearnSQL.com.br Chief Content Officer Agnieszka Kozubek-Krycuń.

Obrigado por ler e aprender feliz!