MaxCompute:Visão geral das funções de janela

Observações de uso

• As funções de janela podem aparecer apenas em instruções SELECT.

• Não é permitido aninhar uma função de janela com outras funções de janela ou funções de agregação.

• Funções de janela e funções de agregação não podem ser usadas no mesmo nível.

Índice

O SQL do MaxCompute oferece suporte às seguintes funções de janela.

Sintaxe da função de janela

<function_name>([distinct][<expression> [, ...]]) over (<window_definition>)
<function_name>([distinct][<expression> [, ...]]) over <window_name>

• function_name: Uma função de janela integrada, uma aggregate function ou uma user-defined aggregate function (UDAF).

• expression: O formato da função, que deve estar em conformidade com a sintaxe dela.

• windowing_definition: A definição da janela. Para obter mais informações sobre a sintaxe, consulte windowing_definition.

• window_name: O nome da janela. Use a palavra-chave window para definir uma janela personalizada e atribuir um nome à windowing_definition. A sintaxe para uma definição de janela nomeada (named_window_def) é a seguinte:

  window <window_name> as (<window_definition>)

  A seguir estão as posições das instruções personalizadas no SQL:

  select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]

windowing_definition

--partition_clause:
[partition by <expression> [, ...]]
--orderby_clause:
[order by <expression> [asc|desc][nulls {first|last}] [, ...]]
[<frame_clause>]

Ao adicionar uma função de janela a uma instrução SELECT, os dados são particionados e classificados com base nas cláusulas partition by e order by da definição da janela. Se você não especificar uma cláusula partition by, todos os dados serão tratados como uma única partição. Caso não especifique uma cláusula order by, a ordem dos dados dentro de uma partição não será garantida. Para cada linha, denominada linha atual, um segmento de dados é extraído da partição com base na frame_clause para formar a janela dessa linha. Em seguida, a função de janela calcula um resultado para a linha atual com base nos dados de sua janela.

• partition by <expression> [, ...]: Opcional. Especifica a partição. Linhas com os mesmos valores de coluna de chave de partição pertencem à mesma partição. Para obter mais informações sobre o formato, consulte Table operations.

• order by <expression> [asc|desc][nulls {first|last}] [, ...]: Opcional. Define como os dados são classificados dentro de uma partição.

  Nota

  Se as linhas tiverem os mesmos valores de order by, a ordem de classificação não será garantida. Para assegurar uma ordem consistente, certifique-se de que os valores de order by sejam o mais exclusivos possível.

• frame_clause: Opcional. Define os limites da janela. Para obter mais informações sobre frame_clause, consulte frame_clause.

filter_clause

FILTER (WHERE filter_condition)

filter_condition é uma expressão booleana, utilizada da mesma forma que a cláusula WHERE em uma instrução select ... from ... where.

Se você fornecer uma cláusula FILTER, apenas as linhas para as quais a filter_condition for avaliada como verdadeira serão incluídas no quadro da janela. Para funções de janela de agregação (como COUNT, SUM, AVG, MAX e MIN), um valor ainda é retornado para cada linha. No entanto, as linhas em que a expressão FILTER não é avaliada como verdadeira (como NULL ou falso) não são incluídas no quadro da janela para o cálculo de cada linha. O valor NULL é tratado como falso.

Exemplo

frame_clause

-- Format 1
{ROWS|RANGE|GROUPS} <frame_start> [<frame_exclusion>]
-- Format 2
{ROWS|RANGE|GROUPS} between <frame_start> and <frame_end> [<frame_exclusion>]

• ROWS|RANGE|GROUPS: Obrigatório. O tipo da frame_clause. As regras de implementação para frame_start e frame_end variam conforme o tipo.

  • ROWS: Define os limites da janela com base no número de linhas.

  • RANGE: Define os limites da janela comparando os valores da coluna order by. Normalmente, uma cláusula order by é especificada na definição da janela. Se nenhuma cláusula order by for especificada, todas as linhas em uma partição terão o mesmo valor de coluna order by. Os valores NULL são considerados iguais.

  • GROUPS: Todas as linhas em uma partição com o mesmo valor de coluna order by formam um GROUP. Se nenhuma cláusula order by for especificada, todas as linhas da partição formarão um único GROUP. Os valores NULL são considerados iguais.

• frame_start e frame_end: Especificam os limites inicial e final da janela. O parâmetro frame_start é obrigatório. O parâmetro frame_end é opcional. Se omitido, o valor padrão será CURRENT ROW.

  A posição especificada por frame_start deve preceder a posição especificada por frame_end ou coincidir com a posição de frame_end. Em outras palavras, frame_start está mais próximo do início da partição do que frame_end. O início da partição corresponde à posição da primeira linha após a classificação dos dados pela instrução order by na definição da janela. A tabela a seguir descreve os valores válidos e a lógica para frame_start e frame_end quando o tipo de frame_clause é ROWS, RANGE ou GROUPS.

  Tipo de frame_clause	Valor de frame_start/frame_end	Descrição
  ROWS, RANGE, GROUPS	UNBOUNDED PRECEDING	A primeira linha da partição. A contagem começa em 1.
  	UNBOUNDED FOLLOWING	A última linha da partição.
  ROWS	CURRENT ROW	A posição da linha atual. Cada linha de dados corresponde a um resultado da função de janela. A linha atual é aquela para a qual o resultado da função de janela está sendo calculado.
  	offset PRECEDING	A posição que está offset linhas antes da linha atual, em direção ao início da partição. Por exemplo, 0 PRECEDING refere-se à linha atual, e 1 PRECEDING refere-se à linha anterior. O offset deve ser um inteiro não negativo.
  	offset FOLLOWING	A posição que está offset linhas após a linha atual, em direção ao final da partição. Por exemplo, 0 FOLLOWING refere-se à linha atual, e 1 FOLLOWING refere-se à próxima linha. O offset deve ser um inteiro não negativo.
  RANGE	CURRENT ROW	Como frame_start, refere-se à posição da primeira linha que tem o mesmo valor de coluna order by da linha atual. Como frame_end, refere-se à posição da última linha que tem o mesmo valor de coluna order by da linha atual.
  	offset PRECEDING	As posições de frame_start e frame_end dependem da sequência order by. Suponha que a janela esteja classificada por X. Xi representa o valor X da i-ésima linha e Xc representa o valor X da linha atual. As posições são descritas da seguinte forma: Quando o order by é ascendente: frame_start: A posição da primeira linha que satisfaz Xc - Xi <= offset. frame_end: A posição da última linha que satisfaz Xc - Xi >= offset. Quando o order by é descendente: frame_start: A posição da primeira linha que satisfaz Xi - Xc <= offset. frame_end: A posição da última linha que satisfaz Xi - Xc >= offset. Os tipos de dados suportados para a coluna order by são: TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, DATETIME, DATE e TIMESTAMP. A sintaxe para o offset de tipos de data é a seguinte: N: Representa N dias ou N segundos. Deve ser um inteiro não negativo. Para DATETIME e TIMESTAMP, representa N segundos. Para DATE, representa N dias. interval 'N' {YEAR\MONTH\DAY\HOUR\MINUTE\SECOND}: Representa N anos, meses, dias, horas, minutos ou segundos. Por exemplo, INTERVAL '3' YEAR representa 3 anos. INTERVAL 'N-M' YEAR TO MONTH: Representa N anos e M meses. Por exemplo, INTERVAL '1-3' YEAR TO MONTH representa 1 ano e 3 meses. INTERVAL 'D[ H[:M[:S[:N]]]]' DAY TO SECOND: Representa D dias, H horas, M minutos, S segundos e N nanossegundos. Por exemplo, INTERVAL '1 2:3:4:5' DAY TO SECOND representa 1 dia, 2 horas, 3 minutos, 4 segundos e 5 nanossegundos.
  	offset FOLLOWING	As posições de frame_start e frame_end dependem da sequência order by. Suponha que a janela esteja classificada por X. Xi representa o valor X da i-ésima linha e Xc representa o valor X da linha atual. As posições são descritas da seguinte forma: Quando o order by é ascendente: frame_start: A posição da primeira linha que satisfaz Xi - Xc >= offset. frame_end: A posição da última linha que satisfaz Xi - Xc <= offset. Quando o order by é descendente: frame_start: A posição da primeira linha que satisfaz Xc - Xi >= offset. frame_end: A posição da última linha que satisfaz Xc - Xi <= offset.
  GROUPS	CURRENT ROW	Como frame_start, refere-se à primeira linha do GROUP ao qual a linha atual pertence. Como frame_end, refere-se à última linha do GROUP ao qual a linha atual pertence.
  	offset PRECEDING	Como frame_start, refere-se à posição da primeira linha no GROUP que está offset GROUPs antes do GROUP da linha atual, em direção ao início da partição. Como frame_end, refere-se à posição da última linha no GROUP que está offset GROUPs antes do GROUP da linha atual, em direção ao início da partição. Nota Não é possível definir frame_start como UNBOUNDED FOLLOWING ou frame_end como UNBOUNDED PRECEDING.
  	offset FOLLOWING	Como frame_start, refere-se à posição da primeira linha no GROUP que está offset GROUPs após o GROUP da linha atual, em direção ao final da partição. Como frame_end, refere-se à posição da última linha no GROUP que está offset GROUPs após o GROUP da linha atual, em direção ao final da partição. Nota Não é possível definir frame_start como UNBOUNDED FOLLOWING ou frame_end como UNBOUNDED PRECEDING.

• frame_exclusion: Opcional. Usado para excluir uma parte dos dados da janela. Os valores válidos são:

  • EXCLUDE NO OTHERS: Não exclui nenhum dado.

  • EXCLUDE CURRENT ROW: Exclui a linha atual.

  • EXCLUDE GROUP: Exclui todo o GROUP, ou seja, todos os dados na partição que têm o mesmo valor de order by da linha atual.

  • EXCLUDE TIES: Exclui todas as linhas que compartilham o mesmo valor de order by da linha atual, exceto a própria linha atual.

frame_clause padrão

Exemplos de limites de janela

Instruções

1. Leia o inglês para entender O QUE precisa ser comunicado

2. Escreva o português brasileiro DO ZERO — esqueça a estrutura da frase em inglês

3. Preserve toda a formatação markdown, blocos de código, links e imagens exatamente como estão

4. Placeholders xref ({XREF_N}) — copie literalmente, NÃO traduza nem modifique

5. Aplique todas as regras específicas do idioma rigorosamente

6. Aplique as regras de stopwords com tolerância zero

7. Use o modo imperativo em etapas numeradas e listas de procedimentos

8. Garanta a consistência da terminologia — mesmo termo = mesma tradução em todos os lugares

