在PostgreSQL中按日期分组聚合函数

Question

运行此查询时出现错误

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: 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

如果我在group by调用中删除date（）函数，则可以正常运行。但是我正在使用date函数，因为我想按日期进行分组，而不是按日期时间。

有什么建议吗？

- Chris Baxter

Answer 1

目前还不清楚您想让Postgres返回什么。您说应该按persons.updated_at排序，但没有从数据库中检索该字段。

我认为您想要做的是：

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，有效地告诉数据库按结果集中的第二列进行排序。但是，为了清晰起见，我强烈建议明确声明列。

注意，我目前无法测试此查询，但我认为这应该可以工作。

- pyrocumulus

Answer 2

问题在于，因为你正在按date(updated_at)分组，所以updated_at的值可能不是唯一的，不同的updated_at值可以返回相同的date(updated_at)值。你需要告诉数据库应该使用哪个可能的值，或者替代地使用由group by返回的值，也许是其中之一。

SELECT date(updated_at) FROM persons GROUP BY date(updated_at)
ORDER BY date(updated_at)

或者

SELECT date(updated_at) FROM persons GROUP BY date(updated_at)
ORDER BY min(updated_at)

- SingleNegationElimination

---

min()在ORDER BY中救了我的一天！ - sequielo