快捷導(dǎo)航

MySQL中如何計(jì)算同比和環(huán)比

更新時(shí)間：2022年06月22日 16:33:33 作者：我菜的要死

在工作的過(guò)程中,經(jīng)常會(huì)使用到環(huán)比、同比,下面這篇文章主要給大家介紹了關(guān)于MySQL中如何計(jì)算同比和環(huán)比的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

前言

今天在做數(shù)據(jù)建模的時(shí)候，ads層的需求中有個(gè)叫同比和環(huán)比的指標(biāo)，這兩指標(biāo)的計(jì)算之前都沒(méi)有接觸過(guò)，經(jīng)過(guò)我兩三個(gè)小時(shí)的研究，終于搞明白什么是同比，什么是環(huán)比，如何使用Mysql來(lái)計(jì)算同比和環(huán)比。

我們先來(lái)看看什么是同比，什么是環(huán)比：

同比：通常是指今年第n月與去年第n月相比。同比發(fā)展速度主要是為了消除季節(jié)變動(dòng)的影響，用以說(shuō)明本期發(fā)展水平與去年同期發(fā)展水平對(duì)比而達(dá)到的相對(duì)發(fā)展速度。

環(huán)比：通常是指表示連續(xù)2個(gè)單位周期（比如連續(xù)兩月）內(nèi)的量的變化比。環(huán)比包括兩種：環(huán)比增長(zhǎng)速度和環(huán)比發(fā)展速度。

那同比增長(zhǎng)率和環(huán)比增長(zhǎng)率又如何計(jì)算呢：

同比增長(zhǎng)率：

同比增長(zhǎng) 率 = ( 本期數(shù) − 同期數(shù) ) / 同期數(shù) ∗ 100 %

環(huán)比增長(zhǎng)率：

環(huán) 比增長(zhǎng) 率 = ( 本期數(shù) − 上期數(shù) ) / 上期數(shù) × 100 %

同比和環(huán)比的區(qū)別

同比是本期與同期做對(duì)比，環(huán)比是本期與上期做對(duì)比。
環(huán)比一般是用在月、日很少用在年上，主要是對(duì)比很短時(shí)間內(nèi)漲幅程度，不過(guò)由于行業(yè)差異，比如旅游，會(huì)受到淡旺季影響。
同比一般用在相鄰兩年，相同時(shí)間段內(nèi)，查看漲幅程度，一般用在兩年相同月份，很少用在兩月相同日期。

在MySQL中如何計(jì)算同比和環(huán)比

數(shù)據(jù)準(zhǔn)備

創(chuàng)建商品表并向商品表中添加數(shù)據(jù)

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

創(chuàng)建訂單明細(xì)表并向訂單明細(xì)表中添加數(shù)據(jù)

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

計(jì)算同比和環(huán)比

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

sql解析

select  year(c.銷售時(shí)間) yy,month(c.銷售時(shí)間) mm,     
       sum(c.銷售數(shù)量*d.產(chǎn)品單價(jià)) ss,
 
# concat函數(shù)，mysql字符串拼接，因?yàn)橥群铜h(huán)比都是百分?jǐn)?shù)
# ifnull函數(shù)，mysql判斷字段是否為空，為空則為0
# abs函數(shù),mysql取絕對(duì)值，因?yàn)槲疫@里取的都是正數(shù)
# round函數(shù)，mysql保留幾位小數(shù)
concat(ifnull(abs(round((sum(c.銷售數(shù)量*d.產(chǎn)品單價(jià))-ss1)/ss1*100,2)),0),'%') 同比,
concat(ifnull(abs(round((sum(c.銷售數(shù)量*d.產(chǎn)品單價(jià))-ss2)/ss2*100,2)),0),'%')  環(huán)比
from sales c
left join product d on c.產(chǎn)品ID=d.產(chǎn)品ID
 
# 上一年銷售額
left join (select month(a.銷售時(shí)間) mm1,
                    year(a.銷售時(shí)間) yy1,
                    sum(a.銷售數(shù)量*d.產(chǎn)品單價(jià)) ss1
          from sales a
          left join product d on a.產(chǎn)品ID=d.產(chǎn)品ID
          GROUP BY mm1,yy1) a
          # 同比月份相同，年份減1
          on month(c.銷售時(shí)間) = a.mm1   
          and a.yy1 = year(c.銷售時(shí)間)-1	
 
# 今年銷售額 
 left join  (select month(a.銷售時(shí)間) mm2,
                    year(a.銷售時(shí)間) yy2,
                    sum(a.銷售數(shù)量*d.產(chǎn)品單價(jià)) ss2
             from sales a
		     left join product d on a.產(chǎn)品ID=d.產(chǎn)品ID
              GROUP BY mm2,yy2) b
          # 環(huán)比取數(shù)考慮到為一月的情況
on (b.yy2 = year(c.銷售時(shí)間) and b.mm2+1 = month(c.銷售時(shí)間) OR (yy2=year(c.銷售時(shí)間)-1 
AND b.mm2 = 12 AND month(c.銷售時(shí)間) = 1))
 group by yy, mm
 order by yy,mm asc

結(jié)果：

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

select year(銷售時(shí)間) yy,month(銷售時(shí)間) mm,
sum(銷售數(shù)量*b.產(chǎn)品單價(jià)) over(order by year(銷售時(shí)間) ,month(銷售時(shí)間) ) 累計(jì)數(shù)量 
from sales a
left join product b on a.產(chǎn)品ID=b.產(chǎn)品ID
order by  yy,mm

同比和環(huán)比這塊的確不好計(jì)算