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.
|
|
CREATE TABLE `prodotti` ( `id` int(255) NOT NULL, `nome` varchar(1024) NOT NULL, `prezzo_unitario` double NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Inseriamo i dati nel modo seguente:
|
|
INSERT INTO `prodotti` (`id`, `nome`, `prezzo_unitario`) VALUES (1, 'Mele', 600), (2, 'Pere', 900), (3, 'Banane', 750), (4, 'Pesche', 1100), (5, 'Kiwi', 1200); |
AGENTI
Andiamo adesso a creare la tabella degli agenti, in modo analogo:
|
|
CREATE TABLE `agenti` ( `id` int(255) NOT NULL, `nome` varchar(100) NOT NULL, `cognome` varchar(100) NOT NULL, `stato` int(10) NOT NULL DEFAULT '1' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Ed inseriamo i dati:
|
|
INSERT INTO `agenti` (`id`, `nome`, `cognome`, `stato`) VALUES (1, 'Mario', 'Rossi', 1), (2, 'Luigi', 'Verdi', 1), (3, 'Luisa', 'Neri', 1), (4, 'Chiara', 'Celesti', 1), (5, 'Paolo', 'Bianchi', 1), (6, 'Anna', 'Rossi', 1); |
VENDITE
Per le vendite andremo a creare la tabella in questo modo:
|
|
CREATE TABLE `vendite` ( `id` int(255) NOT NULL, `idAgente` int(255) NOT NULL, `idProdotto` int(255) NOT NULL, `data` int(11) NOT NULL, `qta` int(255) NOT NULL DEFAULT '0', `importo` double NOT NULL, `stato` int(10) NOT NULL DEFAULT '1' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
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:
|
|
INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`) VALUES (ROUND(1+RAND()*4,0),ROUND(1+RAND()*4,0),UNIX_TIMESTAMP()-ROUND(RAND()*365*3600*24,0),ROUND(RAND()*10000,2),1); |
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:
RAND() genera un valore casuale decimale da 0 a 1- Moltiplicando per
(8-3) il valore casuale sarà tra 0 e 5.
- Sommano il risultato a 3 il valore casuale sarà un decimale tra 3 e 8. Potrebbe essere per esempio 6,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.
|
|
DELIMITER // CREATE PROCEDURE inserimenti_casuali() BEGIN DECLARE tot INT UNSIGNED DEFAULT 0; WHILE tot <= 1000 DO INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`) VALUES (ROUND(1+RAND()*4,0),ROUND(1+RAND()*4,0),UNIX_TIMESTAMP()-ROUND(RAND()*365*3600*24,0),ROUND(RAND()*10000,2),1); SET tot = tot + 1; END WHILE; END// DELIMITER ; |
Chiamiamo la procedura digitando:
|
|
CALL inserimenti_casuali(); |
2. Creazione tabella pivot
Per creare una tabella pivot manualmente utilizziamo CASE WHEN ... END. A mano potremmo scrivere una query come quella di seguito:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale, ROUND(SUM( CASE WHEN v.idProdotto = 1 THEN v.importo ELSE 0 END ),2) AS mele, ROUND(SUM( CASE WHEN v.idProdotto = 2 THEN v.importo ELSE 0 END ),2) AS pere FROM agenti a LEFT JOIN vendite v ON v.idAgente = a.id GROUP BY a.id |
Ogni colonna della pivot sarà generata da:
|
|
ROUND(SUM( CASE WHEN v.idProdotto = 1 THEN v.importo ELSE 0 END ),2) AS mele, |
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:
|
|
SELECT GROUP_CONCAT(DISTINCT CONCAT( 'ROUND(SUM( CASE WHEN v.idProdotto = ', p.id,' THEN v.importo ELSE 0 END ),2) AS `', p.nome,'` ' ) ) FROM prodotti p; |
Il risultato di questa query sarebbe un singolo campo contenente i seguenti valori:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
ROUND(SUM( CASE WHEN v.idProdotto = 1 THEN v.importo ELSE 0 END ),2) AS `Mele` ,ROUND(SUM( CASE WHEN v.idProdotto = 2 THEN v.importo ELSE 0 END ),2) AS `Pere` ,ROUND(SUM( CASE WHEN v.idProdotto = 3 THEN v.importo ELSE 0 END ),2) AS `Banane` ,ROUND(SUM( CASE WHEN v.idProdotto = 4 THEN v.importo ELSE 0 END ),2) AS `Pesche` ,ROUND(SUM( CASE WHEN v.idProdotto = 5 THEN v.importo ELSE 0 END ),2) AS `Kiwi` |
Faccio notare che grazie a GROUP_CONCAT() vengono inserite le virgole tra le singole righe.
Andiamo a creare quindi una procedura come la seguente:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
DELIMITER // CREATE PROCEDURE pivot_vendite_casuali() BEGIN SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'ROUND(SUM( CASE WHEN v.idProdotto = ', p.id,' THEN v.importo ELSE 0 END ),2) AS `', p.nome,'` ' ) ) INTO @sql FROM prodotti p; SET @sql = CONCAT(' SELECT a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale, ', @sql, ' FROM agenti a LEFT JOIN vendite v ON v.idAgente = a.id GROUP BY a.id '); PREPARE esecuzione FROM @sql; EXECUTE esecuzione; DEALLOCATE PREPARE esecuzione; END// DELIMITER ; |
Eseguiamo, come prima, la procedura:
|
|
CALL pivot_vendite_casuali(); |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'ROUND(SUM( CASE WHEN v.idProdotto = ', p.id,' THEN v.importo ELSE 0 END ),2) AS `', p.nome,'` ' ) ) INTO @sql FROM prodotti p; SET @sql = CONCAT(' SELECT a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale, ', @sql, ' FROM agenti a LEFT JOIN vendite v ON v.idAgente = a.id GROUP BY a.id '); PREPARE esecuzione FROM @sql; EXECUTE esecuzione; DEALLOCATE PREPARE esecuzione; |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
<?php defined("IN_ESEMPIO") or die("Hacking attempt!"); class MySQLdb { private $mysql = NULL; function __construct($cfg = NULL) { if( $cfg != NULL ) { $this->config($cfg['dbname'],$cfg['dbhost'],$cfg['dbuser'],$cfg['dbport'],$cfg['dbpass']); $this->connect_db(); } } function __destruct() { $this->close_db(); } function config( $dbname, $dbhost, $dbuser, $dbport, $dbpass ) { $this->dbname = $dbname; $this->dbhost = $dbhost; $this->dbuser = $dbuser; $this->dbport = $dbport; $this->dbpass = $dbpass; } function select_db($dbname = NULL) { $this->mysql->select_db($dbname ? $dbname : $this->dbname); } function connect_db() { if( $this->mysql == NULL ) { $this->mysql = new mysqli($this->dbhost, $this->dbuser, $this->dbpass); $this->select_db(); $this->errorHandler(); } } function close_db() { if( $this->mysql != NULL ) { $this->mysql->close(); $this->mysql = NULL; } } function errorHandler() { if( $this->mysql->connect_errno ) { printf( "Connect failed: %s\n", $this->mysql->connect_error ); die(); } } function clean( $val ) { return $this->mysql->real_escape_string($val); } function in( $query ) { $status = $this->mysql->query( $query ); if( $this->mysql->insert_id ) return $this->mysql->insert_id; else return $status; } function out( $query , $autoReduce = true ) { $result = $this->mysql->query( $query ); if( !is_object($result) ) return array(); while ($linea = $result->fetch_assoc()) { $i = $i !== NULL ? $i + 1 : 0; foreach( $linea as $key => $value ) { $dati[$i][$key] = $value; } } $result->close(); if( count($dati) == 1 && $autoReduce ) return $dati[0]; return $dati; } } |
Creiamo adesso un file index.php, nella stessa posizione, contenente il seguente codice:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
<?php define("IN_ESEMPIO",true); include("MySQLdb.php"); $mysql = new MySQLdb(array( "dbname" => "esempio_pivot", "dbuser" => "root", "dbhost" => "127.0.0.1", "dbpass" => "", "dbport" => 3306 )); $prodotti = $mysql->out("SELECT * FROM prodotti"); $dati = $mysql->out("CALL pivot_vendite_casuali()"); ?> <table> <thead> <tr> <th>Nome</th> <th>Cognome</th> <th>Totale</th> <?php foreach( $prodotti as $p ) { ?> <th><?php echo $p['nome'] ?></th> <?php } ?> </tr> </thead> <tbody> <?php foreach( $dati as $d ) { ?> <tr> <td><?php echo $d['nome'] ?></td> <td><?php echo $d['cognome'] ?></td> <td><?php echo $d['totale'] ?></td> <?php foreach( $prodotti as $p ) { ?> <td><?php echo $d[$p['nome']] ?></td> <?php } ?> </tr> <?php } ?> </tbody> </table> |
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:
