【自记】Hive SQL 的 WITH 子句用法详解_hive中 with
Hive SQL 中的 WITH 子句（也称为公共表表达式，Common Table Expressions, CTEs）允许你在查询中定义临时命名的结果集，这些结果集可以在后续查询中被引用。使用 WITH 子句可以提高查询的可读性和可维护性，尤其适用于复杂查询。
基本语法
WITH cte_name1 AS (SELECT ...), cte_name2 AS (SELECT ...)SELECT ...FROM cte_name1JOIN cte_name2 ON ...;主要用途
1.简化复杂查询：将大型查询拆分成多个逻辑部分
2.多次引用相同子查询：避免重复编写相同的子查询逻辑
3.分层数据处理：按步骤处理数据，每一步结果可被后续步骤使用
简单示例
假设有一个销售数据表 sales，包含以下字段：
product_id(产品 ID)category(产品类别)amount(销售金额)sales_date(销售日期)
下面的查询使用 WITH 子句计算每个类别的总销售额和平均销售额：
WITH category_sales AS ( SELECT category, SUM(amount) AS total_sales, COUNT(*) AS sale_count FROM sales GROUP BY category)SELECT category, total_sales, total_sales / sale_count AS avg_saleFROM category_salesORDER BY total_sales DESC;多 CTE 示例
可以在一个查询中定义多个 CTE，每个 CTE 可以引用前面定义的 CTE：
WITH daily_sales AS ( SELECT sales_date, category, SUM(amount) AS daily_amount FROM sales GROUP BY sales_date, category ), monthly_avg AS ( SELECT DATE_TRUNC(\'MONTH\', sales_date) AS month, category, AVG(daily_amount) AS avg_daily_sales FROM daily_sales GROUP BY DATE_TRUNC(\'MONTH\', sales_date), category )SELECT month, category, avg_daily_salesFROM monthly_avgORDER BY month, category;CTE 在 JOIN 中的应用
CTE 可以像普通表一样参与 JOIN 操作：
WITH top_products AS ( SELECT product_id, SUM(amount) AS total_amount FROM sales GROUP BY product_id ORDER BY total_amount DESC LIMIT 10 ), product_info AS ( SELECT product_id, product_name, category FROM products )SELECT p.product_id, p.product_name, p.category, s.total_amountFROM top_products sJOIN product_info p ON s.product_id = p.product_id;递归 CTE
Hive 支持递归 CTE，可用于处理层次结构数据：
WITH RECURSIVE employee_hierarchy AS ( SELECT employee_id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.name, e.manager_id, eh.level + 1 FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id)SELECT level, manager_id, employee_id, nameFROM employee_hierarchyORDER BY level, manager_id;使用技巧
1.命名规范：使用有意义的 CTE 名称，提高查询可读性
2.性能优化：复杂查询中合理使用 CTE 可能提高性能，但过度使用可能适得其反
3.嵌套使用：CTE 内部可以引用其他 CTE，形成多层嵌套结构
4.结合窗口函数：CTE 与窗口函数结合使用可以实现更复杂的数据分析
注意事项
1.Hive 0.13.0 及以上版本支持 WITH 子句
2.递归 CTE 需要使用 WITH RECURSIVE 语法
3.确保 CTE 定义的列名唯一且有意义
4.避免在 CTE 中进行过于复杂的操作，保持逻辑清晰
额外的内容：递归的讲解
Hive SQL 中的递归 CTE（Common Table Expression）通过自引用的方式处理层级结构数据，是实现层级查询的强大工具。我将以employee_hierarchy为例详细解释其执行步骤。
递归 CTE 的两个组成部分
1.锚点成员 (Anchor Member)：初始查询，定义基础数据集
2.递归成员 (Recursive Member)：通过 UNION ALL 连接，引用自身扩展数据
WITH RECURSIVE employee_hierarchy AS ( -- 锚点成员：获取顶层管理者 SELECT employee_id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL -- 递归成员：逐级向下查找下属 SELECT e.employee_id, e.name, e.manager_id, eh.level + 1 FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id)执行步骤详解
假设employees表包含以下简化数据：
递归查询按以下步骤执行：
步骤 1：执行锚点成员查询
SELECT employee_id, name, manager_id, 1 AS levelFROM employeesWHERE manager_id IS NULL结果：
1
步骤 2：初始化工作表 (Work Table)
将锚点成员的结果放入临时工作表：
步骤 3：执行递归成员查询
将工作表与原始表连接，查找每个管理者的直接下属：
SELECT e.employee_id, e.name, e.manager_id, eh.level + 1FROM employees eJOIN employee_hierarchy eh ON e.manager_id = eh.employee_id- 当前工作表中只有 Alice (employee_id=1)
- 查找 manager_id=1 的员工，得到 Bob 和 Charlie
- 层级值为上级层级 + 1，即 1+1=2
步骤 4：合并递归结果到工作表
递归成员结果：
合并后工作表：
步骤 5：重复递归过程
- 对工作表中的每个新行 (Bob 和 Charlie) 执行递归查询
- Bob (employee_id=2) 的下属：Dave (4) 和 Eve (5)，层级为 2+1=3
- Charlie (employee_id=3) 的下属：Frank (6)，层级为 2+1=3
递归成员结果：
合并后工作表：
步骤 6：终止条件检查
- 递归在以下情况终止：
- 递归成员查询返回空结果
- 达到最大递归深度限制 (Hive 默认 1000 层)
步骤 7：最终结果输出
SELECT level, manager_id, employee_id, nameFROM employee_hierarchyORDER BY level, manager_id;最终结果：
关键技术细节
层级追踪：通过level字段记录每个员工在组织中的层级
循环检测：Hive 会自动检测并防止无限递归（如 A 管理 B，B 又管理 A 的情况）
性能考虑：递归查询可能导致性能问题，大型数据集建议预先扁平化层级结构
