SQL statement generation system and method based on thinking chain and small sample enhancement

Abstract

The invention belongs to the field of artificial intelligence application, and particularly discloses an SQL (Structured Query Language) statement generation system and method based on a thinking chain and small sample enhancement, and a key entity in a natural language problem is associated with a database mode by establishing a professional noun library, constructing an entity recognition model and generating a database structure link (Schema Links). Then, corresponding sample sets are selected according to complexity classification of the problems to generate SQL statements, layer-by-layer reasoning and multi-layer classification strategies are adopted, it is ensured that the generated SQL is accurate and efficient, meanwhile, the correctness of a result is verified through an SQL inspection module, and therefore the accuracy and efficiency of conversion from the natural language to the SQL are improved. According to the method, the problem of conversion from the natural language to the SQL statement is effectively solved, and the convenience and the intelligent level of database query are improved.

Description

Technical Field

[0001] The present invention belongs to the field of artificial intelligence applications and is a method for generating database query language (SQL) through natural language questions. Specifically, it relates to a SQL statement generation system and method based on thought chain and small sample enhancement. Background Art

[0002] At present, large language model technology has been widely used in many fields such as text summarization and sentiment classification. However, due to the complexity of database language and its highly specialized needs, generating accurate SQL statements directly through natural language input query questions faces significant challenges. Database queries require not only the accuracy of the results, but also the logical rigor of the query structure. Therefore, it is of great significance to generate accurate SQL query statements based on natural language questions. It can not only greatly reduce the difficulty of using the database, but also significantly improve the efficiency and convenience of data query. In the prior art, patent CN116629227B proposes a SQL generation method based on database mode and difficulty classification, but does not consider the matching of professional terms and similar sample enhancement, which is difficult to apply in professional fields. CN118210818B proposes a method to improve the accuracy of SQL generation by generating table names, column names and column values ​​in sequence, but has limitations in generating complex SQL statements containing nested queries and aggregate functions. Summary of the invention

[0003] In view of the above problems, the present invention provides a SQL statement generation system and method based on thought chain and small sample enhancement, which associates the key entities in natural language questions with database schemas by establishing a professional term library, building an entity recognition model and generating database structure links (Schema Links). Then, the corresponding sample set is selected according to the complexity classification of the problem to generate SQL statements, and the layer-by-layer reasoning and multi-layer classification strategies are adopted to ensure that the generated SQL is accurate and efficient. At the same time, the correctness of the results is verified by the SQL verification module, thereby improving the accuracy and efficiency of natural language to SQL conversion.

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

[0005] A SQL statement generation system based on thought chain and small sample enhancement, comprising:

[0006] 1) Entity recognition module, which is used to extract entity information related to the database schema from natural language queries;

[0007] 2) a query generation module, used to generate SQL statements based on the extracted entity information and database schema;

[0008] 3) SQL verification module, used to verify the correctness of the generated SQL statements.

[0009] Furthermore, in the above-mentioned SQL statement generation system based on thought chain and small sample enhancement, the 1) entity recognition module further includes:

[0010] Train entity recognition models to identify professional terms, synonyms, and synonyms in natural language queries by building models based on named entity recognition;

[0011] Noun entity set extraction, uses the trained entity recognition model to parse natural language queries and extract noun entity sets related to the database pattern.

[0012] Furthermore, in the above-mentioned SQL statement generation system based on thought chain and small sample enhancement, the 2) query generation module further includes:

[0013] Fine-tune the model and prepare the dataset. Fine-tune the BERT model by building a Text-to-SQL sample set to obtain a large Text-to-SQL fine-tuned model.

[0014] The reasoning process adopts layer-by-layer reasoning and multi-layer classification strategies, including generating Schema thinking chains and SchemaLinks, generating classification thinking chains and classification labels, and generating SQL statements based on classification labels; including: expanding the sample set according to needs in each reasoning stage, and adopting the method of selecting small samples based on similarity to enhance the large model, so as to improve the accuracy of reasoning.

[0015] Furthermore, in the above-mentioned SQL statement generation system based on thought chain and small sample enhancement, the reasoning process also includes the following process:

