MySQL統計同比環比SQL

標籤：int   sso   blog   ack   out   copyto   edit   add   div   

大體思路：

MySQL沒有類似oracle方便的統計函數，只能靠自己去硬計算：通過時間欄位直接增加年份、月份，然後通過left join關聯時間欄位去計算環比、同比公式即可

原始表結構：

求同比SQL：

[sql] view plain copy print?

1. -- 按年同比  
2.   
3. SELECT t5.*, CASE WHEN t5.last_energy_year IS NULL OR t5.last_energy_year =0 THEN 0.00  
4.                 ELSE FORMAT(((t5.energy_year - t5.last_energy_year)/t5.last_energy_year)*100,2)   
5.         END YoY  -- 同比  
6. FROM (   
7.    SELECT t3.*,t4.last_energy_year  
8.     FROM   
9.     (  
10.        SELECT DATE_FORMAT(CONCAT(t1.`tyear`, ‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘) AS YearMonth,t1.tyear,SUM(t1.energy_month) energy_year,t1.linename  
11.         FROM(SELECT   
12.         t.*,COUNT(DISTINCT t.`tyear`,t.`tmonth`,t.`linename`) c_cot   
13.         FROM `ksh_tgyjy_llxgyjydlxx` t   
14.         GROUP BY t.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1  
15.         GROUP BY t1.tyear,t1.`linename`  
16.      ) t3   
17.       LEFT JOIN   
18.     (  
19.       SELECT DATE_ADD(DATE_FORMAT(CONCAT(t1.`tyear`, ‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘),INTERVAL 1 YEAR) AS lastYearMonth,t1.tyear,SUM(t1.energy_month) last_energy_year,t1.linename  
20.         FROM(SELECT t.*,COUNT(DISTINCT t.`tyear`,t.`tmonth`,t.`linename`) c_cot   
21.         FROM `ksh_tgyjy_llxgyjydlxx` t   
22.         GROUP BY t.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1  
23.         GROUP BY t1.tyear,t1.`linename`  
24.     ) t4  
25.     ON t3.YearMonth = t4.lastYearMonth  
26.     AND t3.linename = t4.linename   
27. ) t5  

-- 按年同比SELECT t5.*, CASE WHEN t5.last_energy_year IS NULL OR t5.last_energy_year =0 THEN 0.00                ELSE FORMAT(((t5.energy_year - t5.last_energy_year)/t5.last_energy_year)*100,2)         END YoY  -- 同比FROM (    SELECT t3.*,t4.last_energy_year    FROM     (       SELECT DATE_FORMAT(CONCAT(t1.`tyear`, ‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘) AS YearMonth,t1.tyear,SUM(t1.energy_month) energy_year,t1.linename        FROM(SELECT         t.*,COUNT(DISTINCT t.`tyear`,t.`tmonth`,t.`linename`) c_cot         FROM `ksh_tgyjy_llxgyjydlxx` t         GROUP BY t.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1        GROUP BY t1.tyear,t1.`linename`     ) t3       LEFT JOIN     (      SELECT DATE_ADD(DATE_FORMAT(CONCAT(t1.`tyear`, ‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘),INTERVAL 1 YEAR) AS lastYearMonth,t1.tyear,SUM(t1.energy_month) last_energy_year,t1.linename        FROM(SELECT t.*,COUNT(DISTINCT t.`tyear`,t.`tmonth`,t.`linename`) c_cot         FROM `ksh_tgyjy_llxgyjydlxx` t         GROUP BY t.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1        GROUP BY t1.tyear,t1.`linename`    ) t4    ON t3.YearMonth = t4.lastYearMonth    AND t3.linename = t4.linename ) t5

查詢結果：

求月環比SQL：

[sql] view plain copy print?

1. -- 按月同比  
2.   
3. SELECT DATE_FORMAT(t5.YearMonth,‘%Y-%m‘) YearMonth,t5.`linename`,t5.`energy_month`,   
4.          CASE WHEN t5.lat_energy_month IS NULL OR t5.lat_energy_month=0 THEN 0.00  
5.                  ELSE FORMAT(((t5.energy_month - t5.lat_energy_month)/t5.lat_energy_month)*100,2)   
6.         END YoY  
7. FROM (   
8.    SELECT t3.*,t4.energy_month lat_energy_month  
9.      FROM   
10.     (  
11.       SELECT DATE_FORMAT(CONCAT(t1.`tyear`, ‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘) AS YearMonth,t1.*  
12.         FROM(SELECT   
13.           t.*,COUNT(DISTINCT t.`tyear`,t.`tmonth`,t.`linename`) c_cot   
14.           FROM `ksh_tgyjy_llxgyjydlxx` t   
15.           GROUP BY t.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1  
16.      ) t3   
17.        LEFT JOIN   
18.     (  
19.       SELECT DATE_ADD(DATE_FORMAT(CONCAT(t1.`tyear`, ‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘),INTERVAL 1 YEAR) AS lastYearMonth,t1.*   
20.         FROM(SELECT t.*,COUNT(DISTINCT t.`tyear`,t.`tmonth`,t.`linename`) c_cot   
21.         FROM `ksh_tgyjy_llxgyjydlxx` t   
22.         GROUP BY t.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1  
23.     ) t4  
24.     ON t3.YearMonth = t4.lastYearMonth  
25.     AND t3.linename = t4.linename  
26. ) t5  

-- 按月同比SELECT DATE_FORMAT(t5.YearMonth,‘%Y-%m‘) YearMonth,t5.`linename`,t5.`energy_month`,          CASE WHEN t5.lat_energy_month IS NULL OR t5.lat_energy_month=0 THEN 0.00                 ELSE FORMAT(((t5.energy_month - t5.lat_energy_month)/t5.lat_energy_month)*100,2)         END YoYFROM (    SELECT t3.*,t4.energy_month lat_energy_month     FROM     (      SELECT DATE_FORMAT(CONCAT(t1.`tyear`, ‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘) AS YearMonth,t1.*        FROM(SELECT           t.*,COUNT(DISTINCT t.`tyear`,t.`tmonth`,t.`linename`) c_cot           FROM `ksh_tgyjy_llxgyjydlxx` t           GROUP BY t.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1     ) t3        LEFT JOIN     (      SELECT DATE_ADD(DATE_FORMAT(CONCAT(t1.`tyear`, ‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘),INTERVAL 1 YEAR) AS lastYearMonth,t1.*         FROM(SELECT t.*,COUNT(DISTINCT t.`tyear`,t.`tmonth`,t.`linename`) c_cot         FROM `ksh_tgyjy_llxgyjydlxx` t         GROUP BY t.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1    ) t4    ON t3.YearMonth = t4.lastYearMonth    AND t3.linename = t4.linename) t5

查詢結果：

MySQL統計同比環比SQL