如何查找事务表中仍处于活动状态的记录

Question

我有一个包含两个事件值的事务表：CREATE 和 REMOVE。 还有account_id和project_id字段。 我正在尝试确定具有 2 个或更多已创建但未删除的项目的帐户数量。

以下是获取具有 2 个以上项目的帐户的初始查询：

SELECT account_id, count(*) AS counter
FROM projects a
WHERE event = 'INSERT'
GROUP BY account_id
HAVING counter > 1

我认为像这种自连接这样的东西会从project_id索引出来，并给我左表中具有"INSERT"值的记录，而左表中没有相应的"删除"值。 但返回的数据不是预期的。

SELECT a.accountid, count(*) AS counter
FROM projects a
LEFT JOIN projects b ON a.project_id = b.project_id
WHERE a.eventname = 'INSERT'
AND b.eventname != 'REMOVE'
GROUP BY a.account_id
HAVING counter > 1

关于如何解决这个问题的任何想法？

Answer 1

如果我假设一个项目只创建和删除一次，那么你只需要计算这些值：

select account_id,
sum(case when event = 'CREATE' then 1 else 0 end) as num_creates,
sum(case when event = 'removes' then 1 else 0 end) as num_removes
from projects p
group by account_id
having num_creates - num_removes >= 2