MySQL 8.0：统计设备Value=1状态时长及启动次数的查询开发

现有一张包含数百万条记录的history表，表结构及示例数据如下：

+----------------------+-----------+------------+
| trnWhen              | Alias     | Value      |
+----------------------+-----------+------------+
| 2022-12-01 00:03:00  | DevID1    |  0         |
| 2022-12-01 00:04:00  | DevID2    |  1         |
| 2022-12-01 01:00:00  | DevID2    |  1         |
| 2022-12-01 01:25:00  | DevID1    |  1         |
| 2022-12-01 02:00:00  | DevID1    |  1         |
| 2022-12-01 02:00:00  | DevID2    |  1         |
| 2022-12-01 02:25:00  | DevID1    |  0         |
| 2022-12-01 02:45:00  | DevID2    |  0         |
| 2022-12-01 03:00:00  | DevID1    |  0         |
| 2022-12-01 03:00:00  | DevID2    |  0         |
| 2022-12-01 03:30:00  | DevID1    |  1         |
| 2022-12-01 04:00:00  | DevID2    |  1         |
| 2022-12-01 04:10:00  | DevID1    |  0         |
+----------------------+-----------+------------+

需求 #

统计指定时间区间（示例：2022-12-01 00:00:00至2022-12-02 00:00:00）内，每个Alias的两个指标：

• RunHours：处于Value=1状态的总时长（按小时计算，保留两位小数）
• Starts：切换至Value=1状态的次数

边界规则 #

• 若某Alias在区间起始前无Value=0的记录，视为区间起始时状态为1
• 若某Alias在区间结束后无Value=0的记录，视为区间结束时状态为1

预期结果 #

Alias      Runhours     Starts
DevID1      1.33          2
DevID2     22.75          1

现有实现问题 #

已完成单个Alias的Starts统计，但无法实现时长统计及多Alias的批量统计：

Set @AL = 'DevID1';

SELECT Alias, COUNT(*) as Starts
FROM history curr
WHERE Alias = @AL 
AND curr.value = 1
AND trnwhen Between '2022-12-01 00:00:00' and '2022-12-05 00:00:00'
AND (
    SELECT value
    FROM history prev
    WHERE Alias = @AL  
    AND prev.trnWhen < curr.trnwhen
    ORDER BY trnwhen DESC
    LIMIT 1
) = 0;

核心思路是通过窗口函数获取每条记录的前序状态，补全区间边界的初始状态，再生成有效状态区间并计算时长，最后聚合得到结果。

完整SQL查询 #

-- 定义统计区间
SET @start_time = '2022-12-01 00:00:00';
SET @end_time = '2022-12-02 00:00:00';

WITH alias_status AS (
    -- 1. 收集每个Alias的相关记录，获取前一条状态
    SELECT 
        h.Alias,
        h.trnWhen,
        h.Value,
        LAG(h.Value) OVER (PARTITION BY h.Alias ORDER BY h.trnWhen) AS prev_value,
        CASE WHEN h.trnWhen BETWEEN @start_time AND @end_time THEN 1 ELSE 0 END AS in_range
    FROM history h
    WHERE 
        h.trnWhen <= @end_time
        AND EXISTS (
            SELECT 1 FROM history h2 
            WHERE h2.Alias = h.Alias 
            AND h2.trnWhen >= @start_time
        )
    UNION ALL
    -- 2. 补全区间起始前的初始状态：无0记录则视为起始时状态为1
    SELECT 
        DISTINCT h.Alias,
        @start_time AS trnWhen,
        1 AS Value,
        NULL AS prev_value,
        1 AS in_range
    FROM history h
    WHERE 
        h.Alias NOT IN (
            SELECT DISTINCT h2.Alias 
            FROM history h2 
            WHERE h2.trnWhen < @start_time AND h2.Value = 0
        )
        AND EXISTS (
            SELECT 1 FROM history h3 
            WHERE h3.Alias = h.Alias 
            AND h3.trnWhen >= @start_time
        )
),
status_intervals AS (
    -- 3. 生成每个状态的有效时间段，处理边界结束时间
    SELECT 
        Alias,
        CASE 
            WHEN prev_value IS NULL AND Value = 1 THEN @start_time
            ELSE trnWhen
        END AS start_interval,
        LEAD(trnWhen) OVER (PARTITION BY Alias ORDER BY trnWhen) AS end_interval,
        Value
    FROM alias_status
    WHERE 
        (prev_value IS NULL OR prev_value != Value)
        OR (trnWhen = @start_time)
),
calculated_intervals AS (
    -- 4. 计算状态区间与统计区间的交集时长（小时）
    SELECT 
        Alias,
        Value,
        TIMESTAMPDIFF(MINUTE, 
            GREATEST(start_interval, @start_time), 
            LEAST(COALESCE(end_interval, @end_time), @end_time)
        ) / 60 AS duration_hours
    FROM status_intervals
)
-- 5. 聚合得到最终结果
SELECT 
    Alias,
    ROUND(SUM(CASE WHEN Value = 1 THEN duration_hours ELSE 0 END), 2) AS Runhours,
    -- 统计切换到1的次数：包括初始状态为1的情况
    SUM(CASE WHEN Value = 1 AND (prev_value IS NULL OR prev_value = 0) THEN 1 ELSE 0 END) AS Starts
FROM (
    SELECT 
        c.Alias,
        c.Value,
        a.prev_value
    FROM calculated_intervals c
    LEFT JOIN alias_status a 
        ON c.Alias = a.Alias 
        AND c.Value = a.Value 
        AND a.trnWhen = c.start_interval
) t
GROUP BY Alias
ORDER BY Alias;

代码说明 #

1. alias_status CTE：收集每个Alias在区间内及之前的记录，用LAG()获取前一条状态，同时补全区间起始时初始状态为1的记录。
2. status_intervals CTE：筛选状态切换的记录，生成每个状态的时间区间，无后续记录时用统计区间结束时间作为状态结束时间。
3. calculated_intervals CTE：计算每个状态区间与统计区间的交集时长，转换为小时单位。
4. 最终聚合：求和得到总运行时长，统计从0切换到1的次数（包括初始状态为1的情况）。

性能优化建议 #

针对百万级数据场景：

• 创建复合索引：(Alias, trnWhen, Value)，大幅提升窗口函数和过滤查询的效率。
• 通过EXISTS过滤出在统计区间内有记录的Alias，避免全表扫描。

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