hive窗口函数
前言
拉钩教育 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。
hive窗口函数格式
分析函数(如：sum(), max(), row_number()…) + 窗⼝⼦句(over函数)
窗口函数over()应用
1.统计test_join_order表中的订单数
select count(distinct order_id) from test_join_order ; -- 不用开窗函数的情况下进行统计
select distinct count(distinct order_id) over() from test_join_order ; --over()所有的数据为一组即一个窗口
注意：之所以使用两个distinct，是因为，over()函数会返回相同的重复的数据结果，因为窗口函数会为窗口内的每一行返回一个值。
如下图所示：
窗口函数over(partition by)应用
2.统计test_join_order表中各商品数的订单数，总销售额，平均销售额，最⼤⾦额，最⼩⾦额
select distinct round(count(distinct order_id)over(partition by quantity),2)count,
round(sum(sales)over(partition by quantity),2)sum,
round(avg(sales)over(partition by quantity),2)avg,
round(max(sales)over(partition by quantity),2)max,
round(min(sales)over(partition by quantity),2)min from test_join_order;
结果如下：
分析test_join_order表中各订单，⾦额⼤于平均⾦额的为⾼，否则为低
select distinct(order_id),
if(sum(sales)over(partition by order_id)>(sum(sales)over()/count(distinct order_id)over()),"高","低")
from test_join_order;
结果如下：
这里不用avg(sales)作为平均订单金额，是因为，一个订单会有多个购买商品，如果用avg(sales)over(),则会计算成所有商品的平均金额。
窗口函数rows between… and…
3.分析test_join_order表中各记录对销售额累计求和（保留两位⼩数点），销售量累计求最⼤
unbounded preceding 表示该窗⼝最前⾯的⾏
unbounded following 表示该窗⼝最后⾯的⾏
往前 n preceding 从当前⾏向前n⾏
往后 n following 从当前⾏向后n⾏
select order_id,quantity,sales,
round(sum(sales)over(rows between unbounded preceding and current row),2)heji,
round(max(quantity)over(rows between unbounded preceding and current row),2)max
from test_join_order;
4.分析overData表中每三天（前⼀天，当前天，后⼀天）的销售额合计（保留两位⼩数点）
select order_date,sales,
round(sum(sales)over(order by order_date rows between 1 preceding and 1 following ),2)from overdate;
5.统计Pianyi 表中每天⾦额最⾼，最低的订单号
窗口函数first_value
select order_id,order_date,sales,
first_value(order_id)over(partition by order_date order by sales desc)max,
first_value(order_id)over(partition by order_date order by sales)min from pianyi;
6.利⽤lead ,lag 找的每个⽤户上⼀次，下⼀次的购买时间
窗口函数之偏移函数
lag(字段,num),向上偏移num是偏移的行数，默认是1。
lead(字段,num),向下偏移。
select customer_id,order_date,
lag(order_date,1) over(partition by customer_id order by customer_id,order_date ) ago,
lead(order_date,1) over(partition by customer_id order by customer_id,order_date) next
from Pianyi order by customer_id,order_date;
7.分析Pianyi表中 各记录， 按销量降序排序后标上上序号
排序函数
row_numer 无空位排序
rank()排名相同，留下空位
dense_rank()排名相同，不会留下排名空缺位
select order_id,order_date,quantity,
row_number()over(order by quantity desc)row_number,
rank()over(order by quantity desc)rank,
dense_rank()over(order by quantity desc)dense_rank
from pianyi;
8.把Pianyi表的销量分为4份
select * ,
ntile(4) over(order by quantity ) zu
from Pianyi;
9.统计⼩于等于当前⾦额的订单数 占总订单数的⽐例
⼩于等于当前值的⾏数/分组内总⾏数
select * ,ntile(4) over(order by quantity ) zu
from Pianyi
order by quantity;