Funções de janela - Amazon Redshift
Resumo da sintaxe de funções da janelaOrdenação exclusiva de dados para funções da janelaFunções compatíveisAmostra de tabela para exemplos de funções de janela

Funções de janela

Usando funções da janela, é possível criar consultas analíticas empresariais de forma mais eficiente. Funções de janela operam em uma partição ou “janela” de um conjunto de resultados e retornam um valor para cada linha naquela janela. Por outro lado, funções sem janela executam seus cálculos em relação a cada linha no conjunto de resultados. Diferente de funções de grupo que agregam linhas de resultado, as funções de janela retêm todas as linhas na expressão da tabela.

Os valores retornados são calculados usando valores dos conjuntos de linhas dessa janela. Para cada linha da tabela, a janela define um conjunto de linhas que é usado para computar atributos adicionais. Um janela é definida usando uma especificação de janela (a cláusula OVER) se baseia em três conceitos principais:

  • Particionamento da janela, que forma grupos de linhas (cláusula PARTITION)

  • Ordenação da janela, que define uma ordem ou sequência de linhas dentro de cada partição (cláusula ORDER BY)

  • Quadros da janela, que são definidos em relação a cada linha para restringir ainda mais o conjunto de linhas (especificação de ROWS)

As funções da janela são o último conjunto de operações executadas em uma consulta, exceto pela cláusula ORDER BY final. Todas as junções e todas as cláusulas WHERE, GROUP BY e HAVING são concluídas antes do processamento das funções da janela. Portanto, as funções da janela podem aparecer somente na lista de seleção ou na cláusula ORDER BY. Você pode usar várias funções da janela em uma única consulta com diferentes cláusulas de quadro. Você também pode usar funções da janela em outras expressões escalares, tal como CASE.

Resumo da sintaxe de funções da janela

As funções de janela seguem uma sintaxe padrão, mostrada a seguir.

function (expression) OVER (
[ PARTITION BY expr_list ]
[ ORDER BY order_list [ frame_clause ] ] )

Aqui, function é uma das funções descritas nesta seção.

A expr_list é como indicado a seguir.

expression | column_name [, expr_list ]

A order_list é como a seguir. 

expression | column_name [ ASC | DESC ] 
[ NULLS FIRST | NULLS LAST ]
[, order_list ]

O frame_clause é como a seguir. 

ROWS
{ UNBOUNDED PRECEDING | unsigned_value PRECEDING | CURRENT ROW } |

{ BETWEEN
{ UNBOUNDED PRECEDING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW}
AND
{ UNBOUNDED FOLLOWING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW }}

Argumentos

função

A função de janela. Para obter detalhes, consulte as descrições individuais da função.

OVER

A cláusula que define a especificação da janela. A cláusula OVER é obrigatória para funções da janela e diferencia funções da janela de outras funções SQL.

PARTITION BY expr_list

(Opcional) A cláusula PARTITION BY subdivide o conjunto de resultados em partições, bem como a cláusula GROUP BY. Se uma cláusula de partição estiver presente, a função será calculada para as linhas em cada partição. Se nenhuma cláusula de partição estiver especificada, uma única partição contém a tabela inteira e a função é computada para esta tabela completa.

As funções de classificação DENSE_RANK, NTILE, RANK e ROW_NUMBER exigem uma comparação global de todas as linhas no conjunto de resultados. Quando uma cláusula PARTITION BY é utilizada, o otimizador de consulta pode executar cada agregação em paralelo, distribuindo a workload em várias fatias de acordo com as partições. Se a cláusula PARTITION BY não estiver presente, a etapa de agregação deverá ser executada em série em uma única fatia, o que poderá ter um impacto negativo considerável na performance, sobretudo para grandes clusters.

O Amazon Redshift não oferece suporte a literais de string nas cláusulas PARTITION BY.

ORDER BY order_list

(Opcional) A função da janela é aplicada às linhas dentro de cada partição classificada de acordo com a especificação do pedido em ORDER BY. Esta cláusula ORDER BY é diferente e totalmente não relacionada a uma cláusula ORDER BY na frame_clause. A cláusula ORDER BY pode ser usada sem a cláusula PARTITION BY.

