oracle一句sql搞定积分兑换和撤销

积分功能由原来的有效期1个月升级到1年，相应的积分兑换功能就遇到了难题。
1、兑换需要从最接近有效期的积分月份里扣除
2、撤销兑换时的积分还原问题

开始设计是一个月一个月的查询然后扣除，这种查询多次数据库，效率很低。
最终发现了oracle数据库的sum(nid) over(partition by v1 order by nid)函数。

效果如下：

一、先建表：
create table tmp_table(
A varchar(6), --月份
B number(10), --积分
C number(10), --兑换积分
D varchar(11) --手机号
);

二、插入测试数据：
insert into tmp_table(a,b,c,d)values('201511',10,0,'13800138000');
insert into tmp_table(a,b,c,d)values('201512',2,0,'13800138000');
insert into tmp_table(a,b,c,d)values('201601',50,0,'13800138000');


 三、兑换11分时查询该扣除的积分月份
select a,sum(b) over(partition by d order by a) sum_value from tmp_table;

 
 很明显2015年11月只有10分，2015年11月+12月积分有12分，扣除11分最少应该减少201511月和12月的数据，2016年1月的数据跟本次操作无关。
sql逻辑如下：
select t.d, --手机号
       t.a, --月份
       t.b, -- 当月积分
       sum(t.b) over(partition by t.d order by t.a) sum_value, --当月积分+之前月积分
       (sum(t.c) over(partition by t.d) + 11) exchange_value --已兑换积分+将要兑换积分
  from tmp_table t
 where t.d = '13800138000';

 
 通过执行结果可以看到2015年12月为临界点，12月之前的积分都被兑换完毕，12月一部分积分被兑换，12月之后的积分本次兑换不被消耗。

通过sql语句反映上面的结了如下：

select d,
       min(sum_value - exchange_value) over(partition by a) mark_value,
       min(a) over(partition by d) mark_month,
       row_number() over(partition by d order by d) cn
  from (select t.d,
               t.a,
               t.b,
               sum(t.b) over(partition by t.d order by t.a) sum_value,
               (sum(t.c) over(partition by t.d) + 11) exchange_value
          from tmp_table t
         where t.d = '13800138000')
 where (sum_value - exchange_value) >= 0;

 

 

获取第一条数据为基准，12月份之前的积分全部兑换,12月份积分兑换1分，12月之后的积分数据不处理。

 

    merge into tmp_table m
  using (select d, mark_value, mark_month
           from (select d,
                        min(sum_value - exchange_value) over(partition by a) mark_value,
                        min(a) over(partition by d) mark_month,
                        row_number() over(partition by d order by d) cn
                   from (select t.d,
                                t.a,
                                t.b,
                                sum(t.b) over(partition by t.d order by t.a) sum_value,
                                (sum(t.c) over(partition by t.d) + 11) exchange_value
                           from tmp_table t
                          where t.d = '13800138000')
                  where (sum_value - exchange_value) >= 0)
          where cn = 1) t2
  on (m.d = t2.d)
  when matched then
    update
       set m.c =
           (case
             when m.a < t2.mark_month then
              m.b
             when m.a > t2.mark_month then
              0
             else
              m.b - t2.mark_value
           end);

执行后的结果如下：
 


 

兑换处理完成，撤销同理。