Uso de funções de janela

As funções de janela operam em janelas, que são grupos de linhas que estão relacionadas (por exemplo, por data ou local). Este tópico descreve como usar os diferentes tipos de funções de janela aceitos pelo Snowflake, inclusive:

  • Funções gerais de janela.

  • Funções de janela que calculam a classificação (por exemplo, mais alta, segunda mais alta, etc.).

  • Funções de janela com suporte para quadros de janelas cumulativas e quadros de janelas deslizantes.

Este documento é destinado aos leitores que ainda não são fluentes em funções de janela. Os leitores que já são fluentes nessas funções podem achar o material de referência suficiente:

Neste tópico:

Conceitos de janela

Uma janela é um grupo de linhas. Uma janela pode conter 0, 1, ou várias linhas. Entretanto, para simplificar, normalmente dizemos apenas que uma janela contém “várias linhas”.

Todas as linhas em uma janela estão relacionadas de alguma forma, por exemplo, por localização (por exemplo, todas da mesma cidade) ou por tempo (por exemplo, todas do mesmo ano fiscal).

Uma função que utiliza uma janela é uma função de janela.

Funções categorizadas como funções de janela ajudam a responder tipos de perguntas diferentes das funções escalares:

  • Uma consulta usando uma função escalar responde a perguntas sobre uma única linha, usando apenas os dados daquela linha.

  • Uma consulta usando uma função de janela responde perguntas sobre a relação de uma linha com outras linhas na mesma janela.

Por exemplo, suponha que você administre uma filial de uma rede de cinco lojas. Para calcular o lucro de sua loja, o cálculo precisa olhar apenas as informações sobre sua loja específica, tais como a receita e os custos da loja. Você usaria uma função escalar para essa consulta.

Para calcular o lucro de sua loja relativa a outras lojas, o cálculo deve olhar as informações não só sobre sua loja, mas também sobre outras lojas. Você usaria uma função de janela para essa consulta.

Pode-se pensar em uma função de janela como tomando dois argumentos: o primeiro argumento é a coluna ou expressão a ser usada no cálculo, por exemplo, receita ou lucro. O segundo argumento define a janela (ou seja, o grupo de linhas utilizadas na comparação); a janela inclui tanto a linha atual (sua loja) quanto as outras linhas com as quais comparar (outras lojas da mesma cadeia).

Para calcular a porcentagem do lucro de sua loja em toda a cadeia de lojas, você divide o lucro de sua loja pelo lucro total de todas as lojas (lucro da filial/lucro da cadeia).

Para ajudar a explicar as funções de janela, este tópico mostra como calcular a porcentagem dos lucros de sua filial, com e sem uma função de janela.

O exemplo a seguir mostra uma maneira de calcular a porcentagem de lucro de sua loja sem uma função de janela.

Comece por criar a tabela, carregar os dados e calcular o lucro de cada loja.

CREATE TRANSIENT TABLE store_sales (
    branch_ID    INTEGER,
    city        VARCHAR,
    gross_sales NUMERIC(9, 2),
    gross_costs NUMERIC(9, 2),
    net_profit  NUMERIC(9, 2)
    );

INSERT INTO store_sales (branch_ID, city, gross_sales, gross_costs)
    VALUES
    (1, 'Vancouver', 110000, 100000),
    (2, 'Vancouver', 140000, 125000),
    (3, 'Montreal', 150000, 140000),
    (4, 'Montreal', 155000, 146000);

UPDATE store_sales SET net_profit = gross_sales - gross_costs;
Copy

Agora mostre a porcentagem de cada loja no lucro total da cadeia:

SELECT branch_ID,
       net_profit AS store_profit,
       (SELECT SUM(net_profit) FROM store_sales) AS chain_profit,
       store_profit / chain_profit * 100 AS store_percentage_of_chain_profit
     FROM store_sales
     ORDER BY branch_ID;
