SQL统计连续出现的1的数量：非连续ID场景下的实现方案

这个场景我之前处理过好多次，核心思路就是用窗口函数给连续的记录打上分组标记，再聚合出每个序列的起止ID就行。我给你一步步拆解：

假设你的表名叫your_table，包含两个关键字段：ID（非连续的数值型字段）和status（标记是否为1的字段）。

步骤1：筛选有效数据 #

先把所有status=1的记录单独拎出来，排除其他状态的干扰，避免影响连续序列的判断：

WITH filtered_data AS (
    SELECT ID
    FROM your_table
    WHERE status = 1
)

步骤2：给连续ID打分组标签 #

接下来用LAG()窗口函数获取当前行的前一个ID，计算两者的差值。如果差值不等于1，说明当前行是一个新序列的起点。我们用累积求和生成分组ID，让连续的ID归到同一个组：

, grouped_data AS (
    SELECT 
        ID,
        -- 当当前ID和前一个ID不连续时，标记为新分组起点，累积求和得到组ID
        SUM(CASE WHEN ID - LAG(ID, 1, ID-1) OVER (ORDER BY ID) != 1 THEN 1 ELSE 0 END) 
            OVER (ORDER BY ID) AS sequence_group
    FROM filtered_data
)

这里LAG(ID, 1, ID-1)的第三个参数是默认值，专门处理第一行没有前序ID的情况，保证第一行如果是1的话，不会被误判为新分组。

步骤3：聚合得到序列起止 #

最后按分组ID聚合，取每个组的最小和最大ID，就是你要的连续序列：

SELECT 
    MIN(ID) AS start_id,
    MAX(ID) AS end_id
FROM grouped_data
GROUP BY sequence_group
ORDER BY start_id;

补充说明 #

如果你的“连续出现的1”是指status连续为1但ID不一定连续（比如ID跳号但status一直是1也算一个序列），那只要把判断逻辑改成对比前一行的status就行：

SUM(CASE WHEN status != LAG(status, 1, 0) OVER (ORDER BY ID) THEN 1 ELSE 0 END) 
    OVER (ORDER BY ID) AS sequence_group

不过看你的例子是ID连续的区间，所以第一种方法更贴合你的需求。

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