Conceitos de funções analíticas no SQL padrão

Neste tópico, explicamos as funções analíticas no BigQuery. Para uma descrição das diferentes funções analíticas compatíveis com o BigQuery, consulte os tópicos de referência para funções de navegação, funções de numeração e funções analíticas agregadas.

Nos bancos de dados, uma função analítica calcula valores agregados em um grupo de linhas. Ao contrário das funções de agregação, que retornam um único valor de agregação de um grupo de linhas, as funções analíticas retornam um único valor para cada linha, calculando a função em um grupo de linhas da entrada.

As funções analíticas são um mecanismo poderoso para representar operações complexas de maneira sucinta. Elas permitem avaliações eficazes que poderiam envolver JOINs automáticos ou cálculos caros fora da consulta SQL.

As funções analíticas também são chamadas de "funções (analíticas) de janela" no SQL padrão e alguns bancos de dados comerciais. Isso porque uma função analítica é avaliada em um grupo de linhas, chamadas de window ou window frame. Em alguns outros bancos de dados, elas podem ser chamadas de funções de processamento analítico on-line (OLAP, na sigla em inglês).

Sintaxe simplificada:

analytic_function_name ( [ argument_list ] )
  OVER (
    [ PARTITION BY partition_expression_list ]
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ window_frame_clause ]
  )

Uma função analítica requer uma cláusula OVER, que define a window frame em que a função analítica é avaliada. A cláusula OVER contém as três cláusulas opcionais a seguir. O BigQuery avalia as subcláusulas de uma cláusula OVER na ordem em que são gravadas.

  • Uma cláusula PARTITION BY divide as linhas da entrada em partições, semelhante a GROUP BY, mas sem realmente combinar as linhas com a mesma chave.
  • Uma cláusula ORDER BY especifica a ordem dentro de cada partição.
  • Uma window_frame_clause define a window frame dentro da partição atual.

A cláusula OVER também pode ficar vazia (OVER()). Nesse caso, window frame inclui todas as linhas de entrada.

As funções analíticas são avaliadas após a agregação (GROUP BY e funções de agregação não analíticas).

Exemplo: uma empresa quer criar um placar de "classificação por tempo de serviço" com os funcionários mais antigos de cada departamento. A tabela Employees contém as colunas Name, StartDate e Department.

A consulta a seguir calcula a classificação de cada funcionário no departamento:

SELECT firstname, department, startdate,
  RANK() OVER ( PARTITION BY department ORDER BY startdate ) AS rank
FROM Employees;

O processo de computação conceitual está ilustrado na figura 1.

imagem Markdown Figura 1: ilustração da função analítica

O BigQuery avalia as subcláusulas de uma cláusula OVER na ordem em que aparecem:

  1. PARTITION BY: primeiro, a tabela é dividida em três partições por department.
  2. ORDER BY: as linhas dos funcionários em cada partição são ordenadas por startdate.
  3. Frame: nenhum. A cláusula do frame da janela não é permitida para RANK(), como é para todas as funções de numeração.
  4. RANK(): a classificação por tempo de serviço é calculada em cada linha acima da window frame.

Sintaxe da função analítica

analytic_function_name ( [ argument_list ] )
  OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
  [ window_frame_clause ]

window_frame_clause:
{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

As funções analíticas podem ser exibidas como uma expressão escalar ou um operando dessa expressão em apenas dois lugares na consulta:

  • A lista SELECT. Se a função analítica for exibida na lista SELECT, a respectiva argument_list não poderá se referir a aliases introduzidos na mesma lista SELECT.
  • A cláusula ORDER BY. Se a função analítica for exibida na cláusula ORDER BY da consulta, a respectiva argument_list poderá se referir a aliases da lista SELECT.

Além disso, uma função analítica não pode referir-se a outra função analítica em argument_list ou na cláusula OVER, mesmo que indiretamente por meio de um alias.

Inválido:

SELECT ROW_NUMBER() OVER () AS alias1
FROM Singers
ORDER BY ROW_NUMBER() OVER(PARTITION BY alias1)

Na consulta anterior, a função analítica alias1 é resolvida como a função ROW_NUMBER() OVER().

Cláusula OVER

Sintaxe:

OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY sort_specification_list ]
  [ window_frame_clause ]

