大宽表与复杂多表 JOIN 难题：从子查询分解到临时中间表-平芜编程栈

大宽表与复杂多表 JOIN 难题：从子查询分解到临时中间表

在企业级 Text2SQL（自然语言转 SQL）智能体系统的落地攻坚中，最容易让大模型直接“宕机”或写出性能灾难 SQL 的场景，莫过于**“涉及 5 张表以上的多层 JOIN 关联查询”以及“单表超过 100 列的企业级事实大宽表”**。

当业务分析师抛出一个典型的综合经营问题（例如：“统计过去半年各业务线在华东区复购率超过 3 次的 VIP 用户中，退货金额占其总支付金额比例最高的 Top 10 用户画像”）时，如果直接让大模型一口气写出一个包含 4 层嵌套子查询和 6 个LEFT JOIN的巨型 SQL，往往会面临三重灾难：

关联逻辑混乱与笛卡尔积（Cartesian Product）：大模型在多层关联中极易漏写ON a.id = b.a_id或写反关联条件，引发数十亿行的内存级笛卡尔积，直接把线上生产库打挂；
聚合维度膨胀与重复计算：在不同层级的GROUP BY中，一对多关系导致金额指标被重复累加翻倍；
数据库执行计划极差：生成的单条巨型 SQL 无法利用索引，在数仓中执行耗时数分钟甚至超时被 Kill。

攻克这一难题的核心，在于将“一次性生成单条巨型复杂 SQL”的传统范式，重构为“基于多步骤子查询分解（Sub-query Decomposition）与临时中间表（CTE / Temporary Tables）编排”的 Agentic 执行模式。

一、复杂 SQL 的分阶段解耦架构

[ 复杂经营查询需求 ] │ ▼ (Planner 将复合查询分解为 3 个递进阶段) ┌────────────────────────────────────────────────────────┐ │ 阶段 1: 筛选符合条件的 VIP 基础用户集 (CTE 1) │ │ 生成轻量临时表: with_vip_users │ └──────────────────────────┬─────────────────────────────┘ │ ▼ ┌────────────────────────────────────────────────────────┐ │ 阶段 2: 独立计算各用户的复购指标与退款聚合 (CTE 2 & 3) │ │ 生成独立指标表: with_repurchase_stats, with_refund_stats│ └──────────────────────────┬─────────────────────────────┘ │ ▼ ┌────────────────────────────────────────────────────────┐ │ 阶段 3: 主干汇总与比例计算 (Final Join & Order By) │ │ 基于干净的 CTE 临时表执行极简的单层主键关联与排序 │ └────────────────────────────────────────────────────────┘

二、CTE（通用表表达式）在 Text2SQL 中的标准提示词模板

引导大模型使用WITH ... AS (...)（Common Table Expressions, CTE）替代深层嵌套子查询，能够让 SQL 的逻辑像写 Python 代码一样模块化、清晰易懂：

-- 标准 CTE 模块化生成的生产级 SQL 示例 WITH -- 步骤 1: 提取华东区 VIP 用户基础清单 target_vip_users AS ( SELECT user_id, user_name, created_at FROM dim_user WHERE region = 'East_China' AND user_level = 'VIP' AND is_deleted = 0 ), -- 步骤 2: 统计过去半年的复购支付总额与订单数 user_payment_stats AS ( SELECT o.user_id, COUNT(DISTINCT o.order_id) AS total_orders, SUM(o.pay_amount) AS total_paid_amount FROM dwd_orders o INNER JOIN target_vip_users u ON o.user_id = u.user_id WHERE o.pay_time >= NOW() - INTERVAL 180 DAY AND o.order_status = 'COMPLETED' GROUP BY o.user_id HAVING COUNT(DISTINCT o.order_id) >= 3 ), -- 步骤 3: 统计对应的退货退款总金额 user_refund_stats AS ( SELECT r.user_id, SUM(r.refund_amount) AS total_refund_amount FROM dwd_refund_orders r INNER JOIN target_vip_users u ON r.user_id = u.user_id WHERE r.refund_time >= NOW() - INTERVAL 180 DAY AND r.refund_status = 'REFUNDED_SUCCESS' GROUP BY r.user_id ) -- 步骤 4: 最终主干汇总与比例计算 SELECT p.user_id, u.user_name, p.total_orders, p.total_paid_amount, COALESCE(r.total_refund_amount, 0.0) AS total_refund_amount, ROUND(COALESCE(r.total_refund_amount, 0.0) / NULLIF(p.total_paid_amount, 0.0) * 100, 2) AS refund_ratio_pct FROM user_payment_stats p INNER JOIN target_vip_users u ON p.user_id = u.user_id LEFT JOIN user_refund_stats r ON p.user_id = r.user_id ORDER BY refund_ratio_pct DESC LIMIT 10;