SQL per raggruppare e totalizzare

In questo articolo focalizziamo l’attenzione sui metodi disponibili nel linguaggio SQL per analizzare i dati raggruppandoli e totalizzandoli.

I gruppi

La struttura base di un’istruzione select prevede due clausole per gestire i raggruppamenti dei record: GROUP BY e HAVING.

SELECT [DISTINCT] {*|colonna… [AS alias] …}
      FROM tabella [AS alias]…
      [WHERE condizione]
      [GROUP BY colonna…]
      [HAVING condizione…]
      [ORDER BY colonna [ASC|DESC]…]

Group by

La clausola group by consente di raggruppare le righe del result set in base a una o più  colonne oppure anche ad un’espressione. Nella clausola select possono essere elencate solo le colonne o le espressioni utilizzate nella clausola GROUP BY per effettuare il raggruppamento e funzioni di aggregazione.
Le funzioni di aggregazione sono elencate nella documentazione IBM https://www.ibm.com/docs/en/i/7.6.0?topic=functions-aggregate.

ATTENZIONE: la clausola group by non implica che il result set sia ordinato anche per i campi di raggruppamento; se si desidera ordinare il risultato bisogna specificare anche la clausola order by.

select DEP, sum(AMOUNT) as IMPORTO from SALES group by DEP order by DEP;
select left(DEP, 2) as FIL, sum(AMOUNT) as IMPORTO from SALES group by left(DEP, 2) order by FIL;

Having

Per effettuare delle selezioni sul result set raggruppato si usa la clausola HAVING. HAVING è l’equivalente della clausola WHERE.

ATTENZIONE: La clausola WHERE filtra i record prima che venga eseguita il raggruppamento, invece la clausola HAVING filtra il result set dopo che è già stato applicato il raggruppamento definito dalla clausola GROUP BY.

• La clausola HAVING può essere specificata anche senza la clausola GROUP BY
• Devono essere usati solo predicati con funzioni di aggregazione
• Suggerimento: NON usare HAVING per fare selezioni che possono essere scritte nella clausola WHERE

Nell’esempio seguente i record vengono raggruppati in base al campo DEP e viene totalizzato il campo AMOUNT, dopo il raggruppamento il result set viene filtrato in base al valore totale dell’importo di ogni deposito

select DEP, sum(AMOUNT) as IMPORTO from SALES group by DEP having sum(AMOUNT) > 50000 order by DEP;

Invece in questo esempio i record vengono raggruppati in base al campo DEP e viene totalizzato il campo AMOUNT, ma prima di tutto ciò i record vengono filtrati in base al valore dell’importo di ogni singolo record

select DEP, sum(AMOUNT) as IMPORTO from SALES where AMOUNT > 10000 group by DEP order by DEP;

Nel terzo esempio i record vengono raggruppati in base al campo DEP e viene totalizzato il campo AMOUNT, ma prima di tutto ciò i record vengono filtrati in base al campo deposito

select DEP, sum(AMOUNT) as IMPORTO from SALES where DEP between 'A ' and 'B99' group by DEP order by DEP;

questa istruzione è (dal punto di vista del risultato ottenuto) esattamente equivalente alla precedente, ma molto più penalizzante dal punto di vista delle prestazioni perché costringe il DB2 a totalizzare tutti i depositi anche se poi ne vengono mostrati solo alcuni

select DEP, sum(AMOUNT) as IMPORTO
  from SALES
 group by DEP
 having DEP between 'A  ' and 'B99'
 order by DEP;

la clausola HAVING può essere specificata anche con un funzione di aggregazione su un campo non utilizzato nella clausola select

select DEP, sum(AMOUNT) as IMPORTO 
  from SALES
 group by DEP
 having sum(QTY) > 1000
 order by DEP;

Un ultimo esempio prima di parlare dei supergruppi. Nelle funzioni di aggregazione è possibile – se necessario – nidificare il predicato case. Una situazione tipica è una tabella con i movimenti di magazzino (carisco/scarico) o i movimenti contabili (dare/avere) in cui il campo importo ha sempre il segno positivo ed esiste un altro campo che definisce il tipo movimento.

select DEP "Dep.", ITEM as "Articolo", sum(case TYPE when 'C' then QTY when 'S' then QTY*-1 end) as "Quantità" from WRHJRN group by DEP, ITEM having sum(case TYPE when 'C' then QTY when 'S' then QTY*-1 end) <> 0 order by DEP, ITEM;

