统计每2个小时时间段的设备各种状态时长，使用SQL做数据分析，“查表法”“表驱动法”使用案例分析

“表驱动法”是一种编程设计模式。

它使用一个表（使用数据结构，如数组、字典，Map等）来存储和关联相关的数据和行为。

通过“查表”来根据输入参数，确定相应的处理逻辑或结果，从而减少使用大量的条件判断语句。

优点：

1. 提高代码可读性和可维护性：将复杂的逻辑以较为清晰直观的方式呈现。

2. 增强灵活性：可以方便地添加、修改或扩展表中的数据和行为。

3. 减少代码重复：避免了重复编写相似的条件判断代码。

其实本文案例并不算是“表驱动法”的实际应用示例。

但编程思想确是共通的。

SQL数据分析案例，小表驱动大表。

工厂以每天8点00分钟作为当日工作日的起始时间。

业务逻辑：求解今日8点至次日8点，每两个时间段内的设备各种运行状态时长汇总（秒数）。

模板时间阶段:字段包含（开始时间段，结束时间段，开始时间段转化分钟数，结束时间转化分钟数）。

**备注：将工作课表起止时间转换分钟数（分） 分钟数（分）=时*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个小时时间段的设备各种状态时间，注意设备状态时间可能跨越每两个小时边界值，此时需要将设备状态时间数据拆分为两条。

        08:00	     10:00	     12:00           14:00
时段	|_______________|_______________|_______________|
情景 a	      |_____________a_____________|   
情景 b,c,d	     |__b__| |__c__| |__d___|

情景a: 设备状态时间开始时间小于10点，结束时间大于12点

情景b:设备状态时间开始时间小于10点，结束时间大于10点，但小于12点。

情景c:设备状态时间开始时间大于10点，结束时间小于12点。

情景d:设备状态时间开始时间小于12点，但大于10点，结束时间大于12点。

实际设备状态数据所能处于时间段的位置，如上。对于跨越时间段边界的数据，统计时将时长拆分，并且分别计算在各自时间段内。

使用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