postgres聚合函数调用可能未嵌套 [英] postgres aggregate function calls may not be nested
本文介绍了postgres聚合函数调用可能未嵌套的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个查询:
select sum(
sum((Impressions / Count) * Volume) / sum(Volume)
) as frequency
from datatable;
但是我无法在postgres中执行此操作,因为它使用了嵌套聚合。还有另一种不用嵌套聚合写此方法的方法吗?
however I cannot execute this in postgres because it uses nested aggregations. Is there another way to write this without using nested aggregations?
推荐答案
我假设您需要首先计算某些项目组的内部公式,而不是对结果求和。我将 product
列用作分组列的任意选择。我还将 Count
重命名为 dcount
。
I will assume that you need to calculate the inner formula for some groups of items first and than sum results. I am using product
column as an arbitrary choice for grouping column. I also renamed Count
to dcount
.
示例数据:
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 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.
这篇关于postgres聚合函数调用可能未嵌套的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文