Hive中常用SQL梳理

注意：hive子查询需要起别名！！！

先groupby后取第一条：

1. 方法1：

• 使用row_number()和over()函数：

遇到这么一个需求，输入数据为一个ID对应多个name，要求输出数据为ID是唯一的，name随便取一个就可以。

select A.* from (select row_number() over (partition by id order by name desc) row_num from table_name) A where A.row_num=1;

3. 方法二：

• Hive不允许直接访问非group by字段；
• 对于非group by字段，可以用Hive的collect_set函数收集这些字段，返回一个数组；
• 使用数字下标，可以直接访问数组中的元素；

select id,collect_set(name)[0] from table_name group by id;

先groupby后取top K：

select A.* from (select row_number() over (partition by id order by name desc) row_num from table_name) A where A.row_num<k;

取非groupby的字段：

Hive不允许直接访问非group by字段；
对于非group by字段，可以用Hive的collect_set函数收集这些字段，返回一个数组；
使用数字下标，可以直接访问数组中的元素；

select id,collect_set(name)[0] from table_name group by id;