Postgresql如何基于两列组合为查询结果新增百分比列

最终修改后查询（最简写法） #

SELECT 
  animaltype,
  size,
  SUM(total) AS Total,
  ROUND(
    SUM(total) * 100.0 / SUM(SUM(total)) OVER (PARTITION BY animaltype),
    2
  ) || '%' AS percentage
FROM animals
WHERE sponsored_animalid IS NULL
GROUP BY animaltype, size
ORDER BY 
  animaltype ASC,
  CASE size 
    WHEN 'small' THEN 1 
    WHEN 'medium' THEN 2 
    WHEN 'large' THEN 3 
  END ASC;

逻辑说明 #

• 用PARTITION BY animaltype的窗口聚合函数，直接计算每个动物类型下所有size的总数量，无需额外创建表、视图或嵌套子查询
• 计算百分比时乘以100.0避免整数除法丢失精度，通过ROUND函数保留两位小数，拼接%符号后直接输出符合要求的百分比格式
• 排序部分修正了size的排序规则，通过CASE表达式自定义权重，严格按照small→medium→large的顺序输出，animaltype默认按字母升序排列

如果不需要百分比带%符号，只需要数值结果，删除语句中的|| '%'即可。

备选写法（逻辑更直观） #

如果觉得嵌套聚合写法不易理解，可以用CTE先计算分组汇总结果再算百分比：

WITH group_stats AS (
  SELECT 
    animaltype,
    size,
    SUM(total) AS Total
  FROM animals
  WHERE sponsored_animalid IS NULL
  GROUP BY animaltype, size
)
SELECT 
  animaltype,
  size,
  Total,
  ROUND(Total * 100.0 / SUM(Total) OVER (PARTITION BY animaltype), 2) || '%' AS percentage
FROM group_stats
ORDER BY 
  animaltype ASC,
  CASE size 
    WHEN 'small' THEN 1 
    WHEN 'medium' THEN 2 
    WHEN 'large' THEN 3 
  END ASC;

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