[0016] The first stage generates Schema thinking chains and Schema Links to establish links between database tables, columns, and query values;

[0017] The second stage generates classification thought chains and classification labels, and derives classification labels based on database links and preset classification strategies;

[0018] The third stage generates SQL statements based on classification labels, selects corresponding sample sets according to classification labels, and generates target SQL queries.

[0019] Furthermore, in the above-mentioned SQL statement generation system based on thought chain and small sample enhancement, the 3) SQL verification module further includes:

[0020] Large model self-correction, combining database schema, questions, Schema Links and generated SQL statements for automatic correction;

[0021] Mask replacement: replace the mask in the generated SQL statement to ensure that the generated SQL statement is accurately mapped to the target database schema;

[0022] SQL statement verification: connect the corrected SQL statement to the database for execution verification to ensure the correctness of the SQL statement.

[0023] The present invention also discloses a SQL statement generation method based on thought chain and small sample enhancement, comprising:

[0024] S1 extracts entity information related to the database schema from natural language queries;

[0025] S2 generates SQL statements based on the extracted entity information and database schema;

[0026] S3 verifies the correctness of the generated SQL statement.

[0027] Furthermore, in the above-mentioned SQL statement generation method based on thought chain and small sample enhancement, the step S1 of extracting entity information further includes:

[0028] Train entity recognition models to identify professional terms, synonyms, and synonyms in natural language queries by building models based on named entity recognition;

[0029] The trained entity recognition model is used to parse natural language queries and extract the set of noun entities related to the database schema.

[0030] Furthermore, in the above-mentioned SQL statement generation method based on thought chain and small sample enhancement, the step of generating the SQL statement in S2 further comprises:

[0031] Build a Text-to-SQL sample set, fine-tune the BERT model, and obtain a large Text-to-SQL fine-tuned model;

[0032] The SQL statements are generated by adopting layer-by-layer reasoning and multi-layer classification strategies, including generating Schema thinking chains and SchemaLinks, generating classification thinking chains and classification labels, and generating SQL statements based on classification labels.

[0033] Furthermore, in the above-mentioned SQL statement generation method based on thought chain and small sample enhancement, the step of generating the SQL statement in S2 further includes:

[0034] In each inference stage, the sample set is expanded according to the needs, and the method of selecting small samples based on similarity to enhance the large model is adopted to improve the accuracy of inference;

[0035] Verify the correctness of the generated SQL statements, including large model self-correction, mask replacement, and SQL statement verification.

[0036] The present invention also discloses a hardware device for realizing SQL statement generation based on thought chain and small sample enhancement, comprising:

[0037] A processor configured to execute computer program instructions to implement the following functions:

[0038] Extract entity information related to the database schema from natural language queries;

[0039] Generate SQL statements based on the extracted entity information and database schema;

[0040] Verify the correctness of the generated SQL statements;

[0041] A memory for storing the computer program instructions and data generated during the processing, including a professional term library, a Text-to-SQL sample set, generated SQL statements and intermediate data thereof;

[0042] An input interface for receiving natural language queries and database schema information;

[0043] Output interface, used to output the generated SQL statements and verification results;

[0044] Communication interface, used to connect and exchange data with external databases in order to execute generated SQL statements and verify their correctness.

[0045] Compared with the prior art, the present invention has the following beneficial effects:

[0046] 1 Improve the accuracy of SQL statement generation:

[0047] By establishing a professional term library and constructing an entity recognition model, the present invention can accurately extract entity information related to the database model from natural language queries, avoiding misunderstanding and misuse of professional terms, thereby improving the accuracy of SQL statement generation.

[0048] The layer-by-layer reasoning and multi-layer classification strategies, as well as the method of selecting small samples based on similarity to enhance the large model, further improve the model's reasoning ability and correctness when generating complex SQL statements.

[0049] 2 Improve the efficiency of SQL statement generation:

[0050] The present invention fine-tunes the BERT model to enable it to better understand and generate SQL statements suitable for specific query requirements, thereby significantly improving the efficiency of SQL statement generation.

