数据准备:
create table TEST_GROUP_TABLE
(
t_year NUMBER,
t_month NUMBER,
quantity NUMBER
);
insert into test_group_table values(2012,1,29);
insert into test_group_table values(2012,1,39);
insert into test_group_table values(2012,2,11);
insert into test_group_table values(2012,3,12);
insert into test_group_table values(2013,1,10);
insert into test_group_table values(2013,2,20);
insert into test_group_table values(2013,3,30);
insert into test_group_table values(2013,4,40);
--根据年份分组计算最小(大)数量及及最小(大)数量的月份
select t.t_year,
t.t_month,sum(quantity) qty,
first_value(sum(quantity)) over(partition by t_year order by sum(quantity)) min_qty,
first_value(t_month) over(partition by t_year order by sum(quantity)) min_month,
first_value(sum(quantity)) over(partition by t_year order by sum(quantity) desc) max_qty,
first_value(t_month) over(partition by t_year order by sum(quantity) desc) max_month,
rank() over(partition by t_year order by sum(quantity) desc) rank_1
from test_group_table t
group by t.t_year, t.t_month;
--根据年份分组计算当月合计数量,上月合计数量,下月合计数量
select t.t_year,
t.t_month,
sum(quantity) curr_month,
lag(sum(quantity)) over(partition by t_year order by t_month) last_month,
lead(sum(quantity)) over(partition by t_year order by t_month) next_month
from test_group_table t
group by t.t_year, t.t_month;
--根据年份分组,计算前n月至当月的合计数量
select t.t_year,
t.t_month,
sum(quantity),
sum(sum(quantity)) over(partition by t_year order by t_month rows between unbounded preceding and current row) agg_qty
from test_group_table t
group by t.t_year, t.t_month;
--根据年份分组,计算上月、当月和下月的合计数量
select t.t_year,
t.t_month,
sum(quantity),
sum(sum(quantity)) over(partition by t_year order by t_month rows between 1 preceding and 1 following) agg_qty
from test_group_table t
group by t.t_year, t.t_month;