Text-to-SQL (Structured Query Language) generation method based on adaptive knowledge distillation

Abstract

The invention discloses a Text-to-SQL (Structured Query Language) generation method based on adaptive knowledge distillation, which comprises the following steps of: firstly, generating a single-table SQL statement and a corresponding natural language query text based on target database metadata, and training a single-table SQL to natural language generation model; then defining a composite rule, combining the single-table data into a multi-table composite query SQL and a corresponding natural language text, decomposing the composite SQL to obtain sub-queries, generating a sub-query text, and training a multi-text to composite text generation model; then a generation-scoring-screening module is constructed, an optimal SQL statement is generated and screened in combination with a large language model and similarity calculation, so that training sample data is obtained, and a Text-to-SQL generation model based on Transform is trained. The method has the advantages of being high in sample data quality, capable of achieving composite query SQL statement generation, high in adaptability, good in flexibility and the like.

Description

Technical Field

[0001] The present invention belongs to the field of information retrieval, and particularly relates to a method for generating Text-to-SQL. Background Art

[0002] Text-to-SQL is a technology that automatically converts natural language query texts (such as Chinese, English, etc.) into structured query language (SQL), aiming to lower the threshold of database queries and enable non-professional users to interact with databases through intuitive natural language. This technology is widely used in fields such as intelligent data analysis, enterprise report generation, and database management tools, and can significantly improve the efficiency and usability of data queries.

[0003] Currently, the implementation of Text-to-SQL mainly has the following methods:

[0004] (1) Traditional template matching method: This method generates SQL statements by predefining SQL statement templates and corresponding natural language query templates and using rule matching and template filling. Although the generated SQL has no syntax errors, its generalization ability is poor. It not only requires strict input of natural language according to the template requirements, which is difficult to use, but also cannot handle query requirements outside the predefined templates. Moreover, when the data table structure changes, the template needs to be adjusted manually, resulting in high maintenance costs. This method has gradually been replaced by other methods.

[0005] (2) Deep learning method based on Seq2Seq: Regarding SQL as the target language, it realizes the conversion from natural language to SQL through a sequence-to-sequence (Seq2Seq) model (such as a machine translation framework). The drawback of this method is that a large amount of labeled data (natural language query text - SQL paired samples) is required for supervised training, and the model only optimizes token-level generation, making it easy to output SQL with syntax errors and requiring additional rule constraints, resulting in high training difficulty.

[0006] (3) The method based on large language model (LLM) uses techniques such as prompt engineering and vector retrieval to enhance the input of the LLM and directly generate SQL statements. This method also requires a large amount of labeled data, and a large amount of resources are needed for fine-tuning, with great difficulties and high costs, and insufficient adaptability.

[0007] (4) Reinforcement learning method: This method is mostly used for local optimization of methods such as Seq2Seq and LLM, that is, it cooperates with other methods to achieve certain automated learning. It not only has problems of low efficiency and difficult training, but also cannot fundamentally solve the problems of the need for a large amount of labeled data and insufficient adaptability existing in other methods.

[0008] To solve the problem that existing methods require a large amount of labeled data, those skilled in the art have also proposed a training method based on high-order large models and distillation technology. Its core idea is: Use a high-order large model (such as Deepseek V3, GPT4, Claude3.5) to generate a large number of "natural language query text - SQL statement" samples, and then use another high-order model to evaluate and score these samples, select the samples with higher evaluations, and then use these samples to train the target model. Although this method can solve the data problem, it also has the following defects: (1) At present, large language models all have similar technical architectures, and there is a problem of "mutual recognition" during evaluation, that is, it is difficult for one large model to see whether there are problems with the SQL generated by another large model, resulting in an unobjective evaluation. Moreover, the scoring criteria of the large model during each evaluation also have a certain degree of randomness, and the intermediate process cannot be intervened. Therefore, the evaluation by the large model has a problem of poor objectivity, and the selected samples may not be the optimal samples. (2) In a certain fixed application field, in order to save resources, the target model only needs to have the Text-to-SQL ability for a specific database. However, the existing method cannot provide dedicated training samples for a specific database, and a large number of samples need to be used for training, resulting in high costs and unsatisfactory final generation effects. (3) When facing complex query problems, the existing large models have a low accuracy rate in generating SQL statements and cannot guarantee the final generation effect. Summary of the Invention

[0009] The present invention proposes a Text-to-SQL generation method based on adaptive knowledge distillation, and its objectives are: (1) to solve the problem of insufficient objectivity and low data quality in the sample data screening process; (2) to solve the problem that the sample data cannot match the target database, resulting in poor adaptability and flexibility; (3) to solve the problem that the existing method cannot generate high-quality sample data for complex queries.

[0010] The technical solution of the present invention is as follows:

