Snowflake/SQL：如何比较含非确定性转换的两张表数据？

当使用MINUS对比两张表时，遇到像array_unique_agg这类非确定性函数生成的数组列，会因为元素顺序不固定导致内容相同的行被误判为不同。核心解决思路是对这类特殊列做标准化处理，消除顺序差异后再对比，以下是几种可行的实现方式：

1. 标准化数组列后使用MINUS #

在对比查询中，先将数组列的元素排序后重新聚合，确保内容相同的数组生成完全一致的结果。以PostgreSQL为例：

-- 假设table1和table2的结构为：id INT, data_arr TEXT[]
SELECT 
  id,
  -- 将数组元素去重并排序后重新聚合，生成顺序固定的数组
  array_agg(DISTINCT elem ORDER BY elem) AS data_arr
FROM table1, unnest(data_arr) elem
GROUP BY id

MINUS

SELECT 
  id,
  array_agg(DISTINCT elem ORDER BY elem) AS data_arr
FROM table2, unnest(data_arr) elem
GROUP BY id;

如果你的数据库支持直接对数组排序的函数（比如PostgreSQL的array_sort），可以简化为：

SELECT id, array_sort(data_arr) AS data_arr FROM table1
MINUS
SELECT id, array_sort(data_arr) AS data_arr FROM table2;

2. 用EXISTS关联验证逐行匹配 #

这种方式更灵活，适合需要精确找出两张表中互不存在的行的场景，同样针对数组列做标准化处理：

-- 找出table1中存在但table2中不存在的行
SELECT t1.*
FROM table1 t1
WHERE NOT EXISTS (
  SELECT 1
  FROM table2 t2
  WHERE t1.id = t2.id
    -- 对比排序后的数组，消除顺序差异
    AND array_sort(t1.data_arr) = array_sort(t2.data_arr)
    -- 其他列直接等值对比
    AND t1.other_col = t2.other_col
);

UNION ALL

-- 找出table2中存在但table1中不存在的行
SELECT t2.*
FROM table2 t2
WHERE NOT EXISTS (
  SELECT 1
  FROM table1 t1
  WHERE t2.id = t1.id
    AND array_sort(t2.data_arr) = array_sort(t1.data_arr)
    AND t2.other_col = t1.other_col
);

3. 利用数据库原生集合比较能力 #

部分数据库（如Oracle）支持集合类型的直接比较，可以忽略元素顺序。比如Oracle中对嵌套表类型列，可使用SET()函数将其转换为无顺序的集合进行对比：

SELECT id, SET(data_arr) AS data_arr, other_col FROM table1
MINUS
SELECT id, SET(data_arr) AS data_arr, other_col FROM table2;

如果是VARRAY类型，需要先转换为嵌套表再使用SET()函数。

核心原则 #

无论用哪种方法，本质都是消除非确定性列的顺序或随机因素——要么在生成列时就固定元素顺序（比如用带ORDER BY的聚合函数代替非确定性函数），要么在对比时将列转换为内容等价、形式固定的结构，让数据库能正确判定等值。

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