Sintaxe e uso da função de janela

Neste tópico:

O Snowflake oferece suporte a um grande número de funções analíticas SQL conhecidas como funções de janela. Os detalhes de cada função são documentados em páginas de referência individuais. O objetivo desta seção é fornecer informações gerais de referência que se aplicam a algumas ou todas as funções de janela, incluindo sintaxe detalhada para os principais componentes da cláusula OVER:

  • Cláusula PARTITION BY

  • Cláusula ORDER BY

  • Sintaxe do quadro de janela

Usuários que não estão familiarizados com as funções de janela podem querer ler o material conceitual em Análise de dados com funções de janela.

Sintaxe

<function> ( [ <arguments> ] ) OVER ( [ <windowDefinition> ] )
Copy

Onde:

windowDefinition ::=

[ PARTITION BY <expr1> [, ...] ]
[ ORDER BY <expr2> [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ <windowFrameClause> ]
Copy

Onde:

windowFrameClause ::=

{
    { ROWS | RANGE } UNBOUNDED PRECEDING
  | { ROWS | RANGE } <n> PRECEDING
  | { ROWS | RANGE } CURRENT ROW
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  | { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND <n> { PRECEDING | FOLLOWING }
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND <n> { PRECEDING | FOLLOWING }
  | { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}
Copy

Parâmetros

OVER( [ windowDefinition ] )

Especifica que a função está sendo usada como uma função de janela e especifica a janela sobre a qual a função opera. A cláusula OVER deve conter parênteses, mas eles podem estar vazios, dependendo dos requisitos da função em questão. Uma cláusula OVER vazia não tem partições e uma quadro de janela padrão implícito.

PARTITION BY expr1

Agrupa linhas em partições, por produto, cidade ou ano, por exemplo. As linhas de entrada são agrupadas por partições e, em seguida, a função é computada em cada partição. A cláusula PARTITION BY é opcional; é possível analisar um conjunto de linhas como uma única partição.

ORDER BY expr2

Ordena linhas dentro de cada partição ou dentro de todo o conjunto de linhas se nenhuma partição for especificada. Esta cláusula ORDER BY é diferente da cláusula ORDER BY que controla a ordem de todas as linhas retornadas no resultado final de uma consulta. Embora a cláusula ORDER BY seja opcional para algumas funções de janela, ela é obrigatória para outras. Por exemplo, funções de janela de classificação como RANK e NTILE exigem que seus dados de entrada estejam em uma ordem significativa.

A cláusula ORDER BY para uma função de janela segue regras semelhantes às da cláusula principal ORDER BY em uma consulta, com relação à ordem ASC/DESC (crescente/decrescente) e ao tratamento de NULL. Para obter mais detalhes, consulte ORDER BY.

Diferentes funções manipulam a cláusula ORDER BY de maneiras diferentes:

  • 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 aplicam uma quadro de janela implícito à cláusula ORDER BY. (Para obter mais informações, consulte Notas de uso para quadros de janela.)

{ ROWS | RANGE }

Especifica o tipo ou modo do quadro de janela, que define um número físico de linhas (ROWS) ou um conjunto de linhas computado logicamente (RANGE). Consulte Quadros de janela baseados em intervalos versus quadros de janela baseados em linhas.

Ambos os tipos de quadro especificam pontos inicial e final, usando limites nomeados implícitos ou valores de deslocamento explícitos. Um limite nomeado é definido com as palavras-chave CURRENT ROW, UNBOUNDED PRECEDING e UNBOUNDED FOLLOWING. Deslocamentos explícitos são definidos com números ou intervalos (n PRECEDING ou n FOLLOWING).

{ RANGE BETWEEN n PRECEDING | n FOLLOWING }

Especifica uma quadro de janela baseada em intervalo com deslocamentos explícitos.

Quadros de janela RANGE BETWEEN com deslocamentos explícitos devem ter apenas uma expressão ORDER BY. Os seguintes tipos de dados são compatíveis com essa expressão:

  • DATE, TIMESTAMP, TIMESTAMP_LTZ , TIMESTAMP_NTZ (DATETIME) , TIMESTAMP_TZ

  • NUMBER, incluindo INT, FLOAT e assim por diante

TIME e outros tipos de dados Snowflake não são compatíveis quando esse tipo de quadro de janela é usado. Para outros quadros de janela, outros tipos de dados, como VARCHAR, podem ser usados na expressão ORDER BY.

Para quadros de janela RANGE BETWEEN, n deve ser uma constante sem sinal (um valor numérico positivo, incluindo 0) ou uma constante INTERVAL positiva:

  • Se expr2 for um tipo de dados numérico, n deve ser uma constante sem sinal.

  • Se expr2 for um tipo de dados TIMESTAMP, n deve ser uma constante INTERVAL. Por exemplo: INTERVAL '12 hours' ou INTERVAL '3 days'.

  • Se expr2 for um tipo de dados DATE, n pode ser uma constante sem sinal ou uma constante INTERVAL, mas o início e o fim do quadro devem usar o mesmo tipo de dados para o valor n.

Quando a expressão ORDER BY é crescente (ASC), a sintaxe n FOLLOWING significa “linhas com valores maiores que (ou posteriores a) x” e n PRECEDING significa “linhas com valores menores que (ou anteriores a) x”, onde x é o valor ORDER BY da linha atual. Quando a expressão ORDER BY é decrescente (DESC), o oposto é verdadeiro. (Os deslocamentos 0 PRECEDING e 0 FOLLOWING são equivalentes a CURRENT ROW.)

Limitações de RANGE da BETWEEN

O seguinte subconjunto de funções de janela oferece suporte à sintaxe RANGE BETWEEN com deslocamentos explícitos:

Além disso, observe que:

  • Versões DISTINCT dessas funções não oferecem suporte a essa sintaxe.

  • As seguintes limitações se aplicam quando a função de janela COUNT é usada com esta sintaxe.

    • Somente um argumento de entrada é compatível.

    • Consultas curinga COUNT(table.*) não são compatíveis. Por exemplo, não é possível especificar:

      COUNT(t1.*) OVER(ORDER BY col1 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
      Copy

  • Não é possível especificar um quadro que resulte em uma inversão lógica das posições inicial e final do quadro. Por exemplo, os quadros a seguir retornam erros porque a linha final do quadro precede a linha inicial:

    ORDER BY col1 ASC RANGE BETWEEN 2 PRECEDING AND 4 PRECEDING
ORDER BY col1 ASC RANGE BETWEEN 2 FOLLOWING AND 2 PRECEDING
    Copy

Comportamento RANGE BETWEEN quando a expressão ORDER BY contém os valores NULL

Observe o seguinte comportamento quando um quadro de janela RANGE BETWEEN é usado e a coluna ORDER BY contém valores NULL:

  • Quando a cláusula ORDER BY especifica NULLS FIRST, as linhas com NULL dentro da coluna ORDER BY são incluídas nos quadros UNBOUNDED PRECEDING.

  • Quando a cláusula ORDER BY especifica NULLS LAST, as linhas com NULL dentro da coluna ORDER BY são incluídas nos quadros UNBOUNDED FOLLOWING.

  • Linhas com NULL na coluna ORDER BY são incluídas em um limite de quadro de deslocamento explícito somente quando o valor ORDER BY da linha atual é NULL.

Consulte Exemplos de RANGE BETWEEN com valores NULL na cláusula ORDER BY.

Notas de uso para quadros de janela

  • Todas as funções de janela oferecem suporte a quadros de janela. No entanto, o suporte para sintaxe de quadro de janela varia de acordo com a função. Se nenhum quadro de janela for especificado, o padrão depende da função:

    • Para funções não classificadas (como COUNT, MAX, MIN e SUM), e o padrão é o seguinte quadro de janela (de acordo com o padrão ANSI):

      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      Copy

    • Para funções de classificação (como FIRST_VALUE, LAST_VALUE e NTH_VALUE), o padrão é a janela inteira:

      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      Copy

      Observe que este comportamento não está em conformidade com o padrão ANSI.

      Nota

      Para maior clareza, o Snowflake recomenda evitar quadros de janelas implícitos. Se sua consulta usar um quadro de janela, defina um quadro de janela explícito.

  • Os quadros de janela exigem que os dados na janela estejam em uma ordem conhecida. Portanto, a cláusula ORDER BY dentro da cláusula OVER é obrigatória para a sintaxe do quadro de janela, embora essa cláusula ORDER BY seja geralmente opcional.

Exemplos

Esta seção contém exemplos que mostram como usar funções de janela de diferentes maneiras. Para exemplos adicionais, consulte Análise de dados com funções de janela e as páginas de funções individuais.

Exemplo introdutório

Suponha que você seja proprietário de uma rede de lojas. A consulta a seguir mostra a porcentagem do lucro total da rede que é gerada por cada loja. A consulta usa a função RATIO_TO_REPORT, que pega um valor (net_profit) da linha atual e o divide pela soma dos valores correspondentes de todas as outras linhas:

Criar e carregar a tabela:

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

Consultar a tabela:

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

Quadro de janela com posição inicial ilimitada

Criar e preencher uma tabela com valores:

CREATE OR REPLACE TABLE example_cumulative (p INT, o INT, i INT);

INSERT INTO example_cumulative VALUES
    (  0, 1, 10), (0, 2, 20), (0, 3, 30),
    (100, 1, 10),(100, 2, 30),(100, 2, 5),(100, 3, 11),(100, 3, 120),
    (200, 1, 10000),(200, 1, 200),(200, 1, 808080),(200, 2, 33333),(200, 3, null), (200, 3, 4),
    (300, 1, null), (300, 1, null);
Copy

Execute uma consulta que use uma quadro de janela com uma posição inicial ilimitada e mostre a saída. Retorna os valores cumulativos COUNT, SUM, AVG, MIN e MAX para cada linha em cada partição:

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Rows_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Rows_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Rows_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Rows_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i_Rows_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+
|   P | O |      I | COUNT_I_ROWS_PRE | SUM_I_ROWS_PRE | AVG_I_ROWS_PRE | MIN_I_ROWS_PRE | MAX_I_ROWS_PRE |
|-----+---+--------+------------------+----------------+----------------+----------------+----------------|
|   0 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
|   0 | 2 |     20 |                2 |             30 |         15.000 |             10 |             20 |
|   0 | 3 |     30 |                3 |             60 |         20.000 |             10 |             30 |
| 100 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
| 100 | 2 |     30 |                2 |             40 |         20.000 |             10 |             30 |
| 100 | 2 |      5 |                3 |             45 |         15.000 |              5 |             30 |
| 100 | 3 |     11 |                4 |             56 |         14.000 |              5 |             30 |
| 100 | 3 |    120 |                5 |            176 |         35.200 |              5 |            120 |
| 200 | 1 |  10000 |                1 |          10000 |      10000.000 |          10000 |          10000 |
| 200 | 1 |    200 |                2 |          10200 |       5100.000 |            200 |          10000 |
| 200 | 1 | 808080 |                3 |         818280 |     272760.000 |            200 |         808080 |
| 200 | 2 |  33333 |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |   NULL |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |      4 |                5 |         851617 |     170323.400 |              4 |         808080 |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+
Copy

Retorne os mesmos resultados da consulta acima usando o quadro de janela padrão (ou seja, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o) sum_i_Range_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o) avg_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o) max_i_Range_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
|   P | O |      I | COUNT_I_RANGE_PRE | SUM_I_RANGE_PRE | AVG_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------|
|   0 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
|   0 | 2 |     20 |                 2 |              30 |       15.000000 |              10 |              20 |
|   0 | 3 |     30 |                 3 |              60 |       20.000000 |              10 |              30 |
| 100 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
| 100 | 2 |     30 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 2 |      5 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 3 |     11 |                 5 |             176 |       35.200000 |               5 |             120 |
| 100 | 3 |    120 |                 5 |             176 |       35.200000 |               5 |             120 |
| 200 | 1 |  10000 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 |    200 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 | 808080 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 2 |  33333 |                 4 |          851613 |   212903.250000 |             200 |          808080 |
| 200 | 3 |   NULL |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 200 | 3 |      4 |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
Copy

Quadros de janelas com deslocamentos explícitos

Criar e preencher uma tabela com valores:

CREATE TABLE example_sliding
  (p INT, o INT, i INT, r INT, s VARCHAR(100));

INSERT INTO example_sliding VALUES
  (100,1,1,70,'seventy'),(100,2,2,30, 'thirty'),(100,3,3,40,'forty'),(100,4,NULL,90,'ninety'),
  (100,5,5,50,'fifty'),(100,6,6,30,'thirty'),
  (200,7,7,40,'forty'),(200,8,NULL,NULL,'n_u_l_l'),(200,9,NULL,NULL,'n_u_l_l'),(200,10,10,20,'twenty'),
  (200,11,NULL,90,'ninety'),
  (300,12,12,30,'thirty'),
  (400,13,NULL,20,'twenty');
Copy

Retorna os resultados da função MIN para duas colunas (numérico e cadeia de caracteres) em janelas deslizantes antes, depois e incluindo a linha atual:

select p, o, i AS i_col,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_i_3P_1P,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_i_1F_3F,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_i_1P_3F,
    s,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_s_3P_1P,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_s_1F_3F,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_s_1P_3F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
|   P |  O | I_COL | MIN_I_3P_1P | MIN_I_1F_3F | MIN_I_1P_3F | S       | MIN_S_3P_1P | MIN_S_1F_3F | MIN_S_1P_3F |
|-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 |  1 |     1 |        NULL |           2 |           1 | seventy | NULL        | forty       | forty       |
| 100 |  2 |     2 |           1 |           3 |           1 | thirty  | seventy     | fifty       | fifty       |
| 100 |  3 |     3 |           1 |           5 |           2 | forty   | seventy     | fifty       | fifty       |
| 100 |  4 |  NULL |           1 |           5 |           3 | ninety  | forty       | fifty       | fifty       |
| 100 |  5 |     5 |           2 |           6 |           5 | fifty   | forty       | thirty      | fifty       |
| 100 |  6 |     6 |           3 |        NULL |           5 | thirty  | fifty       | NULL        | fifty       |
| 200 |  7 |     7 |        NULL |          10 |           7 | forty   | NULL        | n_u_l_l     | forty       |
| 200 |  8 |  NULL |           7 |          10 |           7 | n_u_l_l | forty       | n_u_l_l     | forty       |
| 200 |  9 |  NULL |           7 |          10 |          10 | n_u_l_l | forty       | ninety      | n_u_l_l     |
| 200 | 10 |    10 |           7 |        NULL |          10 | twenty  | forty       | ninety      | n_u_l_l     |
| 200 | 11 |  NULL |          10 |        NULL |          10 | ninety  | n_u_l_l     | NULL        | ninety      |
| 300 | 12 |    12 |        NULL |        NULL |          12 | thirty  | NULL        | NULL        | thirty      |
| 400 | 13 |  NULL |        NULL |        NULL |        NULL | twenty  | NULL        | NULL        | twenty      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
Copy

Retorna os resultados da função MAX para duas colunas (numérico e cadeia de caracteres) em janelas deslizantes antes, depois e incluindo a linha atual:

SELECT p, o, i AS i_col,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_i_3P_1P,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_i_1F_3F,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_i_1P_3F,
    s,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_s_3P_1P,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_s_1F_3F,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_s_1P_3F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
|   P |  O | I_COL | MAX_I_3P_1P | MAX_I_1F_3F | MAX_I_1P_3F | S       | MAX_S_3P_1P | MAX_S_1F_3F | MAX_S_1P_3F |
|-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 |  1 |     1 |        NULL |           3 |           3 | seventy | NULL        | thirty      | thirty      |
| 100 |  2 |     2 |           1 |           5 |           5 | thirty  | seventy     | ninety      | thirty      |
| 100 |  3 |     3 |           2 |           6 |           6 | forty   | thirty      | thirty      | thirty      |
| 100 |  4 |  NULL |           3 |           6 |           6 | ninety  | thirty      | thirty      | thirty      |
| 100 |  5 |     5 |           3 |           6 |           6 | fifty   | thirty      | thirty      | thirty      |
| 100 |  6 |     6 |           5 |        NULL |           6 | thirty  | ninety      | NULL        | thirty      |
| 200 |  7 |     7 |        NULL |          10 |          10 | forty   | NULL        | twenty      | twenty      |
| 200 |  8 |  NULL |           7 |          10 |          10 | n_u_l_l | forty       | twenty      | twenty      |
| 200 |  9 |  NULL |           7 |          10 |          10 | n_u_l_l | n_u_l_l     | twenty      | twenty      |
| 200 | 10 |    10 |           7 |        NULL |          10 | twenty  | n_u_l_l     | ninety      | twenty      |
| 200 | 11 |  NULL |          10 |        NULL |          10 | ninety  | twenty      | NULL        | twenty      |
| 300 | 12 |    12 |        NULL |        NULL |          12 | thirty  | NULL        | NULL        | thirty      |
| 400 | 13 |  NULL |        NULL |        NULL |        NULL | twenty  | NULL        | NULL        | twenty      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
Copy

Retornar a soma de uma coluna de números em janelas deslizantes antes, depois e englobando a linha atual:

SELECT p, o, r AS r_col,
    SUM(r) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING) sum_r_4P_2P,
    sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING) sum_r_2F_4F,
    sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 PRECEDING AND 4 FOLLOWING) sum_r_2P_4F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+
