SQL按年分组计算area总和及占比时百分比全为100%如何解决

错误原因 #

• 窗口函数分区逻辑错误：你在计算全局总和的窗口函数中添加了PARTITION BY theyear参数，相当于按年份拆分窗口，每个窗口内仅包含对应年份的单条sum数据，因此SUM(totalarea) OVER (PARTITION BY theyear)返回值就是当年的area总和，当年总和除以自身自然所有百分比结果都是100%。要计算占全部年份总和的比例，窗口函数不需要分区，直接计算全局总和即可。
• 存在拼写错误：主查询中total.totalareal多写了末尾的l，正确字段名是你在CTE中定义的totalarea，否则运行时会报字段不存在的错误。

修正后的SQL语句 #

WITH total AS(
    SELECT EXTRACT(YEAR FROM "datefield") theyear, SUM(area) AS totalarea 
    FROM thetable 
    GROUP BY EXTRACT(YEAR FROM "datefield")
)
SELECT 
    theyear AS year,
    totalarea AS sum,
    ROUND(totalarea/(SUM(totalarea) OVER ()) * 100) AS percentage
FROM total;

上述SQL添加了ROUND函数对百分比取整，和你期望的输出格式匹配。

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