come sommare dei record raggruppandoli per anno

borgo italia · 11 Nov 2015

ciao
prova a fare una paginazione che ti mostri es. 20 record alla volta

marino51 · 11 Nov 2015

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

clodiny · 11 Nov 2015

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

ciao marino,

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

marino51 · 11 Nov 2015

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"

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

clodiny · 12 Nov 2015

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"

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 · 12 Nov 2015

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

marino51 · 12 Nov 2015

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 · 12 Nov 2015

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

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 · 12 Nov 2015

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 · 12 Nov 2015

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 · 12 Nov 2015

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

clodiny · 12 Nov 2015

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

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 · 13 Nov 2015

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 · 13 Nov 2015

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

" ecceZZiunale "

allego solo immagine test

grande Marino...
ciao Claudio

come sommare dei record raggruppandoli per anno

borgo italia

Super Moderatore

marino51

Utente Attivo

clodiny

Nuovo Utente

marino51

Utente Attivo

clodiny

Nuovo Utente

clodiny

Nuovo Utente

marino51

Utente Attivo

clodiny

Nuovo Utente

marino51

Utente Attivo

clodiny

Nuovo Utente

marino51

Utente Attivo

clodiny

Nuovo Utente

marino51

Utente Attivo

clodiny

Nuovo Utente

Discussioni simili

come sommare dei record raggruppandoli per anno

Super Moderatore

Utente Attivo

Nuovo Utente

Utente Attivo

Nuovo Utente

Nuovo Utente

Utente Attivo

Nuovo Utente

Utente Attivo

Nuovo Utente

Utente Attivo

Nuovo Utente

Utente Attivo

Nuovo Utente

Discussioni simili

Privacy & Transparency

Privacy & Transparency