Use AnalyticDB for MySQL window functions - AnalyticDB: Central de Documentação do Alibaba Cloud

Funções com suporte

Função	Categoria	Tipo de retorno	Descrição
Todas as funções de agregação	Agregação	Variável	As funções de agregação operam como funções de janela quando combinadas com uma cláusula `OVER`
`CUME_DIST()`	Classificação	DOUBLE	Distribuição cumulativa de cada valor dentro da partição
`RANK()`	Classificação	BIGINT	Classificação com lacunas para valores empatados
`DENSE_RANK()`	Classificação	BIGINT	Classificação sem lacunas para valores empatados
`NTILE(n)`	Classificação	BIGINT	Distribui as linhas em `n` buckets
`ROW_NUMBER()`	Classificação	BIGINT	Número sequencial da linha dentro da partição, começando em 1
`PERCENT_RANK()`	Classificação	DOUBLE	Classificação como porcentagem: `(r - 1) / (n - 1)`
`FIRST_VALUE(x)`	Valor	Igual à entrada	Valor da primeira linha na partição da janela
`LAST_VALUE(x)`	Valor	Igual à entrada	Valor da última linha no quadro da janela
`LAG(x[, offset[, default]])`	Valor	Igual à entrada	Valor de `offset` linhas anteriores à linha atual
`LEAD(x[, offset[, default]])`	Valor	Igual à entrada	Valor de `offset` linhas posteriores à linha atual
`NTH_VALUE(x, offset)`	Valor	Igual à entrada	Valor da linha na posição `offset` dentro do quadro da janela

Sintaxe

function_name OVER ([PARTITION BY expr] ORDER BY expr [RANGE|ROWS BETWEEN start AND end])

Uma chamada de função de janela tem três partes:

Regra de partição (opcional): Divide as linhas de entrada em partições independentes, semelhante ao GROUP BY. Cada partição é processada separadamente.
Regra de ordenação: Define a ordem de processamento das linhas dentro de cada partição.
Quadro da janela: Especifica o subconjunto de linhas da partição sobre o qual a função opera. O quadro tem como âncora a linha atual.

Modos de quadro da janela

Você pode definir o quadro da janela em dois modos:

Modo	Definição	Exemplo de uso
`ROWS`	Um número fixo de linhas em relação à linha atual	Totais acumulados, médias móveis
`RANGE`	Um intervalo de valores em relação ao valor da linha atual	Janelas deslizantes baseadas em valores

Use BETWEEN start AND end para definir os limites do quadro:

Limite	Significado
`CURRENT ROW`	A linha atual
`N PRECEDING`	`N` linhas antes da linha atual
`UNBOUNDED PRECEDING`	Da primeira linha da partição até a linha atual
`N FOLLOWING`	`N` linhas depois da linha atual
`UNBOUNDED FOLLOWING`	Da linha atual até a última linha da partição

O diagrama a seguir ilustra a relação dos limites com a linha atual dentro de uma partição:

      PARTITION
+─────────────────+  <- UNBOUNDED PRECEDING (start of partition)
|                 |
|=================|  <- N PRECEDING  -+
|   rows before   |                   |
|   current row   |                   |  FRAME
|~~~~~~~~~~~~~~~~~|  <- CURRENT ROW   |
|   rows after    |                   |
|   current row   |                   |
|=================|  <- N FOLLOWING  -+
|                 |
+─────────────────+  <- UNBOUNDED FOLLOWING (end of partition)

Comportamento padrão do quadro da janela

LAST_VALUE: O quadro padrão termina em CURRENT ROW, portanto, por padrão, LAST_VALUE retorna o valor da linha atual, e não o da última linha da partição. Para obter o último valor real, especifique explicitamente ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Especifique sempre o quadro da janela explicitamente para evitar resultados inesperados.

Configuração da tabela de exemplo

Os exemplos deste tópico usam uma tabela chamada testwindow. Execute as instruções abaixo para criá-la e preenchê-la:

CREATE TABLE testwindow (
  year    INT,
  country VARCHAR(20),
  product VARCHAR(20),
  profit  INT
) DISTRIBUTED BY HASH(year);

