A cláusula ORDER BY nas funções de janela do SQL

Neste artigo, você terá uma visão geral do site ORDER BY em funções de janela. Você saberá como ele se compara ao PARTITION BY e ao ORDER BY normal, tudo acompanhado de exemplos e explicações.

Se quiser criar relatórios que vão além de simples agregações, você precisará das funções de janela do SQL. As funções de janela o ajudam a criar classificações, calcular totais em execução e médias móveis e encontrar a diferença entre linhas. Para usar as funções de janela de forma eficaz, você deve entender o papel da cláusula ORDER BY. Ela não apenas altera o comportamento das funções de janela, mas algumas funções de janela nem sequer são executadas sem ORDER BY.

Este artigo destina-se àqueles que já têm um entendimento geral das funções de janela. Se você não estiver familiarizado com elas, recomendo enfaticamente que faça nosso Funções de Janela (Window Functions) em SQL curso. Ele lhe dará um conhecimento abrangente das funções de janela, incluindo funções de classificação, funções analíticas e as cláusulas ORDER BY e PARTITION BY. O curso apresenta 218 desafios de codificação que precisam ser resolvidos; você pode encontrar mais no Funções de Janela (Window Functions) em SQL Practice Set.

Mesmo que você conheça as funções de janela do SQL, talvez queira manter nossa Folha de dicasFunções de Janela (Window Functions) em SQL por perto para referência rápida.

O que é Funções de Janela (Window Functions) em SQL?

As funções de janela no SQL executam operações em um quadro de janela, que consiste na linha atual e nas linhas relacionadas a ela. Diferentemente das funções agregadas, as funções de janela não comprimem linhas individuais; em vez disso, adicionam uma coluna a cada linha com o resultado da função. Isso significa que as funções de janela permitem que você veja dados individuais e agregados simultaneamente.

Sintaxe

A sintaxe das funções de janela do SQL é mostrada abaixo:

window_function OVER ([PARTITION BY column_name] [ORDER BY column_name ASC|DESC])

Veja a seguir uma breve descrição de cada parte da sintaxe:

• window_function: A função de janela que você deseja usar.
• OVER(): Uma cláusula obrigatória para criar uma função de janela.
• PARTITION BY: Uma cláusula opcional que particiona (divide) os dados.
• ORDER BY: Uma cláusula opcional que classifica os dados dentro do quadro da janela.

O que é a cláusula ORDER BY em Funções de Janela (Window Functions) em SQL?

ORDER BY (juntamente com PARTITION BY) é uma parte fundamental de muitas funções de janela. ORDER BY na função de janela classifica as linhas dentro do quadro da janela. Ela define a ordem em que o cálculo da função de janela será realizado.

ORDER BY pode classificar os dados em uma janela de forma ascendente (A a Z, 1 a 10) ou descendente (Z a A, 10 a 1). Você pode classificar dados de texto em ordem alfabética ou alfabética inversa, dados numéricos do menor para o maior (ou vice-versa) e dados de data/hora do mais antigo para o mais recente (ou do mais recente para o mais antigo).

Exemplo: ORDER BY com uma função de janela de classificação

Vejamos um exemplo de como o ORDER BY em funções de janela influencia a execução da consulta.

A consulta abaixo classifica os dados na tabela product_sales por vendas em ordem decrescente, ou seja, das vendas mais altas para as mais baixas.

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(ORDER BY sales DESC) AS ranking
FROM product_sales; 

DENSE_RANK() ORDER BY é uma das funções de janela de classificação. Ela classifica todo o conjunto de dados das vendas mais altas para as mais baixas, o que é especificado em ORDER BY.

A tabela product_sales original tem a seguinte aparência:

Portanto, o código acima classificará as linhas no conjunto de dados de forma decrescente: da maior para a menor.

Esta é a saída da consulta, com as classificações mostradas em uma coluna separada:

Agora, a classificação seria significativamente diferente se você substituísse DESC por ASC em ORDER BY, conforme mostrado abaixo:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(ORDER BY sales ASC) AS ranking
FROM product_sales; 

Agora, a classificação é realizada da menor para a maior venda:

ORDER BY e PARTITION BY em Funções de Janela (Window Functions) em SQL

Sabemos o que ORDER BY faz nas funções de janela. E quanto ao PARTITION BY? Essa é uma cláusula opcional que divide os dados em subconjuntos com base em uma ou mais categorias. Fazemos isso especificando colunas na cláusula PARTITION BY da seguinte forma: PARTITION BY product_name. Demonstrarei como isso funciona na seção a seguir.

Sem PARTITION BY, ORDER BY classifica os dados em todo o conjunto de resultados. Mas use-o com PARTITION BY e ele classificará os dados em cada partição separadamente.

Exemplo: Uso de ORDER BY com PARTITION BY em uma função de janela

Quando usado com PARTITION BY, ORDER BY classifica os dados dentro de cada partição.

