在 Text2SQL 的评测与实际落地中，当业务提出涉及层级结构、自关联或者序列状态传递的复杂问题时，大模型（LLM）往往会暴露出一个经典的“思维短板”——俄罗斯套娃式的深层嵌套子查询。

举个典型的业务场景：“查询连续 3 天都有消费，且第 3 天消费金额高于前 2 天平均值的活跃用户”。
很多没有经过特定语法风格约束的大模型，在直觉推理下会写出“主查询套子查询，子查询里再套相关子查询（Correlated Subquery），最里层还在做自关联”的怪物 SQL。

这类深层嵌套子查询不仅长达几十行、人类几乎无法维护，更对底层数仓（如 ClickHouse、Trino、Spark 等）的查询优化器造成了巨大的灾难：

• 优化器很难对深层嵌套子查询做有效的谓词下推（Predicate Pushdown）；
• 相同的事实表被反复全表扫描三到四次；
• 中间算子退化为极其耗费内存的广播嵌套循环（Broadcast Nested Loop）。

今天我们就来系统探讨：如何通过 Prompt 结构约束与 AST 语法改写机制，引导大模型将复杂的自关联深度嵌套消除，优雅重构为扁平化的公用表表达式（CTE, Common Table Expression）与现代窗口函数组合。

一、病灶剖析：大模型为什么沉迷于深层嵌套？

要根治这个问题，首先要理解大模型生成代码的自回归（Autoregressive）机制。

1. 思维链（CoT）与自底向上直觉的冲突：
   大模型在思考业务逻辑时，往往是“先算 A，再在 A 的基础上算 B，最后在 B 的基础上过滤 C”。在自然语言翻译中，最直接将这种依赖关系表达出来的方式就是把前一步骤作为FROM (SELECT ...)的子查询塞进内层。
2. 训练语料中历史遗留 SQL 的污染：
   在很多早期的 SQL 教学案例和旧系统代码中，存在大量的深层子查询。大模型在预训练时学习了大量的这种模式，若不加引导，会默认选择这种“老派”的写法。
3. 缺乏全局抽象表（Global View）的构建意识：
   模型不知道可以在 SQL 开头先统筹规划好 2 到 3 个轻量视图，而是在写到哪一层时就就地起一个子查询。

真实翻车案例：三层自关联的“俄罗斯套娃”

看一段由某知名大模型面对“找出各部门薪资排名前两名员工及其直属上级的部门”时生成的 SQL：

-- 典型的低效深度嵌套写法 (反面教材) SELECT d.dept_name, e1.emp_name, e1.salary, (SELECT m.emp_name FROM ods.employees m WHERE m.emp_id = ( SELECT e2.manager_id FROM ods.employees e2 WHERE e2.emp_id = e1.emp_id )) AS manager_name FROM ods.employees e1 JOIN ods.departments d ON e1.dept_id = d.dept_id WHERE e1.emp_id IN ( SELECT t.emp_id FROM ( SELECT emp_id, dept_id, (SELECT COUNT(*) FROM ods.employees e3 WHERE e3.dept_id = e4.dept_id AND e3.salary >= e4.salary) AS rk FROM ods.employees e4 ) t WHERE t.rk <= 2 );

这段 SQL 在逻辑上虽然能跑通，但执行计划触目惊心：对ods.employees表进行了整整 4 次扫描，且子查询中夹杂着针对每一行都要重新执行一次的相关子查询，在亿级数仓中运行将直接导致集群 CPU 打满挂起。

二、架构重构：扁平 CTE + 窗口函数的高级范式

在现代数仓工程中，消除深度嵌套的核心武器是：扁平公用表表达式（WITH CTE）+ 分析型窗口函数（Analytic Window Functions）。

我们来看看重构后的标准形态：

-- 扁平化 CTE 范式 (现代化、高可读、极致优化) WITH -- 步骤 1: 单次全表扫描，通过窗口函数直接完成组内排序 ranked_employees AS ( SELECT emp_id, emp_name, dept_id, manager_id, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rank_num FROM ods.employees ), -- 步骤 2: 仅过滤出目标前两名员工，极大幅度裁剪后续关联数据量 top_performers AS ( SELECT * FROM ranked_employees WHERE rank_num <= 2 ) -- 步骤 3: 扁平单层关联维度表与自关联上级信息 SELECT d.dept_name, tp.emp_name, tp.salary, m.emp_name AS manager_name FROM top_performers tp JOIN ods.departments d ON tp.dept_id = d.dept_id LEFT JOIN ods.employees m ON tp.manager_id = m.emp_id;

