随着SQL的进化，现代SQL执行顺序已经远远超越经典“7步逻辑执行顺序”(FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT)。SQL新语法，不仅扩展了执行阶段，还改变了优化器行为。各数据库也趋向封闭，跨数据库兼容性问题，真的越来越是一个问题。

一、现代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在WINDOW之后、SELECT之前。我们具体地讲：逻辑执行上，QUALIFY在窗口函数计算完成后、SELECT投影前执行。虽然窗口函数语法写在SELECT中，但其计算发生在SELECT逻辑阶段之前；QUALIFY可引用这些已经计算的窗口结果(包括：别名)，但其本质是作用在窗口函数输出的过滤层。我们的理由是：SQL标准中并无QUALIFY，其语义是“对窗口函数结果进行过滤”，因此它不能早于窗口函数计算，但也不属于SELECT投影的一部分。我们看看Snowflake文档，它明确指出：QUALIFYis evaluated after window functions and beforeSELECT DISTINCT。

(2)执行顺序应为：

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

(3)为什么能引用SELECT别名？

在SELECT阶段定义窗口函数别名，但逻辑上在WINDOW阶段已计算完成。QUALIFY可以引用SELECT子句中定义的窗口函数别名(如：rn)，但不能引用SELECT中定义的其他表达式(如：col * 2 AS double_col)。这是因为窗口函数在逻辑上先于SELECT投影计算完成，而QUALIFY是对窗口函数输出的过滤层。SQL引擎允许在QUALIFY中使用这些别名，是为了避免我们重复书写窗口函数表达式，属于我们喜欢的“语法糖”，但其底层逻辑仍要求窗口函数必须在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次登录会话(跨数据库统一逻辑)

我们为每个用户保留最近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分离逻辑。我们看Snowflake官方文档，它明确列出：MATCH_RECOGNIZEcannot be used withGROUP BY,HAVING,WINDOW,QUALIFY, orDISTINCTin the same SELECT。
  • 为保证可移植性，我们强烈建议通过子查询组合使用。

示例1：我们要识别连续3次失败登录(安全风控场景)

检测是否存在连续3次“failed”状态的登录事件：

-- 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或“窗口函数+序列分组”标记连续事件(如：ROW_NUMBER() - ROW_NUMBER() OVER (PARTITION BY user_id, status ORDER BY event_time) AS seq_id)。

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或存储层，减少中间数据量。例如：SELECT * FROM t WHERE a = 1→ 存储引擎只读a=1的行，避免读取全表。

(二)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)，has()适用于 Array(String)、Array(Int)等扁平数组

-- 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扩展，PostgreSQL≥11支持基础向量类型，PostgreSQL≥14+pgvector≥0.5.0支持HNSW索引加速ANN搜索)
递归查询	✔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写逻辑，用宏封装差异，用测试保障一致。