作者

InterSystems中国

TEAM

文章 Claire Zheng · 11 小时前 12m read

InterSystems IRIS SQL 中的常见表表达式（CTE）

#InterSystems IRIS #其他 #ObjectScript #SQL #工具

摘要

常见表表达式（CTE）为在SQL语句中定义可重用的中间结果集提供了一个结构化的框架。InterSystems IRIS通过WITH子句实现CTE，在与IRIS基于成本的优化器完全集成的情况下，既能实现更清晰的查询构建，又能支持模块化的分析处理。

本文探讨了 InterSystems IRIS 中 CTE 的语义，解释了其与查询优化的交互关系，讨论了合适的部署场景，并提供了可执行的示例，以说明生产环境中的实用模式。

引言

复杂的 SQL 工作负载通常需要多个逻辑处理阶段（例如过滤、聚合、数据增强和投影）。如果没有抽象机制，这些操作往往会导致查询结构深度嵌套，难以理解和维护。

常见表表达式（CTE）通过允许开发人员定义可在单个 SQL 语句中求值的命名查询组件，解决了这一问题。

在 InterSystems IRIS 中，CTE 通过 SQL 的 WITH 子句实现，并作为逻辑查询表达式运行，与周围的语句一同进行优化 [1]。

InterSystems IRIS 中的常见表表达式

2.1 定义

公共表表达式（Common Table Expression）是在 SELECT 语句之前声明的命名子查询：

WITH PersonSubset AS (
  SELECT ID, Name, Age
  FROM Sample.Person
)
SELECT Name
FROM PersonSubset

要执行上面的示例，请运行以下语句：

CREATE TABLE Sample.Person (
    ID   INTEGER,
    Name VARCHAR(100),
    Age  INTEGER
)

CREATE INDEX idx_person_name
ON Sample.Person (Name)

CREATE INDEX idx_person_age
ON Sample.Person (Age)

INSERT INTO Sample.Person (ID, Name, Age) 
SELECT 1, 'Alice', 30 UNION
SELECT 2, 'Bob', 45 UNION
SELECT 3, 'Carol', 28 UNION
SELECT 4, 'David', 52

根据《InterSystems IRIS SQL 参考手册》，WITH 子句允许将查询表达式作为同一语句中的子查询进行引用 [1]。

主要特征：

作用域仅限于单个 SQL 语句。
能够定义多个CTE。
增强了逻辑组织性。
可充分参与查询优化。

WITH 子句允许我们使用一个或多个 CTE：

WITH CTE1 AS (
  SELECT ...
)

CTE2 AS (
  SELECT ...
)

...

SELECT Name
FROM ...

“查询优化”一词意味着，CTE 定义将与外围查询一同作为统一的执行计划进行分析，从而允许 IRIS 优化器跨 CTE 边界重写、合并和重新排序操作。

2.2 执行语义

在 IRIS 中，CTE 代表一种逻辑抽象，而非物理存储的中间数据集。

优化器可能会执行以下操作：

将 CTE 内联。
合并谓词。
重新排序连接。
消除冗余操作。

示例：

WITH
-- Base filter
OlderPeople AS (
    SELECT ID, Name, Age
    FROM Sample.Person
    WHERE Age >= 18
),

-- Derived computation
AgeGroups AS (
    SELECT
        ID,
        Name,
        Age,
        CASE
            WHEN Age >= 60 THEN 'Senior'
            WHEN Age >= 40 THEN 'Middle'
            ELSE 'Adult'
        END AS AgeCategory
    FROM OlderPeople
),

-- Additional filter
MiddleAged AS (
    SELECT *
    FROM AgeGroups
    WHERE AgeCategory = 'Middle'
),

-- Aggregation
Stats AS (
    SELECT
        COUNT(*) AS TotalPeople,
        AVG(Age) AS AvgAge
    FROM MiddleAged
)

SELECT *
FROM Stats

执行计划：

Statement Text

WITH OlderPeople AS ( SELECT ID , Name , Age FROM Sample . Person WHERE Age >= ? ) , AgeGroups AS ( SELECT ID , Name , Age , CASE WHEN Age >= ? THEN ? WHEN Age >= ? THEN ? ELSE ? END AS AgeCategory FROM OlderPeople ) , MiddleAged AS ( SELECT * FROM AgeGroups WHERE AgeCategory = ? ) , Stats AS ( SELECT COUNT ( * ) AS TotalPeople , AVG ( Age ) AS AvgAge FROM MiddleAged ) SELECT * FROM Stats /*#OPTIONS {"DynamicSQLTypeList":"10,10,1,10,1,1,1"} */

Query Plan

Relative Cost = 21000

• Call module E.
• Output the row.

Module: E

