【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_id
    FROM user_actions
    WHERE dt > '20230101'
)
SELECT a.user_id, b.order_count
FROM high_value_users a
JOIN (
    SELECT user_id, COUNT(1) as order_count
    FROM orders
    GROUP 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;