I supergruppi

I supergruppi sono disponibili dalla versione IBM i 6.1.

Spendiamo due parole per spiegare la teoria e poi illustriamo degli esempi pratici che aiuteranno sicuramente a mostrare la potenzialità di queste clausole e funzioni.

• GROUPING SETS: consente raggruppamenti multipli (ovvero basati su più colonne) in una singola istruzione SQL. Può essere pensato come all’unione di due o più raggruppamenti in un singolo result set. Il grouping sets specificato su un solo campo è equivalente alla clausola group by semplice che abbiamo illustrato in precedenza
• SUPER-GROUPS:
  • ROLLUP: produce un result set che contiene le righe dei subtotali oltre ai “regolari” totali
    ROLLUP(a, b, c) è equivalente a GROUPING SETS((a, b, c), (a, b), (a), ())
  • CUBE: produce un result set che contiene tutte le righe prodotte da ROLLUP e in aggiunta tutte le righe “cross-tabulation”
    CUBE(a, b, c) è equivalente a
    GROUPING SETS((a, b, c), (a, b), (a, c), (b, c), (a), (b), (c), ())

Utilizzo di grouping sets

Esempio di un raggruppamento semplice per deposito e agente ovvero ottengo la
totalizzazione del campo importo a “rottura” del campo deposito + agente.

select DEP, SALREP as AGENTE, sum(AMOUNT) as IMPORTO from SALES group by DEP, SALREP order by DEP, SALREP;

Esempio di raggruppamento su due livelli per campo deposito e per campo agente
ovvero ottengo la totalizzazione del campo importo a “rottura” del campo deposito (evidenziato in verde) e in aggiunta la totalizzazione a “rottura” del campo agente (evidenziato in blu)

select DEP, SALREP as AGENTE, sum(AMOUNT) as IMPORTO from SALES group by grouping sets(DEP, SALREP) order by DEP, SALREP;

Da notare che nell’ordinamento il valore null è considerato come il valore maggiore rispetto a tutti gli altri, quindi in un ordinamento ascendente i record con null sono alla fine del result set

Se voglio avere nel result set anche il totale “generale” posso specificare nella clausola
grouping sets come ultimo campo un gruppo di parentesi vuote

select DEP, SALREP as AGENTE, sum(AMOUNT) as IMPORTO from SALES group by grouping sets(DEP, SALREP, ()) order by DEP, SALREP;

Utilizzo di grouping expression complesse con grouping sets

Nei due esempi seguenti vediamo la differenza del result set ottenuto utilizzando
come argomento della clausola grouping sets 3 campi combinati in modo diverso
tramite l’utilizzo delle parentesi.
Nel primo esempio otteniamo l’unione dei totali sul campo deposito (verde), agente (blu) e sigla del prodotto (giallo)

select DEP, SALREP as AGENTE, BRAND as SIGLA sum(AMOUNT) as IMPORTO from SALES group by grouping sets(DEP, SALREP, BRAND) order by DEP, AGENTE, SIGLA;

Nel secondo esempio otteniamo invece i totali sul campo deposito + agente (verde) e sigla (blu)

select DEP, SALREP as AGENTE, BRAND as SIGLA, sum(AMOUNT) as IMPORTO from SALES group by grouping sets((DEP, SALREP), BRAND) order by DEP, AGENTE, SIGLA;

Utilizzo di rollup

Riprendiamo gli esempi precedenti utilizzati per illustrare grouping sets e proviamo ad utilizzare rollup.

select DEP, SALREP as AGENTE, sum(AMOUNT) as IMPORTO from SALES group by rollup(DEP, SALREP) order by DEP, AGENTE;

Notiamo che la query restituisce più record rispetto all’istruzione equivalente che usava
grouping sets o al raggruppamento semplice con group by. Nella figura sono evidenziati in
giallo i record restituiti anche da grouping sets e in verde quelli restituiti da un
raggruppamento semplice con la sola clausola group by dep, salrep.
In altri termini si può dire che rollup restituisce tutti i record restituiti dalla clausola group by semplice (in verde) più i subtotali per deposito (in giallo) più il totale generale (in bianco).

