| Autor | Zpráva | ||
|---|---|---|---|
| srigi Profil |
#1 · Zasláno: 10. 8. 2010, 14:48:57 · Upravil/a: srigi
Mam tabulku ratings so stlpcami id, score (int), created (date). Surove data vypadaju cca takto:
+-------+------------+ | score | created | +-------+------------+ | 10 | 2010-08-09 | | 10 | 2010-08-09 | | 10 | 2010-08-09 | | 10 | 2010-08-09 | | 6 | 2010-08-09 | | 9 | 2010-08-09 | | 7 | 2010-08-09 | | 6 | 2010-08-09 | | 9 | 2010-08-07 | | 9 | 2010-08-06 | | 4 | 2010-08-06 | | 10 | 2010-08-06 | | 10 | 2010-08-04 | | 10 | 2010-08-04 | ... Potrebujem zistit priemerne skore za den. SELECT AVG(score) AS avg, created FROM ratings WHERE created BETWEEN '2010-07-01' AND NOW() GROUP BY DAY(created) ORDER BY created DESC; Vysledkova sada vypada cca takto: +---------+------------+ | avg | created | +---------+------------+ | 8.6667 | 2010-08-09 | | 9.0000 | 2010-08-07 | | 9.2222 | 2010-08-06 | | 10.0000 | 2010-08-04 | | 7.8000 | 2010-08-03 | | 7.0000 | 2010-08-02 | | 7.0000 | 2010-08-01 | | 7.0000 | 2010-07-30 | ... Ked csak posuniem casovy usek o dva mesiace do minulosti, priemery maju zrazu ine hodnoty: +---------+------------+ | avg | created | +---------+------------+ | 9.0000 | 2010-08-09 | | 9.2000 | 2010-08-07 | | 9.3000 | 2010-08-06 | | 10.0000 | 2010-08-04 | | 8.6667 | 2010-08-03 | | 7.7500 | 2010-08-02 | | 7.0000 | 2010-08-01 | | 8.5000 | 2010-07-30 | ... PLS pomozte mi opravit ten SELECT, tak aby pocital priemery spravne, tj. 2010-08-09 AVG = 8.5 |
||
| nightfish Profil |
#2 · Zasláno: 10. 8. 2010, 15:08:09
srigi:
„Ked csak posuniem casovy usek o dva mesiace do minulosti, priemery maju zrazu ine hodnoty:“ Není to způsobené tím, že agregace se provádí jen pro DAY(created), čiliže číslo dne v měsíci? |
||
| srigi Profil |
#3 · Zasláno: 10. 8. 2010, 15:15:04
nightfish:
To bude asi ono. Aky je teda prosim ta spravny zapis toho GROUP BY? |
||
| Kajman_ Profil * |
#4 · Zasláno: 10. 8. 2010, 15:18:57
GROUP BY created |
||
| srigi Profil |
#5 · Zasláno: 10. 8. 2010, 15:22:14
THX, zafungovalo aj
GROUP BY DATE() |
||
|
Časová prodleva: 14 let
|
|||
0