Postgresql 通过窗口函数收集反连接结果

好的，在快照中报告is的完成状态，即具有new id的快照在前一快照中不存在.id removed，即不在next快照中，您不需要anti-join.您无论如何都不会在您的示例中使用它.

首先使用integers定义快照序列并标记当前快照

with snap as (
select distinct c_date_called from t_table),
snap2 as (
select  
  c_date_called,
  row_number() over(order by c_date_called) as snap_idx,
  c_date_called = max(c_date_called) over() is_current
from snap)
select * from snap2;

c_date_called|snap_idx|is_current|
-------------+--------+----------+
            9|       1|false     |
           12|       2|false     |
           17|       3|true      |

然后将此支持信息连接到您的主表，并基于snapshot index的lag和lead窗口函数添加两个属性，这两个属性标识给定id的上一个/下一个快照是连续的还是有差距.其中的逻辑应该不言而喻.

with snap as (
select distinct c_date_called from t_table),
snap2 as (
select  
  c_date_called,
  row_number() over(order by c_date_called) as snap_idx,
  c_date_called = max(c_date_called) over() is_current
from snap)
select 
 a.c_id, a.c_date_called, b.snap_idx, b.is_current,
 snap_idx != (1 + lag(snap_idx,1,0) over(partition by a.c_id order by snap_idx)) is_new,
 not (is_current) and snap_idx + 1 !=lead(snap_idx,1,0) over(partition by a.c_id order by snap_idx) is_removed
from t_table a
join snap2 b on a.c_date_called = b.c_date_called
order by 1,3;

c_id|c_date_called|snap_idx|is_current|is_new|is_removed|
----+-------------+--------+----------+------+----------+
   1|            9|       1|false     |false |false     |
   1|           12|       2|false     |false |false     |
   1|           17|       3|true      |false |false     |
   2|            9|       1|false     |false |false     |
   2|           12|       2|false     |false |true      |
   3|            9|       1|false     |false |false     |
   3|           12|       2|false     |false |false     |
   3|           17|       3|true      |false |false     |
   4|            9|       1|false     |false |true      |
   5|            9|       1|false     |false |false     |
   5|           12|       2|false     |false |true      |
   6|           12|       2|false     |true  |false     |
   6|           17|       3|true      |false |false     |

请注意，我添加了在第二个快照中介绍的id 6以演示此用例.