Uso de funções de janela¶

Funções de janela relacionadas à classificação¶

A subcategoria mais simples de funções que diferenciam a ordem é a subcategoria de funções relacionadas à classificação.

A função mais simples relacionada à classificação é a função RANK. A função de classificação simplesmente lista a classificação, que é a ordem de uma linha em uma janela ordenada de linhas. A primeira linha na janela tem posição 1; a segunda linha na janela tem posição 2; etc. O exemplo abaixo mostra a ordem de classificação dos vendedores com base na quantidade vendida:

+-------------+-------------+------+
| Salesperson | Amount Sold | Rank |
|-------------+-------------+------|
| Smith       |        2000 |    1 |
| Jones       |        1500 |    2 |
| Torkelson   |        1200 |    3 |
| Dolenz      |        1100 |    4 |
+-------------+-------------+------+

Copy

A função RANK não classifica as linhas; as linhas já devem estar em ordem na janela antes que RANK seja chamada. Para colocar as linhas em ordem, use a subcláusula ORDER BY da cláusula OVER.

Para ilustrar a subcláusula ORDER BY, considere o seguinte exemplo: você gostaria de saber onde o lucro de sua loja está entre as filiais da rede de lojas, ou seja, se sua loja está em primeiro, segundo, terceiro, etc. Aqui está um exemplo de utilização de uma função de janela que diferencia a ordem para classificar cada loja por rentabilidade dentro de sua cidade. As linhas são colocadas em ordem decrescente (lucro mais alto primeiro), de modo que a loja mais lucrativa tem classificação 1:

SELECT city, branch_ID, net_profit,
       RANK() OVER (PARTITION BY city ORDER BY net_profit DESC) AS rank
    FROM store_sales
    ORDER BY city, rank;
+-----------+-----------+------------+------+
| CITY      | BRANCH_ID | NET_PROFIT | RANK |
|-----------+-----------+------------+------|
| Montreal  |         3 |   10000.00 |    1 |
| Montreal  |         4 |    9000.00 |    2 |
| Vancouver |         2 |   15000.00 |    1 |
| Vancouver |         1 |   10000.00 |    2 |
+-----------+-----------+------------+------+

Copy

A saída de uma função relacionada à classificação depende de:

• A linha individual passada à função.

• Os valores das outras linhas na janela passada para a função.

• A ordem de todas as linhas na janela.

O Snowflake oferece várias funções diferentes relacionadas à classificação. Os nomes destas funções, e mais detalhes sobre a sintaxe das funções de janela relacionadas à classificação, estão em Funções de janela.

Funções de quadro de janela¶

Um quadro de janela é um subconjunto de linhas em uma janela.

Uma função de quadro de janela utiliza um quadro de janela para calcular informações como uma média móvel.

O Snowflake oferece suporte a dois tipos de quadros de janela:

• Cumulativa.

• Deslizante.

Quadros de janela deslizantes¶

Você pode imaginar um quadro de janela deslizante pensando no que você vê ao olhar para fora da janela lateral de um carro enquanto o carro avança paralelamente a uma cerca. Se as colunas da cerca forem espaçadas uniformemente, e se a largura da janela for um múltiplo inteiro da distância entre as colunas, então o número de colunas da cerca que você vê permanece constante; no entanto, enquanto você dirige, as “antigas” colunas saem de seu campo de visão, e as “novas” entram nele, de modo que você não vê as mesmas colunas da cerca ao longo do tempo, mesmo que você consulte o mesmo número de colunas o tempo todo.

Um quadro de janela deslizante é um quadro de largura fixa que “desliza” ao longo das linhas na janela, mostrando uma fatia diferente da janela a cada vez. Como no carro que passa pelas colunas da cerca, o quadro de janela se move ao longo dos dados, com linhas antigas desaparecendo do quadro e novas linhas aparecendo, de modo que a largura do quadro (o número de linhas no quadro) é sempre a mesma.

As janelas deslizantes são frequentemente usadas para calcular médias móveis. Uma média móvel é uma média calculada com base em um intervalo de tamanho fixo (por exemplo, número de dias). A média está “em movimento” porque embora o tamanho do intervalo seja constante, os valores reais no intervalo mudam ao longo do tempo (ou devido a algum outro fator) conforme a janela desliza.

