PostgreSQL表规范化：缺失数据补全与去重问题求助

我正在处理一份CSV格式的员工表，表中各列存在零散无规律的缺失数据，无法通过单一列分组合并，导致大量数据丢失。之前尝试用ChatGPT解决但没得到有效方案，试了下面的自连接SQL语句后问题反而更糟，求有效解决方案：

select distinct
COALESCE(t1.staff_id, t2.staff_id) AS staff_id,
COALESCE(t1.staff_age, t2.staff_age) AS staff_age,
COALESCE(t1.staff_first_name, t2.staff_first_name) AS staff_first_name,
COALESCE(t1.staff_last_name, t2.staff_last_name) AS staff_last_name,
COALESCE(t1.staff_lang, t2.staff_lang) AS staff_lang
from corrected_orders_data_staff t1
join corrected_orders_data_staff t2 on
(
(t1.staff_id = t2.staff_id and t1.staff_age = t2.staff_age) or
(t1.staff_id = t2.staff_id and t1.staff_first_name = t2.staff_first_name and t1.staff_last_name = t2.staff_last_name) or
(t1.staff_id = t2.staff_id and t1.staff_lang = t2.staff_lang) or
(t1.staff_age = t2.staff_age and t1.staff_first_name = t2.staff_first_name and t1.staff_last_name = t2.staff_last_name) or
(t1.staff_age = t2.staff_age and t1.staff_lang = t2.staff_lang) or
(t1.staff_first_name = t2.staff_first_name and t1.staff_last_name = t2.staff_last_name and t1.staff_lang = t2.staff_lang) --or
)
order by 1,2,3,4,5
;

相关员工表数据 #

解决方案 #

原SQL问题分析 #

你的自连接SQL使用了过多宽泛的OR匹配条件，导致不同员工的行也会被错误关联（比如年龄相同的不同员工），生成大量冗余甚至错误的合并结果，反而加剧了数据混乱。

方案一：基于特征分组合并（适配当前数据规律） #

观察数据可以发现，每个员工的信息分散在多行，但存在明确的关联特征：

• 有staff_id的行，同一staff_id属于同一个员工
• 无staff_id的行，staff_first_name+staff_last_name+staff_age+staff_lang的组合唯一对应一个员工

基于此，我们可以先为每条记录生成唯一组ID，再按组聚合合并非空数据：

WITH staff_groups AS (
    -- 生成组ID：优先用staff_id，无则用姓名+年龄+语言的拼接串
    SELECT 
        *,
        COALESCE(
            staff_id,
            CONCAT(staff_first_name, '|', staff_last_name, '|', staff_age, '|', staff_lang)
        ) AS group_id
    FROM corrected_orders_data_staff
),
merged_staff AS (
    -- 按组聚合，取各列非空值（MAX忽略NULL）
    SELECT 
        MAX(staff_id) AS staff_id,
        MAX(staff_age) AS staff_age,
        MAX(staff_first_name) AS staff_first_name,
        MAX(staff_last_name) AS staff_last_name,
        MAX(staff_lang) AS staff_lang
    FROM staff_groups
    GROUP BY group_id
)
SELECT * FROM merged_staff ORDER BY staff_id, staff_age;

该方案能快速将同一员工的分散数据合并为一行完整记录，且不会产生冗余。

方案二：递归CTE合并（通用无规则缺失场景） #

如果数据缺失更无规律（比如同一员工的不同行仅部分字段重叠），可以用递归CTE逐步关联合并：

WITH RECURSIVE staff_merged AS (
    -- 基准行：取所有字段完整的记录作为起点
    SELECT * FROM corrected_orders_data_staff
    WHERE staff_id IS NOT NULL 
      AND staff_age IS NOT NULL 
      AND staff_first_name IS NOT NULL 
      AND staff_last_name IS NOT NULL 
      AND staff_lang IS NOT NULL
    UNION ALL
    -- 递归关联：将已有合并记录与其他部分匹配的行合并非空字段
    SELECT 
        COALESCE(m.staff_id, s.staff_id) AS staff_id,
        COALESCE(m.staff_age, s.staff_age) AS staff_age,
        COALESCE(m.staff_first_name, s.staff_first_name) AS staff_first_name,
        COALESCE(m.staff_last_name, s.staff_last_name) AS staff_last_name,
        COALESCE(m.staff_lang, s.staff_lang) AS staff_lang
    FROM staff_merged m
    JOIN corrected_orders_data_staff s ON 
        (m.staff_id = s.staff_id AND m.staff_id IS NOT NULL) OR
        (m.staff_first_name = s.staff_first_name AND m.staff_last_name = s.staff_last_name AND m.staff_first_name IS NOT NULL) OR
        (m.staff_age = s.staff_age AND m.staff_lang = s.staff_lang AND m.staff_age IS NOT NULL)
    -- 排除完全相同的行，避免无限递归
    WHERE NOT (
        m.staff_id = s.staff_id 
        AND m.staff_age = s.staff_age 
        AND m.staff_first_name = s.staff_first_name 
        AND m.staff_last_name = s.staff_last_name 
        AND m.staff_lang = s.staff_lang
    )
),
final_staff AS (
    -- 按员工唯一特征分组，取各列完整值
    SELECT DISTINCT
        MAX(staff_id) OVER (PARTITION BY COALESCE(staff_id, CONCAT(staff_first_name, '|', staff_last_name))) AS staff_id,
        MAX(staff_age) OVER (PARTITION BY COALESCE(staff_id, CONCAT(staff_first_name, '|', staff_last_name))) AS staff_age,
        MAX(staff_first_name) OVER (PARTITION BY COALESCE(staff_id, CONCAT(staff_first_name, '|', staff_last_name))) AS staff_first_name,
        MAX(staff_last_name) OVER (PARTITION BY COALESCE(staff_id, CONCAT(staff_first_name, '|', staff_last_name))) AS staff_last_name,
        MAX(staff_lang) OVER (PARTITION BY COALESCE(staff_id, CONCAT(staff_first_name, '|', staff_last_name))) AS staff_lang
    FROM staff_merged
)
SELECT DISTINCT * FROM final_staff ORDER BY staff_id;

该方案通过递归逐步合并关联数据，适配更复杂的缺失场景，但性能略低于方案一。

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