+-----------+--------------+--------------+----------------------------------+
| BRANCH_ID | STORE_PROFIT | CHAIN_PROFIT | STORE_PERCENTAGE_OF_CHAIN_PROFIT |
|-----------+--------------+--------------+----------------------------------|
|         1 |     10000.00 |     44000.00 |                      22.72727300 |
|         2 |     15000.00 |     44000.00 |                      34.09090900 |
|         3 |     10000.00 |     44000.00 |                      22.72727300 |
|         4 |      9000.00 |     44000.00 |                      20.45454500 |
+-----------+--------------+--------------+----------------------------------+
Copy

Se você quiser um relatório mais detalhado, mostrando o lucro de cada loja como uma porcentagem de todas as lojas daquela cidade, use a consulta abaixo:

SELECT branch_ID,
       net_profit AS store_profit,
       (SELECT SUM(net_profit) FROM store_sales AS s2 WHERE s2.city = s1.city) AS city_profit,
       store_profit / city_profit * 100 AS store_percentage_of_city_profit
    FROM store_sales AS s1
    ORDER BY branch_ID;
+-----------+--------------+-------------+---------------------------------+
| BRANCH_ID | STORE_PROFIT | CITY_PROFIT | STORE_PERCENTAGE_OF_CITY_PROFIT |
|-----------+--------------+-------------+---------------------------------|
|         1 |     10000.00 |    25000.00 |                     40.00000000 |
|         2 |     15000.00 |    25000.00 |                     60.00000000 |
|         3 |     10000.00 |    19000.00 |                     52.63157900 |
|         4 |      9000.00 |    19000.00 |                     47.36842100 |
+-----------+--------------+-------------+---------------------------------+
Copy

Seria bom ter uma função que fizesse aproximadamente a mesma coisa, dividindo o lucro de sua loja pela soma do lucro de todas as lojas (ou pela soma do lucro de um grupo específico de lojas, por exemplo, todas as lojas de uma mesma cidade). Tal função pode usar dois argumentos, um dos quais é a coluna para fazer o cálculo e o segundo especifica com que linhas comparar. A segunda coluna pode ser um pouco como uma cláusula WHERE. Você pode usar essa função de forma similar à seguinte (este é um pseudocódigo; ele não é um SQL válido):

SELECT branch_ID,
       PERCENTAGE(net_profit, <where_condition>)
    FROM store_sales;
Copy

Esta função divide o lucro da linha atual (da loja atual) pela soma dos lucros de todas as lojas que atendem a <where_condition>.

Por exemplo, para calcular a porcentagem de lucro para cada loja em cada cidade, o pseudocódigo seria algo como:

SELECT branch_ID,
       PERCENTAGE(net_profit, 'city')
     FROM store_sales;
Copy

SQL não é compatível com a sintaxe mostrada acima, mas sim com o conceito de uma função de janela, que retorna um resultado baseado tanto na linha atual quanto em um grupo definido de linhas.

O Snowflake não tem uma função chamada PERCENTAGE, mas tem uma função chamada RATIO_TO_REPORT, que divide o valor da linha atual pela soma dos valores em todas as linhas de uma janela. Aqui está o equivalente da consulta anterior.

SELECT branch_ID,
       city,
       100 * RATIO_TO_REPORT(net_profit) OVER (PARTITION BY city)
    FROM store_sales AS s1
    ORDER BY city, branch_ID;
+-----------+-----------+------------------------------------------------------------+
| BRANCH_ID | CITY      | 100 * RATIO_TO_REPORT(NET_PROFIT) OVER (PARTITION BY CITY) |
|-----------+-----------+------------------------------------------------------------|
|         3 | Montreal  |                                                52.63157900 |
|         4 | Montreal  |                                                47.36842100 |
|         1 | Vancouver |                                                40.00000000 |
|         2 | Vancouver |                                                60.00000000 |
+-----------+-----------+------------------------------------------------------------+
Copy

A cláusula OVER() define o grupo de linhas utilizadas no cálculo. Ela desempenha a mesma função que o hipotético segundo argumento (<where_condition>) em nossa função PERCENTAGE anterior.

A subcláusula PARTITION BY nos permite dividir essa janela em subjanelas; neste caso, uma por cidade. (Se a instrução não usar explicitamente uma cláusula PARTITION BY, então a função de janela opera em toda a entrada como uma única janela).

