带不同过滤条件的两表列对比异常及优化问询

问题背景 #

我写了一段用于对比不同表中列的SQL代码，未添加WHERE子句/过滤条件时运行基本正常，但添加过滤条件后会出现多余的非目标行。

现有SQL代码 #

with source1 as (
select 
b.id, 
b.qty, 
a.price 
from <table> as a
,unnest <details> as b
 where b.status != 'canceled'
),

source2 as (
select id_, qty_, price_  from <table2>
where city != 'delhi'
) 

select *
from source1 s1
full outer join source2 s2
on id = id_
where format('%t', s1) != format('%t', s2)

样本数据 #

source1（原始表数据） #

id  qty   price   status
1   100 (null)    canceled
2   0     100       done
3   0      80       canceled
4   50     90       done
5   20    100       done
6   20    100       done
7   80     80       done
8   100   100       canceled
9   40     0        done
10  11     22       done
11  40     40       done
12  null   90       done

source2（原始表数据） #

id_ qty_    price_  city_
1   100     200     ny
2   0       100     ny
3   0        80     ny
4   50       80     ny
5   40      100     ny
6   40       40     ny
7   200     200  delhi
8   100     100  delhi
9   40      100     ny
10  11       22  delhi
12  11       11     ny
13  90       80     NY

预期结果 #

id  qty    price    status       id_    qty_    price_  city_
4   50        90    done          4       50    80      ny
5   20       100    done          5       40    100     ny
6   20       100    done          6       40    40      ny
9   40         0    done          9       40    100     ny
11  40        40    done       null     null    null    null
12  null      90    done         12       11    11      ny
null null   null    null         13       90    80      ny

需求说明 #

• 仅展示符合以下条件的行：至少有一个核心列（qty、price、status）不匹配，且满足status!='canceled'或city!='delhi'，同时展示两表对应列值；
• 若某行仅在一个表中存在且符合过滤条件，需展示该行；
• 若符合过滤条件且核心列值均匹配，则不展示该行。

当前问题 #

• source1的过滤条件会排除自身取消状态行，但source2对应行会被错误展示；
• source2的过滤条件同理，会导致source1对应行被错误展示；
• 若纳入status、city列，会因两表列不一致，导致format('%t')误判差异。

问询问题 #

1. 是否可指定特定列传入format('%t',s2)序列化，排除status、city列？
2. 如何适配未来可能的多过滤条件场景？
3. 如何基于现有format('%t')方式得到预期输出？

解决方案 #

问题1：指定特定列传入format函数 #

完全可以，不需要序列化整个行，只需把需要对比的核心列构造成元组传入format函数即可。比如针对source1的qty、price、status和source2的qty_、price_（source2无status列），可以这样写：

format('%t', (s1.qty, s1.price, s1.status)) != format('%t', (s2.qty_, s2.price_))

注意要保证两边元组的列数量、类型对应，避免序列化后出现无效对比。

问题2：适配多过滤条件场景 #

不要在CTE的WHERE子句中提前过滤数据，而是将过滤条件放到FULL JOIN后的最终逻辑里。这样能保留两表全量数据，再结合过滤条件判断是否展示，后续新增过滤条件只需在最终WHERE中补充逻辑即可：

1. CTE仅做数据提取，不做过滤：

with source1 as (
select 
b.id, 
b.qty, 
a.price,
b.status
from <table> as a
,unnest <details> as b
),
source2 as (
select id_, qty_, price_, city_ from <table2>
)

2. 在最终查询中用逻辑表达式整合所有过滤条件，避免提前过滤导致的单边数据丢失问题。

问题3：基于现有format方式得到预期输出 #

结合前两点，调整后的完整SQL如下：

with source1 as (
select 
b.id, 
b.qty, 
a.price,
b.status
from <table> as a
,unnest <details> as b
),
source2 as (
select id_, qty_, price_, city_ from <table2>
) 
select 
    s1.id, s1.qty, s1.price, s1.status,
    s2.id_, s2.qty_, s2.price_, s2.city_
from source1 s1
full outer join source2 s2
on s1.id = s2.id_
-- 先过滤符合基础条件的行
where (s1.status != 'canceled' or s2.city_ != 'delhi')
-- 再判断是否需要展示：核心列不匹配，或单边存在且符合条件
and (
    format('%t', (s1.qty, s1.price, s1.status)) != format('%t', (s2.qty_, s2.price_))
    or (s1.id is not null and s2.id_ is null and s1.status != 'canceled')
    or (s2.id_ is not null and s1.id is null and s2.city_ != 'delhi')
)

这段代码的逻辑：

• CTE保留全量数据，不提前过滤；
• 先筛选出符合status!='canceled'或city!='delhi'的行；
• 再判断核心列是否不匹配，或单边存在且符合条件，最终得到预期结果。

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