sql - postgresql 中的双重聚合

Question

我有一个这样的 postgresql 表(但有 1400 万条记录)

organization    inn income  parameter
org1            111 10      apple
org2            222 4       pineapple
org1            111 6       orange
org1            111 7       pineapple
org1            111 3       orange
org2            222 8       apple
org2            222 1       orange
org1            111 9       pineapple

我需要计算每个组织的总收入，还要找到每个组织内收入总和最大的参数

我想要的结果如下:

organization inn total_income max_parameter max_parameter_income
org 1        111 35           pineapple     16
org 2        222 13           apple         8

(实际上，我有四个参数列，我必须对每个组织的每个参数做类似的事情)

我该怎么做？

Answer 1

我会这样处理:

select organization, sum(total_income) as total_income,
       max(case when seqnum = 1 then parameter end) as max_parameter,
       sum(case when seqnum = 1 then total_income end) as max_parameter_income
from (select organization, inn, parameter, sum(income) as total_income,
             row_number() over (partition by organization, inn order by sum(income) desc) as seqnum
      from likethis
      group by organization, inn, parameter
     ) t
group organization, inn;

在 Postgres 中，您实际上可以在没有显式聚合的情况下执行此操作:

select distinct on (organization, inn) organization, inn,
       total_income, parameter as max_parameter,
       max_parameter_income
from (select organization, inn, parameter,
             sum(income) over (partition by organization, inn) as total_income,
             sum(income) over (partition by organization, inn, parameter) as parameter_income
      from likethis
     ) t
order by organization, inn, parameter_income desc;

我会使用第一种方法。有时使用有用的 Postgres 扩展很有趣。