Common table expression

common table expression

Common table expression簡稱CTE，由SQL:1999標準引入，
目前支持CTE的數據庫有Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), MariaDB (since 10.2), SQLite (since 3.8.3), HyperSQL and H2 (experimental), MySQL8.0.

CTE的語法如下：

WITH [RECURSIVE] with_query [, ...]
SELECT...

with_query:
query_name [ (column_name [,...]) ] AS (SELECT ...)

以下圖示來自MariaDB

Non-recursive CTEs

Recursive CTEs

CTE的使用

CTE的可以使語句更加簡潔

例如以下兩個語句表達的是同一語義，使用CTE比未使用CTE的嵌套查詢更簡潔明了。

1) 使用嵌套子查詢

SELECT MAX(txt), MIN(txt)
FROM
(
  SELECT concat(cte2.txt, cte3.txt) as txt
  FROM
  (
    SELECT CONCAT(cte1.txt,'is a ') as txt
    FROM
    (
      SELECT 'This ' as txt
    ) as cte1
  ) as cte2,
  (
    SELECT 'nice query' as txt
    UNION
    SELECT 'query that rocks'
    UNION
    SELECT 'query'
  ) as cte3
) as cte4;

2) 使用CTE

WITH cte1(txt) AS (SELECT "This "),
     cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),
     cte3(txt) AS (SELECT "nice query" UNION
                   SELECT "query that rocks" UNION
                   SELECT "query"),
     cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
SELECT MAX(txt), MIN(txt) FROM cte4;

CTE 可以進行樹形查詢
初始化這顆樹

create table t1(id int, value char(10), parent_id int);
insert into t1 values(1, 'A', NULL);
insert into t1 values(2, 'B', 1);
insert into t1 values(3, 'C', 1);
insert into t1 values(4, 'D', 1);
insert into t1 values(5, 'E', 2);
insert into t1 values(6, 'F', 2);
insert into t1 values(7, 'G', 4);
insert into t1 values(8, 'H', 6);

1) 層序遍曆

with recursive cte as (
  select id, value, 0 as level from t1 where parent_id is null
  union all
  select t1.id, t1.value, cte.level+1 from cte join t1 on t1.parent_id=cte.id)
select * from cte;
+------+-------+-------+
| id   | value | level |
+------+-------+-------+
|    1 | A     |     0 |
|    2 | B     |     1 |
|    3 | C     |     1 |
|    4 | D     |     1 |
|    5 | E     |     2 |
|    6 | F     |     2 |
|    7 | G     |     2 |
|    8 | H     |     3 |
+------+-------+-------+

2) 深度優先遍曆

with recursive cte as (
  select id, value, 0 as level, CAST(id AS CHAR(200)) AS path  from t1 where parent_id is null
  union all
  select t1.id, t1.value, cte.level+1, CONCAT(cte.path, ",", t1.id)  from cte join t1 on t1.parent_id=cte.id)
select * from cte order by path;
+------+-------+-------+---------+
| id   | value | level | path    |
+------+-------+-------+---------+
|    1 | A     |     0 | 1       |
|    2 | B     |     1 | 1,2     |
|    5 | E     |     2 | 1,2,5   |
|    6 | F     |     2 | 1,2,6   |
|    8 | H     |     3 | 1,2,6,8 |
|    3 | C     |     1 | 1,3     |
|    4 | D     |     1 | 1,4     |
|    7 | G     |     2 | 1,4,7   |
+------+-------+-------+---------+

Oracle

Oracle從9.2才開始支持CTE, 但隻支持non-recursive with, 直到Oracle 11.2才完全支持CTE。但oracle 之前就支持connect by 的樹形查詢，recursive with 語句可以與connect by語句相互轉化。一些相互轉化案例可以參考這裏.

Oracle recursive with 語句不需要指定recursive關鍵字，可以自動識別是否recursive.

Oracle 還支持CTE相關的hint,

WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

