SQLite3中统计连续相同flag值的序列信息

核心方案（基于窗口函数，SQLite3 3.25.0+） #

利用窗口函数生成分组标识，将连续相同flag的记录归为同一组，再通过聚合得到所需结果：

SELECT
  MIN(date) AS start_date,
  MAX(date) AS end_date,
  flag AS status,
  COUNT(*) AS length
FROM (
  SELECT
    date,
    flag,
    -- 计算分组标识：全局行号 - 按flag分组的行号，连续相同flag的组差值一致
    ROW_NUMBER() OVER (ORDER BY date) - ROW_NUMBER() OVER (PARTITION BY flag ORDER BY date) AS group_id
  FROM your_table -- 替换为你的表名
) AS grouped_records
GROUP BY group_id, flag
ORDER BY start_date;

方案原理 #

• ROW_NUMBER() OVER (ORDER BY date)：生成按日期排序的全局行号，确保记录按时间顺序排列。
• ROW_NUMBER() OVER (PARTITION BY flag ORDER BY date)：按flag分组后生成组内行号，同一flag的连续记录行号连续递增。
• 两者的差值group_id：连续相同flag的记录，全局行号和组内行号的增长速度一致，差值保持不变；当flag切换时，组内行号重置，差值变化，形成新的分组标识。
• 最后按group_id和flag分组聚合，得到每个连续序列的起始/结束日期、状态和长度。

优化方案（若id与日期顺序一致） #

如果表中id是自增且与date顺序完全匹配，可直接用id替代全局行号，提升查询效率：

SELECT
  MIN(date) AS start_date,
  MAX(date) AS end_date,
  flag AS status,
  COUNT(*) AS length
FROM (
  SELECT
    date,
    flag,
    id - ROW_NUMBER() OVER (PARTITION BY flag ORDER BY id) AS group_id
  FROM your_table
) AS grouped_records
GROUP BY group_id, flag
ORDER BY start_date;

兼容旧版本SQLite3（无窗口函数支持） #

若使用低于3.25.0的SQLite3版本，可采用自连接方式实现（效率略低）：

SELECT
  MIN(t1.date) AS start_date,
  MAX(t1.date) AS end_date,
  t1.flag AS status,
  COUNT(*) AS length
FROM your_table t1
LEFT JOIN your_table t2 
  ON t2.date < t1.date AND t2.flag != t1.flag
GROUP BY t1.flag, (SELECT MAX(date) FROM your_table WHERE date < t1.date AND flag != t1.flag)
ORDER BY start_date;

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