9. Varie os inícios das frases em listas/tabelas — nenhum iniciador repetido mais de 3 vezes

10. Retorne APENAS o documento markdown em português brasileiro, sem explicações

    Considere que uma tabela tbl possua a estrutura pid: bigint, oid: bigint, rid: bigint e contenha os seguintes dados:

    +------------+------------+------------+
    | pid        | oid        | rid        |
    +------------+------------+------------+
    | 1          | NULL       | 1          |
    | 1          | NULL       | 2          |
    | 1          | 1          | 3          |
    | 1          | 1          | 4          |
    | 1          | 2          | 5          |
    | 1          | 4          | 6          |
    | 1          | 7          | 7          |
    | 1          | 11         | 8          |
    | 2          | NULL       | 9          |
    | 2          | NULL       | 10         |
    +------------+------------+------------+

    • Janela do tipo ROW

      • Definição de janela 1

        partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW
        --SQL statement is as follows.
        select pid, 
               oid, 
               rid, 
        collect_list(rid) over(partition by pid order by 
        oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

        O resultado retornado é:

        +------------+------------+------------+--------+
        | pid        | oid        | rid        | window |
        +------------+------------+------------+--------+
        | 1          | NULL       | 1          | [1]    |
        | 1          | NULL       | 2          | [1, 2] |
        | 1          | 1          | 3          | [1, 2, 3] |
        | 1          | 1          | 4          | [1, 2, 3, 4] |
        | 1          | 2          | 5          | [1, 2, 3, 4, 5] |
        | 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
        | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
        | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 2          | NULL       | 9          | [9]    |
        | 2          | NULL       | 10         | [9, 10] |
        +------------+------------+------------+--------+

      • Definição de janela 2

        partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
        --SQL statement is as follows.
        select pid, 
               oid, 
               rid, 
        collect_list(rid) over(partition by pid order by 
        oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as window from tbl;

        O resultado retornado é:

        +------------+------------+------------+--------+
        | pid        | oid        | rid        | window |
        +------------+------------+------------+--------+
        | 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 1          | 1          | 3          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 1          | 1          | 4          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 1          | 2          | 5          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 1          | 4          | 6          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 2          | NULL       | 9          | [9, 10] |
        | 2          | NULL       | 10         | [9, 10] |
        +------------+------------+------------+--------+

      • Definição de janela 3

        partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING
        --SQL statement is as follows.
        select pid, 
               oid, 
               rid, 
        collect_list(rid) over(partition by pid order by 
        oid ROWS between 1 FOLLOWING and 3 FOLLOWING) as window from tbl;

        O resultado retornado é:

        +------------+------------+------------+--------+
        | pid        | oid        | rid        | window |
        +------------+------------+------------+--------+
        | 1          | NULL       | 1          | [2, 3, 4] |
        | 1          | NULL       | 2          | [3, 4, 5] |
        | 1          | 1          | 3          | [4, 5, 6] |
        | 1          | 1          | 4          | [5, 6, 7] |
        | 1          | 2          | 5          | [6, 7, 8] |
        | 1          | 4          | 6          | [7, 8] |
        | 1          | 7          | 7          | [8]    |
        | 1          | 11         | 8          | NULL   |
        | 2          | NULL       | 9          | [10]   |
        | 2          | NULL       | 10         | NULL   |
        +------------+------------+------------+--------+

      • Definição de janela 4

        partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW
        --SQL statement is as follows.
        select pid, 
        oid, 
        rid, 
        collect_list(rid) over(partition by pid order by 
        oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW) as window from tbl;

        O resultado retornado é:

        +------------+------------+------------+--------+
        | pid        | oid        | rid        | window |
        +------------+------------+------------+--------+
        | 1          | NULL       | 1          | NULL   |
        | 1          | NULL       | 2          | [1]    |
        | 1          | 1          | 3          | [1, 2] |
        | 1          | 1          | 4          | [1, 2, 3] |
        | 1          | 2          | 5          | [1, 2, 3, 4] |
        | 1          | 4          | 6          | [1, 2, 3, 4, 5] |
        | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
        | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
        | 2          | NULL       | 9          | NULL   |
        | 2          | NULL       | 10         | [9]    |
        +------------+------------+------------+--------+

      • Definição de janela 5

        partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP
        --SQL statement is as follows.
        select pid, 
               oid, 
               rid, 
        collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP) as window from tbl;

        O resultado retornado é:

        +------------+------------+------------+--------+
        | pid        | oid        | rid        | window |
        +------------+------------+------------+--------+
        | 1          | NULL       | 1          | NULL   |
        | 1          | NULL       | 2          | NULL   |
        | 1          | 1          | 3          | [1, 2] |
        | 1          | 1          | 4          | [1, 2] |
        | 1          | 2          | 5          | [1, 2, 3, 4] |
        | 1          | 4          | 6          | [1, 2, 3, 4, 5] |
        | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
        | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
        | 2          | NULL       | 9          | NULL   |
        | 2          | NULL       | 10         | NULL   |
        +------------+------------+------------+--------+

      • Definição de janela 6

        partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES
        --SQL statement is as follows.
        select pid, 
               oid, 
               rid, 
        collect_list(rid) over(partition by pid order by 
        oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES) as window from tbl;                            

        O resultado retornado é:

        +------------+------------+------------+--------+
        | pid        | oid        | rid        | window |
        +------------+------------+------------+--------+
        | 1          | NULL       | 1          | [1]    |
        | 1          | NULL       | 2          | [2]    |
        | 1          | 1          | 3          | [1, 2, 3] |
        | 1          | 1          | 4          | [1, 2, 4] |
        | 1          | 2          | 5          | [1, 2, 3, 4, 5] |
        | 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
        | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
        | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 2          | NULL       | 9          | [9]    |
        | 2          | NULL       | 10         | [10]   |
        +------------+------------+------------+--------+

        Ao comparar os resultados da coluna window para as linhas onde rid é 2, 4 e 10 neste exemplo e no anterior, observa-se a diferença entre EXCLUDE CURRENT ROW e EXCLUDE GROUP. No caso de EXCLUDE GROUP, dentro da mesma partição (onde pid é igual), todos os dados com o mesmo valor de oid da linha atual são excluídos.

    • Janela do tipo RANGE

      • Definição de janela 1

        partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW
        --SQL statement is as follows.
        select pid, 
               oid, 
               rid, 
        collect_list(rid) over(partition by pid order by 
        oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

        O resultado retornado é:

        +------------+------------+------------+--------+
        | pid        | oid        | rid        | window |
        +------------+------------+------------+--------+
        | 1          | NULL       | 1          | [1, 2] |
        | 1          | NULL       | 2          | [1, 2] |
        | 1          | 1          | 3          | [1, 2, 3, 4] |
        | 1          | 1          | 4          | [1, 2, 3, 4] |
        | 1          | 2          | 5          | [1, 2, 3, 4, 5] |
        | 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
        | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
        | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 2          | NULL       | 9          | [9, 10] |
        | 2          | NULL       | 10         | [9, 10] |
        +------------+------------+------------+--------+

        Quando CURRENT ROW é usado como frame_end, a janela inclui todas as linhas até a última linha que possui o mesmo valor de order by (coluna oid) da linha atual. Por isso, o resultado da coluna window para o registro onde rid é 1 resulta em [1, 2].

      • Definição de janela 2

        partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING
        --SQL statement is as follows.
        select pid, 
               oid, 
               rid, 
        collect_list(rid) over(partition by pid order by 
        oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING) as window from tbl;

        O resultado retornado é:

        +------------+------------+------------+--------+
        | pid        | oid        | rid        | window |
        +------------+------------+------------+--------+
        | 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
        | 1          | 1          | 3          | [3, 4, 5, 6, 7, 8] |
        | 1          | 1          | 4          | [3, 4, 5, 6, 7, 8] |
        | 1          | 2          | 5          | [5, 6, 7, 8] |
        | 1          | 4          | 6          | [6, 7, 8] |
        | 1          | 7          | 7          | [7, 8] |
        | 1          | 11         | 8          | [8]    |
        | 2          | NULL       | 9          | [9, 10] |
        | 2          | NULL       | 10         | [9, 10] |
        +------------+------------+------------+--------+

      • Definição de janela 3

        partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING
        --SQL statement is as follows.
        
        select pid, 
               oid, 
               rid, 
        collect_list(rid) over(partition by pid order by 
        oid RANGE between 3 PRECEDING and 1 PRECEDING) as window from tbl;

        O resultado retornado é:

        +------------+------------+------------+--------+
        | pid        | oid        | rid        | window |
        +------------+------------+------------+--------+
        | 1          | NULL       | 1          | [1, 2] |
        | 1          | NULL       | 2          | [1, 2] |
        | 1          | 1          | 3          | NULL   |
        | 1          | 1          | 4          | NULL   |
        | 1          | 2          | 5          | [3, 4] |
        | 1          | 4          | 6          | [3, 4, 5] |
        | 1          | 7          | 7          | [6]    |
        | 1          | 11         | 8          | NULL   |
        | 2          | NULL       | 9          | [9, 10] |
        | 2          | NULL       | 10         | [9, 10] |
        +------------+------------+------------+--------+

        Para linhas em que o valor de order by (coluna oid) é NULL, se você utilizar offset {PRECEDING|FOLLOWING} e o offset não for UNBOUNDED, o limite é determinado da seguinte forma: quando usado como frame_start, aponta para a primeira linha com valor NULL na cláusula order by da partição. Quando usado como frame_end, aponta para a última linha com valor NULL na cláusula order by.

    • Janela do tipo GROUPS

      A definição da janela é:

      partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW
      --SQL statement is as follows.
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid GROUPS between 2 PRECEDING and CURRENT ROW) as window from tbl;

      O resultado retornado é:

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1, 2] |
      | 1          | NULL       | 2          | [1, 2] |
      | 1          | 1          | 3          | [1, 2, 3, 4] |
      | 1          | 1          | 4          | [1, 2, 3, 4] |
      | 1          | 2          | 5          | [1, 2, 3, 4, 5] |
      | 1          | 4          | 6          | [3, 4, 5, 6] |
      | 1          | 7          | 7          | [5, 6, 7] |
      | 1          | 11         | 8          | [6, 7, 8] |
      | 2          | NULL       | 9          | [9, 10] |
      | 2          | NULL       | 10         | [9, 10] |
      +------------+------------+------------+--------+

Dados de exemplo

create table if not exists emp
   (empno bigint,
    ename string,
    job string,
    mgr bigint,
    hiredate datetime,
    sal bigint,
    comm bigint,
    deptno bigint);
tunnel upload emp.txt emp;

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

AVG

