Snowflake/SQL实现分组编号：为每组分配统一递增ID

需求说明 #

• 以dog行作为分组起始，第一个dog分配ID=1
• 后续非dog行沿用当前分组的ID
• 每遇到一个新的dog行，ID递增1

现有Alteryx多行公式逻辑 #

IF [ROW-1:Value] IS NULL AND [Value]='dog' THEN 1
ELSEIF [ROW-1:Value] IS NULL AND [Value]!='dog' THEN NULL()
ELSEIF [Value] = [ROW-1:Value] AND [Value]='dog' THEN [Row-1:ID] + 1
ELSE [ROW-1:ID]
ENDIF

SQL简便实现方案 #

不需要繁琐的自连接偏移，用窗口函数累加计数即可实现需求，这是最简洁的方法：

基础实现（标准SQL） #

假设你的表包含Value列，且有明确的排序字段（比如行号row_num，SQL是无序的，必须指定排序依据才能保证分组顺序正确）：

SELECT
  Value,
  -- 每次遇到'dog'就累加1，非'dog'行保持当前累加值
  SUM(CASE WHEN Value = 'dog' THEN 1 ELSE 0 END) OVER (
    ORDER BY row_num 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS ID
FROM your_table;

适配首行非dog的场景 #

如果首行不是dog，按照Alteryx逻辑要返回NULL，可以嵌套CASE处理：

SELECT
  Value,
  CASE
    -- 若累计计数为0（说明还没遇到第一个'dog'），返回NULL
    WHEN dog_count = 0 THEN NULL
    ELSE dog_count
  END AS ID
FROM (
  SELECT
    Value,
    SUM(CASE WHEN Value = 'dog' THEN 1 ELSE 0 END) OVER (
      ORDER BY row_num 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS dog_count
  FROM your_table
) t;

关于CONDITIONAL_CHANGE_EVENT的说明 #

这个函数是在值发生变化时生成递增编号，但你的需求是每遇到一个dog就递增ID（包括连续的dog行），而CONDITIONAL_CHANGE_EVENT遇到连续相同的dog不会触发编号递增，因此无法满足需求。

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