Una sintassi alternativa per la clausola group by equivalente a quella del precedente esempio:

. . . group by DEP, SALREP with rollup . . .

Procediamo con un esempio più complesso utilizzando rollup su 3 campi: deposito, agente e sigla prodotto

select DEP, SALREP as AGENTE, BRAND as SIGLA, sum(AMOUNT) as IMPORTO from SALES group by rollup(DEP, SALREP, BRAND) order by DEP, AGENTE, SIGLA;

La query restituirà quindi tutti i record che verrebbero restituiti utilizzando il group by semplice sui campi STCDEP, STCAG1, STCC01 (n.b. non visualizzati nella figura a lato) ed in aggiunta:
– i subtotali per deposito + agente (in giallo)
– i subtotali per deposito (in verde)
– il totale generale (in bianco)

Modificando la clausola rollup raggruppando con parentesi i campi deposito e agente
rollup((STCDEP, STCAG1), STCC01)
otterremo un risultato simile al precedente ma senza i record con i subtotali per deposito.

 

Utilizzo di cube

Riprendiamo gli esempi precedenti utilizzati per illustrare grouping sets e rollup e proviamo
ad utilizzare cube

select DEP, SALREP as AGENTE, sum(AMOUNT) as IMPORTO from SALES group by cube(DEP, SALREP) order by DEP, AGENTE;

Notiamo che la query restituisce tutti i record restituiti dall’istruzione equivalente che usava rollup e in aggiunta i record con tutte le altre combinazioni di subtotali, ovvero nel nostro esempio i subtotali per agente (evidenziati in giallo).

Raggruppando con cube i campi deposito, agente e sigla prodotto

select DEP, SALREP as AGENTE, BRAND as SIGLA,
   sum(AMOUNT) as IMPORTO
  from SALES
 group by cube(DEP, SALREP, BRAND)
 order by DEP, AGENTE, SIGLA;

La query restituirà i subtotali per:
– deposito + agente + sigla
– deposito + agente
– deposito
– deposito + sigla
– agente + sigla
– agente
– sigla
e il totale generale. In giallo sono evidenziati i subtotali restituiti in più rispetto alla corrispondente istruzione che usa rollup invece di cube.

Identificare le “rotture” di livello

Nel result set ottenuto utilizzando rollup o cube può essere necessario identificare quali record sono i subtotali derivanti da cube o rollup.

Un primo metodo può essere quello di identificare i record che hanno i campi contenenti i valori null.

Finché nel database non si utilizzano record che contengono valori null si può essere certi che i null presenti nel result set siano quelli generati dal raggruppamento del DB2, altrimenti si possono creare situazioni equivoche.

Esiste la funzione grouping che identifica in maniera precisa quali sono le righe dei subtotali rispetto ad una delle colonne presenti nella clausola group by.

select DEP, SALREP as AGENTE, sum(AMOUNT) as IMPORTO, grouping(DEP) "LIV DEP", grouping(SALREP) "LIV AG" from SALES group by rollup(DEP, SALREP) order by DEP, AGENTE;

La funzione grouping sul campo agente restituisce ‘1’ sui record contenenti i subtotali al suo livello di raggruppamento ovvero quando il record contiene il totale di tutti gli agenti per un codice deposito (in giallo)
La funzione grouping sul campo deposito restituisce ‘1’ in pratica solo sul record (in verde) del totale generale in quanto nella clausola rollup non c’è nessun altro campo di raggruppamento ad un livello superiore al deposito.
Da notare che sul record di totale anche la funzione grouping sul campo agente restituisce anch’essa ‘1’, in quanto anche a livello del campo agente quel record rappresenta un subtotale generato dal raggruppamento del DB2.

Infine analizziamo un esempio di utilizzo della funzione grouping con un raggruppamento di tipo cube

select DEP, SALREP as AGENTE, sum(AMOUNT) as IMPORTO, grouping(DEP) "LIV DEP", grouping(SALREP) "LIV AG" from SALES group by cube(DEP, SALREP) order by DEP, AGENTE;

In giallo sono evidenziati i subtotali dove la funzione grouping sul campo agente restituisce ‘1’, ovvero i subtotali per deposito.
In verde sono evidenziati i subtotali per i quali la funzione grouping sul campo deposito restituisce ‘1’: ovvero il record del totale generale e i subtotali per agente.