自然语言直接生成SQL与图表：AI数据分析助手从0到1实战

做数据这行久了基本都会遇到一个尴尬局面业务同事天天追着要数但你既要做报表又要写分析还要应付各种临时取数需求。明明很简单的查询逻辑对方描述得云里雾里你把SQL写出来人家又看不懂最后只能变成“你把条件发我我查好给你表格”。这种重复劳动最磨人。我最近完整落地了一个“AI数据分析助手”项目核心就一条链路自然语言 → SQL → 图表。用户用大白话提问系统自动生成SQL去查库再把结果渲染成可视化图表。这篇文章不聊虚的把我从方案选型、架构拆分到代码落地的整个过程包括踩过的坑和关键取舍一次性讲清楚。这套东西适合谁如果你正在做AI应用落地或者你是数据分析师/数据工程师想用大模型减少重复取数工作再或者你所在团队有大量“业务提需求、你写SQL”的协作场景这篇实战记录都应该能给你一些直接能抄的思路。1. 整体方案设计与技术选型1.1 核心需求拆解不只是“生成SQL”那么简单表面上看这个项目的核心是NL2SQL也就是让大模型把自然语言翻译成SQL查询。但真正做起来之后我发现如果只盯着“文本转SQL”这一个环节做出来的东西根本没法在实际场景里用。落到真实业务中完整链路至少包含四个环节自然语言理解与意图识别、SQL生成与校验、SQL执行与结果处理、图表推荐与渲染。前两项决定系统“懂不懂人话”后两项决定业务同事“用不用得起来”。最容易被忽视的是“意图识别”。用户说“看下近30天的销售趋势”有人期望的是折线图有人期望的是明细表。“趋势”这个词在不同人嘴里含义完全不同如果不做意图分类直接把所有问题都往SQL上转结果就是系统虽然“答对了”但呈现形式完全不对使用者还是觉得难用。我最终确定的方案是用大模型做“意图理解SQL生成”用规则引擎做“查询结果分析图表类型推荐”再用前端库完成渲染。核心思想是让大模型干它擅长的事把确定性的判断逻辑留给代码。1.2 为什么选择“提示词工程”而不是微调模型项目启动时我面临的第一道选择题要不要微调一个专门的NL2SQL模型坦白说如果你的查询场景非常固定比如只有10种固定报表并且数据表结构常年不变微调一个模型效果确实更好。但大多数实际场景不是这样的数据库表经常加字段业务问题千奇百怪今天问“销售额”明天问“退货率”后天问“库存周转天数”。我选择的路线是以现有大模型为基础配合精心设计的提示词、表结构描述Schema和少量示例来约束输出。这个方案的核心优势有三个零训练成本不需要GPU资源调用API即可迭代速度快。适应变化数据库表结构变了改提示词里的Schema描述就行不用重新训练。可控性强SQL生成逻辑可以加约束和校验出错时能快速定位是提示词问题还是模型理解问题。作为补充我在API调用之外又架了一层规则先让模型输出JSON格式的结果包含“意图类型”“生成的SQL”“需要展示的字段”等字段再通过代码做二次校验避免模型偶尔抽风生成语法错误的SQL。1.3 技术栈选型和架构分层整个系统的技术栈选型遵循“成熟稳定快速迭代”的原则我没有引入太重的东西。架构上分成了四层层次职责技术选型交互层用户输入问题、展示图表与结论Web前端Vue ECharts语义层意图理解、SQL生成、结果解读大模型API 提示词模板服务层SQL校验、执行、结果格式化Python FastAPI数据层数据存储与查询关系型数据库业务库只读从库数据库特意连的是只读从库这个后面在安全部分详细说。服务层用FastAPI是因为它轻量、异步支持好写接口效率高。整个服务就是“接收用户问题 → 组装提示词 → 调用模型 → 解析返回的JSON → 校验并执行SQL → 格式化结果 → 推荐图表类型 → 返回前端”。2. 核心细节解析与实操要点2.1 提示词模板设计决定SQL质量的第一要素我在这个项目里打磨最久的就是提示词模板。NL2SQL的提示词不是简单地把“请把这句话转成SQL”丢给模型而是需要系统性地把数据库结构、业务规则、输出格式要求全部注入进去。一个直接可用的思路是使用三段式提示词模板第一段是“角色与任务定义”明确告诉模型“你是数据分析助手根据用户问题生成SQL查询语句”。第二段是“数据库结构描述”也就是Schema信息。这一步最关键也最容易被忽视。我不建议把整个数据库的所有表结构和全部字段一股脑丢给提示词模型处理长上下文的准确率会下降而且字段一多很容易生成错误的关联关系。我的做法是先维护一份“常用查询字段清单”只把核心表和常用字段放进提示词。第三段是“输出格式约束”要求模型必须输出严格的JSON格式包括querySQL语句、type图表类型、summary数据解读三个字段。格式约束能用代码降低解析成本同时倒逼模型更结构化地思考。我在提示词里还会注入少量Few-shot示例直接告诉模型“类似这样的问题应该生成这样的SQL”。示例的选择有讲究不一定选最复杂的要选最能体现常见查询逻辑的时间范围过滤、分组聚合、排序限制。2.2 Schema注入与业务术语映射做NL2SQL的人都会遇到一个大坑模型不理解业务术语。业务同事问“看下GMV”模型可能不知道GMV在数据库里是“订单金额扣除退款后的净额”而数据库里可能根本没有叫GMV的字段。解决这个问题我采用了两层策略。第一层是在Schema描述里直接给字段加“业务注释”比如order_amount后面标注“(即常说的GMV指用户实际支付金额不含退款)”这样模型在生成SQL时就知道该用哪个字段。第二层是建立“业务术语同义词映射表”。我维护了一份配置里面记录常见业务词对应的标准字段名组合。当用户提问中包含“GMV”但模型没正确映射时代码层可以做一个简单的关键词替换作为兜底方案。这里有个经验抽取Schema时建议把字段的“业务含义”写在注释里不写数据库里的物理注释而是写业务侧的通俗叫法。比如数据库字段叫user_ratio业务注释写成“用户留存率次日留存”。实际测试下来带上这种业务语义的Schema比纯字段名加类型的描述SQL正确率能提升不少。2.3 安全性设计只读账号与SQL校验缺一不可SQL生成类应用最大的风险就是“模型生成了一条破坏性SQL”。测试阶段我亲眼见过模型把SELECT写成了DROP还好当时我用的是只读权限账号否则后果想都不敢想。基于这个教训我的安全方案从三层来保障第一层是数据库账号隔离。系统连接的是一个仅有SELECT权限的只读账号从根上杜绝了写操作的可能性。这个不用多说但确实是最容易省掉的一步。第二层是SQL语法校验层。我在执行SQL之前用sqlglot这个Python库对生成的SQL先做一次解析确认这是一个合法的SELECT查询不包含INSERT、UPDATE、DELETE、DROP、ALTER等非查询语句。第三层是敏感操作拦截。哪怕SQL通过了语法校验我还要检查SQL里是否包含对系统表比如pg_catalog、information_schema的访问防止有恶意构造的提示词绕过模型直接注入非法查询。还需要提一个常见的坑大模型生成的SQL经常会带上反引号包裹字段名这种写法在某些数据库里会报语法错误需要在执行前统一处理去掉多余的反引号或转换为目标数据库支持的引用符。2.4 图表推荐规则不依赖大模型的确定性逻辑最开始我做图表推荐是让大模型直接输出图表类型结果发现效果并不稳定。模型经常推荐出不符合数据特征的图表比如只有两个数据点也推荐折线图分类字段有20个也推荐饼图图都糊成一团了。后来我把图表推荐从模型决策改成了规则引擎决策准确率一下子提升了不少。核心规则其实不复杂查询结果只有一条记录 → 推荐指标卡/数字卡片。结果包含时间字段且按时间排序 → 推荐折线图趋势。结果包含分类字段且只有1-2个维度 → 推荐柱状图。结果包含分类字段且是占比关系 → 推荐饼图但分类数超过8个时自动改为柱状图。这套规则放在后端一个独立的函数里输入是SQL查询结果的列名和行数输出是图表类型逻辑完全可预期、可测试不用承担大模型的随机性。3. 实操过程与核心环节实现3.1 环境搭建与项目结构整个项目可以快速跑通的最小环境要求不高一台能联网的服务器Python 3.9以上一个数据库我用的标准关系型数据库做示例以及一个大模型API的访问凭证。我的项目目录结构大致如下nl2sql-assistant/ ├── app/ │ ├── main.py # FastAPI 入口 │ ├── nlp/ │ │ ├── llm_client.py # 大模型调用封装 │ │ └── prompt_builder.py # 提示词构建 │ ├── query/ │ │ ├── sql_executor.py # SQL执行与结果格式化 │ │ └── sql_validator.py # SQL校验 │ ├── chart/ │ │ └── chart_recommender.py # 图表推荐规则 │ └── config.py # 配置信息 ├── data/ │ └── schema.json # 表结构描述 └── requirements.txt依赖的核心库包括fastapiWeb框架、uvicorn服务启动、sqlglotSQL解析校验、openai或类似SDK大模型调用、pymysql或psycopg2数据库驱动和pandas数据处理。3.2 提示词构建的完整代码示例这是整个系统最核心的代码我直接给出一个可参考的实现。实际使用中Table Schema会从schema.json读取这里为了易懂直接写在代码里。# prompt_builder.py def build_question_prompt(question: str, schema_desc: str, examples: list) - str: system_prompt ( 你是一名资深数据分析师。你的任务是根据用户的自然语言提问 结合给定的数据库表结构生成正确的SQL查询语句。\n 注意\n 1. 只生成SELECT查询语句禁止生成其他任何类型的SQL。\n 2. 必须使用给定的表名和字段名不允许臆造不存在的字段。\n 3. 根据问题语义判断是否需要聚合、分组、排序或限制返回行数。\n 4. 如果问题中指定了时间范围使用WHERE子句进行过滤。\n ) user_prompt f数据库表结构如下\n{schema_desc}\n if examples: user_prompt 参考示例\n examples \n user_prompt f用户问题{question}\n user_prompt ( 请输出严格JSON格式不要输出额外文字格式如下\n {query: 生成的SQL语句, type: line|bar|pie|table, summary: 一句话数据解读} ) messages [ {role: system, content: system_prompt}, {role: user, content: user_prompt} ] return messages这里有一个容易踩的坑如果直接把schema_desc以纯文本形式塞进去模型有时会把表结构里的示例数据当成真实数据导致生成的SQL自带一些奇怪的常量条件。我建议schema里只放“字段名类型业务注释”不要放具体示例数据。3.3 SQL校验与执行守住数据安全底线模型返回的SQL字符串不能直接执行必须经过校验。我的校验逻辑分两步# sql_validator.py import sqlglot from sqlglot.errors import ParseError def validate_sql(sql: str) - bool: 验证SQL是否为合法的SELECT查询 try: parsed sqlglot.parse_one(sql) except ParseError: return False # 确认是SELECT语句 if parsed.key.upper() ! SELECT: return False # 禁止访问系统表 for table in parsed.find_all(sqlglot.exp.Table): table_name table.name.lower() if table_name in (pg_catalog, information_schema): return False return True执行层我会做两件事第一是设置查询超时我给的是30秒超过就主动断开防止生成笛卡尔积类的大查询拖垮数据库第二是限制返回行数在SQL后追加LIMIT 2000不同数据库语法略有差异保证前端渲染性能和响应速度。这里我要强调一个细节超时和行数限制不能只靠代码加你在提示词里就要要求模型“默认给出LIMIT”。因为有些复杂查询如果嵌套了子查询外层追加LIMIT不一定能限制内层数据扫描量。从源头控制是最保险的。3.4 图表类型推荐与数据格式化图表推荐我前面说了用规则引擎这里给出实际代码# chart_recommender.py def recommend_chart(columns: list, rows: list) - str: col_names [c[0] for c in columns] if len(rows) 1: return number if 日期 in col_names or date in col_names or time in col_names: return line if len(rows) 8 and any(占比 in c or rate in c for c in col_names): return pie return bar返回数据到前端之前我会把SQL查询结果统一格式化为固定的JSON结构前端ECharts只需要按照这个结构渲染即可。格式化这一步能屏蔽不同数据库返回类型的差异比如SQLite返回的日期是字符串MySQL返回的是datetime对象统一处理后前端代码可以写得很干净。3.5 完整链路联调从提问到图表我用一个真实场景把整条链路串起来演示一遍方便理解。用户在界面输入“帮我统计一下这个月每天的订单量和销售额按日期排序。”后端执行流程组装提示词注入Schema描述和示例。调用大模型模型返回JSON{ query: SELECT order_date, COUNT(*) AS order_cnt, SUM(order_amount) AS total_amount FROM orders WHERE order_date DATE_FORMAT(CURRENT_DATE(), %Y-%m-01) GROUP BY order_date ORDER BY order_date;, type: line, summary: 本月每日订单量与销售额整体呈上升趋势 }校验SQL无误后在执行层追加LIMIT 2000并发起查询。查询结果格式化后图表推荐函数根据列名包含“order_date”推荐折线图。前端收到数据后渲染出双Y轴折线图左侧是销售额右侧是订单量同时显示summary文本。整体响应时间大约在5-8秒其中大模型调用占3-5秒SQL执行不到1秒。这样的体验已经足够业务同事用了。4. 常见问题与排查技巧实录4.1 模型生成的SQL表名或字段名不存在这是最高频的问题。大模型在生成SQL时偶尔会“编造”字段尤其当Schema描述里包含别名、业务叫法时更容易出现。排查方法是在SQL执行前做一次字段存在性检查用正则或SQL解析对比表结构元数据发现不存在的字段时返回提示并要求模型重新生成。根治方案是Schema的最小化。我前面已经说过只注入核心表和常用字段字段越多幻觉概率越大。我实际测试过Schema从30个字段缩减到10个核心字段后字段幻觉率下降非常明显。4.2 模型生成的SQL逻辑“对”但“不对”举一个实际案例。用户问“哪个品类的退货率最高”模型生成了SELECT category, SUM(return_amount) / SUM(order_amount) AS return_rate FROM orders GROUP BY category ORDER BY return_rate DESC。单看SQL似乎没问题但业务上的“退货率”在数据库里并不是这样计算的退货金额在一个单独的表里需要JOIN退款表。这就是“逻辑对但业务不对”的经典问题。解决思路是增强Schema的业务语义描述同时增加Few-shot示例。我在提示词里同时提供了两类示例一类是“基础查询逻辑”一类是“容易出错的业务口径”比如“退货率必须关联退款表并按订单时间聚合”这样的约束写进去错误率会大幅下降。4.3 执行超时和大查询拖垮数据库生成的SQL带大范围的笛卡尔积JOIN或者没过滤条件直接全表扫描时查询会变得很慢。我已经设置了30秒超时但更关键的是在提示词里约束“查询必须带WHERE条件”特别是统计报表类查询必须限制时间范围。系统层面可以再加一层慢查询日志监控在数据库侧识别到超过阈值的长查询时自动kill。代码层面执行SQL前先通过解析器判断是否存在未带过滤条件的JOIN如果发现多个大表JOIN又没有WHERE条件直接拦截并提醒用户补充筛选范围。4.4 前端图表渲染异常或数据格式不匹配这个问题的根源在我前面提过的“数据格式化”环节。比如SQL查询返回的日期字段是字符串但前端ECharts需要时间戳才能正确排序或者数值字段返回的是Decimal类型json序列化时抛异常。我最后的方案是自定义一个JSON序列化器统一处理Decimal和datetime类型同时在SQL执行结果返回前端之前把字段类型信息一并传给前端。前端根据字段类型决定是否做时间格式化这样就彻底解决了此类问题。4.5 提示词“注入”与恶意查询构造既然系统能把自然语言转成SQL那理论上用户就能构造恶意输入尝试让模型生成非SELECT语句或访问敏感表。虽然我们用只读账号和SQL校验做了防线但仍不能掉以轻心。我在服务层增加了一个输入过滤对用户输入做一些基本检查比如包含明显的“忽略之前指令”这类提示词注入语句时直接拒绝处理。这不是完美的方案但能挡住大多数脚本小子的尝试。还有一个点是完整记录用户提问和最终执行的SQL方便出问题时审计追查这个习惯强烈建议保留。5. 数据缓存与性能优化实录5.1 缓存策略命中一次节省一次API调用调用大模型API是有成本和时延的。如果两个用户连续问同一个问题每次都要重新生成SQL显然是浪费。我的方案是引入两级缓存。第一级是“问题 → SQL”的缓存。把用户提问的规范化文本作为key缓存的value是模型生成的SQL。这样相同的问题直接走缓存不再调用模型。第二级是“SQL → 查询结果”的缓存。由于数据库数据会变化这一级缓存需要设置较短的过期时间。我设置的是5分钟对于大部分BI场景来说5分钟内的数据延迟业务完全可以接受。这里有个细节缓存key不能直接用原始文本做哈希因为用户可能只是换了个标点或改了词序。我在代码里对问题文本做了简单的归一化处理统一大小写、去除多余空格、替换全角标点为半角。这样能显著提高缓存命中率。5.2 并发请求与数据库连接池管理FastAPI本身支持异步但数据库驱动和SQL执行部分是同步的直接在线程池里跑同步代码即可问题不大。比较值得关注的是数据库连接池的大小。刚开始我的代码每次查询都新建一个连接压测稍微上来一点数据库连接数就满了报错一大片。后来改成了用连接池统一管理设置最大连接数为20空闲超时60秒问题就解决了。5.3 大数据量查询的内存保护内存溢出是个容易忽略的问题。有一回业务同事查了一整年的明细数据几十万行一次性返回直接把服务内存打爆了。从那以后我加了双重限制SQL层面强制LIMIT 5000应用层面在读取查询结果时采用流式方式分批处理而不是一次性把所有结果加载进内存。如果业务确实需要大数据量的统计分析正确的做法是在SQL里用聚合而不是把明细捞回应用层计算。这点我在提示词里也加了约束如果用户想要“明细”默认只返回前N行。6. 可扩展方向这个项目做完后我一直在琢磨怎么把它做得更贴近实际业务。有几个方向我认为非常值得尝试。第一个是自动生成周报/月报摘要。现在系统能回答单个问题如果再接入大模型的生成能力把多个查询结果整合成一段结构化的文字描述就能直接输出一份带数据图表的周报这个是业务侧接受度很高的功能。第二个是数据异常自动预警。系统每天定时执行一组关键指标查询如果结果偏离历史均值超出门限就自动推送告警并解释可能的原因。这不改变现有链路只是在SQL执行后多加一层对比逻辑。第三个是SQL注释的自动生成。既然模型生成了SQL可以让它在SQL里自动写好每段查询的注释这样业务同事拿到SQL文件也能理解逻辑减少沟通成本。第四个是接入更多数据源。目前只支持了关系型数据库但很多公司还有日志库、数仓等数据源。如果能把数据源抽象成统一接口同一套NL2SQL逻辑就能横向扩展。有一点必须提醒做这些扩展之前先把基础链路的稳定性打磨好。我最大的体会是NL2SQL系统最怕的不是模型不够聪明而是链路里一个不起眼的环节出错导致整个体验崩掉。提示词、校验、格式化、缓存、超时控制每一环都要做扎实然后才能放心做加法。最后分享一个具体建议上线前准备一份常见问题测试集至少覆盖20个不同类型的业务提问每次修改提示词或调整代码后都跑一遍回归测试。我吃过一次亏改了一个字段描述结果五类SQL的生成逻辑全变了没有回归测试根本发现不了。这个测试集不需要很复杂纯文本加期望SQL就行但它能让你在迭代时心里有底。