oracle分析函数（环比、同比、递增求和）

1.普及一下概念

环比 = 2018年10月/2018年09月（同一时期内不同时间段的比较）

同比 = 2018年10月/2017年10月（不同时期内相同时间段的比较）

环比增长率 =  （2018年10月-2018年09月）/2018年09月

同比增长率 =  （2018年10月-2017年10月）/2017年10月

2.关键sql 解释

(1)分析函数 

lead(params,m,n)  以params为目标向下m位取数，当取不到时默认为 n,

lag(params,m,n)  以params为目标向上m位取数，当取不到时默认为 n

over(order by...) 相当于 order by...

partition by ... 根据参数最为分割，将相同的先分组到一块 （ 可以不要 ）

 

---

 

 

同比环比概念和公式
同比: 同比一般情况下是今年第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');

 

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

第二步: 用我们上面简绍的获取连续月份的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

第三步: 使用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

第四步: 计算出环比和同比的增长率
按照公式

同比增长率=（本期数-同期数）/|同期数|×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优化和简化不是本篇的重点,如有需要请根据实际情况处理.

————————————————
原文链接：https://blog.csdn.net/qq_35976271/article/details/107157769