Oracle之保有量计算（当前记录等于前几条记录之和）

 

需求：存在左图销量表，要得到右边的保有量表，保有量等于前12月销量和。

 

年	月	销量		年	月	保有量
2010	1	4		2010	1	4
2010	2	4		2010	2	8
2010	3	4		2010	3	16
2010	4	4		2010	4	20
2010	5	4		2010	5	24
2010	6	4		2010	6	28
2010	7	4		2010	7	32
2010	8	4		2010	8	36
2010	9	4		2010	9	40
2010	10	4		2010	10	44
2010	11	4		2010	11	48
2010	12	4		2010	12	52
2011	1	4		2011	1	52
2011	2	4		2011	2	52
2011	3	4		2011	3	52
2011	4	4		2011	4	52
2011	5	4		2011	5	52
2011	6	4		2011	6	52

 

解答：创建例表，便于测试

create table tsales(y,m,n)--年，月，销量
as select  2010,  1,  4 from dual union all
select  2010,  2,  4 from dual union all
select  2010,  3,  4 from dual union all
select  2010,  4,  4 from dual union all
select  2010,  5,  4 from dual union all
select  2010,  6,  4 from dual union all
select  2010,  7,  4 from dual union all
select  2010,  8,  4 from dual union all
select  2010,  9,  4 from dual union all
select  2010,  10,  4 from dual union all
select  2010,  11,  4 from dual union all
select 2010, 12, 4 from dual union all
select  2011,  1,  4 from dual union all
select  2011,  2,  4 from dual union all
select  2011,  3,  4 from dual union all
select  2011,  4,  4 from dual union all
select  2011,  5,  4 from dual union all
select 2011, 6, 4 from dual

--计算保有量（保有量等于前12个月内的销量和）
select t1.*,(select sum(t2.n)
             from tsales t2
            where (t2.y = t1.y and t2.m <= t1.m)
               or (t2.y = t1.y - 1 and t2.m > t1.m)
           ) as 保有量 from tsales t1

 

--利用分析函数也可以：

--方法一

select y,m,n,n+LAG(n,11,0)over(order by y,m)+LAG(n,10,0)over(order by y,m)+LAG(n,9,0)over(order by y,m)+
LAG(n,8,0)over(order by y,m)+LAG(n,7,0)over(order by y,m)+LAG(n,6,0)over(order by y,m)+
LAG(n,5,0)over(order by y,m)+LAG(n,4,0)over(order by y,m)+LAG(n,3,0)over(order by y,m)+
LAG(n,2,0)over(order by y,m)+LAG(n,1,0)over(order by y,m) as 保有量from tsales

 

--方法二

SELECT y,m,n,SUM(n)over(order by y,m rows between 11 preceding and 0 following) QTY
FROM tsales

 

以上两个分析函数的使用存在错误，保有量的计算是按钮年月前推12个月（即一年内)，实际业务中不能保证每个月都有数据（即每个月都有销量），如果我们能确保表中每个月都有销量的话（或将缺省的月份构造成0销量也可）上述两个分析函数的方法可以使用。使用range窗体子句才是正解，如下：

SELECT y,m,n,SUM(n)over(order by y,m range between 11 preceding and 0 following) QTY.
FROM tsales

 

 

分析函数中窗口子句rows和range的区别就是前者以记录数分窗，后者以字段值分窗；