如何简化多段相似CTE？数据库只读无法使用函数

由于你有9个逻辑重复的CTE，仅for字段取值不同，且数据库只读无法创建函数，同时数据量较大，重复扫描表会带来极高的性能开销，推荐用单次关联+条件聚合的方式替代多个CTE，具体实现如下：

优化后的SQL代码 #

WITH combined_cte AS (
    SELECT 
        h.id,
        h."time",
        r."for",
        LEAD(h."time", 1) OVER (
            PARTITION BY h.id
            ORDER BY h.id, h."time"
        ) AS next_time
    FROM history h
    JOIN (
        SELECT id, "for"
        FROM req 
        WHERE type = 'sup' 
          AND "for" IN (1,2,3,4,5,6,7,8,9) -- 列出所有需要的for值
    ) r ON h.id = r.id
)
SELECT
    'History' AS "Indic",
    COUNT(DISTINCT CASE WHEN "for" = 1 THEN id END) AS "cte1",
    COUNT(DISTINCT CASE WHEN "for" = 2 THEN id END) AS "cte2",
    COUNT(DISTINCT CASE WHEN "for" = 3 THEN id END) AS "cte3",
    -- 依次添加for=4到9对应的cte4至cte9
    COUNT(DISTINCT CASE WHEN "for" = 9 THEN id END) AS "cte9"
FROM combined_cte;

方案优势 #

• 降低性能开销：原写法需要对history和req表各扫描9次，优化后仅需扫描1次，大幅减少IO消耗，适配大数据量场景。
• 适配只读环境：无需创建函数、存储过程等对象，仅用基础SQL语法实现。
• 简化维护成本：后续调整for取值或CTE逻辑时，只需修改一处即可，避免重复修改多段代码。

额外说明 #

如果你的实际需求中，除了统计count(distinct id)，还需要单独使用每个for对应的next_time数据，可以直接在combined_cte基础上通过WHERE "for" = N筛选对应分组的数据，同样无需重复创建CTE。

内容的提问来源于stack exchange，提问作者executable