ugrás a tartalomhoz

adatok óránkénti átlag eredményének kinyerése 24 órás adatsorból

SunFighter · Feb. 9. (P), 10.52
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.
select AVG(perc) FROM tabla WHERE datum BEETWEEN datum-kezd AND datum-vege 
Gyakorlatilag ennek a lekérdezésnek 24 adatot kellene kihoznia.

Köszi a segítséget.
 
1

Én a Group by HOUR(date) -et

Nagy Gusztáv · Feb. 9. (P), 11.20
Én a Group by HOUR(date) -et próbálnám.
Ha csak 24 óra adata van benne, akkor ennyi elég is. Ha több, akkor
WHERE date > DATE_SUB(NOW(), INTERVAL 1 DAY)
2

további adat

SunFighter · Feb. 9. (P), 17.37
az adatok egy rádió hallgatottságának adatait menti le percenként. 2018 jan 1-től tárolja az adatokat percenként. minden percben leteszi a teljes és a csatornánkénti hallgatói számot külön-külön, mint stat_id - full - live - mulatos - datum (ez time()) - musor-cim. naponta 1440 adatot generál a tábla,egy grafikonhoz szeretném felhasználni a kinyert adatot, óránkénti átlaggal vagyis 24 óra percenkénti adatait kéne óránként átlagolnia
$aa = dbquery("SELECT AVG(full) AS atlag FROM ". DB_STAT ." WHERE datum > DATE_SUB(NOW(), INTERVAL 1 DAY) Group by HOUR(datum)");
while($aa = dbarray($aa)){
    $a = ROUND($aa['atlag']);
}
ez így csak 1 db 24 órás teljes átlagot hoz eredményül :(
3

Próbáld másképp

Pepita · Feb. 11. (V), 14.20
Lehet, hogy én nem tudtam csak a saját nyelvemre lefordítani a kérdést, de talán ha megpróbálnád másképp feltenni, még az is lehet, hogy közben Te jössz rá a jó megoldásra. :)

Ú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. :)
4

válasz

SunFighter · Feb. 12. (H), 22.56
tömören :)
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:
$ftop = dbquery("SELECT MAX(full) AS atlag, DAYNAME(FROM_UNIXTIME(datum)) AS nap
FROM ". DB_STAT ."
GROUP BY DAYOFYEAR(FROM_UNIXTIME(datum))");
ugye ez napokra megkeresi a teljes hallgatottság maximumát.

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
5

Eleje

Pepita · Feb. 13. (K), 10.39
Akkor kezdjük az elején, ez egy struktúra dump (persze nem a Te tábládé):
CREATE TABLE `test_only` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Ezután kéne némi tesztadat is, amit - mivel valószínűleg nagy számú insert - lehetőség szerint sql fájlban kéne megosztani valahol (pl google drive). Utána jöhetnek a kérdések. :)

(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.)
6

radio_stat.sql

SunFighter · Feb. 13. (K), 23.19


Itt van egy sql file, inzertálva 1 napi adattal, köszönöm
7

Hol?

Pepita · Feb. 14. (Sze), 09.19
Hol az az itt?
8

itten e :D

SunFighter · Feb. 14. (Sze), 10.54
9

Így jobb :)

Pepita · Feb. 14. (Sze), 15.53
Később tudok majd ránézni (és a kérdésekre), viszont addig is egy észrevétel:

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.
10

hiányosság

SunFighter · Feb. 14. (Sze), 21.56
Igen, sajna vannak hiányosságaim a profi mysql szerkezetek létrehozásában, bár ennek ellenére azért elég komoly rendszereket írok. Valamikor tanultam a reduntacia kizárást vagy mit, de nagyon nem használok ilyet, pedig kéne :(

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?
11

Kis türelmet kérek :)

Pepita · Feb. 14. (Sze), 22.19
Tehát félmillió rekord évente...

Varchar-t hogy indexeljem, vagyis mihez?
Megmutatom az alapot hamarosan, de az csak az alap lesz, mint mondtam, a stat-query-k után tudjuk jobban optimalizálni. :)
Most lesz 0.5 - 1 órám ezzel foglalkozni, kis türelmet kérek.
13

"Sima" index

Pepita · Feb. 16. (P), 18.27
Erre gondoltam: varchar mezők önmagukban (= egyenként) full text (hátha keresés is lesz) indexelve:
ALTER TABLE `radio_stat`
ADD FULLTEXT INDEX `musvez` (`musvez`) USING BTREE ,
ADD FULLTEXT INDEX `mcim` (`mcim`) USING BTREE ;
SZERK: rossz az alter, FULLTEXT nem lehet BTREE
12

Specifikáció

Pepita · Feb. 14. (Sze), 23.05
Ezt a részt:
---
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.
14

re...

SunFighter · Feb. 19. (H), 16.39
Bocsi, de civil munkámból kifolyólag rengeteget vagyok nem itthon, most 2 hétig a géphez nem volt időm nyúlni.

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.
15

Ajjaj

Pepita · Feb. 19. (H), 18.08
Jelenleg php-ban lehívott time() változót használom
Ez függvény, nem változó, ezért meghívod, és visszaadja az 1970.01.01 00:00:00 óta eltelt másodpercek számát.
Valószínű a túl pongyola / pontatlan fogalmazás miatt nem érteni, hogy mikor mit szeretnél.

Jobb lenne ezt a dátumot datetime vagy timestamp-ba betenni?
Valószínűleg igen, a timestamp ugyan egy picit hosszabb (talán 8 byte?), viszont meg van az az előnye, hogy query-ben stringként meg lehet adni (relációval is) és emellett qrva gyors.

a now() mysql, timestampot tesz le az adatbázisba?
Semmit nem "tesz le", ez is egy függvény, ami eredményt ad vissza. Te illetve a queryd dönti el, hogy mihez kezd vele.

eddig mindig csak a time() részt használtam és azt alakítottam vissza dátum formára php-ban
Ez nem feltétlenül baj, legtöbb esetben jobb és biztosabb megoldás, ha vagy mindenhol PHP-s idő van, vagy mindenhol MySql. Ha másért nem, csak azért, mert "hátha nem egyformán jár" a két óra.
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.