Por exemplo, analistas do mercado de ações frequentemente analisam as ações com base, em parte, na média móvel do preço de uma ação em 13 semanas. O preço médio móvel hoje é a média de preço no final de hoje e o preço no final de cada dia durante as últimas 13 semanas. Se as ações forem negociadas 5 dias por semana, e se não houver feriados nas últimas 13 semanas, então a média móvel é o preço médio em cada um dos últimos 65 dias de negociação (inclusive hoje).

O exemplo a seguir mostra o que acontece com a média móvel em 13 semanas (91 dias) do preço de uma ação no último dia de junho e nos primeiros dias de julho:

• Em 30 de junho, a função retorna o preço médio para 1º de abril a 30 de junho (inclusive).

• Em 1º de julho, a função retorna o preço médio para 2 de abril a 1º de julho (inclusive).

• Em 2 de julho, a função retorna o preço médio para 3 de abril a 2 de julho (inclusive).

• etc.

Nota

Embora as janelas deslizantes sejam de largura fixa, quando uma janela é aplicada pela primeira vez a uma nova fonte de dados, a fonte de dados pode não ter dados suficientes para preencher a janela. Por exemplo, se a manutenção de registros começou em 1º de abril, então de 1º de abril a 29 de junho a janela deslizante incluiria menos de 91 dias de dados. A largura da janela permanece constante somente depois que a janela tiver terminado o preenchimento.

O exemplo abaixo usa uma pequena janela deslizante (3 dias) para os primeiros 7 dias do mês. Ele demonstra como o quadro desliza pela janela, mantendo sempre os 3 valores mais recentes para o cálculo do total dentro da janela. Esta ilustração leva em conta que no início do período, a janela pode não estar cheia:

E, como você pode ver na tabela correspondente, a última coluna contém a soma dos dados de vendas dos três dias mais recentes. Por exemplo, o valor da coluna para o dia 4 é 36, que é a soma das vendas para dias 2, 3 e 4 (11 + 12 + 13):

+--------+-------+---------------+
| Day of | Sales | Most Recent   |
| Month  | Today | 3 Days' Sales |
|--------+-------+---------------+
|      1 |    10 |            10 |
|      2 |    11 |            21 |
|      3 |    12 |            33 |
|      4 |    13 |            36 |
|      5 |    14 |            39 |
|    ... |   ... |           ... |
+--------+-------+---------------+

Copy

Mais informações sobre funções de janela que diferenciam a ordem¶

As cláusulas PARTITION BY e ORDER BY são independentes. Você pode usar a cláusula ORDER BY sem a cláusula PARTITION BY. Para classificar sua loja em relação a todas as outras lojas da rede, e não apenas quanto a outras lojas de sua cidade, use a consulta abaixo:

SELECT
    branch_ID,
    net_profit,
    RANK() OVER (ORDER BY net_profit DESC) AS sales_rank
  FROM store_sales

SELECT
    branch_ID,
    net_profit,
    RANK() OVER (ORDER BY net_profit DESC) AS sales_rank
  FROM store_sales
  ORDER BY branch_ID;

Algumas funções de janela diferenciam a ordem, e outras não.

• Algumas funções de janela requerem uma cláusula ORDER BY.

• Algumas funções de janela proíbem uma cláusula ORDER BY.

• Algumas funções de janela utilizam uma cláusula ORDER BY se ela estiver presente, mas não a exigem.

• Algumas funções de janela tratam uma cláusula ORDER BY como uma cláusula de quadro de janela cumulativo implícito. (Mais informações sobre quadros de janelas implícitos estão em Notas de uso dos quadros de janela).

O diagrama a seguir mostra a relação entre funções de janela, funções de quadro de janela e funções relacionadas à classificação:

                      Window Functions

  Order-insensitive Functions      Order-sensitive Functions
+-----------------------------+--------------------------------------------------------------------+
|                             | Rank-related Functions                                             |
|                             +--------------------------------------------------------------------+
|                             | Functions that can be both Rank-related and Window Frame functions |
|                             +--------------------------------------------------------------------+
|                             | Window Frame Functions                                             |
+-----------------------------+--------------------------------------------------------------------+

