如何修改SQL语句实现按月份统计各年份太阳能发电量分列展示？

我记录了太阳能板的每日发电量，想要编写SQL语句按月份统计发电量总和，且每个年份的数据以单独列展示。

当前我编写的SQL语句如下：

SELECT LPAD(extract (month from inverterlogs_summary_daily.bucket)::text, 2, '0') as month,
sum(inverterlogs_summary_daily."EnergyProduced") as  a2022
from inverterlogs_summary_daily
WHERE
  inverterlogs_summary_daily.bucket >= '01.01.2022' and inverterlogs_summary_daily.bucket < '01.01.2023'
group by month
order by 1;

这个语句只能查询2022年的数据，结果如下：

请问如何修改该SQL语句以实现每个年份对应单独列的效果？是否可行？预期结果如下（每年更新SQL语句也可接受）：

解决方案：使用条件聚合实现列拆分 #

完全可行，你可以通过条件聚合（结合CASE WHEN和SUM()）来实现按年份拆分列的需求。修改后的SQL语句如下：

SELECT 
  LPAD(extract(month from bucket)::text, 2, '0') as month,
  SUM(CASE WHEN extract(year from bucket) = 2022 THEN "EnergyProduced" ELSE 0 END) as a2022,
  SUM(CASE WHEN extract(year from bucket) = 2023 THEN "EnergyProduced" ELSE 0 END) as a2023
FROM inverterlogs_summary_daily
WHERE bucket >= '01.01.2022' AND bucket < '01.01.2024' -- 覆盖2022和2023年
GROUP BY month
ORDER BY month;

关键说明： #

• CASE WHEN会判断每条记录的年份，仅对对应年份的EnergyProduced进行求和，其他年份的记录会被计为0；若希望无数据的月份显示NULL而非0，去掉ELSE 0即可。
• 调整WHERE条件覆盖需要统计的所有年份，后续新增年份时，只需添加对应的SUM(CASE WHEN extract(year from bucket)=年份 THEN ...)列，并更新WHERE的结束日期。
• 保持GROUP BY month按月份聚合所有年份的数据，确保每个月份仅返回一行记录。

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