【Hive入门】Hive查询语言(DQL)完全指南：从基础查询到高级分析

• 基础查询示例

-- 基本结构
SELECT [ALL|DISTINCT] column1, column2...
FROM table_name
[WHERE condition]
[GROUP BY columns]
[HAVING condition]
[ORDER BY columns [ASC|DESC]]
[LIMIT n];-- 实际示例
SELECT employee_id, name, salary 
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC
LIMIT 10;

• WHERE条件优化

• 优先使用分区字段过滤
• 避免在WHERE中使用函数

-- 不推荐
SELECT * FROM logs WHERE SUBSTRING(dt, 1, 6) = '202504';
-- 推荐
SELECT * FROM logs WHERE dt LIKE '202504%';

• 常用聚合函数

-- 基础分组
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;-- 多列分组
SELECT year, month, SUM(sales) 
FROM sales_data
GROUP BY year, month;-- 配合HAVING过滤
SELECT product_id, AVG(rating) as avg_rating
FROM product_reviews
GROUP BY product_id
HAVING AVG(rating) > 4.0;

• 排序优化技巧

• 使用LIMIT减少排序数据量

-- 只排序前100条
SELECT * FROM users ORDER BY reg_date DESC LIMIT 100;

• 分区表排序时先过滤

SELECT * FROM logs 
WHERE dt='202504'
ORDER BY click_count DESC;

• 排序类型示例

-- 全局排序(单Reducer)
SELECT * FROM employees ORDER BY salary DESC;-- 分区间排序(多Reducer)
SELECT * FROM employees 
DISTRIBUTE BY department 
SORT BY salary DESC;-- 局部排序(单个Reducer内)
SELECT * FROM employees SORT BY salary DESC;-- 分桶排序(等同于DISTRIBUTE+SORT)
SELECT * FROM employees CLUSTER BY department;

• CTE示例

WITH high_value_users AS (SELECT user_id FROM users WHERE total_spend > 1000
),
active_users AS (SELECT DISTINCT user_idFROM user_actionsWHERE dt > '20230101'
)
SELECT a.user_id, b.order_count
FROM high_value_users a
JOIN (SELECT user_id, COUNT(1) as order_countFROM ordersGROUP BY user_id
) b ON a.user_id = b.user_id;

• 错误处理示例

-- 类型转换示例
SELECT CAST(price AS DECIMAL(10,2)) 
FROM products;-- 内存调整示例
SET mapreduce.map.memory.mb=2048;
SET mapreduce.reduce.memory.mb=4096;