Hive高级操作-排序操作

合集 - 数据仓库技术与应用(11)

1.掌握Hive数据存储模型2024-06-282.了解Hive基础2024-06-283.MYSQL语法：左连接、右连接、内连接、全外连接2024-03-124.Hive数据库及表操作2024-06-285.Hive高级操作-查询操作2024-06-286.Hive数据库及表操作2024-06-28

7.Hive高级操作-排序操作2024-06-28

8.Hive高级操作-窗口函数2024-06-289.Hive的内置函数（数学、字符串、日期、条件、转换、行转列）2024-06-2810.视图和索引2024-06-2811.Hive 元数据2024-06-28

收起

排序操作

---

1. 排序

1.1 order by（全局排序）

作用：全局排序，只有一个reducer（多个reducer无法保证全局有序）。

升序ASC（ascend），降序DESC（descend）

因为全局排序比较耗时，防止单个reducer处理时间过长，当数据规模大时，需设置hive.mapred.mode=strict ，后面必须跟limit子句。

查询员工信息按工资降序排序
select * from employInfo order by employSalary desc;

1.2 sort by（每个Reducer内部排序）

sort by 的数据只能保证在同一reduce中的数据可以按指定字段排序。
使用sort by 你可以指定执行的reduce 个数 （set mapred.reduce.tasks=）,对输出的数据再执行归并排序，即可以得到全部结果。

1.3 distribute by（分区排序）

distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。distribute by要写在sort by前面。

#设置reducer的个数为4
set mapreduce.job.reduces=4;
#将查询结果导入到文件中（按照部门编号分区且按照薪资降序排序）
select * from employInfo distribute by deptID sort by employSalary desc;

1.4 cluster by

作用：当distribute by和sorts by字段相同时，可以使用cluster by方式代替。
但是排序只能是升序排序，不能像distribute by一样去指定排序的规则为ASC或者DESC，否则报错。

select * from employInfo cluster by deptID;
select * from employInfo distribute by deptID sort by deptID;

2. 分组聚合

聚合函数是指对一组值进行计算并返回单个值的函数，通常与select语句的group by子句一起使用。

2.1 分组 Group by

🆘 使用Group by时，在select后面出现的非聚合函数字段也要出现在Group by后面。

#deptno是非聚合字段，需要出现在Group by后面
select deptno from emp group by deptno;
#avg_sal是聚合字段，不需要出现在Group by后面
select deptno,avg(salary) avg_sal from emp group by deptno;

2.2 基础聚合

sum(col) : 计算指定列的总和。

count(*) : 计算行数，可以计算所有行或指定列的非空值数。

max(col) : 返回指定列中的最大值。

min(col) : 返回指定列中的最小值。

avg(col) : 计算指定列的平均值。

2.3 高级聚合

2.3.1 GROUPING SETS

GROUPING SETS 可以用于对多个列组合进行分组聚合。

2.3.2 CUBE和ROLLUP

ROLLUP 是一种特殊的 GROUP BY，用于创建层次化的聚合数据。

CUBE 类似于 ROLLUP，但会生成所有可能的组合汇总。

2.4 技能实训

查询销售金额前10名的顾客

select a.id ,sum(a.subtotal) total
from 
(select orders.order_customer_id id,order_items.order_item_subtotal subtotal
from 
orders join customers on orders.order_customer_id=customers.customer_id
join order_items on orders.order_id=order_items.order_item_order_id) a
group by a.id order by total desc
limit 10;