sql - 选取每个用户最后一条记录前一个月内的数据

Question

假设我有一个名为“Diary”的表，如下所示:

| id | user_id |        recorded_at       | record |
|----|---------|--------------------------|--------|
| 20 |  50245  |2017-10-01 23:00:14.765366|   89   |
| 21 |  50245  |2017-12-05 10:00:33.135331|   97   |
| 22 |  50245  |2017-12-31 11:50:23.965134|   80   |
| 23 |  76766  |2015-10-06 11:00:14.902452|   70   |
| 24 |  76766  |2015-10-07 22:40:59.124553|   81   |

对于每个用户，我想检索最新的行和之前一个月内的所有行。

换句话说，对于 user_id 50245，我想要他/她的数据从“2017-12-01 11:50:23.965134”到“2017-12-31 11:50:23.965134”；对于 user_id 76766，我希望他/她的数据从“2015-09-07 22:40:59.124553”到“2015-10-07 22:40:59.124553”。

因此，期望的结果如下所示:

| id | user_id |        recorded_at       | record |
|----|---------|--------------------------|--------|
| 21 |  50245  |2017-12-05 10:00:33.135331|   97   |
| 22 |  50245  |2017-12-31 11:50:23.965134|   80   |
| 23 |  76766  |2015-10-06 11:00:14.902452|   70   |
| 24 |  76766  |2015-10-07 22:40:59.124553|   81   |

请注意，id 20 的记录不包括在内，因为它比 user_id 50245 的最后一条记录早了一个多月。

有什么方法可以编写 SQL 查询来实现此目的吗？

Answer 1

我会倾向于使用窗口函数:

select d.*
from (select d.*, max(d.recorded_at) over (partition by d.user_id) as max_recorded_at
      from diary d
     ) d
where recorded_at >= max_recorded_at - interval '1 month';