PostgreSQL 按月统计数据

需求

统计从年初到当前月的每月数据。

如果是历年数据则完整统计1-12月，如果是今年数据则统计1到当前月（如：1-10月）。

generate_series

使用PostgreSQL的内置方法generate_series生成序列，然后left join需要统计的表的数据，并关联相应的月份。

SQL

使用extract从current_date中提取年份（extract(year from current_date)都是PostgreSQL内置方法和关键字，extract返回的是numeric）。

select gs as month, COALESCE(sum(ar.amount) filter ( where category = 'xxx' ), 0) as xxx,
       COALESCE(sum(ar.amount) filter ( where category = 'yyy' ), 0) as yyy
from generate_series(
        1,
        case
            when
                extract(year from current_date)::int = 2024
                then extract(month from current_date)::int
            else 12
        end
     ) gs
left join a_record ar
on gs = ar.month and ar.year = 2024
group by gs
order by gs desc;