CN114003230A - 一种sql脚本快速编写方法及系统 - Google Patents

Abstract

本发明公开了一种SQL脚本快速编写方法及系统，该系统包括：第一提取模块，用于提取SQL脚本中的表关联关系；第二提取模块，用于提取SQL脚本中的表限制条件；第一整合模块，用于整合表关联关系，得到常用表关联关系；第二整合模块，用于整合表限制条件，得到常用表限制条件；第一输出模块，用于输出第一目标表和第二目标表的常用表关联关系；第二输出模块，用于输出第三目标表的常用表限制条件。本发明经过整合梳理将相关的计算口径信息存储下来并进行后续应用，可以提高后续SQL代码的编写效率和准确性，也利于分析不同数据之间的关系。

Description

一种SQL脚本快速编写方法及系统

技术领域

本发明涉及大数据智能化技术领域，特别是一种SQL脚本快速编写方法及系统。

背景技术

SQL是一门ANSI的标准计算机结构化查询语言，用来访问和操作各类数据库系统。SQL代码包含了丰富的技术口径信息，比如表的限制条件、不同表之间的关联关系(主外键关系)，这些信息都没有直观地体现在SQL 代码中，需要经过SQL解析才能让计算机进行识别。

发明内容

本发明为解决上述问题，提供了一种SQL脚本快速编写方法及系统，经过整合梳理将相关的计算口径信息存储下来并进行后续应用，可以提高后续 SQL代码的编写效率和准确性，也利于分析不同数据之间的关系。

为实现上述目的，本发明采用的技术方案为：

一种SQL脚本快速编写系统，包括：第一提取模块，用于提取SQL脚本中的表关联关系；第二提取模块，用于提取所述SQL脚本中的表限制条件；第一整合模块，用于整合所述表关联关系，得到常用表关联关系；第二整合模块，用于整合所述表限制条件，得到常用表限制条件；第一输出模块，用于输出第一目标表和第二目标表的所述常用表关联关系；第二输出模块，用于输出第三目标表的所述常用表限制条件。

优选的，所述系统还包括优化模块，用于将所述SQL脚本补充完整，并根据制定的XPATH规则进行封装。

优选的，所述第一整合模块采用统计学算法。

优选的，所述表关联关系进行双向增益。

优选的，所述第二整合模块采用监督学习算法，对所述表限制条件进行特征化处理。

优选的，所述表限制条件的特征值分别根据字段中文名判定类型、字段类型、限制条件、限制值长度和前文环境获得。

优选的，所述字段中文名判定类型包括日期常量、可罗列字符常量、数值常量和其他类。

优选的，所述监督学习算法为XGBoost算法。

优选的，如果所述常用表限制条件不准确时，手动干预所述监督学习算法。

基于同样的发明构思，本发明还提供了一种SQL脚本快速编写方法，包括以下步骤：提取SQL脚本中的表关联关系；提取所述SQL脚本中的表限制条件；整合所述表关联关系，得到常用表关联关系；整合所述表限制条件，得到常用表限制条件；输出第一目标表和第二目标表的所述常用表关联关系；输出第三目标表的所述常用表限制条件。

本发明的有益效果是：

1.提取SQL代码中的表关联关系和表限制条件，整合得到常用表关联关系和常用表限制条件，应用于后续SQL代码编写；

2.表关联关系进行双向增益，保证整合的信息的完整性；

3.采用监督学习算法优化常用表限制条件的整合效果。

附图说明

图1为一种补充完整的SQL结构的示例图；

图2为一种用于存储形变后的AST语法解析树的XML格式的大字段的示意图；

图3为SQL编辑器的右键菜单图。

具体实施方式

为了使本发明所要解决的技术问题、技术方案及有益效果更加清楚、明白，以下结合具体实施例对本发明进行进一步详细说明。应当理解，此处所描述的具体实施例仅用以解释本发明，并不用于限定本发明。

SQL代码的编写基本上是ETL开发人员根据业务需求及系统设计、取数口径进行编写。历史写过的SQL往往包含了大量可复用的信息(口径、关联关系、限制条件、参数变量等)。但是，实际在进行SQL编写的过程中，如果没有系统的支持，无法继承使用这些信息。