• Read index map Sample.Person.idx_person_age, looping on Age (with a range condition) and ID1.
• For each row:
    - Accumulate the count(rows).
    - Accumulate the count(Age).
    - Accumulate the sum(Age).

该执行过程等同于一个查询计划。

SELECT
    COUNT(*),
    AVG(Age)
FROM Sample.Person
WHERE Age BETWEEN 40 AND 59

执行计划：

Statement Text

SELECT COUNT ( * ) , AVG ( Age ) FROM Sample . Person WHERE Age BETWEEN ? AND ? /*#OPTIONS {"DynamicSQLTypeList":"10,10"} */

Query Plan

Relative Cost = 271.99

• Call module B.
• Output the row.

Module: B

• Read index map Sample.Person.idx_person_age, looping on Age (with a range condition) and ID1.
• For each row:
    - Accumulate the count(rows).
    - Accumulate the count(Age).
    - Accumulate the sum(Age).

请注意，这两个语句生成的执行计划相同，这意味着它们的运行时性能预计是等效的。尽管CTE版本引入了若干中间逻辑步骤，但查询优化器会将其简化为单一的优化执行策略。

因此，使用CTE主要能提高代码的清晰度和可维护性，而性能则取决于整体的查询设计。

2.3 分层CTE定义

可以将多个 CTE 串联起来，形成分析管道。

WITH Base AS (
  SELECT ID, Age
  FROM Sample.Person
),

Adults AS (
  SELECT *
  FROM Base
  WHERE Age >= 18
)

SELECT *
FROM Adults

每一层代表一个逻辑转换阶段。

这种模式对于报表和分析型 SQL 特别有用。

2.4 与其他查询抽象模型的关系

CTE 与其他 IRIS SQL 结构并存：

机制	作用域	实例化	典型用法
CTE	本地	逻辑	查询结构化
子查询	局部	逻辑	简单筛选
视图	持久	逻辑	可重用抽象
临时表	会话	物理	复用与性能控制

尽管CTE和子查询的作用域有限，但CTE可在单个语句内提供可重用的命名结果集，而子查询仅在其使用处存在，若需再次引用则必须重写。

选择何种合适的抽象方式，取决于重用需求和数据量。

使用模式

以下语句对于接下来的示例是必要的。如果您想了解它们的工作原理，请运行这些语句。

CREATE TABLE DemoCTE.DemoOrders (
    ID INTEGER,
    Customer VARCHAR(30),
    Amount NUMERIC(10,2),
    OrderYear INTEGER
)

CREATE INDEX DemoOrdersCustomerIdx ON DemoCTE.DemoOrders(Customer)

CREATE INDEX DemoOrdersAmountIdx ON DemoCTE.DemoOrders(Amount)

CREATE INDEX DemoOrdersYearIdx ON DemoCTE.DemoOrders(OrderYear)

INSERT INTO DemoCTE.DemoOrders 
SELECT 1,'Alice',1200,2025 UNION
SELECT 2,'Bob',300,2025 UNION
SELECT 3,'Alice',800,2024 UNION
SELECT 4,'Carol',1500,2025

CREATE TABLE DemoCTE.Sales (
    ID INTEGER,
    Customer VARCHAR(30),
    Amount NUMERIC(10,2),
    Year INTEGER
)

CREATE INDEX SalesCustomerIdx ON DemoCTE.Sales(Customer)

CREATE INDEX SalesYearIdx ON DemoCTE.Sales(Year)

CREATE INDEX SalesAmountIdx ON DemoCTE.Sales(Amount)

INSERT INTO DemoCTE.Sales 
SELECT 1,'Alice',100,2025 UNION
SELECT 2,'Alice',300,2025 UNION
SELECT 3,'Bob',200,2025 UNION
SELECT 4,'Bob',50,2024 UNION
SELECT 5,'Carol',900,2025

3.1 查询分解

CTE 通过隔离逻辑关注点，简化了复杂的 SQL 语句。

WITH
Orders2025 AS (
    SELECT Customer, SUM(Amount) AS TotalOrders2025
    FROM DemoCTE.DemoOrders
    WHERE OrderYear = 2025
    GROUP BY Customer
),

HistoricalSales AS (
    SELECT Customer, AVG(Amount) AS AvgSales
    FROM DemoCTE.Sales
    GROUP BY Customer
)

SELECT
    o.Customer,
    o.TotalOrders2025,
    s.AvgSales
FROM Orders2025 o
JOIN HistoricalSales s
    ON o.Customer = s.Customer
WHERE o.TotalOrders2025 > s.AvgSales

这种方法减少了嵌套，提高了可读性。

要验证这一点，请将其与相应的非CTE版本进行比较：

SELECT
    o.Customer,
    o.TotalOrders2025,
    s.AvgSales
