SQL技术问询：基于层级规则处理重复数据

问题背景 #

数据表中存在同一标识（如Key/CUSTNUMBER）对应多类别重复记录，需按预设优先级层级（最多17个类别，高优先级覆盖低优先级）保留最高优先级的唯一记录。例如：

• Key=1A同时有VIP、Retail类别时，保留VIP，删除Retail
• CUSTNUMBER=100有X、Y、Z类别时，保留X，删除Y、Z

之前采用给类别赋值（如VIP=100、Retail=1）后分组求和判断的方式，逻辑冗余且维护成本高，需要更简洁易维护的实现方案。

核心实现思路 #

核心是先明确类别优先级映射关系，再通过「分组+按优先级排序取顶」的方式实现去重，这种方式逻辑直观，后续新增/调整优先级只需修改映射表，无需改动核心逻辑。

场景1：SQL实现（数据库端处理） #

步骤1：定义优先级映射

用CTE（公共表达式）或临时表存储优先级，数值越大代表层级越高：

WITH class_priority AS (
    SELECT 'VIP' AS class, 100 AS priority
    UNION ALL SELECT 'Retail', 1
    -- 补充其余15个类别的优先级，按实际层级赋值
    UNION ALL SELECT 'X', 3
    UNION ALL SELECT 'Y', 2
    UNION ALL SELECT 'Z', 1
)

步骤2：关联原表并去重

使用窗口函数ROW_NUMBER()按标识分组，按优先级降序排序，取每组第一条（最高优先级记录）：

SELECT t.*
FROM (
    SELECT 
        original.*,
        ROW_NUMBER() OVER (
            PARTITION BY original.Key  -- 按需替换为CUSTNUMBER
            ORDER BY cp.priority DESC
        ) AS rn
    FROM your_table original
    LEFT JOIN class_priority cp ON original.class = cp.class
) t
WHERE t.rn = 1;

场景2：Python Pandas实现（本地数据处理） #

步骤1：定义优先级字典

class_priority = {
    'VIP': 100,
    'Retail': 1,
    'X': 3,
    'Y': 2,
    'Z': 1
    # 补充其余类别
}

步骤2：映射优先级并分组取顶

import pandas as pd

# 加载数据
df = pd.read_csv('your_data.csv')

# 新增优先级列
df['priority'] = df['CLASS'].map(class_priority)

# 按标识分组，保留每组优先级最高的记录
# keep='first'确保同优先级时保留第一条（按需调整）
result_df = df.sort_values('priority', ascending=False).drop_duplicates(subset='CUSTNUMBER', keep='first')

# 可选：删除临时优先级列
result_df = result_df.drop(columns='priority')

优势对比 #

• 逻辑直观：相比赋值求和的方式，一眼就能看出是按优先级取顶的核心逻辑
• 维护成本低：新增/调整类别优先级时，只需修改映射表/字典，无需改动分组逻辑
• 扩展性强：支持任意数量的类别层级（不管是17个还是更多）

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