[0051] During operation, the system can automatically correct and verify the generated SQL statements, reducing the need for manual intervention and further improving overall processing efficiency.

[0052] 3 Reduce the difficulty of using the database:

[0053] By directly converting natural language queries into SQL statements, the present invention greatly reduces the difficulty of using the database, allowing non-professionals to easily perform data query and analysis.

[0054] The system provides friendly input and output interfaces, allowing users to easily input natural language queries and obtain generated SQL statements and verification results.

[0055] 4 Enhance the applicability and scalability of the system:

[0056] The present invention is not only applicable to generating simple SQL query statements, but also can process complex SQL statements containing nested queries and aggregate functions, and has strong applicability.

[0057] The system can be expanded and customized according to actual needs, such as adding new professional terminology libraries, optimizing entity recognition models, etc., to meet the needs of different application scenarios.

[0058] 5. Improve the convenience and security of data query:

[0059] By automatically generating and verifying SQL statements, the present invention makes data query more convenient and efficient, while also improving the security of the query process and avoiding errors and loopholes caused by manually writing SQL statements.

[0060] The system's connection and data exchange functions with external databases enable users to easily execute generated SQL statements and obtain query results, further improving the convenience and practicality of data query. BRIEF DESCRIPTION OF THE DRAWINGS

[0061] Figure 1 The overall operation flow chart of the system of the present invention;

[0062] Figure 2 Sample set Q examples;

[0063] Figure 3 Example of the first stage sample set;

[0064] Figure 4 Example of the second stage sample set;

[0065] Figure 5 Example of the EASY sample set in the third phase;

[0066] Figure 6Example of the MEDIUM sample set in the third phase;

[0067] Figure 7 Example of the HAED sample set in the third phase;

[0068] Figure 8 SchemaCOT and Schema Links reasoning complete hint words;

[0069] Fig. 9 SchemaCOT and Schema Links reasoning complete hint words (continued);

[0070] Fig.10 classificationCOT and classification label reasoning complete prompt words;

[0071] Fig.11 classificationCOT and classification label reasoning complete prompt words (continued);

[0072] Fig.12 SQL reasoning complete hint words;

[0073] Fig.13 SQL reasoning complete hint words (continued 1);

[0074] Fig.14 SQL reasoning complete hint words (continued 2). DETAILED DESCRIPTION

[0075] Embodiments of the present invention are described in detail below, examples of which are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are intended to be used to explain the present invention, and should not be construed as limiting the present invention.

[0076] Example

[0077] A SQL statement generation system based on thought chain and small sample enhancement includes three main modules: entity recognition module, query generation module and SQL verification module. The system operation process is as follows Figure 1 .

[0078] 1Entity Recognition Module:

[0079] The entity recognition module of the present invention is used to extract entity information related to the target database schema from natural language queries and to express it in a standardized manner. The implementation of the module includes the following steps:

[0080] ①Training entity recognition model

[0081] By building a model based on named entity recognition (NER), we can identify professional terms, synonyms and synonyms in natural language queries and unify their expressions. The model training data comes from standard books and expert experience in the professional field, forming a professional term library to provide vocabulary support for entity recognition.

[0082] ② Noun entity set extraction

