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