• Sintaxe

  double avg([distinct] double <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
  decimal avg([distinct] decimal <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

• Descrição

  Retorna o valor médio de expr em uma janela.

• Parâmetros

  • expr: Obrigatório. A expressão para a qual se deseja calcular o resultado. Deve ser do tipo DOUBLE ou DECIMAL.

    • Se o valor de entrada for do tipo STRING ou BIGINT, ele será convertido implicitamente para DOUBLE para o cálculo. Outros tipos de dados retornam erro.

    • Caso o valor de entrada seja NULL, a linha não entra no cálculo.

    • Ao especificar a palavra-chave distinct, a função calcula a média apenas dos valores únicos.

  • partition_clause, orderby_clause e frame_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  Se expr for do tipo DECIMAL, retorna um valor DECIMAL. Caso contrário, retorna um valor DOUBLE. Se todos os valores de expr forem NULL, o retorno será NULL.

• Exemplos

  • Exemplo 1: Particione por departamento (deptno) e calcule a média salarial (sal) sem ordenação. A função calcula a média salarial de toda a partição (todas as linhas com o mesmo deptno). O comando é:

    select deptno, sal, avg(sal) over (partition by deptno) from emp;

    O resultado retornado é:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 2916.6666666666665 |   -- This is the first row of the window. The value is the cumulative average from the first to the sixth row.
    | 10         | 2450       | 2916.6666666666665 |   -- The value is the cumulative average from the first to the sixth row.
    | 10         | 5000       | 2916.6666666666665 |   -- The value is the cumulative average from the first to the sixth row.
    | 10         | 1300       | 2916.6666666666665 |
    | 10         | 5000       | 2916.6666666666665 |
    | 10         | 2450       | 2916.6666666666665 |
    | 20         | 3000       | 2175.0     |
    | 20         | 3000       | 2175.0     |
    | 20         | 800        | 2175.0     |
    | 20         | 1100       | 2175.0     |
    | 20         | 2975       | 2175.0     |
    | 30         | 1500       | 1566.6666666666667 |
    | 30         | 950        | 1566.6666666666667 |
    | 30         | 1600       | 1566.6666666666667 |
    | 30         | 1250       | 1566.6666666666667 |
    | 30         | 1250       | 1566.6666666666667 |
    | 30         | 2850       | 1566.6666666666667 |
    +------------+------------+------------+

  • Exemplo 2: No modo não compatível com Hive, particione por departamento (deptno), ordene por salário (sal) e calcule a média salarial. A função calcula uma média acumulada desde a primeira linha da partição até a linha atual. Os comandos são:

    -- Disable Hive compatible mode.
    set odps.sql.hive.compatible=false;
    -- Execute the following SQL command.
    select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

    O resultado retornado é:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 1300.0     |           -- First row of the window.
    | 10         | 1300       | 1300.0     |           -- Cumulative average from the first to the second row.
    | 10         | 2450       | 1683.3333333333333 |   -- Cumulative average from the first to the third row.
    | 10         | 2450       | 1875.0     |           -- Cumulative average from the first to the fourth row.
    | 10         | 5000       | 2500.0     |           -- Cumulative average from the first to the fifth row.
    | 10         | 5000       | 2916.6666666666665 |   -- Cumulative average from the first to the sixth row.
    | 20         | 800        | 800.0      |
    | 20         | 1100       | 950.0      |
    | 20         | 2975       | 1625.0     |
    | 20         | 3000       | 1968.75    |
    | 20         | 3000       | 2175.0     |
    | 30         | 950        | 950.0      |
    | 30         | 1250       | 1100.0     |
    | 30         | 1250       | 1150.0     |
    | 30         | 1500       | 1237.5     |
    | 30         | 1600       | 1310.0     |
    | 30         | 2850       | 1566.6666666666667 |
    +------------+------------+------------+

  • Exemplo 3: No modo compatível com Hive, particione por departamento (deptno), ordene por salário (sal) e calcule a média salarial. A função calcula uma média acumulada da primeira linha da partição até o último par da linha atual (linhas com o mesmo sal possuem a mesma média). Os comandos são:

    -- Enable Hive compatible mode.
    set odps.sql.hive.compatible=true;
    -- Execute the following SQL command.
    select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

    O resultado retornado é:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 1300.0     |          -- First row of the window. Since the sal of the first and second rows are the same, the average for the first row is the cumulative average of the first two rows.
    | 10         | 1300       | 1300.0     |          -- Cumulative average from the first to the second row.
    | 10         | 2450       | 1875.0     |          -- Since the sal of the third and fourth rows are the same, the average for the third row is the cumulative average of the first four rows.
    | 10         | 2450       | 1875.0     |          -- Cumulative average from the first to the fourth row.
    | 10         | 5000       | 2916.6666666666665 |
    | 10         | 5000       | 2916.6666666666665 |
    | 20         | 800        | 800.0      |
    | 20         | 1100       | 950.0      |
    | 20         | 2975       | 1625.0     |
    | 20         | 3000       | 2175.0     |
    | 20         | 3000       | 2175.0     |
    | 30         | 950        | 950.0      |
    | 30         | 1250       | 1150.0     |
    | 30         | 1250       | 1150.0     |
    | 30         | 1500       | 1237.5     |
    | 30         | 1600       | 1310.0     |
    | 30         | 2850       | 1566.6666666666667 |
    +------------+------------+------------+

CLUSTER_SAMPLE

• Sintaxe

  boolean cluster_sample(bigint <N>) OVER ([partition_clause])
  boolean cluster_sample(bigint <N>, bigint <M>) OVER ([partition_clause])

• Descrição

  • cluster_sample(bigint <N>): Amostra N linhas aleatoriamente da partição.

  • cluster_sample(bigint <N>, bigint <M>): Amostra aleatoriamente uma fração de linhas (M/N) da partição. O número de linhas amostradas é aproximadamente partition_row_count × M / N, onde partition_row_count representa a quantidade de linhas na partição.

• Parâmetros

  • N: Obrigatório. Uma constante BIGINT. Se N for NULL, o valor retornado será NULL.

  • M: Obrigatório. Uma constante BIGINT. Se M for NULL, o valor retornado será NULL.

  • partition_clause: Opcional. Para mais informações, consulte windowing_definition.

• Valor de retorno

  Retorna um valor BOOLEAN.

• Exemplo

  Para amostrar aproximadamente 20% das linhas de cada grupo, utilize o seguinte comando:

  select deptno, sal
      from (
          select deptno, sal, cluster_sample(5, 1) over (partition by deptno) as flag
          from emp
          ) sub
      where flag = true;

  O resultado retornado é:

  +------------+------------+
  | deptno     | sal        |
  +------------+------------+
  | 10         | 1300       |
  | 20         | 3000       |
  | 30         | 950        |
  +------------+------------+

COUNT

-- Count the number of records.
BIGINT COUNT([DISTINCT|ALL] <colname>)

-- Count the number of records in the window.
BIGINT COUNT(*) OVER ([partition_clause] [orderby_clause] [frame_clause])
BIGINT COUNT([DISTINCT] <expr>[,...]) OVER ([partition_clause] [orderby_clause] [frame_clause])

SELECT sal, COUNT(sal) OVER (PARTITION BY sal) AS count
FROM emp;

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- Two rows share sal=1250; both return 2.
| 1250       | 2          |
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

-- Disable Hive compatible mode.
SET odps.sql.hive.compatible=false;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 1          |  -- Running count starts at 1 for the first row in the partition.
| 1250       | 2          |  -- Increments to 2 for the second row.
| 1300       | 1          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 1          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 1          |
| 3000       | 2          |
| 5000       | 1          |
| 5000       | 2          |
+------------+------------+

-- Enable Hive compatible mode.
SET odps.sql.hive.compatible=true;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- Both rows in the partition return the full partition count.
| 1250       | 2          |
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

SELECT COUNT(*) FROM emp;

+------------+
| _c0        |
+------------+
| 17         |
+------------+

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 20         | 5          |
| 30         | 6          |
| 10         | 6          |
+------------+------------+

SELECT COUNT(DISTINCT deptno) FROM emp;

+------------+
| _c0        |
+------------+
| 3          |
+------------+

CUME_DIST

• Sintaxe

  double cume_dist() over([partition_clause] [orderby_clause])

• Descrição

  Calcula a distribuição cumulativa de um valor dentro de um grupo de valores. O resultado corresponde ao número de linhas com valores menores ou iguais ao valor da linha atual, dividido pelo número total de linhas na partição. A comparação é determinada pela orderby_clause.

• Parâmetros

  partition_clause e orderby_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  Retorna um valor DOUBLE. O valor específico retornado equivale a row_number_of_last_peer / partition_row_count, onde row_number_of_last_peer é o valor retornado pela função de janela ROW_NUMBER para a última linha do GRUPO da linha atual, e partition_row_count é o número de linhas na partição à qual a linha pertence.

• Exemplo

  Particione por departamento (deptno) e calcule a distribuição cumulativa do salário (sal) dentro de cada departamento. O comando é:

  select deptno, ename, sal, concat(round(cume_dist() over (partition by deptno order by sal desc)*100,2),'%') as cume_dist from emp;

  O resultado retornado é:

  +------------+------------+------------+------------+
  | deptno     | ename      | sal        | cume_dist  |
  +------------+------------+------------+------------+
  | 10         | JACCKA     | 5000       | 33.33%     |
  | 10         | KING       | 5000       | 33.33%     |
  | 10         | CLARK      | 2450       | 66.67%     |
  | 10         | WELAN      | 2450       | 66.67%     |
  | 10         | TEBAGE     | 1300       | 100.0%     |
  | 10         | MILLER     | 1300       | 100.0%     |
  | 20         | SCOTT      | 3000       | 40.0%      |
  | 20         | FORD       | 3000       | 40.0%      |
  | 20         | JONES      | 2975       | 60.0%      |
  | 20         | ADAMS      | 1100       | 80.0%      |
  | 20         | SMITH      | 800        | 100.0%     |
  | 30         | BLAKE      | 2850       | 16.67%     |
  | 30         | ALLEN      | 1600       | 33.33%     |
  | 30         | TURNER     | 1500       | 50.0%      |
  | 30         | MARTIN     | 1250       | 83.33%     |
  | 30         | WARD       | 1250       | 83.33%     |
  | 30         | JAMES      | 950        | 100.0%     |
  +------------+------------+------------+------------+

DENSE_RANK

• Sintaxe

  bigint dense_rank() over ([partition_clause] [orderby_clause])

• Descrição

  Calcula a classificação da linha atual dentro de sua partição com base na ordem de classificação especificada na orderby_clause. A classificação começa em 1. Linhas com os mesmos valores de order by em uma partição recebem a mesma classificação. A classificação incrementa em 1 sempre que o valor de order by muda.

• Parâmetros

  partition_clause e orderby_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  Retorna um valor BIGINT. Se nenhuma orderby_clause for especificada, todas as linhas recebem classificação 1.

• Exemplo

  Particione por departamento (deptno) e classifique os funcionários dentro de cada departamento com base em seu salário (sal) em ordem decrescente. O comando é:

  select deptno, ename, sal, dense_rank() over (partition by deptno order by sal desc) as nums from emp;

  O resultado retornado é:

  +------------+------------+------------+------------+
  | deptno     | ename      | sal        | nums       |
  +------------+------------+------------+------------+
  | 10         | JACCKA     | 5000       | 1          |
  | 10         | KING       | 5000       | 1          |
  | 10         | CLARK      | 2450       | 2          |
  | 10         | WELAN      | 2450       | 2          |
  | 10         | TEBAGE     | 1300       | 3          |
  | 10         | MILLER     | 1300       | 3          |
  | 20         | SCOTT      | 3000       | 1          |
  | 20         | FORD       | 3000       | 1          |
  | 20         | JONES      | 2975       | 2          |
  | 20         | ADAMS      | 1100       | 3          |
  | 20         | SMITH      | 800        | 4          |
  | 30         | BLAKE      | 2850       | 1          |
  | 30         | ALLEN      | 1600       | 2          |
  | 30         | TURNER     | 1500       | 3          |
  | 30         | MARTIN     | 1250       | 4          |
  | 30         | WARD       | 1250       | 4          |
  | 30         | JAMES      | 950        | 5          |
  +------------+------------+------------+------------+

FIRST_VALUE

• Sintaxe

  first_value(<expr>[, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])

• Descrição

  Retorna o valor da expressão expr da primeira linha do frame da janela.

• Parâmetros

  • expr: Obrigatório. A expressão para a qual se deseja calcular o resultado.

  • ignore_nulls: Opcional. Um valor BOOLEAN que especifica se valores NULL devem ser ignorados. O valor padrão é False. Se este parâmetro for definido como True, a função retorna o primeiro valor não NULL de expr no frame da janela.

  • partition_clause, orderby_clause e frame_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  O valor de retorno possui o mesmo tipo de dados que expr.

• Exemplo

  O comando a seguir agrupa todos os funcionários por departamento e retorna a primeira linha de dados de cada grupo:

  • Sem especificar order by:

    select deptno, ename, sal, first_value(sal) over (partition by deptno) as first_value from emp;

    O seguinte resultado é retornado:

    +------------+------------+------------+-------------+
    | deptno     | ename      | sal        | first_value |
    +------------+------------+------------+-------------+
    | 10         | TEBAGE     | 1300       | 1300        |   -- First row of the current window.
    | 10         | CLARK      | 2450       | 1300        |
    | 10         | KING       | 5000       | 1300        |
    | 10         | MILLER     | 1300       | 1300        |
    | 10         | JACCKA     | 5000       | 1300        |
    | 10         | WELAN      | 2450       | 1300        |
    | 20         | FORD       | 3000       | 3000        |   -- First row of the current window.
    | 20         | SCOTT      | 3000       | 3000        |
    | 20         | SMITH      | 800        | 3000        |
    | 20         | ADAMS      | 1100       | 3000        |
    | 20         | JONES      | 2975       | 3000        |
    | 30         | TURNER     | 1500       | 1500        |   -- First row of the current window.
    | 30         | JAMES      | 950        | 1500        |
    | 30         | ALLEN      | 1600       | 1500        |
    | 30         | WARD       | 1250       | 1500        |
    | 30         | MARTIN     | 1250       | 1500        |
    | 30         | BLAKE      | 2850       | 1500        |
    +------------+------------+------------+-------------+

  • Especificando order by:

    select deptno, ename, sal, first_value(sal) over (partition by deptno order by sal desc) as first_value from emp;

    O seguinte resultado é retornado:

    +------------+------------+------------+-------------+
    | deptno     | ename      | sal        | first_value |
    +------------+------------+------------+-------------+
    | 10         | JACCKA     | 5000       | 5000        |   -- First row of the current window.
    | 10         | KING       | 5000       | 5000        |
    | 10         | CLARK      | 2450       | 5000        |
    | 10         | WELAN      | 2450       | 5000        |
    | 10         | TEBAGE     | 1300       | 5000        |
    | 10         | MILLER     | 1300       | 5000        |
    | 20         | SCOTT      | 3000       | 3000        |   -- First row of the current window.
    | 20         | FORD       | 3000       | 3000        |
    | 20         | JONES      | 2975       | 3000        |
    | 20         | ADAMS      | 1100       | 3000        |
    | 20         | SMITH      | 800        | 3000        |
    | 30         | BLAKE      | 2850       | 2850        |   -- First row of the current window.
    | 30         | ALLEN      | 1600       | 2850        |
    | 30         | TURNER     | 1500       | 2850        |
    | 30         | MARTIN     | 1250       | 2850        |
    | 30         | WARD       | 1250       | 2850        |
    | 30         | JAMES      | 950        | 2850        |
    +------------+------------+------------+-------------+

LAG

• Sintaxe

  lag(<expr>[,bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)

• Descrição

  Retorna o valor da expressão expr da linha que está offset linhas antes da linha atual (em direção ao início da partição). A expressão expr pode ser uma coluna, uma operação de coluna ou uma operação de função.

• Parâmetros

  • expr: Obrigatório. A expressão a ser calculada.

  • offset: Opcional. O deslocamento, que é uma constante BIGINT maior ou igual a 1. O valor 1 indica a linha anterior. O valor padrão é 1. Se o valor de entrada for do tipo STRING ou DOUBLE, ele será convertido implicitamente para o tipo BIGINT para cálculo.

  • default: Opcional. Especifica um valor padrão a ser retornado quando o offset estiver fora dos limites. Se este parâmetro não for especificado, o padrão será NULL. O valor deve ser uma constante com o mesmo tipo de dados que expr. Se expr não for uma constante, esse valor será avaliado com base na linha atual.

  • partition_clause e orderby_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  O valor de retorno possui o mesmo tipo de dados que expr.

• Exemplo

  Agrupe por departamento (deptno) e recupere o salário (sal) da linha anterior para cada funcionário. O comando é o seguinte:

  select deptno, ename, sal, lag(sal, 1) over (partition by deptno order by sal) as sal_new from emp;

  O seguinte resultado é retornado:

  +------------+------------+------------+------------+
  | deptno     | ename      | sal        | sal_new    |
  +------------+------------+------------+------------+
  | 10         | TEBAGE     | 1300       | NULL       |
  | 10         | MILLER     | 1300       | 1300       |
  | 10         | CLARK      | 2450       | 1300       |
  | 10         | WELAN      | 2450       | 2450       |
  | 10         | KING       | 5000       | 2450       |
  | 10         | JACCKA     | 5000       | 5000       |
  | 20         | SMITH      | 800        | NULL       |
  | 20         | ADAMS      | 1100       | 800        |
  | 20         | JONES      | 2975       | 1100       |
  | 20         | SCOTT      | 3000       | 2975       |
  | 20         | FORD       | 3000       | 3000       |
  | 30         | JAMES      | 950        | NULL       |
  | 30         | MARTIN     | 1250       | 950        |
  | 30         | WARD       | 1250       | 1250       |
  | 30         | TURNER     | 1500       | 1250       |
  | 30         | ALLEN      | 1600       | 1500       |
  | 30         | BLAKE      | 2850       | 1600       |
  +------------+------------+------------+------------+

LAST_VALUE

• Sintaxe

  last_value(<expr>[, <ignore_nulls>]) over([partition_clause] [orderby_clause] [frame_clause])

• Descrição

  Retorna o valor da expressão expr da última linha do frame da janela.

Parâmetros

• expr: Obrigatório. A expressão a ser calculada.

• ignore_nulls: Opcional. Um valor BOOLEAN que especifica se valores NULL devem ser ignorados. O valor padrão é False. Se este parâmetro for definido como True, a função retorna o último valor não NULL de expr no frame da janela.

• partition_clause, orderby_clause e frame_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  O valor de retorno possui o mesmo tipo de dados que expr.

• Exemplo

  O comando a seguir agrupa todos os funcionários por departamento e retorna a última linha de dados de cada grupo:

  • Sem uma cláusula order by, o frame da janela inclui todas as linhas da partição. A função retorna o valor da última linha na partição.

    select deptno, ename, sal, last_value(sal) over (partition by deptno) as last_value from emp;

    O seguinte resultado é retornado:

    +------------+------------+------------+-------------+
    | deptno     | ename      | sal        | last_value |
    +------------+------------+------------+-------------+
    | 10         | TEBAGE     | 1300       | 2450        |
    | 10         | CLARK      | 2450       | 2450        |
    | 10         | KING       | 5000       | 2450        |
    | 10         | MILLER     | 1300       | 2450        |
    | 10         | JACCKA     | 5000       | 2450        |   
    | 10         | WELAN      | 2450       | 2450        |   -- Last row of the current window.
    | 20         | FORD       | 3000       | 2975        |
    | 20         | SCOTT      | 3000       | 2975        |
    | 20         | SMITH      | 800        | 2975        |
    | 20         | ADAMS      | 1100       | 2975        |
    | 20         | JONES      | 2975       | 2975        |   -- Last row of the current window.
    | 30         | TURNER     | 1500       | 2850        |
    | 30         | JAMES      | 950        | 2850        |
    | 30         | ALLEN      | 1600       | 2850        |
    | 30         | WARD       | 1250       | 2850        |
    | 30         | MARTIN     | 1250       | 2850        |
    | 30         | BLAKE      | 2850       | 2850        |   -- Last row of the current window.
    +------------+------------+------------+-------------+

  • Com uma cláusula order by, o frame padrão da janela é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. A função retorna o valor da linha atual.

    select deptno, ename, sal, last_value(sal) over (partition by deptno order by sal desc) as last_value from emp;

    O seguinte resultado é retornado:

    +------------+------------+------------+-------------+
    | deptno     | ename      | sal        | last_value |
    +------------+------------+------------+-------------+
    | 10         | JACCKA     | 5000       | 5000        |   -- Current row of the current window.
    | 10         | KING       | 5000       | 5000        |   -- Current row of the current window.
    | 10         | CLARK      | 2450       | 2450        |   -- Current row of the current window.
    | 10         | WELAN      | 2450       | 2450        |   -- Current row of the current window.
    | 10         | TEBAGE     | 1300       | 1300        |   -- Current row of the current window.
    | 10         | MILLER     | 1300       | 1300        |   -- Current row of the current window.
    | 20         | SCOTT      | 3000       | 3000        |   -- Current row of the current window.
    | 20         | FORD       | 3000       | 3000        |   -- Current row of the current window.
    | 20         | JONES      | 2975       | 2975        |   -- Current row of the current window.
    | 20         | ADAMS      | 1100       | 1100        |   -- Current row of the current window.
    | 20         | SMITH      | 800        | 800         |   -- Current row of the current window.
    | 30         | BLAKE      | 2850       | 2850        |   -- Current row of the current window.
    | 30         | ALLEN      | 1600       | 1600        |   -- Current row of the current window.
    | 30         | TURNER     | 1500       | 1500        |   -- Current row of the current window.
    | 30         | MARTIN     | 1250       | 1250        |   -- Current row of the current window.
    | 30         | WARD       | 1250       | 1250        |   -- Current row of the current window.
    | 30         | JAMES      | 950        | 950         |   -- Current row of the current window.
    +------------+------------+------------+-------------+

LEAD

• Sintaxe

  lead(<expr>[, bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)

• Descrição

  Retorna o valor da expressão expr da linha que está offset linhas após a linha atual (em direção ao final da partição). A expressão expr pode ser uma coluna, uma operação de coluna ou uma operação de função.

• Parâmetros

  • expr: Obrigatório. A expressão a ser calculada.

  • offset: Opcional. O deslocamento, que é uma constante BIGINT maior ou igual a 0. O valor 0 indica a linha atual e o valor 1 indica a próxima linha. O valor padrão é 1. Se o valor de entrada for do tipo STRING ou DOUBLE, ele será convertido implicitamente para o tipo BIGINT para cálculo.

  • default: Opcional. O valor a ser retornado se o offset estiver fora dos limites. Este valor deve ser uma constante com o mesmo tipo de dados que expr. O padrão é NULL. Se expr não for uma constante, o valor será avaliado com base na linha atual.

  • partition_clause e orderby_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  O valor de retorno possui o mesmo tipo de dados que expr.

• Exemplo

  Agrupe por departamento (deptno) e recupere o salário (sal) da próxima linha para cada funcionário. O comando é o seguinte:

  select deptno, ename, sal, lead(sal, 1) over (partition by deptno order by sal) as sal_new from emp;

  O seguinte resultado é retornado:

  +------------+------------+------------+------------+
  | deptno     | ename      | sal        | sal_new    |
  +------------+------------+------------+------------+
  | 10         | TEBAGE     | 1300       | 1300       |
  | 10         | MILLER     | 1300       | 2450       |
  | 10         | CLARK      | 2450       | 2450       |
  | 10         | WELAN      | 2450       | 5000       |
  | 10         | KING       | 5000       | 5000       |
  | 10         | JACCKA     | 5000       | NULL       |
  | 20         | SMITH      | 800        | 1100       |
  | 20         | ADAMS      | 1100       | 2975       |
  | 20         | JONES      | 2975       | 3000       |
  | 20         | SCOTT      | 3000       | 3000       |
  | 20         | FORD       | 3000       | NULL       |
  | 30         | JAMES      | 950        | 1250       |
  | 30         | MARTIN     | 1250       | 1250       |
  | 30         | WARD       | 1250       | 1500       |
  | 30         | TURNER     | 1500       | 1600       |
  | 30         | ALLEN      | 1600       | 2850       |
  | 30         | BLAKE      | 2850       | NULL       |
  +------------+------------+------------+------------+

MAX

• Sintaxe

  max(<expr>) over([partition_clause] [orderby_clause] [frame_clause])

• Descrição

  Retorna o valor máximo de expr em uma janela.

• Parâmetros

  • expr: Obrigatório. A expressão usada para calcular o valor máximo. Pode ser de qualquer tipo de dados, exceto BOOLEAN. Se o valor for NULL, a linha não será incluída no cálculo.

  • partition_clause, orderby_clause e frame_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  O valor de retorno possui o mesmo tipo de dados que expr.

• Exemplos

  • Exemplo 1: Agrupar por departamento (deptno), calcular o salário máximo (sal) e não ordenar. A função retorna o valor máximo da partição atual (linhas com o mesmo deptno). O comando é o seguinte:

    select deptno, sal, max(sal) over (partition by deptno) from emp;

    O seguinte resultado é retornado:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 5000       |   -- First row of the window. The value is the maximum from the first to the sixth row.
    | 10         | 2450       | 5000       |   -- The value is the maximum from the first to the sixth row.
    | 10         | 5000       | 5000       |   -- The value is the maximum from the first to the sixth row.
    | 10         | 1300       | 5000       |
    | 10         | 5000       | 5000       |
    | 10         | 2450       | 5000       |
    | 20         | 3000       | 3000       |
    | 20         | 3000       | 3000       |
    | 20         | 800        | 3000       |
    | 20         | 1100       | 3000       |
    | 20         | 2975       | 3000       |
    | 30         | 1500       | 2850       |
    | 30         | 950        | 2850       |
    | 30         | 1600       | 2850       |
    | 30         | 1250       | 2850       |
    | 30         | 1250       | 2850       |
    | 30         | 2850       | 2850       |
    +------------+------------+------------+

  • Exemplo 2: Agrupar por departamento (deptno), calcular o salário máximo (sal) e ordenar os resultados. A função retorna o valor máximo da primeira linha até a linha atual da partição corrente (linhas com o mesmo deptno). O comando é o seguinte:

    select deptno, sal, max(sal) over (partition by deptno order by sal) from emp;

    O seguinte resultado é retornado:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 1300       |   -- First row of the window.
    | 10         | 1300       | 1300       |   -- Maximum value from the first to the second row.
    | 10         | 2450       | 2450       |   -- Maximum value from the first to the third row.
    | 10         | 2450       | 2450       |   -- Maximum value from the first to the fourth row.
    | 10         | 5000       | 5000       |
    | 10         | 5000       | 5000       |
    | 20         | 800        | 800        |
    | 20         | 1100       | 1100       |
    | 20         | 2975       | 2975       |
    | 20         | 3000       | 3000       |
    | 20         | 3000       | 3000       |
    | 30         | 950        | 950        |
    | 30         | 1250       | 1250       |
    | 30         | 1250       | 1250       |
    | 30         | 1500       | 1500       |
    | 30         | 1600       | 1600       |
    | 30         | 2850       | 2850       |
    +------------+------------+------------+

MEDIAN

• Sintaxe

  median(<expr>) over ([partition_clause] [orderby_clause] [frame_clause])

• Descrição

  Calcula a mediana de expr em uma janela.

• Parâmetros

  • expr: Obrigatório. A expressão para a qual se deseja calcular a mediana. Deve ser do tipo DOUBLE ou DECIMAL.

    • Se o valor de entrada for do tipo STRING ou BIGINT, ele será convertido implicitamente para o tipo DOUBLE para cálculo. Um erro será retornado para outros tipos de dados.

    • Se a entrada for NULL, o valor de retorno será NULL.

  • partition_clause, orderby_clause e frame_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  Retorna um valor DOUBLE ou DECIMAL. Se todos os valores de expr forem NULL, NULL será retornado.

• Exemplo

  Agrupe por departamento (deptno) e calcule o salário mediano (sal). A função retorna a mediana para toda a partição (todas as linhas com o mesmo deptno). O comando é o seguinte:

  select deptno, sal, median(sal) over (partition by deptno) from emp;

  O seguinte resultado é retornado:

  +------------+------------+------------+
  | deptno     | sal        | _c2        |
  +------------+------------+------------+
  | 10         | 1300       | 2450.0     |   -- First row of the window. The value is the median from the first to the sixth row.
  | 10         | 2450       | 2450.0     |
  | 10         | 5000       | 2450.0     |
  | 10         | 1300       | 2450.0     |
  | 10         | 5000       | 2450.0     |
  | 10         | 2450       | 2450.0     |
  | 20         | 3000       | 2975.0     |
  | 20         | 3000       | 2975.0     |
  | 20         | 800        | 2975.0     |
  | 20         | 1100       | 2975.0     |
  | 20         | 2975       | 2975.0     |
  | 30         | 1500       | 1375.0     |
  | 30         | 950        | 1375.0     |
  | 30         | 1600       | 1375.0     |
  | 30         | 1250       | 1375.0     |
  | 30         | 1250       | 1375.0     |
  | 30         | 2850       | 1375.0     |
  +------------+------------+------------+

MIN

• Sintaxe

  min(<expr>) over([partition_clause] [orderby_clause] [frame_clause])

• Descrição

  Retorna o valor mínimo de expr em uma janela.

• Parâmetros

  • expr: Obrigatório. A expressão usada para calcular o valor mínimo. Pode ser de qualquer tipo de dados, exceto BOOLEAN. Se o valor for NULL, a linha não será incluída no cálculo.

  • partition_clause, orderby_clause e frame_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  O valor de retorno possui o mesmo tipo de dados que expr.

• Exemplos

  • Exemplo 1: Agrupar por departamento (deptno), calcular o salário mínimo (sal) e não ordenar. A função retorna o valor mínimo da partição atual (linhas com o mesmo deptno). O comando é o seguinte:

    select deptno, sal, min(sal) over (partition by deptno) from emp;

    O seguinte resultado é retornado:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 1300       |   -- First row of the window. The value is the minimum from the first to the sixth row.
    | 10         | 2450       | 1300       |   -- The value is the minimum from the first to the sixth row.
    | 10         | 5000       | 1300       |   -- The value is the minimum from the first to the sixth row.
    | 10         | 1300       | 1300       |
    | 10         | 5000       | 1300       |
    | 10         | 2450       | 1300       |
    | 20         | 3000       | 800        |
    | 20         | 3000       | 800        |
    | 20         | 800        | 800        |
    | 20         | 1100       | 800        |
    | 20         | 2975       | 800        |
    | 30         | 1500       | 950        |
    | 30         | 950        | 950        |
    | 30         | 1600       | 950        |
    | 30         | 1250       | 950        |
    | 30         | 1250       | 950        |
    | 30         | 2850       | 950        |
    +------------+------------+------------+

  • Exemplo 2: Agrupar por departamento (deptno), calcular o salário mínimo (sal) e ordenar os resultados. A função retorna o valor mínimo da primeira linha até a linha atual da partição corrente (linhas com o mesmo deptno). O comando é o seguinte:

    select deptno, sal, min(sal) over (partition by deptno order by sal) from emp;

    O seguinte resultado é retornado:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 1300       |   -- First row of the window.
    | 10         | 1300       | 1300       |   -- Minimum value from the first to the second row.
    | 10         | 2450       | 1300       |   -- Minimum value from the first to the third row.
    | 10         | 2450       | 1300       |
    | 10         | 5000       | 1300       |
    | 10         | 5000       | 1300       |
    | 20         | 800        | 800        |
    | 20         | 1100       | 800        |
    | 20         | 2975       | 800        |
    | 20         | 3000       | 800        |
    | 20         | 3000       | 800        |
    | 30         | 950        | 950        |
    | 30         | 1250       | 950        |
    | 30         | 1250       | 950        |
    | 30         | 1500       | 950        |
    | 30         | 1600       | 950        |
    | 30         | 2850       | 950        |
    +------------+------------+------------+

NTILE

• Sintaxe

  bigint ntile(bigint <N>) over ([partition_clause] [orderby_clause])

• Descrição

  Divide as linhas ordenadas em uma partição em N grupos, tão iguais em tamanho quanto possível, e retorna o número do grupo para cada linha. Se o número de linhas não for divisível uniformemente por N, os primeiros grupos (aqueles com números de grupo menores) terão uma linha extra.

• Parâmetros

  • N: Obrigatório. O número de grupos. Um valor BIGINT.

  • partition_clause e orderby_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  Retorna um valor BIGINT.

• Exemplo

  Divida todos os funcionários em 3 grupos dentro de cada departamento com base no salário (sal) em ordem decrescente e retorne o número do grupo para cada funcionário. O comando é o seguinte:

  select deptno, ename, sal, ntile(3) over (partition by deptno order by sal desc) as nt3 from emp;

  O seguinte resultado é retornado:

  +------------+------------+------------+------------+
  | deptno     | ename      | sal        | nt3        |
  +------------+------------+------------+------------+
  | 10         | JACCKA     | 5000       | 1          |
  | 10         | KING       | 5000       | 1          |
  | 10         | CLARK      | 2450       | 2          |
  | 10         | WELAN      | 2450       | 2          |
  | 10         | TEBAGE     | 1300       | 3          |
  | 10         | MILLER     | 1300       | 3          |
  | 20         | SCOTT      | 3000       | 1          |
  | 20         | FORD       | 3000       | 1          |
  | 20         | JONES      | 2975       | 2          |
  | 20         | ADAMS      | 1100       | 2          |
  | 20         | SMITH      | 800        | 3          |
  | 30         | BLAKE      | 2850       | 1          |
  | 30         | ALLEN      | 1600       | 1          |
  | 30         | TURNER     | 1500       | 2          |
  | 30         | MARTIN     | 1250       | 2          |
  | 30         | WARD       | 1250       | 3          |
  | 30         | JAMES      | 950        | 3          |
  +------------+------------+------------+------------+

NTH_VALUE

• Sintaxe

  nth_value(<expr>, <number> [, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])

• Descrição

  Retorna o valor da expressão expr da Nª linha do frame da janela.

• Parâmetros

  • expr: Obrigatório. A expressão a ser calculada.

  • number: Obrigatório. Um valor BIGINT. Um inteiro maior ou igual a 1. Se o valor for 1, esta função equivale a FIRST_VALUE.

  • ignore_nulls: Opcional. Um valor BOOLEAN que especifica se valores NULL devem ser ignorados. O valor padrão é False. Se este parâmetro for definido como True, a função retorna o N-ésimo valor não NULL de expr no frame da janela.

  • partition_clause, orderby_clause e frame_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  O valor de retorno possui o mesmo tipo de dados que expr.

• Exemplo

  O comando a seguir agrupa todos os funcionários por departamento e retorna a 6ª linha de dados de cada grupo:

  • Sem uma cláusula order by, o frame da janela inclui todas as linhas da partição. A função retorna o valor da 6ª linha na partição.

    select deptno, ename, sal, nth_value(sal,6) over (partition by deptno) as nth_value from emp;

    O seguinte resultado é retornado:

    +------------+------------+------------+------------+
    | deptno     | ename      | sal        | nth_value  |
    +------------+------------+------------+------------+
    | 10         | TEBAGE     | 1300       | 2450       |
    | 10         | CLARK      | 2450       | 2450       |
    | 10         | KING       | 5000       | 2450       |
    | 10         | MILLER     | 1300       | 2450       |
    | 10         | JACCKA     | 5000       | 2450       |
    | 10         | WELAN      | 2450       | 2450       |   -- 6th row of the current window.
    | 20         | FORD       | 3000       | NULL       |
    | 20         | SCOTT      | 3000       | NULL       |
    | 20         | SMITH      | 800        | NULL       |
    | 20         | ADAMS      | 1100       | NULL       |
    | 20         | JONES      | 2975       | NULL       |   -- The current window does not have a 6th row, so NULL is returned.
    | 30         | TURNER     | 1500       | 2850       |
    | 30         | JAMES      | 950        | 2850       |
    | 30         | ALLEN      | 1600       | 2850       |
    | 30         | WARD       | 1250       | 2850       |
    | 30         | MARTIN     | 1250       | 2850       |
    | 30         | BLAKE      | 2850       | 2850       |   -- 6th row of the current window.
    +------------+------------+------------+------------+

  • Com uma cláusula order by, o frame padrão da janela é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. A função retorna o valor da 6ª linha no frame da janela.

    select deptno, ename, sal, nth_value(sal,6) over (partition by deptno order by sal) as nth_value from emp;

    O seguinte resultado é retornado:

    +------------+------------+------------+------------+
    | deptno     | ename      | sal        | nth_value  |
    +------------+------------+------------+------------+
    | 10         | TEBAGE     | 1300       | NULL       |   
    | 10         | MILLER     | 1300       | NULL       |   -- The current window has only 2 rows, so the 6th row exceeds the window length.
    | 10         | CLARK      | 2450       | NULL       |
    | 10         | WELAN      | 2450       | NULL       |
    | 10         | KING       | 5000       | 5000       |  
    | 10         | JACCKA     | 5000       | 5000       |
    | 20         | SMITH      | 800        | NULL       |
    | 20         | ADAMS      | 1100       | NULL       |
    | 20         | JONES      | 2975       | NULL       |
    | 20         | SCOTT      | 3000       | NULL       |
    | 20         | FORD       | 3000       | NULL       |
    | 30         | JAMES      | 950        | NULL       |
    | 30         | MARTIN     | 1250       | NULL       |
    | 30         | WARD       | 1250       | NULL       |
    | 30         | TURNER     | 1500       | NULL       |
    | 30         | ALLEN      | 1600       | NULL       |
    | 30         | BLAKE      | 2850       | 2850       |
    +------------+------------+------------+------------+

PERCENT_RANK

• Sintaxe

  double percent_rank() over([partition_clause] [orderby_clause])

• Descrição

  Calcula a classificação percentil da linha atual dentro de sua partição, com base na ordem de classificação especificada por orderby_clause.

• Parâmetros

  partition_clause e orderby_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  Retorna um valor DOUBLE no intervalo de [0.0, 1.0]. O valor de retorno específico é igual a “(rank - 1) / (partition_row_count - 1)”, onde rank é o resultado da função de janela RANK para essa linha, e partition_row_count é o número de linhas na partição à qual a linha pertence. Se a partição contiver apenas uma linha, a saída será 0.0.

• Exemplo

  Calcule a classificação percentil do salário de cada funcionário dentro de seu departamento. O comando é o seguinte:

  select deptno, ename, sal, percent_rank() over (partition by deptno order by sal desc) as sal_new from emp;

  O seguinte resultado é retornado:

  +------------+------------+------------+------------+
  | deptno     | ename      | sal        | sal_new    |
  +------------+------------+------------+------------+
  | 10         | JACCKA     | 5000       | 0.0        |
  | 10         | KING       | 5000       | 0.0        |
  | 10         | CLARK      | 2450       | 0.4        |
  | 10         | WELAN      | 2450       | 0.4        |
  | 10         | TEBAGE     | 1300       | 0.8        |
  | 10         | MILLER     | 1300       | 0.8        |
  | 20         | SCOTT      | 3000       | 0.0        |
  | 20         | FORD       | 3000       | 0.0        |
  | 20         | JONES      | 2975       | 0.5        |
  | 20         | ADAMS      | 1100       | 0.75       |
  | 20         | SMITH      | 800        | 1.0        |
  | 30         | BLAKE      | 2850       | 0.0        |
  | 30         | ALLEN      | 1600       | 0.2        |
  | 30         | TURNER     | 1500       | 0.4        |
  | 30         | MARTIN     | 1250       | 0.6        |
  | 30         | WARD       | 1250       | 0.6        |
  | 30         | JAMES      | 950        | 1.0        |
  +------------+------------+------------+------------+

PERCENTILE_CONT

• Sintaxe

  -- Calculate the exact percentile
  PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])
  
  -- Calculate the exact percentile in a window
  PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

• Descrição

  Calcula o percentil exato. A função utiliza um algoritmo de interpolação linear, ordena a coluna especificada em ordem crescente e retorna o valor exato no percentile definido.

• Parâmetros

  • col_name: Obrigatório. Uma coluna do tipo DOUBLE ou DECIMAL.

  • percentile: Obrigatório. O percentil a ser calculado. Deve ser uma constante DOUBLE no intervalo de [0, 1].

  • isIgnoreNull: Opcional. Define se valores NULL devem ser ignorados. É uma constante BOOLEAN com valor padrão TRUE. Se definido como FALSE, os valores NULL são tratados como o valor mínimo durante a ordenação.

  • partition_clause e orderby_clause: Para mais informações, consulte windowing_definition..

• Valor de retorno

  Retorna o valor do percentil calculado como DOUBLE.

• Exemplos

  • Exemplo 1: Ignorar valores NULL e calcular o percentil exato em uma janela.

    SELECT
      PERCENTILE_CONT(x, 0) OVER() AS min,
      PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
      PERCENTILE_CONT(x, 0.5) OVER() AS median,
      PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
      PERCENTILE_CONT(x, 1) OVER() AS max
    FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;
    
    -- Return result
    +------------+-------------+------------+--------------+------------+
    | min        | percentile1 | median     | percentile90 | max        | 
    +------------+-------------+------------+--------------+------------+
    | 0.0        | 0.03        | 1.5        | 2.7          | 3.0        | 
    +------------+-------------+------------+--------------+------------+

  • Exemplo 2: Não ignorar valores NULL. Nesse caso, os valores NULL são tratados como o valor mínimo na ordenação. O cálculo do percentil exato ocorre em uma janela.

    SELECT
      PERCENTILE_CONT(x, 0, false) OVER() AS min,
      PERCENTILE_CONT(x, 0.01, false) OVER() AS percentile1,
      PERCENTILE_CONT(x, 0.5, false) OVER() AS median,
      PERCENTILE_CONT(x, 0.9, false) OVER() AS percentile90,
      PERCENTILE_CONT(x, 1, false) OVER() AS max
    FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;
    
    -- Return result
    +------------+-------------+------------+--------------+------------+
    | min        | percentile1 | median     | percentile90 | max        | 
    +------------+-------------+------------+--------------+------------+
    | NULL       | 0.0         | 1.0        | 2.6          | 3.0        | 
    +------------+-------------+------------+--------------+------------+

PERCENTILE_DISC

• Sintaxe

  -- Calculate a given percentile value
  PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])
  
  -- Calculate the percentile value in a window
  PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