INSERT INTO testwindow VALUES (2000, 'Finland', 'Computer',   1500);
INSERT INTO testwindow VALUES (2001, 'Finland', 'Phone',        10);
INSERT INTO testwindow VALUES (2000, 'Germany', 'Calculator',   75);
INSERT INTO testwindow VALUES (2000, 'Germany', 'Calculator',   75);
INSERT INTO testwindow VALUES (2001, 'Germany', 'Calculator',   79);
INSERT INTO testwindow VALUES (2001, 'USA',     'Calculator',   50);
INSERT INTO testwindow VALUES (2001, 'USA',     'Computer',   1500);

Verifique os dados:

SELECT * FROM testwindow;

+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2000 | Germany | Calculator |     75 |
| 2000 | Germany | Calculator |     75 |
| 2001 | Germany | Calculator |     79 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
+------+---------+------------+--------+

Observações de uso

Restrições gerais de quadro

As combinações de BETWEEN start AND end listadas abaixo são inválidas:

Combinação inválida	Mensagem de erro
`start = UNBOUNDED FOLLOWING`	`Window frame start cannot be UNBOUNDED FOLLOWING`
`end = UNBOUNDED PRECEDING`	`Window frame end cannot be UNBOUNDED PRECEDING`
`start = CURRENT ROW`, `end = N PRECEDING`	`Window frame starting from CURRENT ROW cannot end with PRECEDING`
`start = N FOLLOWING`, `end = N PRECEDING`	`Window frame starting from FOLLOWING cannot end with PRECEDING`
`start = N FOLLOWING`, `end = CURRENT ROW`	`Window frame starting from FOLLOWING cannot end with CURRENT ROW`

Restrições do modo RANGE

No modo RANGE, apenas limites UNBOUNDED têm suporte:

Combinação inválida	Mensagem de erro
`start` ou `end = N PRECEDING`	`Window frame RANGE PRECEDING is only supported with UNBOUNDED`
`start` ou `end = N FOLLOWING`	`Window frame RANGE FOLLOWING is only supported with UNBOUNDED`

Funções de agregação

Qualquer função de agregação passa a atuar como função de janela ao receber uma cláusula OVER. Nesse cenário, a função calcula o resultado sobre as linhas da janela deslizante atual, em vez de consolidar todas as linhas em uma única saída.

O exemplo a seguir calcula uma soma móvel dos preços dos pedidos por data para cada funcionário:

SELECT
  clerk,
  orderdate,
  orderkey,
  totalprice,
  SUM(totalprice) OVER (PARTITION BY clerk ORDER BY orderdate) AS rolling_sum
FROM orders
ORDER BY clerk, orderdate, orderkey;

Este exemplo calcula uma soma cumulativa contínua de profit dentro de cada país:

SELECT
  year,
  country,
  profit,
  SUM(profit) OVER (
    PARTITION BY country
    ORDER BY year
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM testwindow;

+------+---------+--------+---------------+
| year | country | profit | running_total |
+------+---------+--------+---------------+
| 2001 | USA     |     50 |            50 |
| 2001 | USA     |   1500 |          1550 |
| 2000 | Germany |     75 |            75 |
| 2000 | Germany |     75 |           150 |
| 2001 | Germany |     79 |           229 |
| 2000 | Finland |   1500 |          1500 |
| 2001 | Finland |     10 |          1510 |
+------+---------+--------+---------------+

Sem ORDER BY e uma cláusula de quadro, a agregação abrange toda a partição:

SELECT country, SUM(profit) OVER (PARTITION BY country) AS total_profit
FROM testwindow;

+---------+--------------+
| country | total_profit |
+---------+--------------+
| Germany |          229 |
| Germany |          229 |
| Germany |          229 |
| USA     |         1550 |
| USA     |         1550 |
| Finland |         1510 |
| Finland |         1510 |
+---------+--------------+

CUME_DIST

CUME_DIST()

Retorna a distribuição cumulativa de cada valor dentro de uma partição, representando a fração de linhas com valores menores ou iguais ao valor da linha atual.

Tipo de retorno: DOUBLE
Valores empatados recebem o mesmo valor de distribuição.

Exemplo:

SELECT
  year,
  country,
  product,
  profit,
  CUME_DIST() OVER (PARTITION BY country ORDER BY profit) AS cume_dist
FROM testwindow;

+------+---------+------------+--------+--------------------+
| year | country | product    | profit | cume_dist          |
+------+---------+------------+--------+--------------------+
| 2001 | USA     | Calculator |     50 |                0.5 |
| 2001 | USA     | Computer   |   1500 |                1.0 |
| 2001 | Finland | Phone      |     10 |                0.5 |
| 2000 | Finland | Computer   |   1500 |                1.0 |
| 2000 | Germany | Calculator |     75 | 0.6666666666666666 |
| 2000 | Germany | Calculator |     75 | 0.6666666666666666 |
| 2001 | Germany | Calculator |     79 |                1.0 |
+------+---------+------------+--------+--------------------+

RANK

RANK()

Retorna a classificação de cada linha dentro de sua partição, ordenada pela expressão ORDER BY. A classificação corresponde ao número de linhas anteriores à linha atual mais um. Valores empatados recebem a mesma classificação, e a próxima classificação pula as posições correspondentes, gerando lacunas na sequência.

Tipo de retorno: BIGINT

Exemplo:

SELECT
  year,
  country,
  product,
  profit,
  RANK() OVER (PARTITION BY country ORDER BY profit) AS rank
FROM testwindow;

+------+---------+------------+--------+------+
| year | country | product    | profit | rank |
+------+---------+------------+--------+------+
| 2001 | Finland | Phone      |     10 |    1 |
| 2000 | Finland | Computer   |   1500 |    2 |
| 2001 | USA     | Calculator |     50 |    1 |
| 2001 | USA     | Computer   |   1500 |    2 |
| 2000 | Germany | Calculator |     75 |    1 |
| 2000 | Germany | Calculator |     75 |    1 |
| 2001 | Germany | Calculator |     79 |    3 |
+------+---------+------------+--------+------+

Na partição da Alemanha, as duas linhas com profit = 75 recebem ambas a classificação 1, e a linha seguinte salta para a classificação 3.

DENSE_RANK

DENSE_RANK()

Retorna a classificação de cada linha dentro de sua partição. Diferentemente de RANK(), valores empatados não geram lacunas; a próxima classificação é sempre consecutiva.

Tipo de retorno: BIGINT

Exemplo:

SELECT
  year,
  country,
  product,
  profit,
  DENSE_RANK() OVER (PARTITION BY country ORDER BY profit) AS dense_rank
FROM testwindow;

+------+---------+------------+--------+------------+
| year | country | product    | profit | dense_rank |
+------+---------+------------+--------+------------+
| 2001 | Finland | Phone      |     10 |          1 |
| 2000 | Finland | Computer   |   1500 |          2 |
| 2001 | USA     | Calculator |     50 |          1 |
| 2001 | USA     | Computer   |   1500 |          2 |
| 2000 | Germany | Calculator |     75 |          1 |
| 2000 | Germany | Calculator |     75 |          1 |
| 2001 | Germany | Calculator |     79 |          2 |
+------+---------+------------+--------+------------+

Na partição da Alemanha, as duas linhas com profit = 75 recebem ambas a classificação 1, e a linha seguinte recebe a classificação 2 (sem lacuna).

NTILE

NTILE(n)

Divide as linhas de cada partição em n buckets, numerados de 1 a n. Caso a divisão das linhas não seja exata, as linhas extras são distribuídas uma por bucket, começando pelo bucket 1.

Por exemplo, 6 linhas em 4 buckets resultam em: 1, 1, 2, 2, 3, 4.

Tipo de retorno: BIGINT

Exemplo (2 buckets por país):

SELECT
  year,
  country,
  product,
  profit,
  NTILE(2) OVER (PARTITION BY country ORDER BY profit) AS bucket
FROM testwindow;

+------+---------+------------+--------+--------+
| year | country | product    | profit | bucket |
+------+---------+------------+--------+--------+
| 2001 | USA     | Calculator |     50 |      1 |
| 2001 | USA     | Computer   |   1500 |      2 |
| 2001 | Finland | Phone      |     10 |      1 |
| 2000 | Finland | Computer   |   1500 |      2 |
| 2000 | Germany | Calculator |     75 |      1 |
| 2000 | Germany | Calculator |     75 |      1 |
| 2001 | Germany | Calculator |     79 |      2 |
+------+---------+------------+--------+--------+

ROW_NUMBER

ROW_NUMBER()

Atribui um inteiro sequencial exclusivo a cada linha dentro de sua partição, começando em 1. Ao contrário de RANK(), duas linhas jamais compartilham o mesmo número.

Tipo de retorno: BIGINT

Exemplo:

SELECT
  year,
  country,
  product,
  profit,
  ROW_NUMBER() OVER (PARTITION BY country) AS row_num
FROM testwindow;

+------+---------+------------+--------+---------+
| year | country | product    | profit | row_num |
+------+---------+------------+--------+---------+
| 2001 | USA     | Calculator |     50 |       1 |
| 2001 | USA     | Computer   |   1500 |       2 |
| 2000 | Germany | Calculator |     75 |       1 |
| 2000 | Germany | Calculator |     75 |       2 |
| 2001 | Germany | Calculator |     79 |       3 |
| 2000 | Finland | Computer   |   1500 |       1 |
| 2001 | Finland | Phone      |     10 |       2 |
+------+---------+------------+--------+---------+

PERCENT_RANK

PERCENT_RANK()

Retorna a classificação relativa de cada linha como um valor entre 0 e 1, usando a fórmula (r - 1) / (n - 1), onde r representa o RANK() da linha atual e n é o total de linhas na partição.

Tipo de retorno: DOUBLE

Exemplo:

SELECT
  year,
  country,
  product,
  profit,
  PERCENT_RANK() OVER (PARTITION BY country ORDER BY profit) AS pct_rank
FROM testwindow;

+------+---------+------------+--------+----------+
| year | country | product    | profit | pct_rank |
+------+---------+------------+--------+----------+
| 2001 | Finland | Phone      |     10 |      0.0 |
| 2000 | Finland | Computer   |   1500 |      1.0 |
| 2001 | USA     | Calculator |     50 |      0.0 |
| 2001 | USA     | Computer   |   1500 |      1.0 |
| 2000 | Germany | Calculator |     75 |      0.0 |
| 2000 | Germany | Calculator |     75 |      0.0 |
| 2001 | Germany | Calculator |     79 |      1.0 |
+------+---------+------------+--------+----------+

FIRST_VALUE

FIRST_VALUE(x)

Retorna o valor da primeira linha dentro da partição da janela.

Tipo de retorno: Igual ao tipo do argumento de entrada

Exemplo:

SELECT
  year,
  country,
  product,
  profit,
  FIRST_VALUE(profit) OVER (PARTITION BY country ORDER BY profit) AS first_profit
FROM testwindow;

+------+---------+------------+--------+--------------+
| year | country | product    | profit | first_profit |
+------+---------+------------+--------+--------------+
| 2000 | Germany | Calculator |     75 |           75 |
| 2000 | Germany | Calculator |     75 |           75 |
| 2001 | Germany | Calculator |     79 |           75 |
| 2001 | USA     | Calculator |     50 |           50 |
| 2001 | USA     | Computer   |   1500 |           50 |
| 2001 | Finland | Phone      |     10 |           10 |
| 2000 | Finland | Computer   |   1500 |           10 |
+------+---------+------------+--------+--------------+

LAST_VALUE

LAST_VALUE(x)

Retorna o valor da última linha dentro do quadro da janela. O quadro padrão é ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, logo, por padrão, LAST_VALUE retorna o valor da linha atual, e não o da última linha da partição.

Para retornar o último valor real da partição, adicione uma cláusula de quadro explícita:

LAST_VALUE(x) OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Tipo de retorno: Igual ao tipo do argumento de entrada

Exemplo 1 — quadro padrão (retorna o valor da linha atual):

SELECT
  year,
  country,
  product,
  profit,
  LAST_VALUE(profit) OVER (PARTITION BY country ORDER BY profit) AS last_val
FROM testwindow;

+------+---------+------------+--------+----------+
| year | country | product    | profit | last_val |
+------+---------+------------+--------+----------+
| 2001 | USA     | Calculator |     50 |       50 |
| 2001 | USA     | Computer   |   1500 |     1500 |
| 2001 | Finland | Phone      |     10 |       10 |
| 2000 | Finland | Computer   |   1500 |     1500 |
| 2000 | Germany | Calculator |     75 |       75 |
| 2000 | Germany | Calculator |     75 |       75 |
| 2001 | Germany | Calculator |     79 |       79 |
+------+---------+------------+--------+----------+

Exemplo 2 — quadro explícito de partição completa (retorna o valor da última linha):

SELECT
  year,
  country,
  product,
  profit,
  LAST_VALUE(profit) OVER (
    PARTITION BY country
    ORDER BY profit
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_val
FROM testwindow;

+------+---------+------------+--------+----------+
| year | country | product    | profit | last_val |
+------+---------+------------+--------+----------+
| 2001 | Finland | Phone      |     10 |     1500 |
| 2000 | Finland | Computer   |   1500 |     1500 |
| 2000 | Germany | Calculator |     75 |       79 |
| 2000 | Germany | Calculator |     75 |       79 |
| 2001 | Germany | Calculator |     79 |       79 |
| 2001 | USA     | Calculator |     50 |     1500 |
| 2001 | USA     | Computer   |   1500 |     1500 |
+------+---------+------------+--------+----------+

LAG

LAG(x[, offset[, default_value]])

Retorna o valor da linha que está offset linhas antes da linha atual dentro da partição.

Parâmetro	Descrição	Padrão
`x`	Coluna ou expressão a ser avaliada	—
`offset`	Quantidade de linhas para retroceder; `0` refere-se à linha atual	`1`
`default_value`	Valor retornado quando `offset` é `NULL` ou excede o tamanho da partição	`NULL`

Tipo de retorno: Igual ao tipo do argumento de entrada

Exemplo (retroceder 1 linha):

SELECT
  year,
  country,
  product,
  profit,
  LAG(profit) OVER (PARTITION BY country ORDER BY profit) AS prev_profit
FROM testwindow;

+------+---------+------------+--------+-------------+
| year | country | product    | profit | prev_profit |
+------+---------+------------+--------+-------------+
| 2001 | USA     | Calculator |     50 |        NULL |
| 2001 | USA     | Computer   |   1500 |          50 |
| 2000 | Germany | Calculator |     75 |        NULL |
| 2000 | Germany | Calculator |     75 |          75 |
| 2001 | Germany | Calculator |     79 |          75 |
| 2001 | Finland | Phone      |     10 |        NULL |
| 2000 | Finland | Computer   |   1500 |          10 |
+------+---------+------------+--------+-------------+

LEAD

LEAD(x[, offset[, default_value]])

Retorna o valor da linha que está offset linhas depois da linha atual dentro da partição.

Parâmetro	Descrição	Padrão
`x`	Coluna ou expressão a ser avaliada	—
`offset`	Quantidade de linhas para avançar; `0` refere-se à linha atual	`1`
`default_value`	Valor retornado quando `offset` é `NULL` ou excede o tamanho da partição	`NULL`

Tipo de retorno: Igual ao tipo do argumento de entrada

Exemplo (avançar 1 linha):

SELECT
  year,
  country,
  product,
  profit,
  LEAD(profit) OVER (PARTITION BY country ORDER BY profit) AS next_profit
FROM testwindow;

+------+---------+------------+--------+-------------+
| year | country | product    | profit | next_profit |
+------+---------+------------+--------+-------------+
| 2000 | Germany | Calculator |     75 |          75 |
| 2000 | Germany | Calculator |     75 |          79 |
| 2001 | Germany | Calculator |     79 |        NULL |
| 2001 | Finland | Phone      |     10 |        1500 |
| 2000 | Finland | Computer   |   1500 |        NULL |
| 2001 | USA     | Calculator |     50 |        1500 |
| 2001 | USA     | Computer   |   1500 |        NULL |
+------+---------+------------+--------+-------------+

NTH_VALUE

NTH_VALUE(x, offset)

Retorna o valor da linha na posição offset dentro do quadro da janela. O deslocamento começa em 1.

Se offset for NULL ou exceder o número de linhas no quadro, o retorno será NULL.
Caso offset seja 0 ou negativo, um erro será retornado.
Tipo de retorno: Igual ao tipo do argumento de entrada

Exemplo (primeira linha em cada partição, equivalente a FIRST_VALUE):

SELECT
  year,
  country,
  product,
  profit,
  NTH_VALUE(profit, 1) OVER (PARTITION BY country ORDER BY profit) AS nth_val
FROM testwindow;

+------+---------+------------+--------+---------+
| year | country | product    | profit | nth_val |
+------+---------+------------+--------+---------+
| 2001 | Finland | Phone      |     10 |      10 |
| 2000 | Finland | Computer   |   1500 |      10 |
| 2001 | USA     | Calculator |     50 |      50 |
| 2001 | USA     | Computer   |   1500 |      50 |
| 2000 | Germany | Calculator |     75 |      75 |
| 2000 | Germany | Calculator |     75 |      75 |
| 2001 | Germany | Calculator |     79 |      75 |
+------+---------+------------+--------+---------+

Próximos passos

Funções de agregação