如何在PostgreSQL中将月度报告转换为季度报告

Question

它目前在月度报告中。我希望它有 4 个季度，第一季度、第二季度、第三季度、第四季度，而不是一月、二月、三月、四月、五月、六月、七月等。

这是代码：

create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);
select * from crosstab(
'select year, month, qty from sales order by 1',
'select m from generate_series(1,12) m'
) as (
year int,
"Jan" int,
"Feb" int,
"Mar" int,
"Apr" int,
"May" int,
"Jun" int,
"Jul" int,
"Aug" int,
"Sep" int,
"Oct" int,
"Nov" int,
"Dec" int
);

Answer 1

我只会使用条件聚合：

select year,
sum(qty) filter (where month between 1 and 3) as q1,
sum(qty) filter (where month between 4 and 6) as q2,
sum(qty) filter (where month between 7 and 9) as q3,
sum(qty) filter (where month between 10 and 12) as q4
from t
group by year;