|   P |  O | R_COL | SUM_R_4P_2P | SUM_R_2F_4F | SUM_R_2P_4F |
|-----+----+-------+-------------+-------------+-------------|
| 100 |  1 |    70 |        NULL |         180 |         280 |
| 100 |  2 |    30 |        NULL |         170 |         310 |
| 100 |  3 |    40 |          70 |          80 |         310 |
| 100 |  4 |    90 |         100 |          30 |         240 |
| 100 |  5 |    50 |         140 |        NULL |         210 |
| 100 |  6 |    30 |         160 |        NULL |         170 |
| 200 |  7 |    40 |        NULL |         110 |         150 |
| 200 |  8 |  NULL |        NULL |         110 |         150 |
| 200 |  9 |  NULL |          40 |          90 |         150 |
| 200 | 10 |    20 |          40 |        NULL |         110 |
| 200 | 11 |    90 |          40 |        NULL |         110 |
| 300 | 12 |    30 |        NULL |        NULL |          30 |
| 400 | 13 |    20 |        NULL |        NULL |          20 |
+-----+----+-------+-------------+-------------+-------------+
Copy

Exemplos de função de classificação

O exemplo a seguir mostra como classificar as vendas com base no valor total (em dólares) que cada vendedor vendeu. A cláusula ORDER BY dentro da cláusula OVER classifica os totais em ordem decrescente (do maior para o menor). A consulta calcula a classificação de cada vendedor em relação a todos os outros vendedores.

