隔兩行生一行計算變化率

壹頁書發表於2016-10-25
今天宋三胖宋老師發了他們單位的一個需求原型.


他這個原型似乎是每兩天，計算一個變化率. 資料都是示意的.

實際應該是如下圖所示


第三行變化率
計算公式
    (第一行 - 第二行) / 第一行

他們大資料,資料倉儲的需求我也不是很明白.

實驗資料準備.
  1. CREATE TABLE `t` (  
  2.   `k` varchar(20) NOT NULL,  
  3.   `v1` float DEFAULT NULL,  
  4.   `v2` float DEFAULT NULL,  
  5.   PRIMARY KEY (`k`)  
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  
  7. INSERT INTO `t` (`k`,`v1`,`v2`) VALUES ('20160902',1.62,1.07);  
  8. INSERT INTO `t` (`k`,`v1`,`v2`) VALUES ('20160903',1.97,1.43);  
  9. INSERT INTO `t` (`k`,`v1`,`v2`) VALUES ('20160904',2.86,2.36);  
  10. INSERT INTO `t` (`k`,`v1`,`v2`) VALUES ('20160905',2.61,3.62);  
  11. INSERT INTO `t` (`k`,`v1`,`v2`) VALUES ('20160906',1.36,2.96);  
  12. INSERT INTO `t` (`k`,`v1`,`v2`) VALUES ('20160907',5.46,4.24);  
  13. INSERT INTO `t` (`k`,`v1`,`v2`) VALUES ('20160908',4.29,6.12);  
  14. INSERT INTO `t` (`k`,`v1`,`v2`) VALUES ('20160909',2.97,1.3);  
  15. INSERT INTO `t` (`k`,`v1`,`v2`) VALUES ('20160910',5.06,2.26);  
  16. INSERT INTO `t` (`k`,`v1`,`v2`) VALUES ('20160911',4.46,4.11); 

建立數字輔助表
  1. create table nums(id int not null primary key);  
  2.   
  3. INSERT INTO `nums` (`id`) VALUES (1);  
  4. INSERT INTO `nums` (`id`) VALUES (2);  
  5. INSERT INTO `nums` (`id`) VALUES (3);  
  6. INSERT INTO `nums` (`id`) VALUES (4);  
  7. INSERT INTO `nums` (`id`) VALUES (5);  
  8. INSERT INTO `nums` (`id`) VALUES (6);  
  9. INSERT INTO `nums` (`id`) VALUES (7);  
  10. INSERT INTO `nums` (`id`) VALUES (8);  
  11. INSERT INTO `nums` (`id`) VALUES (9);  
  12. INSERT INTO `nums` (`id`) VALUES (10);  
  13. INSERT INTO `nums` (`id`) VALUES (11);  
  14. INSERT INTO `nums` (`id`) VALUES (12);  
  15. INSERT INTO `nums` (`id`) VALUES (13);  
  16. INSERT INTO `nums` (`id`) VALUES (14);  
  17. INSERT INTO `nums` (`id`) VALUES (15);  
  18. INSERT INTO `nums` (`id`) VALUES (16);  
  19. INSERT INTO `nums` (`id`) VALUES (17);  
  20. INSERT INTO `nums` (`id`) VALUES (18);  
  21. INSERT INTO `nums` (`id`) VALUES (19);  
  22. INSERT INTO `nums` (`id`) VALUES (20); 

計算:



  1. select   
  2.             ifnull(k, '變化率') k,  
  3.             ifnull(round(v1,4), @r1) r1,  
  4.             ifnull(round(v2,4), @r2) r2,  
  5.             @r1:=r1,  
  6.             @r2:=r2  
  7. from(  
  8.     select   
  9.         a.*  
  10.     from  
  11.         nums n  
  12.      left outer join    (  
  13.         select   
  14.             t1.k,  
  15.             t1.v1,  
  16.             t1.v2,  
  17.             round(@v1,4) as pv1,  
  18.             round(@v2,4) as pv2,  
  19.             round(@v1-v1/@v1,4) r1,  
  20.             round(@v2-v2/@v2,4) r2,  
  21.             @v1:=t1.v1 ,  
  22.             @v2:=t1.v2 ,  
  23.             @a:=@a + if(@a mod 3 = 0, 2, 1),  
  24.             @a - 1 rn  
  25.         from  
  26.             (select @a:=0, @v1:=null, @v2:=null,@r1:=null,@r2:=null) var ,t t1  
  27.         order by k   
  28.     ) a ON (n.id = a.rn)  
  29.     WHERE  
  30.         n.id <= @a  
  31.     order by id  
  32. ) f  
  33. ; 

變數@a 如果遇到需要計算的"第三行" 則加 2.
然後用數字輔助表外連線,這樣 計算生成的行就出來了。
最後用自定義變數,把值填上.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2127080/，如需轉載，請註明出處，否則將追究法律責任。

相關文章