Newsgroup: it.lavoro.professioni.webmaster
Titolo: Query raggruppamento

Ho un db che viene aggiornato ogni 10sec. con una nuova riga (lo so
verrà enorme... ma non importa...).
Ogni riga ha un campo con una data (arriva un timestamp da php) e
altre colonne con dei valori (numeri).
Vorrei prendere 4 righe e calcolare, di una colonna di esse:
- il vaore massimo
- il valore minimo
- il valore medio
- ultimo
Per 4 righe intendo "ogni 4 righe"

 - ultimo
specificare dbms non avrebbe fatto male, ma:
select min(colonna) as minimo,max(colonna) as massimo,sum(colonna) as 
somma,avg(colonna) as medio from tabella where data>=$timestamp_start and 
data <$timestamp_start+40;

 data <$timestamp_start+40;
Ah... provo subito... Non avevo pensato al +40
e nella stessa query come specifico "l'ultimo valore" dei 4 numeri? In
pratica la data più recente...

 recente...
dai un po' di fantasia...
max(data) as maxdata 

 di ben pi� grandi.
Dipende, se ogni riga ha 300 colonne e magari VARCHAR o "BLOB"
non � che sia una cosuccia da nulla :) tra l'altro quel "non importa"
potrebbe essere valido ora, voglio vedere se sar� lo stesso quando
gli accessi al DB richiederanno secoli :D





Potrebbe convenirti registrare un valore storico.
Nel senso: registri ogni riga sia nella tabella grossa che in una tabella
piccola. Quando nella tabella piccola hai quattro righe, le estrai, fai i
tuoi conti, e reimmetti la riga risultato in una tabella media, che avra'
un quarto delle righe della tabella grossa.
Quando uno vuole i valori mediati, li prendi dalla tabella media.
O potresti aggiungere tre colonne con massimo, minimo e media mobili, per
fare una "specie" di rrdtool.
Per come sono messi i dati - con un indice sul timestamp, potrebbe essere
conveniente fare una SELECT delle righe con timestamp > (NOW-60s) - avrai
cinque o sei righe - e prendere le ultime quattro; dovrebbe costare meno,
a seconda del database, che fare una select di qualunque altro tipo.
Sempre a seconda di cosa vuoi fare (sospetto sia un datalogger), potrebbe
essere conveniente "ruotare" le tabelle, ovvero salvare i dati usando più
tabelle del tipo data_strumento_YYYYMM, e cosi' ogni tabella conterra' un
solo mese, meno di 300mila righe. Alcuni DB lo fanno di serie, se gli dai
i necessari hint.
Leonardo
-- 
Enough, enough!  But, stranger, ere we part,
Glancing farewell to each nefarious bier,
This warning would I beg you take to heart:
There is an end to e'en the worst career!

 i necessari hint.
Ecco... al riguardo dello scopo preciso non avevo detto nulla. In
pratica devo popolare un programmino in flash che fa dei grafici (si
chiama anychart) con valori di quotazioni.
Ogni 10sec da un sito mi arrivano dei dati (un orario e un valore di
una quotazione di un prodotto). Io devo registrare tutti questi valori
e tirare fuori un csv che mostri, ad esempio, i valori degli ultimi 2
giorni (ma serve anche lo storico di tutto per altre operazioni) e mi
costruisca un grafico a "candela": cioè in un determinato
"momento" (40 sec, ad esempio) devo determinare 4 valori (Open, Low,
High, Close) i quali mi determineranno una "tacca" del grafico. Ogni
"quartetto" sarà quindi una di queste tacche, che messe vicine formano
il grafico... Non so se conosci questo tipo di grafici e forse neanche
t'interessa :)  ma se vuoi ti mando il link ai loro esempi.

t'interessa :)
Ho presente, li adopero occasionalmente anch'io. Magari, io devo
farci sopra calcoli piu' pesanti e sicche' il mio punto di vista
e' un po' catastrofistico, ma m'e' capitato di avere un "tot" di
VIP a guardarsi TUTTI gli stessi grafici nella stessa mezz'ora e
vedere il server ansimare [1].
Leonardo
[1] io pero' dovevo fare i rendering server side e inviare JPEG,
    sicche', di nuovo, il mio e' un punto di vista parziale.


 [cut]
