MySQL - pětiminutové průměry hodnot v tabulce

[Krystof1877]

Zdravím všechny,
mám problém se kterým si nějak nevím rady a chtěl bych poprosit někoho znalého o pomoc nebo nakopnutí.
Data z výrobních procesů se mi ukládají do tabulky v MYSQL databázi

Kód: [Vybrat]

id date       station seta pda sla setb pdb slb tempa tempb
48369 23.11.2022 0:01 2 302,5 38,9 3,9 299,3 35 1,1 10 10
48370 23.11.2022 0:00 2 300,5 36,8 2,5 296,3 37,3 2,3 10 10
48371 23.11.2022 0:00 4 298,9 39,5 -0,4 305,3 46,6 5,8 10,1 10,1
48372 23.11.2022 0:01 5 300,4 40,9 1,3 299,5 41,3 2,1 10 10
48373 23.11.2022 0:01 6 303,1 37,8 -0,1 303,8 41,4 1,5 10 10
48374 23.11.2022 0:00 6 300 41,6 2,1 302,6 40,5 2 10 10
48375 23.11.2022 0:00 7 299,4 38,1 1 299 42,4 2,8 10 10
48376 23.11.2022 0:03 2 300,3 33,5 1,5 299,8 35,3 2,4 9,9 9,9
48377 23.11.2022 0:03 4 298,4 36,3 2,1 301,5 46,5 8 9,9 9,9
48378 23.11.2022 0:02 4 295,8 38,8 1,4 300,5 36,4 7,1 9,9 9,9
48379 23.11.2022 0:03 5 298,8 43,6 2,5 301,1 38,9 2,3 10 10
48380 23.11.2022 0:02 5 302,8 36,4 2 296,6 39,1 -0,6 10 10
48381 23.11.2022 0:02 6 301,5 39,8 1,3 303,8 41,4 2,3 10,1 10,1
48382 23.11.2022 0:03 7 303 40,9 1 297,3 44 2 10,1 10,1
48383 23.11.2022 0:02 7 300,1 40 0,9 298,6 37,9 2,1 10 10
48384 23.11.2022 0:05 2 301,6 31,3 1,9 302,9 36,5 1,3 10 10
48385 23.11.2022 0:04 2 295,5 35 1,9 296,6 39,9 1,9 10 10
48386 23.11.2022 0:05 4 302,5 36,3 0,8 303,1 39,8 -0,4 10,1 10,1
48387 23.11.2022 0:04 4 294,3 43,9 5,3 297,4 43 2,5 9,9 9,9
48388 23.11.2022 0:05 5 298,6 40,5 1,8 295,3 38,6 0,1 10 10


Stroj má vždy určitý počet stanic, zde 7  a v různých časech chodí data z jednotlivých stanic. Chtěl bych tato data zobrazovat v jednom grafu a protože je dat poměrně dost, rád bych udělal 5-minutové průměry.
Stačí mi vždy jen jedna hodnota - zde např seta

Kód: [Vybrat]

select convert((min(date) div 500)*500, datetime) as time,avg(seta)
from inspects.tbl_SET3_0275
where station='1' and date BETWEEN '2022-11-23 00:00:00' AND '2022-11-23 10:00:00'
group by date div 500To mi funguje, ale vždy jen pro jednu stanici.

Kód: [Vybrat]

time      avg(seta)
23.11.2022 0:00 300,66667
23.11.2022 0:05 300,05
23.11.2022 0:10 299,76667
23.11.2022 0:15 302,35
23.11.2022 0:20 300,125
23.11.2022 0:25 300,925
23.11.2022 0:30 301,4
23.11.2022 0:35 301,25
23.11.2022 0:45 302,95
23.11.2022 0:50 300,075
23.11.2022 0:55 300,45
23.11.2022 1:00 299,05
23.11.2022 1:05 296,9
23.11.2022 1:10 299,83333
23.11.2022 1:15 299,325
23.11.2022 1:20 298,4
23.11.2022 1:25 303,33333
23.11.2022 1:30 299,2
23.11.2022 1:35 299,825
23.11.2022 1:40 298,225
23.11.2022 1:45 300,2

Jde mi o to, zda jde nějak optimalizovat ten SQL dotaz, abych najednou vytvořil tabulku pro všechny stanice,

Kód: [Vybrat]

     Date            1    2      3       4      5       6      7