• Descrição

  Calcula um valor de percentil discreto. Primeiro, a função ordena a coluna especificada em ordem crescente e depois retorna o primeiro valor cuja distribuição cumulativa seja maior ou igual ao percentil especificado.

• Parâmetros

  • col_name: Obrigatório. Uma coluna com qualquer tipo de dado ordenável.

  • percentile: Obrigatório. O percentil a ser calculado. Deve ser uma constante DOUBLE no intervalo de [0, 1].

  • isIgnoreNull: Opcional. Define se valores NULL devem ser ignorados. É uma constante BOOLEAN com valor padrão TRUE. Se definido como FALSE, os valores NULL são tratados como o valor mínimo durante a ordenação.

  • partition_clause e orderby_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  Retorna o valor do percentil calculado. O tipo de dado é o mesmo da coluna de entrada col_name.

• Exemplos

  • Exemplo 1: Ignorar valores NULL e calcular o valor do percentil em uma janela.

    SELECT
      x,
      PERCENTILE_DISC(x, 0) OVER() AS min,
      PERCENTILE_DISC(x, 0.5) OVER() AS median,
      PERCENTILE_DISC(x, 1) OVER() AS max
    FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);
    
    -- Return result
    +------------+------------+------------+------------+
    | x          | min        | median     | max        | 
    +------------+------------+------------+------------+
    | c          | a          | b          | c          | 
    | NULL       | a          | b          | c          | 
    | b          | a          | b          | c          | 
    | a          | a          | b          | c          | 
    +------------+------------+------------+------------+

  • Exemplo 2: Não ignorar valores NULL. Os valores NULL são considerados o valor mínimo na ordenação. O cálculo do percentil ocorre em uma janela.

    SELECT
      x,
      PERCENTILE_DISC(x, 0, false) OVER() AS min,
      PERCENTILE_DISC(x, 0.5, false) OVER() AS median,
      PERCENTILE_DISC(x, 1, false) OVER() AS max
    FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);
    
    -- Return result
    +------------+------------+------------+------------+
    | x          | min        | median     | max        | 
    +------------+------------+------------+------------+
    | c          | NULL       | a          | c          | 
    | NULL       | NULL       | a          | c          | 
    | b          | NULL       | a          | c          | 
    | a          | NULL       | a          | c          | 
    +------------+------------+------------+------------+

