自然语言到SQL语言转义（基于大语言模型的NL2SQL）

使用说明

1. 创建问题模板表

   问题模板表的名称必须以polar4ai_nl2sql_pattern开头，且表结构必须包含下述建表语句中的五个列。

   DROP TABLE IF EXISTS `polar4ai_nl2sql_pattern`;
   CREATE TABLE `polar4ai_nl2sql_pattern` (
     `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
     `pattern_question` text COMMENT '模板问题',
     `pattern_description` text COMMENT '模板描述',
     `pattern_sql` text COMMENT '模板SQL',
     `pattern_params` text COMMENT '模板参数',
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

   问题模板列说明

   列名	说明
   模板问题	带参数的问题，作为输入提供给LLM-based NL2SQL模型。 在模板问题中，需要按照#{XXX}格式来写入参数。
   模板描述	对模板问题进行提炼，并将一些实体作为参数，例如日期、年份、机构等。这些实体通常对应于表中的特定列。 在模板描述中，需要按照【XXX】格式来写入参数，并且需要与模板问题中的参数顺序保持一致。
   模板SQL	与模板问题相对应的正确SQL语句。该SQL语句中需将模板问题中的参数作为变量进行处理。 说明 模板问题与模板SQL中的参数可不相同，但需具备关联关系。例如，参数应具有相同的前缀，并且参数值之间存在一一映射关系。以#{category}和#{categoryCode}为例，当category的参数值为普通商标、特殊商标和集体商标时，相应的categoryCode值分别为0、1和2。详细说明，请参见下述示例。
   模板参数	模板参数为一个JSON字符串，其结构由table_name、param_info和explanation三个参数组成，具体参数如下： table_name string：模板SQL中的表名。 param_info array：模板SQL中的参数说明。 param_name string：参数名称。 value array：参数的取值样例。 说明 如果参数对应的是有限的枚举值，则尽量在value中将所有的值列出来。 如果只是样例值，可以列出2～4个值。 如果存在相互对应的参数，则需要通过数组索引来映射。以#{category}和#{categoryCode}为例，category中的普通商标对应categoryCode中的1，特殊商标对应categoryCode中的2。详细说明，请参见下述示例。 explanation string：补充说明。通常是对生成SQL语句的一些要求。比如，输出哪些信息或者对字段的一些说明。 说明 当不需要填写模板参数时，您可以将模板参数值设置为以下任意一种形式： NULL 空字符串 空的list字符串[]

   示例

   模板问题	模板描述	模板SQL	模板参数
   查询课程名称为#{courseName}授课状态为#{status}的课程	【课程名称】【授课状态】的课程有哪些？	SELECT course_name, course_time, course_location FROM courses WHERE course_name=#{courseName} AND status=#{statusCode}	[{"table_name":"courses","param_info":[{"param_name":"#{courseName}","value":["数学","物理","化学","英语","历史","地理","生物","计算机科学","艺术","音乐","体育","编程","文学","心理学","哲学","经济学","社会学","物理学实验","化学实验","生物学实验"]},{"param_name": "#{status}", "value": ["未开课","授课中"]},{"param_name": "#{statusCode}","value": [0,1]}], "explanation": "输出课程名称course_name、课程时间course_time、课程地点course_location。注：status为常量映射类型。变量映射字段：statusCode。"}]
   查询年份为#{issueDate}年项目状态为#{projectStat}状态计划发布的国家标准有哪些？	【年份】【项目状态】计划发布的国家标准有哪些？	SELECT DISTINCT planNum, projectCnName, projectStat FROM sy_cd_me_buss_std_gjbzjh WHERE `planNum` IS NOT NULL AND `dataStatus` != 3 AND `isValid` = 1 AND projectStat=#{projectStat} AND DATE_FORMAT(`issueDate`, '%Y')=#{issueDate}	[{"table_name":"sy_cd_me_buss_std_gjbzjh","param_info":[{"param_name":"#{issueDate}","value":[2009,2010,2011,2012]},{"param_name":"#{projectStat}","value":["正在征求意见","已发布","正在审查"]}],"explanation":"输出标准名称projectCnName、计划号、项目状态"}]
   查询商标类型为#{category}国际分类为#{intCls}类型的商标有哪些？	【商标类型】【国际分类】的商标有哪些？	SELECT DISTINCT tmName, regNo, status FROM sy_cd_me_buss_ip_tdmk_new WHERE dataStatus!=3 AND isValid = 1 AND category=#{categoryCode} AND intCls=#{intClsCode}	[{"table_name":"sy_cd_me_buss_ip_tdmk_new","param_info":[{"param_name":"#{intCls}","value":["化学原料","颜料油漆","日化用品","燃料油脂","医药"]},{"param_name":"#{category}","value":["普通商标","特殊商标","集体商标"]},{"param_name":"#{intClsCode}","value":[1,2,3,4,5]},{"param_name":"#{categoryCode}","value":[0,1,2]}],"explanation":"输出商标名称tmName、regNo申请号/注册号、status商标状态。注：category为常量映射类型。变量映射字段：categoryCode。intCls为常量映射类型。变量映射字段：intClsCode。"}]

   以本文示例场景为例，创建上述表格中第一条模板问题。执行SQL如下：

   INSERT INTO `polar4ai_nl2sql_pattern` (`pattern_question`,`pattern_description`,`pattern_sql`,`pattern_params`) VALUES ('查询课程名称为#{courseName}授课状态为#{status}的课程','【课程名称】【授课状态】的课程有哪些？','SELECT course_name, course_time, course_location FROM courses WHERE course_name=#{courseName} AND status=#{statusCode}','[{"table_name":"courses","param_info":[{"param_name":"#{courseName}","value":["数学","物理","化学","英语","历史","地理","生物","计算机科学","艺术","音乐","体育","编程","文学","心理学","哲学","经济学","社会学","物理学实验","化学实验","生物学实验"]},{"param_name": "#{status}", "value": ["未开课","授课中"]},{"param_name": "#{statusCode}","value": [0,1]}], "explanation": "输出课程名称course_name、课程时间course_time、课程地点course_location。注：status为常量映射类型。变量映射字段：statusCode。"}]');

2. 创建问题模板索引表

   支持自定义索引表名（需符合数据库规范）。本文以pattern_index为例，创建问题模板索引表的SQL语句如下：

   /*polar4ai*/CREATE TABLE pattern_index(id integer, pattern_question text_ik_max_word, pattern_description text_ik_max_word, pattern_sql text_ik_max_word, pattern_params text_ik_max_word, pattern_tables text_ik_max_word, vecs vector_768, PRIMARY KEY (id));

   说明

   • 问题模板索引表不会直接展示于数据库中。如需查看相关信息，请使用SQL语句/*polar4ai*/SHOW TABLES;进行查询。

   • 如果需要删除问题模板索引表，以pattern_index为例，可以执行此SQL语句/*polar4ai*/DROP TABLE IF EXISTS pattern_index;。

3. 将问题模板表中信息导入索引表

   说明

   问题模板表中的信息不得为空。在执行导入索引表的SQL语句之前，您需至少添加一条记录。

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_text2vec, SELECT '') WITH (mode='async', resource='pattern') INTO pattern_index;

   参数说明

   • _polar4ai_text2vec为文本转向量化模型。

   • INTO后需要填写之前创建的问题模板索引表名，该名称为步骤2：创建问题模板索引表的表名。

   • WITH()内支持配置多个参数，以便对相关行为进行设置：

     参数	必填	说明
     mode	是	数据写入模式。固定为async，表示为异步模式。
     resource	是	资源类型。固定为pattern，表示对问题模板信息进行向量化。
     pattern_table_name	否	设置转向量的问题模板表的表名，该名称为步骤1：创建问题模板表中的表名。 默认为polar4ai_nl2sql_pattern，表示对polar4ai_nl2sql_pattern表执行转向量操作。设置时，需填写以polar4ai_nl2sql_pattern开头的问题模板表的表名。

     若您希望为不同场景或业务维护不同的问题模板索引表，可以在创建问题模板索引表时指定不同的表名。例如，为用户相关场景创建polar4ai_nl2sql_pattern_user表，随后在第二步创建问题模板索引时，将索引名设定为pattern_index_user。在将信息导入索引表时，使用如下SQL语句：

     /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_text2vec, SELECT '') WITH (mode='async', resource='pattern', pattern_table_name='polar4ai_nl2sql_pattern_user') INTO pattern_index_user;

4. 查看任务状态

   执行完上述导入索引表的语句后，将返回该任务的task_id，例如bce632ea-97e9-11ee-bdd2-492f4dfe0918。您可以通过以下命令查看导入状态，

   /*polar4ai*/SHOW TASK `bce632ea-97e9-11ee-bdd2-492f4dfe0918`;

   当返回值显示为finish时，表示任务已完成。问题模板信息可被模型引用。您可以执行以下SQL语句来查看问题模板索引信息：

   /*polar4ai*/SELECT * FROM pattern_index;

   说明

   如果polar4ai_nl2sql_pattern表中的数据发生了变更，则需要重新执行步骤3：将问题模板信息导入索引表。

5. 在线使用问题模板

   您可以执行以下SQL语句来在线使用LLM-based NL2SQL和问题模板：

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, SELECT '查询课程名称为数学，授课状态为授课中的课程') WITH (basic_index_name='schema_index', pattern_index_name='pattern_index');

   SELECT course_name, course_time, course_location FROM courses WHERE course_name='数学' AND status=1;

   参数说明

   • SELECT后需要输入待转换为SQL语句的问题。

   • basic_index_name为当前数据库检索索引表名称。

   • pattern_index_name为问题模板索引表名称。

   • WITH()内支持配置多个参数，以便对相关行为进行设置（其他参数说明请参见在线使用LLM-based NL2SQL）：

     参数名称	参数描述	取值范围
     pattern_index_top	召回问题模板的最相近个数。	取值范围：[1,10]。 默认值为2，表示对当前问题模板只选出最优的2条模板。
     pattern_index_threshold	召回问题模板是否相近时所使用的阈值。	取值范围：(0,1]。 默认值为0.85，表示当问题模板向量匹配超过0.85时才会被选中。

适用场景

• 场景一：对问题中的确定性词语进行替换，如人名、行业用语、商品名替换等。

  例如：对于所有涉及张三的问题，将张三替换为ZS001。在此情况下，对于问题张三上个月的销售额是多少？和张三今年的总销售额是多少？，在最终调用大模型之前，可以通过配置表将其全部预处理为ZS001上个月的销售额是多少？和ZS001今年的总销售额是多少？。

• 场景二：对包含特定词语的问题补充额外信息。

  例如，对于所有涉及总销售额的问题，应补充总销售额的计算公式：总销售额 = SUM(销售额)。在最终调用大型模型之前，可以通过配置表添加此类信息，当问题满足相应条件时进行补充。

• 场景三：对特定表或列的值进行映射替换。

  例如：对所有最终涉及student_courses表的SQL，将其中的status = '请假'替换为status = 0，以作为一种列值映射的兜底措施。

语法说明

构建配置表的SQL语句如下，表名polar4ai_nl2sql_llm_config固定不可变。

DROP TABLE IF EXISTS `polar4ai_nl2sql_llm_config`;
CREATE TABLE `polar4ai_nl2sql_llm_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `is_functional` int(11) NOT NULL DEFAULT '1' COMMENT '是否生效',
  `text_condition` text COMMENT '文本条件',
  `query_function` text COMMENT '查询处理',
  `formula_function` text COMMENT '公式信息',
  `sql_condition` text COMMENT 'SQL条件',
  `sql_function` text COMMENT 'SQL处理',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

参数说明

示例

1. 未添加配置表前，执行以下SQL语句：

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, SELECT '筛选出2个请假次数最多的学生，按照学生的请假次数降序排列，显示学生的名字和请假次数。') WITH (basic_index_name='schema_index');

   SELECT s.student_name, COUNT(sc.id) AS leave_count FROM students s JOIN student_courses sc ON s.id = sc.student_id WHERE sc.status = 0 GROUP BY s.student_name ORDER BY leave_count DESC LIMIT 2;