23.11.2022 0:10 299,90 299,70 296,85 300,67 298,30 0,00 299,80
23.11.2022 0:15 150,40 302,63 300,90 300,05 299,70 296,85 300,67
23.11.2022 0:20 99,00 301,33 302,50 299,77 302,63 300,90 300,05
23.11.2022 0:25 149,18 298,80 304,10 302,35 301,33 302,50 299,77
23.11.2022 0:30 196,80 303,20 299,20 300,13 298,80 304,10 302,35
23.11.2022 0:35 297,47 300,38 301,08 300,93 303,20 299,20 300,13
23.11.2022 1:00 297,00 296,87 300,43 301,40 300,38 301,08 300,93
23.11.2022 1:05 146,90 302,10 299,57 301,25 296,87 300,43 301,40
23.11.2022 1:10 98,87 301,00 301,50 302,95 302,10 299,57 301,25
23.11.2022 1:30 150,70 302,78 300,47 300,08 301,00 301,50 302,95
23.11.2022 1:35 302,10 301,13 300,78 300,45 0,00 300,47 300,08
23.11.2022 1:40 0,00 298,45 302,80 299,05 301,13 300,78 300,45
23.11.2022 1:45 0,00 298,95 303,05 296,90 298,45 302,80 299,05
23.11.2022 1:50 199,17 299,18 297,23 299,83 298,95 0,00 296,90
23.11.2022 1:55 225,20 298,40 299,55 299,33 299,18 297,23 299,83
23.11.2022 2:00 297,30 300,33 301,10 298,40 298,40 299,55 299,33
23.11.2022 2:05 152,45 299,47 300,28 303,33 300,33 301,10 298,40
23.11.2022 2:10 0,00 296,67 301,27 299,20 299,47 300,28 303,33
23.11.2022 2:15 0,00 303,73 297,70 299,83 296,67 301,27 299,20
23.11.2022 2:20 0,00 301,45 301,93 298,23 303,73 297,70 0,00
23.11.2022 2:30 300,05 299,98 296,93 300,20 301,45 301,93 298,23
23.11.2022 3:10 100,10 298,18 300,13 299,47 299,98 296,93 300,20
23.11.2022 3:15 0,00 300,98 300,35 301,55 298,18 300,13 299,47
23.11.2022 4:05 299,30 301,75 298,73 298,15 300,98 300,35 301,55
23.11.2022 4:10 297,03 298,30 0,00 299,80 301,75 298,73 298,15

V podstatě bych potřeboval sloučit 7 různých selectů do jednoho výstupu, tak aby výsledky seděly s časovou osou , která bude odstupňovaná po 5 minutách. Napadlo mě si udělat nějakou pomocnou tabulku, kde to budu ukládat pomoc nějakého skriptu, ale přijde mi to moc kostrbaté. Jsem v SQL začátečník a to spojování nebo slučování výsledků různých pohledů z jedné tabulky mi moc nejde. Díky moc za jakoukoliv konstruktivní odpověď. Nechci hotové řešení, spíš jen nakopnutí nebo nějaký nástřel. Moc děkuji.

[Reklama]

[none_]

ja bych zkusil upravit GROUP BY:

Kód: [Vybrat]

group by station, date div 500 a somozrejme vyhodit station z WHERE. To by melo dat data, ktery hledas. Ale jak je pak preklopit v SQL uz nevim. tam bych to tahal ven a preklopoval nekde jinde...

[Krystof1877]

ja bych zkusil upravit GROUP BY:

Kód: [Vybrat]

group by station, date div 500 a somozrejme vyhodit station z WHERE. To by melo dat data, ktery hledas. Ale jak je pak preklopit v SQL uz nevim. tam bych to tahal ven a preklopoval nekde jinde...

děkuji za odpověď, asi to nějak spojím v PHP a nasypu do grafu z něj.

[mikesznovu]

zlaté pravidlo SQL: Where určuje, která data vůbec  půjdou do zpracování, a až potom group/having je zpracuje
nebylo by lepší na to nějakáý influx DB? , ta myslím by na to měla být jak poklice na hrnec

[Krystof1877]

zlaté pravidlo SQL: Where určuje, která data vůbec  půjdou do zpracování, a až potom group/having je zpracuje
nebylo by lepší na to nějakáý influx DB? , ta myslím by na to měla být jak poklice na hrnec

Diky za odpoved. To pravidlo chapu, diky. Ale to druhe vubec nevim co je. Nicmene uz to mam hotove diky kombinaci SQL a PHP.

