Hive SQL 中 BY 系列关键字全解析：从排序、分发到分组的核心用法

一、排序与分发相关 BY 关键字

1. ORDER BY：全局统一排序

作用：对查询结果进行全局排序，确保最终结果集完全有序（仅允许单个 Reducer 处理数据）。

语法：

SELECT * FROM table_name ORDER BY column1 [ASC|DESC]; -- ASC 为默认升序

特点：

大数据量时性能较差（单节点压力大），适用于需要绝对有序的场景（如总分排名、全量数据排序）。

2. SORT BY：分区内局部排序

作用：在每个 Reducer 内部对数据排序，不同 Reducer 间结果可能无序。

语法：

SELECT * FROM table_name SORT BY column1 [ASC|DESC];

特点：数据先按分发规则（如 DISTRIBUTE BY）分配到多个 Reducer，再独立排序，性能优于 ORDER BY。

3. DISTRIBUTE BY：数据分发规则指定

作用：定义数据分发到 Reducer 的规则，相同值的记录会被分配到同一个 Reducer。

典型搭配：与 SORT BY 联用，实现 “先分发后排序”（分区内排序）。

语法：

SELECT * FROM table_name DISTRIBUTE BY column1 SORT BY column2 [ASC|DESC];

示例：按 user_id 分发数据到同一 Reducer，再按 log_time 排序：

SELECT * FROM logs DISTRIBUTE BY user_id SORT BY log_time ASC;

4. CLUSTER BY：分发与排序的语法糖

作用：等价于 DISTRIBUTE BY column AND SORT BY column，对同一字段同时进行分发和排序。

语法：

SELECT * FROM table_name CLUSTER BY column;

限制：仅能对单一字段操作，无法对不同字段分别设置分发和排序规则。

二、分组与聚合相关 BY 关键字

5. GROUP BY：数据分组与聚合

作用：按指定字段对数据分组，配合聚合函数（如 SUM、COUNT、AVG） 进行统计计算。

语法：

SELECT group_col, aggregate_func(column) 
FROM table_name 
GROUP BY group_col;

示例：统计各部门员工人数及平均工资：

SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY dept_id;

注意：未在聚合函数中的字段需出现在 GROUP BY 子句中（Hive 3.0+ 支持优化）。

6. PARTITION BY：分区与窗口分组

场景一：Hive 表分区（物理存储）

作用：将数据按字段存储在不同目录（如按日期、地域分区），提升查询效率。

语法：

CREATE TABLE sales_data (order_id INT, amount DECIMAL) 
PARTITIONED BY (year STRING, month STRING);

过滤分区字段时可直接定位文件，如 SELECT * FROM sales_data WHERE year='2023'。

场景二：窗口函数（逻辑分组）

作用：在查询结果中按字段分组，不改变行数，用于计算分组内排名、累计值等。

语法：

SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank 
FROM employees;

说明：按 dept_id 分组，计算每个员工在部门内的工资排名。

三、关键特性对比表

关键字	核心功能	是否改变数据行数	典型场景
ORDER BY	全局排序	否	全量数据排名、总决赛计分
SORT BY	分区内排序	否	日志分区内时间排序
DISTRIBUTE BY	数据分发到 Reducer 的规则	否	配合 SORT BY 实现分区内排序
CLUSTER BY	分发 + 排序（同字段）	否	简单场景下的快速排序分发
GROUP BY	分组聚合	是（聚合后行数减少）	部门统计、类别汇总
PARTITION BY	表分区 / 窗口分组	否	物理存储优化、窗口函数排名