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)
只返回能关联上的结果。
- SELECT a.id,
- a.name,
- b.age
- FROM lxw1234_a a
- join lxw1234_b b
- ON (a.id = b.id);
- --执行结果
- 1 zhangsan 30
- 2 lisi 29
其他用法参考:
[一起学Hive]之十一-Hive中Join的类型和用法