Výběr hodnot pro vygenerování grafu

[to_je_jedno]

Uz nevim kde nacerpat radu a pritom prilis neztracet cas ctenim veci ktery resi neco jinyho, tak zkusim tady, kde by se mohl obcas vyskytnout nejaky zkuseny programator:

mam tabulku s logem:
log_id (auto increment integer, primary key, nepodstatny)
datum (proste datetime kdy ta hodnota byla namerena)
hodnota (integer)
id_sensor (integer, vazba do tabulky sensoru)

Jsou tam treba miliony zaznamu, kazdy sensor tam periodicky uklada sva data. nektere sensory kazdych 5 minut, nekter sensory kazdych 10minut, nektery sensory treba kazdych 30 minut.

mam vytvorit spojnicove grafy - den, 10-denni, 40-denni
kazdy graf ma 240px sirku, tedy potreboval bych prave 240 hodnot s tim ze kdyz nejak neni tak se vynecha a ten graf tam bude predpokladat linearni vyvoj hodnoty.

den:
idealne 10 hodnot za hodinu. to je cca kazdych 6 minut.

10 dnu:
idealne 1 hodnota z kazde hodiny

40 dnu:
idealne 1 hodnot za 4 hodiny


ted co s tim?
kolega ktery delal desktop aplikaci tak da select where datum od-do a nacte tam treba vsech 50 000 radku pro ten sensor a casove obdobi(treba kvartal) a "posle" je renderovat a netrapi se s tim protoze tam ma jednoho uzivatele a ten si kdyztak chvili pocka a vyplivne mu to primo obrazek.
Jenze ja delam web aplikaci, muzou tam byt tisice uzivatelu tak potrebuju idealne vypisovat jen ty hodnoty ktere se maji zobrazit (navic to resim ted pres google api takze v podstate do javascriptu vypisuju tu tabulku hodnot takze by pak zdrojak webpage byl moc velky pro prohlizec/mobilni pripojeni. casem mozna budu renderovat primo obrazky, ale tam zase potrebuju taky zbytecne nepretezovat server).

Nejjednodussi reseni, ale ne moc programatorsky košer me napadlo napsat si nejakou smycku a v kazde udelat jeden select neco jako
"select prumer hodnot WHERE sensor = X AND cas je mezi 10:00 a 11:00"
"select prumer hodnot WHERE sensor = X AND cas je mezi 11:00 a 12:00"
nebo
"select top 1 hodnota WHERE sensor = X AND cas je mezi 11:00 a 12:00 ORDER BY datum ASC"
cimz bych mel pro kazdy graf 240 relativne jednoduchych selectu.

pomohl by nejaky odkaz na clanek, nejaka konkretni rada, nejaka rada vhodneho klicoveho slova pro google atp. nemam problem studovat, ucit se, ale nejak zatim nemuzu natrefit na spravny zdroj.
"graf/graph" me porad vedou na teorii grafu(hledani cesty apod), ale tady vlastne moc ani nejde o graf jako takovy ale o to jak vytvorit tu minitabulku hodnot pro nej.

Jestli to bude prumer, max hodnota, median nebo tak neni asi uplne podstatne.

Kdyby to melo necemu pomoct tak se jedna o postgres a je mi jedno jestli to bude napsany v DB nebo aplikaci (PHP).

[Reklama]

[mslebodnik]

nechce sa mi teraz priliz nad tym laborovat, takze skusim len nacrtnut cestu , ktorou by sa dalo ist

SELECT senzor,avg(hodnota),EXTRACT( "years month day hours whatever" from datum) FROM tabulka GROUP BY senzor,EXTRACT( "years month day hours whatever" from datum);



pri tomto selecte sa nepouziju indexy nad stlpcom datum (ak existuju), takze select moze trvat pri velkom mnozstve zaznamov trosku dlhsie.
Ak sa to pouzije raz za cas, sa to da zniest , ale ak to bude castejsie odporucam vytvorit materializovany pohlad nad takymto selectom .Neviem o aku databazu ide , v oracle, postresql materialized view existuje , v mysql to neexistovalo no rychly pohlad do googlu "materialized view mysql" tvrdi , ze nieco bud je alebo je nejaky workaroud.

good luck

[mslebodnik]

este je moznost vytvorit index nad funkciou, ale to znova mysql nepodporuje. takze mozno by sa to dalo obist nejakym triggrom pri update.

[Ovrscout]

hmm, Pokud neni problem rychlosti vycteni dat z databaze ale spíše velikost tabulky pro javascript/renderovani, skusil bych tu tabulku po nacteni z databaze zredukovat.

Redukce lze provést zhruba takto.
procházením pole a počítáním průměru/min/max až dokud není překročena doba pro jeden redukovaný bod (např 1hodina nebo  celkový zobrazený čas/počet pixelů)
 - potom se spočtené hodnoty uloží(včetně pozice pixelu na ose X dle časů průměrovaných měření.)
 a pokračuje se počítáním hodnoty pro další pixel, atd.

výhoda je že můžete pro každý bod na ose vykreslit i hodnoty min/max(např pomocí svislé čárky) a neztrácíte informaci výběrem jen některých vzorků.

[yankee]

hmm, Pokud neni problem rychlosti vycteni dat z databaze ale spíše velikost tabulky pro javascript/renderovani, skusil bych tu tabulku po nacteni z databaze zredukovat.

Redukce lze provést zhruba takto.
procházením pole a počítáním průměru/min/max až dokud není překročena doba pro jeden redukovaný bod (např 1hodina nebo  celkový zobrazený čas/počet pixelů)
 - potom se spočtené hodnoty uloží(včetně pozice pixelu na ose X dle časů průměrovaných měření.)
 a pokračuje se počítáním hodnoty pro další pixel, atd.

výhoda je že můžete pro každý bod na ose vykreslit i hodnoty min/max(např pomocí svislé čárky) a neztrácíte informaci výběrem jen některých vzorků.

V sql je i min a max, neni treba parsovat vystup dotazu.

[Reklama]

[Ivan]

Obavam se, ze tvuj dotaz nijak nesouvisi s klasikym SQL. Podivej se treba na Wikipedii a "Exponential moving average". To je to co nejspis potrebujes. Pokud mas radu diskretnich hodnot namerenych v nejakych casech, tak pomoci toho postupu muzes "do-interpolovat" namerene hodnoty s nejakou pevnou periodou. Napr: udelas 5-30 mereni za hodinu a potrebujes zobrazit graf, kde je vynesena hodnota presne kazdych 5 minut.

Je to ten samy algoritmus/vzorec jaky pouziva Unix pro system load.

[to_je_jedno]

Ivan: diky za super keyword. To je prave to, ze jsem na zacatku netusil jestli neco jen vybrat z DB a nejak omezit SQL dotaz nebo jestli tu redukci opravdu resit az aplikacne.

To neni spatny napad treba u toho denniho kde jeden bod obsahuje vic hodnot tak udelat krivky min/max/avg.

Zatim diky za rady vsem, dalsi rady prijmu rad