[Reklama]

[Death Walker]

V mysql to musite suplovat cez Prety Html Preprocesor. V postgrese mozete pouzit GROUPING SETS alebo timescale extension ktory vam da hypertabulky.

[Ondrej Nemecek]

není na tohle vhodné použít window functions? https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

[Krystof1877]

není na tohle vhodné použít window functions? https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

díky za tip 

[Ján Mlynár]

Vážení, občas nezaškodí, kouknout se i na nějaký vzdělávací web... tato operace se u většiny relačních databází jmenuje UNION - https://www.w3schools.com/sql/sql_union.asp

[Krystof1877]

Vážení, občas nezaškodí, kouknout se i na nějaký vzdělávací web... tato operace se u většiny relačních databází jmenuje UNION - https://www.w3schools.com/sql/sql_union.asp

Děkuji za odpověď, na všemožné weby, do dokumentace i na stackoverflow koukám dosti často a neodvážil jsem se tento dotaz položit dřív, než pokud jsem byl opravdu v koncích. Všechny možné způsoby spojení tabulek jsem zkoušel, UNION, JOIN a tak podobně. Nicméně jsem se výsledku v čistém SQL nedobral, proto ten dotaz.
Pokud byste věděl, jak pomocí UNIONU  nebo jiného příkazu vytvořit tu tabulku co potřebuji budu moc rád a do budoucna mi to ušetří dost času, díky moc

[LeosB]

Vážení, občas nezaškodí, kouknout se i na nějaký vzdělávací web... tato operace se u většiny relačních databází jmenuje UNION - https://www.w3schools.com/sql/sql_union.asp

To jako že má udělat spoustu dotazů, každý pro jednu stanici, a pak je spojit přes UNION? V takovém případě byste měl sám zapracovat na průzkumu vzdělávacích webů, protože použití window funkcí je mnohem efektivnější!

[rooobertek]

Ak je staníc málo, tak by sa dalo spraviť takéto niečo:

Kód: [Vybrat]

SELECT date, AVG(IF(station=1, seta, NULL)) AS avg1, AVG(IF(station=2, seta, NULL)) AS avg2...
UNION v tomto prípade nemá ako pomôcť.

[Krystof1877]

Ak je staníc málo, tak by sa dalo spraviť takéto niečo:

Kód: [Vybrat]

SELECT date, AVG(IF(station=1, seta, NULL)) AS avg1, AVG(IF(station=2, seta, NULL)) AS avg2...
UNION v tomto prípade nemá ako pomôcť.

Děkuji, tohle je super nápad !!!

[Krystof1877]

Ak je staníc málo, tak by sa dalo spraviť takéto niečo:

Kód: [Vybrat]

SELECT date, AVG(IF(station=1, seta, NULL)) AS avg1, AVG(IF(station=2, seta, NULL)) AS avg2...
UNION v tomto prípade nemá ako pomôcť.

Ještě jednou díky všem.

Kód: [Vybrat]

select q.time, SUM(q.val1), SUM(q.val2),SUM(q.val3), SUM(q.val4),SUM(q.val5), SUM(q.val6),SUM(q.val7) from
(select * from (select unix_timestamp(convert((min(date) div 500)*500, datetime)) as time,
AVG(IF(station='1',seta, 0 )) as val1,
AVG(IF(station='2',seta ,0)) as val2,
AVG(IF(station='3',seta ,0)) as val3,
AVG(IF(station='4',seta ,0)) as val4,
AVG(IF(station='5',seta ,0)) as val5,
AVG(IF(station='6',seta ,0)) as val6,
AVG(IF(station='7',seta ,0)) as val7  from inspects.tbl_SET2_0275 where date > '2022-11-24 00:00:00' group by station, date div 500) as t order by time asc) as q group by time;

Tohle je výsledný kód, který dělá přesně to, co jsem potřeboval. Bez PHP, rovnou v MySQL. Kód je dostatečně rychlý i pro záznamy z celého měsíce. Za mě je problém vyřešen, děkuji.

[rooobertek]

Pozor,

Kód: [Vybrat]

AVG(IF(station='1',seta, 0))nerobí to isté ako

Kód: [Vybrat]

AVG(IF(station='1',seta, NULL)).
AVG z "1, NULL" je 1. AVG z "1 a 0" je 0.5.
https://www.w3schools.com/sql/func_mysql_avg.asp . Je tam note: NULL values are ignored