Autor Zpráva
LadislavMuzik
Profil
Dobrý den, dělám meteostanici a nyní řeším zda se dá v MYSQL udělat dotaz, který např. z tabulky teplota (sloupec teplota a sloupec date-time) vypíše všechny MAX teploty za každý den. Prostě aby to vrátilo 1.1.2016, 0
2.1.2016,2
3.1.2016,1
...
...

Děkuji
Kcko
Profil
SELECT max(teplota), datum FROM tabulka GROUP BY datum
LadislavMuzik
Profil
Kcko:
Děkuji, teď jsem na to přišel, akorát mám date-time ve formátu 2016-01-10 12:29:28 tak jsem udělal toto:
SELECT MAX(temp), date_time, DATE_FORMAT(date_time,'%Y-%m-%d') AS date FROM `temp` GROUP BY date
akorát vypsaný date_time nekoresponduje s max teplotou, ale vypíše první date_time v konrétním dni
Keeehi
Profil
LadislavMuzik:
ale vypíše první date_time v konrétním dni
Správnější by bylo říci náhodný date_time. To že je to zrovna první je náhoda.

Pokud chceš k té maximální teplotě i časy, ve kterých byla zaznamenána ten den, pak budeš muset tu tabulku JOINout samu na sebe.
SELECT temp.temp, temp.date_time
FROM temp
JOIN (
    SELECT MAX(temp) AS max_temp, DATE_FORMAT(date_time,'%Y-%m-%d') AS date
    FROM temp
    GROUP BY date
) AS t
ON DATE_FORMAT(temp.date_time,'%Y-%m-%d') = t.date

Bylo by vhodné rozdělit sloupec s časem na dva. Jeden s datem a druhý s časem. Databáze pak nebude muset při každém dotazu extrahovat ze všech záznamů datum.
juriad_
Profil *
Keeehi:
Jeden s DATE a druhý s DATETIME - sloupec TIME nepotřebuje a je zbytečné z DATE a TIME vytvářet DATETIME a pak index nad dvojicí (date, temp). V MySQL je filrování a groupování rychlé jen pokud pracuješ přímo se sloupcem, nesmíš na něm provést žádnou transformaci (jako provádíš s tím DATE_FORMAT), jinak se vždy musí projít celá tabulka namísto jen malé části.
Keeehi
Profil
juriad:
Potřebuje jeden s DATE, to je jasné, kvůli indexům. A pak potřebuje ještě jeden sloupec aby měl kompletní data. Jestli to bude TIME, DATETIME nebo něco jiného, je už jedno. Resp. záleží k čemu všemu dalšímu to chce používat. O čemž ale bohužel nepíše.
LadislavMuzik
Profil
Děkuji. Tyto data se vypisují podle potřeb vždy za konkrétní období, maximálně za celý rok, měsíc, týden, den, hodinu, prostě čas od do. V tabulce teplota je za rok cca 150000 záznamů. Tabulka s větrem má za rok okolo 600000 záznamů, řešená je stejný způsobem. Rád si od Vás nechám poradit. Zde je odkaz: meteo-zdejcina.wz.cz/aktualni-data


Třeba na té titulní straně používám DATE_FORMAT jen ve třech dotazech:

SELECT `temp`, DATE_FORMAT(`date_time`,"%H:%i") AS `time` FROM `temp` WHERE `temp` =
(SELECT MAX(`temp`) FROM `temp` WHERE `date_time` > ?)
AND `date_time` > ?
ORDER BY id
DESC
LIMIT 1

SELECT `temp`, DATE_FORMAT(`date_time`,"%H:%i") AS `time` FROM `temp` WHERE `temp` =
(SELECT MIN(`temp`) FROM `temp` WHERE `date_time` > ?)
AND `date_time` > ?
ORDER BY id
DESC
LIMIT 1

SELECT DATE_FORMAT(`date_time`,"%H:%i") AS `time`, DATE_FORMAT(`date_time`,"%d.%m.%Y") AS `date`
FROM `rain`
WHERE `rain` <
(SELECT `rain` FROM `rain` ORDER BY id DESC LIMIT 1)
ORDER BY id DESC
LIMIT 1
TomášK
Profil
Já bych ti poradil, ať změníš databázi, ale je mi jasné, že to zřejmě nepřichází v úvahu. Tak jen pro srovnání Postgres:

Vytvoříme tabulku s 600 000 náhodnými čísly, pro každé je náhodný timestamp z letošního roku:
create table t as SELECT generate_series(1,600000) AS id,random() AS temp,  timestamp '2016-01-01' + random() * (timestamp '2016-12-31' - timestamp '2016-01-01') AS dt;

Vytvoříme index:
create index idx on t (date_trunc('day', dt), temp DESC);

Dotaz, který vrací nejvyšší teplotu (náhodné číslo) pro každý den a čas, kdy byla získaná:
select distinct on(date_trunc('day', dt)) dt, temp from t order by date_trunc('day', dt), temp DESC;
283.889 ms

 explain select distinct on(date_trunc('day', dt)) dt, temp from t order by date_trunc('day', dt), temp DESC;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Unique  (cost=0.42..36540.43 rows=599221 width=16)
   ->  Index Scan using idx on t  (cost=0.42..35040.43 rows=600000 width=16)
(2 rows)

Time: 0.284 m
LadislavMuzik
Profil
Jaký je rozdíl když si udělám sloupec s time a index s time?
Kcko
Profil
LadislavMuzik:
Tak si to zkus ... a před SQL dotaz dej EXPLAIN
LadislavMuzik
Profil
TomášK:
trunc

Ať nad tím přemíšlím jak nad tím přemíšlím, tak nemohu přijít na to jak udělám v mysql tohle:
create index idx on t (date_trunc('day', dt), temp DESC);
respektive tuto část: date_trunc('day', dt), exvivalent v mysql by bylo třeba tohle: DATE_FORMAT(dt, '%Y-%m-%d 00:00:00'), ale to my nefunguje.
Kajman
Profil
V tom je právě ta potíž, protože Mysql neumí indexy nad výsledkem funkce, je ke zvýšení rychlosti potřeba pomocný sloupec s datumem a udělat index nad ním.
LadislavMuzik
Profil
Tak jsem u tabulek udělal sloupec year, month, day, time, a date_time, indexy na year, month, day a day_time. Optimalizoval dotazy a na místo 2,5s načítání hlavní stránky se načítá asi 50ms.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: