mysql - 将结果分组为每小时的子组

Question

我有一个查询，该查询会得出给定时间段/天数内资源的总预订时间。我想将预订的总金额拆分为按小时分组。

SELECT
  resourceID,
  SEC_TO_TIME(
    SUM(
      CASE WHEN DATEDIFF(DATE(endDatetime), DATE(startDatetime)) = 0 THEN
        GREATEST(TIME_TO_SEC(TIMEDIFF(LEAST('16:00:00', TIME(endDatetime)), LEAST(GREATEST('08:00:00', TIME(startDatetime)), '16:00:00'))), 0)
      ELSE
        TIME_TO_SEC(TIMEDIFF('16:00:00', LEAST(GREATEST('08:00:00', TIME(startDatetime)), '16:00:00')))
        + TIME_TO_SEC(TIMEDIFF(GREATEST('08:00:00', LEAST('16:00:00', TIME(endDatetime))), '08:00:00'))
        + ((DATEDIFF(DATE(endDatetime), DATE(startDatetime)) - 1) * TIME_TO_SEC(TIMEDIFF('16:00:00', '08:00:00')))
      END
    )
  ) AS booked_time
FROM table_name
WHERE resourceID = 1
AND startDatetime BETWEEN '2019-01-01 00:00:00' AND '2019-01-05 23:59:59'
GROUP BY resourceID

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

这将返回总预订时间。我希望结果每小时分组:外汇。

2019-01-01 08:00-09:00, BookedTime 0:30
2019-01-01 09:00-10:00, BookedTime 1:00
2019-01-01 10:00-11:00, BookedTime 0:30
2019-01-02 08:00-09:00, BookedTime 0:30
2019-01-02 09:00-10:00, BookedTime 1:00
2019-01-02 10:00-11:00, BookedTime 0:30

等等。

编辑:

我也希望得到以下结果的解决方案:

08:00-09:00, BookedTime 6:30
09:00-10:00, BookedTime 10:00
10:00-11:00, BookedTime 3:30

不同之处在于您将多个日期的每小时之间的总预订时间相加。

Answer 1

<强> SQL DEMO

首先，您需要为所有日期和时间创建一个表格。

注意100年的数据只需要100 * 365 * 24 = 876000行您可以选择在WHERE条件上创建的日期范围

CREATE TABLE allDays as
select DATE_ADD(selected_date, INTERVAL start_time HOUR) as start_time
     , DATE_ADD(selected_date, INTERVAL end_time HOUR) as end_time
from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date 
 from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
 ) v
CROSS JOIN (
   SELECT 00 as start_time, 01 as end_time UNION 
   SELECT 01 as start_time, 02 as end_time UNION 
   SELECT 02 as start_time, 03 as end_time UNION 
   SELECT 03 as start_time, 04 as end_time UNION 
   SELECT 04 as start_time, 05 as end_time UNION 
   SELECT 05 as start_time, 06 as end_time UNION 
   SELECT 06 as start_time, 07 as end_time UNION 
   SELECT 07 as start_time, 08 as end_time UNION 
   SELECT 08 as start_time, 09 as end_time UNION 
   SELECT 09 as start_time, 10 as end_time UNION 
   SELECT 10 as start_time, 11 as end_time UNION 
   SELECT 11 as start_time, 12 as end_time UNION 
   SELECT 12 as start_time, 13 as end_time UNION 
   SELECT 13 as start_time, 14 as end_time UNION 
   SELECT 14 as start_time, 15 as end_time UNION 
   SELECT 15 as start_time, 16 as end_time UNION 
   SELECT 16 as start_time, 17 as end_time UNION 
   SELECT 17 as start_time, 18 as end_time UNION 
   SELECT 18 as start_time, 19 as end_time UNION 
   SELECT 19 as start_time, 20 as end_time UNION 
   SELECT 20 as start_time, 21 as end_time UNION 
   SELECT 21 as start_time, 22 as end_time UNION 
   SELECT 22 as start_time, 23 as end_time UNION 
   SELECT 23 as start_time, 24 as end_time 
)  t
where selected_date between '2018-12-31' and '2019-01-10'
;

现在您可以在这些时段之间分配所有预订

SELECT *
     , GREATEST( a.start_time , t.startDatetime ) as book_start
     , LEAST( a.end_time , t.endDatetime ) as book_end
     , TIMEDIFF( LEAST( a.end_time , t.endDatetime )
               , GREATEST( a.start_time , t.startDatetime ) ) as time_diff
FROM allDays a
LEFT JOIN table_name t
  ON a.start_time <= t.endDatetime
 AND a.end_time   >= t.startDatetime  
WHERE start_time < '2019-01-05 23:59:59'
  AND end_time   > '2019-01-01 00:00:00'
ORDER BY 1  
;

现在您可以对每个时段进行分组并添加预订时间

With time_slots as (
SELECT *
     , GREATEST( a.start_time , t.startDatetime ) as book_start
     , LEAST( a.end_time , t.endDatetime ) as book_end
     , TIMEDIFF( LEAST( a.end_time , t.endDatetime )
               , GREATEST( a.start_time , t.startDatetime ) ) as time_diff


FROM allDays a
LEFT JOIN table_name t
  ON a.start_time <= t.endDatetime
 AND a.end_time   >= t.startDatetime  
WHERE start_time < '2019-01-05 23:59:59'
  AND end_time   > '2019-01-01 00:00:00'
ORDER BY 1  
)
SELECT start_time
     , end_time
     , SEC_TO_TIME( SUM( TIME_TO_SEC( `time_diff` ) ) ) AS timeSum  
FROM time_slots
GROUP BY start_time, end_time
ORDER BY 1
;