postgresql - 遍历交易数据，如果它们在时间上靠近另一行则删除行

Question

我在 PostGres 数据库(具体来说是 Greenplum)中有一系列事务，其 date_id 如下:

+----+-----------+----------------+
| id | date_id   | desired result |
+----+-----------+----------------+
|  1 |        58 | keep           |
|  2 |        99 | discard        |
|  3 |       110 | keep           |
|  4 |       188 | keep           |
|  5 |       190 | discard        |
|  6 |       191 | discard        |
|  7 |       201 | discard        |
|  8 |       244 | keep           |
|  9 |       255 | discard        |
+----+-----------+----------------+

date_id 只是日期的替代(因此 date_id 58 和 59 将是连续的天数；上表中的前两个事务相隔 39 天)。

我正在尝试实现以下规则:对于第一笔交易，保留它——但在 50 天内放弃任何其他交易。 50 天后，保留下一笔交易。然后在 50 天内放弃任何其他交易。等等。因此，没有两笔剩余(未丢弃)的交易彼此相隔 50 天。

在这种情况下，事务 id=1 是“保留”，因为它是第一个。交易 id=2 被丢弃(在第一笔交易后的 50 天内)。但是交易 id=3 是“保留”的，尽管距离第二笔交易仅 11 天。

有没有人对如何实现这个有任何想法？我认为这可以通过简单地将表连接到自身来实现，但我想不出满足这些规则的方法。

这没有帮助:

with intervals_between_transactions as 
(select t1.id transaction_id
, t1.date_id date_of_transaction
, max(t2.date_id) date_of_previous_transaction
from transactions t1
join transactions t2 on t2.date_id < t1.date_id
group by 1)
select *
from intervals_between_transactions 
where date_of_transaction - date_of_previous_transaction > 50

因为这会丢弃事务 3，尽管它是“保留的”。

这也行不通:

select date_id::numeric / 50.0 fifty_day_window
, min(id)
from transactions group by 1

因为它会“保留”交易 id=8 和 id=9，尽管它们仅相隔 11 天。

谢谢!

Answer 1

正如 Craig 所建议的，这可以通过使用 CTE 的迭代来解决，但是您需要多层嵌套才能使其工作:

DELETE FROM mytable
WHERE id NOT IN (
  WITH RECURSIVE min_date AS (
    SELECT min(date_id) FROM mytable
  ), keep AS (
    SELECT id, date_id
    FROM mytable, min_date
    WHERE date_id = min_date.min
    UNION
    SELECT * FROM (
      SELECT m.id, m.date_id
      FROM mytable m, keep
      WHERE m.date_id - keep.date_id > 50
      ORDER BY 2 LIMIT 1
    ) sub
  )
  SELECT id FROM keep);

CTE 的递归部分有很多限制，例如没有聚合函数、没有 ORDER BY 和没有 LIMIT 1，添加其中任何一个都很好只是结果集的下一条记录。然而，将递归项包装在子查询中将允许使用 ORDER BY 2 LIMIT 1，因此需要额外的嵌套。

与任何 DELETE 语句一样，首先通过仅运行 CTE 验证是否删除了正确的记录。参见 fiddle在这里。