如何编写SQLite Select查询获取每个月的最后一行数据？

我已经在这个问题上耗费了大量时间，尝试过子查询、max函数、group by having和union left join等多种方法，也查阅了诸多相关解答，但它们要么解决的是类似但不完全一致的问题，要么使用了SQLite不支持的特性（如过程逻辑）。

当前查询及结果 #

执行以下SQL查询：

select strftime('%Y-%m-%d', datetime(date, 'unixepoch')) as date, quantity from summary order by date

得到如下结果：

期望结果 #

需要获取每个月最后一天对应的数量（日期不固定），即返回以下三行数据：

是否存在能生成上述结果的select查询？

测试用数据 #

建表语句 #

CREATE TABLE summary (date integer, quantity integer)

插入语句 #

insert into summary values (1437408078, 346);
insert into summary values (1438262109, 688);
insert into summary values (1438342858, 1222);
insert into summary values (1438554528, 1291);
insert into summary values (1439405315, 1416);
insert into summary values (1440766612, 1618);
insert into summary values (1441126629, 1618);
insert into summary values (1441968543, 1804);
insert into summary values (1443543335, 1846);

验证查询 #

select strftime('%Y-%m-%d', datetime(date, 'unixepoch')) as date, quantity from summary order by date

解决方案 #

可以通过子查询先筛选出每个月的最大Unix时间戳（对应该月最晚的记录日期），再关联原表获取对应数量：

SELECT 
    strftime('%Y-%m-%d', datetime(s.date, 'unixepoch')) AS date,
    s.quantity
FROM summary s
INNER JOIN (
    SELECT 
        strftime('%Y-%m', datetime(date, 'unixepoch')) AS month,
        MAX(date) AS max_date
    FROM summary
    GROUP BY month
) m ON s.date = m.max_date
ORDER BY s.date;

逻辑说明 #

1. 子查询中，通过strftime('%Y-%m', ...)将Unix时间戳转换为年月格式，按年月分组后，用MAX(date)得到每个月的最大时间戳（即该月最后一条记录的时间）。
2. 将原表与子查询结果通过时间戳关联，筛选出每个月最后一条记录的日期和数量，最后按日期排序输出。

内容的提问来源于stack exchange，提问作者Max MacLeod