无需物化中间表的SQL多步ETL类查询最优组织方式咨询

CTE是否为当前场景的最优方案 #

对绝大多数支持CTE的现代数据库（MySQL 8.0+、PostgreSQL 12+、SQL Server、Spark SQL、Flink SQL等）而言，CTE是完全匹配你需求的最优选择：

1. 可读性和扩展性完全符合ETL分步拆解的要求：每个步骤独立命名、逻辑边界清晰，修改单步逻辑不需要调整整体嵌套结构，后续新增步骤直接追加CTE块即可，远优于多层嵌套子查询的写法
2. 默认无额外性能开销：现代数据库的查询优化器会自动将非物化CTE和等价的嵌套子查询做同一优化，执行计划完全一致，不会产生中间结果物化的额外耗时，只有你主动使用MATERIALIZED关键字（如PostgreSQL）强制物化CTE时才会产生落盘开销
3. 唯一的不适用场景：如果你使用的是不支持CTE的老旧数据库版本（如MySQL 5.x、PostgreSQL 11及更早版本），或数据库对CTE的优化存在缺陷（默认强制物化所有CTE），则CTE不是最优选择。

其他符合要求的SQL组织方式 #

如果你不想用CTE，还有以下几种不需要物化物理表的可选方案：

1. 临时视图 #

当前会话生效的临时视图不会持久化存储数据，执行时会被优化器自动展开，性能和CTE完全一致。优势是支持单独调试每个步骤的结果，你写完单步视图后可以直接执行SELECT * FROM 步骤名验证输出是否符合预期，比CTE调试更方便。
示例代码：

-- 定义各步骤临时视图
CREATE TEMP VIEW first_step AS SELECT * FROM Sales WHERE Country='US';
CREATE TEMP VIEW second_step AS SELECT * FROM NewTable JOIN (SELECT id, age FROM first_step GROUP BY Id) x USING (id);
CREATE TEMP VIEW third_step AS SELECT * FROM OtherTable WHERE someField in (SELECT field FROM second_step);

-- 查询最终结果
SELECT * FROM third_step;

2. 扁平别名子查询 #

如果需要兼容不支持CTE的老旧数据库，可以将多层嵌套的子查询改为扁平的别名结构，避免多层缩进，可读性比嵌套子查询高很多，性能和CTE一致：

SELECT * FROM OtherTable 
WHERE someField in (SELECT field FROM second_step)
-- 提前定义各步骤别名子查询
FROM (
    SELECT * FROM NewTable JOIN (SELECT id, age FROM first_step GROUP BY Id) x USING (id)
) AS second_step,
(
    SELECT * FROM Sales WHERE Country='US'
) AS first_step

3. 数据库脚本变量 #

支持动态SQL的数据库可以将单步逻辑存为变量，按需拼接执行，适合需要批量复用某几个步骤逻辑的场景，不过可读性略低于CTE。

选型建议 #

• 优先选CTE：现代数据库下兼顾可读性、性能和可维护性，是最通用的方案
• 需要分步调试选临时视图：调试效率更高，适合逻辑复杂、步骤多的场景
• 兼容老旧数据库选扁平别名子查询：牺牲少量可读性换兼容性

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