Sql 在每个实例之后计算具有冷却期的事件

在Postgres数据库中，我有与id相关联的"事件"条目，以及它们发生的时间.我需要用一条特殊的规则来数一数.

当事件发生时，计数器递增，并且在接下来的14天内，不会对此类型的所有事件进行计数.

示例:

在Excel中，我只需添加两列.在一篇专栏文章中，我延续了一个"封杀日期"，这个日期指的是必须封杀事件的日期.在另一栏中，我将ID与先前的ID和先前的"阻止日期"进行了比较.

当ID不同或区块日期早于当前日期时，我必须计算.当我必须计算时，我将行的阻止日期设置为当前日期+14天，否则我将延续上一个阻止日期.

我现在试着在波斯格雷斯解决这个问题...

• 窗口函数
• 递归CTE
• 侧向连接

...一切似乎都有些希望，但最终我未能实现这一棘手的计算.

例如，我的递归CTE失败，错误为:

在WHERE中不允许使用聚合函数

with recursive event_count AS (
        select event
             , min(created_at) as created
        from test
        group by event
        
        union all
        
        ( select event
               , created_at as created
          from test
          join event_count
          using(event)
          where created_at >= max(created) + INTERVAL '14 days'
          order by created_at
          limit 1
        )
)
select * from event_count

使用lag()访问前一行的窗口函数似乎不起作用，因为它们不能访问使用窗口函数创建的前一行中的列.

在输入新的事件条目时，通过简单地与上一个条目进行比较来添加"块或计数"信息并不能解决这个问题，因为事件条目大约半年后就会"消失".因此，当第一个条目消失时，下一个条目就成为第一个条目，而逻辑必须应用于新的情况.

上述测试数据可通过以下方式创建:

CREATE TABLE test ( 
  event      INTEGER, 
  created_at TIMESTAMP
);

INSERT INTO test (event, created_at) VALUES
(16, '2021-11-11 11:15'),(16, '2021-11-11 11:15'),(16, '2021-11-13 10:45'),(16, '2021-11-16 10:40'),
(16, '2021-11-23 11:15'),(16, '2021-11-23 11:15'),(16, '2021-12-10 13:00'),(16, '2021-12-15 13:25'),
(16, '2021-12-15 13:25'),(16, '2021-12-15 13:25'),(16, '2021-12-20 13:15'),(16, '2021-12-23 13:15'),
(16, '2021-12-31 13:25'),(16, '2022-02-05 15:00'),(16, '2022-02-05 15:00'),(16, '2022-02-13 17:15'),
(16, '2022-02-21 10:09'),
(43, '2021-11-26 11:00'),(43, '2022-01-01 15:00'),(43, '2022-04-13 10:07'),(43, '2022-04-13 10:09'),
(43, '2022-04-13 10:09'),(43, '2022-04-13 10:09'),(43, '2022-04-13 10:10'),(43, '2022-04-13 10:10'),
(43, '2022-04-13 10:47'),(43, '2022-05-11 20:25'),
(75, '2021-10-21 12:50'),(75, '2021-11-02 12:50'),(75, '2021-11-18 11:15'),(75, '2021-11-18 12:55'),
(75, '2021-11-18 16:35'),(75, '2021-11-24 11:00'),(75, '2021-12-01 11:00'),(75, '2021-12-14 13:25'),
(75, '2021-12-15 13:35'),(75, '2021-12-26 13:25'),(75, '2022-01-31 15:00'),(75, '2022-02-02 15:30'),
(75, '2022-02-03 15:00'),(75, '2022-02-17 15:00'),(75, '2022-02-17 15:00'),(75, '2022-02-18 15:00'),
(75, '2022-02-23 15:00'),(75, '2022-02-25 15:00'),(75, '2022-03-04 10:46'),(75, '2022-03-08 21:05');