Crie a tabela e insira os dados:

CREATE TABLE sales_table (salesperson_name VARCHAR, sales_in_dollars INTEGER);
INSERT INTO sales_table (salesperson_name, sales_in_dollars) VALUES
    ('Smith', 600),
    ('Jones', 1000),
    ('Torkelson', 700),
    ('Dolenz', 800);
Copy

Agora consulte os dados:

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Jones            |             1000 |          1 |
| Dolenz           |              800 |          2 |
| Torkelson        |              700 |          3 |
| Smith            |              600 |          4 |
+------------------+------------------+------------+
Copy

A saída não é necessariamente ordenada por classificação. Para exibir resultados ordenados por classificação, especifique uma cláusula ORDER BY para a consulta em si (além da cláusula ORDER BY para a função de janela), conforme mostrado aqui:

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table
  ORDER BY 3;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Jones            |             1000 |          1 |
| Dolenz           |              800 |          2 |
| Torkelson        |              700 |          3 |
| Smith            |              600 |          4 |
+------------------+------------------+------------+
Copy

O exemplo anterior tem duas cláusulas ORDERBY:

  • Uma controla a ordem da classificação.

  • Uma controla a ordem da saída.

Essas cláusulas são independentes. Por exemplo, você pode ordenar as classificações com base no total de vendas (como mostrado acima), mas ordenar as linhas de saída com base no sobrenome do vendedor:

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table
  ORDER BY salesperson_name;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Dolenz           |              800 |          2 |
