使用前一行计算/条件的oracle sql lag函数

cclgggtu  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(368)

我试着用康斯坦塔作为第一个值来计算
然后第二行将根据第一行计算结果进行计算,依此类推(第三行使用第二行结果)
基表如下所示,已经按日期作为键排序

CREATE TABLE T42
    ("Daytime" timestamp, "A" int, "B" int, "C" int)
;

INSERT ALL 
    INTO T42 ("Daytime", "A", "B", "C")
         VALUES ('01-Apr-2020 12:00:00 AM', -7021, 151.6445, -15)
    INTO T42 ("Daytime", "A", "B", "C")
         VALUES ('02-Apr-2020 12:00:00 AM', -7037, 152.3164, -15)
    INTO T42 ("Daytime", "A", "B", "C")
         VALUES ('03-Apr-2020 12:00:00 AM', -7053, 155.0313, -16)
    INTO T42 ("Daytime", "A", "B", "C")
         VALUES ('04-Apr-2020 12:00:00 AM', -7065, 151.9258, -11)
SELECT * FROM dual
;

四月一日,君士坦塔是7005.98。
我需要的结果应该是这样的

+----------+-------+-----+-----+---------+----------+
| Daytime  |   A   |  B  |  C  |  CALC1  | PREV_SAL |
+----------+-------+-----+-----+---------+----------+
| 4/1/2020 | -7021 | 152 | -15 | 7020.98 |        0 |
| 4/2/2020 | -7037 | 152 | -15 | 7035.98 |  7020.98 |
| 4/3/2020 | -7053 | 155 | -16 | 7051.98 |  7035.98 |
| 4/4/2020 | -7065 | 152 | -11 | 7062.98 |  7051.98 |
+----------+-------+-----+-----+---------+----------+

我4月1日的第一次尝试就是用这个

SELECT "Daytime",
    A ,
    B ,
    C ,
    CASE
      WHEN "Daytime" = '1-APR-20'
      THEN
        CASE
          WHEN 7005.98 +(C*-1)>7080
          THEN 7080
          ELSE 7005.98 +(C*-1)
        END          
       ELSE    null       
    END calc1

  FROM T42

但后来我尝试使用lag函数来获得之前的结果,但它在calc1上错误地使用了无效标识符

SELECT "Daytime",
    A ,
    B ,
    C ,
    CASE
      WHEN "Daytime" = '1-APR-20'
      THEN
        CASE
          WHEN 7005.98 +(C*-1)>7080
          THEN 7080
          ELSE 7005.98 +(C*-1)
        END
      WHEN "Daytime" > '1-APR-20'
      THEN 
        CASE
          WHEN calc1 +(C*-1)>7080
          THEN 7080
          ELSE calc1 +(C*-1)
        END 

       ELSE    null       
    END calc1

  FROM T42

我也尝试使用cte,它适合4月2日,但对于其余的是空的

with cte as
(
SELECT "Daytime",
    A ,
    B ,
    C ,
    CASE
      WHEN "Daytime" = '1-APR-20'
      THEN
        CASE
          WHEN 7005.98 +(C*-1)>7080
          THEN 7080
          ELSE 7005.98 +(C*-1)
        END

    END calc1

  FROM T42
  )

  select 

  cc."Daytime",
  A,
  B,
  C,
  cc.calc1,
  LAG(cc. calc1, 1, 0) OVER (ORDER BY cc."Daytime") AS prev_sal,
  CASE
    WHEN cc."Daytime" > '1-APR-20'
    THEN
      CASE
        WHEN LAG(cc. calc1, 1, 0) OVER (ORDER BY cc."Daytime")+(cc.C*-1) >7080
        THEN 7080
        ELSE LAG(cc. calc1, 1, 0) OVER (ORDER BY cc."Daytime")+(cc.C*-1)
      END
  END calc2

  from cte cc

cte结果

+---------+-------+-----+-----+---------+----------+---------+
| Daytime |   A   |  B  |  C  |  CALC1  | PREV_SAL |  CALC2  |
+---------+-------+-----+-----+---------+----------+---------+
| 00:00.0 | -7021 | 152 | -15 | 7020.98 | 0        | (null)  |
| 00:00.0 | -7037 | 152 | -15 | (null)  | 7020.98  | 7035.98 |
| 00:00.0 | -7053 | 155 | -16 | (null)  | (null)   | (null)  |
| 00:00.0 | -7065 | 152 | -11 | (null)  | (null)   | (null)  |
+---------+-------+-----+-----+---------+----------+---------+

有可能做这种计算吗?
这是我的小提琴链接

uqcuzwp8

uqcuzwp81#

您可以使用递归cte生成所需的结果;首先根据 Daytime 使递归更容易,然后计算 CALC1 从上一个值和当前值 C ,和 PREV_SAL 从上一个 CALC1 :

WITH CTE AS (
  SELECT T42.*,
         ROW_NUMBER() OVER (ORDER BY Daytime) AS rn
  FROM T42
),
CTE2 (Daytime, A, B, C, CALC1, PREV_SAL, rn) AS (
  SELECT Daytime, A, B, C,
         LEAST(7005.98 - C, 7080.0) AS CALC1,
         0 AS PREV_SAL,
         rn
  FROM CTE
  WHERE rn = 1
  UNION ALL 
  SELECT c.Daytime, c.A, c.B, c.C,
         LEAST(c2.CALC1 - c.C, 7080.0),
         c2.CALC1,
         c.rn
  FROM CTE c
  JOIN CTE2 c2 ON c.rn = c2.rn + 1
)
SELECT Daytime, A, B, C, CALC1, PREV_SAL
FROM CTE2

输出:

DAYTIME                 A       B       C       CALC1       PREV_SAL
2020-04-01 00:00:00.0   -7021   152     -15     7020.98     0
2020-04-02 00:00:00.0   -7037   152     -15     7035.98     7020.98
2020-04-03 00:00:00.0   -7053   155     -16     7051.98     7035.98
2020-04-04 00:00:00.0   -7065   152     -11     7062.98     7051.98

sqlfiddle演示

相关问题