[0011] A Text-to-SQL generation method based on adaptive knowledge distillation, the steps include:

[0012] Step S1, obtain the metadata of the target database, and generate multiple groups of corresponding single-table query SQL statements X_Single_SQL and natural language single-table query texts Y_Single_Text based on the metadata;

[0013] Step S2: Construct a SingleSQL-to-SingleText generation model. The input of the SingleSQL-to-SingleText generation model is a single-table query SQL statement, and the output is a natural language single-table query text. Then, using the single-table query SQL statement X_Single_SQL obtained in Step S1 as a sample and the natural language single-table query text Y_Single_Text obtained in Step S1 as a label, train the SingleSQL-to-SingleText generation model;

[0014] Step S3: Define multiple groups of composite rules. The composite rules are used to combine two groups of single-table query SQL statements and the corresponding natural language single-table query texts into a composite query SQL statement and the corresponding natural language composite query text. Then, use the composite rules to combine the multiple groups of corresponding single-table query SQL statements X_Single_SQL and natural language single-table query texts Y_Single_Text obtained in Step S1 to obtain multiple groups of corresponding composite query SQL statements X_Complex_SQL and natural language composite query texts Y_Complex_Text;

[0015] Step S4: Construct a MutiSingleText-to-ComplexText generation model. The input of the MutiSingleText-to-ComplexText generation model is two or more natural language single-table query texts, and the output is a natural language composite query text. Decompose the composite query SQL statement X_Complex_SQL obtained in Step S3 through a statement decomposition module to obtain multiple sub-query SQL statements X_Sub_SQL. Then, for each sub-query SQL statement X_Sub_SQL, obtain the corresponding sub-query natural language text Y_Sub_Text through the SingleSQL-to-SingleText generation model. Use the obtained multiple sub-query natural language texts Y_Sub_Text as samples and the natural language composite query text Y_Complex_Text corresponding to the original composite query SQL statement X_Complex_SQL as a label to train the MutiSingleText-to-ComplexText generation model;

[0016] Step S5: Construct a generation-scoring-screening module, where the generation-scoring-screening module includes a reference large language model, a statement decomposition module, a SingleSQL-to-SingleText generation model, a MutiSingleText-to-ComplexText generation model, and a similarity calculation module, and is used to obtain multiple SQL statements based on the input natural language query text X_Input_Text and select the optimal expected SQL statement Y_Expect_SQL from them;

[0017] Step S6: Construct a Transformer-based Text-to-SQL generation model, input multiple groups of natural language query texts X_Input_Text into the generation-scoring-screening module to obtain the corresponding expected SQL statements Y_Expect_SQL, and then use the natural language query text X_Input_Text as a sample and the corresponding expected SQL statement Y_Expect_SQL as a label to train the Text-to-SQL generation model;

[0018] Step S7: Input the user's natural language query text into the trained Text-to-SQL generation model to obtain the corresponding SQL statement.

[0019] As a further improvement of the Text-to-SQL generation method based on adaptive knowledge distillation: In step S1, first obtain the table information of all data tables in the database and all field information in the data tables through the metadata of the target database; then, construct query conditions through the query condition template; then combine the field C, the data table T to which it belongs, and the query conditions V under the same data table, and then embed each group of <C, T, V> combinations and their corresponding natural language descriptions into the single-table query SQL statement template and the natural language single-table query text template to obtain the corresponding single-table query SQL statement X_Single_SQL and natural language single-table query text Y_Single_Text.

[0020] As a further improvement of the Text-to-SQL generation method based on adaptive knowledge distillation: The composite rule includes constraint conditions, a composite query SQL statement template, and a natural language composite query text template.

[0021] As a further improvement of the Text-to-SQL generation method based on adaptive knowledge distillation, the synthesis steps of step S3 are as follows:

[0022] Step S3-1: Combine the obtained multiple sets of corresponding "single-table query SQL statements X_Single_SQL and natural language single-table query texts Y_Single_Text" and a set of empty "single-table query SQL statements X_Single_SQL and natural language single-table query texts Y_Single_Text" in pairs; in each combination, one set of "single-table query SQL statements X_Single_SQL and natural language single-table query texts Y_Single_Text" is denoted as S_1, and the other set is denoted as S_2. S_1 must not be empty, and S_2 can be empty.

[0023] Step S3-2: Traverse all composite rules. For each composite rule, execute steps S3-2-1 to S3-2-3 respectively:

[0024] Step S3-2-1: Read a new set of S_1 and S_2; if all S_1 and S_2 have been traversed, end.

[0025] Parse the query fields, query data tables, and query conditions from S_1 and S_2 respectively. Denote the query fields, query data tables, and query conditions parsed from S_1 as C1, T1, V1 respectively, and denote the query fields, query data tables, and query conditions parsed from S_2 as C2, T2, V2 respectively.

