Comptage horaire basé sur les données d'heure de début et d'heure de fin

Question

Dans le tableau, les données sont au format horodatage mais je les partage au format time(start_at) et time(end_at). Structure du tableau : id,start_at,end_at1,03:00:00,06:00:002,02:00:00,05:00:003,01:00:00,08:00:004,08:00:00 ,13:00:005,09:00:00,21:00:006,13:00:00,16:00:006,15:00:00,19:

P粉432930081 · Answer

ou

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;

ou

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;

La première requête renvoie la colonne heure au format heure, tandis que la deuxième requête renvoie cette colonne sous forme numérique. Choisissez la variante qui est sûre pour vous.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5a77b6e3158be06c7a551cb7e64673de