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

Petar

5 anni fa

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;

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);

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;

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);

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;

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);

1 2	INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`) VALUES (ROUND(1+RAND()4,0),ROUND(1+RAND()4,0),UNIX_TIMESTAMP()-ROUND(RAND()365360024,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 ;

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();

1	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:

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

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,

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;

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:

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`

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:

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 ;

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();

1	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:

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;

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:

<?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;
	
	}


}

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:

<?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>

<?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>Cognome</th>

<th>Totale</th>

<?php foreach( $prodotti as $p ) { ?>

<?php } ?>

</tr>

</thead>

<tbody>

<?php foreach( $dati as $d ) { ?>

<tr>

<?php foreach( $prodotti as $p ) { ?>

<?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:

1. Creazione dati di partenza

PRODOTTI

AGENTI

VENDITE

2. Creazione tabella pivot

3. Utilizzo della tabella nel PHP

Condividi: