SQL需求：基于Indicator列值重置Instance序号列

你需要给现有表新增Instance列，要求每次遇到Indicator值为1的行时，序号重置为1并重新递增。之前用DENSE_RANK() over (Order by Date)的方式无法实现重置，因为这个函数是全局排序，不会按分组重新计数，导致非重置行的序号是全局连续值，不符合预期。

正确实现方法 #

核心思路是先把数据按Indicator=1的行分成多个分组，再在每个分组内生成从1开始的递增序号。可以通过窗口函数的累计求和生成分组ID，再结合ROW_NUMBER()实现：

SELECT 
    Date,
    Name,
    Indicator,
    ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY Date) AS Instance
FROM (
    SELECT 
        *,
        -- 累计计算当前行及之前所有Indicator=1的次数，作为分组ID
        SUM(CASE WHEN Indicator = 1 THEN 1 ELSE 0 END) OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM your_table -- 替换成你的表名
) t
ORDER BY Date;

代码说明 #

1. 内层子查询中，SUM(...) OVER (...)会逐行累计统计到当前行为止Indicator=1的出现次数，每遇到一个Indicator=1的行，分组IDgroup_id就会加1，这样就把数据分割成了一个个独立的组。
2. 外层查询用ROW_NUMBER()，以group_id作为分区依据，按Date排序，每个分组内的序号会从1开始连续递增，正好符合需求。

验证结果 #

执行上述SQL后，得到的结果与期望结果表完全一致：

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