02 - Database DB2 for i02a - SQL

Un po’ di CROSS JOIN

Last Updated on 24 Settembre 2019 by Roberto De Pedrini

Un tipo di join poco usata e considerata di rango inferiore rispetto alle altre join è la CROSS JOIN. Questa operazione viene utilizzata per creare tutte le combinazioni possibili di ogni riga tra due(o più) tabelle; è senz’altro un’opzione da usare con estrema attenzione alla cardinalità delle tabelle coinvolte.

Le righe attese da una cross join sono date dal prodotto delle righe delle due (o più) tabelle partecipanti alla join; immaginiamo se per errore incrociamo una tabella di 1.000.000 righe con una tabella di 2.000.000 righe: il risultato è un dataset di 2.000.000.000.000 di righe.

Proprio per questo si cerca di evitare il disegno di select che producono prodotti cartesiani (Cross Join) perché, dipendentemente dal numero di righe coinvolte, possono creare seri problemi al DB.

Un esempio per chiarire come si usa la CJ: un mazzo di carte

La cross join ci aiuta a creare tutte le combinazioni si semi e valori

La sintassi per invocare il cross join può essere indifferente una di queste:

UNPIVOT

A chiunque sarà capitato di vedere una tabella così disegnata, a spregio della 1NF: un array incorporato in una riga di tabella:

Una tabella che quindi, per ogni cliente/anno, elenca un valore per ogni mese.

Il problema è quindi di normalizzare quindi riportare in riga le 12 colonne. Altri DB hanno funzioni di pivot/unpivot ma sul db2 (in attesa della unpivot, e della pivot, magari) si può fare in differenti modi.

Cominciamo col ricostruire la tabella d’esempio e la popoliamo con qualche riga

l’approccio scelto in questo post è la creazione di view ma nulla vieta di usare lo statement SQL sotteso alla view

Unpivot con utilizzo della UNION

Un primo approccio, tradizionale, funzionante, meno performate rispetto le altre, poco elegante per me, è l’utilizzo di union:

produce un dataset corretto:

Unpivot con Cross Join e tabella ausiliaria


L’uso di una tabella ausiliaria ci viene in aiuto per creare l’elenco dei mesi:

la tabella conterrà quindi

Con la Cross Join si riesce ad ottenere quanto desiderato:

vengono infatti create 12 righe (mesi) per ogni riga della tabella master e, con la clausola <CASE mese> valorizziamo la colonna Valore col valore del mese corrispondente.

Unpivot con Cross Join senza tabella ausiliaria


Senza dover creare una tabella ausiliaria il dataset corrispondente alla FAQ400.MESI può essere generato tramite la VAUES (da 6.1)

Unpivot con Cross Join e connect by

Usando la ricorsione è possibile inoltre usare la connect by  per creare un dataset  che elenca i 12 mesi:

Related Posts
DB2 for i SQL – Stringhe – POSSTR-LOCATE-LOCATE_IN_STRING (IT)

Introduzione Spesso, nelle nostre applicazioni, abbiamo la necessità di lavorare con le stringhe di testo e l'SQL del DB2 può Read more

DB2 for i & SQL – FAQ & Howto (Part. 1) (IT)

Database DB2 e SQL ... forse lo strumento più potente e completo che abbiamo sulla piattaforma IBM i: ecco una Read more

Annuncio IBM i 7.4

Arriva direttamente con l'uovo di Pasqua questo annuncio IBM per le novità della versione IBM i 7.4, versione iNext secondo Read more

Generated Always Columns – Approfondimenti (IT)

Introduzione "Generated Always Column": sono colonne, campi, di una tabella il cui contenuto è controllato direttamente dal sistema ... e Read more

About author

IT Senior Consultant at DedaGroup

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *