SQL使用UNION时CTE写法与直接查询结果不一致原因排查

需统计两类场景下的用户平均搜索次数：

• 场景1：用户搜索后成功完成预订
• 场景2：用户搜索后未产生预订

输出要求包含两列：

• 第一列列名为action，取值为books和does not book
• 第二列列名为average_searches，存储对应场景的平均搜索次数

判定规则：

• ts_booking_at（预订时间）为null则视为未完成预订
• 仅当搜索记录与预订记录的ds_checkin（入住日期）匹配时，才判定二者关联

编写了两个SQL实现方案，二者执行返回结果存在明显差异，无法定位差异点，两个方案及对应执行结果如下：

方案1（CTE写法）

with c1 as(
    select b.id_user, b.n_searches  
    from airbnb_contacts a
    join airbnb_searches b
    on a.ds_checkin = b.ds_checkin and a.id_guest = b.id_user
    where ts_booking_at is not null),
c2 as(
    select b.id_user, b.n_searches  
    from airbnb_contacts a
    join airbnb_searches b
    on a.ds_checkin = b.ds_checkin and a.id_guest = b.id_user
    where ts_booking_at isnull
)
select 'books' as action, avg(c1.n_searches) 
from c1
union 
select 'does not book' as action, avg(c2.n_searches) 
from c2

执行输出：

books           23.333
does not book   28.383

方案2（直接查询写法）

select
    'does not book' as action,
    avg(n_searches) as average_number_of_search
from
    airbnb_searches as searches,
    airbnb_contacts as contacts
where
    contacts.ts_booking_at isnull and
    contacts.id_guest = searches.id_user

union

select
    'books' as action,
    avg(n_searches) as average_number_of_search
from
    airbnb_searches as searches,
    airbnb_contacts as contacts
where
    contacts.ts_booking_at is not null and
    contacts.id_guest = searches.id_user

执行输出：

books                 20.133
does not book         23.578

两个SQL返回结果不一致的核心问题出在方案2的关联逻辑不符合需求规则，且存在笛卡尔积导致的重复计算：

• 关联条件缺项：方案1的JOIN逻辑完全符合需求规则，关联搜索表和预订表时同时校验两个条件：一是用户ID匹配（a.id_guest = b.id_user），二是入住日期匹配（a.ds_checkin = b.ds_checkin）。方案2用逗号做隐式内连时，WHERE子句只加了用户ID匹配的条件，完全漏掉了入住日期必须匹配的强制要求，会把同一用户下所有入住日期不对应的搜索、预订记录强行绑定，从根上就不符合业务判定逻辑。
• 重复计数导致均值失真：由于缺少日期关联条件，方案2会产生大量无意义的笛卡尔积行。举个例子：某用户有2条不同入住日期的预订记录、3条不同入住日期的搜索记录，只要用户ID一致就会被匹配成2*3=6条记录，同一条搜索的n_searches值会被重复算多次，最终算出来的平均值自然和方案1的结果存在明显差距。

补充说明：两个方案本身都存在逻辑疏漏——均未覆盖「用户产生了搜索行为但完全没有对应contacts记录」的场景，但这不是二者结果出现差异的原因。

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