[0026] Step S3-2-2: Use the constraint conditions in the current composite rule to determine whether C1, T1, V1 and C2, T2, V2 meet the synthesis conditions. If they meet, execute step S3-2-3; otherwise, jump to step S3-2-1.

[0027] Step S3-2-3: Embed C1, T1, V1, C2, T2, V2 into the composite query SQL statement template of the current composite rule to obtain the composite query SQL statement X_Complex_SQL. At the same time, embed the natural language texts corresponding to C1, T1, V1, C2, T2, V2 into the natural language composite query text template of the current composite rule to obtain the natural language composite query text Y_Complex_Text.

[0028] As a further improvement of the above Text-to-SQL generation method based on adaptive knowledge distillation: after obtaining the natural language composite query text through step S-2, call the BART model to rewrite and polish it, and then use the rewritten natural language composite query text as the final natural language composite query text.

[0029] As a further improvement of the Text-to-SQL generation method based on adaptive knowledge distillation: the statement decomposition module parses the compound query SQL statement to obtain an AST syntax tree structure, and then depth-first traverses all nodes in the AST syntax tree structure to obtain all sub-query SQL statements in order.

[0030] As a further improvement of the Text-to-SQL generation method based on adaptive knowledge distillation: when depth-first traversing all nodes in the AST syntax tree structure, for the current node, if its attribute is "SELECT" and it is not a root node, the corresponding "subquery SQL statement" is obtained according to the attribute information of the node.

[0031] As a further improvement of the Text-to-SQL generation method based on adaptive knowledge distillation: the input of the generation-scoring-screening module is the natural language query text, and the output is the expected SQL statement. Its working process is as follows:

[0032] Step S5-1, input the natural language query text X_Input_Text into the reference large language model, and generate multiple groups of corresponding SQL statements Y_SQL with the guidance of prompt words;

[0033] Step S5-2, perform syntax check on the generated SQL statement Y_SQL, and remove SQL statements with syntax errors;

[0034] Step S5-3: for each SQL statement Y_SQL, parse and convert it using the statement decomposition module to obtain corresponding multiple sub-query SQL statements, and then input each sub-query SQL statement into the SingleSQL-to-SingleText generation model to obtain the sub-query natural language text; at this time, each SQL statement Y_SQL corresponds to multiple sub-query natural language texts;

[0035] Step S5-4: for each SQL statement Y_SQL, input the corresponding multiple sub-query natural language texts into the MutiSingleText-to-ComplexText generation model to obtain the natural language compound query texts corresponding to the SQL statements Y_SQL; at this time, the natural language query text X_Input_Text input in step S5-1 corresponds to multiple SQL statements Y_SQL, and each SQL statement Y_SQL corresponds to a natural language compound query text;

[0036] Step S5-5: Use the similarity calculation module to calculate the similarity between the natural language query text X_Input_Text input in Step S5-1 and each natural language composite query text obtained in Step S5-4; if the highest similarity score exceeds the preset value, select the SQL statement Y_SQL corresponding to the highest similarity score as the expected SQL statement Y_Expect_SQL.

[0037] As a further improvement of the above Text-to-SQL generation method based on adaptive knowledge distillation, the calculation process of the similarity calculation module is as follows:

[0038] Let the texts for which similarity needs to be calculated be and , and the corresponding embedding vectors be:

[0039] ;

[0040] ;

[0041] and are respectively the -th element in the embedding vector of and the -th element in the embedding vector of

[0042] Define the cosine similarity between any two elements in and as:

[0043] ;

[0044] Then calculate the matching accuracy of the core content of and :

[0045] ;

[0046] Calculate the matching completeness of the core content of and :

[0047] ;

[0048] Finally, calculate the similarity score of and :

[0049] .

[0050] As a further improvement of the above Text-to-SQL generation method based on adaptive knowledge distillation: In step S6, directly use the reference large language model in step S5 as the Text-to-SQL generation model, and use the natural language query text X_Input_Text as the sample and the corresponding expected SQL statement Y_Expect_SQL as the label to fine-tune and train the reference large language model.

[0051] Compared with the prior art, the present invention has the following positive effects:

[0052] 1. The present invention uses a reference large language model to generate SQL statements, and then restores the SQL statements into natural language composite query texts through statement decomposition, SQL-Text transformation, and natural language text synthesis. Then, the optimal SQL statement is selected by calculating the similarity between the natural language composite query text and the original input natural language query text. Each link in this process is independently controllable, thus ensuring the objectivity of the screening process and improving the quality of sample data.

[0053] 2. The generation models in the present invention are all trained based on the metadata of the target database. Therefore, the obtained sample data is more suitable for actual applications, has stronger adaptability, and better flexibility.

[0054] 3. The present invention also proposes a composite rule and a MutiSingleText-to-ComplexText generation model. The former can merge more than one single-table query into a composite query, and the latter can synthesize multiple sub-query natural language texts into a natural language composite query text, establishing a comprehensive mapping relationship from multiple sub-queries to composite queries, thus laying a foundation for providing composite query sample data and enabling the final Text-to-SQL generation model to have the comprehensive ability of SQL generation for both single-table queries and composite queries. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] Figure 1 It is a schematic flow chart of the method of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0056] The technical solution of the present invention will be described in detail below with reference to the drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all of the embodiments.

[0057] A Text-to-SQL generation method based on adaptive knowledge distillation, the steps include:

[0058] Step S1: Obtain the metadata of the target database, and generate multiple groups of corresponding single-table query SQL statements X_Single_SQL and natural language single-table query texts Y_Single_Text based on the metadata.

[0059] Specifically, through the metadata of the target database, obtain the table information of all data tables in the database and all field information in the data tables. The table information and field information include, but are not limited to, the name of the table / field, relevant DDL information, etc.

[0060] Then, construct query conditions through the query condition template. Corresponding query condition templates are set for different field types. Field types are mainly divided into two categories: equal value and range. For fields of the equal value type (such as discrete type data like id, name, etc.), the query condition template is usually in the form of "c = 1", where c is the field name in the condition. For fields of the range type, they are usually datetime or int type data, and the query condition template can be in the form of "c > ’2025-01-01’". Moreover, a field can correspond to multiple query condition templates. For example, for a date field, it can be queried by equal value: "datatime = ’2025-01-01’", or it can be queried by range: "datatime > ’2025-01-01’ and datatime < ’2025-02-01’". An empty query condition can also be constructed, indicating that no query condition is set.

[0061] Then, combine the field C, the data table T to which it belongs, and the query condition V under the same data table, and then embed each group of <C, T, V> combinations and their corresponding natural language descriptions into the single-table query SQL statement template and the natural language single-table query text template to obtain the corresponding single-table query SQL statements X_Single_SQL and natural language single-table query texts Y_Single_Text.

[0062] The reference code for splicing by embedding the template is as follows:

[0063] def GenerateSQL(table=None, columns=None, value=None):

[0064] sql = f"SELECT" # SQL

[0065] desc = f"Query" # Natural language query

[0066] if table:

[0067] sql + = f" FROM {table}"

[0068] desc += f"{Covert(table)}"

[0069] if columns:

[0070] sql += f" {columns}"

[0071] desc += f" of the {Covert(table, columns)} data"

[0072] if condition:

[0073] sql += f" WHERE {value}"

[0074] desc += f", satisfying the {Covert(table, value)} condition"

[0075] return sql, desc

[0076] Among them, Covert is a string conversion function that maps the given table / field name to the natural language description of the database (from the DDL information of the database), and then embeds it into the corresponding string template (the template contains query logics such as "greater than", "equal to", etc.) to obtain the required text.

[0077] The following are examples of the single-table query SQL statement X_Single_SQL and the natural language single-table query text Y_Single_Text:

[0078] Table 1 - Examples of single-table query SQL statements and natural language single-table query texts.

[0079] Single-table query SQL statement Natural language single-table query text select p1r0 from test_table Query the power data of the test table select p1r0 from test_tablewhere datatime=t1 Query the power data of the test table, meeting the condition that the time is t1 select p2r0 from test_tablewhere datatime=t1 Query the power generation data of the test table, meeting the condition that the time is t1 select p1r0 from test_table where datatime>t1 and datatime<t2 Query the power data of the test table, meeting the condition that the time is greater than t1 and less than t2 … …

[0080] Although the natural language single-table query text generated here is relatively "mechanical", it already records the core information of the query and accurately matches the SQL statement. Therefore, no further processing is required.

[0081] Step S2: Build a SingleSQL-to-SingleText generation model. The input of the SingleSQL-to-SingleText generation model is the single-table query SQL statement, and the output is the natural language single-table query text. Then, using the single-table query SQL statement X_Single_SQL obtained in step S1 as a sample and the natural language single-table query text Y_Single_Text obtained in step S1 as a label, train the SingleSQL-to-SingleText generation model.

[0082] The SingleSQL-to-SingleText generation model is based on the Transformer architecture and includes an encoder and a decoder. Its working principle is prior art and will not be elaborated here.

[0083] Step S3: Define multiple groups of composite rules, which are used to synthesize two sets of single-table query SQL statements and the corresponding natural language single-table query texts into a composite query SQL statement and the corresponding natural language composite query text. Then, use the composite rules to pairwise synthesize the multiple groups of corresponding single-table query SQL statements X_Single_SQL and natural language single-table query texts Y_Single_Text obtained in step S1 to obtain multiple groups of corresponding composite query SQL statements X_Complex_SQL and natural language composite query texts Y_Complex_Text.

