postgres聚合函数调用可能未嵌套

我假设您需要先计算某些项目组的内部公式，然后再对结果求和。我将

product

Count

dcount

SQLFiddle

样本数据：

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);

询问：

select  sum(frequency) as frequencyfrom   (  select    product,    sum((impressions / dcount::numeric) * volume) / sum(volume) as frequency  from     sample  group by    product  ) x;

关键是您不能嵌套聚合函数。如果需要聚合聚合，则需要使用子查询。