<p>In der Tabelle liegen die Daten im Zeitstempelformat vor, ich teile sie jedoch im Zeitformat (start_at) und im Zeitformat (end_at). </p> <p>Tabellenstruktur:</p> <pre class="brush:php;toolbar:false;">id, start_at, end_at 1, 03:00:00, 06:00:00 2, 02:00:00, 05:00:00 3, 01:00:00, 08:00:00 4, 08:00:00, 13:00:00 5, 09:00:00, 21:00:00 6, 13:00:00, 16:00:00 6, 15:00:00, 19:00:00</pre> <p>Für das Ergebnis müssen wir die Anzahl der aktiven IDs zwischen start_at und end_at zählen. </p> <pre class="brush:php;toolbar:false;">Stunden, Anzahl 0, 0 1, 1 zweiundzwanzig 3, 3 4, 3 5, 2 6, 1 7, 1 8,1 9, 2 10, 2 11, 2 12, 2 13, 3 14, 2 15, 3 16, 2 17, 2 18, 2 19,1 20,1 21,0 22,0 23, 0</pre></p>

WITH RECURSIVE
cte AS (
    SELECT 0 `hour`
    UNION ALL
    SELECT `hour` + 1 FROM cte WHERE `hour` < 23
)
SELECT cte.`hour`, COUNT(test.id) `count`
FROM cte 
LEFT JOIN test ON cte.`hour` >= HOUR(test.start_at)
              AND cte.`hour` < HOUR(test.end_at)
GROUP BY 1
ORDER BY 1;

WITH RECURSIVE
cte AS (
    SELECT CAST('00:00:00' AS TIME) `hour`
    UNION ALL
    SELECT `hour` + INTERVAL 1 HOUR FROM cte WHERE `hour` < '23:00:00'
)
SELECT cte.`hour`, COUNT(test.id) `count`
FROM cte 
LEFT JOIN test ON cte.`hour` >= test.start_at 
              AND cte.`hour` < test.end_at 
GROUP BY 1
ORDER BY 1;