ORACLE——一条SQL计算同比、环比

60 阅读 0 评论 40 点赞

我是靠谱客的博主迷路曲奇，这篇文章主要介绍ORACLE——一条SQL计算同比、环比，现在分享给大家，希望可以做个参考。

按照月份进行计算：

复制代码

SELECT 
D.YEARMONTH,
D.VALUE,
TO_CHAR(NVL(ROUND(DECODE(D.monthOnMonth,0,NULL,(D.VALUE - D.monthOnMonth )/D.monthOnMonth * 100),2), 0), 'fm9999999990.00') || '%' 环比增长率,
TO_CHAR(NVL(ROUND(DECODE(D.yearOnYear,0,NULL,(D.VALUE - D.yearOnYear)/D.yearOnYear * 100),2), 0),'fm9999999990.00')  || '%' 同比增长率
FROM (
    SELECT
    C.YEARMONTH,
    C.VALUE,
    LAG (C.VALUE, 1, 0) OVER (ORDER BY C.YEARMONTH) AS monthOnMonth,
    LAG (C.VALUE, 12, 0) OVER (ORDER BY C.YEARMONTH) AS yearOnYear
    FROM (
        SELECT A.YEARMONTH, NVL(B.VALUE, 0) VALUE FROM 
            (SELECT TO_CHAR( ADD_MONTHS( TO_DATE( '202101', 'yyyyMM' ), ROWNUM - 1), 'yyyyMM') AS YEARMONTH FROM DUAL CONNECT BY ROWNUM <= months_between( to_date('202212', 'yyyyMM'), to_date( '202101', 'yyyyMM')) + 1) A
        LEFT JOIN
            (
                SELECT TO_CHAR(CYJSSJ, 'yyyyMM') YEARMONTH, NVL(COUNT(*), 0) VALUE FROM BA_JBXX WHERE TO_CHAR(CYJSSJ, 'yyyyMM') >=  202101 AND TO_CHAR(CYJSSJ, 'yyyyMM') <= 202212 
                GROUP BY TO_CHAR(CYJSSJ, 'yyyyMM') ORDER BY TO_CHAR(CYJSSJ, 'yyyyMM')
            ) B 
        ON A.YEARMONTH = B.YEARMONTH ORDER BY A.YEARMONTH
    ) C
) D WHERE D.YEARMONTH >= 202001 AND D.YEARMONTH <= 202212

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT 
D.YEARMONTH,
D.VALUE,
TO_CHAR(NVL(ROUND(DECODE(D.monthOnMonth,0,NULL,(D.VALUE - D.monthOnMonth )/D.monthOnMonth * 100),2), 0), 'fm9999999990.00') || '%' 环比增长率,
TO_CHAR(NVL(ROUND(DECODE(D.yearOnYear,0,NULL,(D.VALUE - D.yearOnYear)/D.yearOnYear * 100),2), 0),'fm9999999990.00')  || '%' 同比增长率
FROM (
    SELECT
    C.YEARMONTH,
    C.VALUE,
    LAG (C.VALUE, 1, 0) OVER (ORDER BY C.YEARMONTH) AS monthOnMonth,
    LAG (C.VALUE, 12, 0) OVER (ORDER BY C.YEARMONTH) AS yearOnYear
    FROM (
        SELECT A.YEARMONTH, NVL(B.VALUE, 0) VALUE FROM 
            (SELECT TO_CHAR( ADD_MONTHS( TO_DATE( '202101', 'yyyyMM' ), ROWNUM - 1), 'yyyyMM') AS YEARMONTH FROM DUAL CONNECT BY ROWNUM <= months_between( to_date('202212', 'yyyyMM'), to_date( '202101', 'yyyyMM')) + 1) A
        LEFT JOIN
            (
                SELECT TO_CHAR(CYJSSJ, 'yyyyMM') YEARMONTH, NVL(COUNT(*), 0) VALUE FROM BA_JBXX WHERE TO_CHAR(CYJSSJ, 'yyyyMM') >=  202101 AND TO_CHAR(CYJSSJ, 'yyyyMM') <= 202212 
                GROUP BY TO_CHAR(CYJSSJ, 'yyyyMM') ORDER BY TO_CHAR(CYJSSJ, 'yyyyMM')
            ) B 
        ON A.YEARMONTH = B.YEARMONTH ORDER BY A.YEARMONTH
    ) C
) D WHERE D.YEARMONTH >= 202001 AND D.YEARMONTH <= 202212