Spark1.4发布，支持了窗口分析函数(window functions)。
在离线平台中，90%以上的离线分析任务都是使用Hive实现，其中必然会使用很多窗口分析函数，如果SparkSQL支持窗口分析函数，
那么对于后面Hive向SparkSQL中的迁移的工作量会大大降低，使用方式如下：
1、初始化数据
创建表
create table window_test2 (url string, rate int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
准备测试数据
url1,12
url2,11
url1,23
url2,25
url1,58
url3,11
url2,25
url3,58
url2,11
加载数据：
load data local inpath '/opt/bin/short_opt/windows2.data' overwrite into table window_test2 ;
2、窗口函数测试
查询所有数据
select * from window_test2;
+-------+-------+
| url | rate |
+-------+-------+
| url1 | 12 |
| url2 | 11 |
| url1 | 23 |
| url2 | 25 |
| url1 | 58 |
| url3 | 11 |
| url2 | 25 |
| url3 | 58 |
| url2 | 11 |
+-------+-------+
分组排序：
select url,rate,row_number() over(partition by url order by rate desc) as r from window_test2;
+-------+-------+----+
| url | rate | r |
+-------+-------+----+
| url1 | 58 | 1 |
| url1 | 23 | 2 |
| url1 | 12 | 3 |
| url2 | 25 | 1 |
| url2 | 25 | 2 |
| url2 | 11 | 3 |
| url2 | 11 | 4 |
| url3 | 58 | 1 |
| url3 | 11 | 2 |
+-------+-------+----+
分组统计sum
select url,rate,sum(rate) over(partition by url ) as r from window_test2;
+-------+-------+-----+
| url | rate | r |
+-------+-------+-----+
| url1 | 12 | 93 |
| url1 | 23 | 93 |
| url1 | 58 | 93 |
| url2 | 11 | 72 |
| url2 | 25 | 72 |
| url2 | 25 | 72 |
| url2 | 11 | 72 |
| url3 | 11 | 69 |
| url3 | 58 | 69 |
+-------+-------+-----+
分组统计avg
select url,rate,avg(rate) over(partition by url ) as r from window_test2;
+-------+-------+-------+
| url | rate | r |
+-------+-------+-------+
| url1 | 12 | 31.0 |
| url1 | 23 | 31.0 |
| url1 | 58 | 31.0 |
| url2 | 25 | 18.0 |
| url2 | 11 | 18.0 |
| url2 | 11 | 18.0 |
| url2 | 25 | 18.0 |
| url3 | 11 | 34.5 |
| url3 | 58 | 34.5 |
+-------+-------+-------+
分组统计count
select url,rate,count(rate) over(partition by url ) as r from window_test2;
+-------+-------+----+
| url | rate | r |
+-------+-------+----+
| url1 | 12 | 3 |
| url1 | 23 | 3 |
| url1 | 58 | 3 |
| url2 | 11 | 4 |
| url2 | 25 | 4 |
| url2 | 25 | 4 |
| url2 | 11 | 4 |
| url3 | 11 | 2 |
| url3 | 58 | 2 |
+-------+-------+----+
分组lag
select url,rate,lag(rate) over(partition by url ) as r from window_test2;
+-------+-------+-------+
| url | rate | r |
+-------+-------+-------+
| url1 | 12 | NULL |
| url1 | 23 | 12 |
| url1 | 58 | 23 |
| url2 | 25 | NULL |
| url2 | 11 | 25 |
| url2 | 11 | 11 |
| url2 | 25 | 11 |
| url3 | 11 | NULL |
| url3 | 58 | 11 |
+-------+-------+-------+
3、spark-1.4以后，支持所有的窗口函数了，有利用于hive作业向spark-sql来转换。
---------------------
原文：https://blog.csdn.net/kwu_ganymede/article/details/50457528
下面的博客汇总中，例子都很清晰，感谢博主：
分析窗口函数汇总：
part1: SUM,AVG,MIN,MAX
http://lxw1234.com/archives/2015/04/176.htm
part2: NTILE,ROW_NUMBER,RANK,DENSE_RANK
http://lxw1234.com/archives/2015/04/181.htm
part3: CUME_DIST,PERCENT_RANK
http://lxw1234.com/archives/2015/04/185.htm
part4：LAG,LEAD,FIRST_VALUE,LAST_VALUE
http://lxw1234.com/archives/2015/04/190.htm
part5: GROUPING SETS,GROUPING__ID,CUBE,ROLLUP
http://lxw1234.com/archives/2015/04/193.htm
序列函数不支持WINDOW子句.
http://lxw1234.com/archives/tag/hive-window-functions
