如何在设备状态时间跨工作周时拆分数据行并关联工作周

核心思路 #

原脚本的问题在于仅匹配设备状态时间完全落在单个工作周内的场景，跨周时没有符合条件的工作周，导致返回NULL。要解决这个问题，需要先找到设备状态时间覆盖的所有工作周，再针对每个工作周计算实际对应的时间段，自动实现跨周拆分。

解决方案SQL #

SELECT
    es.equip_id,
    es.state_code,
    -- 取设备状态开始时间和工作周开始时间的较大值，作为该工作周内的实际开始时间
    GREATEST(es.equip_state_in_datetime, w.start_date_time) AS actual_in_datetime,
    -- 取设备状态结束时间和工作周结束时间的较小值，作为该工作周内的实际结束时间
    LEAST(es.equip_state_out_datetime, w.end_date_time) AS actual_out_datetime,
    w.workweek_id,
    w.start_date_time AS workweek_start,
    w.end_date_time AS workweek_end
FROM
    equip_state_datetime es
-- 用时间重叠条件关联工作周，替代原有的完全包含条件
JOIN
    workweek w ON es.equip_state_in_datetime <= w.end_date_time 
               AND es.equip_state_out_datetime >= w.start_date_time
ORDER BY
    es.equip_id, actual_in_datetime;

逻辑说明 #

1. 关联条件调整：使用es.equip_state_in_datetime <= w.end_date_time AND es.equip_state_out_datetime >= w.start_date_time判断设备状态时间与工作周是否有重叠，这样不管是单周还是跨周场景，都能匹配到所有相关的工作周。
2. 时间段修正：
   • 单周场景：GREATEST和LEAST会直接返回设备原有的起止时间，结果与原需求一致。
   • 跨周场景：设备状态时间会匹配到两个工作周，自动生成两行数据：第一行的实际结束时间为前一个工作周的end_date_time，第二行的实际开始时间为后一个工作周的start_date_time，完美实现拆分需求。
3. 排序优化：按设备ID和实际开始时间排序，方便查看同一设备的状态时间拆分结果。

内容的提问来源于stack exchange，提问作者PekoBeko