SQL基于行历史值添加计算字段获取X字段连续1的起始日期

这个需求属于SQL经典的孤岛（Island）问题，核心是先给连续的1序列划分到同一个分组，再取每个分组的最小日期即可，不需要像Excel一样逐行引用上一行的计算结果。

实现步骤 #

• 第一步：对所有行按日期升序排序，每当遇到X为0/空值时就生成一个新的分组标识，连续的1会被划入同一个分组
• 第二步：对每个分组，当X=1时取该分组的最小日期，否则返回空值

代码示例 #

假设你的表名为dataset，日期字段为dt（需为日期类型，不要存储为字符串），二进制字段为x，代码如下：

WITH group_mark AS (
    SELECT 
        dt,
        x,
        -- 生成连续1的分组标识
        SUM(CASE WHEN COALESCE(x, 0) = 0 THEN 1 ELSE 0 END) OVER (ORDER BY dt ASC) AS group_id
    FROM dataset
)
SELECT 
    dt,
    x,
    CASE WHEN COALESCE(x, 0) = 1 THEN MIN(dt) OVER (PARTITION BY group_id) ELSE NULL END AS `desired field (calculated)`
FROM group_mark
ORDER BY dt ASC;

逻辑说明 #

• COALESCE(x, 0) 是将X字段的空值转换为0，避免空值判断异常
• 第一层CTE生成的group_id会将连续的1划入同一个ID分组，遇到0/空值时分组ID+1
• 第二层用窗口函数取每个分组的最小日期，就是当前连续1序列的首个起始日期

适配说明 #

• 如果你的数据需要按其他维度拆分（比如不同用户、不同产品单独计算连续序列），只需要在两个窗口函数的OVER子句里加上PARTITION BY 维度字段即可，比如按用户id拆分就写成OVER (PARTITION BY user_id ORDER BY dt ASC)
• 该代码支持所有支持标准SQL窗口函数的数据库，包括MySQL 8.0+、PostgreSQL、SQL Server、Hive、Spark SQL等

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