SQLite选择查询以返回每个月的最后一行

nc1teljy 于 2023-10-23 发布在 SQLite

关注(0)|答案(2)|浏览(121)

我花了很长时间研究这个。我尝试了使用子查询、max函数、group by having和union left join的各种方法。
也可以在这里阅读许多解决类似但不相同问题的答案。或者，它们使用SQLite中不可用的特性（如过程逻辑）进行解析。
下表由此查询生成：

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

| 日期|数量|
| --|--|
| 2015-07-20 2015-07-20| 346 |
| 2015-07-30 2015-07-30| 688 |
| 2015-07-31| 1222 |
| 2015-08-02 2015-08-02| 1291 |
| 2015-08-12 2015-08-12 2015-08-12| 1416 |
| 2015-08-28 2015-08-28| 1618 |
| 2015-09-01 2015-09-01| 1618 |
| 2015-09-11 2015-09-11 2015-09-11| 1804 |
| 2015-09-29 2015-09-29| 1846 |
所需的结果集是按月的最后日期的数量。日期可能会有所不同。鉴于上表，它将是三行，对应于七月，八月和九月：
| 日期|数量|
| --|--|
| 2015-07-31| 1222 |
| 2015-08-28 2015-08-28| 1618 |
| 2015-09-29 2015-09-29| 1846 |
是否有一个select查询可以生成上面的三行？
下面是在本地示例上尝试此操作的测试数据。首先，创建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

sqlite

来源：https://stackoverflow.com/questions/77314512/sqlite-select-query-to-return-last-row-for-each-month

2条答案

按热度按时间

8wtpewkr1#

这里有一种方法，使用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

Demo here

赞(0）回复(0）举报 2023-10-23

pgx2nnw82#

使用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;

赞(0）回复(0）举报 2023-10-23

我来回答

SQLite选择查询以返回每个月的最后一行

2条答案

相关问题

热门标签

最新问答