FROM (
    SELECT Customer, SUM(Amount) AS TotalOrders2025
    FROM DemoCTE.DemoOrders
    WHERE OrderYear = 2025
    GROUP BY Customer
) o
JOIN (
    SELECT Customer, AVG(Amount) AS AvgSales
    FROM DemoCTE.Sales
    GROUP BY Customer
) s
ON o.Customer = s.Customer
WHERE o.TotalOrders2025 > s.AvgSales

逻辑虽然相同，但与CTE版本相比，其可读性明显更差。

3.2 多阶段数据转换

CTE 能够自然地表达数据转换管道。

WITH Filtered AS (
  SELECT *
  FROM DemoCTE.Sales
  WHERE Amount > 100
),

Aggregated AS (
  SELECT Customer, SUM(Amount) Total
  FROM Filtered
  GROUP BY Customer
)

SELECT *
FROM Aggregated
ORDER BY Total DESC

所有操作均在 SQL 优化器内部完成。

通常，需要多个逻辑步骤（例如过滤、聚合和比较）的查询非常适合使用CTE，因为如果没有CTE，查询结构会变得层层嵌套，更难理解。

3.3 中间结果的复用

在同一个查询中，可以多次引用同一个 CTE。

WITH HighSales AS (
  SELECT *
  FROM DemoCTE.Sales
  WHERE Amount > 500
)

SELECT COUNT(*)
FROM HighSales

UNION ALL

SELECT SUM(Amount)
FROM HighSales

由于 CTE 更多是逻辑概念而非物理概念，因此根据优化器的决策，重复引用可能会导致重新评估。

性能注意事项

4.1 优化行为

InterSystems IRIS 将 CTE 视为统一执行计划的一部分，而非独立步骤。

由此产生的后果包括以下几点：

执行顺序由优化器决定。
可能会发生谓词下推。
CTE 的边界并不强制要求进行实例化。

当外层查询中定义的过滤条件在执行过程中更早地应用时，就会发生谓词下推，这使得优化器能够在后续操作之前减少要处理的行数。

例如，请看以下代码：

WITH SalesData AS (
  SELECT *
  FROM DemoCTE.Sales
)

SELECT *
FROM SalesData
WHERE Customer = 'Alice'

执行计划：

Statement Text

WITH SalesData AS ( SELECT * FROM DemoCTE . Sales ) SELECT * FROM SalesData WHERE Customer = ? /*#OPTIONS {"DynamicSQLTypeList":"1"} */

Query Plan

Relative Cost = 936

• Read index map DemoCTE.Sales.SalesCustomerIdx, using the given %SQLUPPER(Customer), and looping on ID1.
• For each row:
    - Read the master map DemoCTE.Sales.IDKEY, using the given IDKEY value.
    - Output the row.

优化器识别出该过滤条件可以更早应用，因此在内部重写了查询：

SELECT *
FROM DemoCTE.Sales
WHERE Customer = 'Alice'

执行计划：

Statement Text

SELECT * FROM DemoCTE . Sales WHERE Customer = ? /*#OPTIONS {"DynamicSQLTypeList":"1"} */

Query Plan

Relative Cost = 936

• Read index map DemoCTE.Sales.SalesCustomerIdx, using the given %SQLUPPER(Customer), and looping on ID1.
• For each row:
    - Read the master map DemoCTE.Sales.IDKEY, using the given IDKEY value.
    - Output the row.

该谓词已被下推至基础表访问操作中。您可以通过观察两个执行计划完全一致这一点来验证这一点。

因此，性能评估应基于执行计划分析，而非仅依赖查询结构。

4.2 索引与基数

性能主要取决于底层表和数据访问路径。因此，适用于一般 SQL 查询优化的最佳实践在此情境下同样有效：

使用索引进行连接和筛选列，
避免不必要的行展开，
保持准确的统计信息。

通过避免不必要的行扩展，您可以在设计查询时最大限度地减少中间结果集的过度增长（“行扩展”）。尽管 IRIS 优化器可能会应用谓词下推来预先过滤行，但这种优化并非总是可行（例如，当谓词依赖于连接、聚合或计算结果时）。在逻辑上尽可能早地引入选择性过滤条件，并适当为连接列建立索引，有助于防止产生庞大的中间数据集，并改善基数估计。

CTE 本身既不会本质上提高性能，也不会降低性能。

何时 CTE 并非最佳选择

虽然CTE在逻辑结构化方面很有效，但在某些场景下，采用其他方法会更有利。

庞大或需重复利用的中间结果

当多次访问中间数据集时，显式 materialization 可能更为合适：

CREATE GLOBAL TEMPORARY TABLE TopCustomers AS
SELECT Customer, SUM(Amount) Total
FROM DemoCTE.Sales
GROUP BY Customer

临时表可避免重复计算，并提供可预测的执行成本。

复杂的迭代或分层处理

