come sommare dei record raggruppandoli per anno

marino51

Utente Attivo
28 Feb 2013
2.874
154
63
Lombardia
puoi sostituire la query con questa semplificata
Codice:
select * from (
SELECT gruppo, 9999 as anno, sum(importo) as importo from tabpivot group by gruppo
union
SELECT gruppo, year(data) as anno, sum(importo) as importo from tabpivot group by gruppo, year(data)
union
SELECT distinct t1.gruppo, year(t2.data)as anno, 0 as importo FROM tabpivot t1, tabpivot t2
where not exists (select 1 FROM tabpivot where gruppo=t1.gruppo and year(data)=year(t2.data))
) x  order by x.gruppo, x.anno desc
potrei suggerirti di creare una colonna con il solo anno, per eliminare tutte le funzioni year() soprattutto nelle clausole where
indicizzando gruppo e anno

trattandosi di dati per noi anonimi, escludendo le note, se potessi postare uno zip con i 1200 record potrei utilizzare "tuning advisor" per vedere i suoi suggerimenti, ora non lavora data l'esiguità dei dati disponibili
 
Ultima modifica:

clodiny

Nuovo Utente
4 Nov 2015
27
0
0
puoi sostituire la query con questa semplificata
Codice:
select * from (
SELECT gruppo, 9999 as anno, sum(importo) as importo from tabpivot group by gruppo
union
SELECT gruppo, year(data) as anno, sum(importo) as importo from tabpivot group by gruppo, year(data)
union
SELECT distinct t1.gruppo, year(t2.data)as anno, 0 as importo FROM tabpivot t1, tabpivot t2
where not exists (select 1 FROM tabpivot where gruppo=t1.gruppo and year(data)=year(t2.data))
) x  order by x.gruppo, x.anno desc
potrei suggerirti di creare una colonna con il solo anno, per eliminare tutte le funzioni year() soprattutto nelle clausole where
indicizzando gruppo e anno

trattandosi di dati per noi anonimi, escludendo le note, se potessi postare uno zip con i 1200 record potrei utilizzare "tuning advisor" per vedere i suoi suggerimenti, ora non lavora data l'esiguità dei dati disponibili

ciao marino,

allego file sql della tabella con cui sto conducendo i test.
Vedi l'allegato tabpivot.zip
 

marino51

Utente Attivo
28 Feb 2013
2.874
154
63
Lombardia
ho inserito nel db tutti i dati che mi hai inviato, lasciando quelli che già c'erano

ho fatto le prove con due metodi che chiamo "query" e "array" dei quali ti posto risultato e relativo script

considera che, ho fermato e fatto ripartire il motore del db, prima di eseguire ciascuno script, in modo che nessuno dei due traesse vantaggio dalle esecuzioni precedenti (prove)

considera che uso il portatile dell d630 (con ssd) come "sql server" su cui c'è anche il "web server"

Cattura_query.PNG Cattura_array.PNG

SOLUZIONE QUERY
PHP:
<?php

require_once 'Config_DB.php';

$time_start = microtime_float();

$sql="
select * from (
SELECT gruppo, 9999 as anno, sum(importo) as importo from tabpivot group by gruppo
union
SELECT gruppo, year(data) as anno, sum(importo) as importo from tabpivot group by gruppo, year(data)
union
SELECT distinct t1.gruppo, year(t2.data)as anno, 0 as importo FROM tabpivot t1, tabpivot t2
where not exists (select 1 FROM tabpivot where gruppo=t1.gruppo and year(data)=year(t2.data))
) x  order by x.gruppo, x.anno desc
";

$nrighe = 0;
$tabella = "";
$titolo = "<tr><td>GRUPPO</td>"."<td>TOTALE</td>";

