CTE

Common Table Expressions - 公共表表达式

举例

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region
), top_regions AS (
    SELECT region FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
) 
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders 
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

常见的数据库支持版本

It was introduced in SQL:1999, the fourth SQL revision.

Mysql - 从8.0 (2018) 开始支持完整的CTE
PostgresSQL - 从8.4 (2009) 开始支持CTE，还扩展了CTE的功能， CTE的query中支持DML语句
SQL Server - 2005年开始支持
SQLite - 从 3.8.3 开始支持
MaxCompute - 1.0 和 2.0 都支持
Hive - 支持

背景知识：UNION 和 UNION ALL 的区别

UNION 其实全名叫 UNION DISTINCT

UNION 结果集去重
UNION ALL 结果集不去重

> select 1 as n union select 1;
+---+
| n |
+---+
| 1 |
+---+
1 rows in set

> select 1 as n union all select 1
+---+
| n |
+---+
| 1 |
| 1 |
+---+
2 rows in set

CTE 的语法

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

非递归

with teacher as (
    select username from passport where role = 1
), student as (
    select username from passport where role = 2
)

递归

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

为什么要用 CTE

CTE 对语句的优化，和具体数据库实现有关

通过拆分复杂语句，提高代码 阅读性 和 可维护性
实现对层次/树状表的递归查询
优化子查询

CTE 的使用分类

递归
非递归

递归查询的执行过程

已实现简单的 linear sequence 举例

WITH RECURSIVE XXX AS (
    {非递归项}
    UNION ALL
    {递归项}
) select ...

执行步骤如下:

执行非递归项其结果作为递归项中对结果的引用，同时将这部分结果放入临时的working table中
重复执行如下步骤，直到working table为空：

a. 计算递归项，用当前 working table 的内容替换递归自引用。将剩下的所有行包括在递归查询的结果中，并且也把它们放在一个临时的中间表中。

b. 用中间表的内容替换 working table 的内容，然后清空中间表。

递归举例

求格力电器 (000651) 最长上涨天数

with recursive t1 as (
    select *,row_number() over(order by tdate) rn 
    from stktrade where sid='000651'
),
t2 as (
    select *,0 rise from t1 where rn=1
union all
    select t1.*, if(t1.close>t2.close,t2.rise+1,0) 
    from t1 join t2 on t1.rn=t2.rn+1
)
select max(rise) from t2;

防止递归死循环

使用 UNION 代替 UNION ALL
使用数据保存已经储存的节点，用于打破循环

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
      SELECT g.id, g.link, g.data, 1, ARRAY[g.id], false 
      FROM graph g
    UNION ALL
      SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path)
      FROM graph g, search_graph sg 
      WHERE g.id = sg.link AND NOT cycle
) SELECT * FROM search_graph;

CTE 和子查询/临时表的性能差异

从理论上讲: CTE和子查询的性能应该相同，因为它们都向查询优化器提供了相同的信息。

CTE:

多次使用的CTE可以轻松识别和计算一次
然后可以存储结果并读取多次

临时表:

优化器可以使用临时表中的统计信息来建立其查询计划。这样可以提高性能。

另外，如果您有一个不止一次使用的复杂CTE(子查询)，那么将其存储在临时表中通常会提高性能。该查询仅执行一次。

CTE

举例