RANK

• Sintaxe

  bigint rank() over ([partition_clause] [orderby_clause])

• Descrição

  Calcula a classificação da linha atual dentro de sua partição, com base na ordem de classificação definida por orderby_clause. A contagem começa em 1.

• Parâmetros

  partition_clause e orderby_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  Retorna um valor BIGINT. Os valores retornados podem ser duplicados e não consecutivos. O valor específico corresponde ao ROW_NUMBER() da primeira linha do GRUPO ao qual a linha de dados pertence. Caso nenhuma orderby_clause seja especificada, todas as linhas recebem classificação 1.

• Exemplo

  Particione por departamento (deptno) e classifique os funcionários dentro de cada departamento com base no salário (sal) em ordem decrescente. O comando é o seguinte:

  select deptno, ename, sal, rank() over (partition by deptno order by sal desc) as nums from emp;

  O resultado retornado é:

  +------------+------------+------------+------------+
  | deptno     | ename      | sal        | nums       |
  +------------+------------+------------+------------+
  | 10         | JACCKA     | 5000       | 1          |
  | 10         | KING       | 5000       | 1          |
  | 10         | CLARK      | 2450       | 3          |
  | 10         | WELAN      | 2450       | 3          |
  | 10         | TEBAGE     | 1300       | 5          |
  | 10         | MILLER     | 1300       | 5          |
  | 20         | SCOTT      | 3000       | 1          |
  | 20         | FORD       | 3000       | 1          |
  | 20         | JONES      | 2975       | 3          |
  | 20         | ADAMS      | 1100       | 4          |
  | 20         | SMITH      | 800        | 5          |
  | 30         | BLAKE      | 2850       | 1          |
  | 30         | ALLEN      | 1600       | 2          |
  | 30         | TURNER     | 1500       | 3          |
  | 30         | MARTIN     | 1250       | 4          |
  | 30         | WARD       | 1250       | 4          |
  | 30         | JAMES      | 950        | 6          |
  +------------+------------+------------+------------+

