现代SQL执行顺序深度解析：从7步到10+阶段

随着SQL语言的不断演进，现代SQL的执行顺序早已超越了经典的“7步逻辑执行顺序”（FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT）。新的语法不仅扩展了执行阶段，也深刻改变了优化器的工作方式。同时，各数据库的语法和特性日趋封闭，跨数据库的兼容性问题也变得日益突出。

一、现代SQL执行顺序，不再是经典7步

(一) 经典7步（SQL-92逻辑模型）

1、FROM/JOIN
2、WHERE
3、GROUP BY
4、HAVING
5、SELECT（含表达式、别名）
6、ORDER BY
7、LIMIT/OFFSET

这套模型适用于简单的查询和传统的OLTP场景，但已无法涵盖窗口函数、模式匹配、嵌套数据展开和向量搜索等现代特性。

(二) 现代SQL扩展后的逻辑执行模型（10+阶段）

阶段	说明	引入的语法/特性	执行时机
0、WITH/CTE处理	决定CTE是内联、物化还是递归计算	WITH … AS、WITH … AS MATERIALIZED（Oracle/PG）、WITH RECURSIVE（ANSI标准）	逻辑上早于FROM；部分引擎（如：MySQL）延迟处理递归CTE
1、FROM/JOIN/LATERAL/TABLE FUNCTION	包含相关子查询、表函数调用、横向关联	LATERAL, CROSS APPLY, TABLE(func), OUTER APPLY（SQL Server）	第一阶段（数据源初始化）
2、UNNEST/ARRAY JOIN/FLATTEN	嵌套结构展开（数组、JSON数组、Variant数组）	UNNEST(), ARRAY JOIN, FLATTEN(), JSON_TABLE()（Oracle）	逻辑上在FROM后、WHERE前
3、WHERE	行级过滤（支持普通谓词与子查询谓词）	标准谓词（= / > / <>）、IN()、EXISTS()、BETWEEN	传统阶段
4、GROUP BY/ROLLUP/CUBE/GROUPING SETS	分组聚合与多级汇总	ROLLUP, CUBE, GROUPING SETS	聚合函数（SUM/COUNT）在此计算
5、HAVING	分组后过滤（仅支持分组字段或聚合函数结果）	HAVING SUM(amount) > 1000	依赖GROUP BY结果
6、MATCH_RECOGNIZE（状态机匹配）	模式识别引擎（序列模式匹配）	MATCH_RECOGNIZE（Oracle/Snowflake 7.19+）	在GROUP BY之后、WINDOW之前；需独立指定PARTITION BY和ORDER BY
7、WINDOW（窗口函数计算）	计算ROW_NUMBER()， SUM() OVER等（不改变行数）	窗口函数（ROW_NUMBER/LAG/SUM OVER）	新增阶段（在SELECT前，是计算窗口函数的逻辑执行层，语法上窗口函数写在SELECT子句中，但逻辑执行早于SELECT投影）
8、QUALIFY	窗口函数结果过滤	QUALIFY（Snowflake/BigQuery/Redshift≥3.0）	逻辑执行顺序上，在WINDOW之后、SELECT之前
9、SELECT（投影）	选择列、计算表达式、定义别名（含窗口函数别名，是将窗口函数结果投影为列并赋予别名的层）	SELECT col AS alias, col * 2 AS double_col, ROW_NUMBER() OVER (…) AS rn	传统阶段
10、DISTINCT	结果去重（基于SELECT指定列）	SELECT DISTINCT col1， col2	逻辑上在投影后、排序前
11、ORDER BY	排序（支持普通字段、表达式、向量距离）	ORDER BY, ORDER BY vec <-> target	传统阶段
12、LIMIT/OFFSET	分页（依赖ORDER BY稳定排序）	LIMIT	传统阶段（最后一步）
13、ANN/VECTOR SEARCH	向量近似搜索（基于专用索引加速）	ORDER BY vec <-> target（PGVector）、VECTOR_SEARCH()（Snowflake Cortex）	逻辑上归属ORDER BY → LIMIT阶段，物理层通过ANN索引（如：HNSW）优化距离计算与排序，仅部分引擎支持独立向量范围查询（如：pgvector的<->过滤）

注：在多数场景下，阶段“13”并非新增逻辑阶段，而是对ORDER BY … LIMIT的向量化物理执行优化；在逻辑执行模型中，向量近似搜索（ANN）不构成独立阶段，其语义仍属于ORDER BY distance_expr LIMIT k。数据库通过重写执行计划，将欧氏/余弦距离计算与ANN索引（如：IVFFlat、HNSW）结合，实现物理层加速；但部分数据库（如：PostgreSQL + pgvector）也支持非排序的向量距离过滤。

