6 exemplos da função NTILE() no SQL

A função SQL NTILE() pode simplificar muito a análise de dados e a geração de relatórios. Siga estes seis exemplos para saber o que ela é e quando usá-la.

As funções de janela são muito úteis e proporcionam grande poder de expressão ao SQL. Neste artigo, discutiremos a função NTILE(), que nos permite dividir um conjunto de registros em subconjuntos de tamanho aproximadamente igual. Essa função é amplamente usada em cálculos financeiros ou econômicos.

Antes de entrarmos na função NTILE(), vamos revisar rapidamente as funções de janela no SQL.

O que é SQL Funções de Janela em SQL?

As funções de janela são funções que funcionam em um grupo de linhas chamado janela; elas retornam um valor com base nesse grupo. A função NTILE(N) A função recebe um parâmetro inteiro(N) e divide o conjunto completo de linhas em N subconjuntos. Cada subconjunto tem aproximadamente o mesmo número de linhas e é identificado por um número entre 1 e N. Esse número de identificação é o que NTILE() retorna.

Se você quiser aprender as funções de janela do SQL, recomendo nosso curso interativo. Ele contém 218 exercícios práticos para ajudá-lo a praticar diferentes funções de janela. No final, você se sentirá à vontade para usar essa técnica avançada de SQL.

Se quiser ver as funções de janela em ação, confira nosso artigo Exemplo de função de janela SQL com explicações. E se você quiser explorar as diferenças entre GROUP BY e as funções de janela, leia SQL Funções de Janela em SQL vs. GROUP BY.

Exemplo de conjunto de dados: Uma loja de futebol

No restante do artigo, basearemos nossos exemplos no banco de dados de uma loja de futebol fictícia. Vamos nos concentrar na tabela sales que tem a seguinte estrutura e dados:

Acho que tudo nessa tabela é autoexplicativo, então vamos aos exemplos.

Exemplo nº 1: Dividindo linhas em 2 grupos

Começaremos com um exemplo muito simples. Queremos dividir as linhas da tabela sales em dois grupos: grupo nº 1 e grupo nº 2. Então, a consulta a seguir faz isso:

SELECT NTILE(2) OVER() AS group, 
       sale_date, 
       product_id,
 soccer_team 
FROM sales;

Na consulta, a expressão NTILE(2) OVER() retorna 1 para os primeiros 50% das linhas no conjunto de resultados e 2 para os segundos 50% das linhas. As linhas são atribuídas a cada grupo de forma não determinística, ou seja, não há nenhum critério para atribuir linhas a um grupo específico. Aqui está um resultado parcial da consulta, mostrando cada grupo em uma cor diferente:

Exemplo nº 2: Distribuição das vendas de 2023

A tabela sales armazena registros de cada venda concluída. O departamento de marketing está interessado em analisar a distribuição das vendas com base no valor gasto. Eles solicitaram um relatório que agrupasse todas as vendas do ano de 2023 em quatro grupos do mesmo tamanho (o número de vendas em cada grupo deve ser o mesmo). Cada venda deve ser atribuída com base no valor da venda.

