在很多刚接触 Text2SQL 的团队看来，“大模型能够生成包含WHERE和GROUP BY的简单聚合 SQL”就已经大功告成了。
然而，当把系统真正推向生产环境的业务分析师和财务总监时，系统往往会在第一时间惨遭嫌弃：
因为业务在日常经营管理中，极少会问“昨天订单总额是多少”这种单值问题，他们提问的核心几乎全都是复合衍生指标与动态对比分析：
- “看看各省份本周的退款率，以及相比上周同期的**周环比（WoW）**波动；”
- “统计各品类今年截至目前的累计 GMV（YTD）与年度目标达成率；”
- “计算新客与老客的**客单价差异（ARPU）及日环比（DoD）**增速。”
如果缺乏专门针对复合指标的语义模型约束，直接让通用大模型去“临场发挥”，生成的 SQL 几乎必然会掉进以下三个致命坑洞中：
- 除以零异常（Division by Zero）：直接写
a / b，一旦遇到分母为 0 整个查询直接崩溃抛错； - 暴力自连接导致性能雪崩：为了算昨日同比，大模型写了两个包含几千万行事实表的
LEFT JOIN，把集群算力直接吃干抹净； - 窗口函数范围错位：使用
LAG()函数时没有正确处理非连续日期（例如中间某天没有交易），导致拿 3 天前的数据充当了昨天，同环比结果全部算错！
今天我们就来全面系统地攻坚：如何通过语义层（Metrics Semantic Layer）建模与规范化 Prompt，引导大模型精准、稳健地生成包含比率与同环比的高性能 SQL。
一、数学与工程准则：复合指标 SQL 的三大铁律
在大模型生成涉及比率、同环比与累计聚合的 SQL 时，必须在系统底层注入以下三大工业级工程规范：
【复合指标计算的工程黄金三角】 ┌─────────────────────────────────────────────────────────────┐ │ 1. 防御性除法原则 (Defensive Division) │ │ • 严禁直接使用 a / b │ │ • 必须使用 NULLIF(denominator, 0) 或 safe_divide() │ ├─────────────────────────────────────────────────────────────┤ │ 2. 窗口位移替代自关联 (Window LAG / LEAD over Self-Join) │ │ • 严禁为了取历史对比值去 JOIN 相同事实表 │ │ • 强制使用 CTE 汇总后，在轻量结果集上走 LAG() 窗口函数 │ ├─────────────────────────────────────────────────────────────┤ │ 3. 严格日历连续性补齐 (Continuous Date Alignment) │ │ • 业务数据可能存在断流 (某天销量为0) │ │ • 必须先通过日历维表对齐连续日期，防止 LAG() 错位 │ └─────────────────────────────────────────────────────────────┘二、真实翻车案例：大模型的低效暴力写法
我们先来看一段未经语义约束的大模型，在面对“计算华东大区各品类最近 7 天的每日销售额及日环比（DoD）增长率”时生成的典型反面教材：
-- 低效且脆弱的反面教材 (自关联 + 未防除零) SELECT t1.event_date, t1.category_name, t1.daily_gmv, t2.daily_gmv AS yesterday_gmv, -- 致命伤 1: 若 yesterday_gmv 为 0 或 NULL，直接引发除零运行时异常 (t1.daily_gmv - t2.daily_gmv) / t2.daily_gmv AS dod_growth_rate FROM ( SELECT dt AS event_date, category_name, SUM(amount) AS daily_gmv FROM fact_orders WHERE region = '华东' AND dt >= '2026-10-07' GROUP BY dt, category_name ) t1 -- 致命伤 2: 暴力自连接！如果涉及多维度，笛卡尔膨胀和内存开销翻倍 LEFT JOIN ( SELECT dt AS event_date, category_name, SUM(amount) AS daily_gmv FROM fact_orders WHERE region = '华东' AND dt >= '2026-10-06' GROUP BY dt, category_name ) t2 ON t1.category_name = t2.category_name -- 致命伤 3: 字符串日期直接减 1，一旦跨月（如 10月1日 减 1）计算直接错乱！ AND t1.event_date = DATE_ADD(t2.event_date, 1);这段 SQL 不仅跨月时逻辑失效，且对底层的订单事实表进行了两次重复的全量扫描和聚合，在真实生产中运行缓慢且极易崩溃。
三、标准范式：扁平 CTE + 现代窗口函数的极致重构
经过架构优化后的标准生产级 SQL 范式如下：
-- 生产级标准范式 (单次聚合 + 连续窗口 + 安全除法) WITH -- 步骤 1: 单次全表扫描聚合，将明细压缩为极其轻量的中间聚合集 category_daily_agg AS ( SELECT dt AS event_date, category_name, SUM(amount) AS daily_gmv, COUNT(DISTINCT order_id) AS order_cnt, COUNT(DISTINCT user_id) AS buyer_cnt FROM fact_orders -- 核心优化点: 窗口计算向前多取 1 天，确保第一天也能算出日环比！ WHERE region = '华东' AND dt >= DATE_SUB(DATE('2026-10-14'), 7) GROUP BY dt, category_name ), -- 步骤 2: 利用窗口函数 LAG()，在轻量内存结果集上完成平移 enriched_window_metrics AS ( SELECT event_date, category_name, daily_gmv, order_cnt, buyer_cnt, -- 使用 LAG 获取前一天的 GMV，按品类分区、按日期升序排列 LAG(daily_gmv, 1) OVER ( PARTITION BY category_name ORDER BY event_date ASC ) AS prev_day_gmv FROM category_daily_agg ) -- 步骤 3: 最终计算复合指标，严密包裹 NULLIF 防御 SELECT event_date, category_name, daily_gmv, prev_day_gmv, -- 复合比率指标: 客单价 (GMV / 买家数) ROUND(daily_gmv / NULLIF(buyer_cnt, 0), 2) AS arpu, -- 复合比率指标: 笔单价 (GMV / 订单数) ROUND(daily_gmv / NULLIF(order_cnt, 0), 2) AS avg_order_value, -- 动态环比指标: 日环比增长率 ((今日 - 昨日) / 昨日) ROUND( (daily_gmv - prev_day_gmv) * 100.0 / NULLIF(prev_day_gmv, 0), 2 ) AS dod_growth_rate_pct FROM enriched_window_metrics -- 最终仅展示最近 7 天的业务目标时间段 WHERE event_date >= '2026-10-08' ORDER BY event_date DESC, daily_gmv DESC;四、Prompt 工程与指标语义层（Metrics Layer）的设计
要让大模型能够每次都稳定写出上述高水平的 SQL，靠“玄学”提示词是不够的。我们必须在 Prompt 中引入结构化指标元数据层（Metrics Semantic Definition）：
1. 注入 Prompt 的结构化指标定义配置
metrics_semantic_layer: - metric_name: "退款率" formula_sql: "ROUND(SUM(refund_amount) * 100.0 / NULLIF(SUM(pay_amount), 0), 2)" unit: "%" business_meaning: "退款总金额占支付总金额的百分比" - metric_name: "日环比增长率" window_pattern: "ROUND((current_val - prev_val) * 100.0 / NULLIF(prev_val, 0), 2)" lag_expression: "LAG(metric_val, 1) OVER (PARTITION BY [group_keys] ORDER BY [date_col] ASC)" - metric_name: "周同比增长率" lag_expression: "LAG(metric_val, 7) OVER (PARTITION BY [group_keys] ORDER BY [date_col] ASC)"2. 系统级 Prompt 强化约束
在发送给大模型的 Prompt 中追加强制指令：
【高阶指标与同环比生成铁律】 1. 涉及任何比率、除法计算（如达成率、转化率、单价、增速），除号后方必须使用 `NULLIF(分母, 0)` 包裹，严禁裸写 `A / B`。 2. 涉及日环比（DoD）、周同比（WoW）、月同比（MoM）时，严禁使用自关联（Self-Join）！必须使用公用表表达式（WITH CTE）进行一级粗聚合，随后在外部使用 `LAG()` 窗口函数提取基准值。 3. 如果查询要求统计 N 天的同环比，WHERE 条件中的日期过滤必须向前多探寻对应的时间步长（日环比多探寻 1 天，周同比多探寻 7 天），并在最外层 SELECT 过滤回业务要求的展示周期。五、总结
商业洞察的深度，取决于对复合与动态指标的驾驭能力。
让大模型跨过“简单查数”走向“深度经营分析”，关键在于把数仓架构师多年的防御性编程习惯与分析型窗口函数范式，沉淀为标准化的语义层与编译规则。
通过统一的防除零保障、无损的 CTE 窗口位移架构，Text2SQL 才能真正成为辅助管理层进行精细化商业决策的利器。
