如何在Snowflake中基于列值按预设规则筛选有效记录

你的核心问题是原有的ROW_NUMBER排序逻辑无法保证_INTERNAL类型的记录优先保留——直接按SOURCESYSTEM升序排序时，_EXTERNAL的字典序可能排在_INTERNAL前面（比如HR_EXTERNAL的E在HR_INTERNAL的I之前），导致错误保留_EXTERNAL记录。

要实现需求，需要调整分区和排序规则，确保同一员工+同一来源前缀的分组内，优先保留_INTERNAL记录：

具体SQL实现 #

WITH ranked_records AS (
    SELECT 
        *,
        -- 按员工ID+来源前缀分区，排序时优先_INTERNAL类型
        ROW_NUMBER() OVER(
            PARTITION BY EMPID, SPLIT_PART(SOURCESYSTEM, '_', 1)
            ORDER BY CASE WHEN SOURCESYSTEM LIKE '%_INTERNAL' THEN 1 ELSE 2 END ASC
        ) AS RANK_GID
    FROM 你的表名
)
-- 筛选每个分组中排名第一的记录
SELECT EMPID, SOURCESYSTEM, NAME, DEPT
FROM ranked_records
WHERE RANK_GID = 1;

逻辑说明 #

1. 分区规则：PARTITION BY EMPID, SPLIT_PART(SOURCESYSTEM, '_', 1)
   用SPLIT_PART提取来源系统的前缀（比如HR、SALES），确保同一员工+同一前缀的记录被分到同一分组，避免不同前缀的记录互相覆盖。

2. 排序规则：CASE WHEN SOURCESYSTEM LIKE '%_INTERNAL' THEN 1 ELSE 2 END ASC
   给_INTERNAL类型的记录设置更高的排序优先级（值越小越靠前），这样每个分组中_INTERNAL记录会被标记为RANK_GID=1；如果没有_INTERNAL记录，_EXTERNAL记录会成为RANK_GID=1被保留。

3. 过滤逻辑：WHERE RANK_GID = 1
   只保留每个分组中排名第一的记录，完全匹配你的需求。

兼容其他SQL方言 #

如果你的数据库不支持SPLIT_PART（比如SQL Server、MySQL），可以用以下方式提取前缀：

-- SQL Server/MySQL 写法
SUBSTRING(SOURCESYSTEM, 1, CHARINDEX('_', SOURCESYSTEM) - 1)

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