概述
hive中的窗口函数和sql中的窗口函数类似，都是用来做一些数据分析类的工作，一般用于olap分析。
理解窗口函数可以从理解聚合函数开始，我们知道聚合函数的概念，就是将某列多行中的值按照聚合规则合并为一行，比如说Sum、AVG等等，简单的概念如图1所示。
通常来说，聚合后的行数都要小于聚合前的行数。
在sql中有一类函数叫做聚合函数，例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据，这时我们便引入了窗口函数。
特性和好处：
- 类似Group By的聚合
- 非顺序的访问数据
- 可以对于窗口函数使用分析函数、聚合函数和排名函数
- 简化了SQL代码（消除Join）
- 消除中间表
hive开窗函数over(partition by ……)用法
一、over(partition by ……)
主要和聚合函数sum()、count()、avg()等结合使用，实现分组聚合的功能
示例:根据day_id日期和mac_id机器码进行聚合分组求每一天的该机器的销量和即sum_num，hive sql语句:
select day_id,mac_id,mac_color,day_num,sum(day_num)over(partition by day_id,mac_id order by day_id) sum_num from test_temp_mac_id;
注:day_id,mac_id,mac_color,day_num为查询原有数据,sum_num为计算结果
| day_id | mac_id | mac_color | day_num | sum_num |
|---|---|---|---|---|
| 20171011 | 1292 | 金色 | 1 | 89 |
| 20171011 | 1292 | 金色 | 14 | 89 |
| 20171011 | 1292 | 金色 | 2 | 89 |
| 20171011 | 1292 | 金色 | 11 | 89 |
| 20171011 | 1292 | 黑色 | 2 | 89 |
| 20171011 | 1292 | 粉金 | 58 | 89 |
| 20171011 | 1292 | 金色 | 1 | 89 |
| 20171011 | 2013 | 金色 | 10 | 22 |
| 20171011 | 2013 | 金色 | 9 | 22 |
| 20171011 | 2013 | 金色 | 2 | 22 |
| 20171011 | 2013 | 金色 | 1 | 22 |
| 20171012 | 1292 | 金色 | 5 | 18 |
| 20171012 | 1292 | 金色 | 7 | 18 |
| 20171012 | 1292 | 金色 | 5 | 18 |
| 20171012 | 1292 | 粉金 | 1 | 18 |
| 20171012 | 2013 | 粉金 | 1 | 7 |
| 20171012 | 2013 | 金色 | 6 | 7 |
| 20171013 | 1292 | 黑色 | 1 | 1 |
| 20171013 | 2013 | 粉金 | 2 | 2 |
| 20171011 | 12460 | 茶花金 | 1 | 1 |
二、over(partition by ……)与group by 区别
如果用group by实现一中根据day_id日期和mac_id机器码进行聚合分组求每一天的该机器的销量和即sum_num,
则hive sql语句为:
select day_id,mac_id,sum(day_num) sum_num from test_temp_mac_id group by day_id,mac_id order by day_id;
注:我们可以观察到group by可以实现同样的分组聚合功能，但sql语句不能写与分组聚合无关的字段，否则会报错，即group by 与over(partition by ……)主要区别为，带上group by的hive sql语句只能显示与分组聚合相关的字段，而带上over(partition by ……)的hive sql语句能显示所有字段。
| day_id | mac_id | sum_num |
|---|---|---|
| 20171011 | 124609 | 1 |
| 20171011 | 20130 | 22 |
| 20171011 | 12922 | 89 |
| 20171012 | 12922 | 18 |
| 20171012 | 20130 | 7 |
| 20171013 | 12922 | 1 |
| 20171013 | 20130 | 2 |
hive中三个排序函数rank、row_number、dense_rank三者的区别
1、rank()函数
此排序方法进行排序时，相同的排序是一样的，而且下一个不同值是跳着排序的。
2、row_number()函数
此方法不管排名是否有相同的，都按照顺序1，2，3…..n
3、dense_rank()函数
此方法对于排名相同的名次一样，且后面名次不跳跃
| a | row_number | rank | dense_rank |
|---|---|---|---|
| A | 1 | 1 | 1 |
| C | 2 | 2 | 2 |
| D | 3 | 3 | 3 |
| B | 4 | 3 | 3 |
| E | 5 | 5 | 4 |
| F | 6 | 6 | 5 |
| G | 7 | 7 | 6 |
时间戳
unix时间戳
unix时间戳是从1970年1月1日（UTC/GMT的午夜）开始所经过的秒数，不考虑闰秒。
一个小时表示为UNIX时间戳格式为：3600秒；一天表示为UNIX时间戳为86400秒，闰秒不计算。
FROM_UNIXTIME(unix_timestamp,format)
把unix时间戳转换成date格式
示例：
hive>SELECT FROM_UNIXTIME( 1249488000, ‘%Y%m%d’ )
-> 20071120
UNIX_TIMESTAMP
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
如 果没有参数调用，返回一个Unix时间戳记(从’1970-01-01 00:00:00’GMT开始的秒数)。如果UNIX_TIMESTAMP()用一个date参数被调用，它返回从’1970-01-01 00:00:00’ GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的 本地时间的一个数字。
示例：
hive> select UNIX_TIMESTAMP();
-> 882226357
hive> select UNIX_TIMESTAMP(‘1997-10-04 22:23:00’);
-> 875996580
hive>select UNIX_TIMESTAMP(‘20071014’,’YYYYMMdd’)
-> 1169568500