时间戳

首页 > 解决方案 > 如何在Postgres中按年月按最大（日期）组获取行？

sql - 如何在Postgres中按年月按最大（日期）组获取行？

问题描述

我正在将报告从 MySQL 迁移到 Postgres，我正在尝试按年和月获取每个类别组的最新记录，在 MySQL 中它看起来像这样：

 select Category,
        max(DATECOL) AS Date
 from Table
 group by Category, date_format(DATECOL,'%Y-%m')
 order by DATECOL desc;

+----------+------------+
| Category | Date       |
+----------+------------+
| A        | 2021-05-27 |
+----------+------------+
| B        | 2021-05-27 |
+----------+------------+
| A        | 2021-04-30 |
+----------+------------+
| B        | 2021-04-30 |
+----------+------------+
| A        | 2021-03-31 |
+----------+------------+
| B        | 2021-03-31 |
+----------+------------+

但是当我在 Postgres 中尝试以下操作时，它会给我一条"Must include DATECOL in GROUP BY"错误消息，而当我包含 DATECOL 时，它只会返回所有可能的日期。有没有办法在 Postgres 中获取每个类别的最大记录？. 这是我在 Postgres 中尝试过的返回"Must include DATECOL in GROUP BY"错误的方法

 select Category,
        max(DATECOL) AS DATE
 from Table
 group by Category, concat(EXTRACT(year from DATECOL),'-', EXTRACT(month from DATECOL) )
 order by DATECOL desc;

标签： sqlpostgresqlgreatest-n-per-groupaggregation

解决方案

要获取每个类别和月份的最新日期，只需按两者分组即可。使用 to_char()您喜欢的任何方式格式化：

SELECT category
     , to_char(datecol, 'YYYY-MM') AS mon
     , max(datecol) AS max_date
FROM   tbl
GROUP  BY 2, 1
ORDER  BY 2 DESC, 1;

mon不必在SELECT列表中。但是，当然，您不能将序数位置用作速记：

SELECT category
     , max(datecol) AS max_date
FROM   tbl
GROUP  BY to_char(datecol, 'YYYY-MM'), category
ORDER  BY to_char(datecol, 'YYYY-MM') DESC, category;

缺点：格式化文本可能无法正确排序（当然，YYYY-MM 通常没有问题）。对于大桌子，date_trunc()价格便宜一些。而且由于我们甚至没有显示它：

SELECT category
     , max(datecol) AS max_date
FROM   tbl
GROUP  BY date_trunc('month', datecol), 1
ORDER  BY date_trunc('month', datecol) DESC, 1;

以您喜欢的任何方式显示和格式化：

SELECT category
     , to_char(date_trunc('month', datecol), 'YYYY-MM') AS mon
     , max(datecol) AS max_date
FROM   tbl
GROUP  BY date_trunc('month', datecol), category
ORDER  BY date_trunc('month', datecol) DESC, category;

您必须重复GROUP BY表达式（除非您将其推送到子查询中），即使这对to_char().

有关的：

推荐阅读