1、同比环比

  1. ---- 练习  同比环比
  3. create table sales_amount
  4. (years varchar2(20),
  5.  months varchar2(20),
  6.  sales_amount number
  7. );
  10. insert into sales_amount values('2016','01',100);
  11. insert into sales_amount values('2016','02',110);
  12. insert into sales_amount values('2016','03',140);
  13. insert into sales_amount values('2016','04',150);
  14. insert into sales_amount values('2016','05',190);
  15. insert into sales_amount values('2016','06',300);
  16. insert into sales_amount values('2016','07',450);
  17. insert into sales_amount values('2016','08',580);
  18. insert into sales_amount values('2016','09',470);
  19. insert into sales_amount values('2016','10',340);
  20. insert into sales_amount values('2016','11',240);
  21. insert into sales_amount values('2016','12',180);
  24. insert into sales_amount values('2017','01',130);
  25. insert into sales_amount values('2017','02',150);
  26. insert into sales_amount values('2017','03',190);
  27. insert into sales_amount values('2017','04',250);
  28. insert into sales_amount values('2017','05',390);
  29. insert into sales_amount values('2017','06',800);
  30. insert into sales_amount values('2017','07',950);
  31. insert into sales_amount values('2017','08',380);
  32. insert into sales_amount values('2017','09',570);
  33. insert into sales_amount values('2017','10',740);
  34. insert into sales_amount values('2017','11',340);
  35. insert into sales_amount values('2017','12',680);
  36. COMMIT;
  38. ---- 目标表
  39. 展示效果一
  41. 年   1月销售额 2月销售额 3月销售额 ...... 12月销售额
  42. 2017 100    110   140    180
  46. 展示效果二
  48. 年   1季度销售额  2季度销售额  3季度销售额   4季度销售额
  50. 求:月增长率 季度增长率?   
  52. -- 环比 相邻月份 
  54. 举例
  56. (二月份销售额 - 一月份销售额)/一月份销售额
  60. -- 同比 相邻年份的相同月份 
  62. (2017 一月份销售额  - 2016 一月份销售额) / 2016 一月份销售额
  64. -------------------------------------------------------------
  66. -- 行转列
  67. -- deoce 月度展示
  68. select s.years,
  69.        sum(decode(s.months, '01', s.sales_amount, 0)) as m1,
  70.        sum(decode(s.months, '02', s.sales_amount, 0)) as m2,
  71.        sum(decode(s.months, '03', s.sales_amount, 0)) as m3,
  72.        sum(decode(s.months, '04', s.sales_amount, 0)) as m4,
  73.        sum(decode(s.months, '05', s.sales_amount, 0)) as m5,
  74.        sum(decode(s.months, '06', s.sales_amount, 0)) as m6,
  75.        sum(decode(s.months, '07', s.sales_amount, 0)) as m7,
  76.        sum(decode(s.months, '08', s.sales_amount, 0)) as m8,
  77.        sum(decode(s.months, '09', s.sales_amount, 0)) as m9,
  78.        sum(decode(s.months, '10', s.sales_amount, 0)) as m10,
  79.        sum(decode(s.months, '11', s.sales_amount, 0)) as m11,
  80.        sum(decode(s.months, '12', s.sales_amount, 0)) as m12
  81.   from sales_amount s
  82.  group by s.years;
  87. -- case when  季度展示
  88. select s.years
  89.       ,sum(case when s.months in('01','02','03') then s.sales_amount else 0 end ) as Q1
  90.       ,sum(case when s.months in('04','05','06') then s.sales_amount else 0 end ) as Q2
  91.       ,sum(case when s.months in('07','08','09') then s.sales_amount else 0 end ) as Q3
  92.       ,sum(case when s.months in('10','11','12') then s.sales_amount else 0 end ) as Q4
  93.   from sales_amount s
  94.  group by s.years;
  98. -- 季度环比 相邻
  99. -- Q4 下移一行  分析函数 lag
  100. with tmp as
  101. (select s.years
  102.       ,sum(case when s.months in('01','02','03') then s.sales_amount else 0 end ) as Q1
  103.       ,sum(case when s.months in('04','05','06') then s.sales_amount else 0 end ) as Q2
  104.       ,sum(case when s.months in('07','08','09') then s.sales_amount else 0 end ) as Q3
  105.       ,sum(case when s.months in('10','11','12') then s.sales_amount else 0 end ) as Q4
  106.   from sales_amount s
  107.  group by s.years)
  109. select t.*
  110.       ,lag(Q4) over(partition by null order by years) as new_Q4
  111.   from tmp t;
  115. -- 
  116. with tmp as
  117. (select s.years
  118.       ,sum(case when s.months in('01','02','03') then s.sales_amount else 0 end ) as Q1
  119.       ,sum(case when s.months in('04','05','06') then s.sales_amount else 0 end ) as Q2
  120.       ,sum(case when s.months in('07','08','09') then s.sales_amount else 0 end ) as Q3
  121.       ,sum(case when s.months in('10','11','12') then s.sales_amount else 0 end ) as Q4
  122.   from sales_amount s
  123.  group by s.years)
  125. select t.years
  126.       ,(t.Q1 - lag(Q4) over(partition by null order by years))/lag(Q4) over(partition by null order by years) as Q1_HB_RATE
  127.       ,(t.Q2 - t.Q1)/t.Q1 as Q2_HB_RATE
  128.       ,(t.Q3 - t.Q2)/t.Q2 as Q3_HB_RATE
  129.       ,(t.Q4 - t.Q3)/t.Q3 as Q4_HB_RATE
  130.   from tmp t;
  134.  -- 月份环比 相邻
  135. with tmp as(
  136. select s.years
  137.       ,sum(decode(s.months,'01',s.sales_amount,0)) as m1
  138.       ,sum(decode(s.months,'02',s.sales_amount,0)) as m2
  139.       ,sum(decode(s.months,'03',s.sales_amount,0)) as m3
  140.       ,sum(decode(s.months,'04',s.sales_amount,0)) as m4
  141.       ,sum(decode(s.months,'05',s.sales_amount,0)) as m5
  142.       ,sum(decode(s.months,'06',s.sales_amount,0)) as m6
  143.       ,sum(decode(s.months,'07',s.sales_amount,0)) as m7
  144.       ,sum(decode(s.months,'08',s.sales_amount,0)) as m8
  145.       ,sum(decode(s.months,'09',s.sales_amount,0)) as m9
  146.       ,sum(decode(s.months,'10',s.sales_amount,0)) as m10
  147.       ,sum(decode(s.months,'11',s.sales_amount,0)) as m11
  148.       ,sum(decode(s.months,'12',s.sales_amount,0)) as m12
  149.   from sales_amount s
  150.  group by s.years)
  152. select t.years
  153.       ,(t.m1 - lag(m12) over(partition by null order by years))/lag(m12) over(partition by null order by years) as M1_HB_RATE
  154.       ,(t.m2 - t.m1)/t.m1    as M2_HB_RATE
  155.       ,(t.m3 - t.m2)/t.m2    as M3_HB_RATE
  156.       ,(t.m4 - t.m3)/t.m3    as M4_HB_RATE
  157.       ,(t.m5 - t.m4)/t.m4    as M5_HB_RATE
  158.       ,(t.m6 - t.m5)/t.m5    as M6_HB_RATE
  159.       ,(t.m7 - t.m6)/t.m6    as M7_HB_RATE
  160.       ,(t.m8 - t.m7)/t.m7    as M8_HB_RATE
  161.       ,(t.m9 - t.m8)/t.m8    as M9_HB_RATE
  162.       ,(t.m10 - t.m9)/t.m9   as M10_HB_RATE
  163.       ,(t.m11 - t.m10)/t.m10 as M11_HB_RATE
  164.       ,(t.m12 - t.m11)/t.m11 as M12_HB_RATE
  165.   from tmp t;
  169. -- 同比 季度
  170. with tmp as
  171. (select s.years
  172.       ,sum(case when s.months in('01','02','03') then s.sales_amount else 0 end ) as Q1
  173.       ,sum(case when s.months in('04','05','06') then s.sales_amount else 0 end ) as Q2
  174.       ,sum(case when s.months in('07','08','09') then s.sales_amount else 0 end ) as Q3
  175.       ,sum(case when s.months in('10','11','12') then s.sales_amount else 0 end ) as Q4
  176.   from sales_amount s
  177.  group by s.years)
  179. select t.years
  180.       ,(t.Q1 - lag(Q1) over(partition by null order by years))/lag(Q1) over(partition by null order by years) as Q1_TB_RATE
  181.       ,(t.Q2 - lag(Q2) over(partition by null order by years))/lag(Q2) over(partition by null order by years) as Q1_TB_RATE
  182.       ,(t.Q3 - lag(Q3) over(partition by null order by years))/lag(Q3) over(partition by null order by years) as Q1_TB_RATE
  183.       ,(t.Q4 - lag(Q4) over(partition by null order by years))/lag(Q4) over(partition by null order by years) as Q1_TB_RATE
  184.   from tmp t;
  188. -- 同比 月份
  189. with tmp as(
  190. select s.years
  191.       ,sum(decode(s.months,'01',s.sales_amount,0)) as m1
  192.       ,sum(decode(s.months,'02',s.sales_amount,0)) as m2
  193.       ,sum(decode(s.months,'03',s.sales_amount,0)) as m3
  194.       ,sum(decode(s.months,'04',s.sales_amount,0)) as m4
  195.       ,sum(decode(s.months,'05',s.sales_amount,0)) as m5
  196.       ,sum(decode(s.months,'06',s.sales_amount,0)) as m6
  197.       ,sum(decode(s.months,'07',s.sales_amount,0)) as m7
  198.       ,sum(decode(s.months,'08',s.sales_amount,0)) as m8
  199.       ,sum(decode(s.months,'09',s.sales_amount,0)) as m9
  200.       ,sum(decode(s.months,'10',s.sales_amount,0)) as m10
  201.       ,sum(decode(s.months,'11',s.sales_amount,0)) as m11
  202.       ,sum(decode(s.months,'12',s.sales_amount,0)) as m12
  203.   from sales_amount s
  204.  group by s.years)
  206. select t.years
  207.       ,(t.m1 - lag(m1) over(partition by null order by years))/lag(m1) over(partition by null order by years) as M1_TB_RATE
  208.       ,(t.m2 - lag(m2) over(partition by null order by years))/lag(m2) over(partition by null order by years) as M2_TB_RATE
  209.       ,(t.m3 - lag(m3) over(partition by null order by years))/lag(m3) over(partition by null order by years) as M3_TB_RATE
  210.       ,(t.m4 - lag(m4) over(partition by null order by years))/lag(m4) over(partition by null order by years) as M4_TB_RATE
  211.       ,(t.m5 - lag(m5) over(partition by null order by years))/lag(m5) over(partition by null order by years) as M5_TB_RATE
  212.       ,(t.m6 - lag(m6) over(partition by null order by years))/lag(m6) over(partition by null order by years) as M6_TB_RATE
  213.       ,(t.m7 - lag(m7) over(partition by null order by years))/lag(m7) over(partition by null order by years) as M7_TB_RATE
  214.       ,(t.m8 - lag(m8) over(partition by null order by years))/lag(m8) over(partition by null order by years) as M8_TB_RATE
  215.       ,(t.m9 - lag(m9) over(partition by null order by years))/lag(m9) over(partition by null order by years) as M9_TB_RATE
  216.       ,(t.m10 - lag(m10) over(partition by null order by years))/lag(m10) over(partition by null order by years) as M10_TB_RATE
  217.       ,(t.m11 - lag(m11) over(partition by null order by years))/lag(m11) over(partition by null order by years) as M11_TB_RATE
  218.       ,(t.m12 - lag(m12) over(partition by null order by years))/lag(m12) over(partition by null order by years) as M12_TB_RATE
  219.   from tmp t;