Snowflake SQL中基于字符串规则筛选每个ID对应唯一行的方案

问题场景 #

现有Snowflake环境下的数据表，结构及示例数据如下：

ID      Group                 Timestamp   Data
001         A   2021-04-13 12:51:12.063   content121
001  A-Direct   2021-04-13 12:52:13.063   content121
002  A-Direct   2021-04-13 12:50:14.063   content133
003  B-Direct   2021-04-13 12:55:12.063   content132
003         B   2021-04-13 12:56:11.063   content142
003        BA   2021-04-13 12:57:22.063   content153
004         D   2021-04-13 12:10:23.063   content113
004         C   2021-04-13 12:11:43.063   content144
005         C   2021-04-13 12:12:12.063   content111
005         A   2021-04-13 12:13:23.063   content100
005  D-Direct   2021-04-13 12:15:23.063   content121
006         A   2021-04-13 12:51:12.063   content121
006  B-Direct   2021-04-13 12:52:13.063   content121
007  A-Direct   2021-04-13 12:51:12.063   content121
007         A   2021-04-13 12:52:13.063   content121
008  B-Direct   2021-04-13 12:55:12.063   content132
008         B   2021-04-13 12:56:11.063   content142
008  B-Direct   2021-04-13 12:57:22.063   content153
009  B-Direct   2021-04-13 12:55:12.063   content132
009  C-Direct   2021-04-13 12:56:11.063   content142
009  D-Direct   2021-04-13 12:57:22.063   content153

筛选规则 #

每个ID仅保留唯一一行，优先级如下：

• 若同一ID下存在前缀匹配的两组Group值，其中一组后缀为-Direct、另一组无该后缀，优先选择不带-Direct的条目；不存在匹配前缀的无后缀条目时才保留带-Direct的条目
• 其他默认情况优先选取Timestamp字段最新的条目

预期输出 #

ID      Group
001         A
002  A-Direct
003        BA
004         C
005  D-Direct
006  B-Direct
007         A
008         B
009  D-Direct

实现方案 #

核心逻辑是通过窗口函数自定义排序规则，优先排无-Direct后缀的条目，再按时间倒序排序，取每个ID排名第一的条目即可。

完整SQL代码 #

WITH process_data AS (
    SELECT 
        *,
        -- 提取Group前缀：带-Direct则去掉后缀，否则保留原值
        CASE WHEN ENDSWITH(Group, '-Direct') 
             THEN REPLACE(Group, '-Direct', '') 
             ELSE Group 
        END AS group_prefix,
        -- 优先级标记：不带-Direct的条目优先级更高，值越小排序越靠前
        CASE WHEN NOT ENDSWITH(Group, '-Direct') 
             THEN 1 
             ELSE 2 
        END AS priority
    FROM your_table_name -- 替换为实际表名
),
rank_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY ID 
            ORDER BY priority ASC, Timestamp DESC
        ) AS final_rn
    FROM process_data
)
SELECT ID, Group
FROM rank_data
WHERE final_rn = 1
ORDER BY ID;

代码说明 #

• ENDSWITH是Snowflake内置的字符串判断函数，用于识别-Direct后缀，兼容性和性能更优
• 排序逻辑完全匹配需求规则：先按优先级（无后缀优先）排序，同优先级按时间倒序取最新条目
• 最终通过final_rn = 1过滤得到每个ID的唯一结果，和预期输出完全匹配

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