Text-to-SQL Generation Method Based on Adaptive Knowledge Distillation

Abstract

The present invention discloses a Text-to-SQL generation method based on adaptive knowledge distillation. First, a single-table SQL statement and a corresponding natural language query text are generated based on the target database metadata, and a generation model from single-table SQL to natural language is trained. Then, compound rules are defined to combine single-table data into multi-table compound query SQL and corresponding natural language text. Subqueries are obtained by decomposing the compound SQL, and subquery texts are generated to train a generation model from multiple texts to compound texts. Then, a generation-scoring-screening module is constructed, which combines a large language model and similarity calculation to generate and screen the optimal SQL statement, thereby obtaining training sample data and training a Transformer-based Text-to-SQL generation model. The present invention has the advantages of high sample data quality, the ability to generate compound query SQL statements, strong adaptability, and good flexibility.

Description

Technical Field

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

[0002] Text-to-SQL is a technology that automatically converts natural language query text (such as Chinese and English) into Structured Query Language (SQL). It aims to lower the barrier to entry for database queries, enabling non-expert users to interact with databases through intuitive natural language. This technology is widely used in intelligent data analysis, enterprise report generation, database management tools, and other fields, significantly improving the efficiency and usability of data queries.

[0003] Currently, Text-to-SQL can be implemented in the following ways:

[0004] (1) Traditional template matching method: This method generates SQL statements by pre-defining SQL statement templates and corresponding natural language query templates, using rule matching and template filling. Although the SQL generated by this method has no syntactic errors, its generalization ability is poor. Not only does it require strict input of natural language according to the template requirements, which is difficult to use, but it is also unable to handle query requirements outside the pre-defined templates. In addition, when the data table structure changes, the template needs to be manually adjusted, which has high maintenance costs. This method has gradually been replaced by other methods.

[0005] (2) Seq2Seq-based deep learning method: SQL is treated as the target language, and the conversion from natural language to SQL is achieved through a sequence-to-sequence (Seq2Seq) model (such as a machine translation framework). The disadvantage of this method is that it requires a large amount of labeled data (natural language query text-SQL paired samples) for supervised training, and the model only optimizes token-level generation, which is prone to outputting SQL with grammatical errors. It requires additional rule constraints and is difficult to train.

[0006] (3) The Large Language Model (LLM)-based approach uses hint engineering, vector retrieval, and other technologies to enhance the LLM input and directly generate SQL statements. This approach also requires a large amount of labeled data and consumes a lot of resources for fine-tuning. It is difficult, costly, and lacks adaptability.

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

[0008] In order to solve the problem that existing methods require a large amount of labeled data, technicians in this field have also proposed a training method based on high-level large models and distillation technology. The core concept is: use a high-level 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-level model to evaluate and score these samples, filter out 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 a large model to see whether there is a problem with the SQL generated by another large model, resulting in an objective evaluation. In addition, the scoring criteria of each evaluation of the large model also have a certain degree of randomness, and the intermediate process cannot be intervened. Therefore, the evaluation of the large model has the problem of poor objectivity, and the filtered samples may not be the best samples. (2) In a fixed application field, in order to save resources, the target model only needs to have the Text-to-SQL capability for a specific database. However, the existing methods cannot provide dedicated training samples for specific databases. A large number of samples are required for training, which is costly and the final generation effect is not ideal. (3) When facing complex query problems, the accuracy of the SQL statements generated by the existing large models is low, and the final generation effect cannot be guaranteed. Summary of the Invention

[0009] This paper proposes a Text-to-SQL generation method based on adaptive knowledge distillation, the purpose of which is to: (1) solve the problem that the sample data screening process is not objective enough and the data quality is low; (2) solve the problem that the sample data cannot be matched with the target database and has poor adaptability and flexibility; (3) solve the problem that existing methods cannot generate high-quality sample data for complex queries.

[0010] The technical solutions of the present invention are as follows:

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

[0012] Step S1: Obtain metadata of a target database, and generate multiple sets 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, where 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, the SingleSQL-to-SingleText generation model is trained 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.

