如何在BigQuery/SQL中对比两个结构一致时间不同的表提取差异记录

实现方案 #

基础方案：返回curr中全字段与prev不匹配的所有行（全数据库兼容） #

如果你不需要过滤同一用户的历史订阅记录，仅需要找出所有curr中存在、prev中完全不存在的行，直接用NOT EXISTS全字段匹配即可，逻辑和EXCEPT一致但兼容性更强，也能解决部分数据库EXCEPT返回结果不符合预期的问题：

SELECT *
FROM curr c
WHERE NOT EXISTS (
    SELECT 1
    FROM prev p
    WHERE 
        c.primary_email = p.primary_email
        AND c.start_date = p.start_date
        AND c.status = p.status
        AND c.cancellation_type = p.cancellation_type
        AND c.amount = p.amount
        AND c.frequency = p.frequency
        AND c.bundle = p.bundle
)

优化方案：仅返回每个用户最新的变更记录 #

如果你需要排除同一用户的历史旧订阅，仅对比当前最新的激活订阅的差异，可以配合窗口函数先过滤每个用户的最新记录再做对比：

WITH curr_latest AS (
    SELECT *
    FROM (
        SELECT 
            *,
            -- 按用户分组，订阅开始时间倒序排序，最新的记录排序为1
            ROW_NUMBER() OVER (PARTITION BY primary_email ORDER BY start_date DESC) AS rn
        FROM curr
    ) t
    WHERE rn = 1 -- 仅保留每个用户最新的一条订阅
),
prev_latest AS (
    SELECT *
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY primary_email ORDER BY start_date DESC) AS rn
        FROM prev
    ) t
    WHERE rn = 1
)
-- 取curr最新记录中与prev最新记录不匹配的行
SELECT cl.primary_email, cl.start_date, cl.status, cl.cancellation_type, cl.amount, cl.frequency, cl.bundle
FROM curr_latest cl
LEFT JOIN prev_latest pl
    ON cl.primary_email = pl.primary_email
WHERE 
    pl.primary_email IS NULL -- 新用户新增订阅
    OR cl.start_date <> pl.start_date
    OR cl.status <> pl.status
    OR cl.cancellation_type <> pl.cancellation_type
    OR cl.amount <> pl.amount
    OR cl.frequency <> pl.frequency
    OR cl.bundle <> pl.bundle;

说明 #

• 如果你表中有更准确的排序字段（如记录更新时间、订阅生效时间），可以替换窗口函数中ORDER BY start_date DESC的逻辑，保证取到的是用户当前激活的订阅记录。
• 优化方案中可自由增减WHERE后的字段对比逻辑，不需要对比的字段直接删除对应判断条件即可。

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