用分析函数计算环比、同比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'
