在AWS Athena中批量对比两表多列并生成对比结果列的优化方案

问题背景 #

现有两张表t1和t2，需按id关联后对比近30列（如gender、age），每列需生成对应的对比结果列（如gender_compare）。当前仅能写出基础关联SQL，但手动编写30个CASE语句过于繁琐：

SELECT t1.id ,t2.id, 
t1.gender, t2.gender,
t1.age, t2.age
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id

优化方案 #

方案1：UNPIVOT + PIVOT 批量处理 #

通过列转行对比再转回列的方式，避免重复编写CASE：

1. 将两张表的目标列转为列名-值的键值对（UNPIVOT）
2. 关联后对比值，生成结果标记
3. 再将结果转回列（PIVOT）

示例SQL：

WITH t1_unpivoted AS (
    SELECT id, column_name, column_value
    FROM table1
    UNPIVOT (
        column_value FOR column_name IN (gender, age, /* 列出所有需对比的30列 */)
    ) AS unpvt
),
t2_unpivoted AS (
    SELECT id, column_name, column_value
    FROM table2
    UNPIVOT (
        column_value FOR column_name IN (gender, age, /* 与t1对应列一致 */)
    ) AS unpvt
),
comparison AS (
    SELECT 
        t1.id,
        t1.column_name,
        CASE 
            WHEN t1.column_value = t2.column_value THEN '一致'
            ELSE '不一致'
        END AS compare_result
    FROM t1_unpivoted t1
    JOIN t2_unpivoted t2 ON t1.id = t2.id AND t1.column_name = t2.column_name
)
SELECT 
    id,
    gender AS gender_compare,
    age AS age_compare,
    /* 其余对比列依次类推 */
FROM comparison
PIVOT (
    MAX(compare_result) FOR column_name IN ('gender' AS gender, 'age' AS age, /* 与前面列一致 */)
) AS pvt

方案2：Map函数批量生成对比结果 #

利用Athena的Map相关函数，将目标列转为Map后批量对比，可直接生成结果Map或提取单个列：

生成对比结果Map

SELECT 
    t1.id,
    transform_values(
        map_from_entries(ARRAY[
            ('gender', t1.gender),
            ('age', t1.age),
            /* 列出所有需对比列 */
        ]),
        (k, v) -> CASE 
            WHEN v = map_from_entries(ARRAY[
                ('gender', t2.gender),
                ('age', t2.age),
                /* 对应t2的列 */
            ])[k] THEN '一致' 
            ELSE '不一致' 
        END
    ) AS all_compare_results
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id

提取单个对比列

如果需要单独的结果列，可结合element_at提取Map中的值：

SELECT 
    id,
    element_at(all_compare_results, 'gender') AS gender_compare,
    element_at(all_compare_results, 'age') AS age_compare,
    /* 其余列依次提取 */
FROM (
    SELECT 
        t1.id,
        transform_values(
            map_from_entries(ARRAY[('gender', t1.gender), ('age', t1.age)]),
            (k, v) -> CASE WHEN v = map_from_entries(ARRAY[('gender', t2.gender), ('age', t2.age)])[k] THEN '一致' ELSE '不一致' END
        ) AS all_compare_results
    FROM table1 t1
    JOIN table2 t2 ON t1.id = t2.id
) t

进阶技巧 #

如果两张表的目标列名完全一致，可通过查询元数据动态获取列列表，减少手动输入：

SELECT column_name 
FROM information_schema.columns 
WHERE table_name = 'table1' 
  AND column_name NOT IN ('id') -- 排除关联键

将查询结果复制到UNPIVOT或Map的ARRAY中即可。

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