某些工作负载需要迭代或分层遍历逻辑。然而，由于InterSystems IRIS CTE目前不支持递归，此类场景仍可通过以下方式实现：

自连接、
临时中转表，
基于 ObjectScript 的过程式编排。

这体现了IRIS SQL引擎的设计特征，并在需要迭代行为时，引导开发人员采用SQL与ObjectScript相结合的混合解决方案。

极其深层的CTE分层

CTE 的过度链式嵌套会降低可读性，并使执行计划分析变得复杂。在这种情况下，视图或分阶段查询可能提供更清晰的结构。

完整的可执行示例

示例 1 — 基本 CTE

CREATE TABLE IF NOT EXISTS DemoCTE.DemoOrders(
  ID INT,
  Customer VARCHAR(30),
  Amount NUMERIC(10,2),
  OrderYear INT
)

INSERT INTO DemoCTE.DemoOrders
SELECT 1,'Alice',1200,2025 UNION
SELECT 2,'Bob',300,2025 UNION
SELECT 3,'Alice',800,2024 UNION
SELECT 4,'Carol',1500,2025

WITH HighValue AS (
  SELECT *
  FROM DemoCTE.DemoOrders
  WHERE Amount > 1000
)

SELECT *
FROM HighValue

示例 2 — 多阶段转换

WITH Orders2025 AS (
  SELECT *
  FROM DemoCTE.DemoOrders
  WHERE OrderYear = 2025
),

CustomerTotals AS (
  SELECT Customer,
         SUM(Amount) TotalAmount
  FROM Orders2025
  GROUP BY Customer
)

SELECT *
FROM CustomerTotals
ORDER BY TotalAmount DESC

示例 3 — 重用 CTE

WITH HighSales AS (
  SELECT *
  FROM DemoCTE.DemoOrders
  WHERE Amount > 500
)

SELECT COUNT(*) AS NumOrders
FROM HighSales

UNION ALL

SELECT SUM(Amount)
FROM HighSales

示例 4 — 改用临时表

注意：要运行此示例并查看数据，您必须使用 DBeaver 等 SQL 客户端。如果您通过 IRIS 门户启动该程序，每次执行时进程都会发生变化，您将无法看到临时数据。

CREATE GLOBAL TEMPORARY TABLE DemoCTE.HighSales AS
SELECT *
FROM DemoCTE.DemoOrders
WHERE Amount > 500

SELECT COUNT(*)
FROM DemoCTE.HighSales

SELECT Customer, SUM(Amount)
FROM DemoCTE.HighSales
GROUP BY Customer

示例 5 — 生成 CTE 的动态 SQL（ObjectScript）


Set sql = ""_
"WITH CustomerTotals AS (" _
"  SELECT Customer, SUM(Amount) Total" _
"  FROM DemoCTE.DemoOrders" _
"  GROUP BY Customer" _
") " _
"SELECT * FROM CustomerTotals"
Set stmt = ##class(%SQL.Statement).%New()
$$$ThrowOnError(stmt.%Prepare(sql))
Set result = stmt.%Execute()
If (result.%SQLCODE '= 0) && (result.%SQLCODE '= 100) {
    Throw ##class(%Exception.SQL).CreateFromSQLCODE(result.%SQLCODE, result.%Message)
}
While result.%Next() {
    Write result.Customer, " ", result.Total, !
}

结论

InterSystems IRIS 中的常见表表达式（CTE）为组织复杂的 SQL 查询和构建易于阅读的分析管道提供了一种有效的机制。

最佳实践包括以下内容：

使用 CTE 确保逻辑清晰，
通过执行计划验证性能，
在需要重复使用或迭代时显式地将数据实例化。
当工作负载超出声明式查询模式时，结合使用 SQL 和 ObjectScript。

若应用得当，CTE 能显著有助于构建可维护且高性能的 IRIS SQL 解决方案。

参考文献

[1] InterSystems 公司。WITH (SQL) —— InterSystems IRIS SQL 参考手册 https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_with

脚注

本文在人工智能工具的协助下完成起草和语言润色。所有技术验证和最终审阅均由作者完成。

查看原帖由 @José Pereira

讨论 (0)0

登录或注册以继续

作者

摘要

引言

2.1 定义

2.2 执行语义

2.3 分层CTE定义

2.4 与其他查询抽象模型的关系

3.1 查询分解

3.2 多阶段数据转换

3.3 中间结果的复用

4.1 优化行为

4.2 索引与基数

庞大或需重复利用的中间结果

复杂的迭代或分层处理

极其深层的CTE分层

示例 1 — 基本 CTE

示例 2 — 多阶段转换

示例 3 — 重用 CTE

示例 4 — 改用临时表

示例 5 — 生成 CTE 的动态 SQL（ObjectScript）

参考文献

脚注