Ok... ho capito cosa vuoi dire. Forse i miei dati pesano molto meno ma
credo che mi ispirerò alla tua analisi. Vado subito a fare qualche
prova.
Grazie mille

 un quarto delle righe della tabella grossa.
Mi potresti dare una mano su questo punto?
Cioè... il concetto è ok: 2 tabelle... una con 2 colonne Time e Valore
che si aggiorna ogni 10sec e una, per esempio, che si aggiorna ogni
minuto formata da 5 colonne (Data_minuto, O,H,L,C).
Esempio Tab_1
Time       |     Valore
10:00:00  |    1,1000
10:00:10  |    1,2000
10:00:20  |    1,3000
10:00:30  |    1,4000
10:00:40  |    1,5000
10:00:50  |    1,6000
10:01:00  |    1,7000
10:01:10  |    1,8000
10:01:20  |    1,9000
Esempio Tab_2 (non scrivo i valori ma son coalcolati in base alla
Tab_1 (il max, il min etc..)
Time_min |  O | H | L | C
10:00       |      |
10:01       |      |
10:02       |      |
Ma non ho capito come si tiene aggiornata la seconda tabella.
E come si fa a capire quando arrivo a 6 righe (1min)?
...Perchè non va inserito l'ultimo valore... ma gli ultimi 6 ...
giusto?

giusto?
Generalmente i valori OHLC sono calcolati in base a una finestra mobile di
X righe. Quindi hai tre tabelle:
storico ( ts, valore )
appoggio ( i, ts, valore )	# "N" righe
olhc	( id, ts1, o, h, l, c )
==
Arriva una coppia ($ts, $valore) dal dispositivo.
INSERT INTO storico ( $ts, $valore );	// Come prima.
INSERT INTO appoggio ( $ts, $valore );
SELECT * FROM appoggio;	// Conviene selezionarli tutti, si fa prima,
			// in un array di N $tuple
Se gli elementi selezionati sono N+1:
	INSERT INTO olhc ($tuple[N][ts], $o, $h, $l, $c);
	DELETE FROM appoggio WHERE i = $tuple[0][i];
Con questo, ohlc ha la stessa periodicita' temporale di storico. Se non
ti serve, non specifichi il WHERE e appoggio viene svuotata del tutto.
O in MySQL, puoi ordinare "TRUNCATE appoggio;".
Poi puoi usare un trucco: con LAST_INSERT_ID recuperi l'ultimo id di
ohlc a costo zero. E:
	// MCD e' il massimo comun divisore dei membri dell'array di $K
	// e lo si puo' precalcolare, e' una costante
	if (0 == ($id % (MCD)))
	for $K in 10, 30, 60,...
	{
		if (0 == ($id % $K))
		{
			INSERT INTO ohlc_$K SELECT MAX(ts1), SUM(o)/$K,
SUM(h)/$K, SUM(l)/$K, SUM(c)/$K FROM ohlc WHERE id > ($id-$K);
		}
	}
...ora hai ohlc con i dati ogni N*10 secondi, e ohlc_10 con i dati ogni
dieci volte tanto (se N=6 ogni 10 minuti), ogni 30, ... e puoi mostrare
il grafico a diversi "zoom" semplicemente cambiando la tabella source.
Costo di spazio: costo di ohlc * (1 + 1/10 + 1/30 + 1/60 + ...) - poco,
tutto sommato.
Costo computazionale:
	+ una INSERT su una tabella di poche righe
	+ una SELECT da una tabella di poche righe
	+ una INSERT in ohlc (ma la dovresti fare comunque [1])
	+ una INSERT extra ogni tot (10m)
	+ una INSERT extra ogni tot piu' lungo (30m)
	+ ...
...si tratta pero' sempre di quisquilie.
Leonardo SE&O
[1] a meno di fare un macello di conti a display time


         DELETE FROM appoggio WHERE i = $tuple[0][i];
E questa sarebbe l'esecuzione di un php nel cron del server ogni tot
secondi? Giusto?

 secondi? Giusto?
Forse ho capito che deve stare nello stesso file... subito dopo la
query che popola la tabella temporanea (di appoggio)
mysql_query("$q_temp");
	// Controllo il num di righe
	$q_cntrl = "SELECT * FROM $tb_temp";
	$res_cntrl = mysql_query($q_cntrl,);
	$righe_cntrl = mysql_num_rows($res_cntrl);
	if($righe_cntrl>=4)
	{
		QUI NON HO CAPITO IL DISCORSO DI PRIMA DEL "i" e dell'array che
volevi creare... in pratica mi manca il tuo suggerimento :
   		>>  INSERT INTO olhc ($tuple[N][ts], $o, $h, $l, $c);
   		e poi...
		$q_svuota_tmp = "TRUNCATE TABLE $tb_temp";
		mysql_query("$q_svuota_tmp") or die(mysql_error());
	}

secondi?
No, lo fai quando ti arriva una coppia ($ts, $valore) dal dispositivo di
acquisizione. Fai la normale INSERT nello storico - e poi fai "anche" un
inserimento in "appoggio". Poi selezioni tutto quello che c'e' dentro ad
"appoggio".
Per sapere se e' il momento di fare i calcoloni in ohlc, o ti basi sulle
righe presenti in "appoggio", oppure, salvi da qualche parte l'ultimo ts
per il quale hai fatto i calcoloni, e se $ts - $saved_ts > $LIMIT (dove,
che so, $LIMIT = 60 secondi), allora prosegui, se no hai finito.
In teoria, ma è più costoso[1], potresti fare in alternativa ad appoggio
	SELECT * FROM storico ORDER BY ts DESC LIMIT $N;
per prelevare solo le ultime N righe dello storico... potrà servirti, in
questo caso, un indice su "ts" su storico.
Sulle N righe che hai preso dalla query, fai i tuoi conti, e inserisci i
risultati in ohlc. Poi aggiorni saved_ts. Potresti fare touch($file) con
un file accessibile, e usare filemtime($file) per rileggere $saved_ts.
Leonardo
[1] in realtà dipende da tanti fattori. Potrebbe anche convenire. Oppure
    (se i dati arrivano con regolarità!) puoi prendere LAST_INSERT_ID, a
    valle dell'inserimento in "storico", e dividere per N. Se il resto è
    diverso da zero esci subito, se no prosegui. Sostanzialmente, stiamo
    attrezzandoci per reinventare rrdtool :-D


 righe presenti in "appoggio",
Questo è quello che sto cercando di fare... e sto procedendo in questo
modo:
Supponiamo che, in tab. Appoggio, il numero "magico" sia 6righe e che
quindi voglio fare il Truncate di Appoggio dopo averlo raggiunto.
Prima del truncate dovrò fare i calcoloni (min, max etc... rispetto a
tutte e 6 le righe).
Non calcolo e non svuoto finchè il conteggio è uguale 6.
Arrivato a 6 la query c'era già perchè ci contavo le righe (SELECT *
FROM appoggio)....
Come calcolo il Min delle 6 righe trovate con quella select?
O mi conviene fare una cosa tipo ...?
if($righe==6)
{
   $min = mysql_query("SELECT MIN(valore) FROM appoggio");
   $max = mysql_query("SELECT MAX(valore) FROM appoggio");
   ... ...
   etc... calcoloni etc...
   mysql_query("TRUNCATE TABLE appoggio");
}
Grazie ancora...

