1、Hive 常用的窗口函数：
按照功能划分：
● 序号函数： rank () /dense_rank () /row_number ()
● 分布函数：percent_rank () /cume_dist ()
● 前后函数：lag () /lead ()
● 头尾函数：first_val () /last_val ()
● 聚合函数 + 窗⼝函数联合：
○ 求和 sum () over ()
○ 求最⼤ / ⼩ max ()/min () over ()
○ 求平均 avg () over ()
● 其他函数：nth_value () /nfile ()
如上，窗⼝函数的⽤法多种多样，不仅有专⻔的的窗⼝函数，还可以与聚合函数配合使⽤。他们都要跟窗口子句 Over（）一起使用才行，放在 over（）函数的前面。
2、各个窗口函数的含义：
RANK() ：返回一个数字，该数字表示在窗口内当前行的排名，排序相同时会重复，最后显示的总数不会变。
即如果有两个用户的值相同，则并列第一，那么下一个排名应该是第三名。
如：1-2-3-3-5-6
DENSE_RANK() ：返回一个数字，该数字表示在窗口内当前行的排名，排序相同时会重复，下一个排名将被跳过，最后显示的总数会减少。
即如果有两个用户的值相同，则并列第一，那么下一个排名应该是第二名。
如：1-2-3-3-4-5
ROW_NUMBER() ：返回一个数字，该数字表示在窗口内当前行的排名，会根据顺序计算，每行都有一个唯一的行号，从 1 开始，依次递增。如：1-2-3-4-5
percent_rank（）：返回所在窗口中当前行的百分比排名
cume_dist(): 如果按升序排列，则统计：⼩于等于当前值的⾏数 / 总⾏数。
如果是降序排列，则统计：⼤于等于当前值的⾏数 / 总⾏数。
LAG(col,n)：返回所在窗口中当前行前面第 n 行的值。
LEAD(col,n)：返回指定窗口中当前行后面第 n 行的值。
first_value（）：返回所在窗口中第一行的值。
last_value（）：返回所在窗口中最后一行的值。
max（）、min（）、sum（）、avg（）、count（）：常用的聚合函数。
NTILE(n)：将窗口划分为 n 个桶，并返回当前行所在桶的编号。可用于 where 条件中，传入桶的编号返回桶内的数据。
特别说明：上述的这些函数必须跟着 over（）一起使用，不能单独使用。
3、开窗函数 over ( ) 中加 order by 和 不加 order by 的区别：
如果使⽤环境为 hive，over ( ) 开窗函数前分排序函数和聚合函数两种。
当为排序函数，如 row_number (),rank () 等时，over 中的 order by 只起到窗⼝内排序作⽤。
当为聚合函数，如 max，min，count 等时，over 中的 order by 不仅起到窗⼝内排序，还起到窗⼝内从当前⾏到之前所有⾏的聚合（多了⼀个范围）。
4、窗⼝函数与普通聚合函数的区别：
（1）聚合函数是将多条记录聚合为⼀条；窗⼝函数是每条记录都会执⾏，有⼏条记录执⾏完还是⼏条。
（2）窗⼝函数兼具 GROUP BY ⼦句的分组功能以及 ORDER BY ⼦句的排序功能。但是，PARTITION BY ⼦句并不具备 GROUP BY ⼦句的汇总功能。
5、 OVER ( )（开窗函数）：用于执行基于窗口的聚合计算的函数，它把数据分为不同的窗口，并在每个窗口上执行聚合计算。
Over（）函数的语法如下：
OVER ( [ PARTITION BY partition_expression, ... ] [ ORDER BY sort_expression [ ASC | DESC ], ... ] [ ROWS/RANGE BETWEEN frame_start AND frame_end ] )
在 over（）函数内可以通过 partition by 指定分区，也可以通过 order by 指定排序方式，同时还可以根据 rows/range between and 框架子句指定窗口的大小和位置；这三个部分是可选的参数。
指定窗口大小和位置的参数如下：
CURRENT ROW：当前行
n PRECEDING：往前 n 行数据
n FOLLOWING：往后 n 行数据
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING 表示到后面的终点
示例： SELECT col1, col2, SUM(col3) OVER (PARTITION BY col1 ORDER BY col2 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sum_col3 FROM table;
6、案例展示：
1）数据准备：name，orderdate，cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
2）创建 hive 表并导入数据
create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/module/data/business.txt" into table
business;
3）按需求查询数据
（3.1）查询顾客及总人数
select
name,
count(*) over ()
from business;
结果显示：
说明：如果没有 over（）函数的话，那么查询出来的 count（*）只有一行数据，name 字段与 count（*）字段匹配不上，所以会出错。而用了 over（）函数之后，count（*）字段的数据会与每一个 name 字段的数据相匹配，因为 over（）函数将整张表作为了一个窗口。
（3.2）查询在 2017 年 4 月份购买过的顾客及总人数
select
name,
count(*) over ()
from business
where substring(orderdate,1,7) = '2017-04'
group by name;
结果显示：
注明：这里用 over（），over（）中没有写其他功能，那它会将整个表作为一个整体计算并对应每行数据显示出来。
substring（字符串，起始位置，截取多少位）：substring（）函数用于截取字符串，字符串位置从 0 开始计算，注意第三个参数代表截取多少位，，而不是截取到字符串哪个位置，可以不填写表示截取到字符串最后一位。
（3.3）查询顾客的购买明细及月购买总额
select
name,
orderdate,
cost,
sum(cost) over(partition by name, month(orderdate))
from business;
结果显示：
说明：sum(cost) over(partition by name, month(orderdate)) 表示，按照相同人名、同一月份进行开窗，数据在各自的窗口进行处理，sum（cost）计算的是各个区内的数据，不是整个表的数据。也就是对相同人名、同一月份进行开窗。
（3.4）将每个顾客的 cost 按照日期进行累加
select name,orderdate,cost,
sum (cost) over () as sample1,-- 所有行相加
sum (cost) over (partition by name) as sample2,-- 按 name 分组，组内数据相加
sum (cost) over (partition by name order by orderdate) as sample3,-- 按 name
分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between
UNBOUNDED PRECEDING and current row ) as sample4 ,-- 和 sample3 一样 , 由起点到
当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1
PRECEDING and current row) as sample5, -- 当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1
PRECEDING AND 1 FOLLOWING ) as sample6,-- 当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current
row and UNBOUNDED FOLLOWING ) as sample7 -- 当前行及后面所有行
from business;
注意：rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量
（3.5）查看顾客上次的购买时间
select
name,
orderdate,
cost,
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1,
lag(orderdate,2) over (partition by name order by orderdate) as time2
from business;
结果显示：
说明：lag（col，n，default_val）函数要用在 over（）前面。表示往前 n 行取数据，其中前面没有行的，则用 default_val 代替。LEAD（col，n，default_val）函数用法和 LAG（col，n，default_val）函数一样，表示往后 n 行取数据。
