postgresql postgres 聚合函数调用不能嵌套
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24063370/
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
postgres aggregate function calls may not be nested
提问by girlcoder
I have a query:
我有一个疑问：
select sum(
sum((Impressions / Count) * Volume) / sum(Volume)
) as frequency
from datatable;
however I cannot execute this in postgres because it uses nested aggregations. Is there another way to write this without using nested aggregations?
但是我无法在 postgres 中执行它，因为它使用嵌套聚合。有没有另一种方法可以在不使用嵌套聚合的情况下编写它？
回答by Tomas Greif
I will assume that you need to calculate the inner formula for some groups of items first and than sum results. I am using productcolumn as an arbitrary choice for grouping column. I also renamed Countto dcount.
我将假设您需要先计算某些项目组的内部公式，然后再计算结果。我使用product列作为分组列的任意选择。我也重命名Count为dcount.
Sample data:
样本数据：
create table sample (
product varchar,
dcount int,
impressions int,
volume int
);
insert into sample values ('a', 100, 10, 50);
insert into sample values ('a', 100, 20, 40);
insert into sample values ('b', 100, 30, 30);
insert into sample values ('b', 100, 40, 30);
insert into sample values ('c', 100, 50, 10);
insert into sample values ('c', 100, 60, 100);
Query:
询问：
select
sum(frequency) as frequency
from
(
select
product,
sum((impressions / dcount::numeric) * volume) / sum(volume) as frequency
from
sample
group by
product
) x;
The point is that you cannot nest aggregate functions. If you need to aggregate aggregates then you need to use subquery.
关键是你不能嵌套聚合函数。如果您需要聚合聚合，则需要使用子查询。
