基于RECORD_TYPE逻辑去重ID_NUMBER的SQL实现求助

需求梳理 #

处理数据集的重复ID_NUMBER，规则如下：

• 保留ID_NUMBER唯一的所有记录
• 保留ID_NUMBER重复但组内存在多种RECORD_TYPE的所有记录
• 剔除ID_NUMBER重复且组内RECORD_TYPE完全相同的所有记录

输入与目标示例 #

输入表 #

目标表 #

修正后的SQL代码 #

SELECT t.*
FROM (
    SELECT 
        *,
        -- 统计每个ID组内的总记录数
        COUNT(*) OVER (PARTITION BY ID_NUMBER) AS total_records,
        -- 统计每个ID组内不同RECORD_TYPE的数量
        COUNT(DISTINCT RECORD_TYPE) OVER (PARTITION BY ID_NUMBER) AS distinct_types
    FROM YOUR_TABLE_NAME -- 替换为你的实际表名
) t
-- 筛选符合要求的记录：ID唯一 或 重复ID但存在多种类型
WHERE total_records = 1 OR distinct_types > 1;

代码解释 #

1. 子查询逻辑：通过窗口函数OVER (PARTITION BY ID_NUMBER)对每个ID_NUMBER分组，计算两个核心指标：
   • total_records：标记该ID下的总记录数，用于判断ID是否重复
   • distinct_types：标记该ID下不同RECORD_TYPE的数量，用于判断组内类型是否唯一
2. 外层筛选：通过WHERE子句精准保留目标记录，剔除重复且类型单一的ID组。

原代码问题说明 #

你的原代码存在几处语法与逻辑问题：

• 窗口函数的别名distinct_type声明位置错误，需用AS放在函数后
• 无需提前筛选重复ID，窗口函数可直接处理全表数据，嵌套逻辑过于复杂
• WHERE distinct_type no in ('0')语法错误，数值比较应使用>/<而非IN

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