Domine as Window Functions no PostgreSQL: Conceitos, Exemplos Práticos e Aplicações Reais

A função ROW_NUMBER() é útil em vários contextos, especialmente quando você precisa atribuir números únicos a linhas dentro de grupos específicos, com base em uma ordem determinada. No exemplo fornecido, a função ROW_NUMBER() é utilizada para numerar as vendas em cada estado de acordo com a data da venda. Aqui estão algumas utilidades específicas para o uso de ROW_NUMBER() nesse contexto:

• Ordenação Cronológica: Permite que você atribua uma numeração sequencial às vendas em cada estado, com base na data. Isso é útil para acompanhar a ordem das vendas ao longo do tempo dentro de um estado, permitindo que você veja qual venda foi a primeira, a segunda, etc.
  
• Análise de Frequência: Com a numeração sequencial, você pode facilmente analisar padrões e tendências de vendas ao longo do tempo em cada estado. Por exemplo, ao olhar para as vendas numeradas como 1, 2, 3, etc., você pode identificar se as vendas tendem a aumentar ou diminuir em determinados períodos.
  
• Seleção de Vendas Recorrentes: Se você quiser selecionar apenas a primeira venda de cada estado, por exemplo, poderia usar um WHERE num_venda_por_estado = 1. Isso é útil em relatórios que precisam mostrar apenas o primeiro registro de cada grupo.
  
• Relatórios Detalhados: Em relatórios detalhados, você pode querer incluir uma numeração que ajude a organizar e compreender as vendas dentro de cada estado. A numeração sequencial pode facilitar a leitura e interpretação dos dados.
  
• Mudança de Status: Se você tivesse uma coluna adicional para indicar se um cliente fez outra compra, poderia usar ROW_NUMBER() para identificar a primeira, segunda, terceira compra, e assim por diante. Isso permite analisar como o comportamento de compra evolui ao longo do tempo.
  
• Subconsultas e Junções: A numeração sequencial com ROW_NUMBER() pode ser usada em subconsultas para operações mais complexas, como juntar apenas as vendas mais recentes ou as mais antigas em cada estado com outros conjuntos de dados.
  
• Histórico de Vendas: Se você precisar manter um histórico detalhado e rastreável das vendas, a numeração cronológica é essencial. Você pode, por exemplo, criar registros históricos onde a posição cronológica da venda dentro de um estado é um fator importante.

Neste exemplo, vamos gerar uma numeração sequencial para as vendas, ordenando-as pela data. O ROW_NUMBER() será reiniciado para cada estado usando a cláusula PARTITION BY.

select
    id,
    estado,
    vendedor,
    cliente,
    produto,
    "data",
    valor,
    row_number() over (partition by estado order by data) as num_venda_por_estado
from
    public.vendas

Resultado:

Explicação:

• PARTITION BY estado: Divide as vendas por estado, criando partições.
• ORDER BY data: Ordena as vendas dentro de cada partição (estado) pela data.
• ROW_NUMBER(): Gera uma numeração sequencial dentro de cada partição.

A função RANK() é utilizada em cenários onde você deseja classificar as linhas dentro de grupos específicos, com base em uma determinada ordem, e lidar com empates na classificação. Ao contrário de ROW_NUMBER(), que atribui números sequenciais únicos, RANK() atribui a mesma classificação a linhas com valores idênticos, deixando lacunas na sequência para as próximas classificações. Vamos explorar algumas utilidades da função RANK():

• Classificação com Empates: A função RANK() é útil quando você precisa atribuir classificações às linhas dentro de grupos, levando em conta empates. Por exemplo, se duas vendas em um estado possuem o mesmo valor, ambas receberão a mesma classificação e a próxima venda receberá a classificação correspondente com uma lacuna.
• Análise de Desempenho: Em relatórios de vendas, RANK() pode ser utilizado para analisar o desempenho relativo de produtos ou vendedores, especialmente quando há múltiplas vendas com valores semelhantes. Com isso, é possível identificar facilmente quais vendas se destacam e quais estão em posições intermediárias.
• Identificação de Posições: Ao utilizar RANK() em subconsultas, você pode identificar as posições relativas de elementos em um conjunto de dados, o que pode ser útil para filtrar ou comparar dados em junções ou em análises temporais.
• Relatórios de Liderança: Em rankings ou relatórios de liderança, onde você precisa mostrar a posição de um cliente, produto ou vendedor em relação a outros dentro de um grupo, RANK() é uma escolha ideal. Se dois ou mais itens estão empatados, eles aparecem na mesma posição, refletindo de forma precisa a igualdade entre eles.
  