实施例一

本实施例公开了一种SQL脚本快速编写系统，包括：

优化模块，用于将SQL脚本补充完整，并根据制定的XPATH规则进行封装。

优化模块对SQL进行遍历，与数据库的元数据库进行匹配，对SQL中每个层次使用的库、表、字段信息进行补充完整。

参照附图1，本实施例作为示例的SQL代码如下，记为SQL1：

SELECT A.A1,B1

fromAA A

INNER JOIN BB

ON A.A1＝B

WHEREA.A2＝‘S’AND B.B2＝1

优化模块的操作具体包括：针对节点属性为表的节点，扩充节点的第一子节点，第一子节点的节点属性为表中文名，值为该表对应中文名。

针对节点属性为字段的节点，扩充节点的第二子节点，第二子节点的节点属性为字段中文名，值为该字段对应中文名。

针对节点属性为字段的节点，扩充节点的第三子节点，第三子节点的节点属性为字段类型，值为该字段对应字段类型。

针对节点属性为表的节点，如果没有节点属性为表别名的兄弟节点，扩展节点的第一兄弟节点，第一兄弟节点的节点属性为表别名，值为该表对应别名。如果已有这个兄弟节点，不额外扩展，直接复用。

针对节点属性为字段的节点，如果没有节点属性为字段别名的兄弟节点，扩展节点的第二兄弟节点，第二兄弟节点的节点属性为字段别名，值为该字段对应别名。如果已有这个兄弟节点，不额外扩展，直接复用。

针对节点属性为字段的节点，如果没有节点属性为归属表别名的兄弟节点，扩展节点的第三兄弟节点，第三兄弟节点的节点属性为归属表别名，值为该字段归属的表对应别名。如果已有这个兄弟节点，不额外扩展，直接复用。

由于SQL解析树存在于内存中(JAVA类)，无法有效存储，遍历比较困难，属性提取不直观，并不适宜后续应用。在本实施例中，根据所有SQL结构，建立一个用于存储SQL解析树的结构(SQL知识库结构)，该结构具备可二维化存储、有灵活的遍历方式、可重新还原成SQL、可快速提取相关信息等能力，后续方便使用，并设计一个可以使用XPATH语法进行遍历的大字段，用于存储该结构。

在本实施例中，优化模块按照制定的XPATH规则，将补充完整的SQL 按照XML格式进行封装并保存到数据库二维表中。

该XPATH规则主要属性类型包括：

root根节点、ddlStatementddl语句、dmlStatementdml语句、SimpleSelect 简单查询语句、QuerySpecification查询描述、SelectElements查询元素列表、FullColumnName字段、FullColumnCnName字段中文名、FullColumnType字段类型、FromClause from语句块、TableSources库名、TableSourceBase表来源、AtomTableItem原子表、TableName表名、TableCnName表中文名、FullId 完整Id、Uid唯一Id、SimpleId简单Id、OuterJoin外连接、TNode终结符、 SubqueryTableItem子查询表、ComparisonOperator比较运算符、 PredicateExpression判别表达式、BinaryComparasionPredicate二元比较判别、 ExpressionAtomPredicate原子表达式判别、ConstantExpressionAtom常量表达式判别、Constant常量、DecimalLiteral数字等。

将单段SQL语句保存在数据库二维表中的一行的XML里。将多段SQL 语句或SQL脚本根据SQL序号，将分割成的多个单段SQL语句分开保存到多行的XML里。

第一提取模块，用于提取SQL脚本中的表关联关系。

在本实施例中，使用XPATH规则访问数据库二维表，获取的结构如表1 所示。具体的，XPATH规则是提取ComparisonOperator(等式)值为＝，并且等式左右两边的TableSourceBase.TableName.FullColumnNam(字段)归属于不同的表。

表1 SQL1表关联关系

表名	字段名	关联表名	关联字段名	关联关系
					AA	A1	BB	B1	“＝”

第一整合模块，用于整合表关联关系，得到常用表关联关系。