Como mostrado acima:

• Algumas funções de janela não diferenciam a ordem.

• Algumas funções de janela diferenciam a ordem.

• As funções de janela que diferenciam a ordem estão divididas em duas categorias:

  • Funções relacionadas à classificação.

  • Funções de quadro de janela.

• Algumas funções que diferenciam a ordem são tanto funções relacionadas à classificação como funções de quadro de janela.

Funções de janela vs funções agregadas¶

Muitas funções de janela e funções agregadas têm o mesmo nome. Por exemplo, existe uma função de janela SUM() e uma função agregada SUM().

Para distinguir entre o uso das duas, lembre-se:

• Para uma função agregada, a entrada é de múltiplas linhas, e a saída é 1 linha.

• Para uma função de janela, há duas entradas, uma janela de linhas e uma única linha dentro daquela janela, e a saída é 1 linha por linha da entrada.

Uma função agregada lhe diz algo, como SUM, sobre todas as linhas combinadas, mas nada sobre as linhas individuais.

Uma função de janela lhe diz algo sobre a linha atual em relação a todas as outras linhas da janela.

As seguintes instruções SQL mostram a diferença entre o uso da função agregada SUM(), que retorna 1 linha para todo o grupo de linhas de entrada, e o uso da função de janela SUM(), que retorna 1 linha para cada linha do grupo de linhas de entrada:

SELECT SUM(net_profit) 
    FROM store_sales;
+-----------------+
| SUM(NET_PROFIT) |
|-----------------|
|        44000.00 |
+-----------------+
SELECT branch_ID, SUM(net_profit) OVER ()
    FROM store_sales
    ORDER BY branch_id;
+-----------+-------------------------+
| BRANCH_ID | SUM(NET_PROFIT) OVER () |
|-----------+-------------------------|
|         1 |                44000.00 |
|         2 |                44000.00 |
|         3 |                44000.00 |
|         4 |                44000.00 |
+-----------+-------------------------+

Copy

Cálculo da classificação de vendas¶

A coluna Rank é calculada usando a função RANK:

SELECT day, 
       sales_today, 
       RANK()
           OVER (ORDER BY sales_today DESC) AS Rank
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------+
| DAY | SALES_TODAY | RANK |
|-----+-------------+------|
|   1 |          10 |    5 |
|   2 |          14 |    3 |
|   3 |           6 |    6 |
|   4 |           6 |    6 |
|   5 |          14 |    3 |
|   6 |          16 |    2 |
|   7 |          18 |    1 |
+-----+-------------+------+

Copy

Observe que embora existam 7 dias no período de tempo, existem apenas 5 classificações diferentes (1, 2, 3, 5, 6). Houve dois empates (para 3º lugar e 6º lugar), portanto não há linhas com linhas 4 ou 7.

Cálculo das vendas até agora nesta semana¶

A coluna Sales So Far This Week é calculada usando SUM como uma função de janela com um quadro de janela cumulativa:

