Funzioni finestra - AWS Clean Rooms
Riepilogo della sintassi della funzione finestraOrdinamento univoco dei dati per le funzioni finestraFunzioni supportateTabella di esempio per gli esempi della funzione finestra

Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

Funzioni finestra

Le funzioni finestra ti consentono di creare query aziendali analitiche in modo più efficiente. Le funzioni finestra operano su una partizione o "finestra" di un insieme di risultati e restituiscono un valore per ogni riga in quella finestra. Tuttavia, le funzioni non finestra eseguono i calcoli in relazione a ogni riga del set di risultati. A differenza delle funzioni di gruppo che aggregano le righe dei risultati, le funzioni finestra mantengono tutte le righe nell'espressione della tabella.

I valori restituiti sono calcolati utilizzando i valori dai set di righe in quella finestra. Per ogni riga nella tabella, la finestra definisce un set di righe che viene utilizzato per calcolare gli attributi aggiuntivi. Una finestra viene definita utilizzando una specifica della finestra (la clausola OVER) e si basa su tre concetti principali:

  • Partizionamento della finestra, che forma gruppi di righe (clausola PARTITION)

  • Ordinamento della finestra, che definisce un ordine o una sequenza di righe all'interno di ciascuna partizione (clausola ORDER BY)

  • Frame della finestra, che sono definiti in relazione a ciascuna riga per restringere ulteriormente l'insieme di righe (specifica ROWS)

Le funzioni finestra sono l'ultimo insieme di operazioni eseguite in una query ad eccezione della clausola ORDER BY finale. Tutti i join e tutte le clausole WHERE, GROUP BY e HAVING vengono completati prima che le funzioni finestra vengano elaborate. Pertanto, le funzioni finestra possono essere visualizzate solo nell'elenco di selezione o nella clausola ORDER BY. È possibile utilizzare più funzioni finestra all'interno di una singola query con diverse clausole del frame. È inoltre possibile utilizzare le funzioni finestra in altre espressioni scalari, come ad esempio CASE.

Riepilogo della sintassi della funzione finestra

Le funzioni della finestra seguono una sintassi standard, che è la seguente.

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

Qui, function è una delle funzioni descritte in questa sezione.

L'expr_list è il seguente.

expression | column_name [, expr_list ]

L'order_list è il seguente. 

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

La frame_clause è la seguente. 

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

Argomenti

funzione

La funzione finestra. Per informazioni dettagliate, vedere le descrizioni della singola funzione.

OVER

La clausola che definisce la specifica della finestra. La clausola OVER è obbligatoria per le funzioni finestra e le contraddistingue da altre funzioni SQL.

PARTITION BY expr_list

(Facoltativo) La clausola PARTITION BY suddivide il set di risultati in partizioni in modo molto simile alla clausola GROUP BY. Se è presente una clausola di partizione, la funzione viene calcolata per le righe in ogni partizione. Se non viene specificata alcuna clausola di partizione, una singola partizione contiene l'intera tabella e la funzione viene calcolata per quella tabella completa.

Le funzioni di classificazione DENSE_RANK, NTILE, RANK e ROW_NUMBER richiedono un confronto globale di tutte le righe nell'insieme di risultati. Quando viene utilizzata una clausola PARTITION BY, l'ottimizzatore della query può eseguire ciascuna aggregazione in parallelo distribuendo il carico di lavoro su più sezioni in base alle partizioni. Se la clausola PARTITION BY non è presente, la fase di aggregazione deve essere eseguita in serie su una singola sezione, che può avere un impatto negativo significativo sulle prestazioni, in particolare per i cluster di grandi dimensioni.

AWS Clean Rooms non supporta stringhe letterali nelle clausole PARTITION BY.

ORDER BY order_list

(Facoltativo) La funzione finestra viene applicata alle righe all'interno di ciascuna partizione ordinata in base alla specifica dell'ordine in ORDER BY. Questa clausola ORDER BY è distinta e completamente non correlata a una clausola ORDER BY in una frame_clause. La clausola ORDER BY può essere utilizzata senza la clausola PARTITION BY.

Per le funzioni di classificazione, la clausola ORDER BY identifica le misure per i valori di classificazione. Per le funzioni di aggregazione, le righe partizionate devono essere ordinate prima che la funzione di aggregazione sia calcolata per ciascun frame. Per ulteriori informazioni sui tipi di funzione finestra, consultare Funzioni finestra.

Gli identificatori o le espressioni di colonna che valutano gli identificatori di colonna sono obbligatori nell'elenco degli ordini. Né le costanti né le espressioni costanti possono essere utilizzate come sostituti dei nomi delle colonne.

I valori NULL vengono trattati come il proprio gruppo, ordinati e classificati in base all'opzione NULLS FIRST o NULLS LAST. Per impostazione predefinita, i valori NULL vengono ordinati e classificati per ultimi in ordine ASC e ordinati e classificati per primi in ordine DESC.

AWS Clean Rooms non supporta stringhe letterali nelle clausole ORDER BY.

Se viene omessa la clausola ORDER BY, l'ordine delle righe non è deterministico.

Nota

