一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第3天，点击查看活动详情。 --每月停发、环比、同比
`SELECT DD.YEARMONTH,
DD.addcount,
TO_CHAR(NVL(ROUND(DECODE(DD.monthOnMonth,
0,
100,
(DD.addcount - DD.monthOnMonth) /
DD.monthOnMonth * 100),
2),
0),
'fm9999999990.00') || '%' 环比,
TO_CHAR(NVL(ROUND(DECODE(DD.yearOnYear,
0,
100,
(DD.addcount - DD.yearOnYear) /
DD.yearOnYear * 100),
2),
0),
'fm9999999990.00') || '%' 同比
FROM (SELECT CC.YEARMONTH,
CC.addcount,
LAG(CC.addcount, 1, 0) OVER(ORDER BY CC.YEARMONTH) AS monthOnMonth,
LAG(CC.addcount, 12, 0) OVER(ORDER BY CC.YEARMONTH) AS yearOnYear
FROM (SELECT AA.YEARMONTH, NVL(BB.addcount, 0) addcount
FROM (SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201901', 'yyyy-MM'),
ROWNUM - 1),
'yyyy-MM') AS YEARMONTH
FROM DUAL
CONNECT BY ROWNUM <=
months_between(to_date('2020-12', 'yyyy-MM'),
to_date('2019-01', 'yyyy-MM')) + 1) AA
LEFT JOIN (select to_char(t.jzdate, 'yyyy-mm') YEARMONTH,
sum(t.addcount) addcount
from JZ_STOPMANAGE t
where t.jztype = 3
and to_char(t.jzdate, 'yyyy') =
TO_CHAR(ADD_MONTHS(sysdate, -12), 'YYYY')
group by to_char(t.jzdate, 'yyyy-mm')
order by to_char(t.jzdate, 'yyyy-mm')) BB
ON AA.YEARMONTH = BB.YEARMONTH
ORDER BY AA.YEARMONTH) CC) DD
WHERE DD.YEARMONTH >= '2020-01'
AND DD.YEARMONTH <= '2020-12';`
结果：
下面介绍sql拆分和编写步骤，对其思路进行说明：
同比环比概念和公式：
同比: 同比一般情况下是今年第n月与去年第n月比
环比: 表示连续2个统计周期（比如连续两月）内的量的变化比。
同比增长率=（本期数-同期数）/|同期数|×100%
环比增长率=（本期数-上期数）/上期数×100%
需求:
计算2020年01月到2020年12月, 每个月的本期值和环比和同比率
数据库表和数据准备【这里可以直接select好需要的数据作为“表”直接用，不用建表】
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 (--上步那串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
) CC
结果: 我们可以观察结果,发现月份都获取到了正确的环比和同比值
第四步: 计算出环比和同比的增长率
按照公式：
同比增长率=（本期数-同期数）/|同期数|×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`