Se você quiser ver a porcentagem de lucro relativa a toda a cadeia, em vez de apenas a lojas dentro de uma cidade específica, então omita a cláusula PARTITION BY:

SELECT branch_ID,
       100 * RATIO_TO_REPORT(net_profit) OVER ()
    FROM store_sales AS s1
    ORDER BY branch_ID;
+-----------+-------------------------------------------+
| BRANCH_ID | 100 * RATIO_TO_REPORT(NET_PROFIT) OVER () |
|-----------+-------------------------------------------|
|         1 |                               22.72727300 |
|         2 |                               34.09090900 |
|         3 |                               22.72727300 |
|         4 |                               20.45454500 |
+-----------+-------------------------------------------+
Copy

Funções de janela que diferenciam a ordem

O resultado da consulta anterior não depende da ordem das linhas selecionadas pela subcláusula PARTITION BY. A porcentagem dos lucros corporativos de sua loja não depende da ordem na qual a consulta lê os dados das outras lojas.

Algumas consultas, no entanto, diferenciam a ordem. Há dois tipos de funções de janela que diferenciam a ordem:

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

  • Funções de quadro de janela.

Algumas funções são tanto funções relacionadas à classificação como funções de quadro de janela.

Funções que diferenciam a ordem utilizam a subcláusula ORDER BY da cláusula OVER, por exemplo:

select order_sensitive_function(column_name) over ( [partition by <col2>] order by <col3> )
...
Copy

A cláusula ORDER BY pode ser usada com ASC (crescente) ou DESC (decrescente). O padrão é crescente.

(A subcláusula ORDER BY da cláusula OVER é separada da cláusula ORDER BY que ordena a saída final de uma consulta).

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 cumulativa

Uma janela cumulativa é uma janela de largura variável que começa em um ponto fixo e cresce a cada nova linha. Por exemplo, “Vendas até agora este mês” é calculada usando uma janela cumulativa que começa no primeiro dia do mês e cresce à medida que os dados de cada novo dia são adicionados:

+--------+-------+--------------+
| Day of | Sales | Sales So Far |
| Month  | Today | This Month   |
|--------+-------+--------------+
|      1 |    10 |           10 |
|      2 |    10 |           20 |
|      3 |    10 |           30 |
|    ... |   ... |          ... |
+--------+-------+--------------+
Copy

Muitas vezes, uma janela cumulativa reinicia a partir de 0 a intervalos regulares. Por exemplo, se a tabela acima mostrasse as vendas para fevereiro, então em 1 de março, o ponto de partida do quadro da janela se tornaria 1 de março, e as vendas até o momento para o mês seriam redefinidas para 0 e começariam a contar a partir de 1 de março.

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:

Estrutura de janela deslizante de 3 dias em uma janela de 7 dias

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
Copy

Nota

Uma subcláusula ORDER BY dentro de uma cláusula OVER controla apenas a ordem na qual a função de janela processa as linhas; a cláusula não controla a saída de toda a consulta. O controle da ordem de saída da consulta ainda requer uma cláusula ORDER BY externa no nível superior da consulta. Estas cláusulas ORDER BY são independentes e não são mutuamente exclusivas. A consulta a seguir usa a primeira cláusula ORDER BY para controlar o processamento pela função de janela e a segunda cláusula ORDER BY para controlar a ordem de toda a saída da consulta:

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

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).

Cuidado

Na maioria das situações, a linguagem SQL é explícita. A linguagem faz o que você diz para ela fazer - nem mais, nem menos. Há muito poucas cláusulas implícitas, ou seja, quando uma cláusula cria implicitamente outra cláusula que não é visível. Para algumas funções de janela, uma cláusula ORDER BY adiciona implicitamente uma cláusula adicional (uma cláusula de quadro de janela cumulativa). Isto muda o comportamento. Mais informações sobre as cláusulas de quadros de janela implícitos estão em Notas de uso dos quadros de janela.

Como o comportamento que é implícito em vez de explícito pode levar a resultados que são difíceis de entender, o Snowflake recomenda evitar quadros de janela implícitos. Em vez disso, explicite todos os 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                                             |
+-----------------------------+--------------------------------------------------------------------+
Copy

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

