大数据最全hive开窗函数总结

函数名(字段名)   over(partition by <要分列的组> order by <要排序的列> rows between <数据范围>) 

窗口大小可以通过 rows between …and…来限定,如下:

sum(A) over(partition by B order by C rows between D1 and D2)
avg(A) over(partition by B order by C rows between D1 and D2)
A:需要被加工的字段名称
B:分组的字段名称
C:排序的字段名称
D:计算的行数范围

rows between 2 preceding and current row # 取当前行和前面俩行
rows between unbounded preceding and current row  #包括本行和之前所有行
rows between current row and unbounded following #包括本行和之后所有的行
rows between 3 preceding and current row #包括本行和前面三行
rows between 3 preceding and 1 following #从前面三行和下面一行,总共五行
# 当order by 后面缺少窗口从句条件,窗口范围默认是 rows between unbounded preceding and current row.  ->上无边界到当前行
# 当 order by 和窗口从句都缺失,窗口规范默认是 rows between unbounded preceding and unbounded following. ->相当于只写partition by ,默认上无边界下午边界
以上所有范围划定 都是partition by  这个分组内来计算的 

示例1

题目:从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额，以及每个用户在其每个下单日期的VIP等级。 用户vip等级根据累积消费金额计算，计算规则如下： 设累积消费总额为X， 若0=<X<10000,则vip等级为普通会员 若10000<=X<30000,则vip等级为青铜会员 若30000<=X<50000,则vip等级为白银会员 若50000<=X<80000,则vip为黄金会员 若80000<=X<100000,则vip等级为白金会员 若X>=100000,则vip等级为钻石会员
表内容:

代码:

select user_id,
create_date,
cast(sum_so_far as decimal(16,2)) sum_so_far,
       case
           when sum_so_far >= 100000 then '钻石会员'
           when sum_so_far >= 80000 then '白金会员'
           when sum_so_far >= 50000 then '黄金会员'
           when sum_so_far >= 30000 then '白银会员'
           when sum_so_far >= 10000 then '青铜会员'
           when sum_so_far >= 0 then '普通会员'
           end vip_level
from(
select user_id,create_date,
sum(total_amount_per_day) over(partition by user_id order by create_date rows between 2 preceding and current row) sum_so_far
from (
select user_id,create_date,sum(total_amount) total_amount_per_day
from order_info
group by user_id, create_date
) t1
) t2
-- 注意开窗范围,cast( as 数据格式),case when 的使用<

结果:

示例2

更换窗口：sum(total_amount_per_day) over(partition by user_id order by create_date ) sum_so_far

结果:

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**