[mysql] Creare tabella pivot dinamica in MySQL (per esordienti totali)

Con il MySQL è possibile generare tabelle pivot dinamiche, ovvero che contengano nomi di colonne automatiche, in base ad una tabella di riferimento.

Quello che vogliamo ottenere sarà un risultato come il seguente:

Prima di cominciare creiamo un database con un po’ di dati in modo da poterci lavorare.

1. Creazione dati di partenza

Creiamo 3 tabelle: prodotti, agenti e vendite. Di seguito riporto le query per creare le singole tabelle ed inserire i dati di esempio.

PRODOTTI

Per creare la tabella prodotti eseguiamo la seguente query.

Inseriamo i dati nel modo seguente:

AGENTI

Andiamo adesso a creare la tabella degli agenti, in modo analogo:

Ed inseriamo i dati:

VENDITE

Per le vendite andremo a creare la tabella in questo modo:

A differenza delle precedenti due, qui voglio inserire dei dati casuali. Per inserire una riga di dati casuali, pescati dalle precedenti due tabelle, potremmo scrivere:

Faccio notare che con l’istruzione ROUND(1+RAND()*4,0) inseriamo un valore intero casuale tra 1 e 5. Se volessimo generalizzare il procedimento potremmo scrivere ROUND(n+RAND()*(m-n),0) dove n è il minimo, mentre m è il massimo.

Con n = 3 e m = 8 avremmo infatti le seguenti operazioni:

  1. RAND() genera un valore casuale decimale da 0 a 1
  2. Moltiplicando per (8-3) il valore casuale sarà tra 0 e 5.
  3. Sommano il risultato a 3 il valore casuale sarà un decimale tra 3 e 8. Potrebbe essere per esempio 6,4.
  4. Usando ROUND() arrotondiamo a 0 cifre decimali ottenendo quindi un intero.

Se volessimo inserire più di una riga in una singola operazione, possiamo farlo scrivendo una procedura.

Dichiariamo quindi una procedura nel modo seguente, che inserisca 1000 valore casuali per volta.

Chiamiamo la procedura digitando:

2. Creazione tabella pivot

Per creare una tabella pivot manualmente utilizziamo CASE WHEN ... END. A mano potremmo scrivere una query come quella di seguito:

Ogni colonna della pivot sarà generata da:

In questo caso noi abbiamo una tabella dei prodotti, dove sappiamo esserci all’id = 1 il prodotto chiamato mele.

Con SUM() sommiamo i valori (avremmo potuto usare COUNT(), AVG(), MAX(), MIN() ecc.) e con ROUND() arrotondiamo. Quest’ultima funzione in particolare non sarebbe necessaria, ma la utilizzo solo perché, visto che si parla di importi in euro, non ha senso calcolare un risultato al di sotto dei centesimi.

Quello che vogliamo fare ora è poter scrivere, quel pezzo di query, in modo iterativo, ripetendola per CIASCUN valore della tabella prodotti.

In particolare potremmo eseguire una query come la seguente:

Il risultato di questa query sarebbe un singolo campo contenente i seguenti valori:

Faccio notare che grazie a GROUP_CONCAT() vengono inserite le virgole tra le singole righe.

Andiamo a creare quindi una procedura come la seguente:

Eseguiamo, come prima, la procedura:

Otterremo il risultato cercato.

Dobbiamo usare una procedura per evitare di incorrere in una serie di incongruenze legate all’interrogazione del database.

DIfatti, usando il PHPMyAdmin e lanciando solamente l’istruzione:

Si incorrere nel seguente errore:

Fatal error: Uncaught Error: Call to a member function getClauses() on null in D:\xampp\phpMyAdmin\vendor\phpmyadmin\sql-parser\src\Utils\Query.php:564 Stack trace: #0 D:\xampp\phpMyAdmin\vendor\phpmyadmin\sql-parser\src\Utils\Query.php(681): PhpMyAdmin\SqlParser\Utils\Query::getClause(NULL, NULL, 'ORDER BY', -1, false) #1 D:\xampp\phpMyAdmin\libraries\DisplayResults.php(1385): PhpMyAdmin\SqlParser\Utils\Query::replaceClause(NULL, NULL, 'ORDER BY', '') #2 D:\xampp\phpMyAdmin\libraries\DisplayResults.php(4376): PMA\libraries\DisplayResults->_getUnsortedSqlAndSortByKeyDropDown(Array, '') #3 D:\xampp\phpMyAdmin\libraries\sql.lib.php(1689): PMA\libraries\DisplayResults->getTable(Object(mysqli_result), Array, Array, false) #4 D:\xampp\phpMyAdmin\libraries\sql.lib.php(1980): PMA_getHtmlForSqlQueryResultsTable(Object(PMA\libraries\DisplayResults), './themes/pmahom...', NULL, Array, false, 6, 6, NULL, Object(mysqli_result), Array) #5 D:\xampp\phpMyAdmin\libraries\sql.lib.php(2199): PMA_getQueryResponseForResultsReturned(Object(my in D:\xampp\phpMyAdmin\vendor\phpmyadmin\sql-parser\src\Utils\Query.php on line 564

3. Utilizzo della tabella nel PHP

Leggiamo la tabella tramite il PHP. Per farlo utilizzerò la classe MySQLdb come in questo esercizio sul MySQL Cluster.

Creiamo nella root del nostro sito un file MySQLdb.php contenente il seguente codice:

Creiamo adesso un file index.php, nella stessa posizione, contenente il seguente codice:

Vediamo in particolare come nel vettore $prodotti abbiamo l’elenco di tutti i prodotti e quindi delle colonne della pivot, che si trovano in $dati. Il risultato che otterremo sarà simile a questo:

 

Vedi articolo