Pivot dinamici con colonne sconosciute

Ogni pivot statico, sia che utilizzi un'aggregazione con CASE, PIVOT di SQL Server o crosstab di Postgres, presenta la stessa limitazione: è necessario elencare le colonne di output quando si scrive la query.

Ma cosa succede quando le categorie non sono note, ad esempio nomi di prodotti che cambiano ogni settimana o una colonna per ogni mese attivo? Si tratta di un pivot dinamico, una domanda da colloquio per profili senior perché il SQL semplice non può restituire un risultato la cui lista di colonne viene determinata a runtime.

SQL è tipizzato staticamente a livello di set di risultati: il pianificatore deve conoscere le colonne e i relativi tipi prima dell'esecuzione. Una singola query non può dire crei una colonna per ogni valore trovato.

La tecnica universale consiste quindi nel generare il testo SQL in due passaggi: prima si interrogano le categorie distinte, poi si costruisce una stringa contenente la query di pivot a partire da esse e si esegue quella stringa.

Il primo passaggio consiste in una query normale che elenca i valori distinti che diventeranno colonne. In genere li si ordina per ottenere una disposizione stabile delle colonne.

Questo risultato alimenta il passaggio di costruzione della stringa. In un sistema reale si esegue questa query, si acquisiscono le righe e si assembla la query successiva a partire da esse.

Successivamente, si trasformano questi valori in una lista separata da virgole di espressioni CASE (oppure in nomi tra parentesi quadre per PIVOT). I database offrono funzioni di aggregazione di stringhe per eseguire questa operazione direttamente in SQL.

In Postgres la funzione è string_agg; in MySQL GROUP_CONCAT; in SQL Server STRING_AGG oppure il più vecchio espediente FOR XML PATH.

Si concatena il frammento generato in una stringa contenente la query completa, quindi la si esegue dinamicamente: EXECUTE in PL/pgSQL, sp_executesql in SQL Server oppure PREPARE/EXECUTE in MySQL.

Questo è il cuore di un pivot dinamico: SQL scrive SQL e poi lo esegue.

In Postgres si racchiudono i tre passaggi in un blocco DO o in una funzione. Si costruisce la lista delle colonne con string_agg, la si inserisce nella query e si esegue quest'ultima con EXECUTE.

Poiché le colonne del risultato non sono note fino al runtime, una funzione che restituisce questo risultato utilizza spesso RETURNS SETOF record oppure restituisce le righe come json, che il chiamante espande successivamente.

MySQL non dispone di un operatore pivot, quindi i pivot dinamici costruiscono una stringa di aggregazione condizionale con GROUP_CONCAT, quindi la eseguono tramite un'istruzione preparata.

GROUP_CONCAT ha un limite di lunghezza (group_concat_max_len) che chi conduce il colloquio potrebbe menzionare; se si hanno molte categorie, lo si aumenti.

Poiché si concatenano valori dei dati in SQL eseguibile, i pivot dinamici comportano un rischio di injection. Se un valore di categoria contiene un apice o testo malevolo, può danneggiare o dirottare la query generata.

Utilizzi sempre gli strumenti sicuri del motore per fare l'escaping di identificatori e letterali: format('%I', ...) e %L in Postgres, QUOTENAME in SQL Server. Non inserisca mai valori non elaborati direttamente nella stringa.

Un'altra difficoltà è che il chiamante non può conoscere in anticipo la struttura del risultato. Tra le strategie comunemente accettate nei colloqui ci sono:

• Restituire le righe come JSON e lasciare che sia il livello applicativo a espandere le chiavi.
• Fare in modo che la procedura stampi o costruisca la query, per poi eseguirla in un secondo passaggio.
• Eseguire il pivot finale nel codice dell'applicazione (pandas, strumento di BI) una volta note le categorie.

Non esiste un modo semplice per restituire colonne arbitrarie da una singola chiamata statica.

Supponiamo che i prodotti entrino ed escano dal catalogo e che il report richieda una colonna dei ricavi per ogni prodotto attualmente presente in sales. Non è possibile codificare l'elenco in modo rigido, quindi lo si genera. Postgres rende il procedimento leggibile: si costruisce il frammento CASE con string_agg e un quoting sicuro, lo si inserisce nella query e poi si usa EXECUTE.

Spieghi il procedimento a chi conduce il colloquio: individuare i prodotti, formattare ciascuno in una colonna tra virgolette, assemblare ed eseguire. La stessa struttura si applica a qualsiasi motore; cambiano solo gli strumenti di supporto.

I candidati migliori sanno quando non eseguire questa operazione in SQL. Il SQL dinamico è più difficile da leggere, testare, proteggere e memorizzare nella cache. Spesso la risposta migliore è:

• Restituire il formato lungo da SQL ed eseguire il pivot nell'applicazione o nel livello di reporting.
• Se l'insieme di categorie è ridotto e cambia lentamente, utilizzare un pivot statico e aggiornarlo occasionalmente.

Riservi i pivot dinamici agli insiemi di categorie realmente aperti e in continua evoluzione.

Verifichi il motivo fondamentale per cui esistono i pivot dinamici.

I pivot dinamici gestiscono insiemi di colonne non noti:

• I pivot statici non funzionano perché le colonne del risultato devono essere fissate prima dell'esecuzione.
• Schema: interrogare le categorie distinte, costruire una stringa SQL per il pivot ed eseguirla dinamicamente.
• Utilizzare string_agg/GROUP_CONCAT/STRING_AGG per costruire la lista delle colonne.
• Eseguire l'escaping dei valori (%I/%L, QUOTENAME) per evitare SQL injection.
• Spesso è più pulito restituire il formato lungo ed eseguire il pivot nel livello applicativo.