Method of Generating SQL Statement Model from Natural Language
Through the combination of SQL multi-task classification prediction model and W-VALUE conditional value prediction model, directly predict selected columns, conditional columns and conditional operators, solving the problem of multiple tasks and difficulty in the existing NL2SQL model, and improving SQL statements Efficiency and accuracy of generation.
Patent Information
- Application Number
- CN202410640963.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-05-22
- Publication Date
- 2024-12-13
- Estimated Expiration
- 2044-05-22
AI Technical Summary
The existing NL2SQL model fails to effectively merge and handle strong dependencies between selected columns and aggregate functions and conditional columns and conditional operators, resulting in numerous tasks and difficult tasks.
Using the SQL multi-task classification prediction model, each column name of the data table is directly predicted through the semantic feature encoding layer and the list-based enhancement layer, the selected column, the condition column and the condition operator are judged, and the W-VALUE condition value is combined. The prediction model obtains the real condition value and simplifies the generation of SQL statements.
The complexity of the NL2SQL model is simplified, the ability to generate complex SQL statements is improved, and query failures are avoided due to inconsistent expressions.
Smart Images
Figure CN118349567B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of NL2SQL technology, and in particular to a method for generating an SQL statement model from a natural language. Background Art
[0002] SQL (structured query language) is a database query and programming language used to access, query, and update database data and manage relational database systems. NL2SQL (Natural Language to SQL) is a technology that converts natural language expressions of humans into SQL statements that can be executed by the database. It is one of the subtasks in the field of semantic parsing and can also be called Text2SQL (Text to SQL) technology.
[0003] The existing models SQLNet, SQLova, and X-SQL that perform well on English WikiSQL all decompose the task of generating a complete SQL statement into six subtasks: S-COL (Select-Column), S-AGG (Select-Aggregation), W-NUM (Where-Number), W-COL (Where-Column), W-OP (Where-Operato), and W-VAL (Where-Value). Figure 2 , and finally fill in the parameters into the SQL statement template.
[0004] These models have the same characteristics: for S-COL and S-AGG, they first predict which column is the selected column in the column name set (S-COL), and then predict its aggregation function operation on this column (S-AGG); for W-COL and W-OP, they first predict which column is the conditional column in the column name set (W-COL), and then predict its matching conditional operator on this column (W-OP).
[0005] In fact, analyzing a large number of SQL samples, we can find that there is a strong dependency between the selected columns and the aggregation functions on the columns (S-COL and S-AGG), and between the conditional columns and the conditional operators (W-COL and W-OP), and the corresponding variables also correspond one to one. However, the above model does not combine S-COL and S-AGG, W-COL and W-OP for unified processing, and cannot use the same feature representation for prediction. It requires a large number of prediction tasks and is difficult to predict. Summary of the invention
[0006] In view of the above problems in the prior art, the present invention provides a method for generating an SQL statement model from a natural language, which solves the problem that the existing NL2SQL model does not merge processing tasks and thus results in too many tasks.
[0007] In order to achieve the above object, the technical solution adopted by the present invention is as follows:
[0008] A method for generating a SQL statement model from a natural language is provided, comprising the steps of:
[0009] S1. construct an input sequence of natural language query statements and data table column names;
[0010] S2. Input the input sequence into the SQL multi-task classification prediction model: wherein the SQL multi-task classification prediction model includes a semantic feature encoding layer, a list enhancement layer and three sub-task classification layers;
[0011] S3, use the semantic feature encoding layer to obtain the global feature vector of the input sequence ;
[0012] S4, using the list-based enhancement layer to transform the global feature vector Enhance the column name feature vector of the data table through attention weighting;
[0013] S5. Add the enhanced column name feature vector and global information feature vector to By inputting into the three subtask classification layers, the three subtask classification layers predict each column name of the data table and obtain the aggregation operation of the selected column, the conditional operator of the conditional column, and the logical relationship of the conditional column respectively;
[0014] S6. Input all candidate conditional values in the input sequence and condition column into the W-VALUE conditional value prediction model to obtain the true conditional value;
[0015] S7. Fill the aggregation operation of the selected column, the conditional operator of the conditional column, the logical relationship of the conditional column and the real conditional value into the SQL statement template and output the SQL statement.
[0016] The working principle of this solution is to input the natural language query statement and the data table column name into the SQL multi-task classification prediction model and the W-VALUE conditional value prediction model. The SQL multi-task classification prediction model directly predicts and classifies each column name of the data table, and determines whether each column name is a selected column, a conditional column, or a non-selected non-conditional column, and at the same time obtains the aggregation operation of the selected column and the conditional operator of the conditional column; the W-VALUE conditional value prediction is used to obtain the true conditional value. Compared with the existing technology that first predicts the selected column and then predicts the aggregation function operation on it, or first predicts the conditional column and then predicts its conditional operator, this solution merges two subtasks with strong dependencies, thereby simplifying downstream tasks, which helps the NL2SQL model to more effectively handle complex SQL statement generation.
[0017] Furthermore, the semantic feature encoding layer is the pre-trained model BERT, the language of the natural language query is Chinese, and the natural language query is ;
[0018] Data Sheet ;
[0019] { } { };
[0020] Input of the pre-trained model BERT:
[0021]
[0022] ;
[0023] in, Represents input; Represents a splicing operation; It is a classifier, added at the beginning of the entire sequence, used to learn all the semantic information of the entire sequence; is a separator, added in Q Separate at the end of a sentence, between each column name, and at the end of the entire input sequence; and Respectively Q 1st, 2nd, i and n characters; t Indicates the number of column names in the data table; , , and Respectively represent the 1st, 2nd, and j and m column names; each column name consists of kword vectors, { }, { }, and Respectively 1st, 2nd and k word vectors, and Respectively 1st, 2nd and k word vectors.
[0024] Output of the pre-trained model BERT:
[0025]
[0026]
[0027] Indicates output; for The hidden layer vector of , after being learned by the bidirectional Transformer encoder, integrates the information of the entire input sequence and is the global feature vector; , and Respectively Q 1st, 2nd and n The hidden vector of the characters; Used to separate each different subsequence; and Respectively represent the hidden vectors of the first and second characters in the first column name of the data table; and Respectively represent the data table m The hidden vectors of the first and second characters in the column names.
[0028] Furthermore, the attention matrix in the list-based enhancement layer for:
[0029]
[0030]
[0031]
[0032] in, softmax represents the normalized exponential function; U and V are two learnable parameter matrices introduced 、V , respectively and Do a linear transformation and get the matrix , Indicated by k The characters consist of i column names; ; , express The eigenvector of Indicates that d A matrix of 1 row and 1 column, Indicates that d Line and d A matrix of columns, Indicates that k Line and d Matrix of columns.
[0033] Furthermore, the three subtask classification layers include CONN-OP Classification models; S-COL-AGG Classification models and W-COL-OP Classification models;
[0034] CONN-OP The classification model is used to predict the logical relationship of the conditional column. It is a three-classification model with a value range of {0: ' ', 1: 'AND ', 2: 'OR '};
[0035] CONN-OP The probability model of the classification model is:
[0036] express CONN-OP Output classification probability distribution; are parameters that can be learned during training, It means that there are 3 rows and d A matrix of columns;
[0037] S-COL-AGG The classification model predicts the selected columns and the aggregation functions of the selected columns by directly predicting the aggregation operations on each column. It is a seven-classification model with a value range of {0: ' ', 1: 'AVG', 2: 'MAX', 3: 'MIN', 4: 'COUNT', 5: 'SUM', 6: 'NO_OP'}; 6: 'NO_OP' means that the column is not a selected column;
[0038] S-COL-AGG The probability model of the classification model is:
[0039] in, Indicates data table i Listed in S-COL-AGG The output probability distribution of iWhether the column is selected and contains aggregation operations; This is the data sheet i The feature vectors weighted by the attention mechanism are: are parameters that can be learned during training, It means that there are 7 rows and d A matrix of columns;
[0040] W-COL-OP The classification model predicts the conditional column and the conditional operator of the conditional column at the same time by directly predicting the conditional operator on each column. It is a five-category model with a value range of {0: '>', 1: '<', 2: '= ', 3: '!= ', 4: 'NO_OP '}; 4: 'NO_OP ' means that the column is not a selected column;
[0041] W-COL-OP The probability model of the classification model is:
[0042] in, Indicates data table i Listed in W-COL-OP The output probability distribution of the task, that is, predicting i Whether the column is used as a conditional column and matches the corresponding conditional operator; yes S-COL-AGG No. i The output vector of columns, represents the concatenation operation. The dimension after concatenation is (768+7). are parameters that can be learned during training, It means that there are 5 rows and d Matrix of columns.
[0043] Furthermore, the method for obtaining the real conditional value by the W-VALUE conditional value prediction model includes the steps of:
[0044] B1. Input the natural language query statement, condition column and all condition candidate values on the condition column into the encoding layer to obtain the global feature vector ;
[0045] B2. Use the fully connected layer to construct a binary classification conditional value classification layer. The binary classification conditional value classification layer is constructed through the global feature vector Perform binary classification prediction on each candidate conditional value to obtain the true conditional value.
[0046] Furthermore, the encoding layer of step B1 is the Chinese pre-trained model BERT, and the natural language query statement , data table i The column name is , data table iThe first i The candidate condition value is ;
[0047] The input of the Chinese pre-trained model BERT is:
[0048]
[0049]
[0050] The output of the Chinese pre-trained model BERT is:
[0051]
[0052]
[0053] in, represents the input sequence; Represents a splicing operation; and Respectively Q 1st, 2nd and i characters; and Respectively represent i The first, second, and j characters; is a separator, added in Q Separate at the end of a sentence, between each column name, and at the end of the entire input sequence; A Chinese character vector representing a conditional operator; and Respectively represent i The first i The first, second, and third k characters.
[0054] represents the output sequence; Represents the global feature vector of the output sequence, which integrates the information of the entire input sequence; , and Respectively Q 1st, 2nd and i The hidden layer vector of characters; Used to separate each different subsequence; and Respectively represent i The first, second, and j hidden layer vectors; express The hidden vector of , and Represents conditional values No. o Character hidden vector.
[0055] Furthermore, the probability model of the binary conditional value classification layer is:
[0056]
[0057] Indicates i The first i The probability of a candidate conditional value being the true conditional value, , are parameters that can be learned during training, Represents a system with 2 rows and d Matrix of columns.
[0058] Furthermore, the construction of the input sequence of the Chinese pre-training model BERT includes the following steps:
[0059] B11. Determine the candidate condition value type on the condition column. If the candidate condition value is a numeric type, use regular expressions to extract all numeric expressions in the natural language query statement and standardize all numeric expressions in the natural language query statement into numeric types. Otherwise, proceed to B12.
[0060] B12. Generate an input sequence. The composition order of the input sequence is natural query statement, condition column, Chinese condition operator and candidate condition value.
[0061] In this solution, different types of conditional values in the text are designed and processed differently, taking into account the inconsistency of Chinese expressions. When constructing the input sequence, for text-type conditional values, the real conditional values under the conditional column are used as candidate conditional values. For numeric conditional values, according to regular expressions and extraction rules, valid numeric expressions are obtained from natural language query statements and then standardized as candidate values shared by the current conditional column. It is then determined whether each candidate conditional value is a real conditional value, so that the real column value of the database can be filled into the SQL statement template, avoiding the situation where the query results cannot be obtained due to inconsistent expressions.
[0062] Furthermore, the objective functions in the SQL multi-task classification prediction model and the W-VALUE conditional value prediction model include and ;
[0063]
[0064]
[0065] in, is a weighted loss function for predicting selected and conditional columns, is the weighted loss function for predicting conditional values, and are the total losses of the true labels; is the classification prediction probability of the column name by the SQL multi-task classification prediction model. Represents the SQL multi-task classification prediction model for i The classification prediction probability of the column names; is the predicted probability of the candidate conditional value on the conditional column by the W-VALUE conditional value prediction model. The W-VALUE conditional value prediction model is the first j The predicted probability of the candidate conditional values; Represents the true label value, which can be 0 or 1. and Respectively represent i The column name and the j The true labels of candidate conditional values; α and β These are hyperparameters used to balance positive and negative samples.
[0066] The present invention discloses a method for generating a SQL statement model from a natural language, which has the following beneficial effects:
[0067] 1. The SQL multi-task classification model of the present invention adopts a method based on SQL templates to splice natural language query statements with column name information, merge subtasks with strong dependencies, directly predict and classify each column name in the data table, and judge whether each column name is a selected column, a conditional column, or a non-selected non-conditional column. At the same time, it derives the aggregation operation of the selected column and the conditional operator of the conditional column, merges two subtasks with strong dependencies, and thus simplifies downstream tasks, which helps the NL2SQL model to more effectively handle complex SQL statement generation.
[0068] 2. The W-VALUE conditional value prediction model of the present invention is used to predict the real conditional value. Taking into account the inconsistency of Chinese expressions, different types of conditional values in the text are designed and processed differently. When constructing the input sequence, for text-type conditional values, the real conditional value under the conditional column is used as the candidate conditional value. For numerical conditional values, according to regular expressions and extraction rules, valid digital expressions are obtained from natural language query statements and then standardized as candidate values shared by the current conditional column. It is then determined whether each candidate conditional value is a real conditional value, so that the real column value of the database can be filled into the SQL statement template, avoiding the situation where the result cannot be queried due to inconsistent expressions. BRIEF DESCRIPTION OF THE DRAWINGS
[0069] Figure 1 Flowchart of a method for generating a SQL statement model for a natural language
[0070] Figure 2 Six subtask schematics for existing SQLNet, SQLova and X-SQL
[0071] Figure 3 The overall structure of the SQL statement generation model for natural language
[0072] Figure 4 Schematic diagram of the SQL multi-task classification model
[0073] Figure 5 Schematic diagram of the WHERE conditional value prediction model DETAILED DESCRIPTION
[0074] The specific implementation modes of the present invention are described below so that those skilled in the art can understand the present invention. However, it should be clear that the present invention is not limited to the scope of the specific implementation modes. For those of ordinary skill in the art, as long as various changes are within the spirit and scope of the present invention as defined and determined by the attached claims, these changes are obvious, and all inventions and creations utilizing the concept of the present invention are protected.
[0075] Example 1
[0076] refer to Figure 1 This embodiment provides a method for generating a SQL statement model from a natural language, including the steps of:
[0077] S1. Construct an input sequence of natural language query statements and data table column names.
[0078] S2. Input the input sequence into the SQL multi-task classification prediction model: wherein the SQL multi-task classification prediction model includes a semantic feature encoding layer, a list enhancement layer and three sub-task classification layers.
[0079] S3, use the semantic feature encoding layer to obtain the global feature vector of the input sequence .
[0080] S4, using the list-based enhancement layer to transform the global feature vector Enhance the column name feature vector of the data table through attention weighting. Use attention weighting to enhance the SQL multi-task classification prediction model's ability to process column names, so as to obtain a more semantic column name feature vector.
[0081] S5. Add the enhanced column name feature vector and global information feature vector to By inputting into the three sub-task classification layers, the three sub-task classification layers predict each column name of the data table and obtain the aggregation operation of the selected column, the conditional operator of the conditional column, and the logical relationship of the conditional column. The use of a multi-task classification framework improves the overall performance of the model and the accuracy of the segmented tasks.
[0082] Specifically, refer to Figure 3 , input the natural language query statement and data table column name into the SQL multi-task classification prediction model and W-VALUE conditional value prediction model. Figure 4 The SQL multi-task classification prediction model directly predicts and classifies each column name of the data table, determines whether each column name is a selected column, a conditional column, or a non-selected non-conditional column, and obtains the aggregation operation of the selected column and the conditional operator of the conditional column; compared with the existing technology that first predicts the selected column and then predicts the aggregation function operation on it, or first predicts the conditional column and then predicts its conditional operator, this solution merges two subtasks with strong dependencies, thereby simplifying downstream tasks, which helps the NL2SQL model to more effectively handle complex SQL statement generation.
[0083] S6. Input all candidate conditional values in the input sequence and conditional column into the W-VALUE conditional value prediction model to obtain the true conditional value. Figure 5 , the W-VALUE conditional value prediction module is used to obtain the true conditional value.
[0084] S7. Fill the aggregation operation of the selected column, the conditional operator of the conditional column, the logical relationship of the conditional column and the real conditional value into the SQL statement template and output the SQL statement.
[0085] Specifically, the template of the SQL statement includes two parts: SELECT and WHERE, which are:
[0086] SELECT ($AGG $COLUMN) *
[0087] WHERE ($CONN_OP($COLUMN $WOP $VALUE) *
[0088] Since the SQL statement in this embodiment is for a single-table query scenario, there is no need to predict the table name, so the template does not contain a FROM clause. Among them, SELECT and WHERE are reserved words in SQL statements, ( ) * Indicates that the expression in the brackets can be repeated multiple times. The variables marked with $ all need to be predicted and generated. The meanings of each variable are as follows:
[0089] (1) In the SELECT clause, COLUMN represents the column to be queried, hereinafter referred to as the selected column. S-COL ;
[0090] (2) AGG represents the aggregate function operation on the selected column, denoted as S-AGG ;
[0091] (3) CONN_OP represents the logical relationship between the various conditional expressions, connecting the various conditions, recorded as CONN_OP ;
[0092] (3) In the WHERE clause, COLUMN represents the column of the screening condition, hereinafter referred to as the condition column. W-COL ;
[0093] (4) VALUE represents the condition value matched by the condition column, denoted as W-VALUE ;
[0094] (5) WOP represents the matching operator between the condition column and the condition value, denoted by W-OP .
[0095] The existing technology decomposes the task of a complete SQL statement into six subtasks: S-COL (selected columns), S- AGG (aggregate function for selected columns), W-NUM (number of conditional columns), W-COL (condition column), W-OP (conditional operator), W-VAL (Conditional value).
[0096] This embodiment will S-COL and S-AGG Two tasks, W-COL and W-OP Two tasks, merged into S- COL-AGG , W-COL-OP deal with.
[0097] Specifically, in this embodiment S-COL-AGG The classification model combines the two tasks of S-COL and S-AGG into a seven-classification problem, namely S-COL-AGG Directly predict which aggregate function the column contains on each column. If there is an aggregate function on this column, it means that this column is both a selected column and has this aggregate operation; if this column is not a selected column (naturally there is no aggregate function), S-AGG A new label "NO_OP" is added to the value space to indicate this situation; if this column is just a selected column and there is no aggregate function on the column, we use ' 'no function to indicate it. S-COL-AGGThe meaning of each value in is shown in Table 1 below:
[0098] Table 1 S-COL-AGG The meaning of each value in
[0099]
[0100] In this embodiment, W-COL-OP The classification model combines the two tasks of W-COL and W-OP into a five-classification problem, namely W-COL-OP Directly predict which conditional operator the column matches on each column. If the column matches a conditional operator, it means that the column is both a conditional column and also matches the conditional operator; if the column is not a conditional column, W-OP A new tag "NO_OP" is added to the value space to indicate this situation.
[0101] W-COL-OP The meaning of each value in the classification model is shown in Table 2:
[0102] Table 2 W-COL-OP The meaning of each value in the classification model
[0103]
[0104] After the above two tasks are merged, in addition to the W-VALUE condition value and COLMUN column name, the value ranges of each variable in the SQL template are summarized as a data dictionary, as shown below:
[0105] CONN-OP = {0: ' ', 1: AND, 2: OR}
[0106] S-COL-AGG= {0: ' ', 1: AVG, 2: MAX, 3: MIN, 4: COUNT, 5:SUM, 6: NO_OP}
[0107] W-COL-OP = {0: , 1:>, 2:<, 3: =, 4: !=, 5: NO_OP}
[0108] Specifically, the data table of this embodiment is shown in Table 3:
[0109] Table 3 Data table
[0110]
[0111] Natural language query : Comparing the two shows, Super Vocal and Golden Tuner, which one has higher ratings?
[0112] Output SQL: SELECT MAX (ratings) WHERE name = 'Super Vocal' OR name = 'Gold Medal Mediation'
[0113] This embodiment S-COL-AGG Classification models incorporate S-COL and S-AGG Table 4 shows an example of two tasks and one SELECT clause:
[0114] Table 4 SELECT clause representation example
[0115]
[0116] The W-COL-OP classification model in this embodiment combines the two tasks of W-COL and W-OP. An example of a WHERE clause is shown in Table 5:
[0117] Table 5 Examples of WHERE clause expressions
[0118]
[0119] After the tasks are merged, an example of a complete SQL statement is shown in Table 6:
[0120] Table 6 Example of a complete SQL statement
[0121]
[0122] By merging tasks, the original six tasks in SQLNet, SQLova and X-SQL models are now simplified to four tasks: S-COL-AGG, CONN-OP, W-COL-OP and W-VAULE. The meaning of each task is as follows:
[0123] 1) Whether there is an aggregation operation on the S-COL-AGG prediction column, that is, the values of the S-COL and S-AGG variables in the prediction template;
[0124] 2) Whether a conditional operator is matched on the W-COL-OP prediction column, that is, the values of the W-COL and W-OP variables in the prediction template;
[0125] 3) The logical relationship between the CONN-OP prediction screening conditions, that is, the value of the CONN_OP variable in the prediction template;
[0126] 4)W-VAULE predicts the conditional value matched by the conditional column, that is, the value of the VALUE variable in the prediction template.
[0127] As a further solution of this embodiment, the semantic feature encoding layer is a pre-trained model BERT, the language of the natural language query statement is Chinese, and the natural language query statement ;Data sheet ; { } { }.
[0128] Input of the pre-trained model BERT:
[0129]
[0130] ;
[0131] in, Represents input; Represents a splicing operation; It is a classifier, added at the beginning of the entire sequence, used to learn all the semantic information of the entire sequence; It is a separator, added at the end of the Q sentence, between each column name, and at the end of the entire input sequence; and They represent the first, second, i-th and n-th characters of Q respectively; t represents the number of column names in the data table; , , and Respectively represent the 1st, 2nd, jth and mth column names in the data table; each column name consists of k word vectors, { }, { }, and Respectively The first, second and kth word vectors in , and Respectively The first, second and kth word vectors in .
[0132] Output of the pre-trained model BERT:
[0133]
[0134]
[0135] Indicates output; for The hidden layer vector of , after being learned by the bidirectional Transformer encoder, integrates the information of the entire input sequence and is the global feature vector; , and Represent the hidden vectors of the 1st, 2nd and nth characters in Q respectively; Used to separate each different subsequence; and Respectively represent the hidden vectors of the first and second characters in the first column name of the data table; and They represent the hidden vectors of the first and second characters in the mth column name of the data table respectively.
[0136] As a further solution of this embodiment, the attention matrix in the list-based enhancement layer for:
[0137]
[0138]
[0139]
[0140] Among them, softmax represents the normalized exponential function; U and V are two learnable parameter matrices introduced 、V , respectively and Do a linear transformation and get the matrix , Represents the i-th column name consisting of k characters; ; , express The eigenvector of represents a matrix with d rows and 1 column, represents a matrix with d rows and d columns, represents a matrix with k rows and d columns.
[0141] As a further solution of this embodiment, the three subtask classification layers include CONN-OP classification model; S-COL-AGG classification model and W-COL-OP classification model;
[0142] The CONN-OP classification model is used to predict the logical relationship of the conditional column. It is a three-classification model with a value range of {0: ' ', 1: 'AND ', 2: 'OR '}.
[0143] The probability model of the CONN-OP classification model is:
[0144] Represents the classification probability distribution of CONN-OP output; are parameters that can be learned during training, represents a matrix with 3 rows and d columns.
[0145] The S-COL-AGG classification model predicts both the selected columns and the aggregate functions of the selected columns by directly predicting the aggregate operations on each column; the value range is {0: ' ', 1: 'AVG', 2: 'MAX', 3: 'MIN', 4: 'COUNT', 5: 'SUM', 6: 'NO_OP'}.
[0146] The probability model of the S-COL-AGG classification model is:
[0147] in, Represents the output probability distribution of the i-th column of the data table in S-COL-AGG, that is, predicting whether the i-th column is selected and whether it contains aggregation operations; is the feature vector of the i-th column of the data table after weighting by the attention mechanism, are parameters that can be learned during training, represents a matrix with 7 rows and d columns.
[0148] The W-COL-OP classification model predicts both the conditional column and the conditional operator of the conditional column by directly predicting the conditional operator on each column, with a value range of {0: '>', 1: '<', 2: '= ', 3: '!= ', 4: 'NO_OP '}.
[0149] The probability model of the W-COL-OP classification model is:
[0150] in, Represents the output probability distribution of the i-th column of the data table in the W-COL-OP task, that is, predicting whether the i-th column is used as a conditional column and matches the corresponding conditional operator; is the output vector of the i-th column of S-COL-AGG, represents the concatenation operation. The dimension after concatenation is (768+7). are parameters that can be learned during training, represents a matrix with 5 rows and d columns.
[0151] Specifically, the method for obtaining the true conditional value by the W-VALUE conditional value prediction model includes the following steps:
[0152] B1. Input the natural language query statement, condition column and all condition candidate values on the condition column into the encoding layer to obtain the global feature vector .
[0153] B2. Use the fully connected layer to construct a binary classification conditional value classification layer. The binary classification conditional value classification layer is constructed through the global feature vector Perform binary classification prediction on each candidate conditional value to obtain the true conditional value.
[0154] Among them, the construction of the input sequence of the Chinese pre-training model BERT includes the following steps:
[0155] B11. Determine the candidate condition value type on the condition column. If the candidate condition value is a numeric type, use regular expressions to extract all numeric expressions in the natural language query statement and standardize all numeric expressions in the natural language query statement into numeric types. Otherwise, proceed to B12.
[0156] B12. Generate an input sequence. The composition order of the input sequence is natural query statement, condition column, Chinese condition operator and candidate condition value.
[0157] Examples of standardized results of different types of digital representations are shown in Table 7:
[0158] Table 7 Examples of standardized results for different types of digital representations
[0159]
[0160] In this solution, different types of conditional values in the text are designed and processed differently, taking into account the inconsistency of Chinese expressions. When constructing the input sequence, for text-type conditional values, the real conditional values under the conditional column are used as candidate conditional values. For numeric conditional values, according to regular expressions and extraction rules, valid numeric expressions are obtained from natural language query statements and then standardized as candidate values shared by the current conditional column. It is then determined whether each candidate conditional value is a real conditional value, so that the real column value of the database can be filled into the SQL statement template, avoiding the situation where the query results cannot be obtained due to inconsistent expressions.
[0161] As a further solution of this embodiment, the encoding layer of step B1 is the Chinese pre-trained model BERT, and the natural language query statement , data table i The column name is , data table i The first i The candidate condition value is ;
[0162] The input of the Chinese pre-trained model BERT is:
[0163]
[0164]
[0165] The output of the Chinese pre-trained model BERT is:
[0166]
[0167]
[0168] in, represents the input sequence; Represents a splicing operation; and Respectively Q 1st, 2nd and i characters; and Respectively represent i The first, second, and j characters; is a separator, added in Q Separate at the end of a sentence, between each column name, and at the end of the entire input sequence; A Chinese character vector representing a conditional operator; and Respectively represent i The first i The first, second, and third k characters.
[0169] represents the output sequence; Represents the global feature vector of the output sequence, which integrates the information of the entire input sequence; , and Respectively Q 1st, 2nd and i The hidden layer vector of characters; Used to separate each different subsequence; and Respectively represent i The first, second, and j hidden layer vectors; express The hidden vector of , and Respectively represent the conditional values No. o Character hidden vector.
[0170] As a further solution of this embodiment, the probability model of the binary conditional value classification layer is:
[0171]
[0172] Indicates i The first i The probability of a candidate conditional value being the true conditional value, , are parameters that can be learned during training, Represents a system with 2 rows and d Matrix of columns.
[0173] Furthermore, the objective functions in the SQL multi-task classification prediction model and the W-VALUE conditional value prediction model include and ;
[0174]
[0175]
[0176] in, is a weighted loss function for predicting selected and conditional columns, is the weighted loss function for predicting conditional values, and are the total losses of the true labels; is the classification prediction probability of the column name by the SQL multi-task classification prediction model. Represents the SQL multi-task classification prediction model for i The classification prediction probability of the column names; is the predicted probability of the candidate conditional value on the conditional column by the W-VALUE conditional value prediction model. The W-VALUE conditional value prediction model is the first j The predicted probability of each candidate condition value; Represents the true label value, which can be 0 or 1. and Respectively represent i The column name and the j The true labels of candidate conditional values; α and β are all hyperparameters used to balance positive and negative samples. According to the empirical value of the ratio of positive and negative samples, in this embodiment, α =3, β =5.
[0177] Example 2
[0178] This embodiment further provides a specific experimental example based on the method of generating SQL statement models from natural language in Embodiment 1.
[0179] The dataset of this embodiment is a Chinese NL2SQL dataset, which comes from the dataset provided by the first Chinese NL2SQL Challenge held by "Zhuiyi Technology" in 2019. This dataset is also the first large-scale NL2SQL dataset published in the Chinese field. It uses tabular data in the financial and general fields as data sources, and provides labeled matching data pairs of Chinese natural language query statements and SQL statements annotated on this basis.
[0180] The differences between the Chinese NL2SQL dataset and the English NL2SQL dataset are summarized in Table 8.
[0181] Table 8 Comparison of Chinese and English NL2SQL datasets
[0182]
[0183] The Chinese NL2SQL dataset is more difficult than the English WikiSQL dataset in terms of SQL syntax complexity. The Chinese NL2SQL dataset contains more samples with multiple conditions. Multiple columns may be selected in the SELECT clause, and aggregate functions on columns are supported. The number of conditions in the WHERE clause is mainly multiple. SQL conditional values cannot be directly extracted from Chinese query statements. Because of the flexibility and diversity of Chinese spoken expressions, there are often multiple expressions for one thing. Therefore, the SQL conditional value storage value in the dataset is often different from the description of the conditional value in the Chinese statement. Therefore, for numeric conditional values, this embodiment obtains valid digital expressions from natural language query statements based on regular expressions and extraction rules, and then standardizes them as candidate values shared by the current conditional column, and then determines whether each candidate conditional value is a real conditional value, so that the real column value of the database can be filled into the SQL statement template, avoiding the situation where the result cannot be queried due to inconsistent expressions.
[0184] The hardware and experimental environment configuration of the server in this embodiment are shown in Table 9 below:
[0185] Table 9 Server hardware and experimental environment configuration
[0186]
[0187] For the SQL multi-task classification model, in order to establish the relationship between multiple subtasks, improve the multi-task learning ability, and learn the semantic expression under multiple tasks, the training cycle is 30 times. For the WHERE condition column classification model, it is a relatively simple binary classification task, so it does not need too many training cycles, and the number of iterations is set to 5 times.
[0188] The comparison models in this embodiment use Seq2Seq, Seq2Tree, Seq2SQL, SQLNet, BERT-based SQLNet, and SQLova.
[0189] In the SQL multi-task classification model, the filling values of the five position variables S-COL, S-AGG, CONN-OP, W-COL, and W-OP in the SQL template are mainly predicted. On the validation set, the accuracy of the prediction results of each model for each subtask label is shown in Table 10.
[0190] Table 10 Accuracy of prediction results of each subtask
[0191]
[0192] The Chinese NL2SQL dataset contains multiple selected columns and conditional columns. The comparison models SQLNet and SQLova based on SQL templates need to be added or modified. S-NUM , W-NUM The prediction of the entire SQL statement can only be completed by performing the task. However, the SQL multi-task classification model proposed in this embodiment predicts the selected column and the conditional column by making predictions on each column, and does not need to perform these two subtasks. Therefore, the table only focuses on the accuracy of each subtask label that can be filled in the final SQL template.
[0193] The comparative experimental results in the table show that the SQL multi-task classification model of this embodiment has achieved the best accuracy in the prediction results of each task, verifying the effectiveness of the model design in this paper. The following is an analysis of the effects of each model based on the experimental results.
[0194] (1) Seq2Seq and Seq2Tree models are based on the sequence generation principle of encoder-decoder. The experimental results show poor accuracy on each label. The main reason is that neither of them takes into account the structure and grammatical rules of SQL statements, treats the NL2SQL problem as a text generation problem, and does not limit the output length of SQL statements. This proves the importance of considering the structure of SQL statements in the NL2SQL task.
[0195] (2) Seq2SQL takes the query statement concatenated with the column name as input, generates SQL statements in the union space of the table header, question, and SQL keywords, uses the Pointer network to obtain the conditional value, and uses the SQL execution result as a reward to train the reinforcement learning algorithm. Before the SQL template-based method, it had the best accuracy in each label. However, it did not rely on SQL templates and did not fully consider SQL syntax rules, resulting in a large output space. In essence, it is still the Seq2Seq method.
[0196] (3) The SQLNet model was originally unable to process the Chinese NL2SQL dataset because it assumed that there was only one selected column, so we added S-NUM Predict the number of selected columns, and then predict the number of columns in the column name set S-NUM SQLNet proposed a method based on SQL templates to decouple the NL2SQL task into multiple subtasks. Compared with Seq2SQL, this model has improvements in all labels, which verifies that the SQL template-based filling method is more reasonable than the sequence generation method.
[0197] (4) SQLNet based on BERT modifies the Bi-LSTM in the original model into a BERT pre-trained model to encode text sequences. Compared with SQLNet, the accuracy of this model on each SQL tag has been improved. The pre-trained model BERT uses the Transformer encoder as a text feature extractor, which captures contextual information more fully than the unidirectional iterative RNN, thus verifying the effectiveness of the pre-trained model.
[0198] (5) SQLova follows the template-based filling method of SQLNet, selects the pre-trained model BERT as the encoding layer, and uses the natural language query statement combined with the table column name information as the input of the model. However, the difference is that SQLNet adds elements by element when combining the table column name information, while SQLova uses a sequence splicing method and uses different SegmentationEmbedding to distinguish query statements and column names. SQLova has a higher accuracy rate in each label than SQLNet. It can be seen that the column name sequence splicing method has a certain promoting effect on the prediction results of each label.
[0199] (6) The SQL multi-task classification model of this embodiment adopts a method based on SQL templates to concatenate Chinese natural language query statements with column name information, and selects the pre-trained model BERT as the input encoding layer. The model in this paper designs a more reasonable downstream model structure, merges subtasks with strong dependencies, selects columns and their aggregation functions into one task, and combines condition columns and matching operators on columns into one task. And use S-COL-AGG The output of W-COL-OP This allows us to omit the input features of S-NUM and W-NUM These two tasks are non-text label tasks that calculate specific values. The pre-trained model BERT does not bring more improvement to the calculation of specific values.
[23] The SQL multi-task classification model of this embodiment has improved performance in the prediction results of selected columns, conditional columns and other tasks, indicating that the model design of the joint prediction method is effective.
[0200] The W-VALUE conditional value prediction model uses the real conditional columns and matching operators when training each model, and uses the conditional columns and matching operators output by the SQL multi-task classification model when predicting. The prediction accuracy of each model for different types of conditional values is shown in Table 11.
[0201] Table 11 Accuracy of conditional value prediction results
[0202]
[0203] REAL indicates the prediction accuracy on text type condition values, and TEXT indicates the prediction accuracy on numeric type. If this SQL statement does not contain a condition value of a certain type, the condition value prediction on the corresponding type is assumed to be correct. ALL indicates the prediction accuracy of the data on both types of condition values.
[0204] (1) The Seq2Seq and Seq2Tree models use sequence generation to obtain conditional values in the WHRER clause. This method has a poor prediction effect on conditional values, and the conditional values generated by the sequence are relatively arbitrary and have no restrictions on the actual column values.
[0205] (2) Seq2SQL, SQLNet, BERT-based SQLNet, and SQLova models all use Pointer networks to obtain WHERE condition values. This method directly obtains condition values from Chinese query statements through sequence annotation in the text, so it cannot guarantee that the same condition value is consistent in the description of the query statement and the expression of the column value stored in the data table.
[0206] (3) Seq2SQL performs worse than the other three models, largely due to its poor prediction of the conditional column in the previous model. The accuracy of the TEXT type conditional value in SQLNet+BERT and SQLova is better than that of the REAL type. This can be considered that the Chinese text pre-training model has a better positive effect on Chinese text than the digital type.
[0207] (4) The W-VALUE conditional value prediction model performs different processing for the numeric REAL type and the text type TEXT type, and proposes solutions for the inconsistent conditional value expression, so it achieves the best results in all three indicators. The text type uses the real column value as the candidate conditional value, while the numeric type needs to extract the numeric expression from the query statement and standardize it as the candidate value. The generated conditional values are all from the real conditional column value, not the subsequence in the query statement, avoiding the problem of inconsistent expression. Therefore, the accuracy of conditional value prediction is significantly improved.
[0208] We selected relatively better models, including SQLNet, SQLNet based on BERT, SQLova and W-VALUE conditional value prediction model. On the same validation set, the prediction results of two samples with inconsistent expressions of the same conditional value are summarized as shown in Table 12, where SQLNet+ BERT represents SQLNet based on BERT.
[0209] Table 12 Prediction results of two samples with inconsistent expressions of the same condition value
[0210]
[0211] in, and Chinese query sentence samples containing TEXT-type conditional values and REAL-type conditional values. As can be seen from the above figure, since the comparison model uses a Pointer network, it cannot handle inconsistent expressions and can only extract similar conditional values such as "Hunan TV", 1, or 11 from the annotations. The model of this embodiment has a better effect on the prediction of conditional values because the W-VALUE conditional value prediction model has different treatments for numerical values and text types, and uses candidate conditional values as the real conditional value screening range.
[0212] The complete SQL statement generation in the single-table query scenario is generated by filling the SQL template with the prediction results of the two models. The real keywords corresponding to the prediction results of the SQL multi-task classification model are filled in the selected columns, aggregate functions, condition columns, condition matching operators, and logical connectors between conditions according to the SQL template; then the predicted condition value results of the W-VALUE condition value prediction model are connected to the corresponding column operator according to the condition column, and a complete SQL statement is obtained.
[0213] This embodiment uses the two indicators of logical form accuracy (LX) and execution accuracy (EX) to measure the generation quality of complete SQL statements. The experimental results of each model are shown in Table 13.
[0214] Table 13 Experimental results of complete SQL of each model
[0215]
[0216] As can be seen from Table 12, each model has an execution accuracy of The accuracy is better than the logical form The main reason is that the evaluation criteria of the two accuracy rates are different. The logic accuracy rate focuses on whether the variables in each position in the entire SQL statement are accurately predicted, that is, whether the SQL expression is completely consistent. The execution accuracy rate focuses on whether the execution result of the statement is accurately predicted, that is, whether the execution result of the generated SQL statement is consistent with the execution result of the real SQL statement. Since different SQL statements may query the same data record results, the execution accuracy rate is generally higher.
[0217] Compared with SQLNet, SQLNet+BERT, SQLova and the model proposed in this paper using pre-trained models have greatly improved the two accuracy indicators, indicating that the use of Chinese natural language pre-training models can indeed bring performance improvements to the experimental results. The SQL statement model of this embodiment ensures the syntax and structure of SQL statements. It uses a SQL template-based method to decouple the generation task of SQL statements into a classification task of variables at each position in the template. The predicted subtask labels are then filled into complete SQL statements through the template. At the same time, the fusion relationship between subtasks is also taken into account. The subtasks containing corresponding relationships are combined into one task for processing, and the output of one task is integrated into the input of another task to help the task training and improve the accuracy. In addition, there is also a special treatment for the problem of inconsistent conditional value expressions. Therefore, the SQL statement model of this embodiment has good results in both indicators in the Chinese NL2SQL task, thereby proving the effectiveness of the model.
[0218] Although the specific implementation of the invention is described in detail in conjunction with the drawings, it should not be understood as limiting the scope of protection of this patent. Within the scope described in the claims, various modifications and variations that can be made by those skilled in the art without creative work still fall within the scope of protection of this patent.
Claims
1. A method for generating a SQL statement model from a natural language, characterized in that: Includes steps: S1. construct an input sequence of natural language query statements and data table column names; S2. Input the input sequence into the SQL multi-task classification prediction model: wherein the SQL multi-task classification prediction model includes a semantic feature encoding layer, a list enhancement layer and three sub-task classification layers; S3, use the semantic feature encoding layer to obtain the global feature vector of the input sequence ; S4, using the list-based enhancement layer to transform the global feature vector Enhance the column name feature vector of the data table through attention weighting; S5. Add the enhanced column name feature vector and global information feature vector to By inputting into the three subtask classification layers, the three subtask classification layers predict each column name of the data table and obtain the aggregation operation of the selected column, the conditional operator of the conditional column, and the logical relationship of the conditional column respectively; The three subtask classification layers include CONN-OP Classification models; S-COL-AGG Classification models and W-COL-OP Classification models; CONN-OP The classification model is used to predict the logical relationship of the conditional columns, which is a three-classification model; CONN-OP The probability model of the classification model is: express CONN-OP Output classification probability distribution; are parameters that can be learned during training, It means that there are 3 rows and d A matrix of columns; S-COL-AGG The classification model predicts the selected columns and the aggregation functions of the selected columns by directly predicting the aggregation operations on each column. This is a seven-classification model. S-COL-AGG The probability model of the classification model is: in, Indicates data table i Listed in S-COL-AGG The output probability distribution of i Whether the column is selected and contains aggregation operations; This is the data sheet i The feature vectors weighted by the attention mechanism are: are parameters that can be learned during training, It means that there are 7 rows and d A matrix of columns; W-COL-OP The classification model predicts both the conditional column and the conditional operator of the conditional column by directly predicting the conditional operator on each column. This is a five-class classification model. W-COL-OP The probability model of the classification model is: in, Indicates data table i Listed in W-COL-OP The output probability distribution of the task, that is, predicting i Whether the column is used as a conditional column and matches the corresponding conditional operator; yes S-COL-AGG No. i The output vector of columns, Indicates the concatenation operation. The dimension after concatenation is 768+7. are parameters that can be learned during training, It means that there are 5 rows and d A matrix of columns; S6. Input all candidate conditional values in the input sequence and condition column into the W-VALUE conditional value prediction model to obtain the true conditional value; The method for obtaining the real conditional value by the W-VALUE conditional value prediction model includes the following steps: B1. Input the natural language query statement, condition column and all condition candidate values on the condition column into the encoding layer to obtain the global feature vector ; The encoding layer of step B1 is the Chinese pre-trained model BERT, and the natural language query statement , data table i The column name is , data table i The first i The candidate condition value is ; The input of the Chinese pre-trained model BERT is: The output of the Chinese pre-trained model BERT is: in, represents the input sequence; Represents a splicing operation; and Respectively Q 1st, 2nd and i characters; and Respectively represent i The first, second, and j characters; is a separator, added in Q Separate at the end of a sentence, between each column name, and at the end of the entire input sequence; A Chinese character vector representing a conditional operator; and Respectively represent i The first i The first, second, and third k characters; represents the output sequence; Represents the global feature vector of the output sequence, which integrates the information of the entire input sequence; , and Respectively Q 1st, 2nd and i The hidden layer vector of characters; Used to separate each different subsequence; and Respectively represent i The first, second, and j hidden layer vectors; express The hidden vector of , and Respectively represent the conditional values No. o Character hidden vector; B2. Use the fully connected layer to construct a binary classification conditional value classification layer. The binary classification conditional value classification layer is constructed through the global feature vector Perform binary classification prediction on each candidate condition value to obtain the true condition value; S7. Fill the aggregation operation of the selected column, the conditional operator of the conditional column, the logical relationship of the conditional column and the real conditional value into the SQL statement template and output the SQL statement.
2. The method for generating SQL statement model from natural language according to claim 1, characterized in that: The semantic feature encoding layer is the pre-trained model BERT, the language of the natural language query is Chinese, and the natural language query is ; Data Sheet ; { } { }； Input of the pre-trained model BERT: ； Output of the pre-trained model BERT: in, Represents input; Represents a splicing operation; It is a classifier, added at the beginning of the entire sequence, used to learn all the semantic information of the entire sequence; is a separator, added in Q Separate at the end of a sentence, between each column name, and at the end of the entire input sequence; and Respectively Q 1st, 2nd, i and n characters; t Indicates the number of column names in the data table; , , and Respectively represent the 1st, 2nd, and j and m column names; each column name consists of k word vectors, { }, { }, and Respectively 1st, 2nd and k word vectors, and Respectively 1st, 2nd and k word vectors; Indicates output; for The hidden layer vector of , after being learned by the bidirectional Transformer encoder, integrates the information of the entire input sequence and is the global feature vector; , and Respectively Q 1st, 2nd and n The hidden vector of the characters; Used to separate each different subsequence; and Respectively represent the hidden vectors of the first and second characters in the first column name of the data table; and Respectively represent the data table m The hidden vectors of the first and second characters in the column names.
3. The method for generating SQL statement model from natural language according to claim 2, characterized in that: Attention matrix in list-based enhancement layer for: in, softmax represents the normalized exponential function; U and V are two learnable parameter matrices introduced 、V , respectively and Do a linear transformation and get the matrix , Indicated by k The characters consist of i column names; ; , express The eigenvector of Indicates that d A matrix of 1 row and 1 column, Indicates that d Line and d A matrix of columns, Indicates that k Line and d Matrix of columns.
4. The method for generating SQL statement model from natural language according to claim 1, characterized in that: The probability model of the binary conditional value classification layer is: Indicates i The first i The probability of a candidate conditional value being the true conditional value, , are parameters that can be learned during training, Represents a system with 2 rows and d Matrix of columns.
5. The method for generating SQL statement model from natural language according to claim 1, characterized in that: The construction of the Chinese pre-training model BERT for the input sequence includes the following steps: B11. Determine the candidate condition value type on the condition column. If the candidate condition value is a numeric type, use regular expressions to extract all numeric expressions in the natural language query statement and standardize all numeric expressions in the natural language query statement into numeric types. Otherwise, proceed to B12. B12. Generate an input sequence. The composition order of the input sequence is natural query statement, condition column, Chinese condition operator and candidate condition value.
6. The method for generating SQL statement model from natural language according to claim 1, characterized in that: The objective functions in the SQL multi-task classification prediction model and the W-VALUE conditional value prediction model include and ; in, is a weighted loss function for predicting selected and conditional columns, is the weighted loss function for predicting conditional values, and are the total losses of the true labels; is the classification prediction probability of the column name by the SQL multi-task classification prediction model. Represents the SQL multi-task classification prediction model for i The classification prediction probability of the column names; is the predicted probability of the candidate conditional value on the conditional column by the W-VALUE conditional value prediction model. The W-VALUE conditional value prediction model is the first j The predicted probability of each candidate condition value; Represents the true label value, which can be 0 or 1. and Respectively represent i The column name and the j The true labels of candidate conditional values; α and β These are hyperparameters used to balance positive and negative samples.