Snowflake中按ID分组创建二进制标记列HasX的SQL问题

错误原因分析 #

你原SQL中的1 OVER (PARTITION BY ID)属于语法错误，OVER子句必须搭配聚合函数或窗口函数使用，不能直接跟常量值，这才导致了Invalid function '1'的报错。

正确解法 #

方法1：GROUP BY + CASE WHEN（最直观）

通过按ID分组，统计每个ID下是否存在Item 'X'，直接生成标记列：

SELECT 
  ID,
  CASE 
    WHEN COUNT(CASE WHEN Items = 'X' THEN 1 END) > 0 THEN 1 
    ELSE 0 
  END AS HasX
FROM items
GROUP BY ID;

逻辑：内层CASE仅在Item为'X'时返回1，外层通过COUNT统计该ID下'X'的数量，大于0则标记为1，否则为0。

方法2：窗口函数MAX + DISTINCT

利用窗口函数对每个ID范围内的标记值取最大值，再去重得到每个ID的唯一结果：

SELECT 
  DISTINCT ID,
  MAX(CASE WHEN Items = 'X' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS HasX
FROM items;

逻辑：先给每行生成临时标记（有X则1，否则0），再通过MAX()窗口函数在每个ID分组内取最大值（只要有一个X，最大值就是1），最后用DISTINCT去除重复的ID行。

方法3：EXISTS子查询

通过子查询判断当前ID是否存在Item 'X'：

SELECT 
  DISTINCT ID,
  CASE 
    WHEN EXISTS (SELECT 1 FROM items i2 WHERE i2.ID = i.ID AND i2.Items = 'X') 
    THEN 1 
    ELSE 0 
  END AS HasX
FROM items i;

逻辑：对每个ID，检查子查询中是否存在匹配的'X'记录，存在则标记1，否则0，DISTINCT确保每个ID只返回一行。

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