如何用PARTITION BY从重复数据中选取信息最完整的行

嘿，这个需求太典型了！咱们要解决的核心就是给每个ID分组里的行按“字段非空数量”打分，然后挑出得分最高的那一行，得分相同的话任选其一就行。下面我给你一步步拆解实现方法：

核心思路 #

1. 先给每一行计算「完整度得分」：统计该行里非空字段的数量，字段值越多得分越高；
2. 用窗口函数按ID分区，对每个分区内的行按完整度得分降序排序；
3. 筛选出每个分区里排名第一的行（得分最高的），如果有多个行得分相同，随机或按指定规则选其一。

具体SQL示例 #

假设你的表叫user_info，字段包括id, name, city, zip_code, email（你可以根据实际字段调整），具体代码如下：

WITH ranked_user_rows AS (
    SELECT 
        *,
        -- 计算每行的完整度得分：每个非空字段加1
        CASE WHEN name IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN city IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN zip_code IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN email IS NOT NULL THEN 1 ELSE 0 END AS completeness_score,
        -- 按ID分区，先按得分降序，得分相同则随机排序（确保任选其一）
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY completeness_score DESC, RAND()
        ) AS row_rank
    FROM user_info
)
-- 只保留每个ID里排名第一的行，去掉计算用的临时字段
SELECT id, name, city, zip_code, email
FROM ranked_user_rows
WHERE row_rank = 1;

关键细节说明 #

• 完整度得分计算：你需要把所有要统计的字段都加到CASE语句里，比如如果还有phone字段，就再加一行CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END；
• 排序规则调整：如果不想随机选得分相同的行，比如想选最新创建的，可以把RAND()换成create_time DESC（假设你有创建时间字段）；
• 多得分行保留：如果允许同一个ID下所有得分最高的行都保留（比如Sean的第二、第三行都要），把ROW_NUMBER()换成RANK()即可——RANK()会给相同得分的行相同排名，筛选row_rank=1时会把这些行都选出来；
• 数据库适配：不同数据库的随机函数不一样，比如SQL Server用NEWID()，Oracle用DBMS_RANDOM.VALUE()，替换掉RAND()就行。

对应你的需求场景 #

• Joe的第二行有城市和邮编，得分比第一行高，会被选中；
• Pete的第二行信息更全，得分更高，自然被选；
• Max两行得分相同，会随机选其中一行；
• Sean的第二、第三行得分相同，按代码里的随机规则二选一，或者你换成时间字段固定选某一行。

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