用分析函数计算环比、同比oracle

1.普及一下概念

环比 = 2018年10月/2018年09月（同一时期内不同时间段的比较）

同比 = 2018年10月/2017年10月（不同时期内相同时间段的比较）

环比增长率 =  （2018年10月-2018年09月）/2018年09月

同比增长率 =  （2018年10月-2017年10月）/2017年10月

2.关键sql 解释

(1)分析函数 

lead(params,m,n)  以params为目标向下m位取数，当取不到时默认为 n,

lag(params,m,n)  以params为目标向上m位取数，当取不到时默认为 n

over(order by...) 相当于 order by...

partition by ... 根据参数最为分割，将相同的先分割到一块

3.具体sql例子

//按月统计发票的总金额、总税额、总票数、月同比、月环比 、同比增长率、环比增长率

select  a.sumquantity,
nvl(round(a.sumquantity /lag(a.sumquantity) over( order by a.yearmonth)*100, 2), '0') ||'%' sumquantityHB,
nvl(round((a.sumquantity-a.lagAHB)/lag(a.sumquantity) over( order by a.yearmonth)*100, 2), '0') ||'%' sumquantityHBG,//计算环比（按月） 关键代码lag()over(partition by 月份/年份 order by 排序条件)
nvl(round(a.sumquantity/lag(a.sumquantity) over(partition  by to_char((to_date(a.yearmonth, 'yyyy/MM')), 'MM')  order by a.yearmonth)*100, 2), '0')||'%' sumquantityTB,
nvl(round((a.sumquantity-a.lagATB)/lag(a.sumquantity) over(partition  by to_char((to_date(a.yearmonth, 'yyyy/MM')), 'MM')  order by a.yearmonth)*100, 2), '0') ||'%' sumquantityTBG,

a.totalamount,

nvl(round(a.totalamount/lag(a.totalamount) over( order by a.yearmonth)*100, 2), '0')||'%' totalamountHB,

nvl(round((a.totalamount-a.lagBHB)/lag(a.totalamount) over( order by a.yearmonth)*100, 2), '0') || '%' totalamountHBG,
nvl(round(a.totalamount/lag(a.totalamount) over(partition  by to_char((to_date(a.yearmonth, 'yyyy/MM')), 'MM')  order by a.yearmonth)*100, 2), '0')||'%' totalamountTB,
nvl(round((a.totalamount-a.lagBTB)/lag(a.totalamount) over(partition  by to_char((to_date(a.yearmonth, 'yyyy/MM')), 'MM')  order by a.yearmonth)*100, 2), '0')||'%' totalamountTB,

a.taxamount ,

nvl(round(a.taxamount/lag(a.taxamount) over( order by a.yearmonth)*100, 2), '0')||'%' taxamountHB,
nvl(round((a.taxamount-a.lagCHB)/lag(a.taxamount) over( order by a.yearmonth)*100, 2), '0')||'%' taxamountHBG,
nvl(round(a.taxamount/lag(a.taxamount) over(partition  by to_char((to_date(a.yearmonth, 'yyyy/MM')), 'MM')  order by a.yearmonth)*100, 2), '0')||'%' taxamountTB,
nvl(round((a.taxamount-a.lagCTB)/lag(a.taxamount) over(partition by to_char((to_date(a.yearmonth,'yyyy/MM')),'MM') order bya.yearmonth)*100,2),'0')||'%' taxamountTBG
from
( select yearMonth, sumQuantity, lag(sumQuantity) over( order by yearmonth) lagAHB ,

lag(sumQuantity) over(partition  by to_char((to_date( yearmonth, 'yyyy/MM')), 'MM')  order by yearmonth) lagATB,

totalAmount, lag(totalAmount) over(order by yearmonth) lagBHB,

lag(totalAmount) over(partition  by to_char((to_date( yearmonth, 'yyyy/MM')), 'MM')  order by yearmonth) lagBTB,

taxAmount , lag(taxAmount) over(partition  by to_char((to_date( yearmonth, 'yyyy/MM')), 'MM')  order by yearmonth) lagCTB,

lag(taxAmount) over( order by yearmonth) lagCHB //计算环比（按月） 关键代码lag()over(partition by 月份/年份 order by 排序条件)
from
( select to_char(billing_date, 'yyyy/MM') yearMonth, count(1) sumQuantity , nvl(sum(total_amount) , 0.00) totalAmount, nvl(sum(total_tax) , 0.00) taxAmount
from input_original_account
where 1=1  group by to_char(billing_date, 'yyyy/MM'))//先按月份对要统计的数据进项分组

) a

where 1=1 and a.yearMonth='2018/09'

转载于:https://www.cnblogs.com/lijianan/p/9958494.html