[0014] Step S3, defining multiple groups of composite rules, wherein the composite rules are used to combine the two groups of single-table query SQL statements and the corresponding natural language single-table query texts into a composite query SQL statement and a corresponding natural language composite query text; then, using the composite rules, the multiple groups of mutually corresponding single-table query SQL statements X_Single_SQL and natural language single-table query texts Y_Single_Text obtained in step S1 are composited to obtain multiple groups of mutually corresponding composite query SQL statements X_Complex_SQL and natural language composite query texts Y_Complex_Text;

[0015] Step S4, constructing 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 compound query text; the compound query SQL statement X_Complex_SQL obtained in step S3 is used by a statement decomposition module to obtain multiple sub-query SQL statements X_Sub_SQL, and then for each sub-query SQL statement X_Sub_SQL, a corresponding sub-query natural language text Y_Sub_Text is obtained by the SingleSQL-to-SingleText generation model respectively, the obtained multiple sub-query natural language texts Y_Sub_Text are used as samples, and the natural language compound query text Y_Complex_Text corresponding to the original compound query SQL statement X_Complex_SQL is used as a label to train the MutiSingleText-to-ComplexText generation model;

[0016] Step S5: Constructing a generation-scoring-screening module, which includes a reference large language model, a statement 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 X_Input_Text and select the optimal expected SQL statement Y_Expect_SQL from them;

[0017] Step S6: Build a Transformer-based Text-to-SQL generation model, and input multiple sets of natural language query texts X_Input_Text into the Generate-Score-Filter module to obtain the corresponding expected SQL statements Y_Expect_SQL. Then, use the natural language query texts X_Input_Text as samples and the corresponding expected SQL statements Y_Expect_SQL as labels 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 the 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 combine each group<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.

[0020] As a further improvement of the Text-to-SQL generation method based on adaptive knowledge distillation: the compound rules include constraint conditions, compound query SQL statement templates and natural language compound query text templates.

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

[0022] Step S3-1, combining the obtained multiple corresponding sets of "single-table query SQL statements X_Single_SQL and natural language single-table query text Y_Single_Text" and a set 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 set of "single-table query SQL statements X_Single_SQL and natural language single-table query text Y_Single_Text" is denoted as S_1, and the other set is denoted as S_2, S_1 cannot be empty, and S_2 can be empty;

[0023] Step S3-2: Traverse all compound rules and execute steps S3-2-1 to S3-2-3 for each compound rule:

