如何将GROUP BY求和结果除以统计总条数得到对应平均百分比数值

你无需拆分两次查询再关联结果，同一筛选条件下的聚合查询可直接拿到所需的总条数，有两种实现方式可选：

方式1：手动求和后除以总条数 #

和你预期的计算逻辑完全一致，直接在当前查询中用count(1)获取符合条件的总条数做分母即可：

select
  sum(cast(json_data -> 'surface_extension' -> 'cold' -> 'percentage' as float)) / count(1) as cold_perc,
  sum(cast(json_data -> 'surface_extension' -> 'coolest' -> 'percentage' as float)) / count(1) as coolest_perc,
  sum(cast(json_data -> 'surface_extension' -> 'comfort' -> 'percentage' as float)) / count(1) as comfort_perc,
  sum(cast(json_data -> 'surface_extension' -> 'hot' -> 'percentage' as float)) / count(1) as hot_perc,
  sum(cast(json_data -> 'surface_extension' -> 'very_hot' -> 'percentage' as float)) / count(1) as very_hot_perc,
  area_urban_id
from project_urbaninfo
where area_urban_id = 3 and item_service_id = 29 and infotype_id = 1
group by area_urban_id 

方式2：直接使用内置平均值函数 #

你需要的计算本质就是求对应字段的平均值，直接用SQL原生的avg聚合函数即可实现，和手动计算的结果完全一致，写法更简洁：

select
  avg(cast(json_data -> 'surface_extension' -> 'cold' -> 'percentage' as float)) as cold_perc,
  avg(cast(json_data -> 'surface_extension' -> 'coolest' -> 'percentage' as float)) as coolest_perc,
  avg(cast(json_data -> 'surface_extension' -> 'comfort' -> 'percentage' as float)) as comfort_perc,
  avg(cast(json_data -> 'surface_extension' -> 'hot' -> 'percentage' as float)) as hot_perc,
  avg(cast(json_data -> 'surface_extension' -> 'very_hot' -> 'percentage' as float)) as very_hot_perc,
  area_urban_id
from project_urbaninfo
where area_urban_id = 3 and item_service_id = 29 and infotype_id = 1
group by area_urban_id 

补充说明 #

如果需要同时保留求和结果、总条数、平均值三个维度的数据，可以分别定义别名查询：

select
  sum(cast(json_data -> 'surface_extension' -> 'cold' -> 'percentage' as float)) as cold_perc_sum,
  avg(cast(json_data -> 'surface_extension' -> 'cold' -> 'percentage' as float)) as cold_perc_avg,
  -- 其余字段按上述规则补充即可
  count(1) as total_rows,
  area_urban_id
from project_urbaninfo
where area_urban_id = 3 and item_service_id = 29 and infotype_id = 1
group by area_urban_id 

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