提问人：Max MacLeod 提问时间：10/18/2023 最后编辑：Max MacLeod 更新时间：10/18/2023 访问量：87
SQLite 选择查询以返回每个月的最后一行
SQLite Select Query To Return Last Row For Each Month
问：
已经为此烦恼了太久了。我尝试了使用子查询、函数和 的各种方法。maxgroup by havingunion left join
还可以在这里阅读许多答案，这些答案解决了 - 相似 - 但不完全相同的问题。或者，它们使用 SQLite 中不可用的功能（例如过程逻辑）进行解析。
下表由此查询生成：
select strftime(' %Y-%m-%d', datetime(date, 'unixepoch')) as date, quantity from summary order by date
| 日期 | 数量 |
|---|---|
| 2015-07-20 | 346 |
| 2015-07-30 | 688 |
| 2015-07-31 | 1222 |
| 2015-08-02 | 1291 |
| 2015-08-12 | 1416 |
| 2015-08-28 | 1618 |
| 2015-09-01 | 1618 |
| 2015-09-11 | 1804 |
| 2015-09-29 | 1846 |
所需结果集是按月划分的最后一个日期的数量。日期可能会有所不同。根据上表，它将是三行，对应于 7 月、8 月和 9 月：
| 日期 | 数量 |
|---|---|
| 2015-07-31 | 1222 |
| 2015-08-28 | 1618 |
| 2015-09-29 | 1846 |
是否有可以生成上述三行的查询？select
以下是在本地实例上尝试此操作的测试数据。首先，create table 语句：
CREATE TABLE summary ( date integer , quantity integer )
请注意，我别无选择，只能使用 Unix Epoch 格式的日期。插入：
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
答：
1赞
choroba
10/18/2023
#1
使用 SUSBSTR（） 仅从日期中提取年份和月份：
SELECT MAX(date), quantity
FROM summary
GROUP BY SUBSTR(date, 1, 7);
请注意，此查询使用“裸列”，这使得它与 SQLite 以外的大多数其他数据库引擎不兼容。例如，在 Postgres 中，我不得不使用
SELECT DISTINCT ON (month) date, quantity
FROM (
SELECT date,
quantity,
TO_CHAR(date, 'YYYYMM') AS month
FROM summary
) AS t ORDER BY month, date DESC;
而更冗长的 JOIN 版本似乎快了大约两倍：
SELECT t.date, t.quantity
FROM summary AS t
JOIN (
SELECT MAX(m.date) AS date
FROM (
SELECT date, quantity, TO_CHAR(date, 'YYYYMM') AS month
FROM summary
ORDER BY date
) AS m
GROUP BY month
) AS t1 ON t.date = t1.date;
评论
1赞
Thorsten Kettner
10/18/2023
SUBSTR(date, 0, 8)未定义。文档仅指定字符串中第一个字符以 1 开头的正数和字符串中最后一个字符以 -1 开头的负数的行为。您想要或 .SUBSTR(date, 1, 8)SUBSTR(date, 1, 7)
1赞
Thorsten Kettner
10/18/2023
您解释您正在使用 SQLite 的裸列处理，这将使您的答案受益匪浅，这使得查询在 SQLite 中有效且有效，而在其他 DBMS 中无效。
1赞
choroba
10/18/2023
@ThorstenKettner：已记录并更新。谢谢。
0赞
Max MacLeod
10/18/2023
不幸的是，上面的查询返回了 9 行，而不是所需的 3 行。如果您想尝试，将添加创建表和插入。
1赞
choroba
10/18/2023
@MaxMacLeod：我根据您第一个查询的输出创建了一个表。如果以不同的格式存储，则可能需要更改 SUBSTR 以从时间戳中提取年份和月份。
1赞
SelVazi
10/18/2023
#2
这是一种方法，用于按月对数据进行分组，然后获取最大日期及其相关数量作为裸列：group by
SELECT max(strftime('%Y-%m-%d', datetime(date, 'unixepoch'))) as date, quantity
FROM summary
group by strftime('%Y-%m-01', datetime(date, 'unixepoch'))
结果：
date quantity
2015-07-31 1222
2015-08-28 1618
2015-09-29 1846
一种不使用裸列的更通用的方法：
SELECT strftime('%Y-%m-%d', datetime(date, 'unixepoch')) as date, quantity
FROM summary s
INNER JOIN (
SELECT strftime('%Y-%m-01', datetime(date, 'unixepoch')) as month,
max(date) as max_date
FROM summary
GROUP BY 1
) AS t on t.max_date = s.date
评论
1赞
Max MacLeod
10/18/2023
哇，很高兴知道 db 小提琴链接！
1赞
symcbean
10/18/2023
这可能会产生不一致的结果，具体取决于 SQLITE 的版本。有几种方法可以正确解决问题 - 主要基于子查询或 max-concat 技巧。MySQL手册中还有一整章 - dev.mysql.com/doc/refman/8.0/en/...
0赞
SelVazi
10/18/2023
@symcbean 我不确定裸列何时首次出现在 sqlLite 版本中！我添加了一个更通用的查询来处理它，而不是使用裸列。
评论