Hive中基于复合键统计两表差异及解决关联查询问题

咱们先拆解下你遇到的问题，再一步步给出靠谱的解决办法～

你的SQL语句存在的问题 #

你写的左连接查询逻辑有漏洞：

SELECT count(*) FROM table_x tx LEFT JOIN table_m tm ON tx.key_col_a = tm.key_col_a AND tx.key_col_b = tm.key_col_b WHERE tm.key_col_a IS NULL OR tm.key_col_b IS NULL;

• 当左连接没有匹配到M中的记录时，tm的所有字段都会是NULL，包括两个复合键。但你用了OR条件，会误把M中存在但其中一个键为NULL的记录（虽然复合键通常不会允许这种情况，但语法上如果表结构没限制的话）也统计进来，导致结果不准确。
• 这个查询只能统计“不存在于M”的记录数，没法一次性拿到你需要的所有统计值（存在数、更新/插入数）。

解决方案：一次性统计所有需要的指标 #

我们可以用CASE WHEN配合聚合函数，在一个查询里算出所有你要的数值：

SELECT
  -- 表X的总记录数
  COUNT(*) AS total_x_records,
  -- X中存在于M的记录数（即需要更新的数量）
  SUM(CASE WHEN tm.key_col_a IS NOT NULL THEN 1 ELSE 0 END) AS exists_in_m_count,
  -- X中不存在于M的记录数（即需要插入的数量）
  SUM(CASE WHEN tm.key_col_a IS NULL THEN 1 ELSE 0 END) AS not_exists_in_m_count,
  -- 需要更新的记录数（和exists_in_m_count一致，匹配上的就是要更新的）
  SUM(CASE WHEN tm.key_col_a IS NOT NULL THEN 1 ELSE 0 END) AS need_update_count,
  -- 需要插入的记录数（和not_exists_in_m_count一致）
  SUM(CASE WHEN tm.key_col_a IS NULL THEN 1 ELSE 0 END) AS need_insert_count
FROM table_x tx
LEFT JOIN table_m tm
  ON tx.key_col_a = tm.key_col_a 
  AND tx.key_col_b = tm.key_col_b;

进阶：统计唯一复合键（而非重复记录） #

如果你的表X里有重复的复合键记录，而你想统计的是「有多少个唯一的键存在/不存在于M」，而非总记录数，可以先对X去重：

WITH unique_x_keys AS (
  SELECT DISTINCT key_col_a, key_col_b FROM table_x
)
SELECT
  COUNT(*) AS unique_x_key_count,
  SUM(CASE WHEN tm.key_col_a IS NOT NULL THEN 1 ELSE 0 END) AS exists_in_m_key_count,
  SUM(CASE WHEN tm.key_col_a IS NULL THEN 1 ELSE 0 END) AS not_exists_in_m_key_count,
  SUM(CASE WHEN tm.key_col_a IS NOT NULL THEN 1 ELSE 0 END) AS need_update_key_count,
  SUM(CASE WHEN tm.key_col_a IS NULL THEN 1 ELSE 0 END) AS need_insert_key_count
FROM unique_x_keys tx
LEFT JOIN table_m tm
  ON tx.key_col_a = tm.key_col_a 
  AND tx.key_col_b = tm.key_col_b;

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