hive sql练习4(不典型)：窗口函数的使用

题目

注意： 每个订单号中，按发货人排序，退货数量挨个分发给发货人，直至退完为止。

代码实现

with send_order as(
select '1' as s_order_id, 'A' as sender, 1 as send_count union all 
select '1' as s_order_id, 'B' as sender, 4 as send_count union all 
select '2' as s_order_id, 'A' as sender, 2 as send_count union all 
select '2' as s_order_id, 'B' as sender, 1 as send_count union all 
select '2' as s_order_id, 'C' as sender, 3 as send_count 
)
, return_order as(
select '1' as r_order_id, 2 as return_count union all 
select '2' as r_order_id, 3 as return_count 
)
,tmp as(
select 
s.*
,r.*
--发货数量做一个累加
,sum(s.send_count) over(partition by s.s_order_id order by s.sender) as acc_sum
from send_order s 
join return_order r 
on s.s_order_id = r.r_order_id
)
,tmp2 as(
select 
t.*
--上一个发货数量的累加值
,lag(acc_sum) over(partition by s_order_id order by sender) as lag_acc_sum
from tmp t
)
,tmp3 as(
select 
t.*
,case when acc_sum < return_count then send_count
      when lag_acc_sum < return_count and acc_sum = return_count then send_count
      when lag_acc_sum < return_count and acc_sum > return_count then return_count - lag_acc_sum
      else 0
 end as sender_return_count
from tmp2 t
)
select 
s_order_id as order_id
,sender
,sender_return_count
from tmp3 t
where sender_return_count <> 0
;