Hive(十一)--数据去重及row_number()

桃扇骨 2022-06-17 01:06 304阅读 0赞

## distinct会整条数据去重，有一项是不同的整条数据就算不同的，不会去掉，按照某一个字段去重需要如下方法 ##

## hive数据去重，并根据需求取其中一条 ##

数据案例：

name adx tran\_id cost ts  
ck 5 125.168.10.0 33.00 1407234660  
ck 5 187.18.99.00 33.32 1407234661  
ck 5 125.168.10.0 33.24 1407234661   
  
只需要前两行的记录，因为第三行的tran\_id和第一行的重复了，所以需要将最后面一行重复的去掉。

方案一：

selectt1.tran\_id,t2.name,t2.cost

from (selectdistinct tran\_id from table) t1

join table t2 ont1.tran\_id=t2.tran\_id

分析：  
如果使用distinct的话，需要把tran\_id放在第一列，查出来的数据很不友好。

方案二：

select\*

from(

select \*,row\_number() over (partitionby tran\_idorder by timestamp asc) num from table

) t

wheret.num=1;

分析：

row\_number()over (partition by tran\_idorder by timestamp desc) num 取num=1 的  
意思是先根据tran\_id进行分组，并在分组内部按timestamp 降序排序，row\_number()函数计算的值就表示某个tran\_id组内部排序后的顺序编号（该编号在一个组内是连续并且唯一的) 。  
所以最后直接去每个分组内的第一个（num=1）即可。

PS：

**ROW\_NUMBER() OVER函数的基本用法**  
语法：ROW\_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)  
简单的说row\_number()从1开始，为每一个分组记录返回一个数字，这里的ROW\_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的每条xlh记录返回一个序号。  
示例：  
xlh row\_num   
1700 1  
1500 2   
1085 3  
710 4  
  
row\_number() OVER (PARTITION BY COL1 ORDERBY COL2) 表示根据COL1分组，在分组内部根据COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（该编号在组内是连续并且唯一的) 。

实例：

数据显示为  
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 deptidORDER BY salary desc) rank FROM employee