(三) 部分新阶段详解

1、QUALIFY：窗口后过滤

（1）正确位置：逻辑执行上，QUALIFY在窗口函数计算完成后、SELECT投影前执行。虽然窗口函数语法写在SELECT中，但其计算发生在SELECT逻辑阶段之前；QUALIFY可引用这些已经计算的窗口结果（不包括：别名），本质是作用在窗口函数输出的过滤层。SQL标准中并无QUALIFY，其语义是“对窗口函数结果进行过滤”，因此它不能早于窗口函数计算，但也不属于SELECT投影的一部分。

（2）执行顺序应为：

... → GROUP BY → HAVING → WINDOW → QUALIFY → SELECT → DISTINCT → ORDER BY → LIMIT

（3）为什么能引用SELECT别名？
在SELECT阶段定义窗口函数别名，但逻辑上在WINDOW阶段已计算完成。QUALIFY可以引用SELECT子句中定义的窗口函数别名（如：rn），但不能引用SELECT中定义的其他表达式（如：col * 2 AS double_col）。部分SQL引擎（如Snowflake等）允许在QUALIFY中使用这些别名，是为了避免重复书写窗口函数表达式，属于“语法糖”。

（4）等价转换（正确理解）：

 -- 使用QUALIFY（Snowflake/BigQuery）
 SELECT user_id, session_id
 FROM sessions
 QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) <= 3;
 -- 实际等价于：
 SELECT user_id, session_id
 FROM (
      SELECT user_id, session_id,
             ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) AS rn
      FROM sessions
 ) AS sub
 WHERE rn <= 3;

（5）示例：用户最近3次登录会话（跨数据库统一逻辑）

-- 统一模型（通过dbt宏）
SELECT
    user_id,
    login_time,
    ip_address
FROM {{ ref('stg_logins') }}
{{ qualify("ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC) <= 3") }}

• 在Snowflake/BigQuery/Redshift（≥3.0）中：直接使用QUALIFY，执行计划高效。
• 在PostgreSQL/MySQL/ClickHouse中：自动转为子查询，避免语法错误。

扩展场景：过滤极端值（如：保留订单金额10%-90%分位数）

 SELECT order_id, amount
 FROM orders
 QUALIFY PERCENT_RANK() OVER (ORDER BY amount) BETWEEN 0.1 AND 0.9;

2、MATCH_RECOGNIZE：独立状态机引擎

• 执行逻辑：
  • 对每个PARTITION BY分组（如：用户ID）；
  • 按ORDER BY排序（如：事件时间）；
  • 启动有限状态自动机，逐行匹配PATTERN（如：连续3次失败）；
  • 匹配成功后，根据AFTER MATCH SKIP决定跳转位置（如：TO NEXT ROW）。
• 不属于传统流水线，而是嵌入在GROUP BY之后的专用处理阶段。
• 与GROUP BY共存问题：
  • 目前Oracle和Snowflake的MATCH_RECOGNIZE不允许与GROUP BY、WINDOW、DISTINCT或聚合函数共存在同一查询块。如需组合使用，必须通过子查询或CTE分离逻辑。
  • 为保证可移植性，强烈建议通过子查询组合使用。

示例1：识别连续3次失败登录（安全风控场景）

-- Oracle/Snowflake实现
SELECT *
FROM login_events
MATCH_RECOGNIZE (
    PARTITION BY user_id
    ORDER BY event_time
    MEASURES
        FIRST(fail.event_time) AS first_fail_time,
        COUNT(*) AS fail_count
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN (fail{3})
    DEFINE
        fail AS status = 'failed'
);

示例2：识别用户转化路径（用户行为分析）

SELECT *
FROM user_events
MATCH_RECOGNIZE (
    PARTITION BY user_id
    ORDER BY event_time
    MEASURES
        FIRST(browse.event_time) AS browse_time,
        FIRST(purchase.event_time) AS purchase_time,
        DATEDIFF('second', browse.event_time, purchase.event_time) AS convert_seconds
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN (browse add_to_cart purchase)
    DEFINE
        browse AS event_type = 'product_view',
        add_to_cart AS event_type = 'add_to_cart',
        purchase AS event_type = 'order_pay'
);

• 输出：仅返回满足模式的序列及关键指标（如：转化时长）。
• 优势：比自连接或窗口函数，更简洁、性能更高（状态机逐行匹配，无需回溯）。

