Funções da janela SQL

Começando aqui? Esta lição faz parte de um tutorial completo sobre o uso de SQL para análise de dados. Verifique o início.

Nesta lição, cobriremos:

• Introdução às funções da janela
• Sintaxe básica de janelas
• Os suspeitos do costume: SUM, COUNT e AVG
• ROW_NUMBER ()
• RANK () e DENSE_RANK ()
• NTILE
• LAG e LEAD
• Definindo um alias de janela
• Técnicas avançadas de janelamento

Esta lição usa dados do Programa Capital Bikeshare de Washington DC, que publica dados históricos detalhados de nível de viagem em seu site. Os dados foram baixados em fevereiro de 2014, mas estão limitados aos dados coletados durante o primeiro trimestre de 2012. Cada linha representa uma viagem. A maioria dos campos são autoexplicativos, exceto rider_type: “Registrado” indica uma assinatura mensal do programa de compartilhamento de viagens, “Casual” indica que o passageiro comprou um passe de 3 dias. Os campos start_time e end_time foram limpos de seus formulários originais para se adequar à formatação de data SQL – eles são armazenados nesta tabela como carimbos de data / hora.

Introdução às funções da janela

A documentação do PostgreSQL faz um excelente trabalho ao apresentar o conceito de funções da janela:

Uma função de janela executa um cálculo em um conjunto de linhas da tabela que estão de alguma forma relacionadas à linha atual. Isso é comparável ao tipo de cálculo que pode ser feito com uma função de agregação. Mas, ao contrário das funções de agregação regulares, o uso de uma janela função não faz com que as linhas sejam agrupadas em uma única linha de saída – as linhas mantêm suas identidades separadas. Nos bastidores, a função de janela é capaz de acessar mais do que apenas a linha atual do resultado da consulta.

O exemplo mais prático disso é um total em execução:

Você pode ver que a consulta acima cria uma agregação (running_total) sem usar GROUP BY. Vamos analisar a sintaxe e ver como ela funciona.

Sintaxe básica de janelas

A primeira parte da agregação acima, SUM(duration_seconds) , se parece muito com qualquer outra agregação. Adicionar OVER designa-o como uma função de janela. Você pode ler a agregação acima como “tirar a soma de duration_seconds em todo o conjunto de resultados, em ordem start_time. “

Se desejar estreitar a janela de todo o conjunto de dados para grupos individuais dentro o conjunto de dados, você pode usar PARTITION BY para fazer isso:

A consulta acima agrupa e ordena a consulta por start_terminal . Dentro de cada valor de start_terminal, é ordenado por start_time e as somas totais em execução na linha atual e todas as linhas anteriores de duration_seconds. Role para baixo até que o valor start_terminal mude e você perceberá que running_total é reiniciado. Isso é o que acontece quando você agrupa usando PARTITION BY. Caso você ainda esteja perplexo com ORDER BY, ele simplesmente ordena pela coluna designada (s) da mesma forma que a cláusula ORDER BY faria, exceto que trata cada partição como separada. Ele também cria o total corrente – sem ORDER BY, cada valor será simplesmente uma soma de todos os duration_seconds valores em seus respectivos start_terminal. Tente executar a consulta acima sem ORDER BY para ter uma ideia:

O ORDER e PARTITION defina o que é referido como” janela “- o subconjunto ordenado de dados sobre os quais os cálculos são feitos.

Nota: Você não pode usar funções de janela e agregações padrão na mesma consulta. Mais especificamente, você não pode “incluir funções de janela em uma cláusula GROUP BY.

Os suspeitos usuais: SUM, COUNT e AVG

Ao usar funções de janela, você pode aplicar os mesmos agregados que faria em circunstâncias normais— SUM, COUNT e AVG. A maneira mais fácil de entender isso é executar novamente o exemplo anterior com algumas funções adicionais. Faça

Como alternativa, as mesmas funções com ORDER BY:

Certifique-se de conectar as duas consultas anteriores no modo e executá-las. Este próximo problema prático é muito semelhante aos exemplos, então tente modificar o código acima em vez de começar do zero.