[0083] Before SQL is generated, the NER model is used to parse the input natural language question and extract the noun entity set related to the database model, which is denoted as N = {n1, n2, ..., n m On the one hand, the set N provides key input for generating database schema links in the subsequent reasoning stage. On the other hand, when processing complex samples, the nouns in the set can be used as masks to participate in the SQL generation process. In the SQL post-processing module, the noun mask is replaced with the actual database query value to ensure that the generated SQL statement is accurately mapped to the target database schema.

[0084] Through the processing of the entity recognition module, natural language queries are standardized into structured entities related to the target database, providing important support for the subsequent query generation module.

[0085] 2.SQL query generation module:

[0086] The first step of the query generation module is to train a fine-tuning model. First, we construct a Text-to-SQL sample set Q = {(q i ,s i , D i ), where q i Represents a natural language question, s i Indicates the corresponding SQL query statement, D i Represents the corresponding database schema. Provides the data foundation for supervised learning for the model. In the sample set, for noun queries with complex semantics or abbreviations, noun masking is used to replace the corresponding query value with a noun, and mask replacement is performed through the post-processing module after SQL generation to improve the accuracy of the generated SQL statements. Based on this sample set, the BERT model of the transform architecture is fine-tuned to obtain the Text-to-SQL fine-tuned large model, denoted as M. The fine-tuned model can better understand and generate SQL statements suitable for specific query requirements.

[0087] Based on the fine-tuned model, the system performs reasoning and completes the generation of SQL statements. In the SQL reasoning process, we adopted three stages: the first stage: generating Schema thinking chain (Chain of Thought, COT) and Schema Links, the second stage: generating classification thinking chain (ClassificationCOT) and classification labels, and the third stage: generating SQL based on classification labels.

[0088] In order to enhance the reasoning ability of the model, a sample set is added to the input of each reasoning stage. According to the needs of the model's intermediate process, the sample set is expanded, and subsequent reasoning is based on the expanded sample set. An example of the expanded sample set is as follows Figure 2 , expressed as:

[0089] Q={(q i , N i ,SchemaCOT i , SL i , ClassCOT i , Label i , SqlCOT i s i , D i )}

[0090] Where N i SchemaCOT is the set of related nouns output by the entity recognition module. i For reasoning about the chain of thought of Schema Links, SL i For Schema Links, ClassCOT i The thought chain for classifying the difficulty of the problem, Label i For classification labels, SqlCOT i A chain of thoughts for generating SQL statements.

[0091] The goal of generating SQL based on the sample set is to maximize the probability that the large language model M generates correct SQL based on question q on database D. Selecting the optimal example sample set Q′ from Q plays an important role in improving the accuracy of reasoning of the large model.

[0092] The present invention adopts a method of selecting small samples based on similarity to enhance the large model. Compared with randomly selecting samples, selecting samples based on similarity is easier for the large model to get effective prompts. Therefore, the sample selection of the present invention adopts a vector cosine similarity matching method to vectorize the question and the sample set question, and calculate the cosine similarity. When the cosine similarity is greater than the threshold, it is considered similar, and the first k samples that meet the conditions are selected as the input sample set:

[0093] Q′=TopK{Q,sim(q,q i )>τ}

[0094] Among them, sim(q,q i ) represents the problem q and the sample problem q i Similarity calculation (such as using vector cosine similarity), s i is the corresponding SQL query, D i For the relevant database pattern information, this sample selection method is recorded as the sample selection function σ, and all optimal sample selections in the reasoning process follow this principle.

[0095] (2) Reasoning process

[0096] The reasoning phase consists of three sub-phases, each of which utilizes a different reasoning mechanism to gradually generate SQL queries.

[0097] Phase 1: SchemaCOT and Schema Links

[0098] In the first stage of reasoning, the first stage sample set Q is established first , including natural language questions q i 、Noun N i SchemaCOT i , and Schema Links (SL i ), for example Figure 3 .

[0099] Q first ={(q i , N i ,SchemaCOT i , SL i ,)},Q first ∈Q

[0100] The second stage: classificationCOT and classification label generation.

[0101] In the second stage of reasoning, the second stage sample set Q is established second , including natural language questions q i , Category Label i , ClassCOT i , and SL i Combination, for example Figure 4 .

[0102] Q second ={(q i , SL i , ClassCOT i , Labeli )}, Q second ∈Q

[0103] Input question q and database link SL to model M q , database mode D i and small sample Q′ second , derive the classification thinking chain ClassCOT q and the category label LABEL q .

[0104] ClassCOT q , LABEL q =M(q,SL q , D i , Q′ second ), Q′ second =σ(Q second )

[0105] ClassCOT q Link to the SL q ) The table name required is denoted as T = {T1, T2, ..., T n}, and whether there is a sub-problem, denoted by q 子问题 , and then derive the classification label according to the preset classification strategy. The specific classification strategy is that if ClassCOT q If the number of tables involved in the ClassCOT exceeds 1, a JOIN connection is required; q contains sub-questions, the query needs to be nested.

[0106] Based on these conditions, the final classification label Label can be defined as follows:

[0107]

[0108] This label generation process provides clear classification guidance for subsequent SQL generation to ensure that the model adopts appropriate reasoning paths on different tasks of EASY, MEDIUM, and HARD classes.

[0109] Phase 3: SQL generation based on classification labels

[0110] In the third stage, we first select the corresponding sample set according to the Label tag (such as EASY, MEDIUM, HARD), and the selection strategy adopts the sample selection function σ described above. Then, based on the table name T extracted in the second stage, we extract the schema of the corresponding table from the database schema, and finally apply different SQL generation thinking chains to generate SQL. Through this method, the fine-tuning model can make full use of similar sample sets and simplified database schemas to improve the accuracy and efficiency of SQL generation.

[0111] (1) Define the sample set

[0112] In the third stage of reasoning, the third stage sample set Q is established third , including natural language questions q i , Category Label i , generate SQL statement thinking chain SqlCOT i , and SL i Combination.

[0113] Q third ={(q i , SL i , SqlCOT i , Label i )}, Q third ∈Q

[0114] According to the three Label labels, Q third Classification, and after screening by the sample selection function σ, we can get the sample set combination method of various labels. Figure 5 , 6 , 7, can be expressed as:

[0115]

[0116] (2) Define the database schema

[0117] In the second stage of ClassCOT q The required table name set T = {T1, T2, ..., T n The corresponding database schema is extracted from all database information and only contains the schema information related to these tables, which is defined as:

[0118] D q =D T ∈D i |T={T1,T2,...,T n}

[0119] (3) Define model input parameters

[0120] For EASY problems, there is no need for SQL thinking chain (SqlCOT q ), input question q and database link SL to model M q , database mode D q and small sample Q easy , you can generate SQL.

[0121] For MEDIUM type problems, input the problem q and the database link SL into the model M.q , database mode D q , SQL generation thinking chain SqlCOT q and small sample Q medium , generate SQL. At this time SqlCOT q It is a description of the QL generation idea.

[0122] For HARD problems, first perform ClassCOT on them in the second stage. q The inferred sub-problem (q 子问题 ) Repeat the above steps to generate sub-SQL, then combine the sub-SQL with the sub-question and fill it into SqlCOT q .

[0123] SqlCOT q =(q 子问题 , SQL 子问题 ,)Finally, question q, SL q 、SqlCOT q , sample set Q label and database schema D q Input the fine-tuning model M and generate the target SQL query SQL q :

[0124]

[0125] Examples of complete hint words inferred by SchemaCOT and Schema Links are as follows: Figure 8 and 9 As shown;

[0126] The complete prompt word example of classificationCOT and classification label reasoning is as follows Fig.10 and 11 As shown;

[0127] SQL reasoning complete hint words example Fig.12 , 13 and 14.

[0128] 3.SQL post-processing module

[0129] The specific steps are as follows:

[0130] (1) Large model self-correction

[0131] After the SQL is generated, in order to improve the accuracy of the generated SQL, it is automatically corrected through a large model by combining the database schema, questions, Schema Links and the generated SQL. This module uses regularized matching to correct information such as column names and query values ​​that are prone to errors. The corrected SQL is finally connected to the database and executed to verify its accuracy.

[0132] (2) Mask replacement

[0133] It is worth noting that in the process of generating SQL, for some abbreviations that are highly professional and difficult for large models to understand, we made corresponding replacements when making the sample set. The query generation module obtained the masked SQL, and the mask and the real value were replaced in the SQL verification module.

[0134] (3) SQL verification

[0135] The SQL statements that have undergone self-correction and mask replacement are connected to the database for verification. If they are executed correctly, the generation is successful. If they cannot be executed correctly, the error message and the SQL statement are given to the large model for further correction.

[0136] The above are only preferred embodiments of the present invention, and the protection scope of the present invention cannot be limited thereto. That is, any simple equivalent changes and modifications made according to the claims and the content of the invention still fall within the protection scope of the patent application of the present invention.

Claims

1. A SQL statement generation system based on thought chain and small sample enhancement, characterized in that: include: 1) Entity recognition module, which is used to extract entity information related to the database schema from natural language queries; 2) a query generation module, used to generate SQL statements based on the extracted entity information and database schema; 3) SQL verification module, used to verify the correctness of the generated SQL statements.

