SQL技术咨询：计算系统启停时间及各运行时段总产量

核心思路 #

这是典型的连续相同状态分组问题，用窗口函数可高效解决：

• 用LAG()获取上一行的系统状态，标记新运行时段的起点
• 用累计求和生成每个运行时段的分组ID
• 按分组ID聚合计算各时段的启停时间和总产量

完整SQL代码（适用于MySQL8.0+/PostgreSQL/SQL Server等支持窗口函数的SQL方言） #

假设数据存储在表system_data中，字段与原始数据对应：

WITH run_groups AS (
    -- 第一步：标记运行时段起点，生成分组ID
    SELECT
        timestamp,
        system_status,
        yield_lbs_s,
        -- 当前为运行状态且上一状态为停止时，标记为新时段起点
        CASE 
            WHEN system_status = 1 AND LAG(system_status) OVER (ORDER BY timestamp) = 0 
            THEN 1 
            ELSE 0 
        END AS is_new_run,
        -- 累计求和生成分组ID，连续运行的行会被分到同一组
        SUM(CASE 
            WHEN system_status = 1 AND LAG(system_status) OVER (ORDER BY timestamp) = 0 
            THEN 1 
            ELSE 0 
        END) OVER (ORDER BY timestamp) AS run_group_id
    FROM system_data
),
run_stop_times AS (
    -- 第二步：计算每个运行时段的启停时间和总产量
    SELECT
        run_group_id,
        MIN(timestamp) AS system_start_time, -- 时段启动时间：组内最早的运行时间戳
        -- 时段停止时间：该时段最后一个运行时间之后的第一个停止时间戳
        (SELECT MIN(timestamp) FROM system_data 
         WHERE timestamp > MAX(rg.timestamp) AND system_status = 0) AS system_stop_time,
        SUM(yield_lbs_s) AS total_yield_lbs -- 总产量：组内每秒产量之和
    FROM run_groups rg
    WHERE system_status = 1 -- 仅保留运行状态的行
    GROUP BY run_group_id
)
-- 第三步：生成最终结果，给运行时段编号
SELECT
    ROW_NUMBER() OVER (ORDER BY system_start_time) AS system_run_time,
    system_start_time,
    system_stop_time,
    total_yield_lbs
FROM run_stop_times
ORDER BY system_run_time;

代码解释 #

1. run_groups CTE：

   • LAG(system_status) OVER (ORDER BY timestamp)：获取当前行的上一行系统状态，用于判断是否为新运行时段的开始
   • 累计求和is_new_run值，为每个连续的运行时段分配唯一的run_group_id

2. run_stop_times CTE：

   • 按run_group_id分组，取组内最早时间作为启动时间
   • 通过子查询找到该时段结束后第一个停止状态的时间，作为停止时间
   • 求和组内的每秒产量得到该时段总产量

3. 最终查询：用ROW_NUMBER()为每个运行时段生成编号，按启动时间排序输出

测试结果 #

代入你提供的示例数据，执行后将得到期望输出：

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