同比环比概念和公式

同比: 同比一般情况下是今年第n月与去年第n月比

环比: 表示连续2个统计周期（比如连续两月）内的量的变化比。

同比增长率=（本期数-同期数）/|同期数|×100%

环比增长率=（本期数-上期数）/上期数×100%

需求:

计算2020年01月到2020年2月, 每个月的本期值和环比和同比率

数据库 表和数据准备

CREATE TABLE test (
  "ID" NUMBER NOT NULL ,
  "VALUE" NUMBER ,
  "YEARMONTH" VARCHAR2(6 CHAR) ,
  CONSTRAINT "SYS_C0086930" PRIMARY KEY ("ID")
)


INSERT INTO "test"("ID", "VALUE", "YEARMONTH") VALUES ('1', '100', '202002');
INSERT INTO "test"("ID", "VALUE", "YEARMONTH") VALUES ('2', '80', '202001');
INSERT INTO "test"("ID", "VALUE", "YEARMONTH") VALUES ('3', '120', '201912');
INSERT INTO "test"("ID", "VALUE", "YEARMONTH") VALUES ('4', '142', '201911');
INSERT INTO "test"("ID", "VALUE", "YEARMONTH") VALUES ('5', '70', '201910');
INSERT INTO "test"("ID", "VALUE", "YEARMONTH") VALUES ('6', '51', '201901');

oracle 需要用到的函数简绍:

NVL(expr1, expr2)

expr1为空时, 给默认值expr2

TO_CHAR(number,‘格式’)

  格式化函数 如:FM99990.00 
	9代表此位为0或没有值时,不显示. 0代表此位为0或者没有值时补0. 
    小数点前是99990五位,代表数字的整数位只能有5位,小数位00表示,保留2位小数

ROUND（number[,decimals]）

number 待做截取处理的数值
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分，并四舍五入。
如果为负数则表示从小数点开始左边的位数，相应整数数字用0填充，小数被去掉. 
对截取的数字要四舍五入。