ROW_NUMBER

• Sintaxe

  row_number() over([partition_clause] [orderby_clause])

• Descrição

  Calcula o número da linha atual dentro de sua partição, iniciando a contagem em 1.

• Parâmetros

  Para mais informações, consulte windowing_definition. O uso de frame_clause não é permitido.

• Valor de retorno

  Retorna um valor BIGINT.

• Exemplo

  Particione por departamento (deptno) e atribua um número sequencial único a cada funcionário dentro do seu departamento, com base no salário (sal) em ordem decrescente. O comando é o seguinte:

  select deptno, ename, sal, row_number() over (partition by deptno order by sal desc) as nums from emp;

  O resultado retornado é:

  +------------+------------+------------+------------+
  | deptno     | ename      | sal        | nums       |
  +------------+------------+------------+------------+
  | 10         | JACCKA     | 5000       | 1          |
  | 10         | KING       | 5000       | 2          |
  | 10         | CLARK      | 2450       | 3          |
  | 10         | WELAN      | 2450       | 4          |
  | 10         | TEBAGE     | 1300       | 5          |
  | 10         | MILLER     | 1300       | 6          |
  | 20         | SCOTT      | 3000       | 1          |
  | 20         | FORD       | 3000       | 2          |
  | 20         | JONES      | 2975       | 3          |
  | 20         | ADAMS      | 1100       | 4          |
  | 20         | SMITH      | 800        | 5          |
  | 30         | BLAKE      | 2850       | 1          |
  | 30         | ALLEN      | 1600       | 2          |
  | 30         | TURNER     | 1500       | 3          |
  | 30         | MARTIN     | 1250       | 4          |
  | 30         | WARD       | 1250       | 5          |
  | 30         | JAMES      | 950        | 6          |
  +------------+------------+------------+------------+