首先，在本实施例中，对表关联关系进行处理。由于关联的方向是相对的，对相关信息进行双向增益，如表2所示(其中还有另一段代码SQL2也登记了类似的表关联关系)。

表2 双向增益表关联关系

双向增益可以对另一个方向的关联关系进行补充，保证整合的信息的完整性，以便进行后续应用。

由于表关联关系在一个数据库应该是相对稳定的，因此表关联关系可以采用统计学算法来进行整合。具体为，统计两个关联表中某一添加出现的概率，保留达到预定的阈值的表关联关系，作为常用表关联关系。确定的表关联关系，即常用表关联关系登记如表3所示。

表3 常用表关联关系

表名	字段名	关联表名	关联字段名	关联关系	概率
						AA	A1	BB	B1	“＝”	0.667
BB	B1	AA	A1	“＝”	0.667

存储常用表关联关系用于第一输出模块在SQL代码编写时进行推荐。

其中，当出现第一输出模块的推荐错误的时候，根据具体需要，可以调整阈值，从而获得相对正确的常用表关联关系。

第二提取模块，用于提取SQL脚本中的表限制条件。

在本实施例中，使用XPATH规则访问数据库二维表，获取的结构如表4 所示。具体的，XPATH规则是提取ComparisonOperator(等式)值为＝，并且等式左右分别为TableSourceBase.TableName.FullColumnName(字段)和 Constant(常量)。

表4 SQL1表限制条件

表名	字段名	限制条件	限制值
				AA	A2	“＝”	‘S’
BB	B2	“>”	1

第二整合模块，用于整合表限制条件，得到常用表限制条件。

针对表限制条件，由于通常SQL语句中关于表的限制条件非常多，干扰信息多(比如一个case when语句，可能会包含十几个限制条件)，如果简单的套用统计学算法，一来无法设置合理的阈值从而导致整合错误，二来常用限制条件可能随着时间一直在改变(比如有段时间大家都在写“A国总统＝‘BCD’”，过段时间就是“A国总统＝‘EF’”)，因此第二提取模块提取的信息还是不够的，需要进一步补充特征信息。由于复杂度问题，之后的表限制条件信息根据表5所示的结构进行模拟。

表5 表限制条件

其中，字段中文名判定类型：根据字段的英文名称+字段的中文名称+字段类型综合判定，由于系统中常用表限制条件一般只在日期常量、可罗列字符常量、数值常量之间，根据元数据信息对字段进行业务分类，可以把无法明确判别的类型全部标注为其他类。

对表限制条件信息进行特征化处理。其中，

1、序列ID使用表ID(表在元数据库ID)+字段ID(字段在元数据库 ID)组合获得。

2、特征值1由字段中文名判定类型转化获得，规则如表6所示。

表6 字段中文名判定类型特征转化规则

原值	特征值1	特征转化方法
			日期常量	10	直接指定
数值常量	20	直接指定
			可罗列字符常量	30	直接指定
其他类	40	直接指定

3、特征值2由字段类型转化获得，规则如表7所示。

表7 字段类型特征转化规则

原值	特征值2	特征转换方法
			Date	10	直接指定
Char(8\16\24)	20\21\22	直接指定，每10个长度加1
			int	30	直接指定
string	40	直接指定

4、特征值3由限制条件转化获得，规则如表8所示。

表8 限制条件特征转化规则

5、特征值4由限制值长度直接获得。

6、特征值5由转化获得，规则如表9所示。

表9 出现前文环境特征转化规则

原值	特征值5	特征转换方法
			WHERE子句	10	直接指定
CASE WHEN子句	20	直接指定
			ON子句	30	直接指定
OR子句	40	直接指定

7、常用条件进行人工标注(有监督)。

最终获得的学习样本如表10所示。

表10 学习样本

序列ID	特征值1	特征值2	特征值3	特征值4	特征值5	常用条件
							11102	10	10	10	8	10	Y
22202	20	20	20	1	10	Y
							11102	10	10	10	8	20	N
11102	10	10	10	8	20	N
							11102	10	10	10	8	20	N
11102	10	10	10	8	30	N
							11102	10	10	10	8	10	Y
