Potrzebuję wybrać z mojej tabeli events
liczbę wydarzeń, które aktualnie trwają, dla każdej godziny z osobna, w podanym przedziale czasowym. Innymi słowy potrzebne mi są dane w formacie:
date,value
2017-07-08 00:00:00,0
2017-07-08 01:00:00,0
2017-07-08 02:00:00,0
2017-07-08 03:00:00,0
2017-07-08 04:00:00,0
2017-07-08 05:00:00,0
2017-07-08 06:00:00,0
2017-07-08 07:00:00,1
2017-07-08 08:00:00,1
2017-07-08 09:00:00,2
2017-07-08 10:00:00,3
2017-07-08 11:00:00,3
2017-07-08 12:00:00,2
2017-07-08 13:00:00,3
2017-07-08 14:00:00,2
2017-07-08 15:00:00,1
2017-07-08 16:00:00,1
2017-07-08 17:00:00,1
2017-07-08 18:00:00,6
2017-07-08 19:00:00,0
2017-07-08 20:00:00,0
2017-07-08 21:00:00,3
2017-07-08 22:00:00,2
2017-07-08 23:00:00,0
Zależy mi na tym, żeby puste godziny liczyły się jako 0, ponieważ nie chcę tego uzupełniać po stronie aplikacji, a moja libka wymaga takiego formatu do zbudowania heat mapy.
Wymyśliłem coś takiego, ale wydaje mi się to mocno skomplikowane:
SET @date_from := '2017-07-07 00:00:00';
SET @date_to := '2017-07-12 23:59:59';
SELECT
dt.date,
count(e.id) AS value,
GROUP_CONCAT(CONCAT_WS(', ', e.id, e.start_time, e.end_time) SEPARATOR ' | ') AS data
FROM (
-- to zapytanie generuje daty i godziny (interval 1 hour) dla podanego przedziału @date_from @date_to
-- PostgreSQL I miss you :( generate_series('2017-07-07' :: timestamp, '2017-07-12' :: timestamp, '1 hour');
SELECT DATE_ADD(@date_from, INTERVAL n * 24 + h.hour HOUR) date
FROM generator_256
CROSS JOIN (SELECT n hour
FROM generator_256
WHERE n < 24) h
WHERE n <= DATEDIFF(@date_to, @date_from)
ORDER BY date
) dt
LEFT JOIN events e ON dt.date >= e.start_time AND dt.date < e.end_time
GROUP BY dt.date
SQLFiddle
W fidlu jest przykładowa tabelka z datami na cały miesiąc, więc można pokombinować :)
Z tego co wiem, nie mogę użyć Sequence Storage Engine, więc zastąpiłem to generator views.
Jakby ktoś się zastanawiał o co chodzi z tą heat mapą, to tutaj jest jak to ma wyglądać: Day / Hour Heatmap (chociaż nie patrzcie na format tam podany, bo nie używam tej biblioteki).
@Marcin.Miga masz dobre pomysły, więc jak będziesz miał chwilę, to może i tu coś zaradzisz.