5. Hive的三种去重方法

文章目录

Hive的三种去重方法

1. distinct

-- 语法SELECTDISTINCT column1, column2,...FROM table_name;

注意事项：

• distinct 不能单独用于指定某一列，必须放在 select 中所有字段的最前面，否则会报错select column1,distinct column2, column3 from table_name;-- 报错信息类似于 "cannot recognize input near 'DISTINCT' 'column2' 'column3'" 的语法错误
• distinct 是对 select 后面所有字段的组合进行去重，并不是只对紧跟其后的 column1 去重。distinct 的作用范围是整个 SELECT 子句的结果集
• distinct 对 NULL 是不进行过滤的，即返回的结果中包含NULL值with t1 as(select'a'as name unionallselect'b'as name unionallselectNULLas name unionallselectNULLas name)selectdistinct namefrom t1;-- output:bNULLa
• 当对大数据集进行去重时，使用 DISTINCT 需要考虑性能影响（效率较慢）

2. group by

-- 语法SELECT column1, column2, aggregate_function(column3)FROM table_name
WHERE condition
GROUPBY column1, column2

注意事项：

• distinct 与 group by 的关系？SELECTDISTINCT colA, colB FROM table1;-- 等价于SELECT colA, colB FROM table1GROUPBY colA, colB;
• 在实现去重时，group by 是对 group by 后面所有字段的组合进行去重，并不是只对紧跟其后的 colA 去重。

3. row_number()

-- 语法SELECT column1, column2,..., ROW_NUMBER()OVER(PARTITIONBY column1 ORDERBY column2)as row_num
FROM table_name
WHERE condition;

使用 row_number() 实现去重操作

-- 对 column1 进行去重SELECT column1
FROM(SELECT column1 ROW_NUMBER()OVER(PARTITIONBY column1)as row_num
  FROM table_name
) t
WHERE row_num =1;-- 对 column1、column2 进行去重SELECT column1,column2
FROM(SELECT column1,column2 ROW_NUMBER()OVER(PARTITIONBY column1,column2)as row_num
  FROM table_name
) t
WHERE row_num =1;

4. 三者的效率对比

在 Hive 中，使用

distinct

、

group by

和

row_number()

实现去重时，它们的效率会受到多个因素的影响，包括数据规模、数据分布、查询条件等。

一般情况下，

distinct

的效率相对较低，因为它需要对整个结果集进行扫描和比较，以去除重复行。尤其是当数据量较大时，

distinct

可能会对性能产生较大的影响。因此，使用

distinct

去重时需要谨慎考虑其性能开销。

相比之下，

group by

在处理去重时具有更好的性能。

group by

会先按照指定的列进行分组，然后在每个分组内进行聚合操作，这样可以减少比较的数据量。当数据量较大时，使用

group by

去重往往比

distinct

效率更高。

row_number()

通常用于复杂的去重需求，它能够在查询结果中为每一行生成一个唯一的序号。

row_number()

可以实现更灵活的去重操作，效率相对较高。

参考链接

Hive的三种去重方式对比

hive中三种去重的方法

Hive DISTINCT() for all columns?