Funções de janela¶

Uma função de janela opera em um grupo (“janela”) de linhas relacionadas.

Cada vez que uma função de janela é chamada, são passadas uma linha (a linha atual na janela) e a janela das linhas que contêm a linha atual. A função de janela retorna uma linha de saída para cada linha de entrada. A saída depende da linha individual passada para a função e dos valores das outras linhas na janela passada para a função.

Algumas funções de janela diferenciam a ordem. Há dois tipos principais de funções de janela que diferenciam a ordem:

Funções relacionadas à classificação.

Funções de quadro de janela.

As informações da lista de funções relacionadas à classificação são baseadas no “ranking” de uma linha. Por exemplo, se você classificar as lojas em ordem decrescente por lucro anual, a loja com o maior lucro será classificada 1; a segunda loja mais lucrativa será classificada 2 e assim por diante.

As funções de quadro de janela permitem realizar operações contínuas, como o cálculo de um total em execução ou uma média móvel, em um subconjunto das linhas na janela.

Os usuários que não estão familiarizados com as funções de janela, funções relacionadas à classificação ou funções de estrutura de janela podem ler o material com os conceitos em Uso de funções de janela.

Neste tópico:

Visão geral¶

O que é uma janela?¶

Uma janela é um grupo de linhas relacionadas. Por exemplo, uma janela pode ser definida com base em carimbos de data/hora, com todas as linhas de um mesmo mês agrupadas na mesma janela. Ou uma janela pode ser definida com base na localização, com todas as linhas de uma determinada cidade agrupadas na mesma janela.

Uma janela pode conter zero, uma ou várias linhas. Para simplificar, a documentação do Snowflake geralmente diz que uma janela contém várias linhas.

O que é uma função de janela?¶

Uma função de janela é qualquer função que opera em uma janela de linhas.

Uma função de janela é geralmente passada por dois parâmetros:

Uma linha. Mais precisamente, são passadas 0 ou mais expressões a uma função de janela. Em quase todos os casos, pelo menos uma dessas expressões faz referência a uma coluna naquela linha. (A maioria das funções de janela requer pelo menos uma coluna ou expressão, mas algumas funções de janela, como algumas funções relacionadas à classificação, não exigem uma coluna ou expressão explícita).
Uma janela de linhas relacionadas que inclui essa linha. A janela pode ser a tabela inteira ou um subconjunto das linhas da tabela.

Para funções que não são de janela, todos os argumentos são geralmente passados explicitamente para a função, por exemplo:

MY_FUNCTION(argument1, argument2, …)

As funções de janela se comportam de maneira diferente; embora a linha atual seja passada como argumento da forma normal, a janela é passada por meio de uma cláusula separada, chamada de cláusula OVER. A sintaxe da cláusula OVER está documentada posteriormente.

Lista de funções que oferecem suporte a janelas¶

A lista abaixo mostra todas as funções de janela.

Observe que algumas funções listadas como funções de quadro de janela não aceitam todos os tipos possíveis de quadros de janela.

Nome da função	Janela	Quadro de janela	Relacionada à classificação	Notas
Janela geral
ANY_VALUE	✔
AVG	✔	✔
CONDITIONAL_CHANGE_EVENT	✔
CONDITIONAL_TRUE_EVENT	✔
CORR	✔
COUNT	✔	✔
COUNT_IF	✔	✔
COVAR_POP	✔
COVAR_SAMP	✔
LISTAGG	✔			Não utiliza a sintaxe de janela padrão.
MAX	✔	✔
MEDIAN	✔
MIN	✔	✔
MODE	✔
PERCENTILE_CONT	✔			Utiliza uma sintaxe diferente das outras funções de janela.
PERCENTILE_DISC	✔			Utiliza uma sintaxe diferente das outras funções de janela.
RATIO_TO_REPORT	✔
STDDEV, STDDEV_SAMP	✔	✔		STDDEV e STDDEV_SAMP são aliases.
STDDEV_POP	✔	✔
SUM	✔	✔
VAR_POP	✔	✔
VAR_SAMP	✔	✔
VARIANCE_POP	✔	✔		Alias para VAR_POP.
VARIANCE , VARIANCE_SAMP	✔	✔		Alias para VAR_SAMP.