| Jones            |             1000 |          1 |
| Smith            |              600 |          4 |
| Torkelson        |              700 |          3 |
+------------------+------------------+------------+
Copy

Exemplo de RANGE BETWEEN com deslocamentos numéricos explícitos

O exemplo a seguir usa a sintaxe RANGE BETWEEN com deslocamentos numérico explícitos. Para executar este exemplo, siga estas instruções: Criação e carregamento da tabela menu_items. Para exemplos semelhantes que usam deslocamentos de INTERVAL, consulte Usar agregações em janela para cálculos contínuos.

A consulta a seguir calcula o custo médio dos produtos vendidos para categorias de itens de menu disponíveis em um food truck. A função de janela não particiona os resultados; portanto, as médias são calculadas em todo o conjunto de resultados, sujeito a um quadro baseado em intervalo.

O limite do quadro é o valor do custo das mercadorias na linha atual, mais dois (a primeira linha = 0,50 + 2,00, por exemplo). As linhas se qualificam para o quadro quando estão dentro dessa faixa de dois dólares.

SELECT menu_category, menu_cogs_usd,
    AVG(menu_cogs_usd)
      OVER(ORDER BY menu_cogs_usd RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) avg_cogs
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  GROUP BY menu_category, menu_cogs_usd
  ORDER BY menu_category, menu_cogs_usd;
