Hive SQL 实战训练：10+ 经典练习题（附超详细解析）

一、引言

通过上一篇《手把手教你学 Hive SQL》，我们掌握了基础语法和核心操作。本文通过 30 道实战练习题，从基础到进阶逐步提升，每道题都附带「需求拆解 + SQL 写法 + 易错点解析」，帮助你彻底吃透 Hive SQL！

二、基础练习题（适合新手入门）

题目 1：查询成绩大于 85 分的学生姓名和年龄

需求拆解：

• 表：假设存在students表，字段包含stu_name（姓名）、age（年龄）、score（成绩）
• 条件：成绩 > 85 分，筛选行数据

正确 SQL：

SELECT stu_name, age 
FROM students 
WHERE score > 85;

解析：

• SELECT：指定要查询的字段（姓名、年龄）
• FROM students：数据来自students表
• WHERE score > 85：过滤条件，只保留成绩大于 85 的行
• ❗️易错点：字段名必须与表定义完全一致（如score不能写成成绩）

题目 2：统计每个班级的学生人数

需求拆解：

• 表：students表包含class_id（班级 ID）字段
• 目标：按class_id分组，统计每组人数

正确 SQL：

SELECT class_id, COUNT(*) AS student_count 
FROM students 
GROUP BY class_id;

解析：

• GROUP BY class_id：按班级 ID 分组（相同班级的数据聚合成一组）
• COUNT(*)：统计每组的行数（即该班级的学生人数）
• AS student_count：给统计结果起别名，方便阅读
• ❗️易错点：分组字段必须出现在SELECT中（除聚合函数外）

题目 3：查询年龄在 18-22 岁之间的学生，按成绩降序排列，取前 10 名

正确 SQL：

SELECT stu_name, age, score 
FROM students 
WHERE age BETWEEN 18 AND 22 
ORDER BY score DESC 
LIMIT 10;

解析：

• BETWEEN 18 AND 22：等价于age >= 18 AND age <= 22，简化区间条件
• ORDER BY score DESC：按成绩降序排列（DESC降序，ASC升序，默认ASC）
• LIMIT 10：只返回前 10 条数据（分页查询常用）

三、进阶练习题（掌握核心语法）

题目 4：计算每个班级的平均成绩，并筛选平均成绩 > 80 分的班级

需求拆解：

• 先分组（按班级），再计算平均成绩，最后过滤分组结果
• 注意：过滤分组结果用HAVING，不能用WHERE

正确 SQL：

SELECT class_id, AVG(score) AS avg_score 
FROM students 
GROUP BY class_id 
HAVING avg_score > 80;

解析：

• WHERE vs HAVING：
  • WHERE：在分组前过滤原始数据（如排除成绩 < 60 分的学生）
  • HAVING：在分组后过滤结果（如排除平均成绩 < 80 的班级）
• ❗️易错点：不能在HAVING中直接使用字段别名（部分版本允许，但推荐用聚合函数）

题目 5：创建外部表courses，字段包含课程 ID、课程名、学分，数据存储在/hive/courses路径

正确 SQL：

CREATE EXTERNAL TABLE courses (
  course_id INT,
  course_name STRING,
  credit INT
) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
LOCATION '/hive/courses';

解析：

• EXTERNAL TABLE：外部表，数据存储在 Hive 指定的路径（删除表时数据不丢失）
• LOCATION：指定 HDFS 或本地路径（外部表必须显式声明存储位置）
• ROW FORMAT：定义数据格式（这里字段用制表符\t分隔）

题目 6：将students表中年龄 > 20 岁的学生数据插入到新表older_students中

正确 SQL：

CREATE TABLE older_students AS 
SELECT stu_id, stu_name, age 
FROM students 
WHERE age > 20;

解析：

• CREATE TABLE ... AS SELECT：通过查询结果创建新表（复制数据 + 表结构）
• 新表older_students的字段和数据类型与SELECT的结果一致
• 适用场景：快速生成子集表（如按条件筛选后的样本数据）

四、综合练习题（多知识点结合）

题目 7：查询每个学生的姓名、所选课程名及成绩（两表内连接）

需求拆解：

• 表 1：students（字段：stu_id, stu_name）
• 表 2：scores（字段：stu_id, course_id, score）
• 表 3：courses（字段：course_id, course_name）
• 目标：通过stu_id连接学生表和成绩表，再通过course_id连接课程表

正确 SQL：

SELECT s.stu_name, c.course_name, sc.score 
FROM students s 
INNER JOIN scores sc ON s.stu_id = sc.stu_id 
INNER JOIN courses c ON sc.course_id = c.course_id;

解析：

• INNER JOIN：内连接（只保留两表中ON条件匹配的行）
• 别名用法：s代表students表，sc代表scores表，简化代码
• 连接顺序：先连接学生和成绩表，再连接课程表（结果相同，顺序可调整）

题目 8：统计每个课程的选课人数，包括没有学生选的课程（左外连接）

正确 SQL：