2. The SQL statement generation system according to claim 1, characterized in that: The entity recognition module 1) further comprises: Train entity recognition models to identify professional terms, synonyms, and synonyms in natural language queries by building models based on named entity recognition; Noun entity set extraction, uses the trained entity recognition model to parse natural language queries and extract noun entity sets related to the database pattern.

3. The SQL statement generation system according to claim 1, characterized in that: The 2) query generation module further comprises: Fine-tune the model and prepare the dataset. Fine-tune the BERT model by building a Text-to-SQL sample set to obtain a large Text-to-SQL fine-tuned model. The reasoning process adopts layer-by-layer reasoning and multi-layer classification strategies, including generating Schema thinking chains and Schema Links, generating classification thinking chains and classification labels, and generating SQL statements based on classification labels; including: expanding the sample set according to needs in each reasoning stage, and adopting the method of selecting small samples based on similarity to enhance the large model to improve the accuracy of reasoning.

4. The SQL statement generation system according to claim 3, characterized in that: The reasoning process also includes the following process: The first stage generates Schema thinking chains and Schema Links to establish links between database tables, columns, and query values; The second stage generates classification thought chains and classification labels, and derives classification labels based on database links and preset classification strategies; The third stage generates SQL statements based on classification labels, selects corresponding sample sets according to classification labels, and generates target SQL queries.

