HiveSql基础函数—row_number()、Join的类型和用法

一、row_number()

1、语法：

row_number() over (partition by col_list1 order by col_list2)  

rank() over(partition by col_list1 order by col_list2)

2、row_number() over (partition by col_list1 order by col_list2)  

首先根据col_list1分组，在分组内部根据col_list2排序，row_number()函数计算的值表示每组内部排序后的顺序编号，组内连续的唯一的;例子：

数据显示为
empid       deptid      salary
----------- ----------- ---------------------------------------
1           10          5500.00
2           10          4500.00
3           20          1900.00
4           20          4800.00
5           40          6500.00
6           40          14500.00
7           40          44500.00
8           50          6500.00
9           50          7500.00

需求：根据部门分组，显示每个部门的工资等级

预期结果：
empid       deptid      salary                                  rank
----------- ----------- --------------------------------------- --------------------
1           10          5500.00                                 1
2           10          4500.00                                 2
4           20          4800.00                                 1
3           20          1900.00                                 2
7           40          44500.00                                1
6           40          14500.00                                2
5           40          6500.00                                 3
9           50          7500.00                                 1
8           50          6500.00                                 2

SQL脚本：
SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

也可以不限分组，直接进行排序：

row_number() over (order by col_list2 decs) as 新列名，例子：

123

3、rank() 

rank() over()函数则是跳跃排序,序号不唯一，即当有数据值值相同时，并列，当遇到不同的数据，其序号为上一个数据的序号加上该序号的个数。如两个并列第一，序列号为（1，1，3.......）

 

二、Join的类型和用法

Hive中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联，还支持LEFT SEMI JOIN和CROSS JOIN，但这两种JOIN类型也可以用前面的代替。

注意：Hive中Join的关联键必须在ON ()中指定，不能在Where中指定，否则就会先做笛卡尔积，再过滤。

hive> select * from lxw1234_a;
OK
1       zhangsan
2       lisi
3       wangwu
Time taken: 0.116 seconds, Fetched: 3 row(s)

hive> select * from lxw1234_b;
OK
1       30
2       29
4       21
Time taken: 0.09 seconds, Fetched: 3 row(s)

10.1 内关联（JOIN）

只返回能关联上的结果。

1. SELECT a.id,
2. a.name,
3. b.age
4. FROM lxw1234_a a
5. join lxw1234_b b
6. ON (a.id = b.id);
7.  
8. --执行结果
9.  
10. 1 zhangsan 30
11. 2 lisi 29

其他用法参考：
[一起学Hive]之十一-Hive中Join的类型和用法