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

Question

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

当事件发生时，计数器将递增，并且在接下来的14天内，此类型的所有事件都不会被计数。

示例：

操作计数区块区块区块区块区块24.12.2124.12.2124.12.2124.12.2124.12.2124.12.21计数计数19.02.2219.02.2207.03.22计数计数计数块块块块块块计数计数块计数块块块块计数块块计数块块计数块块块块计数区块

事件	created_at	块日期
16	2021-11:15	25.11.21
16	2021-11-11:15	25.11.21
16	2021-11-13 10:45	25.11.21
16	2021-11-16 10:40	25.11.21
16	2021-11-23 11:15	25.11.21
16	2021-11-23 11:15	25.11.21
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	14.01.22
16	2022-02-0515:00	19.02.22
16	2022-02-0515:00	区块
16	2022-02-13 17:15	区块
16	2022-02-21 10:09	计数
43	2021-11-26 11:00	10.12.21
43	2022-01-01 15:00	15.01.22
43	2022-04-13 10:07	27.04.22
43	2022-04-13 10:09	27.04.22
43	2022-04-13 10:09	27.04.22
43	2022-04-13 10:09	27.04.22
43	2022-04-13 10:10	27.04.22
43	2022-04-13 10:10	27.04.22
43	2022-04-13 10:47	27.04.22
43	2022-05-11 20:25	25.05.22
75	2021-10-21 12:50	04.11.21
75	2021-11-02 12:50	04.11.21
75	2021-11-18 11:15	02.12.21
75	2021-11-18 12:55	02.12.21
75	2021-11-18 16:35	02.12.21
75	2021-11-24 11:00	02.12.21
75	2021-12-01 11:00	02.12.21
75	2021-12-14 13:25	28.12.21
75	2021-12-15 13:35	28.12.21
75	2021-12-26 13:25	28.12.21
75	2022-01-31 15:00	14.02.22
75	2022-02-02 15:30	14.02.22
75	2022-02-03 15:00	14.02.22
75	2022-02-17 15:00	03.03.22
75	2022-02-17 15:00	03.03.22
75	2022-02-18 15:00	03.03.22
75	2022-02-23 15:00	03.03.22
75	2022-02-25 15:00	03.03.22
75	2022-03-04 10:46	18.03.22
75	2022-03-08 21:05	18.03.22

这有助于过程化解决方案，因为它必须遍历每个事件的现有行的整个历史。但是SQL也可以做到。

最佳解决方案在很大程度上取决于基数、数据分布和其他情况
假设不利条件：

• 大桌子
• 相关事件(事件ID)的编号和标识未知
• 每个事件有许多行
• 有些与14天的时间框架重叠，有些则不然
• 任何数量的重复都可能

您需要一个索引，如以下索引：

CREATE INDEX test_event_created_at_idx ON test (event, created_at);

然后以下查询模拟索引跳过扫描。如果表被足够的抽真空，它将使用操作，索引仅在一次扫描中专门扫描：

WITH RECURSIVE hit AS (
(
SELECT event, created_at
FROM   test
ORDER  BY event, created_at
LIMIT  1
)

UNION ALL
SELECT t.*
FROM   hit h
CROSS  JOIN LATERAL (
SELECT t.event, t.created_at
FROM   test t
WHERE  (t.event, t.created_at)
> (h.event, h.created_at + interval '14 days')
ORDER  BY t.event, t.created_at
LIMIT  1
) t
)
SELECT count(*) AS hits FROM hit;

小提琴

我再怎么强调也不为过

这是一个使用LATERAL子查询的递归CTE，所有这些都基于ROW值比较的魔力(并非所有主要的RDBMS都正确支持)。

实际上，我们让Postgres跳过上述索引一次，只获取符合条件的行。

有关详细说明，请参阅：

• SELECT DISTINCT比PostgreSQL 中我的表上预期的要慢

• 从大表中有效地选择不同的(a，b)

• 优化GROUP BY查询以检索每个用户的最新行(第1a章)

不同的方法

就像您提到自己一样，不幸的任务定义迫使您为旧数据发生更改的事件重新计算所有较新的行。

请考虑使用常量光栅。就像从每年1月1日开始的14天网格。然后，每个事件的状态可以从本地帧中导出。更便宜、更可靠。

如果没有递归，我想不出如何做到这一点。

with recursive ordered as ( -- Order and number the event instances
select event, created_at, 
row_number() over (partition by event
order by created_at) as n
from test
), walk as (
-- Get and keep first instances
select event, created_at, n, created_at as current_base, true as keep
from ordered
where n = 1
union all
-- Carry base dates forward and mark records to keep
select c.event, c.created_at, c.n, 
case 
when c.created_at >= p.current_base + interval '14 days' 
then c.created_at
else p.current_base
end as current_base,
(c.created_at >= p.current_base + interval '14 days') as keep
from walk p
join ordered c 
on (c.event, c.n) = (p.event, p.n + 1)
)
select *
from walk
order by event, n;

Fiddle Here