5. The SQL statement generation system according to claim 3, characterized in that: The 3) SQL verification module further includes: Large model self-correction, combining database schema, questions, Schema Links and generated SQL statements for automatic correction; Mask replacement: replace the mask in the generated SQL statement to ensure that the generated SQL statement is accurately mapped to the target database schema; SQL statement verification: connect the corrected SQL statement to the database for execution verification to ensure the correctness of the SQL statement.

6. A SQL statement generation method based on thought chain and small sample enhancement, characterized in that: include: S1 extracts entity information related to the database schema from natural language queries; S2 generates SQL statements based on the extracted entity information and database schema; S3 verifies the correctness of the generated SQL statement.

7. The SQL statement generation method according to claim 6, characterized in that: The step S1 of extracting entity information further includes: Train entity recognition models to identify professional terms, synonyms, and synonyms in natural language queries by building models based on named entity recognition; The trained entity recognition model is used to parse natural language queries and extract the set of noun entities related to the database schema.

8. The SQL statement generation method according to claim 6, characterized in that: The generating of SQL statements in S2 further comprises: Build a Text-to-SQL sample set, fine-tune the BERT model, and obtain a large Text-to-SQL fine-tuned model; SQL statements are generated using layer-by-layer reasoning and multi-layer classification strategies, including generating Schema thinking chains and Schema Links, generating classification thinking chains and classification labels, and generating SQL statements based on classification labels.

9. The SQL statement generation method according to claim 6, characterized in that: The SQL statement generated in S2 also includes: In each inference stage, the sample set is expanded according to the needs, and the method of selecting small samples based on similarity to enhance the large model is adopted to improve the accuracy of inference; Verify the correctness of the generated SQL statements, including large model self-correction, mask replacement, and SQL statement verification.

10. A hardware device for realizing SQL statement generation based on thought chain and small sample enhancement, characterized in that: include: A processor configured to execute computer program instructions to implement the following functions: Extract entity information related to the database schema from natural language queries; Generate SQL statements based on the extracted entity information and database schema; Verify the correctness of the generated SQL statements; A memory for storing the computer program instructions and data generated during the processing, including a professional term library, a Text-to-SQL sample set, generated SQL statements and intermediate data thereof; An input interface for receiving natural language queries and database schema information; Output interface, used to output the generated SQL statements and verification results; Communication interface, used to connect and exchange data with external databases in order to execute generated SQL statements and verify their correctness.

Images