Copy
+---------------+---------------+----------+
| MENU_CATEGORY | MENU_COGS_USD | AVG_COGS |
|---------------+---------------+----------|
| Beverage      |          0.50 |  1.18333 |
| Beverage      |          0.65 |  1.37857 |
| Beverage      |          0.75 |  1.50000 |
| Dessert       |          0.50 |  1.18333 |
| Dessert       |          1.00 |  1.87500 |
| Dessert       |          1.25 |  2.05000 |
| Dessert       |          2.50 |  3.16666 |
| Dessert       |          3.00 |  3.50000 |
| Snack         |          1.25 |  2.05000 |
| Snack         |          2.25 |  2.93750 |
| Snack         |          4.00 |  4.00000 |
+---------------+---------------+----------+

Por exemplo, o valor avg_cogs da primeira linha é 1,1833. Isso é calculado como a soma de todos os valores menu_cogs_usd que estão entre 0,50 e 2,50, dividido pela contagem dessas linhas:

(0.50 + 0.65 + 0.75 + 0.50 + 1.00 + 1.25 + 2.50 + 1.25 + 2.25) / 9 = 1.18333

A penúltima linha tem um valor avg_cogs de 2,93750. Isso é calculado como a soma de todos os valores menu_cogs_usd que estão entre 2,25 e 4,25, dividido pela contagem dessas linhas:

(2.25 + 2.50 + 3.00 + 4.00) / 4 = 2.93750

A última linha retorna 4,0 para avg_cogs e menu_cogs_usd. Este resultado é preciso porque somente esta linha pertence ao quadro; 4,0 é o valor menu_cogs_usd máximo em todo o resultado, então ele se torna um quadro de linha única. Não há linhas “seguintes”.

Observe que esta consulta tem uma cláusula ORDER BY para a função de janela e uma cláusula ORDER BY para os resultados finais da consulta. A saída final ORDER BY não influencia o cálculo dos resultados da função de janela. O conjunto de resultados ordenados para calcular a função é um conjunto de resultados intermediário que a consulta final não exibe.

Exemplos de RANGE BETWEEN com valores NULL na cláusula ORDER BY

A tabela nulls contém cinco linhas e duas têm valores NULL na coluna c1. Crie e carregue a tabela da seguinte forma:

CREATE OR REPLACE TABLE nulls(c1 int, c2 int);

INSERT INTO nulls VALUES
  (1,10),
  (2,20),
  (3,30),
  (NULL,20),
  (NULL,50);
Copy

Quando NULLS LAST é especificado e o quadro de janela usa deslocamentos explícitos, as linhas com NULL em c1 são incluídas no quadro somente quando o valor ORDER BY da linha atual é NULL. A consulta a seguir retorna uma soma de 50 quando a linha 3 é a linha atual. A linha NULL a seguir não está inclusa no quadro.

SELECT c1 c1_nulls_last, c2,
    SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum_c2
  FROM nulls;
Copy
+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
|             1 | 10 |     30 |
|             2 | 20 |     60 |
|             3 | 30 |     50 |
|          NULL | 20 |     70 |
|          NULL | 50 |     70 |
+---------------+----+--------+

Quando NULLS LAST é especificado e o quadro de janela usa UNBOUNDED FOLLOWING, as linhas com NULL em c1 são inclusas no quadro. A consulta a seguir retorna uma soma de 120 quando a linha 3 é a linha atual. Ambas as linhas NULL seguintes estão inclusas no quadro.

SELECT c1 c1_nulls_last, c2,
    SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) sum_c2
  FROM nulls;
Copy
+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
|             1 | 10 |    130 |
|             2 | 20 |    130 |
|             3 | 30 |    120 |
|          NULL | 20 |     70 |
|          NULL | 50 |     70 |
+---------------+----+--------+

Criação e carregamento da tabela heavy_weather

Para criar e inserir linhas na tabela heavy_weather, que é usada em alguns exemplos de função de janela, execute este script.

CREATE OR REPLACE TABLE heavy_weather
  (start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));