[0084] The composite rules include constraint conditions, a composite query SQL statement template, and a natural language composite query text template.

[0085] The synthesis steps are as follows:

[0086] Step S3-1: Pairwise combine the obtained multiple groups of corresponding "single-table query SQL statements X_Single_SQL and natural language single-table query texts Y_Single_Text" and a group of empty "single-table query SQL statements X_Single_SQL and natural language single-table query texts Y_Single_Text". In each combination, one group of "single-table query SQL statements X_Single_SQL and natural language single-table query texts Y_Single_Text" is denoted as S_1, and the other group is denoted as S_2. S_1 cannot be empty, and S_2 can be empty.

[0087] Step S3-2: Traverse all composite rules. For each composite rule, execute steps S3-2-1 to S3-2-3 respectively:

[0088] Step S3-2-1: Read a new set of S_1 and S_2. If all S_1 and S_2 have been traversed, end. Parse the query fields, query data tables, and query conditions from S_1 and S_2 respectively. Denote the query fields, query data tables, and query conditions parsed from S_1 as C1, T1, and V1 respectively, and denote the query fields, query data tables, and query conditions parsed from S_2 as C2, T2, and V2 respectively.

[0089] Step S3-2-2: Use the constraint conditions in the current composite rule to determine whether C1, T1, V1 and C2, T2, V2 meet the synthesis conditions. If they do, execute Step S3-2-3; otherwise, jump to Step S3-2-1.

[0090] Step S3-2-3: Embed C1, T1, V1, C2, T2, V2 into the composite query SQL statement template of the current composite rule to obtain the composite query SQL statement X_Complex_SQL. At the same time, embed the natural language text corresponding to C1, T1, V1, C2, T2, V2 into the natural language composite query text template of the current composite rule to obtain the natural language composite query text Y_Complex_Text.

[0091] The following are examples of several composite rules:

[0092] (1) Merging in the same table.

[0093] Constraint conditions: T1 is the same as T2, and V1 does not conflict with V2.

[0094] The obtained composite query SQL statement can be expressed as: select C1,C2 from T1 where V1 and V2, and the corresponding natural language composite query text can be expressed as: "Query C1_Text and C2_Text from the T1_Text table, while meeting the following conditions: V1_Text, V2_Text". Here, T1_Text, C1_Text, C2_Text, V1_Text, V2_Text are the natural language texts obtained after replacement using natural language descriptions.

[0095] (2) Sub-table query.

[0096] Constraint conditions: The field in C2 and V1 is the same field.

[0097] The obtained composite query SQL statement can be expressed as: select C1 from T1 where V1 AND V1.fieldin(select C2 from T2 where V2), and the corresponding natural language composite query text can be expressed as: "Query C1_Text from the T1_Text table, while meeting the following conditions: V1_Text, V1.field belongs to the result of `querying C2_Text that meets the V2_Text condition from the T2_Text table`. V1.field is the field in V1.

[0098] (3) Grouping statistical query.

[0099] Constraint: S_2 is empty, and T1 contains grouping field G and metric field M.

[0100] The resulting composite query SQL statement can be expressed as: "select G, AGG_FUNC(M) from T1 where V1 group by G order by AGG_FUNC(M)", and the natural language composite query text can be expressed as: "Query G_Text and the average value of M_Text from the T1_Text table, subject to the V1_Text condition, grouped by G_Text, and sorted by the AGG_FUNC_Text result".

[0101] The operation of calculating the average value here can also be replaced by operations such as maximum value, minimum value, and summation.

[0102] The above is only an example. In fact, multiple different composite rules can also be constructed to obtain composite query SQL statements and natural language composite query texts for different purposes.

[0103] It should be noted that although only the composition of at most two single-table queries (training the binary relationship of two queries) is performed in this embodiment, the generative model based on the large model technology has learned the data relationship and composition method of the target database during this process. Through experiments, it is found that when facing more complex query requirements (such as a multi-relationship composite query that can be decomposed into S_1, S_2, and S_3), the large model can rely on the learned mapping relationship and capture ability to perform deeper nested processing autonomously and achieve satisfactory results.

[0104] Optionally, during training, the composite query can also be further composed through a custom composite rule (for example, first combine the single-table queries S_1 and S_2 into S_12, then combine S_12 and the single-table query S_3 into S_123, or combine S_12 and S_34 into S_1234), so as to obtain the SQL statement and text of the multi-relationship composite query and perform more complex training.

[0105] After obtaining the natural language composite query text, it is also necessary to call the BART model to rewrite and polish it. The prompt template is: "Rewrite this content: <original sentence> to make it more in line with the description of natural language". The rewritten natural language composite query text is used as the final natural language composite query text.

[0106] Step S4: Construct a MutiSingleText-to-ComplexText generation model. The input of the MutiSingleText-to-ComplexText generation model is two or more natural language single-table query texts, and the output is a natural language composite query text. The composite query SQL statement X_Complex_SQL obtained in step S3 is decomposed into multiple sub-query SQL statements X_Sub_SQL through a statement decomposition module. Then, for each sub-query SQL statement X_Sub_SQL, the corresponding sub-query natural language text Y_Sub_Text is obtained through a SingleSQL-to-SingleText generation model. The obtained multiple sub-query natural language texts Y_Sub_Text are used as samples, and the natural language composite query text Y_Complex_Text corresponding to the original composite query SQL statement X_Complex_SQL is used as a label to train the MutiSingleText-to-ComplexText generation model.

[0107] The statement decomposition module parses the composite query SQL statement to obtain an AST syntax tree structure, and then depth-first traverses all nodes in the AST syntax tree structure to obtain all sub-query SQL statements in order. Specifically, for the current node, if its attribute is "SELECT" and it is not the root node, the corresponding "sub-query SQL statement" is obtained according to the attribute information of the node.

[0108] During implementation, the moz_sql_parser library is called to parse the SQL statement, and the obtained syntax tree is in dictionary or JSON format.

[0109] The syntax tree structure can refer to the following example: (for reference only)

[0110] # Given SQL

[0111] SELECT u.id,

