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

Conceitos de funções analíticas

Neste tópico, explicamos como as funções analíticas funcionam no BigQuery. Para ver 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 o 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 deseja 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 do 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, argument_list não será possível 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, argument_list será possível 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
  • um window_frame_clause ou window_name, que faça referência 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 nas quais a função analítica é avaliada de forma independente. Várias expressions são permitidas 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
  • Array

Essa lista é quase idêntica à lista de tipos de dados que GROUP BY não suporta, 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 ORDER BY normal nestes 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 no aspecto em que os tipos a seguir não são compatíveis com ordenação:

  • Array
  • 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, sobre a qual a função analítica é avaliada. window_frame_clause permite frames de janela físicos (definidos por meio de ROWS) e frames de janela lógicos (definido 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, ambos os quais 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 utilizar 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 pode conter PRECEDING.
    2. Se window_frame_boundary_start contiver FOLLOWING, window_frame_boundary_end não pode 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 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

O numeric_expression em window_frame_clause é interpretado 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 o window frame de uma linha se estender além do início ou fim da partição, o window frame incluirá somente linhas de dentro dela.

Exemplo: considere a 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 y, e ORDER BY ordena as linhas dentro de cada partição pelo valor 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 no window frame dela.

  • O 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
  • Para a segunda linha da partição, o 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
  • Para a última linha da partição, o window frame inclui apenas 2 linhas, pois 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 de janela em RANGE calculam o window frame de acordo com um intervalo lógico de linhas em torno da linha atual com base no valor da chave ORDER BY dela. O valor do intervalo fornecido é adicionado ou subtraído do valor da chave da linha atual para definir um limite de inicio e fim do intervalo do 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 no window_frame_clause é interpretado como um deslocamento do valor da chave ORDER BY da linha atual. O numeric_expression precisa ter um tipo numérico. No momento, DATE e TIMESTAMP não são compatíveis. Além disso, o numeric_expression tem que ser um número inteiro contante 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 as células coloridas indicam todas as linhas no window frame dela.

  • Para a linha 1, x = 5. Portanto, COUNT(*) incluirá somente as linhas nas quais 3 <=x <= 7
x count_x
5 1
2
11
2
8
10
1
  • Para a linha 2, x = 2. Portanto, COUNT(*) incluirá somente as linhas nas quais 0 <=x <= 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 nas quais 9 <=x <= 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 cujo window_name pode ser referido nas funções analíticas na lista SELECT. Isso é útil quando você quiser usar o mesmo 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, você pode usar as janelas nomeadas nas funções analíticas, mas somente na lista SELECT. Elas não podem ser usadas 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, o window_specification associado ao window_name terá de ser compatível com PARTITION BY, ORDER BY e window_frame_clause que estão na mesma cláusula OVER.

As regras a seguir aplicam-se a janelas nomeadas:

  • Você pode indicar as janelas nomeadas somente 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 fazer referência 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 um 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 ORDER BY.
    3. window_frame_clause da W1, se houver.

Neste tópico, explicamos as funções de navegação analíticas. Para ver uma descrição das funções de navegação analíticas que o BigQuery suporta, 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, explicamos as funções de numeração analítica. Para ver uma explicação das funções de numeração analítica que o BigQuery suporta, 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, uma vez que RANK() incrementa pelo número de pares no grupo de ordenação da janela anterior.
  • DENSE_RANK(): Para x=5, dense_rank retorna 3, uma vez que DENSE_RANK() sempre incrementa por 1, sem pular um valor.
  • ROW_NUMBER():Para x=5, row_num retorna 4.

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

O BigQuery suporta 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 ver uma descrição das funções analíticas agregadas compatíveis com o BigQuery, consulte a referência de funções analíticas agregadas.

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.