adatok óránkénti átlag eredményének kinyerése 24 órás adatsorból
Sziasztok!
Elég komplikált dologra adtam a fejem és nem tudom megoldani. Szeretnék kérni egy használható Mysql lekérdezést az alábbi eredmény megjelenítésére.
Percenként tárolok adatbázisban 3 számot, time() idővel. Szeretném az adatokat kinyerni úgy, hogy egy 24 órás időszakban átlagolja az egy óra alatt beérkezett adatokat és az átlag eredményt adja kimenetként, óránként.Gyakorlatilag ennek a lekérdezésnek 24 adatot kellene kihoznia.
Köszi a segítséget.
■ Elég komplikált dologra adtam a fejem és nem tudom megoldani. Szeretnék kérni egy használható Mysql lekérdezést az alábbi eredmény megjelenítésére.
Percenként tárolok adatbázisban 3 számot, time() idővel. Szeretném az adatokat kinyerni úgy, hogy egy 24 órás időszakban átlagolja az egy óra alatt beérkezett adatokat és az átlag eredményt adja kimenetként, óránként.
select AVG(perc) FROM tabla WHERE datum BEETWEEN datum-kezd AND datum-vege Köszi a segítséget.
Én a Group by HOUR(date) -et
Ha csak 24 óra adata van benne, akkor ennyi elég is. Ha több, akkor
WHERE date > DATE_SUB(NOW(), INTERVAL 1 DAY)
további adat
Próbáld másképp
Úgy tűnik, nem túl bonyolult a dolog, mert egyetlen táblában vannak az adatok.
- Először is látni szeretném a tábla strukturális dump-ját. Ez szerintem lehet pontosan, talán nem árulsz el vele olyat, amit nem szeretnél.
- Aztán jó lenne néhány tesztadat, amik szerkezetükben a valódiakra hasonlítanak, de természetesen nem azok. Ezt már lehet, hogy feltölteni kéne valahova.
- Ha ez meg van, akkor egyetlen tömör és értelmes mondatban tedd fel a kérdést, hogy pl: "keresem azokat az órákat az adott napon, amikor az xy műsor hallgatottsága átlagosan 2 felett volt". (Szándékosan nem a Te kérdésed próbáltam megfogalmazni, azt Te tudod.)
Nekem annyi jött le eddig, hogy valamiféle statisztikát szeretnél meglévő adatokból, egyetlen táblából. Ez egyáltalán nem vészes, viszont a feladat megoldásához nem érdekes, hogy honnan és hogyan kerülnek oda az adatok; és szintén nem, hogy az eredményből grafikon lesz-e vagy űrhajó. (Ha megvan a query, utána lehet finomítani, hogy űrhajó legyen.)
Magam példájából mondom, hogy jó eséllyel, ha le tudod szűkíteni a kérdést csak a query megírására, akkor rögtön kipattan a fejedből a megoldás. Ha mégsem, akkor viszont sokkal könnyeben segítünk. :)
válasz
Nem titok, a Laza Rádiónak vagyok az egyik alaptó tulaja, mellette én csinálom a weboldalt és a rádió admin felületét.
Csináltam egy statisztikai adat táblát, amibe a cron alapon percenként leteszem a rádió hallgatottságának adatait. a tábla tartalmazza:
stat_id AI int
full int // összes hallgatói szám
live int //az élő csatorna hallgatói
mulatos int //mulatós csati hallgatói
musvez varchar //ki van adásban
mcim varchar // mi a műsor címe
datum int // amikor leteszi a tálába, ez time()
Igen, grafikonhoz szeretném kinyerni az adatokat, de közben már ez is tovább lépett.
Éves statot megoldottam:
Viszont belefutottam egy olyan statba, ami nem lesz egyszerű.
A műsorvezetők adásainak szeretnék egy statisztikát létrehozni, mégpedig úgy, hogy az adáscímek alapján. Az adások változó időpontban vannak. Pl.: Borisz h-p 10-12 között a bakelittel, de van neki hétfőn és csütörtökön is adása este 20-22 között. Ugye ez adott minden műsorvezetőnél, hogy több adása van más-más időpontban. Ezeket a műsorokat kellene valahogy kiszedni statba, hogy az adott időben, az adott műsorvezetőnek az adott műsora milyen hallgatottsággal bír, de tetézzem, ezt nem a kezdéstől a végéig, hanem a műsor kezdés után fél óra, műsor befejezés előtt fél óra időtartamra generálva :DDDD Tom, nem vagyok egyszerű ember, de akkor a Laza sem létezne :D
Eleje
(Nem rosszból mondom, viszont így biztosítható, hogy 1 perc alatt pontosan olyan táblánk van, mint Neked, életszerű adatokkal. A 2. percben pedig már a konkrét feladattal foglalkozhatunk.)
radio_stat.sql
Itt van egy sql file, inzertálva 1 napi adattal, köszönöm
Hol?
itten e :D
Így jobb :)
Nincs index az autoincrement mezőn kívül semmin, így nagyon lassú lesz.
Alapból ami varchar, azon legyen (egyesével), a többit majd meglátjuk a query-k alapján.
Naponta mennyi rekord kerül be?
`stat_id` int(6)nem lesz túl kicsi?És amint eléri a kb milliós rekordszámot, szükséges lesz particionálni is, ezt szintén query-k függvényében érdemes okosan kitalálni.
hiányosság
Napi szinten 1440 adat kerül bele ebbe a táblába, úgy gondoltam h talán ennyi elég lesz :)
Varchar-t hogy indexeljem, vagyis mihez?
Kis türelmet kérek :)
Most lesz 0.5 - 1 órám ezzel foglalkozni, kis türelmet kérek.
"Sima" index
Specifikáció
---
A műsorvezetők adásainak szeretnék egy statisztikát létrehozni, mégpedig úgy, hogy az adáscímek alapján. Az adások változó időpontban vannak. Pl.: Borisz h-p 10-12 között a bakelittel, de van neki hétfőn és csütörtökön is adása este 20-22 között. Ugye ez adott minden műsorvezetőnél, hogy több adása van más-más időpontban. Ezeket a műsorokat kellene valahogy kiszedni statba, hogy az adott időben, az adott műsorvezetőnek az adott műsora milyen hallgatottsággal bír, de tetézzem, ezt nem a kezdéstől a végéig, hanem a műsor kezdés után fél óra, műsor befejezés előtt fél óra időtartamra generálva :DDDD Tom, nem vagyok egyszerű ember, de akkor a Laza sem létezne :D
---
nem igazán tudom így egyben értelmezni, amennyit értek belőle, az a (most) rendelkezésemre álló adatok alapján több irányvonalat is felvet:
- PHP - ból (vagy amilyen nyelven íródik a backend) kiszámolod előre valahogy azokat az időpontokat, amik közé kell esnie a vizsgált időnek
- Több lépésben oldod meg Mysql-ből: tárolt eljárás és / vagy részhalmaz áttöltése egy temp (MEMORY) táblába
- Technikai (akár temp) tábla létrehozása, "hogy legyen mihez joinolni / WHERE xy IN - ezni"
Valahogy még kisebb és érthetőbb részekre kéne bontani ezt a definíciót, mert - legalábbis számomra - túl "konyhanyelvű".
Fenti 3 irányvonal-tipphez az is hozzá tartozik, hogy nagyon valószínű, hogy egyetlen (nem éppen rövid) query - vel is meg lehet oldani, viszont azzal 0.5 - 2 éven belül teljesítménygondok lesznek, meg fogja fogni a db szervert.
Ugyanígy performancia okból én kerülni szoktam a DAYOFYEAR, FROM_UNIXTIME, DATE_SUB, NOW, stb időfüggvényeket, mert - főként bigdata-nál, de akár 10k rekordtól is - lényegesen gyorsabb és kevesebb memóriát tud igényelni egy TIMESTAMP mező indexelve, és erre egy vagy több string összehasonlítás (datetimefield >= '1999-12-31 20:55:00'). Amit meg lehet így oldani, azt nagyon érdemes.
Még lesz egy pár alter a táblán (int - ek miatt), de most ennyi tellett, bocsi.
re...
Módosítottam fulltextre a két szöveges mezőt,ezzel meg is volnánk.
Dátumok:
Jelenleg php-ban lehívott time() változót használom, ami ugye egy időbélyeget generál. pl.: 1518763138
Jobb lenne ezt a dátumot datetime vagy timestamp-ba betenni? Fain :) Jó sok adatot kell átalakítanom hozzá, de megoldom :) a now() mysql, timestampot tesz le az adatbázisba? Nagyon nem használtam ezeket, így kuka vagyok, eddig mindig csak a time() részt használtam és azt alakítottam vissza dátum formára php-ban.
Ajjaj
Valószínű a túl pongyola / pontatlan fogalmazás miatt nem érteni, hogy mikor mit szeretnél.
Szerintem én érthetően írtam le, hogy ezeket a fv-ket nem ajánlom használni, pláne nem olyan WHERE feltételben, aminek a kiértékeléséhez rekordonként meg kell hívni.
Két időpont közti vizsgálatra nagyon jó a timstamp és a between, átlátható is és gyors:
... WHERE CreatedAt BETWEEN '2018-02-19 10:00:00' AND '2018-02-20'És hogy mi ez a két időpont, nyugodtan ki lehet matekolni PHP-ból is.