$sth = $db->query($sql); 
$sth->setFetchMode( PDO::FETCH_ASSOC ); 
while( $row = $sth->fetch() )
{
	if ( $row['anno'] == 9999 )
	{
		$nrighe  += 1;

		if ( $nrighe > 1 ) 
		{
			$tabella .= "</tr>";
			$tr = false;
		}
		$tabella .= "<tr><td>".$row['gruppo']."</td>";
		$tr = true;

		$TotAnno = $row['importo'];
	}
	else
	{
		if ( $nrighe == 1 ) $titolo .= "<td>".$row['anno']."</td>";
	}
	$importo  = ( $row['importo'] == 0 ? ' ' : number_format($row['importo'], 2, ",", ".") );
	$tabella .= "<td>".$importo."</td>";
}
$titolo    .= "</tr>";
if ( $tr === true ) $tabella .= "</tr>";

$time_end = microtime_float();

echo "
<style type='text/css'>
*	{ padding:2px 3px 2px 3px; }
td	{ BORDER:#000 1px solid; text-align:center; font-size: 16px; }
</style>
<h1>soluzione query</h1> <br />
<table border=2 cellpadding=4>
".$titolo.$totgruppi.$tabella."
</table>
<p> </p>
";

$time = $time_end - $time_start;
echo "time_start : ".$time_start."<br />time_end : ".$time_end."<br />Did it, in ".$time." seconds<br />";

function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
?>
SOLUZIONE ARRAY
PHP:
<?php

require_once 'Config_DB.php';

$time_start = microtime_float();

// preparo gli anni
$ANNI['TOTALE'] = 0;
$sql ="select distinct year(data) as anno from tabpivot order by year(data) desc";
$sth = $db->query($sql); $sth->setFetchMode( PDO::FETCH_ASSOC ); 
while( $row = $sth->fetch() ) $ANNI[$row['anno']] = 0;
// var_dump($ANNI); echo "<br /> <br />";

// preparo i gruppi
$IMPORTI = array();
$sql ="select distinct gruppo from tabpivot order by gruppo";
$sth = $db->query($sql); $sth->setFetchMode( PDO::FETCH_ASSOC ); 
while( $row = $sth->fetch() ) $IMPORTI[$row['gruppo']] = $ANNI;
// var_dump($IMPORTI); echo "<br /> <br />";

// leggo gli importi e aggiorno tabella
$sql ="select gruppo, year(data) as anno, importo from tabpivot";
$sth = $db->query($sql); $sth->setFetchMode( PDO::FETCH_ASSOC ); 
while( $row = $sth->fetch() ) $IMPORTI[$row['gruppo']][$row['anno']] += $row['importo'];
// var_dump($IMPORTI); echo "<br /> <br />";

// calcolo i totali
foreach( $IMPORTI as $key => $VALORI ) $IMPORTI[$key]['TOTALE'] = array_sum($VALORI);

// preparo il titolo
$titolo = "<tr>"."<td>GRUPPO</td>";
foreach( $ANNI as $key => $value ) $titolo .= "<td>".$key."</td>";
$titolo .= "</tr>";

// preparo gli importi
$tabella = "";
foreach( $IMPORTI as $key => $VALORI ) {
  $tabella .= "<tr>"."<td>".$key."</td>";
  foreach( $VALORI as $key => $value ) {
    $importo  = ( $value == 0 ? ' ' : number_format($value, 2, ",", ".") );
    $tabella .= "<td>".$importo."</td>";
  }
  $tabella .= "</tr>";
}

$time_end = microtime_float();

echo "
<style type='text/css'>
*	{ padding:2px 3px 2px 3px; }
td	{ BORDER:#000 1px solid; text-align:center; font-size: 16px; }
</style>
<h1>soluzione con array</h1> <br />
<table border=2 cellpadding=4>
".$titolo.$tabella."
</table>
<p> </p>
";

$time = $time_end - $time_start;
echo "time_start : ".$time_start."<br />time_end : ".$time_end."<br />Did it, in ".$time." seconds<br />";

function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
?>
la soluzione "query", ha impiegato quasi il doppio della soluzione "array" ma stiamo parlando di 1 secondo

la soluzione array, forse, è preferibile perché lo script è più semplice e lineare ma .... (a me piace la "query")

ciao
Marino
 
Ultima modifica:

clodiny

Nuovo Utente
4 Nov 2015
27
0
0
ho inserito nel db tutti i dati che mi hai inviato, lasciando quelli che già c'erano

ho fatto le prove con due metodi che chiamo "query" e "array" dei quali ti posto risultato e relativo script

considera che, ho fermato e fatto ripartire il motore del db, prima di eseguire ciascuno script, in modo che nessuno dei due traesse vantaggio dalle esecuzioni precedenti (prove)

considera che uso il portatile dell d630 (con ssd) come "sql server" su cui c'è anche il "web server"


ora proverò ha fare dei test,

ma, visto che uso un pc DELL M4800,

dai tuoi tempi di esecuzione, penso che la "lentezza" di esecuzione della query sia un problema di impostazioni.

il sistema che uso è Xampp, e l'unica impostazione che ho variato nel php.ini è:
max_execution_time = 180 ;(30 sec. è il default).

ciao Claudio
 

clodiny

Nuovo Utente
4 Nov 2015
27
0
0
ho fatto i test riavviando il DB server di xampp prima di ogni test

la differenza è a dir poco imbarazzante...

con lo script "Array" 0.00499 sec.
con lo script "Query" 24.39 sec.

lo script eseguito facendo girare una funzione e poi l'altra:
PHP:
<html>
  <head>
    <title>test raggruppa anni</title>

  </head>

  <body class="no-skin">

  <?php

    testQuery();

    //testArray();

  ?>

  </body>
</html>




<?php
//-------------------------------------------------------------------------------------
//             con query
//-------------------------------------------------------------------------------------
function testQuery(){
    // collegamento al database
    $dsn = 'mysql:dbname=test; host=localhost';
    $user = 'root';
    $password = '';

    // blocco try per il lancio dell'istruzione
    try {
        // connessione tramite creazione di un oggetto PDO
        $db = new PDO($dsn, $user, $password);
    }
    // blocco catch per la gestione delle eccezioni
    catch(PDOException $e) {
        // notifica in caso di errorre
        echo 'Connessione fallita: '.$e->getMessage();
    }



$time_start = microtime_float();

$sql="
select * from (
SELECT gruppo, 9999 as anno, sum(importo) as importo from tabpivot group by gruppo
union
SELECT gruppo, year(data) as anno, sum(importo) as importo from tabpivot group by gruppo, year(data)
union
SELECT distinct t1.gruppo, year(t2.data)as anno, 0 as importo FROM tabpivot t1, tabpivot t2
where not exists (select 1 FROM tabpivot where gruppo=t1.gruppo and year(data)=year(t2.data))
) x  order by x.gruppo, x.anno desc
";

$nrighe = 0;
$tabella = "";
$titolo = "<tr><td>GRUPPO</td>"."<td>TOTALE</td>";

$sth = $db->query($sql);
$sth->setFetchMode( PDO::FETCH_ASSOC );
while( $row = $sth->fetch() )
{
    if ( $row['anno'] == 9999 )
    {
        $nrighe  += 1;

        if ( $nrighe > 1 )
        {
            $tabella .= "</tr>";
            $tr = false;
        }
        $tabella .= "<tr><td>".$row['gruppo']."</td>";
        $tr = true;

        $TotAnno = $row['importo'];
    }
    else
    {
        if ( $nrighe == 1 ) $titolo .= "<td>".$row['anno']."</td>";
    }
    $importo  = ( $row['importo'] == 0 ? ' ' : number_format($row['importo'], 2, ",", ".") );
    $tabella .= "<td>".$importo."</td>";
}
$titolo    .= "</tr>";
if ( $tr === true ) $tabella .= "</tr>";

$time_end = microtime_float();

echo "
<style type='text/css'>
*    { padding:2px 3px 2px 3px; }
td    { BORDER:#000 1px solid; text-align:center; font-size: 16px; }
</style>
<h1>soluzione query</h1> <br />
<table border=2 cellpadding=4>
".$titolo./*$totgruppi.*/$tabella."
</table>
<p> </p>
";

$time = $time_end - $time_start;
echo "time_start : ".$time_start."<br />time_end  : ".$time_end."<br />Did it, in ".$time." seconds<br />";
}
//-------------------------------------------------------------------------------------




//-------------------------------------------------------------------------------------
//             con ARRAY
//-------------------------------------------------------------------------------------
function testArray(){
    // collegamento al database
    $dsn = 'mysql:dbname=test; host=localhost';
    $user = 'root';
    $password = '';

    // blocco try per il lancio dell'istruzione
    try {
        // connessione tramite creazione di un oggetto PDO
        $db = new PDO($dsn, $user, $password);
    }
    // blocco catch per la gestione delle eccezioni
    catch(PDOException $e) {
        // notifica in caso di errorre
        echo 'Connessione fallita: '.$e->getMessage();
    }


$time_start = microtime_float();

// preparo gli anni
$ANNI['TOTALE'] = 0;
$sql ="select distinct year(data) as anno from tabpivot order by year(data) desc";
$sth = $db->query($sql); $sth->setFetchMode( PDO::FETCH_ASSOC );
while( $row = $sth->fetch() ) $ANNI[$row['anno']] = 0;
// var_dump($ANNI); echo "<br /> <br />";

// preparo i gruppi
$IMPORTI = array();
$sql ="select distinct gruppo from tabpivot order by gruppo";
$sth = $db->query($sql); $sth->setFetchMode( PDO::FETCH_ASSOC );
while( $row = $sth->fetch() ) $IMPORTI[$row['gruppo']] = $ANNI;
// var_dump($IMPORTI); echo "<br /> <br />";

// leggo gli importi e aggiorno tabella
$sql ="select gruppo, year(data) as anno, importo from tabpivot";
$sth = $db->query($sql); $sth->setFetchMode( PDO::FETCH_ASSOC );
while( $row = $sth->fetch() ) $IMPORTI[$row['gruppo']][$row['anno']] += $row['importo'];
// var_dump($IMPORTI); echo "<br /> <br />";

// calcolo i totali
foreach( $IMPORTI as $key => $VALORI ) $IMPORTI[$key]['TOTALE'] = array_sum($VALORI);

// preparo il titolo
$titolo = "<tr>"."<td>GRUPPO</td>";
foreach( $ANNI as $key => $value ) $titolo .= "<td>".$key."</td>";
$titolo .= "</tr>";

// preparo gli importi
$tabella = "";
foreach( $IMPORTI as $key => $VALORI ) {
  $tabella .= "<tr>"."<td>".$key."</td>";
  foreach( $VALORI as $key => $value ) {
    $importo  = ( $value == 0 ? ' ' : number_format($value, 2, ",", ".") );
    $tabella .= "<td>".$importo."</td>";
  }
  $tabella .= "</tr>";
}

$time_end = microtime_float();

echo "
<style type='text/css'>
*    { padding:2px 3px 2px 3px; }
td    { BORDER:#000 1px solid; text-align:center; font-size: 16px; }
</style>
<h1>soluzione con array</h1> <br />
<table border=2 cellpadding=4>
".$titolo.$tabella."
</table>
<p> </p>
";

$time = $time_end - $time_start;
echo "time_start : ".$time_start."<br />time_end  : ".$time_end."<br />Did it, in ".$time." seconds<br />";

}
//-------------------------------------------------------------------------------------
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
//-------------------------------------------------------------------------------------



?>
allego immagini risultato...
array.png

query.png
 

marino51

Utente Attivo
28 Feb 2013
2.874
154
63
Lombardia
ciao Claudio,

... insomma ... scelta obbligata,

penso che mysql ci metta del suo, la comparazione con ms sql è veramente sorprendente
( o forse il mio ssd è ancora una volta strepitoso )

nel caso dell'array la differenza può forse leggersi nel paio di generazioni che stanno tra le nostre due macchine

ciao
Marino
 

clodiny

Nuovo Utente
4 Nov 2015
27
0
0
ciao Claudio,

... insomma ... scelta obbligata,

penso che mysql ci metta del suo, la comparazione con ms sql è veramente sorprendente
( o forse il mio ssd è ancora una volta strepitoso )

nel caso dell'array la differenza può forse leggersi nel paio di generazioni che stanno tra le nostre due macchine

ciao
Marino
ciao Marino

non volevo fare un confronto fra le macchine, ma solo sul delta dei tempi,

nel tuo caso la differenza è nel termine del secondo;
sulla mia macchina la differenza fra i due metodi è "imbarazzante",

a questo punto il problema è sicuramente nella configurazione del DB server.

le strade sono 2 :
- ho trovo una configurazione più performante;
- oppure uso il metodo dell' Arrray, (pur preferendo la query perchè ha un codice più pulito).


proverò anche se a tentoni di modificare la configurazione del DB Server,
lanciando lo script ad ogni modifica dovrei "forse" riuscire a migliorare...



ciao
Claudio.
 

marino51

Utente Attivo
28 Feb 2013
2.874
154
63
Lombardia
concordo con il tuo pensiero, neppure io volevo fare un confronto tra macchine, ma un fattore superiore a 100, anche se su tempi piccolissimi, rimarrà nella mia mente
ciao
Marino
 

clodiny

Nuovo Utente
4 Nov 2015
27
0
0
Marino,

secondo tuo consiglio,
per trovare aiuto sulla configurazione del DB server,
mi converrebbe aprire una nuova discussione
allegando i risultati dei test?


ciao Claudio
 

marino51

Utente Attivo
28 Feb 2013
2.874
154
63
Lombardia
si, se ci sono persone esperte di Xampp, per loro diventa una "sfida"
io purtroppo non lo conosco per nulla e quindi mi astengo
ciao
Marino

ps, considera sempre la funzione "year" richiamata nelle clausole where
sarebbe interessante creare una colonna e rifare il test con l'anno a se stante
 
Ultima modifica:

clodiny

Nuovo Utente
4 Nov 2015
27
0
0
si, se ci sono persone esperte di Xampp, per loro diventa una "sfida"
io purtroppo non lo conosco per nulla e quindi mi astengo
ciao
Marino

ps, considera sempre la funzione "year" richiamata nelle clausole where
sarebbe interessante creare una colonna e rifare il test con l'anno a se stante
già provato,

ho creato una colonna year, e l'ho popolata
con la query: UPDATE `tabpivot` SET `year`= YEAR(`data`) WHERE 1


poi ho modificato la query così:
PHP:
$sql="
   select * from (
      SELECT gruppo, 9999 as anno, sum(importo) as importo from tabpivot group by gruppo
      union
      SELECT gruppo, year as anno, sum(importo) as importo from tabpivot group by gruppo, year
      union
      SELECT distinct t1.gruppo, t2.year as anno, 0 as importo FROM tabpivot t1, tabpivot t2
      where not exists (select 1 FROM tabpivot where gruppo=t1.gruppo and year= t2.year)
   ) x  order by x.gruppo, x.anno desc";
e ho impostato year come indice,
ma non ho avuto miglioramenti significativi.

ciao claudio
 

marino51

Utente Attivo
28 Feb 2013
2.874
154
63
Lombardia
vuoi essere gentile da provare questa query ?
grazie in anticipo
ciao
Marino
PHP:
select * from 
(
  SELECT gruppo, 9999 as anno, sum(importo) as importo from tabpivot group by gruppo
  union
  select y.gruppo, y.anno, sum(y.importo) from 
  (
    SELECT gruppo, year(data) as anno, importo from tabpivot
    union
    SELECT distinct t1.gruppo, year(t2.data)as anno, 0 as importo FROM tabpivot t1, tabpivot t2
  ) y group by y.gruppo, y.anno
) x  order by x.gruppo, x.anno desc
 

clodiny

Nuovo Utente
4 Nov 2015
27
0
0
vuoi essere gentile da provare questa query ?
Grazie in anticipo
ciao
marino
PHP:
select * from 
(
  select gruppo, 9999 as anno, sum(importo) as importo from tabpivot group by gruppo
  union
  select y.gruppo, y.anno, sum(y.importo) from 
  (
    select gruppo, year(data) as anno, importo from tabpivot
    union
    select distinct t1.gruppo, year(t2.data)as anno, 0 as importo from tabpivot t1, tabpivot t2
  ) y group by y.gruppo, y.anno
) x  order by x.gruppo, x.anno desc
" ecceZZiunale "

allego solo immagine test

grande Marino...
ciao Claudio