在PostgreSQL中按日期分组聚合函数 [英] group by date aggregate function in postgresql

问题描述

运行此查询时出现错误

SELECT date(updated_at), count(updated_at) as total_count 
FROM "persons"
WHERE ("persons"."updated_at" BETWEEN '2012-10-17 00:00:00.000000' AND '2012-11-07     12:25:04.082224') 
GROUP BY date(updated_at)
ORDER BY persons.updated_at DESC

我收到错误ERROR:列"persons.updated_at"必须出现在GROUP BY子句中或在聚合函数中使用第5行:ORDER BY person.updated_at DESC

I get the error ERROR: column "persons.updated_at" must appear in the GROUP BY clause or be used in an aggregate function LINE 5: ORDER BY persons.updated_at DESC

如果我通过调用从分组中删除date(函数，这是可行的，但是我使用的是date函数，因为我想按日期而不是日期时间进行分组

This works if I remove the date( function from the group by call, however I'm using the date function because i want to group by date, not datetime

任何想法

推荐答案

目前尚不清楚您希望Postgres返回什么.您说它应该按 persons.updated_at 排序，但是您不会从数据库中检索该字段.

At the moment it is unclear what you want Postgres to return. You say it should order by persons.updated_at but you do not retrieve that field from the database.

我认为，您要做的是:

SELECT date(updated_at), count(updated_at) as total_count 
FROM "persons"
WHERE ("persons"."updated_at" BETWEEN '2012-10-17 00:00:00.000000' AND '2012-11-07     12:25:04.082224') 
GROUP BY date(updated_at)
ORDER BY count(updated_at) DESC -- this line changed!

现在，您要明确告诉数据库按COUNT汇总的结果值进行排序.您还可以使用: ORDER BY 2 DESC ，有效地告诉数据库按结果集中的第二列进行排序.但是，为了清晰起见，我非常希望明确说明该专栏.

Now you are explicitly telling the DB to sort by the resulting value from the COUNT-aggregate. You could also use: ORDER BY 2 DESC, effectively telling the database to sort by the second column in the resultset. However I highly prefer explicitly stating the column for clarity.

请注意，我目前无法测试此查询，但我确实认为这应该可行.

Note that I'm currently unable to test this query, but I do think this should work.

这篇关于在PostgreSQL中按日期分组聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！