2. 按照语法说明构建配置表。

3. 添加配置记录。如果表students或表student_courses在SQL中，且表courses不在SQL中时，将status = 0替换成status = 10。

   INSERT INTO `polar4ai_nl2sql_llm_config` (`is_functional`,`sql_condition`,`sql_function`) VALUES (1,'students||student_courses&&!!courses','{"replace":{"status = 0":"status = 10"}}');

4. 执行步骤1的SQL语句。可以看出生成的SQL语句已将status的值进行了替换。

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, SELECT '筛选出2个请假次数最多的学生，按照学生的请假次数降序排列，显示学生的名字和请假次数。') WITH (basic_index_name='schema_index');

   SELECT s.student_name, COUNT(sc.id) AS leave_count FROM students s JOIN student_courses sc ON s.id = sc.student_id WHERE sc.status = 10 GROUP BY s.student_name ORDER BY leave_count DESC LIMIT 2;

语法说明

创建自定义表列注释表的SQL语句如下，表名polar4ai_nl2sql_table_extra_info固定不可变。

DROP TABLE IF EXISTS `polar4ai_nl2sql_table_extra_info`;
CREATE TABLE `polar4ai_nl2sql_table_extra_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `table_name` text COMMENT '表名称',
  `table_comment` text COMMENT '表说明',
  `column_name` text COMMENT '列名称',
  `column_comment` text COMMENT '列说明',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

示例

1. 创建自定义表列注释表。

2. 对student_courses表中status列的注释进行修改。此处，为status列新增了一个选项说明：2-缺勤。

   INSERT INTO `polar4ai_nl2sql_table_extra_info` (`table_name`,`table_comment`,`column_name`,`column_comment`) VALUES ('student_courses','课程学生信息表','status','学生状态 0-请假，1-正常，2-缺勤。');

3. 重新执行步骤：将数据表中的信息导入检索索引表。

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_text2vec, SELECT '') WITH (mode='async', resource='schema') INTO schema_index;

4. 查看任务状态。

   执行完上述导入检索索引表的语句后，将返回该任务的task_id，例如bce632ea-97e9-11ee-bdd2-492f4dfe0918。您可以通过以下命令查看导入状态，当taskStatus显示为finish时，表示任务已完成。

   /*polar4ai*/SHOW TASK `bce632ea-97e9-11ee-bdd2-492f4dfe0918`;

5. 在线使用LLM-based NL2SQL。

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, SELECT '筛选出2个缺勤次数最多的学生，按照学生的缺勤次数降序排列，显示学生的名字和缺勤次数。') WITH (basic_index_name='schema_index');

   SELECT s.student_name, COUNT(sc.id) AS absence_count FROM student_courses sc JOIN students s ON sc.student_id = s.id WHERE sc.status = 2 GROUP BY s.student_name ORDER BY absence_count DESC LIMIT 2;

   从上述输出结果中可以看出，LLM-based NL2SQL模型已经将缺勤对应为student_courses表中status列的列值2。