Hive窗口函数row_number案例

数据文件是：rownumbertest.txt，字段信息是：id,xb,age,name

1,男,18,张三
2,女,18,李四
3,女,20,王五
4,男,18,赵六
5,男,18,刘七
6,男,19,石九
7,男,38,黄渤
8,女,22,刘嘉玲
9,女,23,王菲
10,女,28,刘亦菲
11,女,18,赵丽颖

用户信息表

create database if not exists hive_test;
use hive_test;
drop table if exists rownumbertest;
create table rownumbertest(id int, xb string, age int, name string) row format
delimited fields terminated by ",";
load data local inpath "/home/hadoop/rownumbertest.txt" into table
rownumbertest;
select * from rownumbertest;

数据结果展示：

id xb age name rank
1 男 18 张三 3
2 女 18 李四 5
3 女 20 王五 4
4 男 18 赵六 3
5 男 18 刘七 3
6 男 19 石九 2
7 男 38 黄渤 1
8 女 22 刘嘉玲 3
9 女 23 王菲 2
10 女 28 刘亦菲 1
11 女 18 赵丽颖 5

需求：每种性别人群中，年龄最大的两个人
TopN的需求：分组取前几
核心思路：

如果能实现一个操作：
把每一条记录再对应的组中的编号如果能生成出来的话。那么下面的这个SQL能很容易的求出结果
select * from rownumbertest where rank <= 2;

distribute by 和 sort by 搭配使用

select a.id, a.xb, a.age, a.name, row_number() over(distribute by a.xb sort by
a.age desc) as rank from rownumbertest a;

partition by 和 order by 搭配使用

select id,xb,age,name, row_number() over (partition by xb order by age desc) as
index from rownumbertest;

+-----+-----+------+-------+--------+
| id | xb | age | name | index |
+-----+-----+------+-------+--------+
| 10 | 女 | 28 | 刘亦菲 | 1 |
| 9 | 女 | 23 | 王菲 | 2 |
| 8 | 女 | 22 | 刘嘉玲 | 3 |
| 3 | 女 | 20 | 王五 | 4 |
| 11 | 女 | 18 | 赵丽颖 | 5 |
| 2 | 女 | 18 | 李四 | 6 |
| 7 | 男 | 38 | 黄渤 | 1 |
| 6 | 男 | 19 | 石九 | 2 |
| 5 | 男 | 18 | 刘七 | 3 |
| 4 | 男 | 18 | 赵六 | 4 |
| 1 | 男 | 18 | 张三 | 5 |
+-----+-----+------+-------+--------+

select * from (select id,xb,age,name, row_number() over (partition by xb order
by age desc) as index from rownumbertest) abc where abc.index <= 3;

+---------+---------+----------+-----------+------------+
| abc.id | abc.xb | abc.age | abc.name | abc.index |
+---------+---------+----------+-----------+------------+
| 10 | 女 | 28 | 刘亦菲 | 1 |
| 9 | 女 | 23 | 王菲 | 2 |
| 8 | 女 | 22 | 刘嘉玲 | 3 |
| 7 | 男 | 38 | 黄渤 | 1 |
| 6 | 男 | 19 | 石九 | 2 |
| 5 | 男 | 18 | 刘七 | 3 |
+---------+---------+----------+-----------+------------+