BigQuery中开展UAT测试时如何对比两张表并输出差异统计

适用场景 #

针对结构完全一致的两张业务表，单套SQL逻辑即可输出全维度差异统计结果，无需零散编写多个独立查询，覆盖行数校验、主键重复校验、主键关联后逐列值比对全流程UAT校验需求。

待比对表结构 #

两张表列名、列顺序完全一致，建表语句如下：

CREATE TABLE `project.mydataset.table_1` (
  `ADDRESS_ID` STRING,
  `ORDER_NO` STRING,
  `START_DATE` STRING,
  `END_DATE` STRING,
  `JOB_DETAILS` STRING,
  `LOAD_DATE` STRING
);

CREATE TABLE `project.mydataset.table_2` (
  `ADDRESS_ID` STRING,
  `ORDER_NO` STRING,
  `START_DATE` STRING,
  `END_DATE` STRING,
  `JOB_DETAILS` STRING,
  `LOAD_DATE` STRING
);

全量比对SQL实现 #

逻辑通过CTE分层实现：先合并两表数据标记来源，再统计单表基础指标，最后通过全外连接按ADDRESS_ID关联统计主键匹配情况和逐列差异，一次性输出所有校验结果。

WITH
-- 合并两表全量数据，标记来源
base_union AS (
  SELECT 
    'table_1' AS source_table,
    ADDRESS_ID, ORDER_NO, START_DATE, END_DATE, JOB_DETAILS, LOAD_DATE
  FROM `project.mydataset.table_1`
  -- 需限定日期范围（如统计2022-04-01当日数据）时放开下方注释即可
  -- WHERE DATE(LOAD_DATE) = '2022-04-01'
  UNION ALL
  SELECT 
    'table_2' AS source_table,
    ADDRESS_ID, ORDER_NO, START_DATE, END_DATE, JOB_DETAILS, LOAD_DATE
  FROM `project.mydataset.table_2`
  -- WHERE DATE(LOAD_DATE) = '2022-04-01'
),
-- 统计单表基础指标：总行数、ADDRESS_ID重复行数
table_base_stats AS (
  SELECT
    source_table,
    COUNT(1) AS total_row_count,
    COUNT(1) - COUNT(DISTINCT ADDRESS_ID) AS address_id_duplicate_count
  FROM base_union
  GROUP BY source_table
),
-- 按ADDRESS_ID全外连接，统计主键匹配情况、逐列值差异
key_compare_stats AS (
  SELECT
    COUNTIF(t1.ADDRESS_ID IS NOT NULL AND t2.ADDRESS_ID IS NOT NULL) AS address_id_both_exist_cnt,
    COUNTIF(t1.ADDRESS_ID IS NOT NULL AND t2.ADDRESS_ID IS NULL) AS address_id_only_in_t1_cnt,
    COUNTIF(t1.ADDRESS_ID IS NULL AND t2.ADDRESS_ID IS NOT NULL) AS address_id_only_in_t2_cnt,
    COUNTIF(t1.ORDER_NO != t2.ORDER_NO) AS order_no_mismatch_cnt,
    COUNTIF(t1.START_DATE != t2.START_DATE) AS start_date_mismatch_cnt,
    COUNTIF(t1.END_DATE != t2.END_DATE) AS end_date_mismatch_cnt,
    COUNTIF(t1.JOB_DETAILS != t2.JOB_DETAILS) AS job_details_mismatch_cnt,
    COUNTIF(t1.LOAD_DATE != t2.LOAD_DATE) AS load_date_mismatch_cnt
  FROM (SELECT * FROM base_union WHERE source_table = 'table_1') t1
  FULL OUTER JOIN (SELECT * FROM base_union WHERE source_table = 'table_2') t2
  ON t1.ADDRESS_ID = t2.ADDRESS_ID
)
-- 聚合输出所有校验项结果
SELECT
  'table_1总行数' AS stat_item,
  CAST((SELECT total_row_count FROM table_base_stats WHERE source_table = 'table_1') AS STRING) AS stat_value
UNION ALL SELECT 'table_2总行数', CAST((SELECT total_row_count FROM table_base_stats WHERE source_table = 'table_2') AS STRING)
UNION ALL SELECT 'table_1 ADDRESS_ID重复行数', CAST((SELECT address_id_duplicate_count FROM table_base_stats WHERE source_table = 'table_1') AS STRING)
UNION ALL SELECT 'table_2 ADDRESS_ID重复行数', CAST((SELECT address_id_duplicate_count FROM table_base_stats WHERE source_table = 'table_2') AS STRING)
UNION ALL SELECT '两表共有的ADDRESS_ID数量', CAST(address_id_both_exist_cnt AS STRING) FROM key_compare_stats
UNION ALL SELECT '仅在table_1存在的ADDRESS_ID数量', CAST(address_id_only_in_t1_cnt AS STRING) FROM key_compare_stats
UNION ALL SELECT '仅在table_2存在的ADDRESS_ID数量', CAST(address_id_only_in_t2_cnt AS STRING) FROM key_compare_stats
UNION ALL SELECT '共有主键下ORDER_NO字段不一致行数', CAST(order_no_mismatch_cnt AS STRING) FROM key_compare_stats
UNION ALL SELECT '共有主键下START_DATE字段不一致行数', CAST(start_date_mismatch_cnt AS STRING) FROM key_compare_stats
UNION ALL SELECT '共有主键下END_DATE字段不一致行数', CAST(end_date_mismatch_cnt AS STRING) FROM key_compare_stats
UNION ALL SELECT '共有主键下JOB_DETAILS字段不一致行数', CAST(job_details_mismatch_cnt AS STRING) FROM key_compare_stats
UNION ALL SELECT '共有主键下LOAD_DATE字段不一致行数', CAST(load_date_mismatch_cnt AS STRING) FROM key_compare_stats
;

使用注意事项 #

• 输出结果为stat_item（校验项名称）、stat_value（对应统计值）两列，直接覆盖已有的行数统计、ADDRESS_ID重复值统计需求，同时补充主键匹配、逐列差异的统计维度。
• 若需要排查具体差异明细，可移除key_compare_stats层的聚合逻辑，增加WHERE条件筛选字段值不相等的行即可导出明细数据。
• 因当前两表均存在ADDRESS_ID重复值，全外连接阶段可能出现笛卡尔积放大统计结果的情况，建议先根据重复值统计结果排查重复ADDRESS_ID对应的业务逻辑，确认是否需要去重后再做逐行关联比对。

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