寻求SQL查询语句以获取备用电源的启停时间

需求说明 #

需要编写SQL查询语句，从发电机电表数据中提取备用电源的启动和停止时间。

发电机电表数据 #

判定规则 #

• 启动时间：当VALUE值与上一行相比发生变化时，当前行的TIMESTAMP即为备用电源的启动时间（例如数据中VALUE从10837变为10840的时刻）。
• 停止时间：当VALUE值停止变化，且后续连续10行或10分钟内VALUE均无变化时，最后一次VALUE变化对应的TIMESTAMP即为停止时间。

数据示例说明 #

• 首次启动时间为第4行的2023-08-22 19:59:00.123，停止时间为第24行的2023-08-22 20:15:00.013（后续3个值均无变化）。
• 第二次启动时间为第29行的2023-08-23 21:23:00.823，停止时间为第35行的2023-08-23 21:29:00.823。

期望查询结果 #

SQL查询语句 #

以下是基于窗口函数实现的查询方案，适用于支持LAG()、LEAD()和窗口函数的SQL数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）：

WITH change_events AS (
    -- 标记VALUE发生变化的行，以及后续是否满足停止条件
    SELECT
        Sno,
        TIMESTAMP,
        VALUE,
        -- 标记当前行是否是启动事件（VALUE与上一行不同）
        CASE WHEN VALUE != LAG(VALUE) OVER (ORDER BY TIMESTAMP) THEN 1 ELSE 0 END AS is_start,
        -- 检查后续10行内是否有VALUE变化，或后续10分钟内是否有变化
        CASE WHEN EXISTS (
            SELECT 1 
            FROM generator_meter t2
            WHERE t2.TIMESTAMP > t1.TIMESTAMP
              AND t2.TIMESTAMP <= DATE_ADD(t1.TIMESTAMP, INTERVAL 10 MINUTE)
              AND t2.VALUE != t1.VALUE
        ) OR EXISTS (
            SELECT 1 
            FROM generator_meter t2
            WHERE t2.Sno > t1.Sno
              AND t2.Sno <= t1.Sno + 10
              AND t2.VALUE != t1.VALUE
        ) THEN 0 ELSE 1 END AS is_stop_candidate
    FROM generator_meter t1
),
start_stop_groups AS (
    -- 分组识别每个启停周期
    SELECT
        *,
        SUM(is_start) OVER (ORDER BY TIMESTAMP) AS group_id
    FROM change_events
    WHERE is_start = 1 OR is_stop_candidate = 1
)
-- 提取每个组的启动和停止时间
SELECT
    ROW_NUMBER() OVER (ORDER BY MIN(TIMESTAMP)) AS sno,
    MIN(CASE WHEN is_start = 1 THEN TIMESTAMP END) AS `start date time`,
    MAX(CASE WHEN is_stop_candidate = 1 THEN TIMESTAMP END) AS `stop date time`
FROM start_stop_groups
GROUP BY group_id
HAVING `start date time` IS NOT NULL AND `stop date time` IS NOT NULL;

语句说明 #

1. change_events CTE：
   • 使用LAG()函数比较当前行与上一行的VALUE，标记启动事件。
   • 通过子查询检查当前行后续10分钟或10行内是否有VALUE变化，标记潜在的停止候选行。
2. start_stop_groups CTE：
   • 对启动事件进行累加，生成每个启停周期的分组ID，将同一周期的启动和停止行归为一组。
3. 最终查询：
   • 按分组ID聚合，提取每组的启动时间（最早的启动事件）和停止时间（最晚的停止候选行），并生成结果序号。

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