Tabelle pivot in PostgreSQL con la funzione Crosstab

Alcuni anni fa, quando è stata rilasciata la versione 8.3 di PostgreSQL, è stata introdotta una nuova estensione chiamata tablefunc. Questa estensione fornisce un insieme di funzioni davvero interessanti. Una di queste è la funzione crosstab, utilizzata per la creazione di tabelle pivot. È questo l'argomento che tratteremo in questo articolo.

Il modo più semplice per spiegare il funzionamento di questa funzione è utilizzare un esempio con una tabella pivot. Prima spiegheremo il nostro punto iniziale da un punto di vista pratico, poi definiremo la tabella pivot desiderata.

Il nostro punto iniziale: Dati grezzi

Mentre leggete questo articolo, immaginate di essere un insegnante di una scuola elementare. Supponiamo che insegniate tutte le materie (lingua, musica, ecc.). La scuola fornisce un sistema per registrare tutti i risultati delle valutazioni o dei test. La seguente istruzione SQL mostrerà i risultati delle valutazioni che avete precedentemente caricato nel sistema:

SELECT *
FROM evaluations

Il nostro obiettivo: una tabella pivot

La seguente griglia potrebbe facilmente tenere traccia dei progressi degli studenti. In informatica, questo tipo di griglia si chiama tabella pivot. Se si analizza il modo in cui viene costruita la tabella pivot, si scopre che vengono utilizzati i valori dei dati grezzi come intestazioni di colonna o nomi di campi (in questo caso, geografia, storia, matematica, ecc.).

Abilitazione della funzione Crosstab

Come abbiamo già detto, la funzione crosstab fa parte di un'estensione di PostgreSQL chiamata tablefunc. Per richiamare la funzione crosstab, è necessario abilitare l'estensione tablefunc eseguendo il seguente comando SQL:

CREATE extension tablefunc;

Come funziona la funzione Crosstab

La funzione crosstab riceve come parametro un comando SQL SELECT, che deve rispettare le seguenti restrizioni:

• La SELECT deve restituire 3 colonne.
• La prima colonna della SELECT sarà l'identificatore di ogni riga della tabella pivot o del risultato finale. Nel nostro esempio, si tratta del nome dello studente. Si noti che i nomi degli studenti (John Smith e Peter Gabriel) appaiono nella prima colonna.
• La seconda colonna della SELECT rappresenta le categorie della tabella pivot. Nel nostro esempio, queste categorie sono le materie scolastiche. È importante notare che i valori di questa colonna si espandono in molte colonne della tabella pivot. Se la seconda colonna restituisce cinque valori diversi (geografia, storia e così via), la tabella pivot avrà cinque colonne.
• La terza colonna della SELECT rappresenta il valore da assegnare a ciascuna cella della tabella pivot. Questi sono i risultati della valutazione nel nostro esempio.

Se pensiamo alla nostra tabella pivot come a un array bidimensionale, la prima colonna SELECT è la prima dimensione dell'array, la seconda colonna SELECT è la seconda dimensione e la terza è il valore dell'elemento dell'array , come griglia [valore_prima_colonna, valore_seconda_colonna] = valore_terza_colonna.

Nel nostro esempio, il parametro SELECT sarà:

SELECT student, subject, evaluation_result FROM evaluations ORDER BY 1,2

La funzione crosstab viene invocata nella clausola FROM dell'istruzione SELECT. È necessario definire i nomi delle colonne e i tipi di dati che verranno inseriti nel risultato finale. Per i nostri scopi, il risultato finale è definito come:

AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC)

Unendo tutti questi pezzi, la nostra query finale sarà:

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations order by 1,2') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

E possiamo vedere il risultato qui:

Un set di dati grezzi, molte tabelle pivot

Da un unico set di dati, possiamo produrre molte tabelle pivot diverse. Continuiamo con l'esempio dell'insegnante e della classe e vediamo alcune delle opzioni disponibili.

Esempio 1: Medie mensili di valutazione

Come insegnanti, potremmo aver bisogno di un rapporto sui risultati della valutazione di uno studente per l'anno in corso. Ad esempio, supponiamo di voler ottenere le valutazioni medie di John Smith da marzo a luglio. In una griglia come la seguente, la tabella avrebbe il seguente aspetto:

L'SQL per questa tabella pivot è:

SELECT * 
FROM crosstab( 'select extract(month from period)::text, subject.name,
		     trunc(avg(evaluation_result),2) 
     from evaluation, subject  
     where evaluation.subject_id = subject.subject_id and student_id = 1 
     group by 1,2 order by 1,2')  AS final_result(Month TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Confrontate questo risultato con la tabella precedente:

Esempio 2: Trovare i record incompleti degli studenti

Potremmo anche intitolare questa sezione "Una limitazione di Crosstab e come risolverla". Prima di entrare nel merito, prepariamo la scena:

Supponiamo di voler vedere se alcuni studenti non hanno un punteggio di valutazione per determinate materie. Si potrebbe provare la nostra query precedente, aggiungendo una clausola WHERE per il mese di luglio. Il codice sarebbe simile a questo:

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations where extract (month from evaluation_day) = 7 order by 1,2') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

La seguente tabella pivot è il risultato di questa query. Possiamo subito notare che non abbiamo voti per lingua, matematica e musica per Peter.

Tuttavia, se proviamo a eseguire una query regolare per ottenere i voti di Peter a luglio ...

SELECT * from evaluations 
where extract ( month from evaluation_day)=7 and student like 'Gabriel, Peter'

... otteniamo risultati diversi. Qui abbiamo i voti per la geografia e la lingua:

Naturalmente, la seconda query è quella corretta perché mostra i dati grezzi. Il problema è nel processo di creazione della tabella pivot: alcune categorie mancano di informazioni. Per risolvere questo problema, possiamo utilizzare la funzione crosstab con un secondo parametro, che rappresenta l'elenco completo delle categorie. Se ci sono valori mancanti, la tabella pivot verrà comunque costruita correttamente. (Ho evidenziato in rosso la query con il secondo parametro).

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations 
                where extract (month from evaluation_day) = 7 order by 1,2',
                'select name from subject order by 1') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Ora abbiamo una tabella pivot corretta con i valori vuoti al posto giusto.

Esercizio

Le tabelle pivot ci offrono un modo diverso di vedere i nostri dati. Inoltre, è possibile creare diverse tabelle pivot basate sugli stessi dati grezzi utilizzando la funzione crosstab. Provate a creare una tabella pivot che mostri la temperatura massima per ogni città e mese sulla base dei dati grezzi della tabella sottostante.

CREATE TABLE weather (city text, when timestamp, temperature float);

La tabella pivot deve avere una riga per ogni città e una colonna per ogni mese. Se volete, potete pensare ad altre tabelle pivot da realizzare con gli stessi dati.