Trasformazione dei dati: logiche anche complesse
Nello sviluppo di un ETL, non tutte le logiche di trasformazione dei dati possono essere semplici. Esistono casi dove sono necessari – o quantomeno utili – dei calcoli che, ad uno sviluppatore non esperto del linguaggio SQL, potrebbero apparire di difficile implementazione. Si prenda il caso in cui, presa una tabella, sia necessario identificare dei sottoinsiemi di suoi record, e che occorra identificare una sotto-logica che venga applicata “parallelamente” a ciascuno di questi sottoinsiemi. Potrebbe essere necessario identificare un ordinamento interno a questi sottoinsiemi, e magari potrebbe essere necessario che ciascun record ragioni sull’intero suo sottoinsieme, oppure su uno specifico record dello stesso, magari il record precedente, magari il successivo. Come si può fare una cosa del genere?
Le window function di SQL Server
Uno strumento che io trovo essenziale per lo sviluppo della Business Intelligence, quando si tratta delle logiche di trasformazione dei dati in ETL, sono le window function del linguaggio d’interrogazione. Le window function sono funzioni SQL che vengono eseguite per tutti i record di una tabella, ma la cui logica si applica a subset distinti di quella tabella. Le window function sono un ottimo metodo per calcolare, ad esempio, dei subtotali, ma senza dover ricorrere a sotto-query o a common table expression (CTE). Sono anche necessarie per eseguire funzioni aritmetiche progressive (o cumulative, in inglese running) quali somma, media, conteggio, mino e massimo. Oppure possono essere usate per calcolare il ranking delle righe. Infine, sono necessarie per eseguire delle logiche di prelievo del record precedente o successivo al record sulla quale si sta eseguendo il calcolo.
Definire le window function
Le window function si definiscono per mezzo della clausola OVER. La sintassi è la seguente:
f(…) OVER ( PARTITION BY … ORDER BY … )
Se la clausola PARTITION BY viene omessa (o se applicata ad un valore scalare), la funzione f agirà sull’intera tabella come unico insieme logico di dati. Se invece è definita, la funzione f verrà applicata a ciascun sottoinsieme (chiamato partizione) separatamente, cioè restituendo un risultato per ciascuno di essi. La clausola ORDER BY serve invece ad ordinare i record all’interno di ogni partizione. È fondamentale qualora si voglia applicare una funzione in modo progressivo, nelle operazioni di ranking e nelle logiche su record precedente/successivo.
A seconda della funzione richiamata, possiamo distinguere tre differenti tipologie di window function. Le funzioni d’aggregazione sono AVG, COUNT, MAX, MIN e SUM, e credo non debbano essere presentate. Se viene effettuato un ordinamento nella partizione, la loro applicazione è cumulativa nei record. In altre parole, per ciascun record della partizione, la funzione considera tutti i record compresi fra il primo e quello corrente, estremi inclusi.
Esempi:
-- importo totale per cliente SUM([Importo]) OVER (PARTITION BY [Codice Cliente]) -- importo cumulativo totale per cliente, ordinato per data dell’ordine SUM([Importo]) OVER ( PARTITION BY [Codice Cliente] ORDER BY [Data Ordine] ASC )
Le funzioni di ranking che mi è capitato di usare in ETL sono RANK e DENSE_RANK. La prima, RANK, calcola il rango del record per com’è definito matematicamente, ovvero lasciando dei buchi nel ranking qualora ci fosse dei valori “a pari merito”: pensate alle gare d’atletica, dove potrebbe esserci un primo posto e medaglia d’oro seguito da due terzi posti e medaglie di bronzo, lasciando di fatto vuoto l’argento. DENSE_RANK semplicemente tappa i buchi, “addensando” i valori nella partizione.
Esempi:
-- ranking degli ordini per ciascun cliente, ordinati inversamente per data, ora e numero dell’ordine -- al rank = 1 abbiamo l’ultimo ordine di quel cliente, ossia il più recente -- a parità di data e ora (eventualità remota), prendiamo l’ordine di numero maggiore DENSE_RANK() OVER ( PARTITION BY [Codice Cliente] ORDER BY [Data Ordine] DESC ,[Ora Ordine] DESC ,[Numero Ordine] DESC )
Incredibilmente utili sono anche le funzioni a valore. Sono quattro, ma solo due quelle per la quale ho esperienze dirette per l’implementazione di un ETL: LAG che, in una partizione, preleva riga per riga il record precedente; e LEAD, che invece preleva il record successivo. Alle due funzioni deve essere passata un’espressione, che può anche essere semplicemente un valore di colonna. Se le funzioni LAG e LEAD escono dallo scope della partizione (LAG nel primo record della partizione, LEAD nell’ultimo), restituiscono NULL. È anche possibile definire un offset di prelievo del record (impostato a 1 di default) e un valore di default che sostituisca automaticamente in NULL.
-- preleva la data/ora del log precedente dello stesso contratto, e la sposta in avanti di 1 secondo LAG(ADD ( SECOND ,1 ,[Data/Ora Log] )) OVER ( PARTITION BY [Contratto] ORDER BY [Data/Ora Log] DESC ) -- calcola il tempo trascorso, in secondi, fra il log precedente e il log corrente dello stesso contratto -- se non trova un log precedente, considera la data/ora di stipulazione del contratto DATEDIFF(SECOND, [Data/Ora Log], LAG(DATEADD(SECOND, 1, [Data/Ora Log]), 1, [Data Stipula]) OVER ( PARTITION BY [Contratto] ORDER BY [Data/Ora Log] DESC ))
Nel momento in cui è necessario che egli scriva del codice in SQL, consiglio ad ogni sviluppatore di Business Intelligence di vere il completo dominio di queste window function… ma sappiate che non sono le uniche di esistenti in T-SQL. Esistono anche altre funzioni, quali NTILE, FIRST_VALUE e LAST_Value, ma magari le vedremo più avanti in un altro articolo.
di Thomas Tolio, pubblicato il 23 febbraio 2022
