Villanyorabol kinyert adataimat szeretnem megjeleniteni Grafanaban.
A cel az, hogy napi, heti illetve havi bontasban lassam a felhasznalast (kWh-ban).
Igy nez ki a mar nagyjabol formazott adathalmazom:
ts val
2022-05-07 17:06:58 004139.17
2022-05-07 17:12:32 004139.19
2022-05-07 17:18:06 004139.20
2022-05-07 17:29:13 004139.21
2022-05-07 17:34:47 004139.22
2022-05-07 17:40:21 004139.23
2022-05-07 17:45:55 004139.24
2022-05-07 17:51:29 004139.25
2022-05-07 17:57:03 004139.28
2022-05-07 18:02:37 004139.31
2022-05-07 18:08:11 004139.33
2022-05-07 18:13:45 004139.35
2022-05-07 18:19:19 004139.38
2022-05-07 18:24:52 004139.44
2022-05-07 18:30:25 004139.60
Ha jol gondolkozom, akkor ahhoz hogy egy napi bontast lassak, ki kene szamolni az orankenti fogyasztast (lehetne kisebb interval is, hogy egy oran belul tobb datapointom legyen, de szerintem felesleges).
Es akkor a grafikonon az X tengely az idopont: 0 ora, 1 ora, 2 ora, 23 oraig (vagy masnap 0-ig...?) az Y tengely pedig a max-min deltak az adott oraban.
Ugye a nulladik oraban a delta mindig 0 lenne, viszont 1 oranal mar az elso oraban mert legkisebb es legnagyobb ertek kulonbseget kene megjeleniteni es igy tovabb...
Addig jutottam, hogy le tudtam kerdezni az adott oraban a legkisebb es legnagyobb ertekeket, de az osszeboronalas mar nem ment.
Ez a kovetkezo keppen nez ki:
A tabla DDL-je:
create table ts_string
(
id integer not null,
ts bigint not null,
val text,
ack boolean,
_from integer,
q integer,
primary key (id, ts)
);
A legkisebb ertek az adott oraban:
select distinct on (1) date_trunc('hour',to_timestamp(ts/1000)), val from ts_string where id = 2 order by 1,2 asc;A legnagyobb ertek az adott oraban:
select distinct on (1) date_trunc('hour',to_timestamp(ts/1000)), val from ts_string where id = 2 order by 1,2 desc;
Ugye ezt a ket erteket kene kivonni egymasbol es lekorlatozni az adathalmazt a mai napra, azaz majus 7. 00:00-tol 23:59-ig.
Talan valahogy a date_trunc-os datum alapjan kene GROUP BY-olni es akkor a min_val es max_val aliasu selecteket kivonni egymasbol, de a teljes megoldasig nem jutottam.
Ha segit itt egy kis raw data a ts_string tablabol:
id,ts,val,ack,_from,q
2,1651943218419,004139.17,true,1,0
2,1651943552309,004139.19,true,1,0
2,1651943886315,004139.20,true,1,0
2,1651944553927,004139.21,true,1,0
2,1651944887804,004139.22,true,1,0
2,1651945221732,004139.23,true,1,0
2,1651945555690,004139.24,true,1,0
2,1651945889426,004139.25,true,1,0
2,1651946223444,004139.28,true,1,0
2,1651946557211,004139.31,true,1,0
2,1651946891028,004139.33,true,1,0
2,1651947225136,004139.35,true,1,0
2,1651947559034,004139.38,true,1,0
2,1651947892582,004139.44,true,1,0
2,1651948225906,004139.60,true,1,0
2,1651948559496,004139.81,true,1,0
2,1651948892947,004140.03,true,1,0
2,1651949226157,004140.26,true,1,0
Hozzászólások
Itt tartok:
bar nem tudom, hogy ez mennyire elegans...
Alakul...
Nem teljesen ertem, ha napi meg heti bontasban erdekel az adat, miert orankent group by-olsz es truncate-elsz?
A strange game. The only winning move is not to play. How about a nice game of chess?
Lehet felreertheto voltam, de a napi bontast ugy ertettem, hogy egy nap lebontva orakra:
https://drive.google.com/file/d/1BjacIZqIb7ndz9U2bNYbbARt62L800gU/view?…
Ennek amugy kesobb nem lesz jelentosege, mert nem fogom naponta nezegetni a fogyasztast, valoszinuleg a heti es havi fogja atvenni a helyet.
Most mig keves az adat addig meg jatszok vele addig jo.
1. Állíts elő egy másik táblát, ahol nem abszolút értékek hanem delták vannak. Ha jól látom, PGSQL-ben ezt a LAG függvény tudja. De parasztosan a táblát önmagához joinolva is biztos kijön, csak esetleg lassabb lesz.
2. Lekérdezéskor minden idő-delta párhoz állítsd elő, hogy melyik időintervallumhoz akarod hozzávenni (óra/nap/hónap), kerekítsd le az időt, csoportosíts aszerint, és add össze az adott intervallumokba eső több delta értéket.
Csak 1 kérdés:
Time seriers adatokat miért relációs adatbázisban akarsz feltétlen kezelni?
pl egy InlfuxDB helyből nyújtja ezt funkcionalitást.
GROUP BY time intervals
GROUP BY time() queries group query results by a user-specified time interval.
https://docs.influxdata.com/influxdb/v1.7/query_language/data_exploration/
Ha ez is opció, én lehet ilyen irányba mennék....
PostgreSQL-hez is van extension: https://docs.timescale.com/install/latest/
https://iotguru.cloud
Teljesen jogos a kerdes.
Eloszor InfluxDB-be toltam az adatot, mert a legtobb tutorial is ezt hasznalja es azok alapjan indultam.
Viszont amikor kezzel utanaszamoltam a delta szamitasoknak, akkor nekem bugos volt (el voltak csuszva az ertekek az idopontokhoz kepest).
Mikor probaltam megoldast talalni, akkor ezereves hibakat taltalm a forumjaikon amirke meg valasz sem erkezett.
Amugy egy csomo supportos kerdesnel mondtak, hogy ezt meg azt mar nem fixaljak es kb az volt az erzesem hogy ami van problema a free verzioban azt leszarjak es a community helyett inkabb az enterprise productot toljak. Ertheto, abbol van a penz. De ilyen hozzallassal inkabb hagytam a francba.
A Postgres-t szerintem egy csomo helyre ahova pl NoSQL-t raknak siman jo lenne, mert egy igazi svajci bicska es nagyon sok feature van.
Szerintem ez a helyzet a timeseries adattal is, nem arra lett kitalalva, viszont annyi kiraly fuggvenye van, hogy siman lehet arra is hasznalni.
Mire nekem performance gondjaim lesznek az szerintem meg soka lesz.
Próbáld ki ezt InfluxDB helyett.
Erdekes cucc, koszonom. Mivel az api-ja InfluxDB compliant, ezert meg hasznalni is tudnam.
Valoszinuleg ez kene nekem: https://docs.victoriametrics.com/MetricsQL.html#delta
Most ugy nez ki, hogy meg vannak a szukseges query-k Postgres-ben, de ha elakadok es valamit vegkepp nem sikerul megoldanom, akkor kiprobalom.
Annyi, hogy a sajat query-knek megvan az az elonye, hogy muszaj ertenem mi tortenik, de cserebe biztos is lehetek benne, hogy az ertekekek korrektek.
Az ilyen Timeseries DB-knel kicsit blackbox a dolog es tokre lehet, hogy nem is azt az erteket latom a grafikonon amit kene.
https://iotguru.cloud/field/nwsrddMGJAJX4r2w28ER6g/emeter_2_power
Kulisszatitok: én úgy csinálom, hogy egyrészt nem SQL, hanem Cassandra, másrészt van egy tábla, amiben a nyers adatok vannak és van négy tábla, amiben az 5 perces, 60 perces, 3 órás és az egész napos átlagolt adatok vannak és fut id hash alapján adott időpontban 5 percenként olyan job, ami a nyers adatokból átlagolja az 5 perces adatokat, majd az 5 perces adatokból a 60 perces adatokat, a 60 perces adatokból a 3 órás adatokat és a 3 órás adatokból az egész napos adatokat. A megjelenítés pedig ezekből a táblákból megy.
Nincs olyan, hogy nulladik óra, a timeseries egy folyamatos dolog, ha nem csinálsz származtatott segédtáblákat, akkor egy éves range, havi bontás és perces adat esetén fel kell olvasnod mondjuk félmillió adatot, amiből aztán ki fog jönni 12 hónap adata, ha előre összeraktál egynapos átlagokat, akkor meg csak 365 adatot kell felolvasnod.
https://iotguru.cloud
Koszi a tippeket.
Gondoltam hogy majd hozzaszolsz, mert mas hasonlo topicokban mar lattam, hogy foglalkozol ezzel... :)
Igen a Cassandra, vagy valami hasonlo (mondjuk HBase) DB sokkal jobban illene a problemahoz.
Viszont azokkal annyira nem vagyok kepben es a Postgres mindent is tud, szoval ezzel inditottam.
Szerintem meg messze van az hogy performance gondjaim legyenek, de majd ha odajutok akkor mindenkeppen fontolora veszem hogy az altalad ajanlott mondon a szarmaztatott ertekeket egy view-ba tegyem.
A nulladik ora tenyleg ertelmezhetetlen, csak tegnap mar eleg faradt voltam es csak egy adott napban gondolkoztam aminek van kezdete meg vege, de nyilvan ez timeseries adatnal nem ertelmezheto.
Most igy nez ki a query ami kiad egy napi felhasznalast orankenti bontasban:
A kovetkezo dolgok tortennek:
- a unix timestampet atkonvertalom Postgres altal is ertelmezheto datumma
- a timestampet eltolom egy oraval. miert? azert mert alapbol a 13:00 es 13:42 min es max deltakat a 13. orahoz lennenek rendelve, de nekem ugy logikus hogy ez mar a 14. ora fogyasztasa (szoljatok ha ez hulyeseg) lasd meg az osszevetest itt: https://drive.google.com/file/d/1xm1xmFC46cxnvmNOURksaP2TieHA7F-5/view?… (ugye a nem eltolt grafikon ertekei eggyel mindig le vannak maradva, de amugy megeggeznek)
- ahogy a where reszben lathato egy adott nap mindig az elozo nap 23:00:00-tol tart a kovetkezo nap 00:00:01-ig
A kovetkezo adathalmazra:
Ezeket a deltakat kapom:
Ha valaki esetleg levalidalna nekem, hogy ez a logika igy helyes-e azt nagyon megkoszonnem!
Annyit javasolnék, hogy a ts az timestamp legyen, ne bigint, illetve használd a generate_series függvényt, ha dátum és dátum között akarsz interval műveleteket csinálni. Sokat egyszerűsít a dolgodon.
Amúgy jónak tűnik, de azzal is tudnád egyszerűsíteni a dolgod, ha a letárolásnál deltát tárolnál le.
https://iotguru.cloud
Az adattipust nem en valasztom, hanem egy ioBroker nevu cuccnak a smartmeter pluginja adja ki ezt magabol.
Az egesz mericskeles meg csak most indult, orulok, hogy legalabb a kick off megvan, de majd fontolra veszem a delta tarolast is.
Erdekessegkent emiletem meg, hogy egyebkent ket mero van, az egyik a hoszivattyuhoz tartozik, a masik pedig minden mashoz.
Sajnos az altalanos ora az annyira nem tolja az adatot (nincsenek tizedesek), hogy nem is nagyon lehet a napi bontast plotolni.
Ez pl a mai nap termese:
Ezert erre azt talaltam ki, hogy 3 orara veszem az idointervallumokat es ugy probalok deltakat szamolni, annak a selectje igy nez ki:
es ez az ami kijon belole:
Hat sokkal okosabb nem leszek tole, de hatha majd ha elkezdem tolteni az autot is, akkor gyakrabban kapok adatot (mert ugye csak akkor van uj adat a db-ben ha valtozas tortenik).
Tobbet kell fogyasztanom, hogy szep legyen a grafikon. :D
Ahogy elneztem a te megoldasod, neked ki van irva az atlag fogyasztas egy oraban, tehat az adott ora osszes datapointjanak az atlaga, illetve az adott ora ?legnagyobb es legkisebb mert erteke?.
Miert igy van megcsinalva? Nekem ez igy azert furcsa, mert ez nem mutatja az adott oraban a tenylegesen felvett aram mennyiseget, "csak" azt hogy atlagosan mennyit fogyasztottal.
Szerk.: Nem osszegyujteni nehez az adatot hanem ertelmezni. :D
Ennek a mondatnak nincs értelme. Ha az adott órában volt egy 1000 wattos fogyasztóm végig bekapcsolva, akkor arra az órára az átlag fogyasztás 1000 watt, 1 kWh. Ha volt egy 2000 wattos fogyasztóm, 30 percig ment és 30 percig nem ment, akkor a minimum fogyasztás 0 watt, a maximum fogyasztás 2000 watt, az átlag fogyasztás pedig 1000 watt, ugyanúgy 1 kWh. Ha óránként átlagolsz, akkor tulajdonképpen megkapod óránként a fogyasztást kWh mértékegységben.
https://iotguru.cloud
Igen, a peldad utan nyilvanvalova valt szamomra is, hogy hulyeseget beszelek.
Valamiert az en fejemben az volt, hogy ha egy fogyaszto 30 percig 1000 wattot vesz fel es utana megint 30 percig 2000 wattot, akkor az egy ora alatt felvett mennyiseg 3000 watt, azaz 3 kWh lesz.
Ezzel szemben az atlag pedig "csak" (1000 + 2000) / 2 = 1500 watt azaz 1,5 kWh. Megegyszer atgondolva mar vilagos, hogy a 1,5 kWh a helyes es nem pedig a 3 kWh.
Valoszinuleg kozepiskolas fizika es annak is valahol az eleje lehet...
Hat szegyen, nem szegyen, de eleg reg volt mar es azota nem is nagyon foglalkoztam a temaval. Szoval beneztem.
De legalabb helyre kerultek a fejemben a dolgok.
Egyebkent ez nekem a szamitasnal nem relevans, mert nem az adott idopillanatban atfolyo aramot kapom meg a merotol, hanem az ora allasat.
Ezert kell szimplan a max - min delta, de azert nem art tisztaban lenni azzal, hogy mik is azok a szamok a grafikonon.... :D
Ezért írtam, hogy hasznosabb lenne a deltát letárolni és nem utólag bűvészkedni.
https://iotguru.cloud
A letarolast "nem en vegzem", hanem az ioBroker smartmeter pluginja, max valami stored procedure-t tudnek ra letrehozni, de akkor annyi tortenne, hogy a buveszkedes atvandorolna a selectbol oda.
Esetleg annyi hasznom lenne, hogy az adat szebben/tisztabban nezne ki a szarmaztatott tablaban.
Kevered a teljesítmény és az energia mennyiségeit. Ha 1000 watt teljesítménnyel működik valami 30 percig, akkor 500 Wh energiát vesz fel. Ha azt akarod mondani, hogy 1000Wh energiát vett fel 30 perc alatt, akkor a teljesítménye 2000 watt volt.
Hasonlóan, 30 perc alatt 2000 watt teljesítménnyel 1000 Wh energiát lehet felvenni, de 30 perc alatt 2000 Wh energiát felvenni 4000 watt teljesítménnyel lehet. A 4000 W és 2000 W átlaga valóban 3000 W, de ennek a példádban semmi fizikai jelentése nincs. A watt az időegysége jutó energia (teljesítmény), a Wh az meg energiamennyiség. Olyan nincs, hogy "egy óra alatt felvett mennyiség 3000 W", ez fizikailag értelmetlen.
Tisztázd magadban a teljesítmény és energia fogalmakat, és akkor rendben leszel, tudni fogod, hogy mit hogyan számoljál. Haladsz efelé, hajrá.
Meglesz. Koszi a peldakat, meg a felvilagositas.
Igaz, postgresql-ből nem tud dolgozni, csak saját adatbázisból, de amit te akarsz azt az RRDTOOLS elég jól megcsinálja, mer pontosan erre találták ki.
Én a hasonló motyókat elasticban tárolom. Nem pont erre találták ki de elég jó.
Nem szórakozok a "ritkítással" sem, érdektelen amennyi diszket fogyaszt.
Mivel a sok írásra nagyon kevés olvasás jut ezért nem nagyon éri meg aggregálni se. Az utolsó nap kb. úgyis RAM-ban van, azt meg gyorsan összekapja bármilyen grafikon kell.
Sokat lehetne még optimalizálni az egészen, pár éve gyűlik benne a cucc, egyszer majd jó lesz elszórakozni vele.
Gábriel Ákos
Nem kell neked semmit számolnod.
Tedd le a nyers adatokat DB-be, igen, Influx lenne a legjobb, de a Grafana sok data source-t támogat.
A nézeteket, aggregációkat, ilyen-olyan bontásokat meg bízd a Grafanára.