In qualsiasi sistema parallelo AWS Clean Rooms, ad esempio quando una clausola ORDER BY non produce un ordinamento unico e totale dei dati, l'ordine delle righe non è deterministico. Cioè, se l'espressione ORDER BY produce valori duplicati (un ordinamento parziale), l'ordine di restituzione di tali righe potrebbe variare da una sequenza all'altra. AWS Clean Rooms A loro volta, le funzioni finestra potrebbero restituire risultati inattesi o incoerenti. Per ulteriori informazioni, consultare Ordinamento univoco dei dati per le funzioni finestra.

column_name

Nome di una colonna da partizionare o da ordinare.

ASC | DESC

Opzione che definisce l'ordinamento per l'espressione, come segue:

  • ASC: crescente (ad esempio, dal più piccolo al più grande per i valori numerici e da 'A' a 'Z' per le stringhe di caratteri). Se non viene specificata alcuna opzione, i dati vengono ordinati in ordine crescente per impostazione predefinita.

  • DESC: decrescente (ad esempio, dal più grande al più piccolo per i valori numerici e da 'Z' ad 'A' per le stringhe).

NULLS FIRST | NULLS LAST

Opzione che specifica se NULLS deve essere ordinato per primo, prima di valori non null, o per ultimo, dopo valori non null. Per impostazione predefinita, i NULLS vengono ordinati e classificati per ultimi in ordine ASC e ordinati e classificati per primi in ordine DESC.

frame_clause

Per le funzioni di aggregazione, la clausola frame perfeziona ulteriormente l'insieme di righe in una finestra della funzione quando si utilizza ORDER BY. Fornisce la capacità di includere o escludere set di righe all'interno del risultato ordinato. La clausola frame è composta dalla parola chiave ROWS e dagli specificatori associati.

La clausola frame non si applica alle funzioni di classificazione. Inoltre, la clausola frame non è richiesta quando non viene utilizzata alcuna clausola ORDER BY nella clausola OVER per una funzione di aggregazione. Se una clausola ORDER BY viene utilizzata per una funzione di aggregazione, è necessaria una clausola del frame esplicita.

Quando non viene specificata alcuna clausola ORDER BY, il frame implicito è illimitato: equivalente a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

ROWS

Questa clausola definisce il frame della finestra specificando una compensazione fisica dalla riga corrente.

Questa clausola specifica le righe nella finestra o partizione corrente con cui deve essere combinato il valore nella riga corrente. Usa argomenti che specificano la posizione della riga, che può essere prima o dopo la riga corrente. Il punto di riferimento per tutti i frame della finestra è la riga corrente. Ogni riga diventa a sua volta la riga corrente mentre il frame della finestra scorre in avanti nella partizione.

Il frame può essere un semplice insieme di righe fino a includere la riga corrente:

{UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW}

Oppure può essere un insieme di righe tra due limiti.

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

UNBOUNDED PRECEDING indica che la finestra inizia nella prima riga della partizione; offset PRECEDING indica che la finestra inizia un numero di righe equivalente al valore di compensazione prima della riga corrente. UNBOUNDED PRECEDING è il valore predefinito.

CURRENT ROW indica che la finestra inizia o termina nella riga corrente.

UNBOUNDED FOLLOWING indica che la finestra termina nell'ultima riga della partizione; offset FOLLOWING indica che la finestra termina un numero di righe equivalente al valore di compensazione dopo la riga corrente.

offset identifica un numero fisico di righe prima o dopo la riga corrente. In questo caso, offset deve essere una costante che valuta un valore numerico positivo. Ad esempio, 5 FOLLOWING terminerà il frame 5 righe dopo la riga corrente.

Laddove BETWEEN non viene specificato, il frame è implicitamente limitato dalla riga corrente. Ad esempio, ROWS 5 PRECEDING è uguale a ROWS BETWEEN 5 PRECEDING AND CURRENT ROW. Inoltre, ROWS UNBOUNDED FOLLOWING è uguale a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

Nota

Non è possibile specificare un frame in cui il limite iniziale è maggiore del limite finale. Ad esempio, non è possibile specificare nessuno di questi frame: 

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

Ordinamento univoco dei dati per le funzioni finestra

Se una clausola ORDER BY per una funzione finestra non produce un ordinamento univoco e totale dei dati, l'ordine delle righe è non deterministico. Se l'espressione ORDER BY produce valori duplicati (un ordinamento parziale), l'ordine di restituzione di tali righe può variare in più esecuzioni. In questo caso, le funzioni finestra possono restituire risultati inattesi o incoerenti.

Ad esempio, la seguente query restituisce risultati diversi su più esecuzioni. Si ottengono questi risultati diversi perché order by dateid non genera un ordinamento univoco dei dati per la funzione finestra 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
...

In questo caso, l'aggiunta di una seconda colonna ORDER BY alla funzione finestra potrebbe risolvere il 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
...

Funzioni supportate

AWS Clean Rooms supporta due tipi di funzioni delle finestre: aggregate e di classificazione.

Queste sono le funzioni di aggregazione supportate:

Queste sono le funzioni di classificazione supportate:

Tabella di esempio per gli esempi della funzione finestra

Sono presenti esempi di funzione finestra specifici con la descrizione di ogni funzione. Alcuni esempi utilizzano una tabella denominata WINSALES, che contiene 11 righe, come illustrato nella tabella seguente.

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