[0112] (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count

[0113] FROM users u

[0114] JOIN profiles p ON u.profile_id = p.id

[0115] # Syntax tree structure of the given SQL

[0116] SELECT

[0117] ├── columns: ["u.id", subquery]

[0118] ├── from: "users u"

[0119] ├── joins:

[0120] │ ├── table: "profiles p"

[0121] │ ├── on: "u.profile_id = p.id"

[0122] └── subquery (SELECT COUNT(*) FROM orders WHERE orders.user_id =u.id)

[0123] ├── columns: COUNT(*)

[0124] ├── from: "orders o"

[0125] └── where: o.user_id = u.id

[0126] The MutiSingleText-to-ComplexText generation model in this step is also based on the Transformer architecture.

[0127] Step S5, construct a generation-scoring-filtering module, which includes a reference large language model, a statement decomposition module, a SingleSQL-to-SingleText generation model, a MutiSingleText-to-ComplexText generation model, and a similarity calculation module, for obtaining multiple SQL statements based on the input natural language query text X_Input_Text and selecting the optimal expected SQL statement Y_Expect_SQL from them.

[0128] The input of the generation-scoring-filtering module is the natural language query text, and the output is the expected SQL statement. Its working process is as follows:

[0129] Step S5-1, input the natural language query text X_Input_Text into the reference large language model, and generate multiple groups of corresponding SQL statements Y_SQL with the guidance of prompt words.

[0130] The natural language query text is a query requirement expressed in natural language prepared in advance, such as "query the monthly electricity consumption of user with id 1 in January".

[0131] In this embodiment, CodeQwen 1.5 is selected as the reference large language model.

[0132] Step S5-2: Perform syntax detection on the generated SQL statement Y_SQL, and eliminate the SQL statements with syntax errors.

[0133] Step S5-3: For each SQL statement Y_SQL, parse and transform it using the statement decomposition module to obtain the corresponding multiple subquery SQL statements, and then input each subquery SQL statement into the SingleSQL-to-SingleText generation model to obtain the subquery natural language text. At this time, each SQL statement Y_SQL corresponds to multiple subquery natural language texts.

[0134] Step S5-4: For each SQL statement Y_SQL, input the corresponding multiple subquery natural language texts into the MutiSingleText-to-ComplexText generation model to obtain the natural language composite query text corresponding to the SQL statement Y_SQL respectively. At this time, the natural language query text X_Input_Text input in Step S5-1 corresponds to multiple SQL statements Y_SQL, and each SQL statement Y_SQL corresponds to a natural language composite query text respectively.

[0135] Step S5-5: Use the similarity calculation module to calculate the similarity between the natural language query text X_Input_Text input in Step S5-1 and each natural language composite query text obtained in Step S5-4. If the highest similarity score exceeds the preset value, select the SQL statement Y_SQL corresponding to the highest similarity score as the expected SQL statement Y_Expect_SQL.

[0136] The calculation process of the similarity calculation module is as follows:

[0137] Suppose the texts for which the similarity needs to be calculated are and , and the corresponding embedding vectors are:

[0138] ;

[0139] ;

[0140] and are respectively the th element in the embedding vector of and the th element in the embedding vector of

[0141] Define calculation and The cosine similarity between any two elements in is:

[0142] ;

[0143] Then calculate and The matching accuracy of the core content of the text :

[0144] ;

[0145] Calculate and The matching completeness of the core content of the text :

[0146] ;

[0147] Finally, calculate and The similarity score :

[0148] ;

[0149] represents the text similarity measured comprehensively, and the higher the value, the closer it is to the original meaning.

[0150] In this embodiment, must be greater than 0.95 to be retained.

[0151] Step S6, construct a Transformer-based Text-to-SQL generation model, and input multiple groups of natural language query texts X_Input_Text into the generation-scoring-filtering module to obtain the corresponding expected SQL statement Y_Expect_SQL. Then, use the natural language query text X_Input_Text as a sample and the corresponding expected SQL statement Y_Expect_SQL as a label to train the Text-to-SQL generation model.

[0152] Preferably, the reference large language model in step S5 can also be directly used as the Text-to-SQL generation model, and the natural language query text X_Input_Text is used as a sample and the corresponding expected SQL statement Y_Expect_SQL as a label to perform fine-tuning training on the reference large language model.

[0153] Step S7, input the user's natural language query text into the trained Text-to-SQL generation model to obtain the corresponding SQL statement.

[0154] It should be noted that for those skilled in the art, it is obvious that the present invention is not limited to the details of the above-described exemplary embodiments, and without departing from the spirit or basic characteristics of the present invention, the present invention can be implemented in other specific forms. The scope of the present invention is defined by the claims rather than the above description.

Claims

1. A Text-to-SQL generation method based on adaptive knowledge distillation, characterized in that the steps include: Step S1, obtaining metadata of a target database, and generating multiple sets of corresponding single-table query SQL statements and natural language single-table query texts based on the metadata; Step S2, constructing a SingleSQL-to-SingleText generation model, the input of the generation model is a single-table query SQL statement, and the output is a natural language single-table query text; then using the single-table query SQL statement obtained in step S1 as a sample and the natural language single-table query text obtained in step S1 as a label, the SingleSQL-to-SingleText generation model is trained; Step S3, defining multiple groups of compound rules, and then using the compound rules to synthesize the multiple groups of mutually corresponding single-table query SQL statements and natural language single-table query texts obtained in step S1, to obtain multiple groups of mutually corresponding compound query SQL statements and natural language compound query texts; Step S4, constructing a MutiSingleText-to-ComplexText generation model, the input of the generation model is two or more natural language single-table query texts, and the output is a natural language compound query text; the compound query SQL statement obtained in step S3 is used to obtain multiple sub-query SQL statements through a statement decomposition module, and then for each sub-query SQL statement, the corresponding sub-query natural language text t is obtained through the SingleSQL-to-SingleText generation model respectively, the obtained multiple sub-query natural language texts are used as samples, and the natural language compound query text corresponding to the original compound query SQL statement is used as a label to train the MutiSingleText-to-ComplexText generation model; Step S5: constructing a generation-scoring-screening module, wherein the generation-scoring-screening module includes a reference large language model, a sentence decomposition module, a SingleSQL-to-SingleText generation model, a MultiSingleText-to-ComplexText generation model, and a similarity calculation module, and is used to obtain multiple SQL statements based on the input natural language query text and select the optimal expected SQL statement from them; Step S6: construct a Transformer-based Text-to-SQL generation model, and input multiple groups of natural language query texts into the generation-scoring-screening module to obtain corresponding expected SQL statements, and then use the natural language query texts as samples and the corresponding expected SQL statements as labels to train the Text-to-SQL generation model; Step S7: input the user's natural language query text into the trained Text-to-SQL generation model to obtain the corresponding SQL statement.

2. The Text-to-SQL generation method based on adaptive knowledge distillation according to claim 1, characterized in that: In step S1, first, the table information of all data tables in the database and all field information in the data tables are obtained through the metadata of the target database; then, the query condition is constructed through the query condition template; then, the field C, the data table T to which it belongs, and the query condition V under the same data table are combined with each other, and then each group is combined with the query condition V.<C,T,V> The combination and its corresponding natural language description are embedded into the single-table query SQL statement template and the natural language single-table query text template to obtain the corresponding single-table query SQL statement X_Single_SQL and the natural language single-table query text Y_Single_Text.

3. The Text-to-SQL generation method based on adaptive knowledge distillation according to claim 1, characterized in that: The compound rule includes constraint conditions, a compound query SQL statement template and a natural language compound query text template.

4. The Text-to-SQL generation method based on adaptive knowledge distillation according to claim 3, characterized in that: The synthesis steps of step S3 are as follows: Step S3-1, combining the obtained multiple groups of corresponding "single table query SQL statements X_Single_SQL and natural language single table query text Y_Single_Text" and a group of empty "single table query SQL statements X_Single_SQL and natural language single table query text Y_Single_Text" in pairs; in each combination, one group of "single table query SQL statements X_Single_SQL and natural language single table query text Y_Single_Text" is recorded as S_1, and the other group is recorded as S_2, S_1 cannot be empty, and S_2 can be empty; Step S3-2: traverse all compound rules, and execute steps S3-2-1 to S3-2-3 for each compound rule: Step S3-2-1, read a new set of S_1 and S_2; if all S_1 and S_2 have been traversed, then end; Parse the query fields, query data tables and query conditions from S_1 and S_2 respectively, and record the query fields, query data tables and query conditions parsed from S_1 as C1, T1 and V1 respectively, and record the query fields, query data tables and query conditions parsed from S_2 as C2, T2 and V2 respectively; Step S3-2-2, using the constraints in the current composite rule to determine whether C1, T1, V1 and C2, T2, V2 meet the composite conditions, if yes, execute step S3-2-3, otherwise jump to step S3-2-1; Step S3-2-3, embed C1, T1, V1, C2, T2, V2 into the compound query SQL statement template of the current compound rule to obtain the compound query SQL statement X_Complex_SQL, and at the same time embed the natural language text corresponding to C1, T1, V1, C2, T2, V2 into the natural language compound query text template of the current compound rule to obtain the natural language compound query text Y_Complex_Text.

5. The Text-to-SQL generation method based on adaptive knowledge distillation according to claim 4, characterized in that: After the natural language compound query text is obtained through step S-2, the BART model is called to rewrite and polish it, and then the rewritten natural language compound query text is used as the final natural language compound query text.

6. The Text-to-SQL generation method based on adaptive knowledge distillation according to claim 1, characterized in that: The statement decomposition module parses the compound query SQL statement to obtain an AST syntax tree structure, and then depth-first traverses all nodes in the AST syntax tree structure to obtain all sub-query SQL statements in order.

7. The Text-to-SQL generation method based on adaptive knowledge distillation according to claim 6, characterized in that: When traversing all nodes in the AST syntax tree structure in depth first, for the current node, if its attribute is "SELECT" and it is not a root node, the corresponding "subquery SQL statement" is obtained according to the attribute information of the node.

8. The Text-to-SQL generation method based on adaptive knowledge distillation according to claim 1, characterized in that: The input of the Generate-Score-Filter module is the natural language query text, and the output is the expected SQL statement. Its working process is as follows: Step S5-1, input the natural language query text X_Input_Text into the reference large language model, and generate multiple groups of corresponding SQL statements Y_SQL with the guidance of prompt words; Step S5-2, perform syntax check on the generated SQL statement Y_SQL, and remove SQL statements with syntax errors; Step S5-3: for each SQL statement Y_SQL, parse and convert it using the statement decomposition module to obtain corresponding multiple sub-query SQL statements, and then input each sub-query SQL statement into the SingleSQL-to-SingleText generation model to obtain the sub-query natural language text; at this time, each SQL statement Y_SQL corresponds to multiple sub-query natural language texts; Step S5-4: for each SQL statement Y_SQL, input the corresponding multiple sub-query natural language texts into the MutiSingleText-to-ComplexText generation model to obtain the natural language compound query texts corresponding to the SQL statements Y_SQL; at this time, the natural language query text X_Input_Text input in step S5-1 corresponds to multiple SQL statements Y_SQL, and each SQL statement Y_SQL corresponds to a natural language compound query text; Step S5-5, using a similarity calculation module to calculate the similarity between the natural language query text X_Input_Text input in step S5-1 and each natural language compound query text obtained in step S5-4; if the highest similarity score exceeds a preset value, the SQL statement Y_SQL corresponding to the highest similarity score is selected as the expected SQL statement Y_Expect_SQL.

9. The Text-to-SQL generation method based on adaptive knowledge distillation according to claim 8, characterized in that: The calculation process of the similarity calculation module is: Assume that the texts that need to calculate similarity are and , the corresponding embedding vectors are: ； ； and They are The first elements and The first elements; Defining calculations and The cosine similarity of any two elements in is: ； Then calculate and The accuracy of text core content matching : ； calculate and The completeness of the matching of the core content of the text : ； Final calculation and Similarity score : 。 10. The Text-to-SQL generation method based on adaptive knowledge distillation according to any one of claims 1 to 9, characterized in that: In step S6, the reference large language model in step S5 is directly used as the Text-to-SQL generation model, the natural language query text X_Input_Text is used as a sample, and the corresponding expected SQL statement Y_Expect_SQL is used as a label to fine-tune the reference large language model.

Images