MySQL statistics yoy SQL

General Idea:

MySQL does not have a convenient statistical function like Oracle, can only rely on their own hard computing: through the Time field directly increase the year, the month, and then through the left join associated Time field to calculate the chain, year-over formula can be

Original table structure:

Find sql:

[SQL]View PlainCopyPrint?

2. --by year   
6. SELECT t5.*, Case when t5.last_energy_year is NULL OR t5.last_energy_year =0 Then 0.00
8. ELSE FORMAT (((t5.energy_year-t5.last_energy_year)/t5.last_energy_year) *100,2)
10. END YoY --YoY   
12. from  (   
14. SELECT T3.*,t4.last_energy_year
16. from    
18. (
20. SELECT date_format (CONCAT (t1. ' Tyear ', '-', t1. ' Tmonth ',' -01 '), '%y-%m-%d ' as Yearmonth,t1.tyear,SUM(t1.energy_month) Energy_year, T1.linename
22. from (SELECT
24. t.*,COUNT(DISTINCT T. ' Tyear ', T. ' Tmonth ', T. ' Linename ') C_cot
26. from ' ksh_tgyjy_llxgyjydlxx ' t
28. GROUP by T. ' Linename ', T. ' Tyear ', T. ' Tmonth ', T. ' Linename ') T1
30. GROUP by t1.tyear,t1. ' Linename '
32. ) T3
34. Left JOIN
36. (
38. 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
40. from (SELECT t.*,COUNT(DISTINCT T. ' Tyear ', T. ') Tmonth ', T. ' Linename ') C_cot
42. from ' ksh_tgyjy_llxgyjydlxx ' t
44. GROUP by T. ' Linename ', T. ' Tyear ', T. ' Tmonth ', T. ' Linename ') T1
46. GROUP by t1.tyear,t1. ' Linename '
48. ) T4
50. on T3. Yearmonth = T4.lastyearmonth
52. and t3.linename = T4.linename
54. ) T5

--year-over-years select t5.*, case when T5.last_energy_year was NULL OR t5.last_energy_year =0 then 0.00      &nb Sp         ELSE FORMAT (((t5.energy_year-t5.last_energy_year)/t5.last_energy_year) *100,2)         end yoy  --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& nbsp   on T3. Yearmonth = t4.lastyearmonth    and t3.linename = t4.linename) T5

Query Result:

Find the month of SQL:

[SQL]View PlainCopyPrint?

2. --month-on-year   
6. SELECT Date_format (T5. Yearmonth,'%y-%m ') yearmonth,t5. ' Linename ', T5. ' Energy_month ',
8. Case  when t5.lat_energy_month is NULL OR T5.lat_energy_month=0 then 0.00
10. ELSE FORMAT (((t5.energy_month-t5.lat_energy_month)/t5.lat_energy_month) *100,2)
12. END YoY
14. from  (   
16. SELECT T3.*,t4.energy_month Lat_energy_month
18. from    
20. (
22. SELECT date_format (CONCAT (t1. ' Tyear ', '-', t1. ' Tmonth ',' -01 '), '%y-%m-%d ' ) as yearmonth,t1.*
24. from (SELECT
26. t.*,COUNT(DISTINCT T. ' Tyear ', T. ' Tmonth ', T. ' Linename ') C_cot
28. from ' ksh_tgyjy_llxgyjydlxx ' t
30. GROUP by T. ' Linename ', T. ' Tyear ', T. ' Tmonth ', T. ' Linename ') T1
32. ) T3
34. Left JOIN
36. (
38. SELECT Date_add (Date_format (CONCAT (t1. ' Tyear ', '-', t1. ' Tmonth ',' -01 '), '%y-%m-%d '), INTERVAL 1 year) as lastyearmonth,t1 .*   
40. from (SELECT t.*,COUNT(DISTINCT T. ' Tyear ', T. ' Tmonth ', T. ') Linename ') C_cot
42. from ' ksh_tgyjy_llxgyjydlxx ' t
44. GROUP by T. ' Linename ', T. ' Tyear ', T. ' Tmonth ', T. ' Linename ') T1
46. ) T4
48. on T3. Yearmonth = T4.lastyearmonth
50. and t3.linename = T4.linename
52. ) T5

--month-on-year 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

Query Result:

MySQL statistics yoy SQL