A cláusula OVER tem três componentes possíveis:

  • Cláusula PARTITION BY
  • Cláusula ORDER BY
  • Uma window_frame_clause ou uma window_name que se refira a uma window_specification definida em uma cláusula WINDOW

Se a cláusula OVER estiver vazia, OVER(), a função analítica será calculada em uma única partição que contém todas as linhas da entrada. Isso significa que ela produzirá o mesmo resultado para a linha da saída.

Cláusula PARTITION BY

Sintaxe:

PARTITION BY expression [, ... ]

A cláusula PARTITION BY divide as linhas da entrada em partições separadas em que a função analítica é avaliada de forma independente. São permitidos vários expressions na cláusula PARTITION BY.

O tipo de dado de expression precisa ser agrupável e aceitar particionamento. Isso significa que a expression não pode ser nenhum dos tipos de dados a seguir:

  • Ponto flutuante
  • Struct
  • Matriz

Essa lista é quase idêntica à lista de tipos de dados não compatíveis com GROUP BY, além da exclusão adicional dos tipos de pontos flutuantes. Consulte "Agrupável" na tabela "Propriedades de tipos de dados" na parte superior de Tipos de dados do BigQuery.

Se não houver uma cláusula PARTITION BY, o BigQuery tratará a entrada inteira como uma única partição.

Cláusula ORDER BY

Sintaxe:

ORDER BY expression [ ASC | DESC ] [, ... ]

A cláusula ORDER BY define uma ordenação dentro de cada partição. Se não houver uma cláusula ORDER BY, a ordenação de linha dentro da partição não será determinista. Algumas funções analíticas precisam de ORDER BY. Isso pode ser visto na seção de cada família de funções analíticas. Mesmo que haja uma cláusula ORDER BY, algumas funções não são sensíveis à ordenação dentro de um window frame (como COUNT).

A cláusula ORDER BY dentro de uma cláusula OVER é consistente com a cláusula normal nos seguintes aspectos:

  • Pode haver várias expressions.
  • expression precisa ter um tipo compatível com a ordenação.
  • Uma especificação ASC/DESC opcional é permitida para cada expression.
  • Os valores NULL são ordenados como o mínimo valor possível (primeiro para ASC, último para DESC).

A compatibilidade com o tipo de dado é idêntica à cláusula ORDER BY normal, porque os tipos a seguir não são compatíveis com ordenação:

  • Matriz
  • Struct

Se a cláusula OVER contiver uma cláusula ORDER BY, mas não window_frame_clause, então ORDER BY definirá window_frame_clause implicitamente como:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Se não houver a cláusula window_frame_clause nem ORDER BY, window frame será o padrão da partição inteira.

Cláusula Window Frame

Sintaxe:

{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_clause define window frame na linha atual dentro de uma partição em que a função analítica é avaliada. window_frame_clause permite frames de janela físicos (definidos por ROWS) e frames de janela lógicos (definidos por RANGE). Se a cláusula OVER contiver uma cláusula ORDER BY, mas não window_frame_clause, então ORDER BY definirá window_frame_clause implicitamente como:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Se não houver a cláusula window_frame_clause nem ORDER BY, window frame será o padrão da partição inteira.

A numeric_expression só pode ser uma constante ou um parâmetro de consulta e ambos precisam ter um valor não negativo. Caso contrário, o BigQuery produzirá um erro.

Exemplos de cláusulas de frame de janela:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  • inclui a partição inteira.
  • exemplo de uso: cálculo de um total geral na partição.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • inclui todas as linhas na partição antes da linha atual ou incluindo-a.
  • exemplo de uso: cálculo de uma soma cumulativa.
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
  • inclui todas as linhas entre duas antes e duas depois da linha atual.
  • Exemplo de uso: cálculo de uma média móvel.

Se window_frame_spec usar a cláusula BETWEEN:

  • window_frame_boundary_start precisa especificar um limite que não pode começar depois de window_frame_boundary_end. Isso tem as seguintes consequências:
    1. Se window_frame_boundary_start contiver CURRENT ROW, window_frame_boundary_end não poderá conter PRECEDING.
    2. Se window_frame_boundary_start contiver FOLLOWING, window_frame_boundary_end não poderá conter CURRENT ROW ou PRECEDING.
  • window_frame_boundary_start não tem valor padrão.

Caso contrário, o limite especificado de window_frame_spec representará o início do frame da janela. O fim do limite do frame da janela será o padrão de 'CURRENT ROW'. Sendo assim,

ROWS 10 PRECEDING

é equivalente a

ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
ROWS

Os frames da janela com base em ROWS calculam window frame com base em deslocamentos físicos da linha atual. Por exemplo, o frame da janela a seguir é definido para um tamanho cinco (no máximo) ao redor da linha atual.

ROWS BETWEEN 2 PRECEDING and 2 FOLLOWING

A numeric_expression em window_frame_clause é interpretada como um número de linhas a partir da linha atual e precisa ser um número inteiro constante, não negativo. Também pode ser um parâmetro de consulta.

Se a window frame de uma linha se estender além do início ou fim da partição, a incluirá somente linhas de dentro dela.

Por exemplo: pense na tabela a seguir com as colunas z, x e y.

z x y
1 5 AA
2 2 AA
3 11 AB
4 2 AA
5 8 AC
6 10 AB
7 1 AB

Considere a seguinte função analítica:

SUM(x) OVER (PARTITION BY y ORDER BY z ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING)

A cláusula PARTITION BY divide a tabela em três partições com base no valor de y, e ORDER BY ordena as linhas dentro de cada partição pelo valor de z.

Partição 1 de 3:

z x y
1 5 AA
2 2 AA
4 2 AA

Partição 2 de 3:

z x y
3 11 AB
6 10 AB
7 1 AB

Partição 3 de 3:

z x y
5 8 AC

Nas tabelas abaixo, negrito indica a linha que está sendo avaliada no momento, e células coloridas indicam todas as linhas na window frame delas.

  • A window frame inclui somente duas linhas na primeira linha da partição y = AA, uma vez que não há precedente, ainda que window_frame_spec indique uma janela de tamanho 3. O resultado da função analítica é 7 para a primeira linha.
z x y
1 5 AA
2 2 AA
4 2 AA
  • Na segunda linha da partição, a window frame inclui todas as três linhas. O resultado da função analítica é 9, para a segunda linha.
z x y
1 5 AA
2 2 AA
4 2 AA
  • Na última linha da partição, a window frame inclui apenas duas linhas, porque não há linha seguinte. O resultado da função analítica é 4, para a terceira fila.
z x y
1 5 AA
2 2 AA
4 2 AA
RANGE

Os frames da janela com base em RANGE calculam window frame em intervalos lógicos da linha atual com base no valor da chave ORDER BY da linha. O valor do intervalo fornecido é adicionado ou subtraído do valor da chave da linha atual para definir um limite de início e fim do intervalo da window frame.

A cláusula ORDER BY precisa ser especificada, a menos que a janela seja:

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

numeric_expression na window_frame_clause é interpretado como um deslocamento do valor da chave ORDER BY da linha atual. A numeric_expression precisa ter um tipo numérico. No momento, DATE e TIMESTAMP não são compatíveis. Além disso, a numeric_expression tem que ser um número inteiro constante não negativo ou um parâmetro.

Em um frame de janela baseado em RANGE, pode haver no máximo uma expression na cláusula ORDER BY. expression precisa ter um tipo numérico.

Exemplo de um frame de janela baseado em RANGE, no qual há uma única partição:

SELECT x, COUNT(*) OVER ( ORDER BY x
  RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING ) AS count_x
FROM T;

Nas tabelas abaixo, negrito indica a linha que está sendo avaliada no momento, e células coloridas indicam todas as linhas na window frame delas.

  • Para a linha 1, x = 5. Portanto, COUNT(*) incluirá somente as linhas em que 3 <= <= 7
x count_x
5 1
2
11
2
8
10
1
  • Para a linha 2, x = 2. Portanto, COUNT(*) incluirá somente as linhas em que 0 <= <= 4
x count_x
5 1
2 3
11
2
8
10
1
  • Para a linha 3, x = 11. Portanto, COUNT(*) incluirá somente as linhas em que 9 <= <= 13
x count_x
5 1
2 3
11 2
2
8
10
1

Cláusula WINDOW

Sintaxe:

WINDOW window_definition [, ...]
window_definition: window_name AS ( window_specification )

Uma cláusula WINDOW define uma lista de janelas nomeadas em que window_name pode ser referida nas funções analíticas na lista SELECT. Isso é útil quando você quer usar a mesma window_frame_clause para várias funções analíticas.

A cláusula WINDOW pode aparecer apenas no final de uma cláusula SELECT, conforme mostrado em Sintaxe de consulta.

Janelas nomeadas

Depois de definir uma cláusula WINDOW, será possível usar as janelas nomeadas nas funções analíticas, mas somente na lista SELECT. Não é possível usá-las na cláusula ORDER BY. As janelas nomeadas podem aparecer por conta própria ou incorporadas em uma cláusula OVER. As janelas nomeadas podem indicar aliases da lista SELECT.

Exemplos:

SELECT SUM(x) OVER window_name FROM ...
SELECT SUM(x) OVER (
  window_name
  PARTITION BY...
  ORDER BY...
  window_frame_clause)
FROM ...

Quando incorporado em uma cláusula OVER, a window_specification associada à window_name terá de ser compatível com PARTITION BY, ORDER BY e window_frame_clause, que estão na mesma cláusula .

As regras a seguir aplicam-se a janelas nomeadas:

  • Somente é possível indicar as janelas nomeadas na lista SELECT, e não as que estão em uma cláusula ORDER BY, uma consulta externa ou uma subconsulta.
  • Uma janela W1 (nomeada ou não) pode referir-se a uma janela nomeada NW2, com as seguintes regras:
    1. Se W1 for uma janela nomeada, NW2 terá que anteceder W1 na mesma cláusula WINDOW.
    2. W1 não pode conter uma cláusula PARTITION BY.
    3. Não pode ser verdadeiro que W1 e NW2 contenham uma cláusula ORDER BY.
    4. NW2 não pode conter uma window_frame_clause.
  • Se uma janela W1 (nomeada ou não) fizer referência a uma janela nomeada NW2, as especificações da janela resultante serão definidas usando:
    1. PARTITION BY da NW2, se houver;
    2. ORDER BY de W1 ou NW2, se uma delas estiver especificada. Não é possível que as duas tenham uma cláusula ;
    3. window_frame_clause da W1, se houver.

Neste tópico, explicamos as funções de navegação analíticas. Para uma descrição das funções de navegação analíticas compatíveis com o BigQuery, consulte a referência para funções de navegação.

Em geral, as funções de navegação calculam value_expression em uma linha diferente no frame da janela a partir da linha atual. A sintaxe da cláusula OVER varia entre as funções de navegação.

Requisitos da cláusula OVER:

  • PARTITION BY: opcional.
  • ORDER BY:
    1. Bloqueada para PERCENTILE_CONT e PERCENTILE_DISC
    2. Necessária para FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD e LAG
  • window_frame_clause:
    1. Bloqueada para PERCENTILE_CONT, PERCENTILE_DISC, LEAD e LAG
    2. Opcional para FIRST_VALUE, LAST_VALUE e NTH_VALUE

Para todas as funções de navegação, o tipo de dados do resultado é igual aos de value_expression.

Funções de numeração

Neste tópico, você verá as funções de numeração analítica. Para ver uma explicação das funções de numeração analítica compatíveis com o BigQuery, consulte a referência de funções de numeração.

As funções de numeração atribuem valores inteiros a cada linha com base na posição dentro da janela especificada.

Exemplo de RANK(), DENSE_RANK() e ROW_NUMBER():

SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) AS row_num
FROM ...
x rank dense_rank row_num
1 1 1 1
2 2 2 2
2 2 2 3
5 4 3 4
8 5 4 5
10 6 5 6
10 6 5 7
  • RANK(): para x=5, rank retorna 4, porque RANK() incrementa com o número de pares no grupo de ordenação da janela anterior.
  • DENSE_RANK(): para x=5, dense_rank retorna 3, porque sempre incrementa em 1, sem pular um valor.
  • ROW_NUMBER(): para x=5, row_num retorna 4.

Funções de agregação analítica

O BigQuery é compatível com determinadas funções agregadas como funções analíticas.

Com essas funções, a cláusula OVER é simplesmente anexada à chamada de função de agregação. A sintaxe da chamada de função permanece inalterada. Assim como acontece com os equivalentes da função de agregação, as funções analíticas realizam agregações, mas especificamente no frame da janela relevante de cada linha. Os tipos de dados resultantes dessas funções analíticas são iguais aos dos equivalentes da função de agregação.

Para uma descrição das funções analíticas agregadas compatíveis com o BigQuery, consulte a referência de funções analíticas agregadas.