Vamos considerar um exemplo onde você deseja classificar as vendas por estado e valor, utilizando a função RANK() para gerar uma classificação das vendas em cada estado:

select
    id,
    estado,
    cliente,
    produto,
    data,
    valor,
    rank() over (partition by estado order by valor desc) as rank_venda
from
    vendas

Resultado:

Explicação:

Nesse exemplo, o PARTITION BY estado divide as vendas por estado, e o ORDER BY valor DESC classifica as vendas dentro de cada estado do maior para o menor valor. A função RANK() atribui um número de classificação a cada venda dentro do grupo.

A função NTILE() é usada para dividir um conjunto de resultados em um número especificado de “buckets” ou “grupos”, distribuindo as linhas o mais igualmente possível entre esses grupos. Diferente de RANK(), NTILE() atribui um número sequencial para as linhas, correspondendo ao bucket no qual a linha está. Isso pode ser útil para criar distribuições percentuais ou segmentar dados em partes iguais.

Aqui está uma versão do exemplo utilizando a função NTILE():

select
    id,
    estado,
    vendedor,
    cliente,
    produto,
    data,
    valor,
    ntile(3) over (partition by estado order by valor desc) as ntile_venda
from
    vendas

Resultado:

Explicação:

• A função NTILE(3) divide as vendas dentro de cada estado em 3 grupos.
• PARTITION BY estado agrupa as vendas por estado.
• ORDER BY valor DESC garante que as vendas com maior valor estejam nos primeiros grupos.

Essa abordagem é útil para dividir as vendas de cada estado em partes iguais, permitindo ver, por exemplo, o “terço superior”, “terço intermediário” e “terço inferior” de vendas.

A função LAG() permite acessar os valores de linhas anteriores à linha atual dentro de uma janela, enquanto a função LEAD() faz o mesmo para as linhas posteriores. Ambas são úteis quando você precisa comparar valores anteriores ou seguintes em uma série de dados sem usar subconsultas complexas.

Aqui está a versão do exemplo utilizando as funções LAG() e LEAD():

select
    id,
    estado,
    vendedor,
    cliente,
    produto,
    data,
    valor,
    lag(valor, 1) over (partition by estado order by valor desc) as valor_anterior,
    lead(valor, 1) over (partition by estado order by valor desc) as valor_proximo
from
    vendas

Resultado:

Explicação:

• A função LAG() retorna o valor da linha anterior dentro da partição de acordo com a ordenação. Neste exemplo, LAG(valor, 1) traz o valor da venda anterior para cada estado, ordenado por valor.
• A função LEAD() retorna o valor da próxima linha. Aqui, LEAD(valor, 1) traz o valor da próxima venda.
• PARTITION BY estado agrupa as vendas por estado, e ORDER BY valor DESC garante a ordem decrescente dos valores de vendas.

Essas funções podem ser úteis para comparar os valores das vendas com as vendas imediatamente anteriores ou seguintes no mesmo estado, facilitando a análise de mudanças ou tendências.

A função FIRST_VALUE() retorna o primeiro valor de uma janela de resultados com base na ordenação especificada, enquanto a função LAST_VALUE() retorna o último valor da janela de resultados. Ambas são úteis para capturar valores de referência no início ou no final de um conjunto de dados particionado.

Aqui está a versão do exemplo utilizando as funções FIRST_VALUE() e LAST_VALUE():

select
    id,
    estado,
    vendedor,
    cliente,
    produto,
    data,
    valor,
    first_value(valor) over (partition by estado order by valor desc) as primeira_venda,
    last_value(valor) over (partition by estado order by valor desc rows between unbounded preceding and unbounded following) as ultima_venda
from
    vendas

Resultado:

Explicação:

• A função FIRST_VALUE(valor) retorna o maior valor (primeira venda) dentro da partição estado, ordenada por valor DESC.
• A função LAST_VALUE(valor) retorna o menor valor (última venda) dentro da mesma partição.
• PARTITION BY estado agrupa as vendas por estado.
• ORDER BY valor DESC ordena as vendas por valor decrescente, de modo que FIRST_VALUE() traga o maior valor e LAST_VALUE() o menor valor.
• O intervalo de linhas ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING é necessário para garantir que LAST_VALUE() retorne o valor da última linha da janela e não o último valor da janela atual, que por padrão se refere à linha atual.

