基于24小时区间的机器性能数据时段统计方案咨询

核心思路 #

要实现需求，需先合并同一id+task下连续相同的speed状态，再通过窗口函数获取下一个状态的起始时间作为当前状态的结束时间，最后对无后续条目的记录，将结束时间设为24小时区间终点（次日07:00）。

具体SQL实现（以MySQL为例） #

WITH grouped_data AS (
    -- 第一步：标记同一id、task下的连续speed分组，获取初始起始时间
    SELECT 
        id,
        task,
        speed,
        recorded_time AS start_time,
        -- 当当前speed与上一条不同时，标记为新分组
        SUM(CASE WHEN prev_speed != speed OR prev_speed IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY id, task ORDER BY recorded_time) AS speed_group
    FROM (
        -- 获取每条记录的上一条speed，用于判断连续性
        SELECT 
            id,
            task,
            speed,
            recorded_time,
            LAG(speed) OVER (PARTITION BY id, task ORDER BY recorded_time) AS prev_speed
        FROM your_table_name
        -- 筛选目标24小时区间内的数据
        WHERE recorded_time >= '2024-01-01 07:00:00' 
          AND recorded_time < '2024-01-02 07:00:00'
    ) t1
),
final_groups AS (
    -- 第二步：按分组聚合，得到每个连续speed状态的起始时间
    SELECT 
        id,
        task,
        speed,
        MIN(start_time) AS start_time
    FROM grouped_data
    GROUP BY id, task, speed_group, speed
)
-- 第三步：获取结束时间，最后一条用区间终点填充
SELECT 
    id,
    task,
    speed,
    start_time,
    COALESCE(
        LEAD(start_time) OVER (PARTITION BY id ORDER BY start_time),
        '2024-01-02 07:00:00' -- 24小时区间的固定结束时间
    ) AS end_time
FROM final_groups
ORDER BY start_time;

代码解释 #

1. 连续状态分组：通过LAG函数获取上一条记录的speed，判断当前记录是否属于新的连续状态，生成speed_group标记后聚合，合并同一连续状态的多条记录。
2. 结束时间赋值：用LEAD函数获取下一个状态的起始时间作为当前状态的结束时间；当LEAD返回NULL（即最后一条记录），通过COALESCE替换为区间结束时间。
3. 区间过滤：提前筛选07:00-次日07:00内的数据，确保统计范围符合要求。

执行结果 #

注意事项 #

• 若需适配任意日期的24小时区间，可将硬编码的结束时间改为动态计算：DATE_ADD(DATE(MIN(start_time)), INTERVAL 1 DAY + 7 HOUR)。
• 其他SQL方言（如PostgreSQL、SQL Server）仅需调整日期函数写法，核心逻辑保持一致。

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