iSeries Sql – CTE Common Table Expressions – definizioni e utilizzo
Le Common Table Expressions (CTE) in SQL su IBM iSeries (AS/400), come in altri sistemi di gestione di database, sono una caratteristica potente e flessibile che permette di creare query temporanee e riutilizzabili all’interno di un’istruzione SQL più ampia.
Sono particolarmente utili per strutturare e semplificare query complesse, consentendo una maggiore leggibilità e manutenibilità del codice SQL.
Di seguito gli articoli della serie CTE (incluso questo corrente):
Descrizione Tecnica delle CTE
- Definizione: Una CTE è essenzialmente una query temporanea che esiste solo per la durata di una specifica istruzione SQL. È definita all’inizio di una query e può essere poi utilizzata come una normale tabella all’interno di quella stessa istruzione SQL.
- Sintassi: Una CTE viene definita utilizzando la clausola WITH, seguita dal nome della CTE, un elenco facoltativo di colonne e la query che popola la CTE. La sintassi generale è:
WITH NomeCTE (Colonna1, Colonna2, ...) AS ( SELECT ... FROM ... WHERE ... ) SELECT * FROM NomeCTE;
- Riutilizzo: Una CTE può essere utilizzata più volte all’interno di un’unica istruzione SQL. Questo la rende utile per dividere query complesse in parti più piccole e gestibili, per evitare la duplicazione di codice e per migliorare la leggibilità.
- Query Ricorsive: Le CTE offrono la possibilità di scrivere query ricorsive, che sono particolarmente utili per lavorare con dati gerarchici o per eseguire calcoli iterativi. Una CTE ricorsiva si auto-referenzia per creare una serie di risultati ripetuti fino al raggiungimento di una condizione di terminazione.
- Performance: Mentre le CTE possono migliorare la leggibilità del codice, la loro efficienza in termini di prestazioni dipende dalla specifica implementazione del database e dalla natura della query. In alcuni casi, possono essere più efficienti di subquery equivalenti, ma non sempre.
- Compatibilità: Le CTE sono supportate dalla maggior parte dei moderni sistemi di gestione di database, inclusi IBM DB2 per iSeries, SQL Server, PostgreSQL, MySQL (versioni recenti), e altri.
- Applicazioni: Le CTE sono ampiamente utilizzate per semplificare query complesse, per eseguire query ricorsive, per preparare dati per l’aggregazione, per organizzare query con più passaggi, e per migliorare la struttura generale delle query SQL.
Considerazioni per IBM iSeries
- Su IBM iSeries, la disponibilità e le caratteristiche specifiche delle CTE possono dipendere dalla versione del DB2.
- È importante verificare la documentazione specifica del DB2 su iSeries per assicurarsi che tutte le funzionalità delle CTE siano supportate e per capire le migliori pratiche per il loro utilizzo.
vediamo alcuni esempi di utilizzo:
- CTE Common Table Expression per assegnare numerazione over “partition by”
- CTE Common Table Expression per individuare interruzioni nella sequenza numerica
CTE Common Table Expression per assegnare numerazione over “partition by”
Descrizione Contesto e Obiettivo
La query in questione è progettata per operare su una tabella che registra le transazioni, identificate da un ID di transazione (TRANS_ID) e associate a una specifica data di movimento (DATE_MOV).
Mentre la tabella originale potrebbe contenere altre informazioni pertinenti, per lo scopo di questa query, l’attenzione è focalizzata esclusivamente su TRANS_ID e DATE_MOV.
L’obiettivo principale è generare un elenco in cui ogni transazione è elencata con un numero progressivo unico e la relativa data di movimento.
Questo numero progressivo inizia da 1 per ciascuna data di movimento e aumenta di uno per ogni transazione successiva che si verifica nella stessa data.
In altre parole, la query assegna un numero sequenziale alle transazioni, riavviando il conteggio da 1 per ogni nuova data di movimento, garantendo così che ciascuna transazione riceva un identificatore univoco all’interno del suo specifico giorno.
Query
WITH -- starting data
W1 (TRANS_ID, DATE_MOV) AS (
VALUES ('T0011', '2024-01-10'), ('T0012', '2024-01-10'), ('T0013', '2024-01-10')
, ('T0014', '2024-01-10'), ('T0015', '2024-01-10'), ('T0016', '2024-01-10')
, ('T0017', '2024-01-10'), ('T0018', '2024-01-10'), ('T0019', '2024-01-10')
, ('T0020', '2024-01-10'), ('T0023', '2024-01-10'), ('T0024', '2024-01-10')
, ('T0025', '2024-01-10'), ('T0026', '2024-01-10'), ('T0127', '2024-01-10')
, ('T0128', '2024-01-10'), ('T0129', '2024-01-10'), ('T0149', '2024-01-10')
, ('T0150', '2024-01-11'), ('T0151', '2024-01-11'), ('T0152', '2024-01-11')
, ('T0165', '2024-01-11'), ('T0166', '2024-01-11'), ('T0167', '2024-01-11')
, ('T0168', '2024-01-11'), ('T0201', '2024-01-11'))
-- assigns progressive numbering to the same DATA_MOV, ordering by TRANS_ID
, W2 (TRANS_ID, ROW_NUM, DATE_MOV)
as (select trim(W1.TRANS_ID)
, row_number() over ( partition by DATE_MOV -- 'COMMON_VALUE'
order by W1.TRANS_ID) ROW_NUM
, W1.DATE_MOV
from W1 order by W1.TRANS_ID)
select * from W2;
Risultato
Tabella originale (W1)
Tabella risultato (W2)
TRANS_ID DATE_MOV T0011 2024-01-10 T0012 2024-01-10 T0013 2024-01-10 T0014 2024-01-10 T0015 2024-01-10 T0016 2024-01-10 T0017 2024-01-10 T0018 2024-01-10 T0019 2024-01-10 T0020 2024-01-10 T0023 2024-01-10 T0024 2024-01-10 T0025 2024-01-10 T0026 2024-01-10 T0127 2024-01-10 T0128 2024-01-10 T0129 2024-01-10 T0149 2024-01-10 T0150 2024-01-11 T0151 2024-01-11 T0152 2024-01-11 T0165 2024-01-11 T0166 2024-01-11 T0167 2024-01-11 T0168 2024-01-11 T0201 2024-01-11
TRANS_ID ROW_NUM DATE_MOV T0011 1 2024-01-10 T0012 2 2024-01-10 T0013 3 2024-01-10 T0014 4 2024-01-10 T0015 5 2024-01-10 T0016 6 2024-01-10 T0017 7 2024-01-10 T0018 8 2024-01-10 T0019 9 2024-01-10 T0020 10 2024-01-10 T0023 11 2024-01-10 T0024 12 2024-01-10 T0025 13 2024-01-10 T0026 14 2024-01-10 T0127 15 2024-01-10 T0128 16 2024-01-10 T0129 17 2024-01-10 T0149 18 2024-01-10 T0150 1 2024-01-11 T0151 2 2024-01-11 T0152 3 2024-01-11 T0165 4 2024-01-11 T0166 5 2024-01-11 T0167 6 2024-01-11 T0168 7 2024-01-11 T0201 8 2024-01-11
Descrizione dettagliata della query
La query SQL utilizza Common Table Expressions per creare un set di dati di esempio e poi assegnare un numero progressivo a ciascuna riga all’interno di gruppi basati sulla data del movimento (DATE_MOV), ordinando per l’ID della transazione (TRANS_ID).
La query è divisa in due parti principali:
W1: Definizione dei Dati di Partenza
La prima CTE, W1, definisce un set di dati di partenza con due colonne: TRANS_ID e DATE_MOV. Vengono inseriti manualmente dei valori per simulare i dati reali.
W2: Assegnazione del Numero Progressivo
La seconda CTE, W2, si basa sui dati definiti in W1. Utilizza la funzione ROW_NUMBER() per assegnare un numero progressivo a ciascuna transazione (TRANS_ID) all’interno di ogni data (DATE_MOV). La funzione ROW_NUMBER() è partizionata per DATE_MOV per garantire che la numerazione ricominci da 1 per ciascuna nuova data. Inoltre, l’ordine è stabilito dall’TRANS_ID per determinare l’ordine di assegnazione dei numeri all’interno di ogni gruppo di date.
Analizziamo più in dettaglio questa parte:
ROW_NUMBER() OVER (PARTITION BY DATE_MOV ORDER BY W1.TRANS_ID) AS ROW_NUM,
Questa espressione è un esempio di una funzione di finestra in SQL, nello specifico ROW_NUMBER(), che viene utilizzata per assegnare un numero progressivo unico a ogni riga all’interno di un set di risultati di una query. Andiamo a esaminare ogni componente di questa espressione:
ROW_ ROW_NUMBER()
è una funzione di finestra (window functions) che assegna un numero sequenziale a ogni riga in un set di risultati. Il numero inizia da 1 per la prima riga e aumenta di 1 per ogni riga successiva.
OVER (PARTITION BY DATE_MOV ORDER BY W1.TRANS_ID)
La clausola OVER specifica come la funzione di finestra ROW_NUMBER() deve essere applicata al set di risultati.
- PARTITION BY DATE_MOV: Questo divide il set di risultati in partizioni (o gruppi) basati sul valore della colonna DATE_MOV. La funzione ROW_NUMBER() viene poi applicata all’interno di ciascuna di queste partizioni separatamente. Ciò significa che il conteggio ricomincia da 1 per ogni nuova data (DATE_MOV). In altre parole, ciascuna data avrà il proprio set di numeri progressivi.
- ORDER BY W1.TRANS_ID: All’interno di ogni partizione (creata in base a DATE_MOV), le righe vengono ordinate in base al valore di TRANS_ID. La funzione ROW_NUMBER() assegna numeri progressivi a queste righe in base a quest’ordine. Quindi, per ogni data, la prima transazione nell’ordine avrà il numero 1, la seconda il numero 2, e così via.
AS ROW_NUM
Infine, AS ROW_NUM assegna un nome alla colonna che contiene il numero progressivo generato dalla funzione ROW_NUMBER(). In questo caso, la colonna sarà denominata ROW_NUM.
Tenere presente che l’implementazione e le funzionalità effettive potrebbero variare a seconda della versione del sistema operativo iSeries e del database DB2. È sempre buona norma fare riferimento alla documentazione ufficiale IBM per avere informazioni più precise e dettagliate
CTE Common Table Expression per individuare interruzioni nella sequenza numerica
Descrizione Contesto e Obiettivo
La query in questione è progettata per operare su una tabella che registra le transazioni, identificate da un Codice Movimento (MovCode) e associate a uno specifico identificativo (NumberID).
Mentre la tabella originale potrebbe contenere altre informazioni pertinenti, per lo scopo di questa query, l’attenzione è focalizzata esclusivamente su MovCode e NumberID.
L’obiettivo principale è generare un elenco in cui identificare e contrassegnare eventuali interruzioni nella sequenza numerica dei numeri identificativi (NumberID) per ogni Codice Movimento (MovCode).
Query
WITH xx (MovCode, NumberID)
AS (VALUES ('A1002','101')
,('A1002','102')
,('A1002','104')
,('A1002','105')
,('A1002','108')
,('A1002','109')
,('A1002','110')
,('B2002','201')
,('B2002','202')
,('B2002','203')
,('B2002','204')
,('B2002','210')
,('B2002','211')
,('B2002','214'))
, CTE_CheckRegister (MovCode, NumberID, NextNumberID)
AS (
SELECT MovCode,
NumberID,
Lead(NumberID, 1) OVER (PARTITION BY MovCode ORDER BY NumberID) NextNumberID
FROM xx
)
SELECT MovCode,
CASE
WHEN NextNumberID is NULL THEN NumberID
WHEN (NumberID = NextNumberID - 1) THEN NumberID
ELSE NumberID concat ' ***'
END as NumberID
FROM CTE_CheckRegister
ORDER BY MovCode, NumberID;
Risultato
Tabella originale ( XX )
Tabella risultato (CTE_CheckRegister)
MOVCODE NUMBERID A1002 101 A1002 102 A1002 104 A1002 105 A1002 108 A1002 109 A1002 110 B2002 201 B2002 202 B2002 203 B2002 204 B2002 210 B2002 211 B2002 214
MOVCODE NUMBERID A1002 101 A1002 102 *** A1002 104 A1002 105 *** A1002 108 A1002 109 A1002 110 B2002 201 B2002 202 B2002 203 B2002 204 *** B2002 210 B2002 211 *** B2002 214
Descrizione dettagliata della query
Questa query SQL utilizza le CTE (Common Table Expressions) e la funzione LEAD per identificare e contrassegnare eventuali interruzioni nella sequenza numerica dei numeri identificativi (NumberID) per ogni Codice Movimento (MovCode).
Ecco una spiegazione dettagliata di ciascuna parte della query:
- CTE Iniziale xx: Questa CTE definisce un set di valori di esempio che rappresentano coppie di MovCode e NumberID. È una tabella temporanea utilizzata per dimostrare la logica della query.
- CTE CTE_CheckRegister:
- Utilizza i dati dalla CTE xx.
- Applica la funzione LEAD per ottenere il numero del prossimo NumberID (NextNumberID) per ogni MovCode, ordinando per NumberID.
- La funzione LEAD è una funzione di finestra che fornisce l’accesso a una riga a una certa distanza fisica dalla riga corrente, in questo caso, la riga successiva.
- Selezione Finale e Logica CASE:
- La query finale seleziona MovCode e una versione modificata di NumberID.
- La logica CASE viene utilizzata per determinare cosa mostrare per NumberID:
- Se NextNumberID è NULL, ciò indica che è l’ultimo numero identificativo per quel MovCode, quindi mostra semplicemente NumberID.
- Se NumberID è consecutivo al NextNumberID (NumberID = NextNumberID – 1), mostra NumberID.
- Altrimenti, se c’è un’interruzione nella sequenza (cioè, il numero NumberID non è seguito dal numero consecutivo), aggiunge ‘ ***’ a NumberID per segnalare l’interruzione.
- Ordinamento dei Risultati:
- I risultati sono ordinati per MovCode e NumberID, garantendo che l’output sia presentato in modo ordinato e sequenziale per ogni MovCode.
Tenere presente che l’implementazione e le funzionalità effettive potrebbero variare a seconda della versione del sistema operativo iSeries e del database DB2. È sempre buona norma fare riferimento alla documentazione ufficiale IBM per avere informazioni più precise e dettagliate