重构带来的质变：

1. I/O 扫描次数从 4 次锐减为 2 次（甚至在有物化重用支持的优化器中仅需 1 次物理 Scan）；
2. 逻辑层次高度线性化：阅读顺序完全符合人类从输入到处理的逻辑流，调试和排错变得一目了然；
3. 充分激活 CBO（基于代价的优化器）：子查询被剥离后，优化器可以轻松将WHERE rank_num <= 2下推为 Window 算子内部的 Top-N 剪枝，直接省去全量排序内存开销。

三、Prompt 工程引导：让大模型主动编写扁平 CTE

为了从源头上阻止大模型吐出“套娃 SQL”，我们必须在 Text2SQL 的系统提示词（System Prompt）中植入明确的结构规范（Structural Constraints）与思考范式。

1. 结构化约束 Prompt 模板

【SQL 架构与风格规范 - 深度嵌套消除】 1. 【严禁三层及以上嵌套】：禁止在 FROM、WHERE、SELECT 中使用超过两层的子查询，严禁编写 Correlated Subquery（相关子查询）。 2. 【必须采用 CTE 线性结构】： - 任何中间阶段的计算、过滤、聚合，必须在最顶层使用 `WITH cte_name AS (...)` 定义； - CTE 命名必须具有明确的业务语义（如 ranked_orders, filtered_users, merchant_summary），严禁使用 t1, t2, subq； - 整个 SQL 的主干必须保持为单一且扁平的最终 SELECT。 3. 【优先使用窗口函数替代自关联】： - 涉及组内排序、前 N 名、首末跳记录时，必须使用 ROW_NUMBER()、RANK() 或 DENSE_RANK()； - 涉及时间跨度环比或状态比较时，优先使用 LAG() / LEAD()，坚决禁止通过自连接（Self Join）去比对上一行。

2. Few-Shot 样本注入

在提示词中注入正反对比样例（Contrastive Examples），是大模型掌握 CTE 转换技巧最有效的手段。展示一个反例（Bad Case）并直接附带标准重构版（Good Case），模型在自回归预测时会优先抑制嵌套生成倾向。

四、编译期 AST 自动扁平化引擎

对于极少数依然偶尔生成两层嵌套子查询的场景，我们可以在 Text2SQL Pipeline 的后处理（Post-Processing）阶段，加入一个基于sqlglot的子查询扁平化改写器（Subquery Flattener）：

import sqlglot from sqlglot import exp def flatten_subqueries_to_cte(sql: str, dialect: str = "spark") -> str: """ 通过 AST 遍历，自动将 FROM 子句中的内联子查询提取为顶层 CTE """ ast = sqlglot.parse_one(sql, read=dialect) cte_counter = 1 new_ctes = [] # 遍历所有顶层或第二层的 Select for select_expr in ast.find_all(exp.Select): from_clause = select_expr.args.get("from") if not from_clause: continue # 检查 FROM 中是否是子查询 (Derived Table) source = from_clause.this if isinstance(source, exp.Subquery): cte_name = f"auto_cte_stage_{cte_counter}" cte_counter += 1 # 将内层子查询剥离出来，构建为 CTE cte_query = source.this new_cte = exp.CTE( this=cte_query, alias=exp.TableAlias(this=exp.to_identifier(cte_name)) ) new_ctes.append(new_cte) # 将原 FROM 目标替换为新 CTE 表名 alias_name = source.alias or cte_name from_clause.set_arg("this", exp.Table(this=exp.to_identifier(cte_name))) if new_ctes: # 如果原本已有 WITH，追加；若没有，新增 WITH existing_with = ast.args.get("with") if existing_with: existing_with.expressions.extend(new_ctes) else: ast.set_arg("with", exp.With(expressions=new_ctes)) return ast.sql(dialect, pretty=True)

通过这一层后置编译器的兜底，即便是边缘 Case 下模型产出的嵌套结构，也能被安全、无损地重构为标准的工业级扁平 CTE。

五、总结

在 Text2SQL 领域，从“能跑出结果”到“写出高质量、高效率的工程级 SQL”，是一道真正的专业分水岭。

深层嵌套消除不仅是代码视觉上的“整容术”，更是数仓资源消耗与并发吞吐的救命解药。
通过**“Prompt 规则强约束 + 窗口函数替代思维 + AST 编译期重构”**三位一体，我们能够彻底驯服大模型的生成习惯，让每一条产出的 SQL 都如同经验丰富的老架构师亲手调优一般精炼、稳健。