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à.
Estensioni di aggregazione
Amazon Redshift supporta estensioni di aggregazione per eseguire il lavoro di più operazioni GROUP BY in un'unica istruzione.
Gli esempi di estensioni di aggregazione utilizzano la tabella orders, che contiene i dati di vendita di un'azienda di elettronica. Per creare gli orders, procedi come segue.
CREATE TABLE ORDERS ( ID INT, PRODUCT CHAR(20), CATEGORY CHAR(20), PRE_OWNED CHAR(1), COST DECIMAL ); INSERT INTO ORDERS VALUES (0, 'laptop', 'computers', 'T', 1000), (1, 'smartphone', 'cellphones', 'T', 800), (2, 'smartphone', 'cellphones', 'T', 810), (3, 'laptop', 'computers', 'F', 1050), (4, 'mouse', 'computers', 'F', 50);
GROUPING SETS
Calcola uno o più set di raggruppamento in una singola istruzione. Un set di raggruppamento è l'insieme di una singola clausola GROUP BY, un set di 0 o più colonne in base al quale è possibile raggruppare il set di risultati di una query. GROUP BY GROUPING SETS equivale all'esecuzione di una query UNION ALL su un set di risultati raggruppato in colonne diverse. Ad esempio, GROUP BY GROUP SETS((a), (b)) è equivalente a GROUP BY a UNION ALL GROUP BY b.
L'esempio seguente restituisce il costo dei prodotti nella tabella degli ordini raggruppati in base alle categorie dei prodotti e al tipo di prodotti venduti.
SELECT category, product, sum(cost) as total FROM orders GROUP BY GROUPING SETS(category, product); category | product | total ----------------------+----------------------+------- computers | | 2100 cellphones | | 1610 | laptop | 2050 | smartphone | 1610 | mouse | 50 (5 rows)
ROLLUP
Presuppone una gerarchia in cui le colonne precedenti sono considerate le colonne padri delle colonne successive. ROLLUP raggruppa i dati in base alle colonne fornite, restituendo righe di subtotali aggiuntive che rappresentano i totali in tutti i livelli di colonne di raggruppamento, oltre alle righe raggruppate. Ad esempio, puoi utilizzare GROUP BY ROLLUP((a), (b)) per restituire un set di risultati raggruppato prima per a, poi per b supponendo che b sia una sottosezione di a. ROLLUP restituisce anche una riga con l'intero set di risultati senza colonne di raggruppamento.
GROUP BY ROLLUP((a), (b)) è equivalente a GROUP BY GROUPING SETS((a,b), (a), ()).
L'esempio seguente restituisce il costo dei prodotti nella tabella degli ordini raggruppati prima per categoria e poi per prodotto, con product come sezione della categoria.
SELECT category, product, sum(cost) as total FROM orders GROUP BY ROLLUP(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | | 3710 (6 rows)
CUBE
Raggruppa i dati in base alle colonne fornite, restituendo righe di subtotali aggiuntive che rappresentano i totali in tutti i livelli di colonne di raggruppamento, oltre alle righe raggruppate. CUBE restituisce le stesse righe di ROLLUP, ma aggiunge ulteriori righe di subtotali per ogni combinazione di colonne di raggruppamento non previste da ROLLUP. Ad esempio, è possibile utilizzare GROUP BY CUBE((a), (b)) per restituire un set di risultati raggruppato prima per a, poi per b, supponendo che b sia una sottosezione di a, quindi solo per b. CUBE restituisce anche una riga con l'intero set di risultati senza colonne di raggruppamento.
GROUP BY CUBE((a), (b)) è equivalente a GROUP BY GROUPING SETS((a, b), (a), (b), ()).
L'esempio seguente restituisce il costo dei prodotti nella tabella degli ordini raggruppati prima per categoria e poi per prodotto, con product come sezione della categoria. A differenza dell'esempio precedente per ROLLUP, l'istruzione restituisce risultati per ogni combinazione di colonne di raggruppamento.
SELECT category, product, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | laptop | 2050 | mouse | 50 | smartphone | 1610 | | 3710 (9 rows)
Funzioni GROUPING/GROUPING_ID
ROLLUP e CUBE aggiungono valori NULL al set di risultati per indicare le righe di subtotali. Ad esempio, GROUP BY ROLLUP((a), (b)) restituisce una o più righe con un valore NULL nella colonna di raggruppamento b per indicare che si tratta di subtotali dei campi nella colonna di raggruppamento a. Questi valori NULL servono solo a soddisfare il formato delle tuple restituite.
Quando si eseguono operazioni GROUP BY con ROLLUP e CUBE su relazioni che memorizzano i valori NULL, è possibile produrre set di risultati con righe che sembrano avere colonne di raggruppamento identiche. Tornando all'esempio precedente, se la colonna di raggruppamento b contiene un valore NULL memorizzato, GROUP BY ROLLUP((a), (b)) restituisce una riga con un valore NULL nella colonna di raggruppamento b che non è un subtotale.
Per distinguere tra i valori NULL creati da ROLLUP e CUBE e i valori NULL memorizzati nelle tabelle stesse, è possibile utilizzare la funzione GROUPING o il relativo alias GROUPING_ID. GROUPING accetta un singolo set di raggruppamento come argomento e per ogni riga del set di risultati restituisce un valore di 0 o 1 bit corrispondente alla colonna di raggruppamento in quella posizione, quindi converte tale valore in un numero intero. Se il valore in quella posizione è un valore NULL creato da un'estensione di aggregazione, GROUPING restituisce 1. Restituisce 0 per tutti gli altri valori, inclusi i valori NULL memorizzati.
Ad esempio, GROUPING(category, product) può restituire i seguenti valori per una determinata riga, a seconda dei valori della colonna di raggruppamento per quella riga. Ai fini di questo esempio, tutti i valori NULL nella tabella sono creati da un'estensione di aggregazione.
| Colonna della categoria | Colonna del prodotto | Valore in bit della funzione GROUPING | Valore decimale |
|---|---|---|---|
| Non NULL | Non NULL | 00 | 0 |
| Non NULL | NULL | 01 | 1 |
| NULL | Non NULL | 10 | 2 |
| NULL | NULL | 11 | 3 |
Le funzioni di GROUPING vengono mostrate nella parte dell'elenco SELECT della query nel seguente formato.
SELECT ... [GROUPING( expr )...] ... GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} ( expr ) ...
L'esempio seguente è analogo all'esempio precedente per CUBE, ma con l'aggiunta di funzioni GROUPING per i relativi set di raggruppamento.
SELECT category, product, GROUPING(category) as grouping0, GROUPING(product) as grouping1, GROUPING(category, product) as grouping2, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 3,1,2; category | product | grouping0 | grouping1 | grouping2 | total ----------------------+----------------------+-----------+-----------+-----------+------- cellphones | smartphone | 0 | 0 | 0 | 1610 cellphones | | 0 | 1 | 1 | 1610 computers | laptop | 0 | 0 | 0 | 2050 computers | mouse | 0 | 0 | 0 | 50 computers | | 0 | 1 | 1 | 2100 | laptop | 1 | 0 | 2 | 2050 | mouse | 1 | 0 | 2 | 50 | smartphone | 1 | 0 | 2 | 1610 | | 1 | 1 | 3 | 3710 (9 rows)
ROLLUP e CUBE parziali
È possibile eseguire operazioni ROLLUP e CUBE con solo una parte dei subtotali.
La sintassi per le operazioni parziali di ROLLUP e CUBE è la seguente.
GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...])
Qui, la clausola GROUP BY crea solo righe di subtotali a livello di expr2 e successivi.
Gli esempi seguenti mostrano le operazioni parziali di ROLLUP e CUBE nella tabella degli ordini, raggruppate in base allo stato di usato o meno di un prodotto; le operazioni di ROLLUP e CUBE vengono quindi eseguite nelle colonne della categoria e del prodotto.
SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY pre_owned, ROLLUP(category, product) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 F | | | 6 | 1100 T | | | 6 | 2610 (9 rows) SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY pre_owned, CUBE(category, product) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 F | | laptop | 4 | 1050 F | | mouse | 4 | 50 T | | laptop | 4 | 1000 T | | smartphone | 4 | 1610 F | | | 6 | 1100 T | | | 6 | 2610 (13 rows)
Poiché la colonna dei prodotti usati non è inclusa nelle operazioni ROLLUP e CUBE, non esiste una riga totale complessiva che includa tutte le altre righe.
Raggruppamento concatenato
È possibile concatenare più clausole GROUPING SETS/ROLLUP/CUBE per calcolare diversi livelli di subtotali. I raggruppamenti concatenati restituiscono il prodotto cartesiano dei set di raggruppamento forniti.
La sintassi per concatenare le clausole GROUPING SETS/ROLLUP/CUBE è la seguente.
GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]), {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...]
L'esempio seguente mostra come un piccolo raggruppamento concatenato possa produrre un notevole set di risultati finali.
SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY CUBE(category, product), GROUPING SETS(pre_owned, ()) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 | cellphones | smartphone | 1 | 1610 | computers | laptop | 1 | 2050 | computers | mouse | 1 | 50 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 | cellphones | | 3 | 1610 | computers | | 3 | 2100 F | | laptop | 4 | 1050 F | | mouse | 4 | 50 T | | laptop | 4 | 1000 T | | smartphone | 4 | 1610 | | laptop | 5 | 2050 | | mouse | 5 | 50 | | smartphone | 5 | 1610 F | | | 6 | 1100 T | | | 6 | 2610 | | | 7 | 3710 (22 rows)
Raggruppamento nidificato
È possibile utilizzare le operazioni GROUPING SETS/ROLLUP/CUBE come GROUPING SETS espr per formare un raggruppamento nidificato. Il sottoraggruppamento all'interno dei GROUPING SETS nidificati viene appiattito.
La sintassi per il raggruppamento nidificato è la seguente:
GROUP BY GROUPING SETS({ROLLUP|CUBE|GROUPING SETS}(expr[, ...])[, ...])
Analizza l'esempio seguente.
SELECT category, product, pre_owned, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY GROUPING SETS(ROLLUP(category), CUBE(product, pre_owned)) ORDER BY 4,1,2,3; category | product | pre_owned | group_id | total ----------------------+----------------------+-----------+----------+------- cellphones | | | 3 | 1610 computers | | | 3 | 2100 | laptop | F | 4 | 1050 | laptop | T | 4 | 1000 | mouse | F | 4 | 50 | smartphone | T | 4 | 1610 | laptop | | 5 | 2050 | mouse | | 5 | 50 | smartphone | | 5 | 1610 | | F | 6 | 1100 | | T | 6 | 2610 | | | 7 | 3710 | | | 7 | 3710 (13 rows)
È importante notare che, poiché sia ROLLUP(category) che CUBE(product, pre_owned) contengono il set di raggruppamento (), la riga che rappresenta il totale complessivo viene duplicata.
Note per l'utilizzo
-
La clausola GROUP BY supporta fino a 64 set di raggruppamento. Nel caso di ROLLUP e CUBE o di una combinazione di GROUPING SETS, ROLLUP e CUBE, questa limitazione si applica al numero implicito di set di raggruppamento. Ad esempio, GROUP BY CUBE((a), (b)) conta per 4 set di raggruppamento, non 2.
-
Non è possibile utilizzare le costanti come colonne di raggruppamento quando si utilizzano le estensioni di aggregazione.
-
Non è possibile creare un set di raggruppamento che contiene colonne duplicate.