替代方案（非Oracle/Snowflake）：在dbt中，通过Python UDF或“窗口函数+序列分组”标记连续事件。

3、ARRAY JOIN/UNNEST：嵌套展开阶段

• 逻辑位置：在FROM之后、WHERE之前。
• 但优化器可能下推谓词（如：ClickHouse/BigQuery），使实际执行更早。
• 风险：如果先展开再过滤，可能导致行数爆炸（如：1行含1000个元素，展开后需过滤999个）。

示例：分析用户点击流中的商品曝光序列
表结构：user_id, session_id, events ARRAY<STRUCT<event_type STRING， item_id INT>>

-- ClickHouse高效写法
SELECT
    user_id,
    e.item_id,
    COUNT(*) AS view_count
FROM (
    SELECT
        user_id,
        arrayFilter(e -> e.event_type = 'view', events) AS filtered_events
    FROM user_sessions
    WHERE arrayExists(e -> e.event_type = 'view', events)  -- 提前过滤含“view”事件的行
)
ARRAY JOIN filtered_events AS e
GROUP BY user_id, e.item_id;

• 对比低效写法：如果先ARRAY JOIN再WHERE e.event_type = ‘view’，可能展开百万级无效事件。
• 收益：I/O降低90%+，查询从30s → 0.5s。

跨引擎嵌套展开语法对比：

数据库	数组展开语法	JSON数组展开语法
ClickHouse	ARRAY JOIN arr AS elem	ARRAY JOIN JSONExtractArrayRaw(json_col) AS elem
Snowflake	LATERAL FLATTEN(arr) AS f	LATERAL FLATTEN(json_col:arr) AS f
BigQuery	UNNEST(arr) AS elem	UNNEST(JSON_EXTRACT_ARRAY(json_col, ‘$.arr’)) AS elem
PostgreSQL	UNNEST(arr) AS elem	jsonb_array_elements(jsonb_col->’arr’) AS elem
Oracle	TABLE(collection)或JSON_TABLE(…)	JSON_TABLE(json_col, ‘$.arr ’ COLUMNS (…))

⚠️ 说明：（1）PostgreSQL的UNNEST()仅支持SQL数组类型（如：int[]、TEXT[]），不支持jsonb类型。jsonb是独立的复合类型，必须通过专用函数（jsonb_array_elements()等）转换为行集合后才能展开。（2）PostgreSQL不允许对jsonb类型直接使用UNNEST()，必须先通过jsonb_array_elements()等函数，将其转换为行集合。

二、优化器行为更复杂

以ClickHouse谓词下推到数组层为例。

(一) 传统优化器行为

谓词下推（Predicate Pushdown）：将WHERE条件尽可能下推到FROM或存储层，减少中间数据量。

(二) ClickHouse的“数组级谓词下推”

ClickHouse支持对嵌套数据结构进行更细粒度的下推，突破传统“行级下推”的限制：

1、场景：events表含Array(Tuple(name， timestamp))或Nested类型

（1）低效写法（先展开再过滤）：

SELECT user_id， event.name
FROM user_events
ARRAY JOIN events AS event
WHERE event.name = 'click';

• 逻辑：先对每行展开所有events元素 → 生成N行 → 再过滤name = ‘click’。
• 如果平均每行1000个事件，仅1个是‘click’，则99.9%的展开是浪费。

（2）高效写法（利用数组函数预过滤）：

SELECT user_id, event.name
FROM (
    SELECT user_id,
           arrayFilter(e -> e.name = 'click'， events) AS filtered
    FROM user_events
    WHERE arrayExists(e -> e.name = 'click'， events)  -- 适用于 Nested/Array(Tuple)
)
ARRAY JOIN filtered AS event;

说明：has(events.name， ‘click’)适用于一维简单数组（如：Array(String)）及Nested类型的虚拟数组列（如：events.name）；对Array(Tuple)类型，需使用arrayExists或arrayFilter。

示例：

-- Nested类型：events.name是虚拟列（Array(String)）
CREATE TABLE t (user_id UInt32, events Nested(name String, ts DateTime));
SELECT * FROM t WHERE has(events.name, 'click'); -- 合法

-- Array(Tuple)：无自动展开
CREATE TABLE t2 (user_id UInt32, events Array(Tuple(name String, ts DateTime)));
-- has(events.name, ...)语法错误

2、ClickHouse优化器做了什么？

（1）arrayExists(…)：

• 利用列式存储，只读相关子列。
• 使用跳数索引（skip index）快速跳过无匹配数据块。
• 避免读取整行数据，减少I/O开销。

（2）arrayFilter(…)：

