17,088
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE FUNCTION f
(
p_num NUMBER
,p_dat DATE
) RETURN NUMBER AS
cumnum NUMBER;
BEGIN
SELECT SUM(val)
INTO cumnum
FROM (SELECT ((dat - trunc(p_dat) + 1) -
lag((dat - trunc(p_dat) + 1), 1, 0) over(ORDER BY dat)) *
lag(num, 1, 0) over(ORDER BY dat) val
,rownum rn
FROM a)
WHERE rn <= p_num;
RETURN cumnum;
END;
CREATE OR REPLACE VIEW V AS
SELECT dat
,num
,dat - trunc(SYSDATE,'yyyy') + 1 DAY
,f(ROWNUM,trunc(SYSDATE,'yyyy')) + num cumnum
FROM a
select * from v
DAT NUM DAY CUMNUM
2008-1-1 100 1 100
2008-1-3 300 3 500
2008-1-10 300 10 2600
2008-1-12 200 12 3100
2008-2-26 100 57 12000
WITH a AS
(SELECT TO_DATE ('2008-01-01', 'yyyy-mm-dd') dt, 100 kc
FROM DUAL
UNION ALL
SELECT TO_DATE ('2008-01-03', 'yyyy-mm-dd') dt, 300 kc
FROM DUAL
UNION ALL
SELECT TO_DATE ('2008-01-10', 'yyyy-mm-dd') dt, 900 kc
FROM DUAL
UNION ALL
SELECT TO_DATE ('2008-01-15', 'yyyy-mm-dd') dt, 1000 kc
FROM DUAL)
SELECT aa.dt 时间, aa.kc 库存, aa.days 天数,
SUM (sum_day) OVER (ORDER BY aa.dt) + aa.kc 积数
FROM (SELECT a.dt, a.kc, a.dt - FIRST_VALUE (dt) OVER (ORDER BY dt)
+ 1 days,
LAG (kc, 1, 0) OVER (ORDER BY dt)
* TRUNC (a.dt - LAG (dt, 1, a.dt - 1) OVER (ORDER BY dt))
sum_day
FROM a) aa
WITH a AS
(SELECT TO_DATE ('2008-01-01', 'yyyy-mm-dd') dt, 100 kc
FROM DUAL
UNION ALL
SELECT TO_DATE ('2008-01-03', 'yyyy-mm-dd') dt, 300 kc
FROM DUAL
UNION ALL
SELECT TO_DATE ('2008-01-10', 'yyyy-mm-dd') dt, 900 kc
FROM DUAL
UNION ALL
SELECT TO_DATE ('2008-01-15', 'yyyy-mm-dd') dt, 1000 kc
FROM DUAL)
SELECT *
FROM a
SELECT aa.dt 时间, aa.kc 库存, aa.days 天数,
SUM (sum_day) OVER (ORDER BY aa.dt) + aa.kc 积数
FROM (SELECT a.dt, a.kc, a.dt - FIRST_VALUE (dt) OVER (ORDER BY dt)
+ 1 days,
LAG (kc, 1, 0) OVER (ORDER BY dt)
* TRUNC (a.dt - LAG (dt, 1, a.dt - 1) OVER (ORDER BY dt))
sum_day
FROM a) aa