22201	20	20	20	1	10	Y
							11101	30	20	10	2	40	N
11103	30	21	10	2	30	N
							11104	30	22	10	2	30	N
11103	10	10	10	8	30	N
							11104	40	20	10	8	30	N

采用监督学习算法整合表限制条件。可以采用的监督学习算法决策树、逻辑回顾、随机森林、XGBoost、支持向量机、贝叶斯等。在本实施例中，采用XGBoost算法，其最终模型验证效果更好、性能更高。对上述学习样本进行训练并获得机器学习模型。

使用机器学习模型对输入的表限制条件进行判定，保留所有常用条件结果为Y的信息，并对相同或者相似的记录进行合并。最终获得的常用表限制条件的结构如表11所示。其中，判定结果会根据模型输入自动优化。

表11 常用表限制条件

表名	字段名	常用限制值
			AA	A2	＝‘X’
BB	B2	>‘X’

存储常用表限制条件用于第二输出模块在SQL代码编写时进行推荐。

其中，当出现第二输出模块的推荐不准确的时候，可以手动干预模型的判定结果，重新标注相关样本后进行训练。

第一输出模块，用于输出第一目标表和第二目标表的常用表关联关系。

在系统框架底下(WEB化的SQL编辑器)，所有的SQL开发人员在编写代码的时候通过手动或者自动对当前SQL进行补充，补充的信息可包含插入语句的表字段、关联语句的表关联关系、限制语句的表限制条件等，从而实现知识的继承，保证代码的准确性和编码的高效性。

过往人工写条件和关联，需要对业务及表设计有比较深入的了解，并且进行多次数据的验证。通过本实施例所述的系统的辅助编写，既可以实现知识在不同人之间的继承，也提高了SQL代码编写的效率和准确性。

当选择2张或者多张表，并选择关联条件功能时，将第一张和最后一张表作为目标表，并且分别去匹配第一整合模块获得的常用表关联关系中的表名跟关联表名，组装SQL进行输出(输出AA.A1＝BB.B1，此部分即该功能的补充内容)。如果无法匹配，则输出无相关记录。

或者，第一输出模块、第二输出模块和第三输出模块可以监控用户输入界面，自动触发相关功能，并进行SQL的快速编写。

当用户输入ajoinb子句时，自动触发关联条件功能，输出a和b的表关联条件。

同时，第二输出模块输出b的常用表限制条件。

第二输出模块，用于输出第三目标表的常用表限制条件。

当选择1张或者多张表，并选择限制条件功能时，将最后一张表作为目标表，并且同第二整合模块获得的该表的常用表限制条件进行匹配，组装 SQL进行输出(输出AA.A2＝‘X’，此部分即该功能的补充内容，一般一个表的常用表限制条件会有3-4个，而上述模拟的时候为了简便，只体现了一个条件，实际场景会复杂一点，输出多个常用表限制条件)。如果无法匹配，则输出无相关记录。

或者，当用户输入一张表+where子句时，自动触发限制条件功能，输出该表的常用表限制条件。

另外，本实施例所述的系统还可以包括第三输出模块，用于输出第四目标表的所有字段。

当选择1张或者多张表，并选择insert功能时，将最后一张表作为目标表，并且通过查询其元数据库结构并进行字段拼装，最终输出其所有字段 (A1,A2,A3,A4,A5，此部分即该功能的补充内容)。

或者，当用户输入insert into table A时，自动触发insert功能，补充相关insert字段：insert intotable A(A1,A2,A3,A4,A5)。

本实施例所述的系统可以使SQL开发人员在编写代码的过程中获得支持(历史SQL、批量脚本、他人写的代码等整合后的信息)，从而快速获得正确的口径、关联关系、限制条件等，提高SQL代码编写的效率和准确性。

本实施例所述的系统可以降低业务人员使用SQL语言进行自助取数的门槛，通过数据库支撑，使更多的人具备编写SQL代码的能力。

实施例二

本实施例公开了一种SQL脚本快速编写方法，包括以下步骤：

S1.将SQL结构补充完整。