• 在向量化执行引擎中，通过SIMD指令批量过滤数组元素。
• 只保留满足条件的元素，大幅缩小ARRAY JOIN输入规模。

本质总结：优化器不再只在“行级别”下推，而是在“嵌套结构内部”进行谓词下推，这是对传统关系模型的突破。

(三) 其他数据库嵌套谓词下推能力对比

数据库	嵌套谓词下推能力	关键技术/示例
ClickHouse	强（数组/JSON）	跳数索引、arrayExists/arrayFilter（结构化数据）、has（一维数组）
BigQuery	强（数组/JSON）	列存索引、EXISTS(SELECT 1 FROM UNNEST(arr) e WHERE e.col = ‘val’)下推
Snowflake	有限（数组/Variant）	依赖FLATTEN+ 优化器猜测，需显式指定过滤条件
PostgreSQL	中（JSONB）	需手动创建GIN索引，jsonb_path_exists支持有限；数组支持@>操作符
Oracle	强（JSON）	JSON搜索索引、JSON_EXISTS下推

示例：筛选包含特定标签的用户画像
表结构：user_id， tags Array(String)（如[‘premium’， ‘mobile’， ‘us’]）

-- ClickHouse（一维数组）
SELECT user_id
FROM user_profiles
WHERE has(tags， 'premium');  -- 仅适用于Array(String)

-- ClickHouse（结构化数组或Nested）
SELECT user_id
FROM user_events
WHERE arrayExists(e -> e.name = 'click'， events);  -- arrayExists()适用于Array(Tuple(…))或Nested类型

-- PostgreSQL（一维数组，需GIN索引）
SELECT user_id
FROM user_profiles
WHERE tags @> ARRAY['premium'];

-- PostgreSQL（jsonb数组）
SELECT user_id
FROM user_profiles
WHERE jsonb_path_exists(profile， '$.tags
 ? (@ == "premium")');

-- BigQuery
SELECT user_id
FROM user_profiles
WHERE EXISTS(SELECT 1 FROM UNNEST(tags) t WHERE t = 'premium');

三、跨数据库兼容性极差

为何必须抽象为中间层（如：dbt）？

(一) 问题根源

现代SQL特性已经高度数据库厂商绑定，即使是同功能，语法、性能、语义也存在巨大差异：

功能	Oracle	Snowflake	BigQuery	ClickHouse	PostgreSQL
窗口后过滤	✘（需子查询）	✔QUALIFY	✔QUALIFY	✘（需子查询）	✘（需子查询）
模式匹配	✔MATCH_RECOGNIZE	✔MATCH_RECOGNIZE（7.19+）	✘	✘	✘
嵌套展开	JSON_TABLE/TABLE()	FLATTEN()	UNNEST()	ARRAY JOIN	jsonb_array_elements()/UNNEST()
向量搜索	✔（23c）	✔（Cortex）	✔（Preview）	✘	✔（依赖pgvector扩展）
递归查询	✔CONNECT BY/WITH RECURSIVE	✔WITH RECURSIVE	✔WITH RECURSIVE	✘	✔WITH RECURSIVE

向量搜索说明：向量搜索并非新增逻辑阶段，而是对ORDER BY + LIMIT模式的物理层加速机制。逻辑执行模型中，仍归属ORDER BY → LIMIT阶段。

(二) 解决方法：抽象为中间层（以dbt为例）

1、使用宏（Macros）封装差异

-- macros/qualify.sql
{% macro qualify(original_query, condition) %}
  {% if target.type in ['snowflake'， 'bigquery'] or (target.type == 'redshift' and target.version >= '3.0') %}
    {{ original_query }}
    QUALIFY {{ condition }}
  {% else %}
    SELECT * FROM (
      {{ original_query }}
      -- 假设原查询已包含窗口函数并命名为rn，此处condition为rn<=3
    ) AS sub
    WHERE {{ condition }}
  {% endif %}
{% endmacro %}

注：更健壮的实现，需结合caller()或传入子查询，此处为简化示意。

2、适配层模型

统一输出结构，屏蔽底层差异。

3、测试驱动兼容性

通过dbt-expectations和多目标测试保障一致性。

4、避免使用专属语法

核心模型，用ANSI SQL；专属功能，隔离到专用模块。

一句话总结：用ANSI SQL写逻辑，用宏封装差异，用测试保障一致。随着技术栈日益复杂，深入理解这些底层变化和差异，并结合像dbt这样的工具进行抽象管理，对于构建稳健、可移植的数据平台至关重要。云栈社区将持续分享更多关于数据库内核、分布式系统与数据工程的深度解析。