Oracle lavora continuamente per aumentare la capacità di calcolo complesso delle query.
in quest’ottica, a partire dalla versione 10g del DB, è stata aggiunta la clausola MODEL al comando di query.
Questa clausola consente in pratica di trattare i dati estratti da una query come un foglio di calcolo. Consente di referenziare le singole celle per alterarne il valore oppure per utilizzarle come base di calcolo per altre celle.
Vediamo qualche esempio.
Per gli esempi che seguono ho utilizzato la tabella BILANCIO_FAMILIARE, fornisco lo script di creazione e popolamento per chi volesse provare sul proprio DB:
create table bilancio_familiare (
data date,
segno char(1) check (segno in ('D','A')),
categoria varchar2(20)
check (categoria in ('CASA','PERSONALI','AUTO','ISTRUZIONE','REGALI','STIPENDI')),
descrizione varchar2(30),
importo number
);
insert into bilancio_familiare values
(DATE '2009-12-01', 'D', 'CASA', 'RATA MUTUO',500);
insert into bilancio_familiare values
(DATE '2009-12-10', 'D', 'PERSONALI', 'VACANZA',1000);
insert into bilancio_familiare values
(DATE '2009-12-10', 'D', 'AUTO', 'PIENO',60);
insert into bilancio_familiare values
(DATE '2009-12-20', 'D', 'AUTO', 'PIENO',60);
insert into bilancio_familiare values
(DATE '2009-12-30', 'D', 'AUTO', 'PIENO',60);
insert into bilancio_familiare values
(DATE '2010-01-10', 'D', 'AUTO', 'PIENO',60);
insert into bilancio_familiare values
(DATE '2010-01-20', 'D', 'AUTO', 'PIENO',60);
insert into bilancio_familiare values
(DATE '2010-01-30', 'D', 'AUTO', 'PIENO',60);
insert into bilancio_familiare values
(DATE '2010-01-01', 'D', 'CASA', 'RATA MUTUO',510);
insert into bilancio_familiare values
(DATE '2009-12-15', 'A', 'STIPENDI', '13esima',2000);
insert into bilancio_familiare values
(DATE '2009-12-27', 'A', 'STIPENDI', 'Dicembre',2200);
insert into bilancio_familiare values
(DATE '2010-01-27', 'A', 'STIPENDI', 'Gennaio',2400);
insert into bilancio_familiare values
(DATE '2009-12-15', 'D', 'REGALI', 'Natale',800);
insert into bilancio_familiare values
(DATE '2009-12-25', 'A', 'REGALI', 'Natale',200);
Ed ecco dunque i dati presenti in tabella.
SQL> select * from bilancio_familiare ; DATA S CATEGORIA DESCRIZIONE IMPORTO --------- - -------------------- ------------------------------ ---------- 01-DEC-09 D CASA RATA MUTUO 500 10-DEC-09 D PERSONALI VACANZA 1000 10-DEC-09 D AUTO PIENO 60 20-DEC-09 D AUTO PIENO 60 30-DEC-09 D AUTO PIENO 60 10-JAN-10 D AUTO PIENO 60 20-JAN-10 D AUTO PIENO 60 30-JAN-10 D AUTO PIENO 60 01-JAN-10 D CASA RATA MUTUO 510 15-DEC-09 A STIPENDI 13esima 2000 27-DEC-09 A STIPENDI Dicembre 2200 27-JAN-10 A STIPENDI Gennaio 2400 15-DEC-09 D REGALI Natale 800 25-DEC-09 A REGALI Natale 200 14 rows selected.
Noi lavoreremo su una sintesi di questi dati ottenuta accorpandoli per ANNO, segno e categoria di spesa:
SQL> select to_char(data,'yyyy') anno, segno, 2 categoria, sum(importo) importo 3 from bilancio_familiare 4 group by to_char(data,'yyyy'), segno, categoria 5 ; ANNO S CATEGORIA IMPORTO ---- - -------------------- ---------- 2009 D AUTO 180 2009 D CASA 500 2009 D PERSONALI 1000 2009 D REGALI 800 2010 D CASA 510 2010 A STIPENDI 2400 2009 A STIPENDI 4200 2010 D AUTO 180 2009 A REGALI 200 Selezionate 9 righe.
Innanzitutto in una query MODEL bisogna individuare tra le colonne disponibili:
Le partizioni sono colonne rispetto ai cui valori l’insieme dei dati da elaborare è completamente partizionato.
I dati appartenenti a partizioni differenti non si vedono tra di loro e quindi non possono essere inclusi gli uni delle formule di calcolo degli altri.
Nei nostri esempi la colonna partizione sarà l’anno. Quindi tutti i dati saranno calcolati su base annuale ed i dati di anni differenti non si mescoleranno mai tra loro.
Ovviamente non è obbligatorio avere delle partizioni.
Le dimensioni sono le chiavi di accesso ai dati. L’insieme delle dimensioni deve costituire una chiave primaria all’interno della partizione.
Nel nostro esempio saranno dimensioni il segno e la categoria.
Vuol dire che in uno specifico anno (partizione) le coppie (segno, categoria) non sono mai duplicate.
Le misure sono i valori su cui vado effettivamente ad eseguire i calcoli, sono in genere valori numerici. Nel nostro esempio la misura è l’importo.
Detto questo, da questo momento in poi utilizzerò il termine cella per indicare uno specifico valore di IMPORTO individuato da ANNO, SEGNO e CATEGORIA.
Potete pensare ad una cartella Excel in cui per ogni anno ho un foglio, ed in ogni foglio uso segno e categoria come coordinate (righe e colonne) con cui individuare le singole celle.
Il contenuto della singola cella è un valore di importo.
Calcolo vettoriale simbolico
Questo nome roboante rappresenta la capacità di popolare celle (o vettori di celle) con formule che si basano su altre celle.
Vediamo un semplice esempio.
Voglio aggiungere nuove celle al mio foglio per calcolare
1) il “TOTALE CASA E AUTO” facendo la somma delle celle che hanno segno D e categoria AUTO e CASA
2) il “TOTALE AVERE” facendo la somma delle celle che hanno segno A e categoria STIPENDI e REGALI
SQL> select anno, segno, categoria, importo 2 from (select to_char(data,'yyyy') anno, segno, 3 categoria, sum(importo) importo 4 from bilancio_familiare 5 group by to_char(data,'yyyy'), segno, categoria) 6 model 7 PARTITION BY (anno) DIMENSION BY (segno, categoria) 8 MEASURES (importo) 9 RULES 10 (importo['D', 'TOTALE CASA e AUTO'] = 11 importo['D', 'CASA'] + importo['D', 'AUTO'], 12 importo['A', 'TOTALE AVERE'] = 13 importo['A', 'STIPENDI'] + importo['A', 'REGALI'] 14 ) 15 ORDER BY anno, segno,categoria; ANNO S CATEGORIA IMPORTO ---- - -------------------- ---------- 2009 A REGALI 200 2009 A STIPENDI 4200 2009 A TOTALE AVERE 4400 2009 D AUTO 180 2009 D CASA 500 2009 D PERSONALI 1000 2009 D REGALI 800 2009 D TOTALE CASA e AUTO 680 2010 A STIPENDI 2400 2010 A TOTALE AVERE 2010 D AUTO 180 2010 D CASA 510 2010 D TOTALE CASA e AUTO 690 13 rows selected.
Come si vede nella clausola MODEL ho prima di tutto definito PARTIZIONI, DIMENSIONI e MISURE.
Poi sono passato alle regole, indicando che l’importo in Dare con categoria TOTALE CASA E AUTO è dato dalla somma degli importi Dare aventi categorie CASA e AUTO.
Con la stessa logica ho calcolato il TOTALE AVERE.
Notiamo però che il TOTALE AVERE del 2010 ha importo nullo, perché?
Perché per il 2010 manca la cella (A, REGALI). Come al solito sommando quindi questo valore null al valore della cella (A, STIPENDI) si ottiene null.
Per ovviare al problema si può banalmente utilizzare la funzione NVL:
SQL> select anno, segno, categoria, importo 2 from (select to_char(data,'yyyy') anno, segno, 3 categoria, sum(importo) importo 4 from bilancio_familiare 5 group by to_char(data,'yyyy'), segno, categoria) 6 model 7 PARTITION BY (anno) DIMENSION BY (segno, categoria) 8 MEASURES (importo) 9 RULES 10 (importo['D', 'TOTALE CASA e AUTO'] = 11 importo['D', 'CASA'] + importo['D', 'AUTO'], 12 importo['A', 'TOTALE AVERE'] = 13 nvl(importo['A', 'STIPENDI'],0) + nvl(importo['A', 'REGALI'],0) 14 ) 15 ORDER BY anno, segno,categoria; ANNO S CATEGORIA IMPORTO ---- - -------------------- ---------- 2009 A REGALI 200 2009 A STIPENDI 4200 2009 A TOTALE AVERE 4400 2009 D AUTO 180 2009 D CASA 500 2009 D PERSONALI 1000 2009 D REGALI 800 2009 D TOTALE CASA e AUTO 680 2010 A STIPENDI 2400 2010 A TOTALE AVERE 2400 2010 D AUTO 180 2010 D CASA 510 2010 D TOTALE CASA e AUTO 690 13 rows selected.
Adesso è tutto ok.
Aggiungiamo anche il totale dare utilizzando un operatore più generico:
SQL> select anno, segno, categoria, importo 2 from (select to_char(data,'yyyy') anno, segno, 3 categoria, sum(importo) importo 4 from bilancio_familiare 5 group by to_char(data,'yyyy'), segno, categoria) 6 model 7 PARTITION BY (anno) DIMENSION BY (segno, categoria) 8 MEASURES (importo) 9 RULES 10 (importo['D', 'TOTALE CASA e AUTO'] = 11 importo['D', 'CASA'] + importo['D', 'AUTO'], 12 importo['A', 'TOTALE AVERE'] = 13 nvl(importo['A', 'STIPENDI'],0) + nvl(importo['A', 'REGALI'],0), 14 importo['D', 'TOTALE DARE'] = 15 sum(importo)['D',categoria like '%'] 16 ) 17 ORDER BY anno, segno,categoria; ANNO S CATEGORIA IMPORTO ---- - -------------------- ---------- 2009 A REGALI 200 2009 A STIPENDI 4200 2009 A TOTALE AVERE 4400 2009 D AUTO 180 2009 D CASA 500 2009 D PERSONALI 1000 2009 D REGALI 800 2009 D TOTALE CASA e AUTO 680 2009 D TOTALE DARE 3160 2010 A STIPENDI 2400 2010 A TOTALE AVERE 2400 2010 D AUTO 180 2010 D CASA 510 2010 D TOTALE CASA e AUTO 690 2010 D TOTALE DARE 1380 15 rows selected.
Abbiamo detto che la cella (D,TOTALE DARE) deve essere calcolata sommando gli importi di tutte le celle che hanno segno D e categoria like ‘%’. Cioè tutte le categorie.
Vedremo più avanti che questa cosa si può fare in maniera più elegante.
UPSERT o UPDATE?
Adesso però ci concentriamo sulla modalità di calcolo delle celle. ce ne sono tre distinte:
Aggiorna le celle già presenti ma non aggiunge mai nuove celle
Aggiorna le celle già presenti ed aggiunge nuove celle su un numero limitato di regole
Aggiorna le celle già presenti ed aggiunge nuove celle su un numero più ampio di regole
Si può specificare una modalità di calcolo per ogni regola ed il default è UPSERT.
Vediamo un esempio.
Tutte le celle che abbiamo calcolato finora sono celle aggiunte, essendo totali.
poiché non abbiamo mai specificato la modalità di calcolo essa vale UPSERT e le celle sono sempre state create.
Utilizziamo invece la modalità UPDATE per il calcolo del TOTALE DARE.
SQL> select anno, segno, categoria, importo 2 from (select to_char(data,'yyyy') anno, segno, 3 categoria, sum(importo) importo 4 from bilancio_familiare 5 group by to_char(data,'yyyy'), segno, categoria) 6 model 7 PARTITION BY (anno) DIMENSION BY (segno, categoria) 8 MEASURES (importo) 9 RULES 10 (importo['D', 'TOTALE CASA e AUTO'] = 11 importo['D', 'CASA'] + importo['D', 'AUTO'], 12 importo['A', 'TOTALE AVERE'] = 13 nvl(importo['A', 'STIPENDI'],0) + nvl(importo['A', 'REGALI'],0), 14 UPDATE importo['D', 'TOTALE DARE'] = 15 sum(importo)['D',categoria like '%'] 16 ) 17 ORDER BY anno, segno,categoria; ANNO S CATEGORIA IMPORTO ---- - -------------------- ---------- 2009 A REGALI 200 2009 A STIPENDI 4200 2009 A TOTALE AVERE 4400 2009 D AUTO 180 2009 D CASA 500 2009 D PERSONALI 1000 2009 D REGALI 800 2009 D TOTALE CASA e AUTO 680 2010 A STIPENDI 2400 2010 A TOTALE AVERE 2400 2010 D AUTO 180 2010 D CASA 510 2010 D TOTALE CASA e AUTO 690 13 rows selected.
Rispetto a prima abbiamo perso due righe. I due toali dare per il 2009 ed il 2010.
Siccome la cella (D, TOTALE DARE) non esiste al momento dell’estrazione, la regola in modalità UPDATE non l’aggiunge.
Se invece proviamo ad inserire in tabella un record fatto così:
insert into bilancio_familiare values (DATE '2009-12-25', 'D', 'TOTALE DARE', null,1000);
La situazione cambia perché adesso la cella (D, TOTALE DARE) per il 2009 è presente e dunque la regola può essere applicata in modalità UPDATE:
SQL> select anno, segno, categoria, importo 2 from (select to_char(data,'yyyy') anno, segno, 3 categoria, sum(importo) importo 4 from bilancio_familiare 5 group by to_char(data,'yyyy'), segno, categoria) 6 model 7 PARTITION BY (anno) DIMENSION BY (segno, categoria) 8 MEASURES (importo) 9 RULES 10 (importo['D', 'TOTALE CASA e AUTO'] = 11 importo['D', 'CASA'] + importo['D', 'AUTO'], 12 importo['A', 'TOTALE AVERE'] = 13 nvl(importo['A', 'STIPENDI'],0) + nvl(importo['A', 'REGALI'],0), 14 UPDATE importo['D', 'TOTALE DARE'] = 15 sum(importo)['D',categoria like '%'] 16 ) 17 ORDER BY anno, segno,categoria; ANNO S CATEGORIA IMPORTO ---- - -------------------- ---------- 2009 A REGALI 200 2009 A STIPENDI 4200 2009 A TOTALE AVERE 4400 2009 D AUTO 180 2009 D CASA 500 2009 D PERSONALI 1000 2009 D REGALI 800 2009 D TOTALE CASA e AUTO 680 2009 D TOTALE DARE 4160 2010 A STIPENDI 2400 2010 A TOTALE AVERE 2400 2010 D AUTO 180 2010 D CASA 510 2010 D TOTALE CASA e AUTO 690 14 rows selected.
Ovviamente il TOTALE DARE per il 2010 continua a non esserci.
UPSERT ALL si comporta come UPSERT (cioè aggiunge le celle) ma su un nomero maggiore di regole.
Ci sono regole infatti su cui UPSERT non aggiunge le celle.
Un esempio è il seguente:
SQL> select anno, segno, categoria, importo 2 from (select to_char(data,'yyyy') anno, segno, 3 categoria, sum(importo) importo 4 from bilancio_familiare 5 group by to_char(data,'yyyy'), segno, categoria) 6 model 7 PARTITION BY (anno) DIMENSION BY (segno, categoria) 8 MEASURES (importo) 9 RULES 10 (importo[segno='A','TOTALE']=sum(importo)['A',categoria like '%']) 11 ORDER BY anno, segno,categoria; ANNO S CATEGORIA IMPORTO ---- - -------------------- ---------- 2009 A REGALI 200 2009 A STIPENDI 4200 2009 D AUTO 180 2009 D CASA 500 2009 D PERSONALI 1000 2009 D REGALI 800 2010 A STIPENDI 2400 2010 D AUTO 180 2010 D CASA 510 Selezionate 9 righe. SQL> select anno, segno, categoria, importo 2 from (select to_char(data,'yyyy') anno, segno, 3 categoria, sum(importo) importo 4 from bilancio_familiare 5 group by to_char(data,'yyyy'), segno, categoria) 6 model 7 PARTITION BY (anno) DIMENSION BY (segno, categoria) 8 MEASURES (importo) 9 RULES 10 ( UPSERT ALL importo[segno='A','TOTALE']= 11 sum(importo)['A',categoria like '%']) 12 ORDER BY anno, segno,categoria; ANNO S CATEGORIA IMPORTO ---- - -------------------- ---------- 2009 A REGALI 200 2009 A STIPENDI 4200 2009 A TOTALE 4400 2009 D AUTO 180 2009 D CASA 500 2009 D PERSONALI 1000 2009 D REGALI 800 2010 A STIPENDI 2400 2010 A TOTALE 2400 2010 D AUTO 180 2010 D CASA 510 Selezionate 11 righe.
UPSERT non aggiunge celle quando le dimensioni sono specificate con una condizione (SEGNO=’A’) anziché con una costante (‘A’).
UPSERT ALL invece aggiunge le celle anche in questo caso.
La wildcard ANY
Quest’ultimo esempio ci ricorda che dovevamo trovare un modo più elegante per indicare “tutti i valori di categoria”.
Lo possiamo fare con la parola chiave ANY:
SQL> select anno, segno, categoria, importo 2 from (select to_char(data,'yyyy') anno, segno, 3 categoria, sum(importo) importo 4 from bilancio_familiare 5 group by to_char(data,'yyyy'), segno, categoria) 6 model 7 PARTITION BY (anno) DIMENSION BY (segno, categoria) 8 MEASURES (importo) 9 RULES 10 (importo['D', 'TOTALE CASA e AUTO'] = 11 importo['D', 'CASA'] + importo['D', 'AUTO'], 12 importo['A', 'TOTALE AVERE'] = 13 nvl(importo['A', 'STIPENDI'],0) + nvl(importo['A', 'REGALI'],0), 14 UPDATE importo['D', 'TOTALE DARE'] = 15 sum(importo)['D',ANY] 16 ) 17 ORDER BY anno, segno,categoria; ANNO S CATEGORIA IMPORTO ---- - -------------------- ---------- 2009 A REGALI 200 2009 A STIPENDI 4200 2009 A TOTALE AVERE 4400 2009 D AUTO 180 2009 D CASA 500 2009 D PERSONALI 1000 2009 D REGALI 800 2009 D TOTALE CASA e AUTO 680 2009 D TOTALE DARE 4160 2010 A STIPENDI 2400 2010 A TOTALE AVERE 2400 2010 D AUTO 180 2010 D CASA 510 2010 D TOTALE CASA e AUTO 690 14 rows selected.
Abbiamo specificato il TOTALE DARE senza utilizzare una condizione complessa…
La funzione CV
L’utilizzo della parola chiave ANY apre però un problema.
Se la utilizzo a destra nella mia regola non ci sono dubbi. Se la utilizzo a sinistra, invece, come faccio a destra della regola a sapere quale valore della dimensione sto lavorando?
Facciamo un esempio:
SQL> select anno, segno, categoria, importo 2 from (select to_char(data,'yyyy') anno, segno, 3 categoria, sum(importo) importo 4 from bilancio_familiare 5 group by to_char(data,'yyyy'), segno, categoria) 6 model 7 PARTITION BY (anno) DIMENSION BY (segno, categoria) 8 MEASURES (importo) 9 RULES 10 (importo['A', 'TOTALE AVERE'] = sum(importo)['A',ANY], 11 importo['D', 'TOTALE DARE'] = sum(importo)['D',ANY]) 12 ORDER BY anno, segno,categoria; ANNO S CATEGORIA IMPORTO ---- - -------------------- ---------- 2009 A REGALI 200 2009 A STIPENDI 4200 2009 A TOTALE AVERE 4400 2009 D AUTO 180 2009 D CASA 500 2009 D PERSONALI 1000 2009 D REGALI 800 2009 D TOTALE DARE 3480 2010 A STIPENDI 2400 2010 A TOTALE AVERE 2400 2010 D AUTO 180 2010 D CASA 510 2010 D TOTALE DARE 690 13 rows selected.
Questa è facile perché calcola semplicemente la somma di tutte le categorie con segno A e con segno D.
Possimo pensare di scrivere una sola regola che calcoli entrambi i totali usando ANY a sinistra invece che a destra?
Sì, possiamo:
SQL> select anno, segno, categoria, importo 2 from (select to_char(data,'yyyy') anno, segno, 3 categoria, sum(importo) importo 4 from bilancio_familiare 5 group by to_char(data,'yyyy'), segno, categoria) 6 model 7 PARTITION BY (anno) DIMENSION BY (segno, categoria) 8 MEASURES (importo) 9 RULES 10 (UPSERT ALL importo[ANY,'TOTALE']= 11 sum(importo)[CV(segno),ANY]) 12 ORDER BY anno, segno,categoria; ANNO S CATEGORIA IMPORTO ---- - -------------------- ---------- 2009 A REGALI 200 2009 A STIPENDI 4200 2009 A TOTALE 4400 2009 D AUTO 180 2009 D CASA 500 2009 D PERSONALI 1000 2009 D REGALI 800 2009 D TOTALE 2480 2010 A STIPENDI 2400 2010 A TOTALE 2400 2010 D AUTO 180 2010 D CASA 510 2010 D TOTALE 690 Selezionate 13 righe.
A sinistra abbiamo detto di voler calcolere tutte le celle (Segno, TOTALE) per tutti (ANY) i valori di segno.
A destra però abbiamo bisogno di indicare “La somma di tutti gli importi che hanno come segno QUELLO CHE STO LAVORANDO A SINISTRA”.
La funzione CV fa proprio questo. Consente di mettere a destra dell’espressione di una regola un riferimento al valore corrente della dimensione che è attualmente in fase di lavorazione.
Le potenzialità di MODEL non sono finite. In particolare voglio segnalare la capacità definire dei cicli iterativi da applicare per il calcolo delle celle.
Ma per adesso penso che quello che abbiamo detto sia sufficiente.
Alla prossima,
Massimo
Oracle Italia by Massimo Ruocchio 
20 Maggio 2013 alle 16:54 |
Grazie per la spiegazione,veramente semplice chiara ed efficace.
Devo però segnalare un refuso : nel paragrafo “Le misure sono i valori su cui vado effettivamente ad eseguire i calcoli, sono in genere valori numerici. Nel nostro esempio la misura è l’ANNO.” intendevi che la misura è la colonna IMPORTO (stai già partizionando per ANNO). Non inficia la comprensione – è tra l’altro immediatamente evidente dalle successive query quale campo stai usando per misurare- ma qualcuno potrebbe capire una cosa per un’altra.
Grazie ancora per l’articolo e complimenti per il sito,davvero prezioso.
Antonio
20 Maggio 2013 alle 20:34 |
Corretto. Grazie mille.
Massimo