本帖最后由 canhui87 于 2013-7-14 00:16 编辑
with a as
(select 'a' user_id,1 dt,50 ye1,20 ye2 from dual union all
select 'a', 2, 0 ,20 from dual union all
select 'a', 3, 20, 20 from dual union all
select 'a', 4, 0 ,10 from dual union all
select 'a', 5, 0 ,10 from dual union all
select 'b', 1, 100, 50 from dual union all
select 'b', 2, 0 ,80 from dual union all
select 'b', 3, 200, 100 from dual union all
select 'b', 4, 0 ,80 from dual union all
select 'b', 5, 0 ,50 from dual
)
select user_id,
dt,
ye1,
ye2,
nvl(lag(diff) over(partition by user_id order by dt), 0) + ye1 ye3,
case
when nvl(lag(diff) over(partition by user_id order by dt), 0) + ye1 >= ye2 then
ye2
else
nvl(lag(diff) over(partition by user_id order by dt), 0) + ye1
end ye4
from
(
select a.*,
sum(ye1 - ye2) over(partition by user_id order by dt) diff
from a
);
U DT YE1 YE2 YE3 YE4
- ---------- ---------- ---------- ---------- ----------
a 1 50 20 50 20
a 2 0 20 30 20
a 3 20 20 30 20
a 4 0 10 10 10
a 5 0 10 0 0
b 1 100 50 100 50
b 2 0 80 50 50
b 3 200 100 170 100
b 4 0 80 70 70
b 5 0 50 -10 -10
用户b 第2天消费80，但赠送账户余额只有50，想得到从赠送账户划扣金额=50，至于剩下的30会从其他账户划扣，这里就不做考虑
即如果赠送账户余额为0，就不用计算了，重新开始，等有余额再计算
不要出现负数
希望得到
U DT YE1 YE2 YE3 YE4
- ---------- ---------- ---------- ---------- ----------
b 1 100 50 100 50
b 2 0 80 50 50
b 3 200 100 200 100
b 4 0 80 100 80
b 5 0 50 20 20
449
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
