[Hive] CTE 通用表达式 WITH关键字

在Hive中，CTE代表的是Common Table Expression（通用表达式），这是一种SQL语句结构，使用WITH关键字定义的子句。

CTE

CTE提供了一种在查询中定义临时结果集的方式，以便后续查询可以引用这些临时结果集，使得查询更加清晰、简洁和可维护。

使用CTE有以下几个优点：

1. 提高可读性：CTE允许将复杂的逻辑分解成易于理解的部分，提高了查询的可读性。
2. 减少重复：通过CTE，可以定义一次某个结果集，然后在后续查询中多次引用，避免了重复定义相同的逻辑。
3. 支持递归：CTE还支持递归查询，这对于处理层次性数据结构，比如组织架构或者树状结构，非常有用。

在Hive中，CTE的语法类似于标准SQL，使用WITH关键字来定义临时表达式，然后在查询中引用这些临时表达式。以下是一个简单的示例：

WITH temp_table AS (
    SELECT column1, column2
    FROM original_table
    WHERE some_condition
)
SELECT *
FROM temp_table
1
2
3
4
5
6
7

在这个例子中，temp_table被定义为一个临时表达式，然后在后续查询中被引用。这种语法使得查询更加模块化和易于理解。

递归 CTE

CTE支持递归查询的示例通常涉及使用WITH RECURSIVE子句来实现。以下是一个简单的示例：

假设我们有一个名为employee的表，其中包含员工ID和他们的直接上级经理ID。

我们想要编写一个递归查询，以便找出每个员工的直接或间接上级经理。

首先，我们使用WITH RECURSIVE定义CTE，并指定递归查询的初始条件。

然后，在递归查询中，我们将CTE与原始表连接，并递归地引用CTE本身，直到达到终止条件。

WITH RECURSIVE ManagerHierarchy AS (
  -- 初始条件：找出所有直接上级经理
  SELECT employee_id, manager_id
  FROM employee
  WHERE employee_id = 'specific_employee_id'

  UNION ALL

  -- 递归查询：连接CTE自身以找出间接上级经理
  SELECT e.employee_id, e.manager_id
  FROM employee e
  JOIN ManagerHierarchy m ON e.employee_id = m.manager_id
)
-- 最终查询：从CTE中选取所有上级经理
SELECT *
FROM ManagerHierarchy;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

在这个示例中，ManagerHierarchy是CTE的名称，我们递归地从employee表中查找直接或间接上级经理，并最终从CTE中选择所有上级经理的结果集。

请注意，具体的SQL语法可能会因数据库管理系统而有所不同，上述示例基于通用的WITH RECURSIVE语法，需要根据具体的数据库系统进行调整。

将多个CTE（公共表表达式）写在同一个SQL文件中

-- file.sql
WITH cte1 AS (
  SELECT column1, column2
  FROM table1
  WHERE condition1
),
cte2 AS (
  SELECT column3, column4
  FROM table2
  WHERE condition2
)
SELECT *
FROM cte1
JOIN cte2 ON cte1.column1 = cte2.column3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

在上面的示例中，我们定义了两个CTE（cte1和cte2），并在最后的查询中使用了这两个CTE进行连接操作。

要执行这个SQL文件，您可以在Hive中使用以下命令：

hive -f file.sql
1

这将执行file.sql文件中的所有SQL语句，包括定义和使用多个CTE。