单SQL结合CDC事务日志生成指定时间点表快照的方法

• 初始销售数据存储在sales表，字段为id、timestamp、product、price，初始共2条记录：
  • id=1：2014-01-01 01:02:03录入，商品phone，售价14.99
  • id=2：2014-01-01 03:02:03录入，商品car，售价1200.00
• 变更日志独立存储在cdc表，字段为type、id、timestamp、product、price，共3条操作记录：
  • 2014-01-01 04:02:03执行DELETE操作，删除id=1的记录
  • 2014-01-02 04:02:03执行APPEND操作，新增id=3、售价799.00的computer记录
  • 同一时间点执行UPDATE操作，将id=3的computer价格更新为805.00
• 需求：通过单条查询（过程化表函数也可接受），在原有仅统计APPEND操作的逻辑基础上兼容UPDATE、DELETE操作，获取截至指定时间戳的最新表状态。

原有仅支持APPEND逻辑的参考查询如下：

-- only takes into account APPENDS
SELECT * FROM sales WHERE timestamp > '2014-02-01 00:00:00'
UNION
SELECT * FROM cdc WHERE type='APPEND' AND timestamp > '2014-02-01 00:00:00'

• 预期校验结果：截至当前时间查询应返回2条记录：id=2的car记录、id=3售价805.00的computer记录，任意数据库方言的实现均可。

核心思路是把初始数据和CDC日志合并为统一的事件流，按主键取最新一次有效操作，过滤掉已删除的记录即可，代码如下：

WITH all_events AS (
    -- 初始数据标记为初始化事件，并入事件流
    SELECT
        'INIT' AS op_type,
        id,
        timestamp,
        product,
        price
    FROM sales
    WHERE timestamp <= ${query_cutoff_timestamp} -- 替换为要查询的截止时间参数

    UNION ALL

    SELECT
        type AS op_type,
        id,
        timestamp,
        product,
        price
    FROM cdc
    WHERE timestamp <= ${query_cutoff_timestamp}
),
sorted_events AS (
    SELECT
        id,
        timestamp,
        product,
        price,
        op_type,
        -- 按主键分组，按操作时间倒序排序；同时间操作按优先级排序：DELETE > UPDATE > APPEND > INIT，避免同时间操作顺序错乱
        ROW_NUMBER() OVER (
            PARTITION BY id
            ORDER BY
                timestamp DESC,
                CASE op_type
                    WHEN 'DELETE' THEN 1
                    WHEN 'UPDATE' THEN 2
                    WHEN 'APPEND' THEN 3
                    ELSE 4
                END
        ) AS event_rank
    FROM all_events
)
-- 取每个主键最新的操作，过滤掉删除操作，剩余即为截止时间点的有效数据
SELECT id, timestamp, product, price
FROM sorted_events
WHERE event_rank = 1
  AND op_type != 'DELETE'

逻辑说明 #

• 合并事件流阶段只筛选截止时间之前的操作，保证结果是指定时间点的快照
• 排序阶段给同时间的不同操作加优先级，解决题目中同时间执行APPEND和UPDATE的顺序判定问题
• 最终过滤掉最新状态为DELETE的主键，自然实现删除逻辑；如果最新操作是UPDATE/APPEND/INIT，直接取对应字段值就是最新状态
• 代入题目中的测试数据执行，返回结果和预期完全一致：id=2的car（1200.00）、id=3的computer（805.00）

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