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.