hive Spark SQL分析窗口函数
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
相关文章
- SQL Error (1055) sql_mode=only_full_group_by详解数据库
- JSP JSTL <sql:param>标签:指定SQL参数
- Linux下的C语言 SQL编程实践(linuxcsql)
- MySQL备份SQL:确保数据安全(mysql备份sql)
- 轻松学会:Oracle SQL 格式化技巧(oracle格式化sql)
- Oracle中增加列的SQL语法指南(oracle增加列sql)
- Unleashing the Power of PL/SQL in Oracle: A Comprehensive Guide(plsqloracle)
- 换SQL Server中的日期格式转换实践(sqlserver日期转)
- 接Sql Server 左联接技术:实现有效的数据查询(sqlserver 左联)
- SQL Server加密机制:安全保护您的数据(sqlserver 密匙)
- Linux系统下执行SQL脚本文件的技巧(linux执行sql文件)
- 深入理解MySQL下的常见SQL语句(mysql下sql语句是)
- 器Oracle中SQL运行机制取得成功(oracle中sql运行)
- 从Oracle中探索SQL函数的应用(oracle中sql函数)
- Oracle SQL不容忽视的不为空判断(oracle不为空sql)
- 从Oracle数据库查重SQL技巧介绍(oracle sql查重)
- Yii框架在页面输出执行sql语句以方便调试的实现方法