SELECT day, 
       sales_today, 
       SUM(sales_today)
           OVER (ORDER BY day
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS "SALES SO FAR THIS WEEK"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------------------------+
| DAY | SALES_TODAY | SALES SO FAR THIS WEEK |
|-----+-------------+------------------------|
|   1 |          10 |                     10 |
|   2 |          14 |                     24 |
|   3 |           6 |                     30 |
|   4 |           6 |                     36 |
|   5 |          14 |                     50 |
|   6 |          16 |                     66 |
|   7 |          18 |                     84 |
+-----+-------------+------------------------+

Copy

Esta consulta ordena as linhas por data e depois, para cada data, calcula a soma das vendas desde o início da janela até a data atual (inclusive).

Cálculo do total de vendas desta semana¶

A coluna Total Sales This Week é calculada usando SUM como uma simples função de janela.

SELECT day, 
       sales_today, 
       SUM(sales_today)
           OVER ()
               AS total_sales
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+-------------+
| DAY | SALES_TODAY | TOTAL_SALES |
|-----+-------------+-------------|
|   1 |          10 |          84 |
|   2 |          14 |          84 |
|   3 |           6 |          84 |
|   4 |           6 |          84 |
|   5 |          14 |          84 |
|   6 |          16 |          84 |
|   7 |          18 |          84 |
+-----+-------------+-------------+

Copy

Cálculo de uma média móvel de 3 dias¶

A coluna 3-Day Moving Average é calculada usando AVG como uma função de janela com um quadro de janela deslizante:

SELECT day, 
       sales_today, 
       AVG(sales_today)
           OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
               AS "3-DAY MOVING AVERAGE"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+----------------------+
| DAY | SALES_TODAY | 3-DAY MOVING AVERAGE |
|-----+-------------+----------------------|
|   1 |          10 |               10.000 |
|   2 |          14 |               12.000 |
|   3 |           6 |               10.000 |
|   4 |           6 |                8.666 |
|   5 |          14 |                8.666 |
|   6 |          16 |               12.000 |
|   7 |          18 |               16.000 |
+-----+-------------+----------------------+

Copy

A diferença entre este quadro de janela deslizante e o quadro de janela cumulativa descrito anteriormente é simplesmente o ponto de partida:

• Em um quadro de janela deslizante, o ponto de partida desliza para a direita.

• Em um quadro de janela cumulativa, o ponto de partida é fixo e o quadro continua a se acumular com cada linha adicional dentro da janela.

O resultado¶

Aqui está a versão final de nossa consulta, mostrando:

• Classificação.

• Quadro de janela cumulativo.

• Função de janela simples.

• Função de janela deslizante.

  SELECT day, 
         sales_today, 
         RANK()
             OVER (ORDER BY sales_today DESC) AS Rank,
         SUM(sales_today)
             OVER (ORDER BY day
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                 AS "SALES SO FAR THIS WEEK",
         SUM(sales_today)
             OVER ()
                 AS total_sales,
         AVG(sales_today)
             OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
                 AS "3-DAY MOVING AVERAGE"
      FROM store_sales_2
      ORDER BY day;
  +-----+-------------+------+------------------------+-------------+----------------------+
  | DAY | SALES_TODAY | RANK | SALES SO FAR THIS WEEK | TOTAL_SALES | 3-DAY MOVING AVERAGE |
  |-----+-------------+------+------------------------+-------------+----------------------|
  |   1 |          10 |    5 |                     10 |          84 |               10.000 |
  |   2 |          14 |    3 |                     24 |          84 |               12.000 |
  |   3 |           6 |    6 |                     30 |          84 |               10.000 |
  |   4 |           6 |    6 |                     36 |          84 |                8.666 |
  |   5 |          14 |    3 |                     50 |          84 |                8.666 |
  |   6 |          16 |    2 |                     66 |          84 |               12.000 |
  |   7 |          18 |    1 |                     84 |          84 |               16.000 |
  +-----+-------------+------+------------------------+-------------+----------------------+
  

  Copy

Janela com cláusula PARTITION BY¶

A subcláusula PARTITION BY da cláusula de janela divide os dados em subconjuntos distintos com base no valor da expressão de entrada; a instrução SELECT é aplicada a cada subconjunto, e a saída tem linhas para cada subconjunto.

Note que isto é similar, mas não idêntico, a como a cláusula GROUP BY funciona.

O exemplo seguinte mostra a quantidade vendida a cada mês, e usa a cláusula PARTITION BY para dividir os dados em subconjuntos de um mês:

SELECT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date)) AS QUANTITY
    FROM sales
    ORDER BY sales_date;
+-----------+----------+
| MONTH_NUM | QUANTITY |
|-----------+----------|
|         1 |        9 |
|         1 |        9 |
|         1 |        9 |
|         2 |        2 |
+-----------+----------+

Copy

Como você pode ver, as três primeiras linhas são duplicatas. Havia 3 linhas de entrada para o mês 1, e a função de janela gera uma linha de saída para cada linha de entrada, então há 3 linhas de saída para o mês 1 na saída. A função SUM não é usada como uma função cumulativa ou deslizante; ela é aplicada à janela inteira e retorna o mesmo valor para a janela inteira a cada vez; portanto a função retorna valores duplicados como mostrado acima.