Relacionadas à classificação
CUME_DIST	✔		✔
DENSE_RANK	✔	✔	✔
FIRST_VALUE	✔	✔	✔
LAG	✔		✔
LAST_VALUE	✔	✔	✔
LEAD	✔		✔
NTH_VALUE	✔	✔	✔
NTILE	✔		✔
PERCENT_RANK	✔	✔	✔	Aceita quadros de janela cumulativas com base em intervalos, mas não outros tipos de quadros de janelas.
RANK	✔	✔	✔
ROW_NUMBER	✔		✔
Agregação bit a bit
BITAND_AGG	✔
BITOR_AGG	✔
BITXOR_AGG	✔
Agregação booleana
BOOLAND_AGG	✔
BOOLOR_AGG	✔
BOOLXOR_AGG	✔
Hash
HASH_AGG	✔
Agregação de dados semiestruturados
ARRAY_AGG	✔
OBJECT_AGG	✔
Contagem de valores distintos
ARRAY_UNION_AGG	✔
ARRAY_UNIQUE_AGG	✔
Regressão linear
REGR_AVGX	✔
REGR_AVGY	✔
REGR_COUNT	✔
REGR_INTERCEPT	✔
REGR_R2	✔
REGR_SLOPE	✔
REGR_SXX	✔
REGR_SXY	✔
REGR_SYY	✔
Estatística e probabilidade
KURTOSIS	✔
Estimativa de cardinalidade . (usando HyperLogLog)
APPROX_COUNT_DISTINCT	✔			Alias para HLL.
HLL	✔
HLL_ACCUMULATE	✔
HLL_COMBINE	✔
HLL_ESTIMATE				Não é uma função de agregação; utiliza entrada escalar de HLL_ACCUMULATE ou HLL_COMBINE.
HLL_EXPORT	✔
HLL_IMPORT	✔
Estimativa de similaridade . (usando MinHash)
APPROXIMATE_JACCARD_INDEX	✔			Alias para APPROXIMATE_SIMILARITY.
APPROXIMATE_SIMILARITY	✔
MINHASH	✔
MINHASH_COMBINE	✔
Estimativa de frequência . (usando Space-Saving)
APPROX_TOP_K	✔
APPROX_TOP_K_ACCUMULATE	✔
APPROX_TOP_K_COMBINE	✔
APPROX_TOP_K_ESTIMATE				Não é uma função de agregação; utiliza entrada escalar de APPROX_TOP_K_ACCUMULATE ou APPROX_TOP_K_COMBINE.
Estimativa percentil . (usando t-Digest)
APPROX_PERCENTILE	✔
APPROX_PERCENTILE_ACCUMULATE	✔
APPROX_PERCENTILE_COMBINE	✔
APPROX_PERCENTILE_ESTIMATE				Não é uma função de agregação; utiliza entrada escalar de APPROX_PERCENTILE_ACCUMULATE ou APPROX_PERCENTILE_COMBINE.

Funções de janela e valores NULL¶

Algumas funções ignoram valores NULL. Por exemplo, AVG calcula a média dos valores 1, 5 e NULL para ser 3, com base na seguinte fórmula:

(1 + 5) / 2 = 3

Tanto no numerador como no denominador, somente os valores nãoNULL são utilizados.

Se todos os valores passados para a função forem NULL, então a função retorna NULL.

Mais de uma coluna pode ser passada para algumas funções de janela. Por exemplo:

SELECT COUNT(col1, col2) FROM table1;

Copy

Nesses casos, a função ignora uma linha se alguma coluna individual for NULL.

Por exemplo, na consulta seguinte, COUNT retorna 1, não 4, porque três das quatro linhas contêm pelo menos um valor NULL nas colunas selecionadas:

Crie uma tabela e preencha-a com valores:

CREATE TABLE t (x INT, y INT);
INSERT INTO t (x, y) VALUES
  (1, 2),         -- No NULLs.
  (3, NULL),      -- One but not all columns are NULL.
  (NULL, 6),      -- One but not all columns are NULL.
  (NULL, NULL);   -- All columns are NULL.

Copy

Consultar a tabela:

SELECT COUNT(x, y) FROM t;
+-------------+
| COUNT(X, Y) |
|-------------|
|           1 |
+-------------+

Copy

Da mesma forma, se SUM é chamado com uma expressão que faz referência a duas ou mais colunas, e se uma ou mais dessas colunas é NULL, a expressão avalia para NULL, e a linha é ignorada:

SELECT SUM(x + y) FROM t;
+------------+
| SUM(X + Y) |
|------------|
|          3 |
+------------+

Copy

Observe que esse comportamento difere do comportamento de GROUP BY, que não descarta linhas quando algumas colunas são NULL:

