7 day cumulative sum that resets on condition

问题: I am trying to write a script that counts every time a customer spends more than €1200 within 7 days. Once a customer exceeds the €1200 threshold the cumulative sum should...

问题:

I am trying to write a script that counts every time a customer spends more than €1200 within 7 days. Once a customer exceeds the €1200 threshold the cumulative sum should reset. For example, if a customer exceeded €1200 on day 3 this counts as 1 and the cumulative sum should reset on day 4.

I have seen similar questions which cover a resetting cumulative sum. None of these solutions work with the 7 day rolling condition.

Example Data Set

create table test2
(
  yyyymmdd   DATE not null,
  account_id NUMBER,
  vol_eur    NUMBER
);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('21-01-2018 11:16:19', 'dd-mm-yyyy hh24:mi:ss'), 57642, 1500);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('06-01-2018 09:51:23', 'dd-mm-yyyy hh24:mi:ss'), 57645, 190);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('21-01-2018 07:09:35', 'dd-mm-yyyy hh24:mi:ss'), 57645, 300);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('03-01-2018 14:58:14', 'dd-mm-yyyy hh24:mi:ss'), 57646, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('17-01-2018 13:30:44', 'dd-mm-yyyy hh24:mi:ss'), 57646, 130);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('03-01-2018 18:33:33', 'dd-mm-yyyy hh24:mi:ss'), 57647, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('04-01-2018 08:44:33', 'dd-mm-yyyy hh24:mi:ss'), 57647, 270);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('05-01-2018 19:28:08', 'dd-mm-yyyy hh24:mi:ss'), 57647, 800);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('13-01-2018 12:24:21', 'dd-mm-yyyy hh24:mi:ss'), 57647, 700);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('15-01-2018 10:52:50', 'dd-mm-yyyy hh24:mi:ss'), 57647, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('27-01-2018 12:07:20', 'dd-mm-yyyy hh24:mi:ss'), 57647, 500);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('10-01-2018 21:14:46', 'dd-mm-yyyy hh24:mi:ss'), 57647, 690);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('30-01-2018 15:39:17', 'dd-mm-yyyy hh24:mi:ss'), 57647, 5500);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('05-01-2018 19:43:38', 'dd-mm-yyyy hh24:mi:ss'), 57649, 300);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('06-01-2018 17:54:30', 'dd-mm-yyyy hh24:mi:ss'), 57649, 150);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('15-01-2018 19:38:36', 'dd-mm-yyyy hh24:mi:ss'), 57649, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('20-01-2018 13:26:34', 'dd-mm-yyyy hh24:mi:ss'), 57649, 1150);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('06-01-2018 17:09:54', 'dd-mm-yyyy hh24:mi:ss'), 57651, 300);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('28-01-2018 17:31:14', 'dd-mm-yyyy hh24:mi:ss'), 57651, 250);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('04-01-2018 13:39:06', 'dd-mm-yyyy hh24:mi:ss'), 57654, 150);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('07-01-2018 13:18:26', 'dd-mm-yyyy hh24:mi:ss'), 57654, 200);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('13-01-2018 19:44:08', 'dd-mm-yyyy hh24:mi:ss'), 57654, 150);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('21-01-2018 16:18:05', 'dd-mm-yyyy hh24:mi:ss'), 57654, 150);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('28-01-2018 10:53:03', 'dd-mm-yyyy hh24:mi:ss'), 57654, 60);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('01-01-2018 12:09:00', 'dd-mm-yyyy hh24:mi:ss'), 57655, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('01-01-2018 17:01:27', 'dd-mm-yyyy hh24:mi:ss'), 57655, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('02-01-2018 19:30:31', 'dd-mm-yyyy hh24:mi:ss'), 57655, 200);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('21-01-2018 15:52:29', 'dd-mm-yyyy hh24:mi:ss'), 57655, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('21-01-2018 16:58:52', 'dd-mm-yyyy hh24:mi:ss'), 57655, 500);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('11-01-2018 14:26:30', 'dd-mm-yyyy hh24:mi:ss'), 57661, 2000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('12-01-2018 21:54:25', 'dd-mm-yyyy hh24:mi:ss'), 57661, 500);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('06-01-2018 16:46:25', 'dd-mm-yyyy hh24:mi:ss'), 57666, 5000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('10-01-2018 18:27:51', 'dd-mm-yyyy hh24:mi:ss'), 57666, 5000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('14-01-2018 18:52:14', 'dd-mm-yyyy hh24:mi:ss'), 57666, 5000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('20-01-2018 12:19:07', 'dd-mm-yyyy hh24:mi:ss'), 57666, 5000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('24-01-2018 18:38:40', 'dd-mm-yyyy hh24:mi:ss'), 57666, 2990);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('30-01-2018 18:36:01', 'dd-mm-yyyy hh24:mi:ss'), 57666, 1980);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('19-01-2018 18:48:44', 'dd-mm-yyyy hh24:mi:ss'), 57671, 2000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('19-01-2018 23:41:56', 'dd-mm-yyyy hh24:mi:ss'), 57671, 100);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('21-01-2018 19:22:51', 'dd-mm-yyyy hh24:mi:ss'), 57671, 5000);
commit;

回答1:

One option with a recursive cte.

with rownums as (select t.*,row_number() over(partition by id order by dt) as rnum 
                 from tbl t
                )
,rsum as (select id,dt,val,rnum,val as cumsum,0 as dt_diff
          from rownums
          where rnum = 1
          union all
          select r.id
                ,r.dt
                ,r.val
                ,r.rnum
                ,case when dt_diff + rs.dt - r.dt > 7 then r.val
                      when dt_diff + rs.dt - r.dt <= 7 and r.val + rs.cumsum < 1200 then r.val+rs.cumsum
                 else 0 end
                ,case when dt_diff + rs.dt - r.dt > 7 then 0 
                 else dt_diff + rs.dt - r.dt end
          from rsum rs
          join rownums r on r.id = rs.id and r.rnum = rs.rnum+1
         ) 
select id,dt,val,case when cumsum = 0 and lag(cumsum,1) over(partition by id order by dt) <= 1200 then val+lag(cumsum,1) over(partition by id order by dt)
                      when cumsum = 0 and lag(cumsum,1) over(partition by id order by dt) > 1200 then val
                 else cumsum end as res
from rsum
order by 1,2
  • Compute row numbers per user id in the first cte rownums.
  • Select the first row from the previously defined rownums cte as the anchor row and then iterate through the remaining rows, joining with the anchor row and looking one row ahead at a time. case expression here checks for the conditions.
  • Cumulative sum was set to 0 by the rsum cte, this indicates a new group starts based on either the sum exceeding 1200 within 7 days or a new 7 day period starting. Use lag to finally compute values on those rows.

Sample Demo in SQL Server

  • 发表于 2018-12-30 03:51
  • 阅读 ( 253 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除