窗口函数-进阶+小常识

以后复盘要写清楚点了，今天工作需要，翻出了自己半年前写的关于爆炸函数的博客，把自己看得一头雾水…
目录：
1、窗口函数-进阶
2、窗口函数-进阶
3、爆炸函数

1、窗口函数-进阶
背景：我有 x 元钱，我之前欠了不少人钱，现在要还钱，先欠的我得先还，求每一笔欠款能还多少钱。
字段含义：
user_id ：用户id
packet ：钱包
pay_id：欠款id
money ：欠款
time：欠款时间
can_pay_money：能还上的钱
before_sum：在此之前欠的累计钱数
curr_sum：截止至当前欠的累计钱数
数据描述：
用户1欠了四笔钱，他有8元，依次欠了4，2，1，5元，期望结果是4，2，1，1。
用户2欠了三笔钱，他有20元，依次欠了10，15，20元，期望结果是10，10，0。
用户3欠了两笔钱，他有30元，依次欠了50，30元，期望结果是30，0。

with t as (
select 'user1' as user_id ,8 as packet, 1 as pay_id, 4 as money, 1 as time
union all
select 'user1' as user_id ,8 as packet, 2 as pay_id, 2 as money, 2 as time
union all
select 'user1' as user_id ,8 as packet, 3 as pay_id, 1 as money, 3 as time
union all
select 'user1' as user_id ,8 as packet, 4 as pay_id, 5 as money, 4 as time
union all
select 'user2' as user_id ,20 as packet, 1 as pay_id, 10 as money, 1 as time
union all
select 'user2' as user_id ,20 as packet, 2 as pay_id, 15 as money, 2 as time
union all
select 'user2' as user_id ,20 as packet, 3 as pay_id, 20 as money, 3 as time
union all
select 'user3' as user_id ,30 as packet, 1 as pay_id, 50 as money, 1 as time
union all
select 'user3' as user_id ,30 as packet, 2 as pay_id, 30 as money, 2 as time
)
select user_id ,packet,pay_id,time,money,sum(money) over(partition by user_id order by time rows between unbounded preceding and 1 preceding) as before_sum
from t;

上面代码目的是：先给大家看看窗口函数效果，before_sum 是 在此之前欠的累计钱数。

可看到，第一个时间的 before_sum 为 null ，根据背景应该设置为0 : coalesce(before_sum,0) ,我用在下方代码中。

with t as (
select 'user1' as user_id ,8 as packet, 1 as pay_id, 4 as money, 1 as time
union all
select 'user1' as user_id ,8 as packet, 2 as pay_id, 2 as money, 2 as time
union all
select 'user1' as user_id ,8 as packet, 3 as pay_id, 1 as money, 3 as time
union all
select 'user1' as user_id ,8 as packet, 4 as pay_id, 5 as money, 4 as time
union all
select 'user2' as user_id ,20 as packet, 1 as pay_id, 10 as money, 1 as time
union all
select 'user2' as user_id ,20 as packet, 2 as pay_id, 15 as money, 2 as time
union all
select 'user2' as user_id ,20 as packet, 3 as pay_id, 20 as money, 3 as time
union all
select 'user3' as user_id ,30 as packet, 1 as pay_id, 50 as money, 1 as time
union all
select 'user3' as user_id ,30 as packet, 2 as pay_id, 30 as money, 2 as time
),
t1 as (
select user_id,time,packet,money,sum(money) over(partition by user_id order by time) as curr_sum,
coalesce(sum(money) over(partition by user_id order by time rows between unbounded preceding and 1 preceding),0) as before_sum
from t)
select 
    user_id,time,packet,money,curr_sum,before_sum,
    case when curr_sum>packet then (case when packet-before_sum>0 then packet-before_sum else 0 end)
    else money end can_pay_money
from t1

2、常识 ：null不能参与判断，如果可能是null的字段放在where中，条件是一定不成立的，要注意这个。场景如：一张表可由一个字段，分为两个业务，一个业务情况A: field = 1，另一个业务情况B: field in (2,3) ，如果字段存在null值，应该属于哪一个业务呢，要确认这个。假如属于业务B 的情况，那么通过 field<>1 会无法得到字段值为null的情况。因此这种情况应该 coalesce(field, 非1的值（自己设置）)

select null<>1; --null
select 1=1; --true

3、爆炸函数 //todo