SELECT x AS X_COL, y AS Y_COL FROM t GROUP BY x, y;
+-------+-------+
| X_COL | Y_COL |
|-------+-------|
|     1 |     2 |
|     3 |  NULL |
|  NULL |     6 |
|  NULL |  NULL |
+-------+-------+

Copy

Exemplo introdutório¶

Suponha que você seja proprietário de uma rede de lojas. A consulta seguinte mostra a porcentagem do lucro total da rede gerada por cada loja. A consulta utiliza a função RATIO_TO_REPORT, que toma um valor (por exemplo, net_profit) da linha atual e o divide pela soma dos valores correspondentes (net_profit) 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

O que é um quadro de janela?¶

Um quadro de janela é um subgrupo das linhas em uma janela. A criação de subconjuntos permite calcular valores considerando apenas aquele subgrupo de linhas especificado. Os quadros de janela são especificados como uma subcláusula adicional na subcláusula ORDER BY da cláusula OVER.

Tipos de quadros de janela¶

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

Cumulativo:: Permite calcular os valores contínuos do início da janela até a linha atual ou da linha atual até o final da janela.
Deslizante:: Permite calcular valores contínuos entre quaisquer duas linhas (inclusive) na janela, em relação à linha atual.

Para obter mais informações sobre quadros de janela, incluindo a sintaxe usada para quadros de janela, consulte Sintaxe e utilização do quadro de janela.

Sintaxe e utilização da janela¶

Sintaxe da janela¶

<function> ( [ <arguments> ] ) OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> ] )

Copy

A cláusula OVER especifica que a função está sendo usada como uma função de janela.

A subcláusula PARTITION BY permite agrupar as linhas em subgrupos, por exemplo, por cidade, por ano etc. A cláusula PARTITION BY é opcional. Você pode analisar um grupo inteiro de linhas sem dividi-lo em subgrupos.

A cláusula ORDER BY ordena as linhas dentro da janela. (Isso é diferente de ordenar a saída de uma consulta. Uma consulta pode ter uma cláusula ORDER BY que controla a ordem das linhas dentro de uma janela, e uma cláusula ORDER BY separada, fora da cláusula OVER, que controla a ordem de saída de toda a consulta). Embora a cláusula ORDER BY seja opcional para algumas funções de janela, ela é necessária para outras. Por exemplo, as funções de quadro de janela e funções relacionadas à classificação exigem que os dados estejam em uma ordem significativa e, portanto, exigem uma subcláusula ORDER BY.

Nota

As funções que aceitam quadros de janela utilizam uma sintaxe modificada/alterada. Para obter mais detalhes, consulte Sintaxe e utilização do quadro de janela (neste tópico).

Notas de uso da janela¶

A cláusula OVER especifica a janela sobre a qual a função opera. A cláusula consiste em um dos seguintes componentes (ou ambos):
- PARTITION BY expr1: subcláusula que define a partição, se houver, para a janela (ou seja, como os dados serão agrupados antes da aplicação da função).
- ORDER BY expr2: subcláusula que determina a ordenação das linhas na janela.
  
  A subcláusula ORDER BY segue regras semelhantes às da cláusula ORDER BY de consulta, por exemplo, com respeito a ASC/DESC (crescente/decrescente) e tratamento de NULL. Para obter mais detalhes sobre as opções adicionais com suporte, consulte a construção de consulta ORDER BY.
  
  Funções diferentes tratam a cláusula ORDER BY de maneiras distintas:
  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).
  
  Nota
  
  Para maior clareza, o Snowflake recomenda evitar quadros de janelas implícitos. Se sua consulta utiliza um quadro de janela, faça com que ele seja um quadro de janela explícito.

Sintaxe e utilização do quadro de janela¶

Sintaxe do quadro de janela¶

<function> ( <arguments> ) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ cumulativeFrame | slidingFrame ] )

Copy

Onde:

cumulativeFrame ::=
    {
       { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
     | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    }

Copy

slidingFrame ::=
    {
       ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN UNBOUNDED PRECEDING AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
    }

Copy

Nota

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, mesmo que essa cláusula ORDER BY seja opcional na sintaxe geral da função de janela.

Notas de uso dos quadros de janela¶

Para quadros de janela cumulativos:
- ROWS calcula o resultado para a linha atual usando todas as linhas desde o início ou fim da partição até a linha atual (de acordo com a subcláusula ORDER BY especificada).
- RANGE é semelhante a ROWS, exceto que apenas calcula o resultado para linhas que têm o mesmo valor da linha atual (de acordo com a subcláusula ORDER BY especificada).
Para quadros de janela deslizantes:
- ROWS é inclusivo e é sempre relativo à linha atual.
- RANGE não é suportado.
- Se o número especificado de ROWS precedentes ou posteriores se estender além dos limites da janela, o Snowflake trata o valor como NULL.
Se nenhum quadro de janela for especificado, o padrão depende da função:
- Para funções não relacionadas à classificação (COUNT, MAX, MIN, SUM), o padrão é o seguinte quadro de janela cumulativo (de acordo com o padrão ANSI):
  
  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- Para funções relacionadas à classificação (FIRST_VALUE, LAST_VALUE, NTH_VALUE), o padrão é a janela inteira:
  
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  
  Note que isso diverge do padrão ANSI.

Dicas gerais¶

A sintaxe mostra todas as subcláusulas da cláusula OVER como opcionais para funções de janela. Isso é por design (ou seja, você pode usar OVER sem uma subcláusula dentro dos parênteses). Isso pode ser útil em cenários específicos (por exemplo, desativação do processamento paralelo).
PARTITION BY nem sempre é compatível com GROUP BY.
Se uma consulta usa mais de uma função de janela, ela costuma dividir o conjunto de dados de entrada de cada função da mesma forma. Por exemplo, a primeira instrução abaixo tem mais probabilidade de estar correta do que a segunda instrução:
```
SELECT SUM(a) OVER (PARTITION BY x), SUM(b) OVER (PARTITION BY x) ... ;

SELECT SUM(a)                      , SUM(b) OVER (PARTITION BY x) ... ;
```
Copy
A mensagem de erro SQL compilation error: ... is not a valid group by expression é geralmente um sinal de que colunas diferentes nas cláusulas “project” da instrução SELECT não estão divididas da mesma maneira e, portanto, podem produzir números diferentes de linhas.

Exemplos¶

Exemplos adicionais podem ser encontrados em Uso de funções de janela.

Exemplos de quadro de janela cumulativo¶

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

Executar uma consulta que utilize um quadro de janela cumulativo e mostre a saída. Retornar count, sum, min e max cumulativos para as linhas na janela especificada para a tabela:

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

Retornar count, sum, min e max cumulativos por intervalo para as linhas na janela especificada para a tabela:

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Range_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 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

Retornar os mesmos resultados da consulta acima usando a semântica padrão de quadro de janela (ou seja, RANGE 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

Exemplos de quadro de janela deslizante¶

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

Retornar os valores mínimos para duas colunas (numérica e cadeia de caracteres) em janelas deslizantes antes, depois e englobando 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

Retornar os valores máximos para duas colunas (numérica e cadeia de caracteres) em janelas deslizantes antes, depois e englobando 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ções relacionadas à classificação¶

O exemplo a seguir mostra como classificar vendedores com base no valor total (em dólares) que cada um vendeu. A consulta usa a cláusula OVER para criar uma janela que contém o total de vendas de cada vendedor. A subcláusula ORDER BY dentro da cláusula OVER coloca essas linhas em ordem decrescente pelo total de vendas (ou seja, do mais alto para o mais baixo). A consulta então calcula a classificação de cada vendedor em relação a outros vendedores.

Comece criando a tabela e inserindo 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 é apresentada necessariamente por ordem de classificação. Você pode forçar a saída a ser exibida por ordem de classificação usando uma cláusula ORDER BY (fora da cláusula OVER), como mostrado abaixo:

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 ORDER BY cláusulas:

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

Funções de janela¶

Visão geral¶

O que é uma janela?¶

O que é uma função de janela?¶

Lista de funções que oferecem suporte a janelas¶

Funções de janela e valores NULL¶

Exemplo introdutório¶

O que é um quadro de janela?¶

Tipos de quadros de janela¶

O que é uma função relacionada à classificação?¶

Sintaxe e utilização da janela¶

Sintaxe da janela¶

Notas de uso da janela¶

Sintaxe e utilização do quadro de janela¶

Sintaxe do quadro de janela¶

Notas de uso dos quadros de janela¶

Sintaxe e utilização da função de janela relacionada à classificação¶

Sintaxe da função de janela relacionada à classificação¶

Notas de uso da função de janela relacionada à classificação¶

Dicas gerais¶

Exemplos¶

Exemplos de quadro de janela cumulativo¶

Exemplos de quadro de janela deslizante¶

Exemplos de funções relacionadas à classificação¶