SQL: query multiaggregazione

Come unire in una sola riga diverse interrogazioni di conteggio

di Armando Pagliara, 18/11/2012

Dopo tanti articoli orientati a pensieri personali o a sensazioni, voglio scrivere qualcosa di tecnico, che possa essere utile a chi, come me, lavora nel settore IT e ha bisogno di interrogare basi di dati per riuscere a recuperare diversi aggregatori in una sola interrogazione su una sola riga.

Prima di procedere ricordiamo che un aggregatore è un operatore matematico che restituisce un valore singolo a partire da un insieme di valori distinti (semplicemente un conteggio, una somma, una media, etc.).

Un semplice esempio da implementare può costituire il conteggio di persone che vivono in una città.
Per semplicità assumiamo che una riga della tabella PERSONA possa contenere anche il nome della città dove questa vive. Di conseguenza:

SELECT COUNT(*) FROM PERSONA WHERE CITTA='Roma'

restituirà il numero di persone che vivono a Roma.

E se volessimo ottenere, in una stessa interrogazione, il numero di abitanti di Roma e Milano?
È chiaro che non si possono affiancare due query simili a quella scritta su, ma è possibile innestarle.
Il trucco semplicemente consiste nel "simulare" tutti i conti che vogliamo in ogni query di secondo livello e, alla fine, ripeterli come somma nella query di primo livello.

Capito? Immagino di no, per cui facciamo l'esempio citato sopra.

Partiamo con le query interne (secondo livello), per le quali avremo questa struttura:

SELECT COUNT(*) AS TotRoma, 0 AS TotMilano FROM PERSONA WHERE CITTA='Roma'
UNION
SELECT 0 AS TotRoma, COUNT(*) AS TotMilano FROM PERSONA WHERE CITTA='Milano'

Supponendo che il numero di abitanti di Roma sia 2000 e quello di Milano sia 1000, il risultato di questa unione sarà il seguente:

TotRomaTotMilano
20000
01000

Aggiungiamo la query di primo livello:

SELECT SUM(TotRoma) as TotRoma, SUM(TotMilano) as TotMilano FROM (
    SELECT COUNT(*) AS TotRoma, 0 AS TotMilano FROM PERSONA WHERE CITTA='Roma'
    UNION
    SELECT 0 AS TotRoma, COUNT(*) AS TotMilano FROM PERSONA WHERE CITTA='Milano'
)

e il risultato dell'innesto sarà quello finale:

TotRomaTotMilano
20001000

Volendo generalizzare (non è complicato, ma può sembrarlo a chi non è abituato ad una certa formalità) ed applicare la tecnica ad un numero n di operazioni, possiamo pensare all'unione delle query interne come una matrice quadrata diagonale del seguente tipo:

TotCittà1 TotCittà2 ... TotCittàn
100...0
020...0
00...0
00...15

La cui query risulterà come la seguente:

select sum(AA) as TotCittà1, sum(BB) as TotCittà1,...,sum(NN) as TotCittàn from (
    select count(*) as AA, 0 as BB, ..., 0 as NN from tabella t1 where (cond1)
    union
    select 0 as AA, count(*) as BB, ..., 0 as NN from tabella t2 where (cond2)
    union
    ...
    union
    select 0 as AA, 0 as BB, ..., count(*) as NN
from tabella t2 where (cond2)
)


il cui risultato finale sarà, ovviamente, questo:

TotCittà1 TotCittà2 ... TotCittàn
1020...15