如何使用AWS Athena（SQL/Presto）对比同表两行并返回差异

需要对比同一张表中两行数据的data1和data2列：

• 若两行的data1和data2内容完全一致，查询返回空结果
• 若存在差异，返回这两列中的差异内容

以如下输入表为例，行1的adminaccess=false与行2的adminaccess=true存在差异，data2中行1的groups=['updated']与行2的groups=[]存在差异，需得到指定的差异结果。

输入表 #

期望输出 #

由于data1和data2为JSON结构，需借助数据库的JSON处理函数提取差异。以下以PostgreSQL为例提供实现方案：

1. 标记区分两行数据 #

假设表名为your_table，先通过排序给两行数据分配唯一标识：

WITH ranked_rows AS (
    SELECT 
        data1::jsonb, 
        data2::jsonb,
        ROW_NUMBER() OVER (ORDER BY createddate) AS rn
    FROM your_table
)

2. 提取差异并返回结果 #

通过JSON键值对比，筛选出两行中不一致的字段，重新组合为JSON返回：

SELECT
    -- 提取data1的差异：保留行1与行2值不同的键值对
    (SELECT jsonb_object_agg(key, value)
     FROM jsonb_each(r1.data1)
     WHERE r1.data1 -> key != r2.data1 -> key) AS data1,
    -- 提取data2的差异：保留行1与行2值不同的键值对
    (SELECT jsonb_object_agg(key, value)
     FROM jsonb_each(r1.data2)
     WHERE r1.data2 -> key != r2.data2 -> key) AS data2
FROM ranked_rows r1
JOIN ranked_rows r2 ON r1.rn = 1 AND r2.rn = 2
-- 仅当data1或data2存在差异时返回结果
WHERE r1.data1 != r2.data1 OR r1.data2 != r2.data2;

其他数据库适配说明 #

• MySQL：替换为JSON_TABLE、JSON_KEYS等对应JSON处理函数
• BigQuery：使用JSON_EXTRACT、STRUCT相关函数实现类似逻辑
• 若两行的data1和data2完全一致，WHERE条件会过滤结果，返回空集

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