LLM大语言模型不适合统计算数,可以让大模型根据数据自己建表、插入数据、编写查询sql统计
例如有如下数据:
黄小军 语文 100
张飞 语文 34
曹操 语文 56
董卓 语文 78
蒋介石 语文 111
邵逸夫 语文 123
董存瑞 语文 456
黄小军 数学 1000
张飞 数学 245
曹操 数学 385
董卓 数学 234
蒋介石 数学 11
邵逸夫 数学 0
董存瑞 数学 89
表示的是某班级学生各科目的分数,假如我们想让大模型统计该班各科目成绩的总分,最能想到的方法就是编写如下的提示词扔给大模型:
<<<
黄小军 语文 100
张飞 语文 34
曹操 语文 56
董卓 语文 78
蒋介石 语文 111
邵逸夫 语文 123
董存瑞 语文 456
黄小军 数学 1000
张飞 数学 245
曹操 数学 385
董卓 数学 234
蒋介石 数学 11
邵逸夫 数学 0
董存瑞 数学 89
>>>上述被 "<<<" 和 ">>>" 包裹的内容是某班级学生各科目的分数,请统计该班各科目成绩的总分
但是大模型统计的不准,我的解决思路是不让大模型直接算( 相信我们人也不会直接心算或者用笔算或者用计算器算,使用 java 熟练的会编写 java 程序算,使用 Js 熟练的会编写 Js 代码算,使用 mysql 熟练的就是建一张 Mysql 表,然后写程序将数据导入到表中再编写查询 sql 算 ),可以让大模型生成可以统计这个需求的 js、java 等代码,本文章是借助 Mysql 数据库完成,因为 Mysql 天生就是干统计的,要让大模型用 mysql 完成该任务,具体步骤如下:
1. 让大模型建表:
提示词:
<<<
黄小军 语文 100
张飞 语文 34
曹操 语文 56
董卓 语文 78
蒋介石 语文 111
邵逸夫 语文 123
董存瑞 语文 456
黄小军 数学 1000
张飞 数学 245
曹操 数学 385
董卓 数学 234
蒋介石 数学 11
邵逸夫 数学 0
董存瑞 数学 89
>>>上述被 "<<<" 和 ">>>" 包裹的内容是某班级学生各科目的分数,现在需要新建一个 mysql 表来存储上述数据,请给出该表的完整create语句,要包含每个字段的注释和表名的注释
大模型生成的 sql 建表语句如下所示:
CREATE TABLE `student_scores` (`id` int NOT NULL AUTO_INCREMENT COMMENT '成绩记录唯一标识',`student_name` varchar(20) NOT NULL COMMENT '学生姓名',`subject` varchar(10) NOT NULL COMMENT '科目名称',`score` decimal(10,2) NOT NULL COMMENT '考试成绩',`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='存储学生各科目考试成绩的表'
然后提供一个建表的接口让大模型调用或者体现在dify上就是后面加一个 http 请求节点调用下该接口。
2. 让大模型插入数据:
<<<
黄小军 语文 100
张飞 语文 34
曹操 语文 56
董卓 语文 78
蒋介石 语文 111
邵逸夫 语文 123
董存瑞 语文 456
黄小军 数学 1000
张飞 数学 245
曹操 数学 385
董卓 数学 234
蒋介石 数学 11
邵逸夫 数学 0
董存瑞 数学 89
>>>{{{
CREATE TABLE student_scores (id INT AUTO_INCREMENT PRIMARY KEY COMMENT '成绩记录唯一标识',student_name VARCHAR(20) NOT NULL COMMENT '学生姓名',subject VARCHAR(10) NOT NULL COMMENT '科目名称',score DECIMAL(10,2) NOT NULL COMMENT '考试成绩',create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='存储学生各科目考试成绩的表';
}}}
上述被 "<<<" 和 ">>>" 包裹的内容是某班级学生各科目的分数,被 "{{{" 和 "}}}" 包裹的部分是 mysql 数据库中 学生分数表 student_scores 的完整 create 语句,请生成一个将
该班级学生各科目的分数保存到表 student_scores 中的完整 insert 语句
大模型生成的 insert 语句如下:
INSERT INTO student_scores (student_name, subject, score) VALUES
('黄小军', '语文', 100),
('张飞', '语文', 34),
('曹操', '语文', 56),
('董卓', '语文', 78),
('蒋介石', '语文', 111),
('邵逸夫', '语文', 123),
('董存瑞', '语文', 456),
('黄小军', '数学', 1000),
('张飞', '数学', 245),
('曹操', '数学', 385),
('董卓', '数学', 234),
('蒋介石', '数学', 11),
('邵逸夫', '数学', 0),
('董存瑞', '数学', 89);
然后提供一个执行 Insert 语句的接口让大模型调用或者体现在dify上就是后面加一个 http 请求节点调用下该接口。
3. 让大模型查询数据:
提示词:
{{{
CREATE TABLE student_scores (id INT AUTO_INCREMENT PRIMARY KEY COMMENT '成绩记录唯一标识',student_name VARCHAR(20) NOT NULL COMMENT '学生姓名',subject VARCHAR(10) NOT NULL COMMENT '科目名称',score DECIMAL(10,2) NOT NULL COMMENT '考试成绩',create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='存储学生各科目考试成绩的表';
}}}
上述被 "{{{" 和 "}}}" 包裹的部分是 mysql 数据库中 学生分数表 student_scores 的完整 create 语句,
编写用于统计各科目成绩的总分的 mysql select 语句
大模型生成的查询语句如下所示:
SELECT SUBJECT AS '科目名称',SUM(score) AS '总分'
FROM student_scores
GROUP BY SUBJECT
ORDER BY SUM(score) DESC;
然后提供一个执行 select 语句的接口让大模型调用或者体现在dify上就是后面加一个 http 请求节点调用下该接口。
执行结果如下所示:
科目名称 总分
数学 1964.00
语文 958.00
然后再交给大模型进行总结( ps:总结和汇总不是一个概念,例如张三数学、英语、语文分别考了100分、0分、89分,总结就是 "张三偏科严重,虽然美国封锁我国但是学好英语对于 “窃取” 美国技术实现中华民族的伟大复兴还是有帮助的",汇总就是“张三平均分63分,最高分100分,最低分0分,总分189分” )。
模拟 dify 工作流的节点如下所示:
1. 编写 create 语句节点(llm节点)
提示词:
<<<
黄小军 语文 100
张飞 语文 34
...
董存瑞 数学 89
>>>
上述被 "<<<" 和 ">>>" 包裹的内容是某班级学生各科目的分数,现在需要新建一个 mysql 表来存储上述数据,请给出该表的完整create语句,要包含每个字段的注释和表名的注释
2. 执行 create 语句节点(http请求节点)
3. 编写 insert 语句节点:
提示词:
<<<
黄小军 语文 100
张飞 语文 34
...
董存瑞 数学 89
>>>
{{{
CREATE TABLE student_scores (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '成绩记录唯一标识',
student_name VARCHAR(20) NOT NULL COMMENT '学生姓名',
subject VARCHAR(10) NOT NULL COMMENT '科目名称',
score DECIMAL(10,2) NOT NULL COMMENT '考试成绩',
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='存储学生各科目考试成绩的表';
}}}
上述被 "<<<" 和 ">>>" 包裹的内容是某班级学生各科目的分数,被 "{{{" 和 "}}}" 包裹的部分是 mysql 数据库中 学生分数表 student_scores 的完整 create 语句,
请生成一个将该班级学生各科目的分数保存到表 student_scores 中的完整 insert 语句
4. 执行 insert 语句节点(http请求节点)
5. 编写 select 语句节点:
提示词:
{{{
CREATE TABLE student_scores (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '成绩记录唯一标识',
student_name VARCHAR(20) NOT NULL COMMENT '学生姓名',
subject VARCHAR(10) NOT NULL COMMENT '科目名称',
score DECIMAL(10,2) NOT NULL COMMENT '考试成绩',
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='存储学生各科目考试成绩的表';
}}}
上述被 "{{{" 和 "}}}" 包裹的部分是 mysql 数据库中 学生分数表 student_scores 的完整 create 语句,编写用于统计各科目成绩的总分的 mysql select 语句
6. 执行 select 语句节点(http请求节点)
dify 工作流概览:
dify 工作流 DSL:
app:description: ''icon: "\U0001F916"icon_background: '#FFEAD5'mode: workflowname: text2sql
kind: app
version: 0.1.0
workflow:environment_variables: []features:file_upload:image:enabled: falsenumber_limits: 3transfer_methods:- local_file- remote_urlopening_statement: ''retriever_resource:enabled: truesensitive_word_avoidance:enabled: falsespeech_to_text:enabled: falsesuggested_questions: []suggested_questions_after_answer:enabled: falsetext_to_speech:enabled: falselanguage: ''voice: ''graph:edges:- data:isInIteration: falsesourceType: starttargetType: llmid: 1752652121934-source-1752652288134-targetsource: '1752652121934'sourceHandle: sourcetarget: '1752652288134'targetHandle: targettype: customzIndex: 0- data:isInIteration: falsesourceType: llmtargetType: codeid: 1752652288134-source-1752652694991-targetsource: '1752652288134'sourceHandle: sourcetarget: '1752652694991'targetHandle: targettype: customzIndex: 0- data:isInIteration: falsesourceType: llmtargetType: codeid: 1752652825248-source-1752653065502-targetsource: '1752652825248'sourceHandle: sourcetarget: '1752653065502'targetHandle: targettype: customzIndex: 0- data:isInIteration: falsesourceType: codetargetType: http-requestid: 1752652694991-source-1752653155791-targetsource: '1752652694991'sourceHandle: sourcetarget: '1752653155791'targetHandle: targettype: customzIndex: 0- data:isInIteration: falsesourceType: http-requesttargetType: llmid: 1752653155791-source-1752652825248-targetsource: '1752653155791'sourceHandle: sourcetarget: '1752652825248'targetHandle: targettype: customzIndex: 0- data:isInIteration: falsesourceType: codetargetType: http-requestid: 1752653065502-source-1752653242968-targetsource: '1752653065502'sourceHandle: sourcetarget: '1752653242968'targetHandle: targettype: customzIndex: 0- data:isInIteration: falsesourceType: http-requesttargetType: llmid: 1752653242968-source-1752653314535-targetsource: '1752653242968'sourceHandle: sourcetarget: '1752653314535'targetHandle: targettype: customzIndex: 0- data:isInIteration: falsesourceType: llmtargetType: codeid: 1752653314535-source-1752653455711-targetsource: '1752653314535'sourceHandle: sourcetarget: '1752653455711'targetHandle: targettype: customzIndex: 0- data:isInIteration: falsesourceType: codetargetType: http-requestid: 1752653455711-source-1752653527072-targetsource: '1752653455711'sourceHandle: sourcetarget: '1752653527072'targetHandle: targettype: customzIndex: 0- data:isInIteration: falsesourceType: http-requesttargetType: codeid: 1752653527072-source-1752653591934-targetsource: '1752653527072'sourceHandle: sourcetarget: '1752653591934'targetHandle: targettype: customzIndex: 0- data:isInIteration: falsesourceType: codetargetType: llmid: 1752653591934-source-1752653669711-targetsource: '1752653591934'sourceHandle: sourcetarget: '1752653669711'targetHandle: targettype: customzIndex: 0nodes:- data:desc: ''selected: falsetitle: "\u5F00\u59CB"type: startvariables:- label: "\u6570\u636E"max_length: 2048options: []required: truetype: paragraphvariable: data- label: "\u9700\u6C42"max_length: 256options: []required: truetype: text-inputvariable: demand- label: "\u6570\u636E\u63CF\u8FF0"max_length: 256options: []required: truetype: text-inputvariable: dataDescheight: 142id: '1752652121934'position:x: 86y: 73positionAbsolute:x: 86y: 73selected: falsesourcePosition: righttargetPosition: lefttype: customwidth: 244- data:context:enabled: falsevariable_selector: []desc: "\u7F16\u5199create\u8BED\u53E5"model:completion_params:temperature: 0.7mode: chatname: chatglm_turboprovider: zhipuaiprompt_template:- id: 273d2dc1-7f55-4644-8d2a-e88302a96d03role: systemtext: "<<<\n{{#1752652121934.data#}}\n>>>\n\u4E0A\u8FF0\u88AB \"<<<\" \u548C\\ \">>>\" \u5305\u88F9\u7684\u5185\u5BB9\u662F {{#1752652121934.dataDesc#}}\\ \u6570\u636E\uFF0C\u73B0\u5728\u9700\u8981\u65B0\u5EFA\u4E00\u4E2A mysql\\ \u8868\u6765\u5B58\u50A8\u4E0A\u8FF0 {{#1752652121934.dataDesc#}} \u6570\\u636E\uFF0C\u8BF7\u7ED9\u51FA\u8BE5\u8868\u7684\u5B8C\u6574create\u8BED\\u53E5\uFF0C\u8981\u5305\u542B\u6BCF\u4E2A\u5B57\u6BB5\u7684\u6CE8\u91CA\\u548C\u8868\u540D\u7684\u6CE8\u91CA,\u8F93\u51FA\u5982\u4E0B\u6240\u793A\\u7684json\u7ED3\u6784\u4FE1\u606F\uFF1A\n{ \"createSql\":\"\u5EFA\u8868\\u8BED\u53E5\" }"selected: falsetitle: "\u7F16\u5199create\u8BED\u53E5"type: llmvariables: []vision:enabled: falseheight: 126id: '1752652288134'position:x: 454.9999999999999y: 73positionAbsolute:x: 454.9999999999999y: 73selected: falsesourcePosition: righttargetPosition: lefttype: customwidth: 244- data:code: "\nfunction main({text}) {\n return {\n createSql: text.getString(\"\createSql\")\n }\n}\n"code_language: javascriptdesc: "\u63D0\u53D6 create sql"outputs:createSql:children: nulltype: stringselected: falsetitle: "\u63D0\u53D6 create sql"type: codevariables:- value_selector:- '1752652288134'- textvariable: arg1height: 82id: '1752652694991'position:x: 788.9999999999999y: 73positionAbsolute:x: 788.9999999999999y: 73selected: falsesourcePosition: righttargetPosition: lefttype: customwidth: 244- data:context:enabled: falsevariable_selector: []desc: ''model:completion_params:temperature: 0.7mode: chatname: chatglm_turboprovider: zhipuaiprompt_template:- id: 32f98cca-190d-4043-991c-4ffb8a6e69bfrole: systemtext: "<<<\n{{#1752652121934.data#}}\n>>>\n{{{\n{{#1752652694991.createSql#}}\n\}}}\n\u4E0A\u8FF0\u88AB \"<<<\" \u548C \">>>\" \u5305\u88F9\u7684\u5185\\u5BB9\u662F {{#1752652121934.dataDesc#}} \u6570\u636E\uFF0C\u88AB \"\{{{\" \u548C \"}}}\" \u5305\u88F9\u7684\u90E8\u5206\u662F xxx \u6570\u636E\\u5BF9\u5E94\u7684 mysql \u8868\u7684\u5B8C\u6574 create \u8BED\u53E5\uFF0C\\u8BF7\u751F\u6210\u4E00\u4E2A\u5C06\u4E0A\u8FF0 {{#1752652121934.dataDesc#}}\\ \u6570\u636E\u4FDD\u5B58\u5230\u8BE5\u8868\u7684\u5B8C\u6574 insert\\ \u8BED\u53E5\uFF0C\u8F93\u51FA\u5982\u4E0B\u6240\u793A\u7684json\u683C\\u5F0F\u4FE1\u606F\uFF1A\n{ \"insertSql\":\"mysql\u63D2\u5165\u8BED\u53E5\\" }\n"selected: falsetitle: "\u7F16\u5199 insert \u8BED\u53E5"type: llmvariables: []vision:enabled: falseheight: 98id: '1752652825248'position:x: 73.00000000000011y: 282.00000000000006positionAbsolute:x: 73.00000000000011y: 282.00000000000006selected: falsesourcePosition: righttargetPosition: lefttype: customwidth: 244- data:code: "\nfunction main({text}) {\n return {\n insertSql: text.getString(\"\insertSql\")\n }\n}\n"code_language: javascriptdesc: "\u63D0\u53D6 insert sql"outputs:insertSql:children: nulltype: stringselected: falsetitle: "\u63D0\u53D6 insert sql"type: codevariables:- value_selector:- '1752652825248'- textvariable: textheight: 82id: '1752653065502'position:x: 392.0000000000001y: 275.00000000000006positionAbsolute:x: 392.0000000000001y: 275.00000000000006selected: falsesourcePosition: righttargetPosition: lefttype: customwidth: 244- data:authorization:config: nulltype: no-authbody:data: ''type: nonedesc: ''headers: ''method: getparams: ''selected: falsetimeout:max_connect_timeout: 0max_read_timeout: 0max_write_timeout: 0title: "\u6267\u884C create sql"type: http-requesturl: http://localhost:8080/executeSql?sql={{#1752652694991.createSql#}}variables: []height: 125id: '1752653155791'position:x: 1089y: 73positionAbsolute:x: 1089y: 73selected: falsesourcePosition: righttargetPosition: lefttype: customwidth: 244- data:authorization:config: nulltype: no-authbody:data: ''type: nonedesc: ''headers: ''method: getparams: ''selected: falsetimeout:max_connect_timeout: 0max_read_timeout: 0max_write_timeout: 0title: "\u6267\u884C insert sql"type: http-requesturl: http://localhost:8080/executeSql?sql={{#1752653065502.insertSql#}}}variables: []height: 125id: '1752653242968'position:x: 700y: 260.00000000000006positionAbsolute:x: 700y: 260.00000000000006selected: falsesourcePosition: righttargetPosition: lefttype: customwidth: 244- data:context:enabled: falsevariable_selector: []desc: ''model:completion_params:temperature: 0.7mode: chatname: chatglm_turboprovider: zhipuaiprompt_template:- id: c843e821-5ebf-486a-89a8-5d582a9ece7erole: systemtext: "{{{\n{{#1752652694991.createSql#}}\n}}}\n\u4E0A\u8FF0\u88AB \"{{{\"\\ \u548C \"}}}\" \u5305\u88F9\u7684\u90E8\u5206\u662F\u5BF9\u5E94 {{#1752652121934.dataDesc#}}\\ \u6570\u636E\u7684\u5B8C\u6574 create \u8BED\u53E5\uFF0C\u8BF7\u7F16\\u5199\u7528\u4E8E\u7EDF\u8BA1\u5404\u79D1\u76EE\u6210\u7EE9\u7684\u603B\\u5206\u7684 mysql select \u8BED\u53E5\uFF0C\u8F93\u51FA\u5982\u4E0B\u6240\\u793A\u7684 json \u683C\u5F0F\u4FE1\u606F\uFF1A\n{ \"selectSql\":\"mysql\\ select \u67E5\u8BE2\u8BED\u53E5\" }"selected: falsetitle: "\u7F16\u5199 select sql"type: llmvariables: []vision:enabled: falseheight: 98id: '1752653314535'position:x: 1089y: 260.00000000000006positionAbsolute:x: 1089y: 260.00000000000006selected: falsesourcePosition: righttargetPosition: lefttype: customwidth: 244- data:code: "\nfunction main({text}) {\n return {\n selectSql: text.getString(\"\selectSql\")\n }\n}\n"code_language: javascriptdesc: ''outputs:selectSql:children: nulltype: stringselected: falsetitle: "\u63D0\u53D6 select sql"type: codevariables:- value_selector:- '1752653314535'- textvariable: textheight: 54id: '1752653455711'position:x: 73.00000000000011y: 458.4688345131865positionAbsolute:x: 73.00000000000011y: 458.4688345131865selected: falsesourcePosition: righttargetPosition: lefttype: customwidth: 244- data:authorization:config: nulltype: no-authbody:data: ''type: nonedesc: ''headers: ''method: getparams: ''selected: falsetimeout:max_connect_timeout: 0max_read_timeout: 0max_write_timeout: 0title: "\u6267\u884C select sql"type: http-requesturl: http://localhost:8080/executeSql?sql={{#1752653455711.selectSql#}}variables: []height: 125id: '1752653527072'position:x: 392.0000000000001y: 444.97869082303157positionAbsolute:x: 392.0000000000001y: 444.97869082303157selected: falsesourcePosition: righttargetPosition: lefttype: customwidth: 244- data:code: "\nfunction main({body}) {\n return {\n selectData: body.getJsonArray(\"\selectData\")\n }\n}\n"code_language: javascriptdesc: ''outputs:selectData:children: nulltype: stringselected: falsetitle: "\u63D0\u8D77 select \u67E5\u8BE2\u7ED3\u679C"type: codevariables:- value_selector:- '1752653527072'- bodyvariable: bodyheight: 54id: '1752653591934'position:x: 728.1353211957206y: 458.4688345131865positionAbsolute:x: 728.1353211957206y: 458.4688345131865selected: falsesourcePosition: righttargetPosition: lefttype: customwidth: 244- data:context:enabled: falsevariable_selector: []desc: ''model:completion_params:temperature: 0.7mode: chatname: chatglm_turboprovider: zhipuaiprompt_template:- id: 16fbd782-63ae-4cc2-918e-c9de46540677role: systemtext: "{{#1752653591934.selectData#}}}\n\u7528\u6237\u7684\u539F\u95EE\u9898\\u662F \"{{#1752652121934.demand#}}\"\uFF0C\u4EE5\u4E0A\u6570\u636E\u662F\\u4ECE\u6570\u636E\u5E93\u4E2D\u67E5\u8BE2\u51FA\u7684\u6EE1\u8DB3\u7528\\u6237\u7684\u539F\u95EE\u9898\u7684\u67E5\u8BE2\u7ED3\u679C\uFF0C\u8BF7\\u5E2E\u5FD9\u603B\u7ED3\u4E00\u4E0B\uFF0C\u4EE5\u53CB\u597D\u7684\u5F62\\u5F0F\u8F93\u51FA"selected: falsetitle: "select\u67E5\u8BE2\u7ED3\u679C\u7684\u603B\u7ED3"type: llmvariables: []vision:enabled: falseheight: 98id: '1752653669711'position:x: 1103.7268864810808y: 451.74693500691376positionAbsolute:x: 1103.7268864810808y: 451.74693500691376selected: truesourcePosition: righttargetPosition: lefttype: customwidth: 244viewport:x: -26.502832703039076y: 63.111134548403584zoom: 1.1486983549970358
ps:代码执行节点中的代码是伪代码