Essas funções são úteis para capturar os valores extremos em cada grupo de dados, como a primeira e a última venda de um estado, facilitando análises como identificar o maior e o menor valor de venda em um conjunto.

A função CUME_DIST() calcula a “distribuição cumulativa” de um valor em um conjunto de dados. Ela retorna a proporção de linhas com valores menores ou iguais ao valor da linha atual, variando entre 0 e 1. Isso é útil quando você quer entender a posição relativa de uma linha em um conjunto de dados.

Aqui está a versão do exemplo utilizando a função CUME_DIST():

select
    id,
    estado,
    vendedor,
    cliente,
    produto,
    data,
    valor,
    cume_dist() over (partition by estado order by valor desc) as cume_dist_venda
from
    vendas

Resultado:

Explicação:

• A função FIRST_VALUE(valor) retorna o maior valor (primeira venda) dentro da partição estado, ordenada por valor DESC.
• A função LAST_VALUE(valor) retorna o menor valor (última venda) dentro da mesma partição.
• PARTITION BY estado agrupa as vendas por estado.
• ORDER BY valor DESC ordena as vendas por valor decrescente, de modo que FIRST_VALUE() traga o maior valor e LAST_VALUE() o menor valor.
• O intervalo de linhas ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING é necessário para garantir que LAST_VALUE() retorne o valor da última linha da janela e não o último valor da janela atual, que por padrão se refere à linha atual.

Essas funções são úteis para capturar os valores extremos em cada grupo de dados, como a primeira e a última venda de um estado, facilitando análises como identificar o maior e o menor valor de venda em um conjunto.

A função PERCENT_RANK() calcula a posição relativa de uma linha em relação ao conjunto de dados, expressa como um valor percentual entre 0 e 1. Ao contrário de CUME_DIST(), a PERCENT_RANK() distribui as linhas de forma que a primeira linha sempre tenha valor 0 e a última linha tenha valor 1 (ou próxima de 1, dependendo do número de linhas).

Aqui está a versão do exemplo utilizando a função PERCENT_RANK():

select
    id,
    estado,
    vendedor,
    cliente,
    produto,
    data,
    valor,
    percent_rank() over (partition by estado order by valor desc) as percent_rank_venda
from
    vendas

Resultado:

Explicação:

• A função PERCENT_RANK() retorna a posição percentual de uma linha em relação ao conjunto de dados dentro de uma partição.
• PARTITION BY estado agrupa as vendas por estado.
• ORDER BY valor DESC organiza as vendas por valor decrescente dentro de cada estado.
• O cálculo é feito considerando o número total de linhas na partição. A primeira linha de cada partição tem o valor 0, e a última linha se aproxima de 1 (ou é 1 se houver mais de uma linha).

Essa função é útil para determinar a posição percentual de uma venda em relação às outras dentro de um estado, proporcionando uma visão da classificação relativa dentro do grupo de vendas.

A função NTH_VALUE() retorna o valor da n-ésima linha de uma janela de resultados, onde o número n é especificado pelo usuário. Ela é útil quando você deseja capturar um valor específico em uma sequência ordenada de linhas dentro de um grupo.

Aqui está a versão do exemplo utilizando a função NTH_VALUE() para obter o terceiro maior valor de cada grupo de vendas por estado:

select
    id,
    estado,
    vendedor,
    cliente,
    produto,
    data,
    valor,
    nth_value(valor, 3) over (partition by estado order by valor desc rows between unbounded preceding and unbounded following) as terceira_venda
from
    vendas

Resultado:

Explicação:

• A função NTH_VALUE(valor, 3) retorna o valor da terceira venda em cada grupo de estados, conforme ordenado pelo valor.
• PARTITION BY estado agrupa as vendas por estado.
• ORDER BY valor DESC organiza as vendas de cada estado por valor decrescente.
• O intervalo ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING garante que a função olhe para toda a janela de valores dentro da partição.

Essa função é útil quando você deseja capturar o valor da n-ésima venda dentro de um grupo de vendas, possibilitando a análise de posições específicas em um conjunto de dados.

• Documentação PostgreSQL