MySQL中如何計算同比和環比

Posted on by

前言

今天在做數據建模的時候，ads層的需求中有個叫同比和環比的指標，這兩指標的計算之前都沒有接觸過，經過我兩三個小時的研究，終於搞明白什麼是同比，什麼是環比，如何使用Mysql來計算同比和環比。

我們先來看看什麼是同比，什麼是環比：

同比：通常是指今年第n月與去年第n月相比。同比發展速度主要是為瞭消除季節變動的影響，用以說明本期發展水平與去年同期發展水平對比而達到的相對發展速度。

環比：通常是指表示連續2個單位周期（比如連續兩月）內的量的變化比。環比包括兩種：環比增長速度和環比發展速度。

那同比增長率和環比增長率又如何計算呢：

同比增長率：

同 比 增 長 率 = ( 本 期 數 − 同 期 數 ) / 同 期 數 ∗ 100 % 

環比增長率：

環 比 增 長 率 = ( 本 期 數 − 上 期 數 ) / 上 期 數 × 100 % 

同比和環比的區別

  • 同比是本期與同期做對比，環比是本期與上期做對比。
  • 環比一般是用在月、日很少用在年上，主要是對比很短時間內漲幅程度，不過由於行業差異，比如旅遊，會受到淡旺季影響。
  • 同比一般用在相鄰兩年，相同時間段內，查看漲幅程度，一般用在兩年相同月份，很少用在兩月相同日期。

在MySQL中如何計算同比和環比

數據準備

創建商品表並向商品表中添加數據

CREATE TABLE product  (
  `產品ID` varchar(20) NOT NULL,
	  `產品名稱` varchar(20) ,
  `產品單價` int (10)
)
INSERT INTO product VALUES ('C1001','產品A',45);
INSERT INTO product VALUES ('C1002','產品B',52);
INSERT INTO product VALUES ('C1003','產品C',39);

image-20220317095350813

創建訂單明細表並向訂單明細表中添加數據

CREATE TABLE sales  (
    `訂單ID` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `產品ID` varchar(25) NOT NULL ,
	  `銷售數量` int(20) ,
  `銷售時間` timestamp(6) NULL DEFAULT NULL
);
INSERT INTO sales(`產品ID`,`銷售數量`,`銷售時間`) VALUES ('C1001', 15, '2020-06-01 10:10:12');
INSERT INTO sales(`產品ID`,`銷售數量`,`銷售時間`)  VALUES ('C1002',26, '2020-05-02 0:10:12');
INSERT INTO sales (`產品ID`,`銷售數量`,`銷售時間`) VALUES ('C1003', 21, '2020-04-03 0:10:12');
INSERT INTO sales (`產品ID`,`銷售數量`,`銷售時間`) VALUES ('C1003', 23, '2020-04-04 0:10:12');
INSERT INTO sales (`產品ID`,`銷售數量`,`銷售時間`) VALUES ('C1003', 0, '2020-03-05 0:10:12');
INSERT INTO sales (`產品ID`,`銷售數量`,`銷售時間`) VALUES ('C1001', 16, '2020-02-06 3:0:12');
INSERT INTO sales (`產品ID`,`銷售數量`,`銷售時間`) VALUES ('C1002', 32, '2020-01-07 0:10:12');
INSERT INTO sales (`產品ID`,`銷售數量`,`銷售時間`) VALUES ('C1001', 16, '2019-12-08 0:12:24');
INSERT INTO sales (`產品ID`,`銷售數量`,`銷售時間`) VALUES ('C1001', 32, '2019-06-09  0:12:24');
INSERT INTO sales (`產品ID`,`銷售數量`,`銷售時間`) VALUES ('C1002', 17, '2019-05-09 0:12:24');

image-20220317121540101

計算同比和環比

select  year(c.銷售時間) yy,month(c.銷售時間) mm,     
       sum(c.銷售數量*d.產品單價) ss,
concat(ifnull(abs(round((sum(c.銷售數量*d.產品單價)-ss1)/ss1*100,2)),0),'%') 同比,
concat(ifnull(abs(round((sum(c.銷售數量*d.產品單價)-ss2)/ss2*100,2)),0),'%')  環比
from sales c
left join product d on c.產品ID=d.產品ID
left join (select month(a.銷售時間) mm1,
                    year(a.銷售時間) yy1,
                    sum(a.銷售數量*d.產品單價) ss1
          from sales a
          left join product d on a.產品ID=d.產品ID
          GROUP BY mm1,yy1) a
          on month(c.銷售時間) = a.mm1 
          and a.yy1 = year(c.銷售時間)-1	 
 left join  (select month(a.銷售時間) mm2,
                    year(a.銷售時間) yy2,
                    sum(a.銷售數量*d.產品單價) ss2
             from sales a
		     left join product d on a.產品ID=d.產品ID
              GROUP BY mm2,yy2) b
on (b.yy2 = year(c.銷售時間) and b.mm2+1 = month(c.銷售時間) OR (yy2=year(c.銷售時間)-1 
AND b.mm2 = 12 AND month(c.銷售時間) = 1))
 group by yy, mm
 order by yy,mm asc

sql解析

select  year(c.銷售時間) yy,month(c.銷售時間) mm,     
       sum(c.銷售數量*d.產品單價) ss,
 
# concat函數，mysql字符串拼接，因為同比和環比都是百分數
# ifnull函數，mysql判斷字段是否為空，為空則為0
# abs函數,mysql取絕對值，因為我這裡取的都是正數
# round函數，mysql保留幾位小數
concat(ifnull(abs(round((sum(c.銷售數量*d.產品單價)-ss1)/ss1*100,2)),0),'%') 同比,
concat(ifnull(abs(round((sum(c.銷售數量*d.產品單價)-ss2)/ss2*100,2)),0),'%')  環比
from sales c
left join product d on c.產品ID=d.產品ID
 
# 上一年銷售額
left join (select month(a.銷售時間) mm1,
                    year(a.銷售時間) yy1,
                    sum(a.銷售數量*d.產品單價) ss1
          from sales a
          left join product d on a.產品ID=d.產品ID
          GROUP BY mm1,yy1) a
          # 同比月份相同，年份減1
          on month(c.銷售時間) = a.mm1   
          and a.yy1 = year(c.銷售時間)-1	
 
# 今年銷售額 
 left join  (select month(a.銷售時間) mm2,
                    year(a.銷售時間) yy2,
                    sum(a.銷售數量*d.產品單價) ss2
             from sales a
		     left join product d on a.產品ID=d.產品ID
              GROUP BY mm2,yy2) b
          # 環比取數考慮到為一月的情況
on (b.yy2 = year(c.銷售時間) and b.mm2+1 = month(c.銷售時間) OR (yy2=year(c.銷售時間)-1 
AND b.mm2 = 12 AND month(c.銷售時間) = 1))
 group by yy, mm
 order by yy,mm asc

結果：

image-20220317121641787

除此之外，還想要計算累計銷售的話，可以這樣寫：

select year(銷售時間) yy,month(銷售時間) mm,
sum(銷售數量*b.產品單價) over(order by year(銷售時間) ,month(銷售時間) ) 累計數量 
from sales a
left join product b on a.產品ID=b.產品ID
order by  yy,mm

同比和環比這塊的確不好計算

總結

到此這篇關於MySQL中如何計算同比和環比的文章就介紹到這瞭,更多相關MySQL計算同比和環比內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：