在 bigquery 中比较两个表的有效方法

Question

我有兴趣比较两个表是否包含相同的数据。

我可以这样做：

 #standardSQL
SELECT
    key1, key2
FROM
(
    SELECT
    table1.key1,
    table1.key2,
    table1.column1 - table2.column1 as col1,
    table1.col2 - table2.col2 as col2
    FROM
        `table1` AS table1
    LEFT JOIN
        `table2` AS table2
    ON
        table1.key1 = table2.key1
    AND
        table1.key2 = table2.key2
)
WHERE
    col1 != 0
OR
    col2 != 0

但是当我想比较所有数值列时，这有点困难，特别是如果我想对多个表组合进行比较。

因此我的问题是：是否有人知道有可能迭代所有数字列并将结果集限制为那些差异不为零的键？

原文由 Nico Albers 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

首先，我想提出您原始查询的问题

主要问题是 1) 使用 LEFT JOIN ； 2) 使用 col != 0

下面是如何修改它以真正捕获两个表的所有差异

运行您的原始查询并低于一个 - 希望您会看到不同之处

#standardSQL
SELECT key1, key2
FROM
(
    SELECT
    IFNULL(table1.key1, table2.key1) key1,
    IFNULL(table1.key2, table2.key2) key2,
    table1.column1 - table2.column1 AS col1,
    table1.col2 - table2.col2 AS col2
    FROM `table1` AS table1
    FULL OUTER JOIN `table2` AS table2
    ON table1.key1 = table2.key1
    AND table1.key2 = table2.key2
)
WHERE IFNULL(col1, 1) != 0
OR    IFNULL(col2, 1) != 0

或者您可以尝试针对虚拟数据运行原始版本和更高版本以查看差异

#standardSQL
WITH `table1` AS (
  SELECT 1 key1, 1 key2, 1 column1, 2 col2 UNION ALL
  SELECT 2, 2, 3, 4 UNION ALL
  SELECT 3, 3, 5, 6
), `table2` AS (
  SELECT 1 key1, 1 key2, 1 column1, 29 col2 UNION ALL
  SELECT 2, 2, 3, 4 UNION ALL
  SELECT 4, 4, 7, 8
)
SELECT key1, key2
FROM
(
    SELECT
    IFNULL(table1.key1, table2.key1) key1,
    IFNULL(table1.key2, table2.key2) key2,
    table1.column1 - table2.column1 AS col1,
    table1.col2 - table2.col2 AS col2
    FROM `table1` AS table1
    FULL OUTER JOIN `table2` AS table2
    ON table1.key1 = table2.key1
    AND table1.key2 = table2.key2
)
WHERE IFNULL(col1, 1) != 0
OR    IFNULL(col2, 1) != 0

其次，下面将高度简化您的整体查询

#standardSQL
SELECT
  IFNULL(table1.key1, table2.key1) key1,
  IFNULL(table1.key2, table2.key2) key2
FROM `table1` AS table1
FULL OUTER JOIN `table2` AS table2
ON table1.key1 = table2.key1
AND table1.key2 = table2.key2
WHERE TO_JSON_STRING(table1) != TO_JSON_STRING(table2)

您可以使用与上述相同的虚拟数据示例对其进行测试

注意：在此解决方案中，您不需要选择特定列 - 它只是比较所有列！但是如果您只需要比较特定的列 - 您仍然需要像下面的示例一样挑选它们

#standardSQL
SELECT
  IFNULL(table1.key1, table2.key1) key1,
  IFNULL(table1.key2, table2.key2) key2
FROM `table1` AS table1
FULL OUTER JOIN `table2` AS table2
ON table1.key1 = table2.key1
AND table1.key2 = table2.key2
WHERE TO_JSON_STRING((table1.column1, table1.col2)) != TO_JSON_STRING((table2.column1, table2.col2))

原文由 Mikhail Berlyant 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

在标准 SQL 中，我们发现使用 UNION ALL 两个 EXCEPT DISTINCT 对我们的用例起作用：

 (
  SELECT * FROM table1
  EXCEPT DISTINCT
  SELECT * from table2
)

UNION ALL

(
  SELECT * FROM table2
  EXCEPT DISTINCT
  SELECT * from table1
)

这将产生两个方向的差异：

• table1 中的行不在 table2
• table2 中的行不在 table1

注意事项和注意事项：

• table1 和 table2 必须具有相同的宽度并且具有相同顺序和类型的列。
• 这不适用于 STRUCT 或 ARRAY 数据类型。您应该 UNNEST 或使用 TO_JSON_STRING 首先转换这些数据类型。
• 这也不能直接与 GEOGRAPHY 一起使用，您必须先使用 ST_AsText 转换为文本

原文由 Jordan Arseno 发布，翻译遵循 CC BY-SA 4.0 许可协议