在Postgres中如何获取按最大(日期)分组、按年-月的行数? [英] How to get rows by max(date) group by Year-Month in Postgres?
本文介绍了在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;
推荐答案
若要获取每个类别的最新日期和月,只需同时按这两个类别分组。使用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()
没有影响。
相关:
这篇关于在Postgres中如何获取按最大(日期)分组、按年-月的行数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文