Chamada de uma função de janela

Esta seção fornece mais informações sobre a sintaxe da chamada de funções de janela.

As janelas e quadros de janelas são especificados usando uma cláusula OVER:

... OVER ( [ PARTITION BY <expr1> ]
           [ ORDER BY <expr2> [ { cumulativeFrame | slidingFrame } ] ]
         ) ...
Copy

A sintaxe do quadro de janela é coberta com mais detalhes em Funções de janela. Este tópico enfoca o subconjunto da sintaxe exigida pelas funções de janela, especificamente:

  • A subcláusula PARTITION BY divide os dados em janelas. Por exemplo, se você quiser calcular as somas correntes das vendas mensais por mais de um mês, pode dividir os dados por mês. Isto permite calcular uma soma corrente de vendas para janeiro, outra soma corrente para fevereiro, etc.

  • A subcláusula ORDER BY é principalmente para funções de janela relacionadas com a classificação e para funções de quadro de janela deslizantes e cumulativas; ela determina a ordem das linhas dentro de cada janela.

Exemplo ilustrado

Este exemplo utiliza um cenário de vendas para ilustrar muitos dos conceitos descritos anteriormente neste tópico.

Suponha que você precise gerar um relatório financeiro que mostre valores baseados nas vendas da última semana:

  • Vendas diárias

  • Classificação dentro da semana (ou seja, vendas mais altas a mais baixas para a semana)

    Isto usa uma função de janela relacionada à classificação (RANK).

  • Vendas até agora esta semana (ou seja, a “soma corrente” para todos os dias desde o início da semana até o dia atual, inclusive)

    Isto usa uma função janela (SUM), com um quadro de janela cumulativa.

  • Total de vendas para a semana

    Isto usa SUM como uma função de janela simples.

  • média móvel de 3 dias (ou seja, a média do dia atual e dos dois dias anteriores)

    Isto usa (AVG) como uma função de janela com um quadro de janela deslizante.

O relatório pode parecer com isto:

+--------+-------+------+--------------+-------------+--------------+
| Day of | Sales | Rank | Sales So Far | Total Sales | 3-Day Moving |
| Week   | Today |      | This Week    | This Week   | Average      |
|--------+-------+------+--------------+-------------|--------------+
|      1 |    10 |    4 |           10 |          84 |         10.0 |
|      2 |    14 |    3 |           24 |          84 |         12.0 |
|      3 |     6 |    5 |           30 |          84 |         10.0 |
|      4 |     6 |    5 |           36 |          84 |          9.0 |
|      5 |    14 |    3 |           50 |          84 |         10.0 |
|      6 |    16 |    2 |           66 |          84 |         11.0 |
|      7 |    18 |    1 |           84 |          84 |         12.0 |
+--------+-------+------+--------------+-------------+--------------+
Copy

O SQL para esta consulta é um tanto complexo. Em vez de mostrá-la como uma única consulta, esta discussão quebra o SQL para as colunas individuais.

Em um cenário do mundo real, você teria anos de dados, então para calcular somas e médias para uma semana específica de dados, você precisaria usar uma janela de uma semana, ou usar um filtro semelhante a:

... WHERE date >= start_of_relevant_week and date <= end_of_relevant_week ...
Copy

Entretanto, para este exemplo, suponha que a tabela contenha apenas os dados da semana mais recente.

CREATE TABLE store_sales_2 (
    day INTEGER,
    sales_today INTEGER
    );
+-------------------------------------------+
| status                                    |
|-------------------------------------------|
| Table STORE_SALES_2 successfully created. |
+-------------------------------------------+
INSERT INTO store_sales_2 (day, sales_today) VALUES
    (1, 10),
    (2, 14),
    (3,  6),
    (4,  6),
    (5, 14),
    (6, 16),
    (7, 18);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       7 |
+-------------------------+
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

Exemplos adicionais

Esta seção fornece exemplos mais detalhados da utilização das subcláusulas de janela em funções de janela e ilustra como estas subcláusulas funcionam em conjunto.

Estes exemplos utilizam a seguinte tabela e dados:

CREATE TABLE sales (sales_date DATE, quantity INTEGER);

