使用子查询结合WHERE子句按年份计算分组占比的问题

现有数据表 #

practice=# select * from table;
 letter |  value  | year 
--------+---------+------
 A      | 5000.00 | 2021
 B      | 6000.00 | 2021
 C      | 6000.00 | 2021
 B      | 8000.00 | 2022
 A      | 9000.00 | 2022
 C      | 7000.00 | 2022
 A      | 2000.00 | 2021
 B      | 1000.00 | 2022
 C      | 3000.00 | 2021
(9 rows)

原查询及问题 #

原查询用于计算全表中A、B、C的总值占比：

practice=# select letter, cast((group_values/(select sum(value) from percentages)*100) as decimal(4,2)) as group_values from (select letter, sum(value) as group_values from percentages group by letter order by letter) as subquery order by group_values desc;
 letter | group_values 
--------+--------------
 A      |        34.04
 C      |        34.04
 B      |        31.91
(3 rows)

当尝试计算2022年的占比时，仅在子查询中加入WHERE year='2022'，但外层用于计算总值的sum(value)仍统计全表数据，导致占比计算错误：

select letter, cast((group_values/(select sum(value) from percentages)*100) as decimal(4,2)) as group_values from (select letter, sum(value) as group_values from percentages where year='2022' group by letter order by letter) as subquery order by group_values desc;

错误结果：

letter | group_values 
--------+--------------
 A      |        19.15
 B      |        19.15
 C      |        14.89
(3 rows)

在外层子查询后添加WHERE year='2022'会直接报错，因为子查询的结果集中不存在year字段：

select letter, cast((group_values/(select sum(value) from percentages)*100) as decimal(4,2)) as group_values from (select letter, sum(value) as group_values from percentages group by letter order by letter) as subquery where year='2022' order by group_values desc;

ERROR:  column "year" does not exist

解决方案 #

方法1：同步过滤全局总值的子查询 #

让外层用于计算年份总值的子查询也添加年份过滤条件，确保分母是目标年份的总数值：

select 
  letter, 
  cast((group_values/(select sum(value) from percentages where year='2022')*100) as decimal(4,2)) as percentage
from (
  select letter, sum(value) as group_values 
  from percentages 
  where year='2022' 
  group by letter 
  order by letter
) as subquery 
order by percentage desc;

执行结果（2022年总价值为25000）：

letter | percentage 
--------+------------
 A      |       36.00
 B      |       36.00
 C      |       28.00
(3 rows)

方法2：使用窗口函数（更简洁高效） #

利用SUM() OVER ()窗口函数直接计算当前过滤年份的总值，无需额外嵌套子查询：

select 
  letter, 
  cast((sum(value)/sum(sum(value)) over () *100) as decimal(4,2)) as percentage
from percentages
where year='2022'
group by letter
order by percentage desc;

该查询中，sum(sum(value)) over ()会先按letter分组求和，再计算所有分组的总和（即2022年的总价值），一步完成占比计算。

内容的提问来源于stack exchange，提问作者Michael Grogan