Hive 窗口函数

理论

over()：指定聚合函数工作的数据窗口的大小，这个数据窗口大小会随着行的变而变化。over跟在聚合函数后面，只对聚合函数生效。

• current row：当前行
• n preceding：往前n行数据
• n following：往后n行数据
• unbounded：起点：unbounded preceding 表示从前面的起点开始， unbounded following表示到后面的终点结束
• lag(col,n)：往前第n行数据
• lead(col,n)：往后第n行数据
• ntile(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号。注意：n必须为int类型。

示例：查询某一个人的工资并和其所在的部门的平均薪资进行对比。

select ename,sal,deptno,avg(sal) over(partition by deptno )
from tb_emp;

结果：

示例

1. 创建本地shopping.txt，添加数据：

   jack,2017-01-01,180
   tony,2017-01-02,125
   jack,2017-02-03,123
   tony,2017-01-04,29
   jack,2017-01-05,416
   jack,2017-04-06,122
   tony,2017-01-07,520
   jack,2017-01-08,155
   mart,2017-04-08,612
   mart,2017-04-09,268
   neil,2017-05-10,132
   mart,2017-04-11,475
   neil,2017-06-12,380
   mart,2017-04-13,694
   

2. 创建表

3. 导入数据

4. 查看数据

5. 查询在2017年4月份购买过的顾客及4月份共有多少人在商场消费过 默认：over()会针对每一行数据开一个窗口。

6. 查询顾客的购买明细及商场月销售总额 或 结果：

7. 要将cost按照日期进行累加

8. 练习各函数用法 select name,riqi,cost, sum(cost) over() as sample1,–所有行相加 sum(cost) over(partition by name) as sample2,–按name分组，组内数据相加 sum(cost) over(partition by name order by riqi) as sample3,–按name分组，组内数据累加 sum(cost) over(partition by name order by riqi rows between UNBOUNDED PRECEDING and current row ) as sample4 ,–和sample3一样,由起点到当前行的聚合 sum(cost) over(partition by name order by riqi rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合 sum(cost) over(partition by name order by riqi rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,–当前行和前边一行及后面一行 sum(cost) over(partition by name order by riqi rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 from tb_shopping; 结果：

9. 查看顾客上次的购买时间 设置mapreduce.job.reduces=1;

10. 查看顾客上一次和下一次的购买时间 设置mapreduce.job.reduces=1;

11. 查询前20%时间的订单信息

12. 预备知识 只需要查询出来gid=1的就可以了，但where后面不能写分组函数；having后面可以使用分组函数，但having只用于group by分组统计语句。所以需要采用子查询的方式实现：

Rank

• rank() 排序相同时会重复，总数不会变
• dense_rank() 排序相同时会重复，总数会减少
• row_number() 会根据顺序计算

示例：排序

1. 数据：

zhangsan	math	88
lisi	math	88
wanger	math	90
mazi	math	56
zhangsan	english	78
lisi	english	66
wanger	english	89
mazi	english	66
zhangsan	chinese	77
lisi	chinese	69
wanger	chinese	77
mazi	chinese	69

2. 创建表
3. 加载数据并测试
4. 执行排序
5. 结果 上图格式调整，用来分析用