工厂以每天8点00分钟作为当日工作日的起始时间。 业务逻辑：求解今日8点至次日8点，每两个时间段内的设备各种运行状态时间汇总（秒数）。
模板时间阶段:字段包含（开始时间段，结束时间段，开始时间段转化分钟数，结束时间转化分钟数）。 **备注：将工作课表起止时间转换分钟数（分） （表名称：duty_time） 分钟数（分）=时*60+分；跨日（次日）+1440;
order_id |start_time |end_time |start_int |end_int
01 |08:00 |10:00 |0480 |0600
02 |10:00 |12:00 |0600 |0720
03 |12:00 |14:00 |0720 |0840
04 |14:00 |16:00 |0840 |0960
05 |16:00 |18:00 |0960 |1080
06 |18:00 |20:00 |1080 |1200
07 |20:00 |22:00 |1200 |1320
08 |22:00 |00:00 |1320 |1440
09 |00:00 |02:00 |1440 |1560
10 |12:00 |04:00 |1560 |1680
11 |04:00 |06:00 |1680 |1800
12 |06:00 |08:00 |1800 |1920
设备状态变化记录表，字段包含(状态标记，开始时间，结束时间)
state_flag |start_time |end_time
1 |2024-06-01 07:19:01.000 |2024-06-01 08:09:01.000
2 |2024-06-01 08:09:01.000 |2024-06-01 08:19:01.000
3 |2024-06-01 08:19:01.000 |2024-06-01 09:58:01.000
4 |2024-06-01 09:58:01.000 |2024-06-01 10:02:01.000
2 |2024-06-01 10:02:01.000 |2024-06-01 12:15:01.000
3 |2024-06-01 12:15:01.000 |2024-06-01 13:56:01.000
1 |2024-06-01 13:56:01.000 |2024-06-01 15:45:01.000
3 |2024-06-01 15:45:01.000 |2024-06-01 18:31:01.000
4 |2024-06-01 18:31:01.000 |2024-06-01 20:01:01.000
1 |2024-06-01 20:01:01.000 |2024-06-02 09:01:01.000
2 |2024-06-02 09:01:01.000 |2024-06-08 08:01:01.000
3 |2024-06-08 08:01:01.000 |2024-06-08 12:01:01.000
统计每2个小时时间段的设备各种状态时间，注意设备状态时间可能跨越每两个小时边界值，此时需要将设备状态时间数据拆分为两条。 使用SQL做数据分析：
SELECT
order_no,
start_time,
end_time,
day_start_time,
day_end_time,
state_id,
/*时长秒数*/
SUM(DATEDIFF(SECOND, revised_start_time, revised_end_time)) AS second_sum
FROM (
SELECT
base.order_no,
base.start_time,
base.end_time,
base.day_start_time,
base.day_end_time,
state.id AS state_id,
state.state_flag,
state.start_time AS actual_start_time,
state.end_time AS actual_end_time,
/*---设备状态时间跨越每两小时边界，将其截取，拆分---*/
CASE WHEN state.start_time < base.day_start_time THEN base.day_start_time ELSE state.start_time END AS revised_start_time,
/*---设备状态时间跨越每两小时边界，将其截取，拆分---*/
CASE WHEN state.end_time >= base.day_end_time THEN base.day_end_time ELSE state.end_time END AS revised_end_time
FROM (
SELECT
CAST(row_no AS int) AS order_no,
start_time,
end_time,
start_int,
end_int,
DATEADD(MINUTE, start_int, ddtt.date_time) AS day_start_time,
DATEADD(MINUTE, end_int, ddtt.date_time) AS day_end_time
FROM (
VALUES(CAST('2024-06-01' AS datetime))
)AS ddtt(date_time)
,demo.segment_model
)AS base
LEFT JOIN demo.demo_state_info AS state
/*---状态时间与时间段模板关联条件---*/
ON state.start_time <= base.day_end_time
AND state.end_time > base.day_start_time
)AS tmp
GROUP BY
order_no,
start_time,
end_time,
day_start_time,
day_end_time,
state_id
