WITH (CTEs)

定义公用表表达式（CTE），供随后的 SELECT、INSERT、UPDATE 或 DELETE 语句使用。CTE 充当临时的命名结果集，仅在查询执行期间存在。

另请参阅： [SELECT] [INSERT] [UPDATE] [DELETE]

---

语法

withClause::=

cteDefinition::=

   

WITH [RECURSIVE] cte_name [(column_name, ...)] AS (
    select_statement
)
[, cte_name [(column_name, ...)] AS (
    select_statement
)]
SELECT ... FROM cte_name ...;

参数

RECURSIVE 可选关键字，允许 CTE 引用自身，从而支持递归查询，例如遍历层次结构数据。

cte_name 为 CTE 指定的名称。在主查询中可以像引用表一样引用该名称。

column_name 可选。CTE 结果集的列名。如果未指定，列名将从 SELECT 语句推导得出。

select_statement 定义 CTE 结果集的查询。

返回值

CTE 不直接返回值。它们定义可在主查询中引用的临时结果集。

---

示例

用于组织复杂查询的基本 CTE

CTE 可以将复杂查询拆分为具名的逻辑组成部分，从而简化查询。

-- Calculate moving average of commit activity
WITH weekly_totals AS (
    SELECT
        week,
        SUM(json_each.value) as commits_this_week
    FROM github.repos.stats_commit_activity, JSON_EACH(days)
    WHERE owner = 'stackql'
      AND repo = 'stackql'
    GROUP BY week
)
SELECT
    week,
    commits_this_week,
    ROUND(AVG(commits_this_week) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 1) as four_week_moving_avg,
    SUM(commits_this_week) OVER (ORDER BY week) as cumulative_commits
FROM weekly_totals
ORDER BY week;

使用 UNION ALL 合并多个来源的数据

CTE 适合在执行分析之前合并多个来源的数据。

-- Rank total contributions across multiple repos
WITH all_contributors AS (
    SELECT login, contributions, html_url
    FROM github.repos.contributors
    WHERE owner = 'stackql' AND repo = 'stackql'
    UNION ALL
    SELECT login, contributions, html_url
    FROM github.repos.contributors
    WHERE owner = 'stackql' AND repo = 'stackql-deploy'
    UNION ALL
    SELECT login, contributions, html_url
    FROM github.repos.contributors
    WHERE owner = 'stackql' AND repo = 'stackql-provider-registry'
)
SELECT
    DENSE_RANK() OVER (ORDER BY SUM(contributions) DESC) as rank,
    login,
    SUM(contributions) as total_contributions,
    MAX(html_url) as html_url
FROM all_contributors
GROUP BY login
ORDER BY total_contributions DESC;

将 CTE 与窗口函数结合用于排名

-- Rank contributors within each repo using CTE and window functions
WITH repo_contributors AS (
    SELECT
        repo,
        login,
        contributions,
        RANK() OVER (PARTITION BY repo ORDER BY contributions DESC) as rank_in_repo,
        SUM(contributions) OVER (PARTITION BY repo) as repo_total_contributions
    FROM (
        SELECT 'stackql' as repo, login, contributions
        FROM github.repos.contributors
        WHERE owner = 'stackql' AND repo = 'stackql'
        UNION ALL
        SELECT 'stackql-provider-registry' as repo, login, contributions
        FROM github.repos.contributors
        WHERE owner = 'stackql' AND repo = 'stackql-provider-registry'
    ) t
)
SELECT
    repo,
    login,
    contributions,
    rank_in_repo
FROM repo_contributors
ORDER BY repo, rank_in_repo;

在单个查询中使用多个 CTE

可以定义多个 CTE，以逗号分隔。

-- Analyze issues and releases together
WITH open_issues AS (
    SELECT number, title, created_at
    FROM github.issues.issues
    WHERE owner = 'stackql'
      AND repo = 'stackql'
      AND state = 'open'
),
recent_releases AS (
    SELECT tag_name, published_at
    FROM github.repos.releases
    WHERE owner = 'stackql'
      AND repo = 'stackql'
    ORDER BY published_at DESC
    LIMIT 5
)
SELECT
    'Open Issues' as category,
    COUNT(*) as count
FROM open_issues
UNION ALL
SELECT
    'Recent Releases' as category,
    COUNT(*) as count
FROM recent_releases;

递归 CTE

递归 CTE 允许 CTE 引用自身，从而可以编写遍历层次结构或图状数据结构的查询。

WITH RECURSIVE cte_name (columns) AS (
    -- Base case (anchor member)
    SELECT ...
    UNION ALL
    -- Recursive case (references cte_name)
    SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;

提示

递归 CTE 在处理组织结构图、文件系统或依赖树等层次结构数据时非常强大。务必在递归情形中包含终止条件，以防止无限循环。

最佳实践

1. 使用 CTE 提高可读性：将复杂查询拆分为具名的逻辑组成部分。
2. 与窗口函数结合使用：CTE 与窗口函数配合良好，适用于高级分析。
3. 串联多个 CTE：在后面的 CTE 中引用前面的 CTE，逐步构建复杂逻辑。
4. 考虑性能：虽然 CTE 能提高可读性，但非常大的 CTE 可能影响性能。请使用实际的数据量进行测试。

有关更多信息，请参阅 https://sqlite.org/lang_with.html。