如何基于相同OVID拆分数据集列为各来源独立列实现字段比对

• 待处理数据集为ALL_SOURCES_MERGE，共包含7个字段：OVID、ID_VALUE、NAME、SOURCE、LOCATION、MLP_ID、TYPE
• 数据存在一对多关联关系：单个OVID可对应多条不同SOURCE来源的记录
• 处理目标：按OVID维度聚合数据，将不同来源对应的各字段拆分为独立列，列名遵循[来源标识]_[原字段名]规则（例：SOURCE_1_ID、SOURCE_2_NAME），无对应来源数据的位置填充NULL，用于快速比对同一OVID下不同来源的字段取值差异
• 现存问题：直接使用透视（pivoting）操作时，输出表结构符合预期，但所有拆分后的来源字段取值全为NULL，无法得到正确结果

• 未提前对OVID+SOURCE组合做唯一性校验，同组存在多条重复记录时，绝大多数数据处理框架的透视逻辑无法自动判定取值优先级，直接返回空值
• 透视配置时仅指定了列名生成规则，未明确绑定取值字段与聚合逻辑，框架无法匹配到对应列的取值来源，默认填充NULL

方案1：SQL实现（适配Hive/Spark SQL/MySQL 8.0+等主流引擎） #

优先用CASE WHEN手动聚合的写法代替原生PIVOT函数，兼容性更强，不会出现全空值问题：

SELECT
    OVID,
    -- 以下以SOURCE取值为1、2举例，实际使用时替换为你所有的SOURCE枚举值即可
    MAX(CASE WHEN SOURCE = '1' THEN ID_VALUE END) AS SOURCE_1_ID,
    MAX(CASE WHEN SOURCE = '1' THEN NAME END) AS SOURCE_1_NAME,
    MAX(CASE WHEN SOURCE = '1' THEN LOCATION END) AS SOURCE_1_LOCATION,
    MAX(CASE WHEN SOURCE = '1' THEN MLP_ID END) AS SOURCE_1_MLP_ID,
    MAX(CASE WHEN SOURCE = '1' THEN TYPE END) AS SOURCE_1_TYPE,
    MAX(CASE WHEN SOURCE = '2' THEN ID_VALUE END) AS SOURCE_2_ID,
    MAX(CASE WHEN SOURCE = '2' THEN NAME END) AS SOURCE_2_NAME,
    MAX(CASE WHEN SOURCE = '2' THEN LOCATION END) AS SOURCE_2_LOCATION,
    MAX(CASE WHEN SOURCE = '2' THEN MLP_ID END) AS SOURCE_2_MLP_ID,
    MAX(CASE WHEN SOURCE = '2' THEN TYPE END) AS SOURCE_2_TYPE
FROM ALL_SOURCES_MERGE
GROUP BY OVID;

如果SOURCE取值是动态变化的，可以先查询全量去重后的SOURCE枚举值，再动态拼接上述SQL片段即可，灵活度远高于原生PIVOT语法。

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

先做去重再做轴旋转，从根源避免索引冲突导致的空值问题：

import pandas as pd

# 按你实际的数据存储格式调整读取逻辑
df = pd.read_csv("ALL_SOURCES_MERGE.csv")

# 第一步：去重，保证每个OVID+SOURCE组合仅保留1条记录，避免一对多冲突
df_dedup = df.drop_duplicates(subset=["OVID", "SOURCE"], keep="first")

# 第二步：设置双层索引后按SOURCE维度拆列
df_pivot = df_dedup.set_index(["OVID", "SOURCE"]).unstack("SOURCE")

# 第三步：按规则重命名列，ID_VALUE字段对应简写为ID，和示例命名规则对齐
df_pivot.columns = [f"SOURCE_{source}_ID" if col == "ID_VALUE" else f"SOURCE_{source}_{col}" for col, source in df_pivot.columns]

# 第四步：重置索引得到最终结果，缺失值自动填充为NaN（对应SQL的NULL）
df_result = df_pivot.reset_index()

内容的提问来源于stack exchange，提问作者Pauli du Plooy