Você pode reduzir as duplicatas usando a palavra-chave DISTINCT:

SELECT DISTINCT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date))
    FROM sales
    ORDER BY MONTH_NUM;
+-----------+-----------------------------------------------------+
| MONTH_NUM | SUM(QUANTITY) OVER (PARTITION BY MONTH(SALES_DATE)) |
|-----------+-----------------------------------------------------|
|         1 |                                                   9 |
|         2 |                                                   2 |
+-----------+-----------------------------------------------------+

Copy

Neste caso particular, você pode usar uma cláusula GROUP BY em vez de uma cláusula de janela. Por exemplo:

SELECT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity)
    FROM sales
    GROUP BY MONTH_NUM
    ORDER BY MONTH_NUM;
+-----------+---------------+
| MONTH_NUM | SUM(QUANTITY) |
|-----------+---------------|
|         1 |             9 |
|         2 |             2 |
+-----------+---------------+

Copy

Entretanto, GROUP BY não é tão flexível quanto janelas. Além disso, em consultas mais complexas, nem sempre você pode substituir GROUP BY por uma cláusula de janela.

Janela com cláusula ORDER BY¶

A cláusula ORDER BY controla a ordem dos dados dentro de cada janela (e cada partição se houver mais de uma partição). Isto é útil se você quiser mostrar uma “soma corrente” ao longo do tempo à medida que novas linhas são adicionadas.

Uma soma corrente pode ser calculada desde o início da janela até a linha atual (inclusive) ou desde a linha atual até o final da janela.

Uma consulta pode usar uma janela “deslizante”, que é uma janela de largura fixa que processa N linhas especificadas em relação à linha atual (por exemplo, as 10 linhas mais recentes, incluindo a linha atual).

Tanto as janelas cumulativas quanto as deslizantes são explicadas abaixo.

Janela com cláusulas PARTITION BY e ORDER BY¶

Você pode combinar as cláusulas PARTITION BY e ORDER BY para obter somas correntes dentro das partições. Neste exemplo, as partições são de um mês, e como as somas se aplicam somente dentro de uma partição, a soma é redefinida como 0 no início de cada novo mês:

SELECT MONTH(sales_date) AS MONTH_NUM,
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date) ORDER BY sales_date)
          AS MONTHLY_CUMULATIVE_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;

Copy

O resultado da consulta inclui comentários adicionais mostrando como a coluna MONTHLY_CUMULATIVE_SUM_QUANTITY foi calculada:

+-----------+---------------------------------+
| MONTH_NUM | MONTHLY_CUMULATIVE_SUM_QUANTITY |
|-----------+---------------------------------+
|         1 |                               1 |  -- sum = 1
|         1 |                               4 |  -- sum = 1 + 3
|         1 |                               9 |  -- sum = 1 + 3 + 5
|         2 |                               2 |  -- sum = 0 + 2 (new month)
+-----------+---------------------------------+

Copy

Você pode combinar partições e janelas deslizantes. No exemplo abaixo, a janela deslizante normalmente tem duas linhas de largura, mas cada vez que uma nova partição (ou seja, um novo mês) é alcançada, a janela deslizante começa com apenas a primeira linha naquela partição:

SELECT
       MONTH(sales_date) AS MONTH_NUM,
       quantity,
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date) 
                           ORDER BY sales_date
                           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
         AS MONTHLY_SLIDING_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;

Copy

O resultado da consulta inclui comentários adicionais mostrando como a coluna MONTHLY_SLIDING_SUM_QUANTITY foi calculada:

+-----------+----------+------------------------------+
| MONTH_NUM | QUANTITY | MONTHLY_SLIDING_SUM_QUANTITY |
|-----------+----------+------------------------------+
|         1 |        1 |                           1  |  -- sum = 1
|         1 |        3 |                           4  |  -- sum = 1 + 3
|         1 |        5 |                           8  |  -- sum = 3 + 5
|         2 |        2 |                           2  |  -- sum = 0 + 2 (new month)
+-----------+----------+------------------------------+

Copy