我试着用康斯坦塔作为第一个值来计算
然后第二行将根据第一行计算结果进行计算,依此类推(第三行使用第二行结果)
基表如下所示,已经按日期作为键排序
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) |
+---------+-------+-----+-----+---------+----------+---------+
有可能做这种计算吗?
这是我的小提琴链接
1条答案
按热度按时间uqcuzwp81#
您可以使用递归cte生成所需的结果;首先根据
Daytime
使递归更容易,然后计算CALC1
从上一个值和当前值C
,和PREV_SAL
从上一个CALC1
:输出:
sqlfiddle演示