O primeiro grupo (sale_group #1) deve ter as vendas com o menor valor, e o último grupo (sale_group #4) deve ter as vendas com o maior valor. Para cada venda, o relatório deve incluir o número do grupo de vendas, o customer_id, o product_id e o soccer_team. A consulta para obter esse resultado é a seguinte:

  SELECT
      NTILE(4) OVER ( ORDER BY amount ) AS sale_group,
      product_id,
      product_category,
      soccer_team,
      amount as sales_amount
  FROM sales
  WHERE sale_date >= '2023-12-01' AND sale_date <= '2023-12-31';

Essa consulta usa uma cláusula WHERE para filtrar as vendas que ocorreram em 2023. Em seguida, a função NTILE(4) tenta criar quatro grupos de linhas com o mesmo número de linhas cada. Usamos "tentativas" porque nem sempre é possível criar grupos do mesmo tamanho; alguns grupos podem ter uma linha a menos que os outros.

Como definimos a qual grupo cada linha pertence? A cláusula OVER (ORDER BY amount) indica que, antes de atribuir grupos às linhas, todas as linhas devem ser ordenadas com base no valor da venda. Uma vez ordenadas, a função NTILE(4) pegará o primeiro quarto das vendas e atribuirá a elas o valor 1, depois pegará o próximo quarto das vendas e atribuirá a elas o valor 2, e assim por diante. Abaixo está um resultado parcial mostrando os registros em cada sale_group:

Podemos ver que, no grupo mais barato (1), só temos vendas da categoria Acessórios. Isso ocorre porque os produtos de acessórios geralmente são mais baratos, enquanto as camisas geralmente são mais caras. Também podemos ver que os produtos do Barcelona estão em todos os quatro grupos de vendas, o que sugere que essa equipe tem uma oferta em todas as faixas de preço.

Exemplo nº 3: Vendas mensais para cada categoria e equipe

Na próxima consulta, criaremos um relatório sobre vendas mensais. O departamento de marketing deseja dividir as vendas mensais de cada categoria de produto e equipe em quatro grupos. O primeiro grupo terá as categorias de produtos, os times de futebol e os meses com o menor total de vendas. O próximo grupo terá o próximo nível de total_sales, e assim por diante. Dessa forma, os profissionais de marketing poderão analisar a distribuição das vendas em diferentes meses e categorias. Abaixo está a consulta:

WITH monthly_sales_stats AS (
   SELECT
        EXTRACT(MONTH FROM sale_date) as month,
        product_category,
        soccer_team,
        SUM(amount) AS total_sales
   FROM sales
   WHERE sale_date >= '2023-01-01' and sale_date <= '2023-12-31'
   GROUP BY EXTRACT(MONTH FROM sale_date), product_category, soccer_team
)
SELECT
     NTILE(4) OVER ( ORDER BY total_sales ) AS group,
     month,
     product_category,
     soccer_team,
     total_sales
FROM monthly_sales_stats
ORDER BY group DESC, total_sales DESC;

A consulta tem uma expressão de tabela comum (CTE) chamada monthly_sales_stats, que calcula o total de vendas para cada combinação de mês, product_category e soccer_team.

Observe que filtramos as linhas do ano de 2023 na cláusula WHERE. A expressão EXTRACT(MONTH FROM sale_date) é usada para obter o valor do mês no campo sale_date.

Na consulta principal, chamamos NTILE(4) OVER (ORDER BY total_sales) para atribuir a cada linha do CTE um número de grupo. Abaixo está um resultado parcial da consulta que mostra as três primeiras linhas de cada grupo:

No resultado, é possível ver que as maiores vendas de cada time de futebol ocorrem em dezembro, provavelmente por causa das compras de Natal. A categoria "Accessory" (Acessório) está no final da tabela de resultados, porque os acessórios geralmente têm preços mais baixos.

Exemplo nº 4: Vendas mais baixas e mais altas dos times

Como outras funções de janela, você pode usar NTILE() com a cláusula PARTITION BY. Aqui está um exemplo.

A equipe de marketing quer investigar como as vendas são distribuídas nos artigos de cada time de futebol. A ideia é dividir as vendas de cada equipe em conjuntos com base no valor. Mais uma vez, colocaremos as vendas mais baixas no primeiro conjunto e, em seguida, trabalharemos até as vendas mais altas no quarto conjunto.

Essa é a consulta:

SELECT	soccer_team,
      	NTILE(4) OVER (PARTITION BY soccer_team ORDER BY amount) AS group_number,
product_id, 
product_category,
amount 
FROM sales ; 

A consulta é muito simples; ela tem um SELECT com uma lista de colunas e um FROM com um nome de tabela. A função NTILE() usa PARTITION BY e ORDER BY. O PARTITION BY soccer_team coloca todas as linhas com o mesmo valor em soccer_team na mesma partição. O valor ORDER BY ordena os registros no conjunto de cada equipe, colocando os que têm os valores mais baixos primeiro. Em seguida, NTILE(4) retorna 1 para os primeiros 25% das linhas no grupo de linhas, 2 para os segundos 25% das linhas no grupo, e assim por diante.

Abaixo estão alguns resultados parciais:

Exemplo nº 5: Comportamento das vendas de Natal por time de futebol

Esse exemplo é muito semelhante ao Exemplo 3, com a diferença de que a função NTILE() usa uma subcláusula PARTITION BY soccer_team. Isso significa que NTILE() criará grupos de linhas para cada soccer_team em vez de criar grupos a partir do conjunto completo de resultados (como no Exemplo 3). Como resultado, cada time de futebol terá quatro conjuntos.

A consulta é:

WITH monthly_sales_stats AS (
       SELECT
            EXTRACT(MONTH FROM sale_date) as month,
            product_category,
            soccer_team,
            SUM(amount) AS total_sales
      FROM sales
      WHERE sale_date >= '2023-01-01' and sale_date <= '2023-12-31'
      GROUP BY EXTRACT(MONTH FROM sale_date), product_category, soccer_team
)
  SELECT
     NTILE(4) OVER(PARTITION BY soccer_team ORDER BY total_sales DESC) AS group,
     month,
     product_category,
     soccer_team,
     total_sales
  FROM monthly_sales_stats
  ORDER BY total_sales DESC;

O CTE monthly_sales_stats é exatamente igual ao do exemplo anterior. Ele tem uma cláusula GROUP BY que ajuda a calcular o valor total das vendas para cada combinação de mês, product_category e soccer_team.

Depois de criar o CTE, escrevemos um SELECT com a seguinte expressão NTILE():

NTILE(4) OVER(PARTITION BY soccer_team ORDER BY total_sales DESC) AS group

Essa expressão divide as linhas do CTE em conjuntos que têm o mesmo valor no campo soccer_team. Para cada conjunto de times de futebol, NTILE(4) tenta criar quatro subconjuntos iguais, atribuindo a cada linha um valor de 1 a 4.

O restante das colunas no resultado vem do CTE. Abaixo estão os resultados parciais que mostram duas linhas para cada grupo dos times do Barcelona e do Manchester.

Exemplo nº 6: Obter grupos de clientes distribuídos uniformemente

Vamos supor que o departamento de marketing queira criar três grupos de clientes distribuídos uniformemente para executar três campanhas de marketing diferentes; cada campanha será direcionada a um grupo. Os clientes de cada grupo são escolhidos aleatoriamente. Em seguida, o departamento de marketing comparará os resultados da campanha e avaliará qual campanha é melhor.

Para simular uma seleção aleatória de clientes, uma ideia é usar os segundos da última vez que cada cliente comprou algo. E, ordenados pelos segundos, criaremos três grupos de clientes. Vamos ver a consulta:

WITH customer_last_transaction_timestamp AS (
  SELECT customer_id, 
       max(sales_date) AS last_ts
  FROM   sales
  GROUP BY customer_id
)
SELECT NTILE(3) OVER (ORDER BY EXTRACT(SECOND FROM last_ts)) AS group_number, 
       customer_id 
FROM   customer_last_transaction_timestamp 
ORDER BY group_number;

A consulta anterior retorna todos os clientes com um group_number de 1 a 3; isso representa o grupo de marketing ao qual o cliente foi atribuído. O CTE customer_last_transaction_timestamp armazena cada cliente com o registro de data e hora da última transação (obtido com MAX(sales_date)).

A consulta principal usa a função NTILE(3) para criar três grupos de clientes com aproximadamente o mesmo tamanho:

NTILE(3) OVER (ORDER BY EXTRACT(SECOND FROM last_ts))

A instrução acima retorna 1 para 33% dos clientes com um valor de registro de data e hora no terço inferior. Ela retorna 2 para os próximos 33%, e assim por diante. A subexpressão EXTRACT(SECOND FROM last_ts) pega a parte dos segundos (ou seja, 22) de um registro de data e hora (ou seja, '2023-03-30 10:30:22'). Abaixo está um resultado parcial:

Continue aprendendo sobre NTILE() e outros SQL Funções de Janela em SQL

Neste artigo, mostramos vários exemplos de uso da função de janela NTILE(). Também demonstramos diferentes cláusulas OVER. A cláusula OVER é comum a todas as funções de janela do SQL. Se você quiser ter alguma experiência prática com esses comandos, sugiro nosso curso interativo Funções de Janela em SQL curso interativo.

Se você for um usuário frequente de funções de janela, nossa Folha de dicas SQL Funções de Janela em SQL gratuita é um recurso muito útil. Na verdade, eu a tenho na parede do meu escritório, pronta para ser usada quando tenho dúvidas sobre a sintaxe. Eu o recomendo fortemente.

Por fim, o artigo Top 10 Funções da Janela SQL Perguntas para Entrevistas é excelente se você tiver uma entrevista de emprego e quiser estar preparado para tópicos de SQL.

Bom aprendizado e continue progredindo com as funções de janela do SQL!