postgresql postgresql中的按日期聚合函数分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13270172/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
group by date aggregate function in postgresql
提问by Chris Baxter
I'm getting an error running this query
运行此查询时出错
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
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
我收到错误错误：列“persons.updated_at”必须出现在 GROUP BY 子句中或用于聚合函数第 5 行：ORDER BY people.updated_at DESC
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
如果我通过调用从组中删除 date( 函数，但是我使用的是 date 函数，因为我想按日期分组，而不是按日期时间分组，这会起作用
any ideas
有任何想法吗
回答by pyrocumulus
At the moment it is unclear what you want Postgres to return. You say it should order by persons.updated_atbut you do not retrieve that field from the database.
目前尚不清楚您希望 Postgres 返回什么。您说它应该按顺序排序，persons.updated_at但您没有从数据库中检索该字段。
I think, what you want to do is:
我想，你想做的是：
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!
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.
现在您明确地告诉数据库按来自 COUNT 聚合的结果值进行排序。您还可以使用: ORDER BY 2 DESC，有效地告诉数据库按结果集中的第二列排序。但是，为了清楚起见，我非常喜欢明确说明该列。
Notethat I'm currently unable to test this query, but I do think this should work.
请注意，我目前无法测试此查询，但我认为这应该可行。
回答by SingleNegationElimination
the problem is that, because you are grouping by date(updated_at), the value for updated_atmay not be unique, different values of updated_atcan return the same value for date(updated_at). You need to tell the database whichof the possible values it should use, or alternately use the value returned by the group by, probably one of
问题在于，由于您是按 分组date(updated_at)，因此 for 的值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)
or
或者
SELECT date(updated_at) FROM persons GROUP BY date(updated_at)
ORDER BY min(updated_at)
