编写Presto SQL实现两张表数据对账并返回差异记录

需求说明 #

• 仅返回两类对账差异记录：
  • 参考ID仅存在于Table A或仅存在于Table B
  • 参考ID在两张表都存在，但对应Value值不一致

示例数据 #

Table A #

Table B #

期望输出 #

Presto SQL 实现 #

SELECT
    COALESCE(a.ref_id, b.ref_id) AS ref_id,
    a.value AS a_value,
    b.value AS b_value,
    CASE
        WHEN a.ref_id IS NULL THEN '仅存在于Table B'
        WHEN b.ref_id IS NULL THEN '仅存在于Table A'
        ELSE '值不一致'
    END AS diff_type
FROM
    table_a a
FULL OUTER JOIN
    table_b b ON a.ref_id = b.ref_id
WHERE
    -- 过滤掉ID存在且值一致的正常记录，只留差异
    a.ref_id IS NULL
    OR b.ref_id IS NULL
    OR a.value <> b.value;

逻辑说明 #

• 用FULL OUTER JOIN关联两张表，确保所有参考ID都被覆盖，不会遗漏单边存在的ID
• COALESCE函数用来统一获取参考ID，不管ID来自Table A还是Table B
• CASE分支直接判断差异类型，明确标注是单边缺失ID还是两边值不匹配
• WHERE条件直接排除掉ID同时存在且值一致的正常记录，只返回异常数据

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