sql - 如果计数大于则删除行

Question

我有一个包含用户事件的表格，这是我的表格列

events (
  id integer,
  user_id integer,
  event text,
  date timestamp
)

当用户的事件数量大于 100 时，喜欢从表中删除行，并开始删除最旧的行。

---

更新1

适用于 PostgreSQL

我喜欢删除当前拥有超过 100 个事件的任何用户的所有事件，从用户最旧的事件开始。所以喜欢为每个用户保留 100 个最新事件

我尝试使用此查询，但它永远运行并且不执行任何操作

delete
from events as t1
where id IN (
select id from events where t1.user_id = user_id order by date desc offset 100 
)

---

更新2

我已经更新了我的查询并让它运行

delete from events as t
where t.id not in (
   select t2.id
   from events t2
   where t2.user_id = t.user_id
   order by t2.date desc
   limit 100
);

但是运行需要 11 分钟 :( 我的表有 15 个用户，71931 行，每个用户平均有 4795 个事件

Answer 1

在大多数版本的 SQL 中，您可以执行以下操作:

delete from t
    where t.date < (select t2.date
                    from t t2
                    where t2.user_id = t.user_id
                    order by t2.date desc
                    offset 99 fetch first 1 row only
                   );

或者:

delete from t
    where t.date not in (select t2.date
                         from t t2
                         where t2.user_id = t.user_id
                         order by t2.date desc
                         fetch first 100 row only
                        );