公用表表达式（CTE）

1. 概述

CTE（Common Table Expressions，公用表表达式）功能支持通过 WITH 子句定义一个或多个临时结果集（即公用表），这些结果集可以在同一个查询的后续部分中被多次引用。CTE 提供了一种清晰的方式来构建复杂的查询，使 SQL 代码更易读和维护。

注意：该功能从 V2.0.9-beta 版本开始提供。

2. 语法定义

CTE 的简化 SQL 语法如下：

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

支持简单 CTE 和嵌套 CTE：可以在 WITH 子句中定义一个或多个 CTE，且 CTE 之间可以嵌套引用（但不能前向引用，即不能使用尚未定义的 CTE）。
CTE 名称与源表重名：如果 CTE 名称与源表重名，在外层作用域中只有 CTE 可见，源表将被屏蔽。
CTE 的多次引用：同一个 CTE 在外层查询中可以被多次引用。
Explain / ExplainAnalyze 支持：支持对整个查询进行 Explain 或 ExplainAnalyze，但不支持对 CTE 定义中的 subquery 进行 Explain 或 ExplainAnalyze。
列名指定限制：CTE 定义时指定的列名个数需与 subquery 输出列个数一致，否则报错。
未使用的 CTE：如果定义的 CTE 在查询主体中没有用到，查询仍可正常执行。

3. 使用示例

基于示例数据中的表 table1 和 table2作为源表：

3.1 简单 CTE

WITH cte1 AS (SELECT device_id, temperature FROM table1 WHERE temperature IS NOT NULL),
     cte2 AS (SELECT device_id, humidity FROM table2 WHERE humidity IS NOT NULL)
SELECT * FROM cte1 join cte2 on cte1.device_id = cte2.device_id limit 10;

执行结果

+---------+-----------+---------+--------+
|device_id|temperature|device_id|humidity|
+---------+-----------+---------+--------+
|      100|       90.0|      100|    45.1|
|      100|       90.0|      100|    35.2|
|      100|       90.0|      100|    35.1|
|      100|       85.0|      100|    45.1|
|      100|       85.0|      100|    35.2|
|      100|       85.0|      100|    35.1|
|      100|       85.0|      100|    45.1|
|      100|       85.0|      100|    35.2|
|      100|       85.0|      100|    35.1|
|      100|       88.0|      100|    45.1|
+---------+-----------+---------+--------+
Total line number = 10
It costs 0.075s

3.2 CTE 与源表重名

WITH table1 AS (SELECT time, device_id, temperature FROM table1 WHERE temperature IS NOT NULL)
SELECT * FROM table1 limit 5;

执行结果

+-----------------------------+---------+-----------+
|                         time|device_id|temperature|
+-----------------------------+---------+-----------+
|2024-11-30T09:30:00.000+08:00|      101|       90.0|
|2024-11-30T14:30:00.000+08:00|      101|       90.0|
|2024-11-29T10:00:00.000+08:00|      101|       85.0|
|2024-11-27T16:39:00.000+08:00|      101|       85.0|
|2024-11-27T16:40:00.000+08:00|      101|       85.0|
+-----------------------------+---------+-----------+
Total line number = 5
It costs 0.103s

3.3 嵌套 CTE

WITH
    table1 AS (select device_id, temperature from table1 WHERE temperature IS NOT NULL),
    cte1 AS (select device_id, temperature from table2 WHERE temperature IS NOT NULL),
    table2 AS (select temperature from table1),
    cte2 AS (SELECT temperature FROM table1)
SELECT * FROM table2;

执行结果

+-----------+
|temperature|
+-----------+
|       90.0|
|       90.0|
|       85.0|
|       85.0|
|       85.0|
|       85.0|
|       90.0|
|       85.0|
|       85.0|
|       88.0|
|       90.0|
|       90.0|
+-----------+
Total line number = 12
It costs 0.050s

不支持前向引用

WITH
  cte2 AS (SELECT temperature FROM cte1),
  cte1 AS (select device_id, temperature from table1)
SELECT * FROM cte2;

错误信息

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 550: Table 'database1.cte1' does not exist.

3.4 CTE 的多次引用

WITH cte AS (select device_id, temperature from table1 WHERE temperature IS NOT NULL)
SELECT * FROM cte WHERE temperature > (SELECT avg(temperature ) FROM cte);

执行结果

+---------+-----------+
|device_id|temperature|
+---------+-----------+
|      101|       90.0|
|      101|       90.0|
|      100|       90.0|
|      100|       88.0|
|      100|       90.0|
|      100|       90.0|
+---------+-----------+
Total line number = 6
It costs 0.241s

3.5 Explain 支持

支持整个查询

EXPLAIN WITH cte AS (SELECT * FROM table1) SELECT * FROM cte;

执行结果

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                                                distribution plan|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                            ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐                                                             |
|                                                            │OutputNode-7                                                                                                          │                                                             |
|                                                            │OutputColumns-[time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time] │                                                             |
|                                                            │OutputSymbols: [time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time]│                                                             |
|                                                            └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘                                                             |
|                                                                                                                        │                                                                                                                        |
|                                                                                                                        │                                                                                                                        |
|                                                            ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐                                                             |
|                                                            │Collect-42                                                                                                            │                                                             |
|                                                            │OutputSymbols: [time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time]│                                                             |
|                                                            └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘                                                             |
|                                                            ┌───────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────┐                                                           |
|                                                            │                                                                                                                        │                                                           |
|                                                     ┌───────────┐                                                                                                            ┌───────────┐                                                      |
|                                                     │Exchange-49│                                                                                                            │Exchange-50│                                                      |
|                                                     └───────────┘                                                                                                            └───────────┘                                                      |
|                                                            │                                                                                                                        │                                                           |
|                                                            │                                                                                                                        │                                                           |
|┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐|
|│DeviceTableScanNode-41                                                                                                │ │DeviceTableScanNode-40                                                                                                │|
|│QualifiedTableName: database1.table1                                                                                  │ │QualifiedTableName: database1.table1                                                                                  │|
|│OutputSymbols: [time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time]│ │OutputSymbols: [time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time]│|
|│DeviceNumber: 3                                                                                                       │ │DeviceNumber: 3                                                                                                       │|
|│ScanOrder: ASC                                                                                                        │ │ScanOrder: ASC                                                                                                        │|
|│PushDownOffset: 0                                                                                                     │ │PushDownOffset: 0                                                                                                     │|
|│PushDownLimit: 0                                                                                                      │ │PushDownLimit: 0                                                                                                      │|
|│PushDownLimitToEachDevice: false                                                                                      │ │PushDownLimitToEachDevice: false                                                                                      │|
|│RegionId: 2                                                                                                           │ │RegionId: 1                                                                                                           │|
|└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Total line number = 29
It costs 0.065s

不支持 cte 内部查询

WITH cte AS (EXPLAIN SELECT * FROM table1) SELECT * FROM cte;

错误信息

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 700: line 1:14: mismatched input 'EXPLAIN'. Expecting: <query>