Come calcolo il Min delle 6 righe trovate con quella select?
Dipende. Se ti serve solo minimo e massimo, e non credo,
	SELECT *, MAX(val) AS max_val, MIN(val) AS min_val FROM appoggio
altrimenti carichi tutto in un array e fai i calcoli in PHP. Nel caso di
conti complessi, lo preferisco, perche' posso farmi stampare i risultati
intermedi e ogni sorta di controlli.
	$first	= false;	// Questo sarebbe O
	$t_max	= -$t_min;	// Questo sarebbe H
	$t_min	= 999999999;	// Questo sarebbe L
	$last	= true;		// Questo è per fare chiasso.
	$sq	= 0;
	$s	= 0;
	$n	= 0;
	while($tuple = mysql_fetch_assoc($exec))
	{
		if (false === $first)
			$first = $tuple['valore'];
		$last	= $tuple['valore'];
		if ($tuple['valore'] < $t_min) $t_min = $tuple['valore'];
		if ($tuple['valore'] > $t_max) $t_max = $tuple['valore'];
		$sq	+= $last*$last;
		$s	+= $last;
		$n++;
		...
	}
	mysql_free_result($exec);
	$media	= $s/$n;
	$var	= $sq/$n - $media*$media; // CONTROLLARE LA FORMULA
	$stddev	= sqrt($var);
	...
Leonardo
