如何通过窗口函数实现跨多数据快照的Anti-Join结果汇总？

现有表结构与测试数据 #

存储不同数据快照的表定义：

CREATE TABLE t_table (
    c_id int4 NOT NULL,
    c_date_called int4 NOT NULL,
    CONSTRAINT t_table_un UNIQUE (c_id, c_date_called)
);

插入的测试快照数据：

INSERT INTO t_table (c_id, c_date_called) 
   VALUES
(1,9),
(2,9),
(3,9),
(4,9),
(5,9),
(1,12),
(2,12),
(3,12),
(5,12),
(1,17),
(3,17)
;

原有单快照对比方式 #

此前通过Left Anti-Join对比单个历史快照（如c_date_called=9）与当前快照（c_date_called=17），查询当前快照中不存在的ID：

SELECT prev.*
    FROM t_table AS prev
    LEFT JOIN t_table AS cur ON (prev.c_id = cur.c_id) AND cur.c_date_called = 17
    WHERE prev.c_date_called = 9
        AND cur.c_id IS NULL;

查询结果：

c_id|c_date_called|
----+-------------+
   2|            9|
   4|            9|

多快照对比的窗口函数解决方案 #

要一次性汇总所有历史快照与当前快照的对比结果，可通过窗口函数标记每个ID是否存在于当前快照，再筛选出符合条件的记录：

方案1：用MAX()窗口函数标记存在性 #

SELECT t.c_id, t.c_date_called
FROM (
    SELECT 
        *,
        -- 按c_id分组，标记该ID是否在当前快照（17）中存在
        MAX(CASE WHEN c_date_called = 17 THEN 1 ELSE 0 END) OVER (PARTITION BY c_id) AS exists_in_current
    FROM t_table
) t
-- 筛选非当前快照的记录，且该ID在当前快照中不存在
WHERE t.c_date_called != 17 
  AND t.exists_in_current = 0
ORDER BY t.c_date_called, t.c_id;

方案2：用COUNT()窗口函数标记存在性 #

也可以用COUNT()替代MAX()，逻辑一致：

SELECT t.c_id, t.c_date_called
FROM (
    SELECT 
        *,
        -- 统计该ID在当前快照中的出现次数（0代表不存在）
        COUNT(CASE WHEN c_date_called = 17 THEN 1 END) OVER (PARTITION BY c_id) AS current_count
    FROM t_table
) t
WHERE t.c_date_called != 17 
  AND t.current_count = 0
ORDER BY t.c_date_called, t.c_id;

查询结果 #

以上两个方案都会返回所有历史快照中，在当前快照（17）里消失的ID记录：

c_id|c_date_called|
----+-------------+
   2|            9|
   4|            9|
   2|           12|
   5|           12|

方案说明 #

• 窗口函数按c_id分组计算，一次扫描表即可完成所有ID的存在性标记，避免了多次表关联，性能更优。
• 只需修改c_date_called = 17中的日期值，即可切换不同的当前快照进行对比。

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