INSERT INTO sales (sales_date, quantity) VALUES
    ('2018-01-01', 1),
    ('2018-01-02', 3),
    ('2018-01-03', 5),
    ('2018-02-01', 2)
    ;
Copy

Nota

Muitos destes exemplos usam duas cláusulas ORDER BY, uma para a cláusula da janela e outra para ordenar o resultado da forma mais legível. Para o propósito deste tópico, as referências à cláusula ORDER BY são geralmente referências à cláusula dentro da janela.

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.

Exemplo de quadro de janela cumulativa

Em um quadro de janela “cumulativa”, os valores são computados do início da janela até a linha atual (ou da linha atual até o final da janela):

SELECT MONTH(sales_date) AS MONTH_NUM, 
       quantity, 
       SUM(quantity) OVER (ORDER BY MONTH(sales_date)
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS CUMULATIVE_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;
Copy

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

+-----------+----------+-------------------------+
| MONTH_NUM | QUANTITY | CUMULATIVE_SUM_QUANTITY |
|-----------+----------+-------------------------|
|         1 |        1 |                       1 |  -- sum = 1
|         1 |        3 |                       4 |  -- sum = 1 + 3
|         1 |        5 |                       9 |  -- sum = 1 + 3 + 5
|         2 |        2 |                      11 |  -- sum = 1 + 3 + 5 + 2
+-----------+----------+-------------------------+
Copy

Exemplos de quadro de janela deslizante

No mundo financeiro, os analistas frequentemente estudam “médias móveis”.

Por exemplo, você pode ter um gráfico no qual o eixo X é o tempo e o eixo Y mostra o preço médio do estoque nas últimas 13 semanas (ou seja, “média móvel de 13 semanas”). Em um gráfico de uma média móvel de 13 semanas do preço de uma ação, o preço mostrado para 30 de junho não é o preço da ação em 30 de junho, mas a média do preço da ação para as 13 semanas até 30 de junho inclusive (ou seja, de 1º de abril até 30 de junho). O valor em 1º de julho é o preço médio para 2 de abril até 1º de julho; o valor em 2 de julho é o preço médio para 3 de abril até 2 de julho e assim por diante. Cada dia, a janela efetivamente adiciona o valor do dia mais recente à média móvel e remove o valor do dia mais antigo. Isto suaviza as flutuações do dia a dia e pode facilitar o reconhecimento de tendências.

As médias móveis podem ser calculadas utilizando uma “janela deslizante”. A janela tem uma largura específica em linhas. No exemplo de preço de ações acima, 13 semanas são 91 dias, portanto a janela deslizante seria de 91 dias. Se as medidas forem tomadas uma vez por dia (por exemplo, no final do dia), então a janela teria 91 linhas de largura.

Para definir uma janela com 91 linhas de largura:

SELECT AVG(price) OVER (ORDER BY timestamp1
                        ROWS BETWEEN 90 PRECEDING AND CURRENT ROW)
    FROM sales;
Copy

Nota

Quando a janela começa, pode ter menos de 91 dias de largura. Por exemplo, suponhamos que você queira o preço médio móvel de uma ação para 13 semanas. Se a ação foi criada em 1º de abril, então em 3 de abril havia apenas 3 dias de informação de preços; assim, a janela tem apenas 3 linhas de largura.

O exemplo a seguir mostra o resultado da soma em uma janela deslizante com largura suficiente para armazenar dois exemplos:

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

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

+-----------+----------+----------------------+
| MONTH_NUM | QUANTITY | SLIDING_SUM_QUANTITY |
|-----------+----------+----------------------+
|         1 |        1 |                   1  |  -- sum = 1
|         1 |        3 |                   4  |  -- sum = 1 + 3
|         1 |        5 |                   8  |  -- sum = 3 + 5 (1 is no longer in the window)
|         2 |        2 |                   7  |  -- sum = 5 + 2 (3 is no longer in the window)
+-----------+----------+----------------------+
Copy

Note que a funcionalidade de “janela deslizante” requer a cláusula ORDER BY; a janela deslizante precisa saber a ordem em que as linhas entram e saem da janela deslizante.

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