Oracle 查询，用于导出余额从连续 3 个月开始增加的所有 ID 以及增长百分比

Question

我想提取过去连续 3 个月余额增加的所有 id，并显示其增长百分比

示例数据集 - 并非所有 ID 都有连续 3 个月的数据。在这种情况下，从(可用(最近的上一个中获得增长百分比

ID   DATE    BAL
1   201904  81747.88
1   201905  90102.06
1   201906  131580.78
2   201904  47903.14
2   201905  51829.55
2   201906  57044.11
3   201904  43650.41
3   201905  47838.46
4   201906  204320.01
5   201905  528.92
5   201906  1158.07
6   201904  27236.02
6   201905  35019.5
6   201906  45228.87

下面是我用来计算指标的sql，但由于我的growth_prcnt错误，似乎有些东西无法正常工作。应该有更好的方法来推导这个

    select (BAL-PREV_3MTNH_BAL)/PREV_3MTNH_BAL as grwth_prcnt, c.*  
    from(
      select b.* 
        from (
          select A.*, LAG(BAL, 3) over (partition by ID order by DATE) AS PREV_3MTNH_BAL,
            LAG(BAL, 2) over (partition by ID order by DATE) as PREV_2MTNH_BAL,
            LAG(BAL, 1) over (partition by ID order by DATE) as PREV_1MTNH_BAL
          from temp A ) b
        where BAL>PREV_3MTNH_BAL and  BAL>PREV_2MTNH_BAL AND BAL>PREV_1MTNH_BAL AND DATE >=201904)C

Answer 1

据我了解您的问题，以下应该是查询：

-- CONSECUTIVE 3 MONTH DATA MUST BE
-- CURRENT MONTH (JUNE)
-- LAST MONTH (MAY)
-- LAST TO LAST MONTH (APRIL)
SELECT
    -- MULTIPLIED BY 100 BECAUSE IT IS PERCENTAGE AS NAME SUGGEST
    ROUND(((BAL - PREV_2MTNH_BAL) * 100) / PREV_2MTNH_BAL, 2) AS GRWTH_PRCNT,
    B.*
FROM
    (
        SELECT
            A.*, -- CURRENT MONTH DATA AND BALANCE
            LAG(BAL, 1) OVER(
                PARTITION BY ID
                ORDER BY
                    DATE
            ) AS PREV_1MTNH_BAL, -- LAST MONTH BALANCE
            LAG(BAL, 2) OVER(
                PARTITION BY ID
                ORDER BY
                    DATE
            ) AS PREV_2MTNH_BAL -- LAST TO LAST MONTH BALANCE
        FROM
            TEMP A
    ) B
WHERE
    -- CURRENT MONTH BALANCE MUST BE GREATER THAN LAST MONTH BALANCE
    BAL > PREV_1MTNH_BAL 
    -- LAST MONTH BALANCE MUST BE GREATER THAN LAST TO LAST MONTH BALANCE
    AND PREV_1MTNH_BAL > PREV_2MTNH_BAL
    AND DATE >= 201904

干杯！！

Answer 2

假设您每月最多有一行，那么：

select (BAL - PREV_3MTNH_BAL) / PREV_3MTNH_BAL as grwth_prcnt,
       a.*  
from (select A.*,
             LAG(BAL, 3) over (partition by ID order by DATE) AS PREV_3MTNH_BAL,
             LAG(BAL, 2) over (partition by ID order by DATE) as PREV_2MTNH_BAL,
             LAG(BAL, 1) over (partition by ID order by DATE) as PREV_1MTNH_BAL,
             COUNT(*) over (partition by id) as cnt
      from temp A
      where date >= 201904
     ) a
where BAL > PREV_3MTNH_BAL and
      BAL > PREV_2MTNH_BAL and
      BAL > PREV_1MTNH_BAL and
      DATE = 201906 and
      cnt = 3;