Jak zliczyć ilość jednocześnie trwających wydarzeń na podstawie czasu rozpoczęcia i zakończenia - Day/Hour Heatmap?

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.

Nie wiem, czy w mysql, można to jakoś lepiej zrobić.
Może stworzyć tabelę pomocniczą wypełnioną takimi przedziałami?
Zwróć też uwagę na warunek

events e ON dt.DATE >= e.start_time AND dt.DATE < e.end_time

Jeżeli wydarzenie zacznie się przed daną godziną, i skończy się po danej godzinie, też powinno być liczone. np. event : start_time: 10:25, end_time: 14:40. W, w przedziałach 11, 12, 13,14 powinno chyba być?
Ogólnie ja daję w takich przypadkach coś mniej więcej takiego e.start_time < @to_date and e.end_time >@from_date

Wydaje mi się dobrze... Musi być LEFT JOIN, musi być grupowanie... Ten UNION (w funkcji) też jest OK. Ale można go zastąpić czymś innym. Np. tym: http://sqlfiddle.com/#!9/a5640/40
Trzeba użyć jakiejś dużej tabeli... dla pustej bazy, to global_variables lub session_variables, dla zapełnionej, to columns albo statistics :)

Dzięki Panowie.

Użyłem tego: https://github.com/gabfl/mysql_generate_series, co pozwoliło zredukować zapytanie do:

CALL generate_series_date_hour(:from, :to, 1);
SELECT
  dt.series            AS datetime,
  count(e.id)          AS value,
  DATE(dt.series)      AS date,
  HOUR(dt.series)      AS hour
FROM series_tmp dt
  LEFT JOIN events e ON dt.series >= e.start_time AND dt.series < e.end_time
GROUP BY dt.series

Pozwolę sobie odświeżyć temat, bo mam powiązane pytanie. Są trzy zapytania:

1. Ilość jednocześnie pracujących osób w ciągu godz. (czyli trwających zleceń).
2. Dostępność osób do pracy zaznaczona na kalendarzu (od - do + dzień)
3. Dostępność osób ale minus trwające zlecenia tzn. jak ktoś zaznaczył, że jest dostępny od 8 - 16 (pojawia sie w drugim zapytaniu), ale ma zlecenie od 11 - 15 (pierwsze zapytanie), to to zapytanie ma pokazać, że jest na idlu w przedziale 8 - 11 i 15 - 16.

Jak do tego podszedłem? Zapytanie 3 jest w zasadzie identyczne jak 2, tylko joinuje zamówienia, a później w sprytny sposób COUNT(CASE WHEN o.id IS NULL THEN c.id END) liczę tylko jak nie ma zamówień ;)

SELECT -- ten select jest wspólny dla wszystkich zapytań
  day, -- dzień tygodnia
  hour, -- godzina 
  SUM(week.value) AS sum, -- suma dla tej godziny i dnia tygodnia
  AVG(week.value) AS average -- średnia dla tegej godziny i dnia tygodnia
FROM (
       SELECT
         MONDAY_BASED_DAYOFWEEK(dt.series)           AS day,
         HOUR(dt.series)                             AS hour,
         COUNT(CASE WHEN o.id IS NULL THEN c.id END) AS value
       FROM series_tmp dt
         LEFT JOIN calendars c
           ON DATE(dt.series) = c.date AND HOUR(dt.series) >= c.time_start AND HOUR(dt.series) < c.time_end
         LEFT JOIN orders o 
           ON dt.series >= o.start AND dt.series < DATE_ADD(o.start, INTERVAL length * 60 MINUTE)
       WHERE HOUR(dt.series) BETWEEN 6 AND 24
       GROUP BY WEEK(dt.series), day, hour
     ) AS week
GROUP BY week.day, week.hour;

Liczy dobrze, ale nie podoba mi się jakoś to zapytanie i potrzebuję approvalu.
Inaczej jeszcze można to wytłumaczyć tak:

Całe A, to zapytanie nr 2.
Całe B to zapytanie nr 1.
A - B to zapytanie nr 3 :P

Pozostałe zapytania:
1)

SELECT
  day,
  hour,
  SUM(week.value) AS sum,
  AVG(week.value) AS average
FROM (
       SELECT
         MONDAY_BASED_DAYOFWEEK(dt.series) AS day,
         HOUR(dt.series)                   AS hour,
         count(o.id)                       AS value
       FROM series_tmp dt
         LEFT JOIN orders o ON dt.series >= o.start AND dt.series < DATE_ADD(o.start, INTERVAL length * 60 MINUTE)
       WHERE HOUR(dt.series) BETWEEN 6 AND 24
       GROUP BY WEEK(dt.series), day, hour
     ) AS week
GROUP BY week.day, week.hour;

SELECT
  day,
  hour,
  SUM(week.value) AS sum,
  AVG(week.value) AS average
FROM (
       SELECT
         MONDAY_BASED_DAYOFWEEK(dt.series) AS day,
         HOUR(dt.series)                   AS hour,
         count(c.id)                       AS value
       FROM series_tmp dt
         LEFT JOIN calendars c
           ON DATE(dt.series) = c.date AND HOUR(dt.series) >= c.time_start AND HOUR(dt.series) < c.time_end
       WHERE HOUR(dt.series) BETWEEN 6 AND 24
       GROUP BY WEEK(dt.series), day, hour
     ) AS week
GROUP BY week.day, week.hour;

@Marcin.Miga jaka jest Twoja opinia, Señor? Pewnie słabo wytłumaczyłem o co kaman w tych zapytaniach, ale może jesteś w stanie zaproponować lepsze rozwiązanie.