select -- 组内重排序
user,
num,
date,
sum(num) over (partiton by user,acc order by date asc) order_num
from
(
select -- 按照用户分组,订单日期排序 累加发生事件的次数
user,
num,
date,
sum(tag1) over(partition by user order by date asc) acc
from
(-- 步骤二 判断如果当前行与上一行相差大于30则计为1 否则即为0
select
user,
num,
date,
case when date_diff(date,dt) >30 then 1 else 0 end tag1
from
(--步骤一 当前行减去上一行
select
user,
num,
date,
lag(date,1,null) over(partition by user order by date asc) dt
from
table_A
)A
)B
)C