Funções de janela - AWS Clean Rooms
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

As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá.

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. Uma janela é definida usando uma especificação de janela (a OVER cláusula) e é baseada em três conceitos principais:

  • Particionamento de janelas, que forma grupos de linhas (PARTITIONcláusula)

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

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

As funções de janela são o último conjunto de operações realizadas em uma consulta, exceto pela cláusula ORDER BY final. Todas as junções e todasWHERE, GROUP BY e as HAVING cláusulas são concluídas antes que as funções da janela sejam processadas. Portanto, as funções de 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 de janela em outras expressões escalares, comoCASE.

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 OVER cláusula é obrigatória para funções de janela e diferencia as funções de janela de outras SQL funções.

PARTITIONPOR expr_list

(Opcional) A cláusula PARTITION BY subdivide o conjunto de resultados em partições, assim como a cláusula BY. GROUP 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 _ RANKNTILE,RANK, e ROW _ NUMBER exigem uma comparação global de todas as linhas no conjunto de resultados. Quando uma cláusula PARTITION BY é usada, o otimizador de consultas pode executar cada agregação em paralelo distribuindo a carga de trabalho 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 pode ter um impacto negativo significativo no desempenho, especialmente para clusters grandes.

AWS Clean Rooms não oferece suporte a literais de string nas cláusulas PARTITION BY.

ORDERPOR order_list

(Opcional) A função de janela é aplicada às linhas dentro de cada partição classificada de acordo com a especificação da ordem em ORDER BY. Essa ORDER cláusula BY é distinta e completamente não relacionada às cláusulas ORDER BY na frame_clause. A ORDER cláusula BY pode ser usada sem a cláusula PARTITION BY.

Para funções de classificação, a ORDER cláusula 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.

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

AWS Clean Rooms não oferece suporte a literais de string nas cláusulas ORDER BY.

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

nota

Em qualquer sistema paralelo AWS Clean Rooms, como quando uma cláusula ORDER BY não produz uma ordenação ú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 poderá variar de uma sequência AWS Clean Rooms para outra. 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 baixo para alto para valores numéricos e de 'A' a 'Z' para cadeias de caracteres). Se nenhuma opção é especificada, os dados são classificados na ordem ascendente por padrão.

  • DESC: decrescente (de alto a baixo para valores numéricos; de 'Z' a 'A' para strings).

NULLS FIRST | NULLS LAST

Opção que especifica se NULLS deve ser ordenada primeiro, antes de valores não nulos, ou por último, depois de valores não nulos. Por padrão, NULLS são classificados e classificados em último lugar na ASC ordem e classificados e classificados em primeiro lugar na ordem. DESC

frame_clause

Para funções agregadas, a cláusula frame refina ainda mais o conjunto de linhas na janela de uma função ao usar BY. ORDER Ele permite que você inclua ou exclua conjuntos de linhas no resultado ordenado. A cláusula frame consiste na ROWS palavra-chave 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 OVER cláusula para uma função agregada. Se uma cláusula ORDER BY for usada para uma função agregada, será necessária uma cláusula de quadro explícita.

Quando nenhuma ORDER cláusula 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 }

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

CURRENTROWindica que a janela começa ou termina na linha atual.

UNBOUNDEDFOLLOWINGindica que a janela termina na última linha da partição; o deslocamento FOLLOWING indica que a janela encerra um número de linhas equivalente ao valor do deslocamento após a 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 cinco linhas após a linha atual.

Onde não BETWEEN é 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 ORDER cláusula BY para uma função de janela não produzir uma ordenação exclusiva e total dos dados, a ordem das linhas não será determinística. Se a expressão ORDER BY produzir valores duplicados (uma ordenação parcial), a ordem de retorno dessas linhas poderá 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 produzem uma ordenação exclusiva dos dados para a função de SUM janela. 

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 de 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

AWS Clean Rooms suporta dois tipos de funções de 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 chamadaWINSALES, que contém 11 linhas, conforme mostrado na tabela 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