sql - 按属性获取最后 5 行，postgres

Question

我有一个与用户关联的事件表我想知道是否可以编写一个 sql 查询来选择创建事件的用户，并且最后 3 个事件的状态为“已取消”

not count() where status = 'canceled', only where last 3 rows had status canceled

Select u.* from users AS u
Right Join events as e ON e.user_id = u.id
WHERE {last 3 rows have e.status = 'canceled'}
Order by e.created_at
Group by u.id

事件表:

user_id, status, created_at
1, 'canceled', 2017-04-21
1, 'finished', 2017-04-20
1, 'finished', 2017-04-19
1, 'canceled', 2017-04-18
1, 'canceled', 2017-04-17
2, 'canceled', 2017-04-21
2, 'canceled', 2017-04-20
2, 'canceled', 2017-04-19
2, 'finished', 2017-04-18
2, 'finished', 2017-04-17

Answer 1

您可以使用窗口函数获取用户列表。这是一种方法:

select e.user_id
from (select e.*,
             row_number() over (partition by e.user_id order by created_at desc) as seqnum
      from event e
     ) e
where seqnum <= 3 and e.status = 'canceled'
group by e.user_id
having count(*) = 3;

查询从末尾开始枚举事件，然后统计取消的事件数。

您可以通过加入 users(或使用 in 或 exists)来获取额外的用户信息。