WITH dept_count AS (
  SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

“MATERIALIZE”告訴優化器生產一個全局的臨時表保存結果，多次引用CTE時直接訪問臨時表即可。而"INLINE"則表示每次需要解析查詢CTE。

PostgreSQL

PostgreSQL從8.4開始支持CTE，PostgreSQL還擴展了CTE的功能， CTE的query中支持DML語句，例如

create table t1 (c1 int, c2 char(10));
 insert into t1 values(1,'a'),(2,'b');
 select * from t1;
 c1 | c2
----+----
  1 | a
  2 | b


 WITH cte AS (
     UPDATE t1 SET c1= c1 * 2 where c1=1
     RETURNING *
 )
 SELECT * FROM cte; //返回更新的值
 c1 |     c2
----+------------
  2 | a

 truncate table t1;
 insert into t1 values(1,'a'),(2,'b');
 WITH cte AS (
     UPDATE t1 SET c1= c1 * 2 where c1=1
     RETURNING *
 )
 SELECT * FROM t1;//返回原值
 c1 |     c2
----+------------
  1 | a
  2 | b


 truncate table t1;
 insert into t1 values(1,'a'),(2,'b');
 WITH cte AS (
     DELETE FROM t1
     WHERE c1=1
     RETURNING *
 )
 SELECT * FROM cte;//返回刪除的行
 c1 |     c2
----+------------
  1 | a


 truncate table t1;
 insert into t1 values(1,'a'),(2,'b');
 WITH cte AS (
     DELETE FROM t1
     WHERE c1=1
     RETURNING *
 )
 SELECT * FROM t1;//返回原值
 c1 |     c2
----+------------
  1 | a
  2 | b
(2 rows)

MariaDB

MariaDB從10.2開始支持CTE。10.2.1 支持non-recursive CTE, 10.2.2開始支持recursive CTE。目前的GA的版本是10.1.

MySQL

MySQL從8.0開始支持完整的CTE。MySQL8.0還在development
階段，RC都沒有，GA還需時日。

AliSQL

AliSQL基於mariadb10.2， port了no-recursive CTE的實現，此功能近期會上線。

以下從源碼主要相關函數簡要介紹其實現，

//解析識別with table引用
find_table_def_in_with_clauses

//檢查依賴關係，比如不能重複定義with table名字
With_clause::check_dependencies

// 為每個引用clone一份定義
With_element::clone_parsed_spec

//替換with table指定的列名
With_element::rename_columns_of_derived_unit

此實現對於多次引用CTE，CTE會解析多次，因此此版本CTE有簡化SQL的作用，但效率上沒有效提高。

select count(*) from t1 where c2 !='z';
+----------+
| count(*) |
+----------+
|    65536 |
+----------+
1 row in set (0.25 sec)

//從執行時間來看是進行了3次全表掃描
 with t as (select count(*) from t1 where c2 !='z')
     select * from t union select * from t union select * from t;
+----------+
| count(*) |
+----------+
|    65536 |
+----------+
1 row in set (0.59 sec)

 select count(*) from t1 where c2 !='z'
     union
     select count(*) from t1 where c2 !='z'
     union
    select count(*) from t1 where c2 !='z';
+----------+
| count(*) |
+----------+
|    65536 |
+----------+
1 row in set (0.57 sec)

 explain with t as (select count(*) from t1 where c2 !='z')
    -> select * from t union select * from t union select * from t;
+------+-----------------+--------------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type     | table        | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-----------------+--------------+------+---------------+------+---------+------+-------+-------------+
|    1 | PRIMARY         | <derived2>   | ALL  | NULL          | NULL | NULL    | NULL | 65536 |             |
|    2 | SUBQUERY        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
|    3 | RECURSIVE UNION | <derived5>   | ALL  | NULL          | NULL | NULL    | NULL | 65536 |             |
|    5 | SUBQUERY        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
|    4 | RECURSIVE UNION | <derived6>   | ALL  | NULL          | NULL | NULL    | NULL | 65536 |             |
|    6 | SUBQUERY        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
| NULL | UNION RESULT    | <union1,3,4> | ALL  | NULL          | NULL | NULL    | NULL |  NULL |             |
+------+-----------------+--------------+------+---------------+------+---------+------+-------+-------------+
7 rows in set (0.00 sec)

 explain  select count(*) from t1 where c2 !='z'
    union
    select count(*) from t1 where c2 !='z'
    union
    select count(*) from t1 where c2 !='z';
+------+--------------+--------------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type  | table        | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+--------------+--------------+------+---------------+------+---------+------+-------+-------------+
|    1 | PRIMARY      | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
|    2 | UNION        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
|    3 | UNION        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
| NULL | UNION RESULT | <union1,2,3> | ALL  | NULL          | NULL | NULL    | NULL |  NULL |             |
+------+--------------+--------------+------+---------------+------+---------+------+-------+-------------+
4 rows in set (0.00 sec)

以下是MySQL8.0 隻掃描一次的執行計劃

mysql> explain select count(*) from t1 where c2 !='z' union select count(*) from t1 where c2 !='z' union select count(*) from t1 where c2 !='z';
+----+--------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| id | select_type  | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra           |
+----+--------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
|  1 | PRIMARY      | t1           | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62836 |    90.00 | Using where     |
|  2 | UNION        | t1           | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62836 |    90.00 | Using where     |
|  3 | UNION        | t1           | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62836 |    90.00 | Using where     |
| NULL | UNION RESULT | <union1,2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  NULL |     NULL | Using temporary |
+----+--------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

以下是PostgreSQL9.4 隻掃描一次的執行計劃

postgres=# explain with t as (select count(*) from t1 where c2 !='z')
postgres-# select * from t union select * from t union select * from t;
 HashAggregate  (cost=391366.28..391366.31 rows=3 width=8)
   Group Key: t.count
   CTE t
     ->  Aggregate  (cost=391366.17..391366.18 rows=1 width=0)
           ->  Seq Scan on t1  (cost=0.00..384392.81 rows=2789345 width=0)
                 Filter: ((c2)::text <> 'z'::text)
   ->  Append  (cost=0.00..0.09 rows=3 width=8)
         ->  CTE Scan on t  (cost=0.00..0.02 rows=1 width=8)
         ->  CTE Scan on t t_1  (cost=0.00..0.02 rows=1 width=8)
         ->  CTE Scan on t t_2  (cost=0.00..0.02 rows=1 width=8)

AliSQL還有待改進。

最後更新：2017-04-17 20:00:44

Common table expression

common table expression

CTE的使用

Oracle

PostgreSQL

MariaDB

MySQL

AliSQL

上一篇：浙江啟動“十萬企業上雲” 阿裏雲聯合生態首個響應

下一篇：血氧探頭工作原理

相關內容

熱門內容

最新內容

Common table expression

common table expression

CTE的使用

Oracle

PostgreSQL

MariaDB

MySQL

AliSQL

上一篇： 浙江啟動“十萬企業上雲” 阿裏雲聯合生態首個響應

下一篇： 血氧探頭工作原理

相關內容

熱門內容

最新內容

上一篇：浙江啟動“十萬企業上雲” 阿裏雲聯合生態首個響應

下一篇：血氧探頭工作原理