come sommare dei record raggruppandoli per anno

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:
marino51 ha scritto:
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
Clicca per allargare...


ciao marino,

allego file sql della tabella con cui sto conducendo i test.
Vedi l'allegato tabpivot.zip
 
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:
marino51 ha scritto:
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"
Clicca per allargare...



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
 
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
 
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
 
marino51 ha scritto:
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
Clicca per allargare...

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.
 
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
 
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
 
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:
marino51 ha scritto:
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
Clicca per allargare...

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
 
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
 
marino51 ha scritto:
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
Clicca per allargare...

" ecceZZiunale "

allego solo immagine test
query_2.png
Clicca per allargare...


grande Marino...
ciao Claudio
 
Devi accedere o registrarti per poter rispondere.

Discussioni simili

maxnegri
Sommare i prezzi dei prodotti aggiunti al carrello di diverse aziende con Select sum php mysqli
Risposte
10
Visite
3K
PHP
marino51
marino51
P
come sommare dei numeri?
Risposte
2
Visite
2K
PHP
prinzart
P
M
Sommare i punteggi
Risposte
17
Visite
2K
PHP
Max61
M
A
sommare valori a video per lo stesso giorno
Risposte
1
Visite
1K
jQuery
marino51
marino51
M
[PHP] Sommare due campi calcolati
Risposte
3
Visite
2K
PHP
Max61
M
C
[PHP] Sommare o sottrarre a ZERO
Risposte
7
Visite
2K
PHP
marino51
marino51
M
[PHP] Sommare ore e minuti
Risposte
22
Visite
6K
PHP
Max61
M
elpirata
[PHP][RISOLTO] Sommare gli importi estratti da un ciclo while
Risposte
3
Visite
4K
PHP
elpirata
elpirata
ste80
[PHP] sommare le ore
Risposte
24
Visite
9K
PHP
ste80
ste80
L
[PHP] Sommare campi e aggiornare tabella
Risposte
14
Visite
5K
PHP
borgo italia
borgo italia
F
Sommare valori di ogni periodo con SELECT
Risposte
7
Visite
3K
PHP
Frankie McManzi
F
C
selezionare distinti id e sommare uguali
Risposte
1
Visite
1K
PHP
otto9due
otto9due
A
sommare valori in un array multidimensionale
Risposte
0
Visite
2K
PHP
arcanum
A
N
Sommare due date
Risposte
4
Visite
2K
PHP
Nahuel Berardi
N
G
sommare valori più colonne
Risposte
1
Visite
2K
MS Access
jacobous
jacobous
D
Se non c'è il campo da sommare
Risposte
2
Visite
1K
Javascript
djjunior
D
B
Sommare risultati db
Risposte
3
Visite
1K
PHP
marino51
marino51
F
  • Bloccata
[RISOLTO] Sommare data
Risposte
2
Visite
1K
PHP
Fabrizio Villa
F
A
Sommare campi stringa MYSQL PHP
Risposte
3
Visite
4K
PHP
alo
A
N
Sommare iterazioni ciclo while
Risposte
5
Visite
2K
PHP
navajo75
N
Alto Basso