Para as funções de classificação, a cláusula ORDER BY identifica as medidas para os valores de classificação. Para funções de agregação, as linhas particionadas devem ser ordenadas antes que a função agregada seja computada para cada quadro. Para obter mais informações sobre os tipos de função da janela, consulte Funções de janela.

Os identificadores de coluna ou expressões que avaliam os identificadores de coluna são obrigatórios na lista de ordenação. Nem constantes ou expressões constantes podem ser usadas como substitutos para nomes de coluna.

Valores NULL são tratados como seu próprio grupo, ordenados e classificados de acordo com a opção NULLS FIRST ou NULLS LAST. Por padrão, os valores NULL são ordenados e classificados por último na ordem ASC e são ordenados e classificados primeiro na ordem DESC.

O Amazon Redshift não oferece suporte a literais de string nas cláusulas ORDER BY.

Se a cláusula ORDER BY for omitida, a ordem das linhas não será determinística.

nota

Em qualquer sistema paralelo, como o Amazon Redshift, quando uma cláusula ORDER BY não produz uma ordem única e total dos dados, a ordem das linhas não é determinística. Ou seja, se a expressão ORDER BY produzir valores duplicados (uma ordenação parcial), a ordem de retorno dessas linhas pode variar de uma execução do Amazon Redshift para a próxima. Por sua vez, funções da janela podem retornar resultados inesperados ou inconsistentes. Para obter mais informações, consulte Ordenação exclusiva de dados para funções da janela.

column_name

Nome de uma coluna a ser particionada por ou ordenada por.

ASC | DESC

Opção que define a ordem de classificação para a expressão, da seguinte forma:

  • ASC: ascendente (por exemplo, de valores numéricos menores para maiores e de "A" a "Z" para strings de caracteres). Se nenhuma opção é especificada, os dados são classificados na ordem ascendente por padrão.

  • DESC: descendente (de valores numéricos maiores para menores; de "Z" a "A" para strings).

NULLS FIRST | NULLS LAST

Opção que especifica se NULLS devem ser ordenados primeiro, antes de valores não nulos, ou por último, após valores não nulos. Por padrão, NULLs são ordenados e classificados por último na ordem ASC e ordenados e classificados primeiro na ordem DESC.

frame_clause

Para funções agregadas, a cláusula do quadro refina ainda mais o conjunto de linhas na janela de uma função ao usar ORDER BY. Ele permite que você inclua ou exclua conjuntos de linhas no resultado ordenado. A cláusula de quadro consiste na palavra-chave ROWS e nos especificadores associados.

A cláusula frame não se aplica a funções de classificação. Além disso, a cláusula frame não é necessária quando nenhuma cláusula ORDER BY é usada na cláusula OVER para uma função agregada. Se uma cláusula ORDER BY é usada para uma função agregada, uma cláusula de quadro explícita é necessária.

Quando nenhuma cláusula ORDER BY é especificada, o quadro implícito é ilimitado, equivalente a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

ROWS

Especificando um deslocamento físico da linha atual especificando um deslocamento físico da linha atual.

Essa cláusula especifica as linhas na janela ou particionamento atual ao qual o valor da linha atual dever ser combinado. Ela usa os argumentos que especificam a posição da linha, que pode ser antes ou depois da linha atual. O ponto de referência para todos os quadros de janela é a linha atual. Cada linha se torna a linha atual, por sua vez, à medida que o quadro de janela avança pela partição.

O quadro pode ser um conjunto simples de linhas até e incluindo a linha atual.

{UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW}

Ou pode ser um conjunto de linhas entre dois limites.

BETWEEN
{ UNBOUNDED PRECEDING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }
AND
{ UNBOUNDED FOLLOWING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }

UNBOUNDED PRECEDING indica que a janela começa na primeira linha da partição; deslocamento PRECEDING indica que a janela começa um número de linhas equivalentes ao valor do deslocamento antes da linha atual. UNBOUNDED PRECEDING é o padrão.

CURRENT ROW indica que a janela começa ou termina na linha atual.

UNBOUNDED FOLLOWING indica que a janela termina na última linha da partição; deslocamento FOLLOWING indica que a janela termina um número de linhas equivalentes ao valor do deslocamento depois da linha atual.

O offset identifica um número físico de linhas antes ou depois da linha atual. Nesse caso, o deslocamento deve ser uma constante que retorna um valor numérico positivo. Por exemplo, 5 FOLLOWING termina o quadro 5 linhas após a linha atual.

Onde BETWEEN não é especificado, o quadro é limitado implicitamente pela linha atual. Por exemplo, ROWS 5 PRECEDING é igual a ROWS BETWEEN 5 PRECEDING AND CURRENT ROW. Além disso, ROWS UNBOUNDED FOLLOWING é igual a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

nota

Você não pode especificar um quadro em que o limite inicial seja maior do que o limite final. Por exemplo, você não pode especificar nenhum dos quadros a seguir. 

between 5 following and 5 preceding
between current row and 2 preceding
between 3 following and current row

Ordenação exclusiva de dados para funções da janela

Se uma cláusula ORDER BY para uma função da janela não produz uma ordem única e total dos dados, a ordem das linhas não é determinística. Se a expressão ORDER BY produzir valores duplicados (uma ordenação parcial), a ordem de retorno dessas linhas pode variar em várias execuções. Nesse caso, as funções da janela também podem retornar resultados inesperados ou inconsistentes.

Por exemplo, a consulta a seguir retorna resultados diferentes ao longo de várias execuções. Esses resultados diferentes ocorrem porque order by dateid não produz uma ordenação exclusiva dos dados para a função da janela SUM. 

select dateid, pricepaid,
sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid |   sumpaid
--------+-----------+-------------
1827 |   1730.00 |     1730.00
1827 |    708.00 |     2438.00
1827 |    234.00 |     2672.00
...

select dateid, pricepaid,
sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid |   sumpaid
--------+-----------+-------------
1827 |    234.00 |      234.00
1827 |    472.00 |      706.00
1827 |    347.00 |     1053.00
...

Nesse caso, adicionar uma segunda coluna ORDER BY à função da janela pode resolver o problema. 

select dateid, pricepaid,
sum(pricepaid) over(order by dateid, pricepaid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid | sumpaid
--------+-----------+---------
1827 |    234.00 |  234.00
1827 |    337.00 |  571.00
1827 |    347.00 |  918.00
...

Funções compatíveis

O Amazon Redshift oferece suporte a dois tipos de funções da janela: agregação e classificação.

Veja a seguir as funções agregadas compatíveis:

Veja a seguir as funções de classificação compatíveis:

Amostra de tabela para exemplos de funções de janela

É possível encontrar exemplos de função de janela específicos com cada descrição de função. Alguns dos exemplos usam uma tabela chamada WINSALES, que contém 11 linhas, conforme mostrado a seguir.

SALESID DATEID SELLERID BUYERID QTY QTY_SHIPPED
30001 8/2/2003 3 B 10 10
10001 12/24/2003 1 C 10 10
10005 12/24/2003 1 A 30
40001 1/9/2004 4 A 40
10006 1/18/2004 1 C 10
20001 2/12/2004 2 B 20 20
40005 2/12/2004 4 A 10 10
20002 2/16/2004 2 C 20 20
30003 4/18/2004 3 B 15
30004 4/18/2004 3 B 20
30007 9/7/2004 3 C 30

O seguinte script cria e preenche a tabela de amostra WINSALES.

CREATE TABLE winsales(
  salesid int,
  dateid date,
  sellerid int,
  buyerid char(10),
  qty int,
  qty_shipped int);

INSERT INTO winsales VALUES
  (30001, '8/2/2003', 3, 'b', 10, 10),
  (10001, '12/24/2003', 1, 'c', 10, 10),
  (10005, '12/24/2003', 1, 'a', 30, null),	
  (40001, '1/9/2004', 4, 'a', 40, null),	
  (10006, '1/18/2004', 1, 'c', 10, null),	
  (20001, '2/12/2004', 2, 'b', 20, 20),
  (40005, '2/12/2004', 4, 'a', 10, 10),
  (20002, '2/16/2004', 2, 'c', 20, 20),
  (30003, '4/18/2004', 3, 'b', 15, null),
  (30004, '4/18/2004', 3, 'b', 20, null),	
  (30007, '9/7/2004', 3, 'c', 30, null);