CTE Ricorsive: L’Eleganza della Semplicità in SQL – 2

Le Common Table Expressions (CTE) in SQL su IBM iSeries (AS/400) costituiscono uno strumento efficace e versatile per l’elaborazione di query temporanee e riutilizzabili. Esse contribuiscono a semplificare e aumentare la chiarezza di query SQL complesse, rendendole più gestibili e di più facile comprensione.

Di seguito gli articoli della serie CTE (incluso questo corrente):

• iSeries Sql – CTE Common Table Expressions – definizioni e utilizzo

• CTE Ricorsive: L’Eleganza della Semplicità in SQL – 1

• CTE Ricorsive: L’Eleganza della Semplicità in SQL – 2

• CTE Ricorsive: L’Eleganza della Semplicità in SQL – 3

• Recursive CTE come concatenare i risultati su un’unica riga

In particolare le Common Table Expressions (CTE) Ricorsive sono un potente strumento in SQL che permette di eseguire operazioni che, in assenza di questa funzionalità, richiederebbero lo sviluppo di programmi specifici o l’utilizzo di cicli complessi.

Queste CTE sono particolarmente utili per lavorare con dati gerarchici, come alberi o strutture ad albero, e per eseguire calcoli iterativi o ricorsivi.

In questo articolo vedremo un esempio significativo in dettaglio relativo alla “Generazione di un elenco di date” (entro un range di date).

CTE Ricorsiva per Generare elenco Date

Descrizione Contesto e Obiettivo

Questa query è progettata per la generazione di un elenco di date, entro un range predefinito, associando a ogni data con il nome del giorno della settimana corrispondente e il progressivo nel range. Se il range di date è costituito dal 1 gennaio al 31 dicembre di un anno vengono generate le date dell’intero anno.

Query

WITH 
   DayNames (DayNumber, DayName) AS (
       VALUES (1, 'Domenica'),  (2, 'Lunedì'),  (3, 'Martedì'), 
              (4, 'Mercoledì'), (5, 'Giovedì'), (6, 'Venerdì'), 
              (7, 'Sabato')
       )
 , RangeDate (DataFrom, DataTo) AS (
       VALUES (CAST('2024-01-01' AS DATE), CAST('2024-12-31' AS DATE))
       )
 , DateLog (Data, Giorno, Progr) AS (
       SELECT (SELECT DataFrom From RangeDate) AS Data -- Data di inizio
            , (SELECT DayName FROM DayNames WHERE DayNumber =
                      (DAYOFWEEK((SELECT DataFrom From RangeDate)))) AS Giorno
            , 1 AS Progr
         FROM SYSIBM.SYSDUMMY1
        UNION ALL
       SELECT Data  + 1 DAYS AS Data
            , (SELECT DayName FROM DayNames WHERE DayNumber =
                      (DAYOFWEEK(Data + 1 DAYS))) AS Giorno
            , 1 + Progr AS Progr
         FROM DateLog
        WHERE Data < (SELECT DataTo From RangeDate) -- Data di fine
       )
SELECT * FROM DateLog;

Risultato

Descrizione dettagliata della query

La query è composta da tre CTE principali

CTE DayNames:

• Questa CTE crea una semplice tabella di corrispondenza tra i numeri dei giorni della settimana (1-7) e i loro nomi (da “Domenica” a “Sabato”).

CTE RangeDate:

• Questa CTE definisce l’intervallo di date che si vuole generare, in questo caso, dall’1 gennaio 2024 (DataFrom) al 31 dicembre 2024 (DataTo).

CTE Ricorsiva DateLog:

• Questa CTE è il cuore della query. Utilizza la ricorsione per generare un elenco di date, iniziando dal 1 gennaio 2024 e terminando il 31 dicembre 2024.
   
• Il primo elemento della CTE (membro ancoraggio) stabilisce il punto di partenza: la data iniziale (1 gennaio 2024), il nome del giorno corrispondente ottenuto tramite una subquery con la CTE DayNames, e un contatore progressivo (inizializzato a 1).
   
• Il secondo elemento (membro ricorsivo) aggiunge un giorno alla data corrente ad ogni iterazione, determina il nome del giorno per la nuova data (ancora attraverso una subquery con DayNames), e incrementa il contatore progressivo. Questo processo continua fino a quando non viene raggiunta la data finale, il 31 dicembre 2024.

La Query Finale

• La query finale semplicemente seleziona tutte le righe dalla CTE DateLog. Questo elenco include ogni giorno del 2024, con il nome del giorno della settimana corrispondente e un contatore progressivo che rappresenta il numero di giorni dall’inizio dell’anno.

Punti Chiave e Considerazioni

• Utilizzo di CTE Ricorsive: La CTE DateLog è un esempio classico di utilizzo delle CTE ricorsive per generare un elenco sequenziale di valori – in questo caso, un calendario per un anno.

• Subquery con DayNames: In entrambi i membri della CTE DateLog, vi è una subquery che si collega alla CTE DayNames per ottenere il nome del giorno della settimana. Questo mostra come le CTE possono essere interconnesse per eseguire calcoli complessi in modo efficiente.

• Limitazione della Ricorsione: La clausola WHERE nel membro ricorsivo della CTE DateLog assicura che la ricorsione si fermi alla fine dell’anno 2024, prevenendo un ciclo infinito.

• Performance e Limitazioni: Le CTE ricorsive possono essere impegnative in termini di prestazioni, specialmente per grandi intervalli di date. Inoltre, DB2 ha un limite sul numero di iterazioni ricorsive consentite in una CTE, quindi è importante assicurarsi che la query non superi questo limite. In alcune configurazioni di DB2, questo limite è impostato a 100 per impostazione predefinita, ma può essere aumentato (ad esempio, fino a 1000 o più) se necessario.