INSERT INTO heavy_weather VALUES
('2021-12-23 06:56:00.000',0.08,'Mount Shasta','Siskiyou'),
('2021-12-23 07:51:00.000',0.09,'Mount Shasta','Siskiyou'),
('2021-12-23 16:23:00.000',0.56,'South Lake Tahoe','El Dorado'),
('2021-12-23 17:24:00.000',0.38,'South Lake Tahoe','El Dorado'),
('2021-12-23 18:30:00.000',0.28,'South Lake Tahoe','El Dorado'),
('2021-12-23 19:35:00.000',0.37,'Mammoth Lakes','Mono'),
('2021-12-23 19:36:00.000',0.80,'South Lake Tahoe','El Dorado'),
('2021-12-24 04:43:00.000',0.25,'Alta','Placer'),
('2021-12-24 05:26:00.000',0.34,'Alta','Placer'),
('2021-12-24 05:35:00.000',0.42,'Big Bear City','San Bernardino'),
('2021-12-24 06:49:00.000',0.17,'South Lake Tahoe','El Dorado'),
('2021-12-24 07:40:00.000',0.07,'Alta','Placer'),
('2021-12-24 08:36:00.000',0.07,'Alta','Placer'),
('2021-12-24 11:52:00.000',0.08,'Alta','Placer'),
('2021-12-24 12:52:00.000',0.38,'Alta','Placer'),
('2021-12-24 15:44:00.000',0.13,'Alta','Placer'),
('2021-12-24 15:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-24 16:55:00.000',0.09,'Big Bear City','San Bernardino'),
('2021-12-24 21:53:00.000',0.07,'Montague','Siskiyou'),
('2021-12-25 02:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 07:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 08:52:00.000',0.08,'Alta','Placer'),
('2021-12-25 09:48:00.000',0.18,'Alta','Placer'),
('2021-12-25 12:52:00.000',0.10,'Alta','Placer'),
('2021-12-25 17:21:00.000',0.23,'Alturas','Modoc'),
('2021-12-25 17:52:00.000',1.54,'Alta','Placer'),
('2021-12-26 01:52:00.000',0.61,'Alta','Placer'),
('2021-12-26 05:43:00.000',0.16,'South Lake Tahoe','El Dorado'),
('2021-12-26 05:56:00.000',0.08,'Bishop','Inyo'),
('2021-12-26 06:52:00.000',0.75,'Bishop','Inyo'),
('2021-12-26 06:53:00.000',0.08,'Lebec','Los Angeles'),
('2021-12-26 07:52:00.000',0.65,'Alta','Placer'),
('2021-12-26 09:52:00.000',2.78,'Alta','Placer'),
('2021-12-26 09:55:00.000',0.07,'Big Bear City','San Bernardino'),
('2021-12-26 14:22:00.000',0.32,'Alta','Placer'),
('2021-12-26 14:52:00.000',0.34,'Alta','Placer'),
('2021-12-26 15:43:00.000',0.35,'Alta','Placer'),
('2021-12-26 17:31:00.000',5.24,'Alta','Placer'),
('2021-12-26 22:52:00.000',0.07,'Alta','Placer'),
('2021-12-26 23:15:00.000',0.52,'Alta','Placer'),
('2021-12-27 02:52:00.000',0.08,'Alta','Placer'),
('2021-12-27 03:52:00.000',0.14,'Alta','Placer'),
('2021-12-27 04:52:00.000',1.52,'Alta','Placer'),
('2021-12-27 14:37:00.000',0.89,'Alta','Placer'),
('2021-12-27 14:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-27 17:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-30 11:23:00.000',0.12,'Lebec','Los Angeles'),
('2021-12-30 11:43:00.000',0.98,'Lebec','Los Angeles'),
('2021-12-30 13:53:00.000',0.23,'Lebec','Los Angeles'),
('2021-12-30 14:53:00.000',0.13,'Lebec','Los Angeles'),
('2021-12-30 15:15:00.000',0.29,'Lebec','Los Angeles'),
('2021-12-30 17:53:00.000',0.10,'Lebec','Los Angeles'),
('2021-12-30 18:53:00.000',0.09,'Lebec','Los Angeles'),
('2021-12-30 19:53:00.000',0.07,'Lebec','Los Angeles'),
('2021-12-30 20:53:00.000',0.07,'Lebec','Los Angeles')
;
Copy
Linguagem: Português