DECODE(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

该函数的含义如下：


IF 条件=值1 THEN
　　　　RETURN(返回值1)
ELSIF 条件=值2 THEN
　　　　RETURN(返回值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(返回值n)
ELSE
　　　　RETURN(缺省值)
END IF

计算同比和环比最重要的函数

LAG(exp_str,offset,default) OVER(partition by expr2 order by expr3)

LEAD(exp_str,offset,default) OVER(partition by expr2 order by expr3)

exp_str 要做对比的字段
offset 偏移量
default 默认值

partition by expr2 根据expr2分组
order by expr3 根据expr3排序

LAG函数: 向上取offset行的数据中exp_str字段,如果没有取default
LEAD函数: 向下取offset行的数据种exp_str字段,如果没有取default
OVER函数: 先对数据先进行分组和排序. 如: 这次测试,我们用日期进行排序,保证了LAG函数向上取offset行,能取到
对应的环比和同比的日期的值

再简绍一下,如何获取一个时间段内连续的日期

这样保证了如果数据库中有些日期没有数据时,可以给一个默认值,使LAG和LEAD函数可以获取到正确的行数

SELECT AA.monthlist, NVL(BB.CAP,0) CAP FROM -- 获取需要查询的时间范围内 连续月份的 数据值
(SELECT
  TO_CHAR( ADD_MONTHS( TO_DATE( '201901', 'yyyyMM' ), ROWNUM - 1 ), 'yyyyMM' ) AS monthlist 
FROM
  DUAL CONNECT BY ROWNUM <= months_between(
    to_date( '202001', 'yyyyMM' ),
    to_date( '201901', 'yyyyMM' )) + 1

思路:

第一步: 将需要的业务数据计算出来. 也就是将我们数据库中有的数据,按照日期分组.

​ 注意: 在这里我们从业务数据表中获取数据的时间范围,并不是我们需要求的2020年01月到2020年2月的数据,我们想要计算得到同比值那就必须得到最早开始时间(2020年1月)向前推12月的日期,即2019年1月. 这样我们才能

同比增长率=（本期数2020年1月 - 同期数2019年1月 ）/ 同期数2019年1月 ×100% .

环比值所需要的数据(2019年12月)已经在范围内

​

SELECT YEARMONTH, NVL(SUM(VALUE), 0) VALUE FROM  "test" 
WHERE YEARMONTH >=  201901 AND YEARMONTH <= 202002 
GROUP BY YEARMONTH ORDER BY YEARMONTH

结果:

YEARMONTH	VALUE
201901		 51
201910		 70
201911		 142
201912		 120
202001		 80
202002		 100

第二步: 用我们上面简绍的获取连续月份的sql和上一步我们计算出来的业务数据连表, 并与连续月份的临时表为主表.

​ 这样我们就可以得到,我们需要的时间范围只能的所有日期和它的值

​ 注意: ①获取连续日期的sql的时间范围和上一步的相同,都必须包含到同比值

SELECT AA.YEARMONTH, NVL(BB.VALUE, 0) VALUE FROM 
	(SELECT
	TO_CHAR( ADD_MONTHS( TO_DATE( '201901', 'yyyyMM' ), ROWNUM - 1 ), 'yyyyMM' ) AS YEARMONTH 
	FROM DUAL 
	CONNECT BY ROWNUM <= months_between( to_date( '202002', 'yyyyMM' ), to_date( '201901', 'yyyyMM' )) + 1) AA

LEFT JOIN
	(
		SELECT YEARMONTH, NVL(SUM(VALUE), 0) VALUE FROM  "test" 
		WHERE YEARMONTH >=  201901 AND YEARMONTH <= 202002 
		GROUP BY YEARMONTH ORDER BY YEARMONTH
	) BB 
ON AA.YEARMONTH = BB.YEARMONTH	ORDER BY  AA.YEARMONTH

结果:

YEARMONTH	VALUE
201901		51
201902		0
201903		0
201904		0
201905		0
201906		0
201907		0
201908		0
201909		0
201910		70
201911		142
201912		120
202001		80
202002		100

第三步: 使用LAG或LEAD函数,使现值,环比值(month-on-month)和同比值(year-on-year)成为一行数据.

注意: 我们这里使用的LAG**(exp_str,offset,default)**函数, 它向上取offset行的值exp_str,如果没有就是default.

SELECT
CC.YEARMONTH,
CC.VALUE,
LAG ( CC.VALUE, 1, 0 ) OVER (  ORDER BY CC.YEARMONTH ) AS monthOnMonth,
LAG ( CC.VALUE, 12, 0 ) OVER (  ORDER BY CC.YEARMONTH ) AS yearOnYear
FROM (
	SELECT AA.YEARMONTH, NVL(BB.VALUE, 0) VALUE FROM 
		(SELECT
		TO_CHAR( ADD_MONTHS( TO_DATE( '201901', 'yyyyMM' ), ROWNUM - 1 ), 'yyyyMM' ) AS YEARMONTH 
		FROM DUAL 
		CONNECT BY ROWNUM <= months_between( to_date( '202002', 'yyyyMM' ), to_date( '201901', 'yyyyMM' )) + 1) AA

	LEFT JOIN
		(
			SELECT YEARMONTH, NVL(SUM(VALUE), 0) VALUE FROM  "test" 
			WHERE YEARMONTH >=  201901 AND YEARMONTH <= 202002 
			GROUP BY YEARMONTH ORDER BY YEARMONTH
		) BB 
	ON AA.YEARMONTH = BB.YEARMONTH	ORDER BY  AA.YEARMONTH

) CC

结果: 我们可以观察结果,发现月份都获取到了正确的环比和同比值

YEARMONTH	VALUE	MONTHONMONTH	YEARONYEAR
201901		 51			 0				0
201902		 0			 51				0
201903		 0			 0				0
201904		 0			 0				0
201905		 0			 0				0
201906		 0			 0				0
201907		 0			 0				0
201908		 0			 0				0
201909		 0			 0				0
201910   	 70	         0				0
201911		 142	     70				0
201912		 120		 142			0
202001		80			 120			51
202002		100			 80				0

第四步: 计算出环比和同比的增长率

按照公式

同比增长率=（本期数-同期数）/|同期数|×100%

环比增长率=（本期数-上期数）/上期数×100%

注意: ① 我们在上一步中可以看到,我们的环比和同比值有可能为0, 但是根据公式,环比和同比值需要做被除数,而被除数又不能等于0,所以我们需要使用DECODE函数,如何值为0,环比和同比的增长率就为0

​ ② 在整个sql的最后面我们要加一个时间范围条件. 因为我们之前计算的都是根据最大日期范围计算的. 但是我们所需要的仅仅是 2020年1月到2020年2月的数据,所以加上时间范围条件.只获取我们所需的数据.

SELECT 
DD.YEARMONTH,
DD.VALUE,
TO_CHAR(NVL(ROUND(DECODE(DD.monthOnMonth,0,100,(DD.VALUE - DD.monthOnMonth )/DD.monthOnMonth * 100),2), 0), 'fm9999999990.00') || '%' 环比增长率,
TO_CHAR(NVL(ROUND(DECODE(DD.yearOnYear,0,100,(DD.VALUE - DD.yearOnYear)/DD.yearOnYear * 100),2), 0),'fm9999999990.00')  || '%' 同比增长率
FROM (
	SELECT
	CC.YEARMONTH,
	CC.VALUE,
	LAG ( CC.VALUE, 1, 0 ) OVER (  ORDER BY CC.YEARMONTH ) AS monthOnMonth,
	LAG ( CC.VALUE, 12, 0 ) OVER (  ORDER BY CC.YEARMONTH ) AS yearOnYear
	FROM (
		SELECT AA.YEARMONTH, NVL(BB.VALUE, 0) VALUE FROM 
			(SELECT
			TO_CHAR( ADD_MONTHS( TO_DATE( '201901', 'yyyyMM' ), ROWNUM - 1 ), 'yyyyMM' ) AS YEARMONTH 
			FROM DUAL 
			CONNECT BY ROWNUM <= months_between( to_date( '202002', 'yyyyMM' ), to_date( '201901', 'yyyyMM' )) + 1) AA

		LEFT JOIN
			(
				SELECT YEARMONTH, NVL(SUM(VALUE), 0) VALUE FROM  "test" 
				WHERE YEARMONTH >=  201901 AND YEARMONTH <= 202002 
				GROUP BY YEARMONTH ORDER BY YEARMONTH
			) BB 
		ON AA.YEARMONTH = BB.YEARMONTH	ORDER BY  AA.YEARMONTH

	) CC
) DD WHERE DD.YEARMONTH >= 202001 AND DD.YEARMONTH <=202002

就是最终的推导出的sql,该sql还可以优化和简化, sql优化和简化不是本篇的重点,如有需要请根据实际情况处理.