时间戳

首页 > 解决方案 > 为每个 ID 选择每个月的最后一条记录

sql - 为每个 ID 选择每个月的最后一条记录

问题描述

我正在尝试为每个 ID 提取每个月的最后一条记录。

下面是我要求的表格：

myTable
ID  date         data
1   2020-08-27   a
1   2020-08-28   b
1   2020-09-30   c
2   2020-08-29   d
2   2020-09-30   e

因此，我想得到以下信息：

output
ID  date         data
1   2020-08-28   b
1   2020-09-30   c
2   2020-08-29   d
2   2020-09-30   e

这里的“复杂性”是每个 ID 的每个月的最后一条记录可能不一样。

到目前为止，我只能通过以下请求在数据库中获取每个月最后一天可用的所有记录：

SELECT ID, date, data
from myTable
and date in (SELECT max(date) FROM myTable GROUP BY strftime('%Y-%m', date))

这给了我这个

wrong output
ID  date         data
1   2020-09-30   c
2   2020-08-29   d
2   2020-09-30   e

标签： sqlsqlitesubquerygreatest-n-per-groupwindow-functions

解决方案

使用 ROW_NUMBER() 窗口函数：

SELECT ID, date, data
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY ID, strftime('%Y%m', date) ORDER BY date DESC) rn
  FROM myTable 
)
WHERE rn = 1

推荐阅读