Snowflake中如何按ID分类表数据，忽略卡车并生成产品类别？

需求：遍历表中所有ID，将每个ID分类为Car only、Bike only、both，忽略所有包含truck的条目，最终每个ID对应唯一的产品类别。

表结构 #

期望结果 #

尝试的SQL及问题 #

我尝试使用coalesce和max编写如下SQL：

select  id,  
 coalesce(max(case when name like 'car%' and name like
 'bike%' 
              then 'both' end),
          max(case when name like 'car%' then 'car_only' end),
          max(case when name like 'bike%' then 'bike_only' end)
          )
  from test
  group by 1;

这段SQL存在两个错误：

1. 错误地判断单个产品名称同时包含car和bike（实际不存在此类产品），无法识别同一ID下同时存在car和bike的情况
2. coalesce的逻辑会在找到第一个非空结果后停止后续判断，导致即使ID下同时存在bike，只要有car就会返回car_only，无法得到both的结果

正确的Snowflake实现方案 #

思路 #

1. 先过滤掉所有包含truck的条目
2. 按ID分组，统计每个ID下是否存在car和bike类别的产品
3. 根据统计结果判断每个ID的产品类别

实现SQL #

SELECT 
    id,
    CASE
        WHEN has_car = 1 AND has_bike = 1 THEN 'both'
        WHEN has_car = 1 THEN 'car only'
        WHEN has_bike = 1 THEN 'bike only'
    END AS 产品类别
FROM (
    SELECT 
        id,
        -- 标记当前ID是否存在car类产品
        MAX(CASE WHEN 产品名称 LIKE 'car%' THEN 1 ELSE 0 END) AS has_car,
        -- 标记当前ID是否存在bike类产品
        MAX(CASE WHEN 产品名称 LIKE 'bike%' THEN 1 ELSE 0 END) AS has_bike
    FROM test
    -- 过滤掉所有truck相关条目
    WHERE 产品名称 NOT LIKE 'truck%'
    GROUP BY id
) AS category_stats;

逻辑说明 #

• 子查询中通过MAX(CASE...)分别统计每个ID下是否存在car和bike产品：存在则标记为1，否则为0
• 外层CASE语句根据两个标记的组合值，确定最终的产品类别：
  • 两个标记都为1时，说明ID下同时有car和bike，返回both
  • 仅has_car为1时，返回car only
  • 仅has_bike为1时，返回bike only

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