如何用SQL获取时序数据中值≥12的连续时段的最早与最晚时间？

需求：从readings表中获取每一批连续的、value≥12的时序数据对应的最早（min(date)）和最晚（max(date)）时间，预期结果如下：

min                       max
2023-02-22 10:00:00       2023-02-22 10:20:00
2023-02-22 11:00:00       2023-02-22 11:20:00

表结构与测试数据 #

-- 创建表
CREATE TABLE readings (
  id INTEGER PRIMARY KEY,
  date timestamp NOT NULL,
  value int NOT NULL
);

-- 插入测试数据
INSERT INTO readings VALUES (1, '2023-02-22 10:00:00', 12);
INSERT INTO readings VALUES (2, '2023-02-22 10:10:00', 13);
INSERT INTO readings VALUES (3, '2023-02-22 10:20:00', 15);
INSERT INTO readings VALUES (4, '2023-02-22 10:30:00', 11);
INSERT INTO readings VALUES (5, '2023-02-22 10:40:00', 10);
INSERT INTO readings VALUES (6, '2023-02-22 10:50:00', 11);
INSERT INTO readings VALUES (7, '2023-02-22 11:00:00', 12);
INSERT INTO readings VALUES (8, '2023-02-22 11:10:00', 14);
INSERT INTO readings VALUES (9, '2023-02-22 11:20:00', 13);
INSERT INTO readings VALUES (10, '2023-02-22 11:30:00', 8);

原SQLSELECT min(date), max(date) FROM readings WHERE VALUE >= 12 group by date无法得到预期结果，因为GROUP BY date会将每一条记录单独分组，无法识别连续的批次。

纯SQL解决方案 #

这是典型的连续区间分组问题，可通过窗口函数生成分组标识，实现对连续符合条件的记录进行分组统计：

WITH all_rows AS (
    -- 给所有记录按时间排序分配全局行号
    SELECT 
        date,
        value,
        ROW_NUMBER() OVER (ORDER BY date) AS rn1
    FROM readings
),
qualified_rows AS (
    -- 筛选出value≥12的记录，再按时间排序分配行号
    SELECT 
        date,
        rn1,
        ROW_NUMBER() OVER (ORDER BY date) AS rn2
    FROM all_rows
    WHERE value >= 12
)
-- 通过全局行号与筛选后行号的差值作为分组ID，统计每个批次的时间范围
SELECT 
    MIN(date) AS min,
    MAX(date) AS max
FROM qualified_rows
GROUP BY (rn1 - rn2)
ORDER BY min;

原理说明 #

1. all_rows CTE：给所有记录按date升序分配全局行号rn1，确保所有记录按时间顺序排列。
2. qualified_rows CTE：筛选出value≥12的记录，再给这些记录按date升序分配行号rn2。
3. 分组统计：当中间存在value<12的记录时，rn1会持续递增但rn2不会，因此rn1 - rn2的差值会发生变化，同一连续批次的记录会拥有相同的差值，以此作为分组依据即可统计出每个批次的最早和最晚时间。

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