对SQL进行遍历，与数据库的元数据库进行匹配，对SQL中每个层次使用的库、表、字段信息进行补充完整。

按照制定的XPATH规则，将补充完整的SQL按照XML格式进行封装并保存到数据库二维表中。

S2.提取SQL脚本中的表关联关系。

S3.提取SQL脚本中的表限制条件。

使用XPATH规则访问上述数据库二维表，分别获取表关联关系和表限制条件。

S4.整合表关联关系，得到常用表关联关系。

对获取的表关联关系进行双向增益。采用统计学算法整合处理后的信息。统计两个关联表中某一添加出现的概率，保留达到预定的阈值的表关联关系，作为常用表关联关系。

S5.整合表限制条件，得到常用表限制条件。

对表限制条件信息进行特征化处理。采用监督学习算法整合处理后的信息。在本实施例中，采用XGBoost算法。

对学习样本进行人工标注后，训练获得机器学习模型。通过使用机器学习模型对输入的表限制条件进行判定，获取常用表限制条件。

存储常用表关联关系和常用表限制条件用于后续步骤的代码推荐。

S6.输出第一目标表和第二目标表的常用表关联关系。

在系统框架底下(WEB化的SQL编辑器)，通过手动或者自动将目标表与常用表关联关系的结构进行匹配，组装SQL进行输出。

S7.输出第三目标表的常用表限制条件。

在系统框架底下(WEB化的SQL编辑器)，通过手动或者自动匹配目标表的常用表限制条件，组装SQL进行输出。

S8.输出第四目标表的所有字段。

上述说明示出并描述了本发明的优选实施例，应当理解本发明并非局限于本文所披露的形式，不应看作是对其他实施例的排除，而可用于各种其他组合、修改和环境，并能够在本文发明构想范围内，通过上述教导或相关领域的技术或知识进行改动。而本领域人员所进行的改动和变化不脱离本发明的精神和范围，则都应在本发明所附权利要求的保护范围内。

Claims (10)

1.一种SQL脚本快速编写系统，其特征在于，包括：

第一提取模块，用于提取SQL脚本中的表关联关系；

第二提取模块，用于提取所述SQL脚本中的表限制条件；

第一整合模块，用于整合所述表关联关系，得到常用表关联关系；

第二整合模块，用于整合所述表限制条件，得到常用表限制条件；

第一输出模块，用于输出第一目标表和第二目标表的所述常用表关联关系；

第二输出模块，用于输出第三目标表的所述常用表限制条件。

2.根据权利要求1所述的SQL脚本快速编写系统，其特征在于，还包括优化模块，用于将所述SQL脚本补充完整，并根据制定的XPATH规则进行封装。

3.根据权利要求1所述的SQL脚本快速编写系统，其特征在于，所述第一整合模块采用统计学算法。

4.根据权利要求1所述的SQL脚本快速编写系统，其特征在于，所述表关联关系进行双向增益。

5.根据权利要求1所述的SQL脚本快速编写系统，其特征在于，所述第二整合模块采用监督学习算法，对所述表限制条件进行特征化处理。

6.根据权利要求5所述的SQL脚本快速编写系统，其特征在于，所述表限制条件的特征值分别根据字段中文名判定类型、字段类型、限制条件、限制值长度和前文环境获得。

7.根据权利要求6所述的SQL脚本快速编写系统，其特征在于，所述字段中文名判定类型包括日期常量、可罗列字符常量、数值常量和其他类。

8.根据权利要求5所述的SQL脚本快速编写系统，其特征在于，所述监督学习算法为XGBoost算法。

9.根据权利要求5所述的SQL脚本快速编写系统，其特征在于，如果所述常用表限制条件不准确时，手动干预所述监督学习算法。

10.一种SQL脚本快速编写方法，其特征在于，包括以下步骤：

提取SQL脚本中的表关联关系；

提取所述SQL脚本中的表限制条件；

整合所述表关联关系，得到常用表关联关系；

整合所述表限制条件，得到常用表限制条件；

输出第一目标表和第二目标表的所述常用表关联关系；

输出第三目标表的所述常用表限制条件。

Images