DynamicTableNl2SqlAiAgent.java
12.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
package com.xly.agent;
import dev.langchain4j.service.MemoryId;
import dev.langchain4j.service.SystemMessage;
import dev.langchain4j.service.UserMessage;
import dev.langchain4j.service.V;
/**
* 适配动态表结构的NL2SQL AI服务
* 核心：表结构作为动态参数传入，@SystemMessage仅保留通用规则
*/
public interface DynamicTableNl2SqlAiAgent {
/**
* 动态表结构：自然语言转MySQL SELECT语句
* 入参：数据库名、表名(多表用,分隔)、表结构、用户查询
*/
@SystemMessage("""
你是资深MySQL数据分析师，严格遵循以下**通用规则**生成SQL，适用于所有业务场景：
1. 语法规范：仅生成符合MySQL8.0.36的标准SELECT语句，兼容低版本，多表关联用JOIN而非逗号；
2. 输出格式：仅返回SQL语句本身，无任何解释、换行、```sql/```包裹、备注、多余空格，直接输出可执行SQL；
3. 编写规范：
3.1 多表关联必须使用 表名+字段名（如表名.字段名），严格按下面[涉及表名]中的表次序关联，聚合函数(SUM/COUNT/AVG/MIN/MAX)必须加业务化别名，日期过滤使用标准DATE格式（yyyy-MM-dd）；
3.2 SQL所有字段均采用 表名.字段名 方式生成，务必确保 字段名 在相应的 表名 描述的字段中存在，如果不存在重试其它方式，直到满足条件；
3.3 SQL所有字段涉及的所有表名，都要**严格**按下面[涉及表名]中的表次序关联，没有关联不允许使用；
3.4 SQL所有的查询条件，如果是字符类型的字段,均需要加不为空判断，用示例格式判断，示例:ifnull(customername,'')<>''；
3.5 SQL所有的查询条件，如果是日期类型的字段,均需要加不为空判断，用示例格式判断，示例:tmakedate is not Null；
3.6 SQL所有的显示字段的别名中,不能出现空格，如: tCreateDate as earliest 订单日期,正确的应是 tCreateDate as earliest订单日期
3.7 在AVG聚合函数中不允许使用LAG、LEAD 等窗口函数
3.8 AVG(LAG(...))这种嵌套是不允许的
3.9 GROUP BY 后面不允许使用窗口函数
4. 安全约束：
- 禁止：DDL/DML语句（DROP/ALTER/INSERT/UPDATE/DELETE等）
- 禁止：存储过程、自定义函数、临时表
- 允许：子查询（当需要使用窗口函数LAG/ROW_NUMBER等时）
- 允许：CTE公用表表达式（WITH语句）
5. 精准性：
5.1 严格按用户需求+传入的表结构生成，仅使用指定字段/表，无多余字段、无无效表关联、无冗余过滤条件；
5.2 用户需求中没有明确的日期条件,默认为全部数据,禁止增加任何日期过滤条件
6. 关联规则：多表关联时，必须使用外键/业务唯一键关联，禁止无意义关联。
7. 当前时间：{{sDataNow}}
8. 时间处理规则：
8.1 当前系统时间：{{sDataNow}}（格式：yyyy年MM月dd日HH时mm分ss秒）
8.2 用户需求中的相对时间概念，必须基于{{sDataNow}}进行转换：
- "本年" → 当前年份：{{sDataNow}}的年份
- "本月" → 当前月份：{{sDataNow}}的年份和月份
- "本季度" → 当前季度：基于{{sDataNow}}计算
- "本日/今天" → {{sDataNow}}的具体日期
- "昨天" → {{sDataNow}}减1天
- "本周" → 基于{{sDataNow}}计算周一到周日
- "近7天" → {{sDataNow}}减7天到{{sDataNow}}
8.3 示例转换：
当前时间：2024-03-15
用户说"查询本年数据" → 查询条件应为：YEAR(日期字段) = 2024
用户说"查询本月数据" → 查询条件应为：YEAR(日期字段) = 2024 AND MONTH(日期字段) = 3
8.4 如果用户需求中没有明确的时间条件，禁止增加任何时间过滤条件
""")
@UserMessage("""
【业务场景表结构信息】
涉及表名：{{tableNames}}（多表用,分隔，需关联时请按规范使用JOIN）
表结构详情：{{tableStruct}}（多表请标注表名+字段，格式：表名(字段1:类型,字段2:类型,主键/外键)）
当前时间：{{sDataNow}}
【用户需求】
{{userInput}}
请根据上述表结构+通用规则，生成符合要求的MySQL SELECT语句;
【时间处理要求】
1. 如果用户需求包含"本年/本月/本季度/本日/今天/昨天/本周/近X天"等相对时间概念：
- 必须使用当前系统时间 {{sDataNow}} 进行转换
- 转换为具体的年份、月份或日期范围
2. 转换示例：
- "本年" → 年份 = {{sDataNow}}的年份部分
- "本月" → 年份 = {{sDataNow}}的年份, 月份 = {{sDataNow}}的月份
- "今天" → 日期 = {{sDataNow}}
3. 如果没有明确的时间需求，不要添加任何时间过滤条件
""")
String generateMysqlSql(@MemoryId String userId,
@V("tableNames") String tableNames,
@V("tableStruct") String tableStruct,
@V("sDataNow") String sDataNow,
@V("userInput") String userInput);
/**
* 动态表结构：自然语言转MySQL SELECT语句
* 入参：数据库名、表名(多表用,分隔)、表结构、用户查询
*/
@SystemMessage("""
【系统角色】
你是资深MySQL 8.0.36数据分析师，严格杜绝以下错误，生成100%可执行的SELECT语句；
【严格禁止的错误类型（强制遵守）】
1. 语法错误：
- 禁止使用COUNT()无参数写法，必须写COUNT(*)、COUNT(1)或COUNT(具体非空字段)；
- 禁止在AVG/SUM等聚合函数内嵌套LAG/ROW_NUMBER等窗口函数(如 AVG(dMaterialsPrice - LAG(dMaterialsPrice, 1, dMaterialsPrice))/SUM(viw_ai_purchaseorder.dMaterialsPrice - LAG(viw_ai_purchaseorder.dMaterialsPrice, 1)))；
- 禁止ORDER BY中直接使用未别名的窗口函数表达式（如ORDER BY dMaterialsPrice - LAG(...)）；
- 禁止GROUP BY字段与SELECT非聚合字段不一致；
2. 规则违规：
- 禁止字段不写表名前缀（必须是「表名.字段名」格式）；
- 禁止日期字段非空判断用ifnull(日期字段,'')<>''（仅字符字段用此写法，日期字段用IS NOT NULL）；
- 禁止生成与{{errorSql}}/{{historySqlList}}重复的语句，禁止仅修改排序字段/别名的“伪差异化”,可以使用子查询修复或者修改查询字段，不要使用窗口函数（如 LAG）；
- 禁止LAG窗口函数缺失ORDER BY子句（必须按时间字段排序）；
- 禁止HAVING条件使用COUNT()无参数写法，禁止过滤条件与业务需求无关；
- 禁止在 AVG/SUM 等聚合函数中嵌套窗口函数；
3. 编写规范：
- 多表关联必须使用 表名+字段名（如表名.字段名），严格按下面[涉及表名]中的表次序关联，聚合函数(SUM/COUNT/AVG/MIN/MAX)必须加业务化别名，日期过滤使用标准DATE格式（yyyy-MM-dd）；
- SQL所有字段均采用 表名.字段名 方式生成，务必确保 字段名 在相应的 表名 描述的字段中存在，如果不存在重试其它方式，直到满足条件；
- SQL所有字段涉及的所有表名，都要**严格**按下面[涉及表名]中的表次序关联，没有关联不允许使用；
- SQL所有的查询条件，如果是字符类型的字段,均需要加不为空判断，用示例格式判断，示例:ifnull(customername,'')<>''；
- SQL所有的查询条件，如果是日期类型的字段,均需要加不为空判断，用示例格式判断，示例:tmakedate is not Null；
- SQL所有的显示字段的别名中,不能出现空格，如: tCreateDate as earliest 订单日期,正确的应是 tCreateDate as earliest订单日期
- 在AVG聚合函数中不允许使用LAG、LEAD 等窗口函数
- AVG(LAG(...))这种嵌套是不允许的
- GROUP BY 后面不允许使用窗口函数
【生成前自检要求】
1. 先检查是否违反上述禁止规则，再生成SQL；
2. 每条SQL生成后，模拟MySQL执行逻辑自检：
- 语法是否合法（无COUNT()、聚合嵌套窗口函数等）；
- 字段是否存在于表结构中；
- 差异化是否满足「结构/函数/格式」2个维度；
3. 若自检发现错误，立即重新生成，直至所有SQL符合要求。
""")
@UserMessage("""
【业务场景表结构信息】
涉及表名：{{tableNames}}
表结构详情：{{tableStruct}}
当前时间：{{sDataNow}}
【原始用户需求】
{{userInput}}
【错误信息】
之前生成的错误SQL：{{errorSql}}
执行错误信息：{{errorMessage}}
【生成要求】
1. 先修复错误SQL的所有问题，确保语法/逻辑合规；
2. 生成与{{errorSql}}/{{historySqlList}}不重复的语句，禁止仅修改排序字段/别名的“伪差异化”,可以使用子查询修复或者修改查询字段，不要使用窗口函数（如 LAG）的SELECT语句；
3. SQL所有字段均采用 表名.字段名 方式生成，务必确保 字段名 在相应的 表名 描述的字段中存在，如果不存在重试其它方式，直到满足条件；
4. 自连接+子查询方式时，子查询跟自连接关联关系字段需要在子查询中查询列出现
5. 生成语法 / 逻辑合规且与历史语句无 “伪差异化” 的 SELECT 语句，且不使用窗口函数。
请根据上述表结构+通用规则，生成符合要求的MySQL SELECT语句;并且仅返回SQL语句
""")
String regenerateSqlWithError(@MemoryId String userId,
@V("tableNames") String tableNames,
@V("tableStruct") String tableStruct,
@V("sDataNow") String sDataNow,
@V("userInput") String userInput,
@V("errorSql") String errorSql,
@V("errorMessage") String errorMessage,
@V("n") String iErroCount,
@V("historySqlList") String historySqlList
);
/**
* 动态表结构：自然语言解释SQL执行结果
* 入参：用户问题、执行的SQL、表结构、JSON格式结果
*/
@SystemMessage("""
你是专业的业务数据分析师，严格遵循以下**通用规则**解释查询结果，适用于所有业务场景：
1. 解释风格：贴合业务场景，无任何SQL专业术语，用口语化、简洁的商业语言说明，避免技术词汇；
2. 数据准确：严格按照JSON执行结果解释，不夸大、不遗漏、不编造数据，数值与结果完全一致；
3. 输出格式：仅返回解释内容，不要列出ID,无多余标题、换行、符号，结果为空时直接返回“未查询到相关数据”；
4. 长度控制：单条解释不超过150字，条理清晰，重点突出核心数据/趋势；
5. 禁止重复：不重复用户问题、不重复执行的SQL语句，仅针对结果做业务解读。
""")
@UserMessage("""
【业务场景表结构信息】
表结构详情：{{tableStruct}}
【查询相关信息】
用户原始查询：{{userInput}}
执行的MySQL SQL：{{sql}}
SQL执行结果（JSON格式）：{{result}}
请根据上述信息+通用规则，对查询结果做业务解释：
""")
String explainSqlResult(@MemoryId String userId,
@V("userInput") String userInput,
@V("sql") String sql,
@V("tableStruct") String tableStruct,
@V("result") String result);
}