STDDEV

• Sintaxe

  double stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
  decimal stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

• Descrição

  Calcula o desvio padrão populacional. Trata-se de um alias para a função STDDEV_POP.

• Parâmetros

  • expr: Obrigatório. A expressão para a qual se deseja calcular o desvio padrão populacional. Deve ser do tipo DOUBLE ou DECIMAL.

    • Se o valor de entrada for STRING ou BIGINT, ele será convertido implicitamente para DOUBLE para o cálculo. Outros tipos de dados retornam erro.

    • Valores NULL na entrada excluem a linha do cálculo.

    • Ao especificar a palavra-chave distinct, a função calcula o desvio padrão populacional apenas dos valores únicos.

  • partition_clause, orderby_clause e frame_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  O valor retornado possui o mesmo tipo de dado que expr. Se todos os valores de expr forem NULL, o retorno será NULL.

• Exemplos

  • Exemplo 1: Particionar por departamento (deptno), calcular o desvio padrão populacional do salário (sal) sem aplicar ordenação. A função retorna o desvio padrão populacional cumulativo da partição atual (linhas com o mesmo deptno). O comando é o seguinte:

    select deptno, sal, stddev(sal) over (partition by deptno) from emp;

    O resultado retornado é:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 1546.1421524412158 |   -- First row of the window. The value is the cumulative population standard deviation from the first to the sixth row.
    | 10         | 2450       | 1546.1421524412158 |   -- The value is the cumulative population standard deviation from the first to the sixth row.
    | 10         | 5000       | 1546.1421524412158 |
    | 10         | 1300       | 1546.1421524412158 |
    | 10         | 5000       | 1546.1421524412158 |
    | 10         | 2450       | 1546.1421524412158 |
    | 20         | 3000       | 1004.7387720198718 |
    | 20         | 3000       | 1004.7387720198718 |
    | 20         | 800        | 1004.7387720198718 |
    | 20         | 1100       | 1004.7387720198718 |
    | 20         | 2975       | 1004.7387720198718 |
    | 30         | 1500       | 610.1001739241042 |
    | 30         | 950        | 610.1001739241042 |
    | 30         | 1600       | 610.1001739241042 |
    | 30         | 1250       | 610.1001739241042 |
    | 30         | 1250       | 610.1001739241042 |
    | 30         | 2850       | 610.1001739241042 |
    +------------+------------+------------+

  • Exemplo 2: No modo não compatível com Hive, particione por departamento (deptno), calcule o desvio padrão populacional do salário (sal) e ordene os resultados. A função retorna o desvio padrão populacional cumulativo da primeira linha até a linha atual da partição (linhas com o mesmo deptno). Os comandos são os seguintes:

    -- Disable Hive compatible mode.
    set odps.sql.hive.compatible=false;
    -- Execute the following SQL command.
    select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;

    O resultado retornado é:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 0.0        |           -- First row of the window.
    | 10         | 1300       | 0.0        |           -- Cumulative population standard deviation from the first to the second row.
    | 10         | 2450       | 542.1151989096865 |    -- Cumulative population standard deviation from the first to the third row.
    | 10         | 2450       | 575.0      |           -- Cumulative population standard deviation from the first to the fourth row.
    | 10         | 5000       | 1351.6656391282572 |
    | 10         | 5000       | 1546.1421524412158 |
    | 20         | 800        | 0.0        |
    | 20         | 1100       | 150.0      |
    | 20         | 2975       | 962.4188277460079 |
    | 20         | 3000       | 1024.2947268730811 |
    | 20         | 3000       | 1004.7387720198718 |
    | 30         | 950        | 0.0        |
    | 30         | 1250       | 150.0      |
    | 30         | 1250       | 141.4213562373095 |
    | 30         | 1500       | 194.8557158514987 |
    | 30         | 1600       | 226.71568097509268 |
    | 30         | 2850       | 610.1001739241042 |
    +------------+------------+------------+

  • Exemplo 3: No modo compatível com Hive, particione por departamento (deptno), calcule o desvio padrão populacional do salário (sal) e ordene os resultados. A função retorna o desvio padrão populacional cumulativo da primeira linha até a linha com o mesmo valor da linha atual (linhas com o mesmo sal possuem o mesmo desvio padrão populacional). Os comandos são os seguintes:

    -- Enable Hive compatible mode.
    set odps.sql.hive.compatible=true;
    -- Execute the following SQL command.
    select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;

    O resultado retornado é:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 0.0        |           -- First row of the window. Since the sal of the first and second rows are the same, the population standard deviation for the first row is the cumulative population standard deviation of the first two rows.
    | 10         | 1300       | 0.0        |           -- Cumulative population standard deviation from the first to the second row.
    | 10         | 2450       | 575.0      |           -- Since the sal of the third and fourth rows are the same, the population standard deviation for the third row is the cumulative population standard deviation of the first four rows.
    | 10         | 2450       | 575.0      |           -- Cumulative population standard deviation from the first to the fourth row.
    | 10         | 5000       | 1546.1421524412158 |
    | 10         | 5000       | 1546.1421524412158 |
    | 20         | 800        | 0.0        |
    | 20         | 1100       | 150.0      |
    | 20         | 2975       | 962.4188277460079 |
    | 20         | 3000       | 1004.7387720198718 |
    | 20         | 3000       | 1004.7387720198718 |
    | 30         | 950        | 0.0        |
    | 30         | 1250       | 141.4213562373095 |
    | 30         | 1250       | 141.4213562373095 |
    | 30         | 1500       | 194.8557158514987 |
    | 30         | 1600       | 226.71568097509268 |
    | 30         | 2850       | 610.1001739241042 |
    +------------+------------+------------+