[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, then end;

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

[0026] Step S3-2-2, use the constraints in the current composite rule to determine whether C1, T1, V1 and C2, T2, V2 meet the composite conditions. If they do, 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 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.

[0028] As a further improvement of the Text-to-SQL generation method based on adaptive knowledge distillation: after obtaining the natural language compound query text 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.

[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 traverses all nodes in the AST syntax tree structure in depth-first to obtain all sub-query SQL statements in order.

[0030] As a further improvement to 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 based on the attribute information of the node.

[0031] As a further improvement to the Text-to-SQL generation method based on adaptive knowledge distillation, 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:

[0032] Step S5-1: Input the natural language query text X_Input_Text into the reference large language model, and generate multiple sets 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 to 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 multiple corresponding 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 MultiSingleText-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 the various natural language compound query texts obtained in step S5-4; if the highest similarity score exceeds a 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 Text-to-SQL generation method based on adaptive knowledge distillation, the calculation process of the similarity calculation module is as follows:

[0038] Assume that the texts to be calculated for similarity are and , the corresponding embedding vectors are:

[0039] ;

[0040] ;

[0041] and They are The first embedding vector of elements and The first embedding vector of elements;

[0042] Defining calculations and The cosine similarity of any two elements in is:

[0043] ;

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

[0045] ;

[0046] calculate and The completeness of the matching of the core content of the text :

[0047] ;

[0048] Final calculation and Similarity score :

[0049] .

[0050] As a further improvement to the Text-to-SQL generation method based on adaptive knowledge distillation, 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.

[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, then restores the SQL statements into natural language compound query texts through statement decomposition, SQL-Text transformation, and natural language text synthesis. The optimal SQL statement is then selected by calculating the similarity between the natural language compound query text and the original input natural language query text. Each link in this process is independently controllable, thereby ensuring the objectivity of the screening process and improving the quality of sample data.

[0053] 2. The generative models in the present invention are all trained based on the metadata of the target database, so the obtained sample data are more consistent with the actual application, have stronger adaptability and better flexibility.

[0054] 3. The present invention also proposes compound rules and a MultiSingleText-to-ComplexText generation model. The former can merge more than one single-table query into a compound query, while the latter can convert multiple sub-query natural language texts into natural language compound query texts, establishing a comprehensive mapping relationship from multiple sub-queries to compound queries, thereby laying the foundation for providing compound query sample data, so that the final Text-to-SQL generation model has the comprehensive ability to generate SQL for single-table queries and compound queries. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] Figure 1 Schematic diagram of the process of the present invention. DETAILED DESCRIPTION

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

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

[0058] Step S1: Obtain metadata of a target database, and generate multiple sets 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, the metadata of the target database is used to 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 names of the tables / fields and related DDL information.

[0060] Next, query conditions are constructed using query condition templates. Different field types have corresponding query condition templates. Field types are primarily categorized as equality and range. For equality fields (discrete data types like id and name), query condition templates typically take the form "c=1," where c is the field name in the condition. For range fields, typically datetime or int data, query condition templates might take the form "c>'2025-01-01'." Furthermore, a single field can correspond to multiple query condition templates. For example, for a date field, you can query by equality: "datatime = '2025-01-01'" or by range: "datatime > '2025-01-01' anddatatime < '2025-02-01'." You can also construct an empty query condition, 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<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.

[0062] The reference code for embedding templates for splicing 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"{Covert(table, columns)}data"

[0072] if condition:

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

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

[0075] return sql, desc

[0076] Covert is a string conversion function that maps a given table or field name to a natural language description of the database (derived from the database's DDL information) and then embeds it into the corresponding string template (which contains query logic such as "greater than" and "equal to") 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 text.

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

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

[0081] Step S2: Build a SingleSQL-to-SingleText generation model. The SingleSQL-to-SingleText generation model takes a single-table query SQL statement as input and outputs a natural language single-table query text. The SingleSQL-to-SingleText generation model is then trained 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.

[0082] The SingleSQL-to-SingleText generation model is based on the Transformer architecture and includes an encoder and decoder. Its working principle is based on existing technology and will not be described in detail here.

[0083] Step S3: Define multiple sets of compound rules, each used to combine two sets of single-table query SQL statements and the corresponding natural language single-table query text into a compound query SQL statement and a corresponding natural language compound query text. Then, using the compound rules, the multiple sets of corresponding single-table query SQL statements X_Single_SQL and natural language single-table query text Y_Single_Text obtained in step S1 are synthesized pairwise to obtain multiple sets of corresponding compound query SQL statements X_Complex_SQL and natural language compound query text Y_Complex_Text.

[0084] The compound rule includes constraint conditions, a compound query SQL statement template and a natural language compound query text template.

[0085] The synthesis steps are as follows:

[0086] Step S3-1. Combine the obtained multiple corresponding groups of "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 must not be empty, and S_2 can be empty.

[0087] Step S3-2: Traverse all compound rules and execute steps S3-2-1 to S3-2-3 for each compound rule:

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

[0089] Step S3-2-2: Use the constraints 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 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.

[0091] The following are some examples of compound rules:

[0092] (1) Merge the same table.

[0093] Constraints: T1 and T2 are the same, and V1 and V2 do not conflict.

[0094] The resulting compound query SQL statement can be expressed as: select C1,C2 from T1 where V1 and V2. The corresponding natural language compound query text can be expressed as: "Query C1_Text and C2_Text from the T1_Text table that also meet the following conditions: V1_Text, V2_Text." Here, T1_Text, C1_Text, C2_Text, V1_Text, and V2_Text are natural language texts obtained by replacing the natural language descriptions.

[0095] (2) Subtable query.

[0096] Constraint: The fields in C2 and V1 are the same.

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

[0098] (3) Group statistical query.

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

[0100] The resulting compound query SQL statement can be expressed as: "select G, AGG_FUNC(M) from T1 where V1group by G order by AGG_FUNC(M)". The natural language compound query text can be expressed as: "Query the average values of G_Text and M_Text from the T1_Text table, which must meet the V1_Text condition, group by G_Text, and sort by the AGG_FUNC_Text results."

[0101] The average value here can also be replaced by maximum value, minimum value, summation and other operations.

[0102] The above are only examples. In practice, multiple groups of different compound rules can be constructed to obtain compound query SQL statements and natural language compound query texts for different purposes.

[0103] It's important to note that while this example only composites a maximum of two single-table queries (training the binary relationships between the two queries), the generative model based on large-scale model technology has already learned the target database's data relationships and composite methods during this process. Experiments have shown that when faced with 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-scale model, leveraging its learned mapping relationships and capture capabilities, can autonomously perform deeper nesting processing, achieving satisfactory results.

[0104] Optionally, during training, the compounded queries can be further synthesized using custom compound rules (for example, first combining single-table queries S_1 and S_2 into S_12, then combining S_12 and single-table query S_3 into S_123, or combining S_12 and S_34 into S_1234), thereby obtaining SQL statements and text for multi-relational compound queries, allowing for more complex training.

[0105] After obtaining the natural language compound query text, the BART model needs to be called to rewrite and polish it. The prompt word template is: "Rewrite this content: <original sentence> to make it more consistent with the natural language description." The rewritten natural language compound query text is used as the final natural language compound query text.

[0106] Step S4: Construct a MutiSingleText-to-ComplexText generation model. The MutiSingleText-to-ComplexText generation model takes two or more natural language single-table query texts as input and outputs natural language compound query texts. The compound query SQL statement X_Complex_SQL obtained in step S3 is decomposed into multiple sub-query SQL statements X_Sub_SQL using a statement decomposition module. Then, for each sub-query SQL statement X_Sub_SQL, a corresponding sub-query natural language text Y_Sub_Text is obtained using 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 compound query text Y_Complex_Text corresponding to the original compound 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 compound query SQL statement into an AST syntax tree structure, then performs a depth-first traversal of all nodes in the AST syntax tree structure to sequentially obtain all sub-query SQL statements. Specifically, for the current node, if its attribute is "SELECT" and it is not a root node, the corresponding "sub-query SQL statement" is obtained based on the node's attribute information.

[0108] During implementation, the moz_sql_parser library is called to parse the SQL statement, and the resulting 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) ASorder_count

[0113] FROM users u

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

[0115] # Given the syntax tree structure of 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 MultiSingleText-to-ComplexText generation model in this step is also based on the Transformer architecture.

[0127] Step S5: Construct a generation-scoring-screening module. The generation-scoring-screening module includes a reference large language model, a statement decomposition module, a SingleSQL-to-SingleText generation model, a MultiSingleText-to-ComplexText generation model, and a similarity calculation module. The module 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.

[0128] 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:

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

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

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

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

[0133] Step S5-3: For each SQL statement Y_SQL, the statement decomposition module parses and converts it to obtain multiple corresponding sub-query SQL statements. Each sub-query SQL statement is then input into the SingleSQL-to-SingleText generation model to obtain the sub-query natural language text. At this point, each SQL statement Y_SQL corresponds to multiple sub-query natural language texts.

[0134] Step S5-4: For each SQL statement Y_SQL, the corresponding multiple sub-query natural language texts are input into the MultiSingleText-to-ComplexText generation model to obtain the natural language compound query texts corresponding to each SQL statement Y_SQL. At this point, the natural language query text X_Input_Text input in step S5-1 corresponds to multiple SQL statements Y_SQL, with each SQL statement Y_SQL corresponding to a natural language compound query text.

[0135] Step S5-5: Use 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.

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

[0137] Assume that the texts to be calculated for similarity are and , the corresponding embedding vectors are:

[0138] ;

[0139] ;

[0140] and They are The first embedding vector of elements and The first embedding vector of elements.

[0141] Defining calculations and The cosine similarity of any two elements in is:

[0142] ;

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

[0144] ;

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

[0146] ;

[0147] Final calculation and Similarity score :

[0148] ;

[0149] Represents a comprehensive measure of text similarity. 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: Build a Transformer-based Text-to-SQL generation model, and 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. Then, use the natural language query texts X_Input_Text as samples and the corresponding expected SQL statements Y_Expect_SQL as labels to train the Text-to-SQL generation model.

[0152] Preferably, the reference large language model in step S5 can also be directly used as a 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 is used as a label to fine-tune 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 it is obvious to those skilled in the art that the present invention is not limited to the details of the exemplary embodiments described above, and that the present invention can be implemented in other specific forms without departing from the spirit or essential characteristics of the present invention. The scope of the present invention is defined by the claims rather than the foregoing description.

Claims

1. A Text-to-SQL generation method based on adaptive knowledge distillation, characterized by the following steps: include: Step S1: Obtain metadata of the target database, and generate multiple sets of corresponding single-table query SQL statements and natural language single-table query texts based on the metadata; Step S2: construct a SingleSQL-to-SingleText generation model, where 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, the SingleSQL-to-SingleText generation model is trained 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; Step S3: defining multiple sets of compound rules, and then using the compound rules to synthesize the multiple sets of mutually corresponding single-table query SQL statements and natural language single-table query texts obtained in step S1 to obtain multiple sets of mutually corresponding compound query SQL statements and natural language compound query texts; Step S4: constructing a MutiSingleText-to-ComplexText generation model, wherein 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 subjected to a statement decomposition module to obtain multiple sub-query SQL statements, and then for each sub-query SQL statement, a corresponding sub-query natural language text t is obtained by respectively using the SingleSQL-to-SingleText generation model; 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, which includes a reference large language model, a statement 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: Build a Transformer-based Text-to-SQL generation model, and input multiple sets of natural language query texts into the Generate-Score-Filter module to obtain the corresponding expected SQL statements. 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 method for generating text-to-SQL based on adaptive knowledge distillation according to claim 1, wherein: 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 build the 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 combine each group<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 method for generating Text-to-SQL based on adaptive knowledge distillation according to claim 3, wherein: The synthesis steps of step S3 are as follows: Step S3-1. Combine the obtained multiple corresponding sets of "single-table query SQL statements X_Single_SQL and natural language single-table query text Y_Single_Text" and a set of empty "single-table query SQL statements X_Single_SQL and natural language single-table query text Y_Single_Text" into pairs; in each combination, one set of "single-table query SQL statements X_Single_SQL and natural language single-table query text 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; 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. Record the query fields, query data tables, and query conditions parsed from S_1 as C1, T1, and V1 respectively. Record the query fields, query data tables, and query conditions parsed from S_2 as C2, T2, and V2 respectively. Step S3-2-2, use the constraints in the current composite rule to determine whether C1, T1, V1 and C2, T2, V2 meet the composite conditions. If they do, 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 method for generating text-to-SQL based on adaptive knowledge distillation according to claim 4, wherein: 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 method for generating Text-to-SQL based on adaptive knowledge distillation according to claim 1, wherein: The statement decomposition module parses the compound query SQL statement to obtain an AST syntax tree structure, and then traverses all nodes in the AST syntax tree structure in depth-first order to obtain all sub-query SQL statements in order.

7. The method for generating Text-to-SQL based on adaptive knowledge distillation according to claim 6, wherein: 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 the root node, the corresponding "subquery SQL statement" is obtained according to the attribute information of the node.

8. The method for generating Text-to-SQL based on adaptive knowledge distillation according to claim 1, wherein: 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 sets 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 to 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 multiple corresponding 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 MultiSingleText-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: Use the similarity calculation module to calculate the similarity between the natural language query text X_Input_Text input in step S5-1 and the various natural language compound query texts obtained in step S5-4; if the highest similarity score exceeds a preset value, select the SQL statement Y_SQL corresponding to the highest similarity score as the expected SQL statement Y_Expect_SQL.

9. The method for generating Text-to-SQL based on adaptive knowledge distillation according to claim 8, wherein: The calculation process of the similarity calculation module is: Assume that the texts to be calculated for similarity are and , the corresponding embedding vectors are: ； ； and They are The first embedding vector of elements and The first embedding vector of 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 the sample, and the corresponding expected SQL statement Y_Expect_SQL is used as the label to fine-tune the reference large language model.

Images