Hive函数：rank()、dense_rank()
2024-09-30 05:58:41
数据准备：
G1,KING,5000
G1,BING,5000
G2,FING,5000
G1,FORD,3000
G2,SCOTT,3000
G1,JONES,2975
G2,BLAKE,2850
G1,CLARK,2450
G1,ALLEN,1600
G1,CELL1,NULL
G2,CELL2,NULL CREATE EXTERNAL TABLE test_data (
gid string,
ename STRING,
sal INT
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as textfile location '/user/jc_rc_ftp/test_data'; select * from test_data l;
+--------+----------+--------+--+
| l.gid | l.ename | l.sal |
+--------+----------+--------+--+
| G1 | CELL1 | NULL |
| G2 | CELL2 | NULL |
| G1 | KING | 5000 |
| G1 | BING | 5000 |
| G2 | FING | 5000 |
| G1 | FORD | 3000 |
| G2 | SCOTT | 3000 |
| G1 | JONES | 2975 |
| G2 | BLAKE | 2850 |
| G1 | CLARK | 2450 |
| G1 | ALLEN | 1600 |
+--------+----------+--------+--+
rank
---rank()over(order by 列名排序)的结果是不连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果结果如：1 1 1 4
SQL> select gid,ename,sal,rank() over(order by sal desc) from test_data;
---rank() over (partition by 分组字段 order by 排序字段 顺序)
注意：使用rank()over(order by 排序字段 顺序)排序的时候，空值是最大的
select gid,ename,sal,rank()over(partition by gid order by coalesce(sal,0) desc) from test_data;
+------+--------+-------+----------------+--+
| gid | ename | sal | rank_window_0 |
+------+--------+-------+----------------+--+
| G1 | BING | 5000 | 1 |
| G1 | KING | 5000 | 1 |
| G1 | FORD | 3000 | 3 |
| G1 | JONES | 2975 | 4 |
| G1 | CLARK | 2450 | 5 |
| G1 | ALLEN | 1600 | 6 |
| G1 | CELL1 | NULL | 7 |
| G2 | FING | 5000 | 1 |
| G2 | SCOTT | 3000 | 2 |
| G2 | BLAKE | 2850 | 3 |
| G2 | CELL2 | NULL | 4 |
+------+--------+-------+----------------+--+
dense_rank
---dense_rank()over(order by 列名排序)的结果是连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果如：1 1 1 2
如果排序字段为null，可能造成在排序时将null字段排在最前面，影响排序的正确性。
所以建议将 dense_rank()over(order by 列名 排序)改为dense_rank()over(order by 列名排序 nulls last)
---rank()over(order by 列名排序)的结果是不连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果结果如：1 1 1 4
SQL> select gid,ename,sal,rank() over(order by sal desc) from test_data;
+------+--------+-------+----------------+--+
| gid | ename | sal | rank_window_0 |
+------+--------+-------+----------------+--+
| G2 | FING | 5000 | 1 |
| G1 | BING | 5000 | 1 |
| G1 | KING | 5000 | 1 |
| G2 | SCOTT | 3000 | 4 |
| G1 | FORD | 3000 | 4 |
| G1 | JONES | 2975 | 6 |
| G2 | BLAKE | 2850 | 7 |
| G1 | CLARK | 2450 | 8 |
| G1 | ALLEN | 1600 | 9 |
| G1 | CELL1 | NULL | 10 |
| G2 | CELL2 | NULL | 10 |
+------+--------+-------+----------------+--+ ---rank() over (partition by 分组字段 order by 排序字段 顺序)
注意：使用rank()over(order by 排序字段 顺序)排序的时候，空值是最大的
select gid,ename,sal,rank()over(partition by gid order by coalesce(sal,0) desc) from test_data;
+------+--------+-------+----------------+--+
| gid | ename | sal | rank_window_0 |
+------+--------+-------+----------------+--+
| G1 | BING | 5000 | 1 |
| G1 | KING | 5000 | 1 |
| G1 | FORD | 3000 | 3 |
| G1 | JONES | 2975 | 4 |
| G1 | CLARK | 2450 | 5 |
| G1 | ALLEN | 1600 | 6 |
| G1 | CELL1 | NULL | 7 |
| G2 | FING | 5000 | 1 |
| G2 | SCOTT | 3000 | 2 |
| G2 | BLAKE | 2850 | 3 |
| G2 | CELL2 | NULL | 4 |
+------+--------+-------+----------------+--+ ---dense_rank()over(order by 列名排序)的结果是连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果如：1 1 1 2
如果排序字段为null，可能造成在排序时将null字段排在最前面，影响排序的正确性。
所以建议将 dense_rank()over(order by 列名 排序)改为dense_rank()over(order by 列名排序 nulls last) select gid,ename,sal,dense_rank()over(order by sal desc) from test_data;
+------+--------+-------+----------------------+--+
| gid | ename | sal | dense_rank_window_0 |
+------+--------+-------+----------------------+--+
| G2 | FING | 5000 | 1 |
| G1 | BING | 5000 | 1 |
| G1 | KING | 5000 | 1 |
| G2 | SCOTT | 3000 | 2 |
| G1 | FORD | 3000 | 2 |
| G1 | JONES | 2975 | 3 |
| G2 | BLAKE | 2850 | 4 |
| G1 | CLARK | 2450 | 5 |
| G1 | ALLEN | 1600 | 6 |
| G2 | CELL2 | NULL | 7 |
| G1 | CELL1 | NULL | 7 |
+------+--------+-------+----------------------+--+
select gid,ename,sal,dense_rank()over(partition by gid order by sal desc) from test_data;
+------+--------+-------+----------------------+--+
| gid | ename | sal | dense_rank_window_0 |
+------+--------+-------+----------------------+--+
| G1 | BING | 5000 | 1 |
| G1 | KING | 5000 | 1 |
| G1 | FORD | 3000 | 2 |
| G1 | JONES | 2975 | 3 |
| G1 | CLARK | 2450 | 4 |
| G1 | ALLEN | 1600 | 5 |
| G1 | CELL1 | NULL | 6 |
| G2 | FING | 5000 | 1 |
| G2 | SCOTT | 3000 | 2 |
| G2 | BLAKE | 2850 | 3 |
| G2 | CELL2 | NULL | 4 |
+------+--------+-------+----------------------+--+
最新文章
- 如何解决Visual Studio调试Debug很卡很慢
- (转)MySQL优化实例
- emulator: ERROR: x86 emulation currently requires hardware acceleration!
- 2015年第2本（英文第1本）：《The Practice of Programming》
- Ubuntu链接ubuntu服务器
- Tkinter教程之Scrollbar篇
- C++对象的自销毁
- RabbitMQ 原文译03--发布和订阅
- For循环复杂练习
- 【HDOJ】3587 NUDOTA
- HP-UX磁带备份错误收集
- 【算法系列学习三】[kuangbin带你飞]专题二 搜索进阶 之 A-Eight 反向bfs打表和康拓展开
- http.go
- kai linux安装搜狗输入法以及更新源地址
- /usr/lib/python2.7/site-packages/requests/__init__.py:80: RequestsDependencyWarning: urllib3 (1.22) or chardet (2.2.1) doesn't match a supported version! RequestsDependencyWarning)
- Pandas 基础(16) - Holidays
- [C#.Net]Window服务调用外部程序
- Oracle的nvl函数和nvl2函数
- 单用户实例添加DB账号
- Matlab中函数句柄@的作用及介绍
热门文章
- New Windows 10 SDK - Multi-instance UWP apps
- spy++捕获窗口消息
- Mycat 分片规则详解--日期范围 hash 分片
- redis客户端可以连接集群，但JedisCluster连接redis集群一直报Could not get a resource from the pool
- 将 Shiro 作为应用的权限基础 二：基于SpringMVC实现的认证过程
- Sum of xor
- Be Better , Be Better
- java基础笔记（8）----接口
- [BZOJ 3813]奇数国
- 多目标跟踪(MOT)论文随笔-POI: Multiple Object Tracking with High Performance Detection and Appearance Feature