STDDEV_SAMP

• Sintaxe

  double stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])
  decimal stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])

• Descrição

  Calcula o desvio padrão amostral.

• Parâmetros

  • expr: Obrigatório. A expressão para a qual se deseja calcular o desvio padrão amostral. Deve ser do tipo DOUBLE ou DECIMAL.

    • Se o valor de entrada for STRING ou BIGINT, ele será convertido implicitamente para DOUBLE para o cálculo. Outros tipos de dados retornam erro.

    • Valores NULL na entrada excluem a linha do cálculo.

    • Ao especificar a palavra-chave distinct, a função calcula o desvio padrão amostral apenas dos valores únicos.

  • partition_clause, orderby_clause e frame_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  O valor retornado possui o mesmo tipo de dado que expr. Se todos os valores de expr forem NULL, o retorno será NULL. Caso a janela contenha apenas um valor não NULL para expr, o resultado será 0.

• Exemplos

  • Exemplo 1: Particionar por departamento (deptno), calcular o desvio padrão amostral do salário (sal) sem aplicar ordenação. A função retorna o desvio padrão amostral cumulativo da partição atual (linhas com o mesmo deptno). O comando é o seguinte:

    select deptno, sal, stddev_samp(sal) over (partition by deptno) from emp;

    O resultado retornado é:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 1693.7138680032904 |   -- First row of the window. The value is the cumulative sample standard deviation from the first to the sixth row.
    | 10         | 2450       | 1693.7138680032904 |   -- The value is the cumulative sample standard deviation from the first to the sixth row.
    | 10         | 5000       | 1693.7138680032904 |   -- The value is the cumulative sample standard deviation from the first to the sixth row.
    | 10         | 1300       | 1693.7138680032904 |     
    | 10         | 5000       | 1693.7138680032904 |
    | 10         | 2450       | 1693.7138680032904 |
    | 20         | 3000       | 1123.3320969330487 |
    | 20         | 3000       | 1123.3320969330487 |
    | 20         | 800        | 1123.3320969330487 |
    | 20         | 1100       | 1123.3320969330487 |
    | 20         | 2975       | 1123.3320969330487 |
    | 30         | 1500       | 668.331255192114 |
    | 30         | 950        | 668.331255192114 |
    | 30         | 1600       | 668.331255192114 |
    | 30         | 1250       | 668.331255192114 |
    | 30         | 1250       | 668.331255192114 |
    | 30         | 2850       | 668.331255192114 |
    +------------+------------+------------+

  • Exemplo 2: Particionar por departamento (deptno), calcular o desvio padrão amostral do salário (sal) e ordenar os resultados. A função retorna o desvio padrão amostral cumulativo da primeira linha até a linha atual da partição (linhas com o mesmo deptno). O comando é o seguinte:

    select deptno, sal, stddev_samp(sal) over (partition by deptno order by sal) from emp;

    O resultado retornado é:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 0.0        |          -- First row of the window.
    | 10         | 1300       | 0.0        |          -- Cumulative sample standard deviation from the first to the second row.
    | 10         | 2450       | 663.9528095680697 |   -- Cumulative sample standard deviation from the first to the third row.
    | 10         | 2450       | 663.9528095680696 |
    | 10         | 5000       | 1511.2081259707413 |
    | 10         | 5000       | 1693.7138680032904 |
    | 20         | 800        | 0.0        |
    | 20         | 1100       | 212.13203435596427 |
    | 20         | 2975       | 1178.7175234126282 |
    | 20         | 3000       | 1182.7536725793752 |
    | 20         | 3000       | 1123.3320969330487 |
    | 30         | 950        | 0.0        |
    | 30         | 1250       | 212.13203435596427 |
    | 30         | 1250       | 173.20508075688772 |
    | 30         | 1500       | 225.0      |
    | 30         | 1600       | 253.4758371127315 |
    | 30         | 2850       | 668.331255192114 |
    +------------+------------+------------+

SUM

• Sintaxe

  sum([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

• Descrição

  Retorna a soma de expr em uma janela.

• Parâmetros

  • expr: Obrigatório. A coluna para a qual se deseja calcular a soma. Deve ser do tipo DOUBLE, DECIMAL ou BIGINT.

    • Se o valor de entrada for STRING, ele será convertido implicitamente para DOUBLE para o cálculo. Outros tipos de dados retornam erro.

    • Valores NULL na entrada excluem a linha do cálculo.

    • Ao especificar a palavra-chave distinct, a função calcula a soma apenas dos valores únicos.

  • partition_clause, orderby_clause e frame_clause: Para mais informações, consulte windowing_definition.

• Valor de retorno

  • Entradas do tipo BIGINT retornam um valor BIGINT.

  • Entradas do tipo DECIMAL retornam um valor DECIMAL.

  • Entradas do tipo DOUBLE ou STRING retornam um valor DOUBLE.

  • Se todos os valores de entrada forem NULL, o retorno será NULL.

• Exemplos

  • Exemplo 1: Particionar por departamento (deptno), calcular a soma do salário (sal) sem aplicar ordenação. A função retorna a soma cumulativa da partição atual (linhas com o mesmo deptno). O comando é o seguinte:

    select deptno, sal, sum(sal) over (partition by deptno) from emp;

    O resultado retornado é:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 17500      |   -- First row of the window. The value is the cumulative sum from the first to the sixth row.
    | 10         | 2450       | 17500      |   -- The value is the cumulative sum from the first to the sixth row.
    | 10         | 5000       | 17500      |   -- The value is the cumulative sum from the first to the sixth row.
    | 10         | 1300       | 17500      |
    | 10         | 5000       | 17500      |
    | 10         | 2450       | 17500      |
    | 20         | 3000       | 10875      |
    | 20         | 3000       | 10875      |
    | 20         | 800        | 10875      |
    | 20         | 1100       | 10875      |
    | 20         | 2975       | 10875      |
    | 30         | 1500       | 9400       |
    | 30         | 950        | 9400       |
    | 30         | 1600       | 9400       |
    | 30         | 1250       | 9400       |
    | 30         | 1250       | 9400       |
    | 30         | 2850       | 9400       |
    +------------+------------+------------+

  • Exemplo 2: No modo não compatível com Hive, particione por departamento (deptno), calcule a soma do salário (sal) e ordene os resultados. A função retorna a soma cumulativa da primeira linha até a linha atual da partição (linhas com o mesmo deptno). Os comandos são os seguintes:

    -- Disable Hive compatible mode.
    set odps.sql.hive.compatible=false;
    -- Execute the following SQL command.
    select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

    O resultado retornado é:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 1300       |   -- First row of the window.
    | 10         | 1300       | 2600       |   -- Cumulative sum from the first to the second row.
    | 10         | 2450       | 5050       |   -- Cumulative sum from the first to the third row.
    | 10         | 2450       | 7500       |
    | 10         | 5000       | 12500      |
    | 10         | 5000       | 17500      |
    | 20         | 800        | 800        |
    | 20         | 1100       | 1900       |
    | 20         | 2975       | 4875       |
    | 20         | 3000       | 7875       |
    | 20         | 3000       | 10875      |
    | 30         | 950        | 950        |
    | 30         | 1250       | 2200       |
    | 30         | 1250       | 3450       |
    | 30         | 1500       | 4950       |
    | 30         | 1600       | 6550       |
    | 30         | 2850       | 9400       |
    +------------+------------+------------+

  • Exemplo 3: No modo compatível com Hive, particione por departamento (deptno), calcule a soma do salário (sal) e ordene os resultados. A função retorna a soma cumulativa da primeira linha até a linha com o mesmo valor da linha atual (linhas com o mesmo sal possuem a mesma soma). Os comandos são os seguintes:

    -- Enable Hive compatible mode.
    set odps.sql.hive.compatible=true;
    -- Execute the following SQL command.
    select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

    O resultado retornado é:

    +------------+------------+------------+
    | deptno     | sal        | _c2        |
    +------------+------------+------------+
    | 10         | 1300       | 2600       |   -- First row of the window. Since the sal of the first and second rows are the same, the sum for the first row is the cumulative sum of the first two rows.
    | 10         | 1300       | 2600       |   -- Cumulative sum from the first to the second row.
    | 10         | 2450       | 7500       |   -- Since the sal of the third and fourth rows are the same, the sum for the third row is the cumulative sum of the firstfour rows.
    | 10         | 5000       | 17500      |
    | 10         | 5000       | 17500      |
    | 20         | 800        | 800        |
    | 20         | 1100       | 1900       |
    | 20         | 2975       | 4875       |
    | 20         | 3000       | 10875      |
    | 20         | 3000       | 10875      |
    | 30         | 950        | 950        |
    | 30         | 1250       | 3450       |
    | 30         | 1250       | 3450       |
    | 30         | 1500       | 4950       |
    | 30         | 1600       | 6550       |
    | 30         | 2850       | 9400       |
    +------------+------------+------------+

Referências

• Caso as funções integradas não atendam às suas necessidades, crie funções definidas pelo usuário. MaxCompute UDF overview.

• Problemas comuns de SQL no MaxCompute:

  • FAQ about DDL operations

  • FAQ about DML operations

  • FAQ about DQL operations

  • General SQL FAQ

• Códigos de erro comuns para funções integradas:

  • Common error codes for built-in functions

  • FAQ about built-in functions