Aprenda Tipos de Funções de Janela | Alguns Tópicos Adicionais

Deslize para mostrar o menu

Vamos explorar brevemente os principais tipos de funções de janela utilizadas em SQL.

Funções de agregação

Estas são as funções de agregação padrão (AVG, SUM, MAX, MIN, COUNT) usadas em um contexto de janela. Já utilizamos esse tipo de função de janela no capítulo anterior.

Funções de classificação

Funções de classificação em SQL são um tipo de função de janela que permite atribuir uma classificação a cada linha dentro de uma partição de um conjunto de resultados. Essas funções podem ser extremamente úteis para realizar cálculos e análises ordenadas.

RANK(): atribui uma classificação única para cada linha distinta dentro da partição com base na cláusula ORDER BY. Linhas com valores iguais recebem a mesma classificação, deixando lacunas na sequência;
DENSE_RANK(): semelhante ao RANK(), mas sem lacunas na sequência de classificação;
NTILE(n): divide as linhas em uma partição ordenada em n grupos e atribui um número de grupo para cada linha.

Exemplo

Vamos classificar as vendas com base no Amount para cada ProductID em ordem crescente utilizando a função DENSE_RANK():


              12345678
            
SELECT 
    sales_id,
    product_id,
    sales_date,
    amount,
    DENSE_RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS dense_rank_amount
FROM 
    Sales;

A tabela de resultados contém todas as informações da tabela principal e uma coluna adicional que fornece a classificação de cada venda para o respectivo produto.

Funções de comparação de valores

Funções de janela para comparação de valores em SQL são usadas para comparar valores da linha atual com valores de outras linhas dentro da mesma partição.
Essas funções são particularmente úteis para tarefas que envolvem análise de tendências, cálculos baseados em linhas adjacentes ou acesso a valores específicos de linhas dentro de uma janela definida. Existem várias funções de comparação de valores em SQL:

LAG(): recupera o valor de uma linha anterior no conjunto de resultados sem a necessidade de um self-join;
LEAD(): recupera o valor de uma linha subsequente no conjunto de resultados sem a necessidade de um self-join;
FIRST_VALUE(): retorna o valor da primeira linha na janela;
LAST_VALUE(): retorna o valor da última linha na janela.

Exemplo

Vamos utilizar a função de janela de comparação de valores LAG() para calcular a variação no valor das vendas em relação à venda anterior para cada produto:


              1234567891011
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS amount_change
FROM
    Sales
ORDER BY
    product_id, sales_date;

Descrição da Consulta

Principais Partes da Consulta:

Cláusula SELECT: Especifica as colunas a serem recuperadas da tabela.
- Nomes das Colunas: Inclui colunas como SalesID, ProductID, SalesDate e Amount para exibir os dados relevantes de cada venda.
- Função de Janela: Utiliza a função LAG() para recuperar o valor da linha anterior para uma coluna específica dentro de uma partição. Uma coluna calculada adicional mostra a diferença entre os valores atual e anterior.

Sintaxe da Função de Janela:

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
- column_name: Coluna da qual recuperar o valor anterior.
- offset: Número de linhas anteriores a partir da linha atual para recuperar o valor (padrão é 1).
- default_value: Valor retornado se o deslocamento ultrapassar os limites da partição (opcional).
- PARTITION BY partition_column: Divide o conjunto de resultados em partições às quais a função de janela é aplicada, garantindo que a função opere separadamente em cada partição.
- ORDER BY order_column: Especifica a ordem das linhas dentro de cada partição, garantindo que a função de janela processe as linhas em uma sequência significativa.

Como resultado, é possível extrair informações sobre as diferenças de vendas para cada produto específico sem utilizar subconsultas ou procedimentos armazenados.
Também é possível calcular diferenças para todas as vendas sem particionamento utilizando a seguinte consulta:


              123456789
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (ORDER BY sales_date) AS amount_change
FROM
    Sales;

Você pode ver que não incluímos a cláusula PARTITION BY no bloco OVER. Isso significa que não queremos obter valores anteriores apenas para um produto específico, mas para todas as vendas na tabela.

Tudo estava claro?

Obrigado pelo seu feedback!

Seção 3. Capítulo 3

Pergunte à IA

Pergunte o que quiser ou experimente uma das perguntas sugeridas para iniciar nosso bate-papo

Seção 3. Capítulo 3