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

Autore	Titolo	Forum	Risposte	Data
	Sommare i prezzi dei prodotti aggiunti al carrello di diverse aziende con Select sum php mysqli	PHP	10	26 Set 2018
P	come sommare dei numeri?	PHP	2	26 Feb 2007
M	Sommare i punteggi	PHP	17	2 Mar 2021
A	sommare valori a video per lo stesso giorno	jQuery	1	14 Feb 2019
M	[PHP] Sommare due campi calcolati	PHP	3	3 Ott 2018
C	[PHP] Sommare o sottrarre a ZERO	PHP	7	16 Giu 2018
M	[PHP] Sommare ore e minuti	PHP	22	9 Apr 2018
	[PHP][RISOLTO] Sommare gli importi estratti da un ciclo while	PHP	3	21 Dic 2017
	[PHP] sommare le ore	PHP	24	19 Lug 2017
L	[PHP] Sommare campi e aggiornare tabella	PHP	14	6 Nov 2016
F	Sommare valori di ogni periodo con SELECT	PHP	7	8 Gen 2016
C	selezionare distinti id e sommare uguali	PHP	1	12 Dic 2015
A	sommare valori in un array multidimensionale	PHP	0	15 Feb 2015
N	Sommare due date	PHP	4	2 Ago 2014
G	sommare valori più colonne	MS Access	1	30 Lug 2014
D	Se non c'è il campo da sommare	Javascript	2	14 Mar 2014
B	Sommare risultati db	PHP	3	11 Mar 2014
F	[RISOLTO] Sommare data	PHP	2	4 Ott 2013
A	Sommare campi stringa MYSQL PHP	PHP	3	11 Set 2012
N	Sommare iterazioni ciclo while	PHP	5	2 Set 2012
Z	Sommare i numeri con FOR	Javascript	1	5 Ago 2012
Z	Sommare prezzi da campi multiple	Javascript	1	5 Lug 2012
M	sommare input mascherati	Javascript	0	4 Mag 2011
G	Sommare i dati in una tabella	PHP	35	21 Gen 2011
Z	Sommare i numeri	PHP	2	30 Mar 2010
L	Datagrid selezionare e sommare un campo	Visual Basic	0	24 Feb 2010
G	contare e sommare dopo JOIN	Database	8	13 Nov 2009
G	Sommare caselle excel contrassegnate di un elenco	Windows e Software	0	2 Lug 2007
M	Come sommare elementi di un vettore in Visual basic	Visual Basic	1	16 Giu 2004
T	come ordinare dei record recuperati da due tabelle	MySQL	0	5 Lug 2023
C	Creare Tabella Dati Meteo prelevandoli da dei Tags in un altro foglio	PHP	27	8 Dic 2022
C	prendere dei valori da Plugin e inserirli nel database joomla	Joomla	0	20 Mag 2022
	Impedire esecuzione diretta dei file	PHP	7	31 Gen 2022
	Blocco preventivo e Registro dei Consensi per Cookie tecnici?	Leggi, Normative e Fisco	2	21 Nov 2021
F	Scrivere dei dati in word con php	PHP	0	3 Nov 2021
M	Problemi con la stampa dei valori in php	PHP	1	21 Mag 2021
L	Ricezione dei dati su file php da modulo html	PHP	6	19 Mag 2021
G	Somma dei Minuti	PHP	3	15 Mar 2021
W	Elenco dei link del file presenti in una cartella	PHP	2	13 Mar 2021
C	Esclusione dei giorni festivi	Javascript	0	27 Nov 2020
W	Evitare ridondanza dei dati	Classic ASP	3	1 Lug 2020
I	Passare dei parametri in javascript	PHP	0	12 Mag 2020
G	Scelta dei Breakpoint	HTML e CSS	1	20 Mar 2020
D	Javascript per il download dei dati	Javascript	0	11 Mar 2020
G	MariaDB non restituisce dei valori	PHP	7	8 Feb 2020
J	estrarre url dei file video da youtube "get_video_info"	PHP	6	5 Feb 2020
	[PHP] Uso dei namespace	PHP	5	27 Nov 2019
B	[PHP] recuperare IP dei server in load balancing [RISOLTO]	PHP	3	28 Giu 2019
W	[WordPress] [WooCommerce] Non trovo più la Tab Visulizzazione dei Prodotti in questo nuovo Plugin	WordPress	2	16 Mar 2019
L	Trasferimento dei file OLM Mac in formato PST sul sistema Mac	Mac e Software	1	4 Mar 2019

Cerca

Cerca

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