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 OVER clausola) e si basa su tre concetti principali:

• Partizionamento delle finestre, che forma gruppi di righe (clausola) PARTITION

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

• Cornici delle finestre, che sono definite in relazione a ciascuna riga per limitare ulteriormente l'insieme di righe (specificazione) ROWS

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

Le funzioni della finestra non possono essere annidate. Ad esempio, una funzione aggregata SUM può apparire all'interno di una funzione finestraSUM, ma una funzione finestra non SUM può apparire all'interno di un'altra funzione di finestra. SUM Quanto segue non è supportato perché una funzione finestra è annidata in un'altra funzione di finestra.

SELECT SUM(SUM(selectcol) OVER (PARTITION BY ordercol)) OVER (Partition by ordercol) FROM t;

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 OVER clausola è obbligatoria per le funzioni delle finestre e differenzia le funzioni delle finestre dalle altre funzioni. SQL

PARTITIONDI expr_list

(Facoltativo) La clausola PARTITION BY suddivide il set di risultati in partizioni, proprio come la clausola BY. GROUP 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, NTILERANK, e ROW _ NUMBER richiedono un confronto globale di tutte le righe del set di risultati. Quando viene utilizzata una clausola PARTITION BY, l'ottimizzatore di query può eseguire ogni aggregazione in parallelo distribuendo il carico di lavoro su più slice in base alle partizioni. Se la clausola PARTITION BY non è presente, la fase di aggregazione deve essere eseguita in serie su una singola slice, il che può avere un impatto negativo significativo sulle prestazioni, specialmente per i cluster di grandi dimensioni.

Amazon Redshift non supporta stringhe letterali nelle PARTITION clausole BY.

ORDERBY order_list

(Facoltativo) La funzione window viene applicata alle righe all'interno di ciascuna partizione ordinate in base all'ordine specificato in BY. ORDER Questa clausola ORDER BY è distinta e completamente estranea alle clausole ORDER BY di frame_clause. La clausola ORDER BY può essere utilizzata senza la clausola BY. PARTITION

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.

NULLSi valori vengono trattati come un gruppo a sé stante, ordinati e classificati in base all'opzione or. NULLS FIRST NULLS LAST Per impostazione predefinita, NULL i valori vengono ordinati e classificati per ultimi nell'ASCordinamento e ordinati e classificati per primi nell'ordinamento. DESC

Amazon Redshift non supporta stringhe letterali nelle ORDER clausole BY.

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

Nota

In qualsiasi sistema parallelo come Amazon Redshift, 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 un'esecuzione di Amazon Redshift all'altra. 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ù basso al più alto 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 (dal più alto al più basso per i valori numerici; da «Z» a «A» per le stringhe).

NULLS FIRST | NULLS LAST

Opzione che specifica se NULLS deve essere ordinata per prima, prima dei valori non nulli, o per ultima, dopo i valori non nulli. Per impostazione predefinita, NULLS vengono ordinati e classificati per ultimi nell'ordinamento e ordinati e classificati per ASC primi nell'ordinamento. DESC

frame_clause

Per le funzioni aggregate, la clausola frame perfeziona ulteriormente l'insieme di righe nella finestra di una funzione quando si utilizza BY. ORDER Fornisce la capacità di includere o escludere set di righe all'interno del risultato ordinato. La clausola frame è composta dalla ROWS parola chiave 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 per una funzione aggregataOVER. Se viene utilizzata una clausola ORDER BY per una funzione aggregata, è richiesta una clausola 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 }

UNBOUNDEDPRECEDINGindica che la finestra inizia dalla prima riga della partizione; offset PRECEDING indica che la finestra avvia un numero di righe equivalente al valore di offset prima della riga corrente. UNBOUNDEDPRECEDINGè l'impostazione predefinita.

CURRENTROWindica che la finestra inizia o termina nella riga corrente.

UNBOUNDEDFOLLOWINGindica che la finestra termina all'ultima riga della partizione; offset FOLLOWING indica che la finestra termina un numero di righe equivalente al valore di offset 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 termina il riquadro cinque righe dopo la riga corrente.

Dove non BETWEEN è specificato, il riquadro è delimitato implicitamente 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 unico 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. Questi risultati diversi si verificano perché order by dateid non producono un ordinamento univoco dei dati per la SUM funzione finestra.

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 window può 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

Amazon Redshift supporta due tipi di funzioni finestra: di aggregazione e di classificazione.

Queste sono le funzioni di aggregazione supportate:

• AVGfunzione window

• COUNTfunzione finestra

• CUMEfunzione _ DIST window

• DENSEfunzione _ RANK window

• FIRSTfunzione _ VALUE window

• LAGfunzione finestra

• LAST_ funzione VALUE window

• LEADfunzione finestra

• LISTAGGfunzione finestra

• MAXfunzione window

• MEDIANfunzione finestra

• MINfunzione finestra

• NTHfunzione _ VALUE window

• PERCENTILEfunzione _ CONT window

• PERCENTILE_ funzione window DISC

• RATIOREPORTfunzione finestra _TO_

• STDDEVfunzioni _ e _ window SAMP STDDEV POP(STDDEV_ SAMP e STDDEV sono sinonimi)

• SUMfunzione finestra

• VARfunzioni _ SAMP e _ window VAR POP(VAR_ SAMP e VARIANCE sono sinonimi)

Queste sono le funzioni di classificazione supportate:

• DENSEfunzione _ RANK window

• NTILEfunzione window

• PERCENT_ funzione window RANK

• RANKfunzione finestra

• ROWfunzione _ NUMBER window

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 denominataWINSALES, che contiene 11 righe, come illustrato di seguito.

SALESID	DATEID	SELLERID	BUYERID	QTY	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

Lo script seguente crea e popola la WINSALES tabella di esempio.

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);