O uso de ORDER BY com a função de janela SUM() produz uma soma cumulativa (ou seja, a soma do valor da linha atual mais todas as linhas anteriores a ela na partição). Por exemplo, o código abaixo calcula a soma cumulativa das vendas por nome de produto (conforme especificado em PARTITION BY) da data mais antiga até a mais recente (conforme especificado em ORDER BY).

SELECT 
  id,
  date,
  sales,
  product_name,
  SUM(sales) OVER(PARTITION BY product_name ORDER BY date ASC) AS cumulative_sum
FROM product_sales; 

A consulta primeiro dividirá o conjunto de dados em partições por nome de produto. Em seguida, os valores dentro de cada partição serão classificados da data mais antiga para a mais recente, que é especificada em ORDER BY. Em seguida, a função de janela SUM() calculará o total cumulativo somando as vendas da data atual com as vendas de todas as vendas anteriores dentro da partição.

Aqui está o resultado, que mostra cada linha e as vendas acumuladas de cada produto:

Se removermos ORDER BY do código, conforme mostrado abaixo ...

SELECT 
  id,
  date,
  sales,
  product_name,
  SUM(sales) OVER(PARTITION BY product_name) AS cumulative_sum
FROM product_sales; 

... o código retornará a soma total das vendas de cada produto. Em outras palavras, ao simplesmente omitir ORDER BY de uma função de janela, você perde a capacidade de calcular cumulativamente. Sem ORDER BY, todas as linhas da partição formam a moldura da janela.

ORDER BY em Funções de Janela (Window Functions) em SQL vs. ORDER BY regular

Quando digo regular, estou me referindo ao padrão ORDER BY no final da consulta. Qual é a diferença de ORDER BY em uma função de janela?

Um ORDER BY regular classifica uma saída de consulta, enquanto o ORDER BY em funções de janela classifica uma janela de dados ou uma partição de dados.

Exemplo: ORDER BY em Funções de Janela (Window Functions) em SQL e ORDER BY normal

Essa consulta usa a função de janela DENSE_RANK() com PARTITION BY e ORDER BY para classificar as datas de vendas de cada produto:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(PARTITION BY product_name ORDER BY sales DESC) AS day_rank
FROM product_sales;

Você pode ver que o resultado geral não está classificado das vendas mais altas para as mais baixas. A classificação decrescente das datas é aplicada somente dentro de cada partição.

Se você quiser que o resultado seja classificado de forma decrescente por vendas, terá de fazer isso explicitamente adicionando ORDER BY no final da consulta:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(PARTITION BY product_name ORDER BY sales DESC) AS sales_rank_by_product
FROM product_sales
ORDER BY sales DESC;

Agora você obtém o resultado classificado por vendas e data. Você pode ver como a classificação de vendas de uma data se compara às vendas de outras datas do mesmo produto.

Funções de Janela (Window Functions) em SQL Isso requer ORDER BY

Como você viu no exemplo anterior, as funções de janela podem funcionar sem ORDER BY. ORDER BY é geralmente considerada uma cláusula opcional em funções de janela.

Porém, mesmo que ORDER BY em funções de janela seja considerada uma cláusula opcional, algumas funções de janela exigem que ORDER BY funcione. Nesses casos, o ORDER BY se torna obrigatório; essas funções de janela exigem uma ordem de classificação para funcionar corretamente. Essas funções de janela são:

• RANK()
• DENSE_RANK()
• NTILE()
• LEAD()
• LAG()

Quadros de janela padrão com e sem ORDER BY

O comportamento de uma função de janela muda dependendo do fato de ela ter sido escrita com ou sem ORDER BY. Mais precisamente, a presença ou ausência de ORDER BY afeta o quadro padrão da janela.

Se não houver ORDER BY, o quadro de janela padrão incluirá a linha atual e todas as linhas anteriores e posteriores a ela. Em outras palavras, todas as linhas da partição são incluídas. Vimos isso no exemplo da soma cumulativa: sem ORDER BY, o quadro da janela tem como padrão toda a partição e a soma se torna a soma total.

Se houver ORDER BY, a moldura da janela incluirá o valor atual e todos os valores anteriores. Vimos isso no exemplo da soma cumulativa: com ORDER BY, o quadro da janela inclui todas as linhas anteriores à linha atual e a linha atual.

Se você não gostar do quadro de janela padrão, poderá defini-lo explicitamente usando as palavras-chave ROWS e RANGE.

Uso de ORDER BY em Funções de Janela (Window Functions) em SQL

Aí está: ORDER BY - às vezes obrigatória, às vezes opcional - é a cláusula que cria e, ocasionalmente, quebra as funções de janela.

Entretanto, saber tudo sobre ORDER BY em funções de janela não vale nada se você não puder usá-la em suas consultas. Portanto, teste o que você aprendeu aqui resolvendo os desafios de codificação em nosso curso Window Function e no Funções de Janela (Window Functions) em SQL Practice Set.

Para obter ainda mais exercícios, resolva estes 11 exercícios de função de janela e responda às 10 principais perguntas da entrevista sobre função de janela. Bom aprendizado!