SELECT c.course_name, COUNT(sc.stu_id) AS选课人数 
FROM courses c 
LEFT OUTER JOIN scores sc ON c.course_id = sc.course_id 
GROUP BY c.course_name;

解析：

• LEFT OUTER JOIN：左外连接（保留左表courses的所有行，右表scores无匹配则填NULL）
• COUNT(sc.stu_id)：统计右表关联的学生 ID，NULL值不会被计数（相当于统计选课人数）
• 结果示例：即使某课程没人选，也会显示课程名，选课人数为 0

题目 9：创建分区表sales_data，按year和month分区，存储格式为 ORC

正确 SQL：

CREATE TABLE sales_data (
  order_id STRING,
  amount DOUBLE
) 
PARTITIONED BY (year STRING, month STRING) 
STORED AS ORC;

解析：

• PARTITIONED BY：定义分区字段（年、月，必须放在所有数据字段之后）
• STORED AS ORC：使用 ORC 列式存储（压缩率高、查询速度快，适合数据分析）
• 数据存储路径：Hive 自动生成/user/hive/warehouse/sales_data/year=2023/month=10这样的目录

五、易错点专项训练

题目 10：找出错误并修正

❌ 错误 SQL：

SELECT class_id, AVG(score) 
FROM students 
WHERE AVG(score) > 80;  -- 错误：WHERE不能用聚合函数

✅ 修正后：

SELECT class_id, AVG(score) 
FROM students 
GROUP BY class_id 
HAVING AVG(score) > 80;  -- 正确：分组后用HAVING过滤

题目 11：分区表查询必须加分区条件（严格模式下）

❌ 错误 SQL（严格模式报错）：

SELECT * FROM sales_data;  -- 未指定分区，全表扫描危险！

✅ 正确 SQL：

SELECT * FROM sales_data WHERE year='2023' AND month='10';  -- 必须指定分区条件

六、实战项目模拟（企业级场景）

场景：电商用户行为分析

表结构：

• user_info（用户表）：user_id, user_name, gender, age
• order_info（订单表）：order_id, user_id, order_time, total_amount

题目 12：查询 2023 年 10 月订单金额 > 1000 元的用户姓名和订单金额

SELECT u.user_name, o.total_amount 
FROM user_info u 
INNER JOIN order_info o ON u.user_id = o.user_id 
WHERE order_time LIKE '2023-10%' AND o.total_amount > 1000;

解析：

• LIKE '2023-10%'：模糊匹配 10 月的订单时间（假设order_time格式为YYYY-MM-DD HH:MM:SS）
• 连接条件：通过user_id关联用户表和订单表

题目 13：按性别分组，统计每组的平均订单金额和订单数量

SELECT u.gender, 
       AVG(o.total_amount) AS avg_amount, 
       COUNT(o.order_id) AS order_count 
FROM user_info u 
LEFT JOIN order_info o ON u.user_id = o.user_id 
GROUP BY u.gender;

解析：

• LEFT JOIN：保留所有用户，包括没有订单的用户（其订单金额为NULL，不参与平均计算）
• COUNT(o.order_id)：统计订单 ID 数量（NULL值不计数，相当于有效订单数）

七、练习题答案汇总表

题目类型	题目编号	核心知识点	关键语法
基础题	1-3	条件查询、排序、分页	WHERE, ORDER BY, LIMIT
进阶题	4-6	分组统计、外部表、CTAS	GROUP BY, HAVING, CREATE AS
综合题	7-9	多表连接、分区表	JOIN, PARTITIONED BY
易错点专项	10-11	WHERE vs HAVING、分区表规则	聚合函数作用域、严格模式
实战项目	12-13	电商场景分析、复杂连接	LIKE, LEFT JOIN

八、动手实操步骤（建议本地练习）

1. 创建测试表：

   CREATE TABLE students (
     stu_id INT,
     stu_name STRING,
     age INT,
     score DOUBLE,
     class_id INT
   );
   

2. 插入测试数据：

   INSERT INTO students VALUES 
     (1, '张三', 19, 85, 1),
     (2, '李四', 20, 90, 1),
     (3, '王五', 18, 75, 2);
   

3. 执行练习题：对照题目要求，编写 SQL 并查看结果

九、总结：3 个核心学习方法

1. 先理解需求再写 SQL：把每个题目拆解成「要查什么表→需要哪些字段→有什么条件」
2. 掌握错误排查技巧：
   • 报错信息看关键词（如SemanticException通常是语法错误）
   • 用EXPLAIN查看执行计划，定位性能问题
3. 多场景模拟练习：从学生成绩到电商订单，覆盖不同业务场景，积累实战经验

---

通过这10多道题的练习，你已经能应对 80% 的 Hive SQL 基础开发场景。记住：大数据分析的核心不是写复杂 SQL，而是理解业务需求并选择合适的工具（如分区表提升查询效率，外部表实现数据共享）。下一篇我们将深入讲解 Hive 高级特性（窗口函数、自定义函数、性能优化），记得关注哦！ 🌟