ROW_NUMBER ()

ROW_NUMBER() faz exatamente o que parece – exibe o número de uma determinada linha. Ele começa com 1 e numera as linhas de acordo com ORDER BY parte da instrução da janela. ROW_NUMBER() não exige que você especifique uma variável entre parênteses:

Usar a cláusula PARTITION BY permitirá que você comece a contar 1 novamente em cada partição. A consulta a seguir inicia a contagem novamente para cada terminal:

RANK () e DENSE_RANK ()

RANK() é ligeiramente diferente de ROW_NUMBER(). Se você pedir por start_time, por exemplo, pode ser que alguns terminais tenham corridas com dois horários de início idênticos. Nesse caso, eles recebem a mesma classificação, enquanto ROW_NUMBER() dá a eles números diferentes. Na consulta a seguir, você observa a 4ª e a 5ª observações para start_terminal 31000 – ambas recebem uma classificação 4 e o seguinte resultado recebe uma classificação 6:

Você também pode usar DENSE_RANK() em vez de RANK() dependendo do seu aplicativo. Imagine uma situação em que três entradas tenham o mesmo valor. Usando qualquer um dos comandos, todos eles obterão a mesma classificação. Por causa deste exemplo, digamos que é “2”. Veja como os dois comandos avaliam os próximos resultados de forma diferente:

• RANK() daria às linhas idênticas uma classificação 2 e, em seguida, pularia as classificações 3 e 4, então o próximo resultado seria 5
• DENSE_RANK() ainda daria a todas as linhas idênticas uma classificação de 2, mas a linha seguinte seria 3 – não classificações seriam ignoradas.

NTILE

Você pode usar funções de janela para identificar em qual percentil (ou quartil, ou qualquer outra subdivisão) uma determinada linha se enquadra. A sintaxe é NTILE(*# of buckets*). Nesse caso, ORDER BY determina qual coluna usar para determinar os quartis (ou qualquer número de “blocos que você especificamos). Por exemplo:

Olhando para os resultados da consulta acima, você pode ver que a coluna percentile não calcula exatamente como você esperava. Se você apenas tinha dois registros e você estava medindo percentis, você esperava que um registro definisse o 1º percentil e o outro registro definisse o 100º percentil. Usando a função NTILE, o que você realmente verá é um registro no 1º percentil e um no 2º percentil. Você pode ver isso nos resultados de start_terminal 31000 — a coluna percentile apenas parece uma classificação numérica. Se você rolar para baixo até start_terminal 31007, você pode ver que calcula corretamente os percentis porque há mais de 100 registros para esse start_terminal. Se você estiver trabalhando com janelas muito pequenas, lembre-se disso e considere o uso de quartis ou igualmente pequenos bandas.

LAG e LEAD

Muitas vezes, pode ser útil comparar as linhas com as anteriores ou seguintes, especialmente se você tiver os dados em uma ordem que faça sentido. Você pode usar LAG ou LEAD para criar colunas que extraiam valores de outros linhas – tudo que você precisa fazer é inserir de qual coluna puxar e quantas linhas de distância você gostaria de puxar. LAG extrai das linhas anteriores e LEAD extrai das seguintes linhas:

Isso é especialmente útil se você deseja calcular diferenças entre as linhas:

A primeira linha da coluna difference é nula porque não há nenhuma linha anterior da qual extrair. Da mesma forma, usar LEAD criará nulos no final do conjunto de dados. Se desejar tornar os resultados um pouco mais claros, você pode envolvê-lo em uma consulta externa para remover nulos:

Definindo um alias de janela

Se você está planejando escrever várias funções de janela na mesma consulta, usando a mesma janela, você pode criar um alias.Veja o NTILE exemplo acima:

Isso pode ser reescrito como:

O WINDOW cláusula, se incluída, deve sempre vir após a WHERE cláusula.

Técnicas avançadas de janelamento

Você pode verificar uma lista completa de janelas funções no Postgres (o modo de sintaxe usa) na documentação do Postgres. Se você estiver usando funções de janela em um banco de dados conectado, deve consultar o guia de sintaxe apropriado para o seu sistema.