在很多刚接触 Text2SQL 的团队看来“大模型能够生成包含WHERE和GROUP BY的简单聚合 SQL”就已经大功告成了。然而当把系统真正推向生产环境的业务分析师和财务总监时系统往往会在第一时间惨遭嫌弃因为业务在日常经营管理中极少会问“昨天订单总额是多少”这种单值问题他们提问的核心几乎全都是复合衍生指标与动态对比分析“看看各省份本周的退款率以及相比上周同期的**周环比WoW**波动”“统计各品类今年截至目前的累计 GMVYTD与年度目标达成率”“计算新客与老客的**客单价差异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 Definition1. 注入 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 才能真正成为辅助管理层进行精细化商业决策的利器。