Natural language structured query method based on two-stage trial and error learning
By employing a two-stage trial-and-error learning method, a high-quality dataset and a self-correction mechanism were constructed, which solved the problems of accuracy and adaptability in natural language to SQL queries, thereby improving the system's query accuracy and response speed.
Patent Information
- Application Number
- CN202511098160.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-08-06
- Publication Date
- 2025-11-07
AI Technical Summary
Existing natural language to SQL query technologies suffer from several drawbacks, including generating SQL statements that do not reflect the original meaning of the natural language, limited adaptability to database changes, lack of effective error correction mechanisms, and high resource consumption in multi-turn interaction frameworks.
A two-stage trial-and-error learning approach is adopted. In the offline stage, a domain-specific original translation dataset is constructed, and high-quality samples are generated through data augmentation and classifier selection. In the online stage, SQL statements are generated and corrected by combining parameter weight configuration and self-correction mechanism.
It improves query accuracy and system responsiveness, adapts to complex query scenarios, reduces resource consumption, and achieves dynamic adaptation and rapid response.
Smart Images
Figure CN120910077A_ABST
Abstract
Description
TECHNICAL FIELD
[0001] The present application relates to the field of natural language, and in particular to a natural language structured query method based on two-stage trial and error learning. BACKGROUND
[0002] The core goal of the natural language question to SQL query (Text-to-SQL) translation technology is to automatically convert the question posed by the user in natural language into an executable SQL statement. The significance of this technology lies in its ability to provide a direct database access method for users without professional knowledge, allowing them to easily retrieve the required data without the need to learn complex SQL syntax. In the current context of explosive data growth and the increasing diversification of database application scenarios, the demand for data query through natural language is becoming increasingly urgent.
[0003] Thanks to the rapid development of large language models, the natural language to SQL conversion technology has made significant progress, with both accuracy and processing efficiency being significantly improved. However, this technology still faces some challenges: the generated SQL statements may not conform to the original intention of the natural language, the adaptability to database changes is limited, and there is a lack of effective error correction mechanism. In addition, how to maintain the response speed of the system while ensuring the accuracy of the query is also a key problem that needs to be solved.
[0004] In recent years, various mainstream solutions have emerged in the field of Text-to-SQL based on large language models, but each solution has its own shortcomings: single-round Prompt generation method directly generates SQL from user questions in a single prompt without the need for learning, but lacks the integration of domain knowledge and cannot fully adapt to different database architectures, limiting the generalization ability of the model; RAG-style external knowledge assistance method introduces RAG (Retrieval-Augmented Generation) to expand the prompt by retrieving external information such as database documents, examples, or API documents. This method can provide additional context and domain knowledge for large models, but it faces the problem of unstable results. Some complex Text-to-SQL frameworks currently use multiple rounds of interaction with large models to generate and correct errors multiple times to tap the potential of the model and improve its performance. However, this method requires a large number of interactions with large models, significantly increasing the computational power, time consumption, and deployment and management costs of the framework. The above solutions each focus on optimizing a certain aspect of the Text-to-SQL process, but often overlook the overall process coordination. Single-round generation lacks feedback and self-correction ability, RAG method faces challenges in retrieval efficiency and noise control, and the multi-round interaction framework causes excessive resource consumption, resulting in technical defects in generalization, structure understanding, and execution efficiency of existing systems. SUMMARY
[0005] To solve the above technical problems, the application provides a natural language structured query method based on two-stage trial and error learning.
[0006] The technical scheme adopted by the application is: The application comprises the following steps: S1, database schema information of a database is obtained, preset samples and the database schema information are input into a large language model to generate a preliminary question pair, and then the preliminary question pair is sequentially subjected to data augmentation processing and screening annotation processing to construct a translation original data set; S2, a plurality of reference question pairs are selected by searching and processing the translation original data set according to a natural language question to be translated, and the plurality of reference question pairs are input into the large language model to obtain a large language model after learning; S3, the natural language question to be translated is input into the large language model after learning according to different preset parameter weight configurations to generate SQL statements of the respective parameter weight configurations; S4, the SQL statements of all parameter weight configurations are subjected to self-correction processing to obtain a plurality of candidate SQL statements, each candidate SQL statement is subjected to statement execution to obtain an execution result of the respective candidate SQL statement, and all execution results are compared to obtain a target SQL statement.
[0007] The samples and the preliminary question pair each comprise a natural language question, background knowledge and an SQL statement; the database schema information comprises a relational structure, a data type and a data sample of the database.
[0008] The step S1 specifically comprises: S11, database schema information of a database is obtained, preset samples and the database schema information are input into a large language model to generate a plurality of preliminary question pairs; S12, all preliminary question pairs are subjected to data augmentation processing to obtain a plurality of data-augmented preliminary question pairs; S13, the SQL statements in the data-augmented preliminary question pairs are respectively executed, and the preliminary question pairs whose SQL statements cannot be executed are screened out to obtain a plurality of effective preliminary question pairs; S14, a classifier is used to evaluate the difficulty of each effective preliminary question pair and extract keywords to obtain the difficulty and the keywords of the respective effective preliminary question pair, and each effective preliminary question pair and the difficulty and the keywords thereof are combined to construct a translation original data set.
[0009] The classifier in the step S14 adopts a BERT neural network model.
[0010] The step S2 specifically comprises: S21, predicting the correlation probability of the natural language question to be translated and all sub-class pattern information in the database according to the natural language question to be translated through the connector, and selecting the sub-class pattern information in the database pattern information with the largest correlation probability; S22, selecting a number of reference question pairs by retrieving and processing in the translation original data set according to the sub-class pattern information in the database pattern information with the largest correlation probability; S23, inputting the number of reference question pairs into the large language model to obtain the learned large language model.
[0011] The step S3 is specifically: S31, setting a parameter weight as a parameter weight configuration combination in different dimensions of the natural language to be translated; S32, setting a plurality of different parameter weight configuration combinations, and inputting the natural language question to be translated into the learned large language model according to different parameter weight configuration combinations to generate SQL statements of each parameter weight configuration combination.
[0012] The self-correction processing is specifically: performing syntax checking processing on the SQL statements of all parameter weight configuration combinations, and checking and correcting the table name and column name in the SQL statements of all parameter weight configuration combinations according to the database pattern information, and taking the corrected SQL statement as a candidate SQL statement.
[0013] The comparison processing is specifically: comparing the execution results of each candidate SQL statement, if the execution results of any two candidate SQL statements are completely the same, then selecting one of the candidate SQL statements with the same execution result as the target SQL statement, if the execution results of all candidate SQL statements are not completely the same, then selecting the candidate SQL statement generated by the preset optimal parameter weight configuration combination in the parameter weight configuration combination as the target SQL statement.
[0014] The step S1 is performed in the offline stage, and the steps S2-S4 are all performed in the online stage.
[0015] The beneficial effects of the present application are: The present application adopts a two-stage learning mechanism, in the offline stage, a high-quality translation original data set specific to the field is constructed, and rich reference examples are provided, the understanding ability of the model to complex queries and different expression modes is improved, and it is better adapted to specific field data.
[0016] In the online stage, the online matching corresponds to the question and answer pair and the database schema information, and the prompt word is constructed. The multi-dimensional information such as semantics, keywords, fields, and difficulty is introduced in the construction of the prompt, and different parameter weight configurations are used for combined retrieval. Dynamically learn the required information, significantly improve the accuracy of query generation, and at the same time ensure that the system can respond to query requirements in new scenarios in a timely manner.
[0017] The accuracy of the system is improved through the self-correction function and SQL query candidate mechanism.
[0018] The online stage can be dynamically updated according to specific application scenarios, with the ability of dynamic self-adaptation. BRIEF DESCRIPTION OF DRAWINGS
[0019] Figure 1 The overall framework flowchart is shown in the figure; Figure 2 The data augmentation flowchart is shown in the figure; Figure 3 The database intelligent interaction framework based on two-stage trial and error learning is shown in the figure. DETAILED DESCRIPTION
[0020] The present application will be further described below in conjunction with the drawings and examples. The modes of the present application include but are not limited to the following examples.
[0021] As shown in Figure 1 and Figure 3 , the present embodiment includes the following steps: S1, offline learning mechanism based on data augmentation: obtaining database schema information of the database, inputting pre-set different difficulty, different keyword examples and database schema information into a large language model to generate preliminary question pairs, and then sequentially performing data augmentation processing and screening annotation processing on the preliminary question pairs to construct a translation original dataset; The example is a set of natural language questions and corresponding SQL statement pairing data arranged in advance, that is, the demonstration data used for training. Using the existing training data as an example, the large model is interacted with the database to generate preliminary questions and corresponding SQL statements.
[0022] The example and the preliminary question pair both include natural language questions, background knowledge and SQL statements; the database schema information contains the relationship structure, data type and data sample of the database.
[0023] S11, obtaining database schema information of the database, inputting pre-set examples and database schema information into a large language model to generate a plurality of preliminary question pairs; Obtaining the database schema information of the database, that is, the schema diagram of the database, ensures that the large model can understand the structure and relationship of the database.
[0024] S12, data augmentation is performed on all preliminary question pairs to obtain a plurality of data-augmented preliminary question pairs; S13, the SQL statements in the data-augmented preliminary question pairs are executed respectively, and the preliminary question pairs that cannot be executed are screened out to obtain a plurality of valid preliminary question pairs; S14, the difficulty of each valid preliminary question pair is evaluated and the keywords are extracted by the trained classifier to obtain the respective difficulty and keywords, and each valid preliminary question pair and the respective difficulty and keywords are combined to construct a translation original data set. Execute the sample pair, test the accuracy of the generated sample, and evaluate the difficulty and classification of the correct sample to ensure that the translation original data set covers questions of various difficulty levels.
[0025] S2, based on the translation original data set, retrieve the enhanced large language model prompt construction: according to the natural language question to be translated, select a plurality of reference question pairs in the translation original data set by retrieval processing, and input the plurality of reference question pairs as context prompts into the large language model to obtain a learned large language model through context learning; S21, according to the natural language question to be translated, the connector predicts the correlation probability between the natural language question to be translated and all sub-class pattern information in the database, and selects the most relevant sub-class pattern information in the database pattern information; S22, according to the most relevant sub-class pattern information in the database pattern information, a plurality of highly relevant reference question pairs are selected in the translation original data set by retrieval processing according to the current natural language question to be translated; Highly relevant specifically refers to a question pair with a correlation degree exceeding a preset threshold.
[0026] S23, input the plurality of reference question pairs as context prompts into the large language model to obtain a learned large language model through context learning.
[0027] S3, input the natural language question to be translated into the learned large language model according to the preset different parameter weight configurations to generate SQL statements of each parameter weight configuration; S31, set the parameter weight as a parameter weight configuration in different dimensions (such as semantic correlation, difficulty correlation, keyword correlation, etc.) of the natural language to be translated; S32, set a plurality of different parameter weight configurations, and input the natural language question to be translated into the learned large language model according to different parameter weight configurations to generate SQL statements of each parameter weight configuration.
[0028] Generally, steps S2 and S3 include: (1) database schema information connection (2) question pair retrieval two parts. First, the connector will predict the correlation probability of the table and column in the database based on the input natural language question, and select part of the most relevant schema information for connection. Second, the retriever will accurately match the relevant positive and negative cases from the offline stage based on information such as semantics, difficulty, keywords, field, etc. to help the large model learn the inherent information in the database and guide it to generate a preliminary SQL statement.
[0029] S4, error correction, candidate and learning mechanism based on natural language question and database execution result: self-correction processing of all parameter weight configuration combined SQL statements obtains a plurality of candidate SQL statements, each candidate SQL statement is executed to obtain the execution result of each candidate SQL statement, and all execution results are compared to obtain the target SQL statement.
[0030] Generally, the self-correction function based on SQL syntax and database information will update the keywords through rule judgment and update the keywords to make the generated SQL query conform to the standard syntax. At the same time, by matching the database schema information, the wrong table and column name is corrected. Then, the SQL statement obtained after self-correction processing of all parameter weight configuration combined SQL statements is used as a candidate SQL statement, and the statement execution is attempted, and the result is obtained. By selecting the SQL statement with the most consistent return result, the accuracy of the result is improved while maintaining the timeliness of single interaction. After returning the result, the framework will also update the question pair in this interaction process to the translation original data set based on the execution result and user feedback, and realize the continuous learning in the online stage.
[0031] The large language model in this embodiment adopts GPT or Deepseck or Qianwen.
[0032] The database schema information is mainly composed of the relationship structure, data type and data sample of the database.
[0033] The preset sample is mainly composed of a natural language question, background knowledge and a SQL statement.
[0034] The preliminary question pair is mainly composed of a natural language question, background knowledge and a SQL statement.
[0035] The translation original data set is mainly composed of a large number of initial question pairs and corresponding difficulty, field and type labels.
[0036] As shown in Figure 2 , in this embodiment, the data augmentation processing includes question rewriting, direct augmentation, template modification and template generation.
[0037] The classifier in step S14 adopts a BERT neural network model.
[0038] The self-correction processing specifically comprises: performing syntax checking processing on the SQL statements of all parameter weight configuration combinations, and checking and correcting table names and column names in the SQL statements of all parameter weight configuration combinations according to database schema information, and taking the corrected SQL statements as candidate SQL statements.
[0039] The comparison processing specifically comprises: comparing the execution results of the respective candidate SQL statements, if the execution results of any two candidate SQL statements are completely same, then selecting one of the candidate SQL statements with the same execution results as a target SQL statement, if the execution results of all candidate SQL statements are not completely same, then selecting the candidate SQL statement generated by the preset optimal parameter weight configuration combination in the parameter weight configuration combinations as the target SQL statement.
[0040] Step S1 is performed in an offline stage, and steps S2-S4 are all performed in an online stage. By introducing the two-stage learning method, the present technology fully taps the potential of the large model, and respectively improves the performance of the large language model in database information mining, information acquisition and query accuracy. Through the offline learning stage, the present technology deeply mines the internal information of the database, and constructs a translation original dataset covering different difficulties and different keywords to provide similar examples for the subsequent online stage, so as to improve the timeliness and accuracy of natural language-SQL statement conversion.
[0041] The present technology uses a two-stage trial-and-error learning database intelligent interaction framework to improve the accuracy of natural language queries and the system response ability, so that the system can adapt to domain-specific database modes and complex query scenarios, and provide an effective error correction mechanism. The entire framework learns through two stages of trial and error, that is, in the offline stage, the database interaction is used to build a translation original dataset, and an unsupervised database query sample pair (including correct cases and error examples) is generated; in the online stage, the positive and negative cases related to the natural language question are accurately matched from the translation original dataset, the large model is assisted to intelligently query the database, and self-correction is realized.
[0042] In the offline learning stage, the present technology pre-interacts the large model with the database to generate question and SQL query sample pairs covering different difficulties and different types. In this process, various data augmentation methods are used to construct a high-quality translation original dataset.
[0043] In the online application stage, the trained classifier is first used to assess the difficulty of the user's natural language question. Then, the natural language question is linked to the database structure through an optimized linking model, and the relevant table and column information is extracted. According to the characteristics of the question (difficulty, type, and involved data), the most relevant positive and negative cases are retrieved from the translated original dataset as context information for the large model. Combining the natural language question, schema linking results, and retrieved examples, the large language model generates a preliminary SQL query. Multiple candidate SQL queries are generated in parallel, and the self-correction function is used to correct the statements. The most likely answer is selected by considering the timeliness and accuracy of the execution results. The query process and user feedback are recorded, and valuable interaction information is added to the translated original dataset to achieve continuous optimization of the framework.
[0044] Embodiment: Template 1 (corresponding to step 1) Instructions: According to the provided question pair and question template information, extract the corresponding natural language keywords and SQL statement keywords. Based on the analysis of the question pair and the provided database schema information, generate a natural language question and the corresponding SQL query statement that conforms to the database context.
[0045] Database schema: {Database schema information used in the translated original dataset} Question pair: {Question pair containing natural language queries and SQL statements} Question template information: {Statement skeleton for extracting SQL keywords} Template 2 (corresponding to step 2 question rewriting) Instructions: According to the provided question pair and the returned results after executing the SQL query, determine whether the requirements of the original question are met. If consistent, modify the original question without changing the meaning to make it more consistent with natural language query habits; if not consistent, prompt an error.
[0046] Database schema: {Database schema information used in the translated original dataset} Question pair: {Question pair containing natural language queries and SQL statements} Embodiment: Take the college information-related database as an example to illustrate the construction process of the translated original dataset 1) Constructing prompt input: First, according to the difficulty classification, preset the instruction prompt word, then randomly select the preset question pair for the sample, and add the schema information of the database. The synthesized prompt is input into the large language model. The prompt is, for example: simple difficulty question pair generation, sample question pair, natural language question: "Tell me the name of the food with the most quantity", SQL statement: "SELECT name FROM food ORDER BY quantity DESC LIMIT 1;", and there is schema information of the college database.
[0047] 2) Generating target database related question pairs: The large language model returns the generated question pairs containing target database information. For example: natural language question: "Tell me the name of the college with the most students", SQL statement: "SELECT name FROM college ORDER BY quantity DESC LIMIT 1;" 3) Rewriting natural language questions: Re-input the rewriting instructions and generated question pairs into the large model, and return question pairs that are more consistent with natural language habits and database context. For example, after modification, the natural language question is: "Which college has the most students?" 4) Execute the generated SQL query: Execute the generated SQL query in the target database, confirm that there are no exceptions during execution, and return a non-empty result, thereby ensuring that the information of the translated original data set is valid.
[0048] Through the above way, we generate question pairs based on the target database and construct the corresponding translated original data set. The translated original data set covers different difficulties, keywords, and fields, thereby extracting the internal information of the target database and providing referenceable data samples, thereby enhancing the accuracy and usability of the query.
[0049] Embodiment: Take the college-related database natural language query question "How many students are in the college with the fewest students?" as an example to illustrate the entire online SQL query generation process.
[0050] Database schema information connection: According to the natural language query question ("How many students are in the college with the fewest students?") and the complete database schema information (including database tables, column information, schema description, and data samples), through the schema connector, predict the probability of each table and the corresponding column being used in the SQL query in the database, and retain a certain number of high-probability tables and columns.
[0051] Question pair retrieval: According to the predicted database schema connection result, combine the natural language query question, input into the retriever, retrieve the difficulty, keyword, and domain relative matching question pair from the translated original data set as prompt word examples, such as the question pair in example 1.
[0052] Generate preliminary SQL query: Combine the natural language query question, database schema connection result, and returned question pair, input into the large model, and generate multiple possible SQL statements in parallel, such as the SQL statement: "SELECT quantity FROM college ORDER BY quantity ASC LIMIT 1;" and the like.
[0053] Correct and select the optimal SQL query: Correct the generated SQL statement by matching the database schema information based on SQL syntax rules, and execute the SQL statement in parallel to obtain the execution result. Return the SQL query with the most execution result overlap and the execution result as the natural language question query result, such as the SQL query statement "SELECT quantity FROM college ORDER BY quantity ASC LIMIT 1;", and the query return result "320".
[0054] The way of automatically generating SQL queries through retrieving question pairs can significantly improve the timeliness and accuracy of data processing. First, compared with traditional methods, it reduces the large amount of time required for manual SQL statement writing and the possibility of errors, and can efficiently and accurately generate SQL query statements that meet the logic through large language models. Second, by constructing a translated original data set, this method can quickly adapt to new data query contexts, extract the internal information of the database, and allow database administrators and users to easily obtain the required data information without deep understanding of the structure of the target database. In addition, compared with other large model-based frameworks, this method only has one interaction process with the large model in the online query stage, greatly improving the response speed of the data query process and helping database users make corresponding decisions more quickly. Finally, the automated and end-to-end framework provides a unified interface for framework deployment, data management, and use, ensuring that database users use consistent interfaces when accessing data and reducing the time and personnel consumption of database management.
[0055] The above describes the present application and its embodiments, which are not limiting, and the embodiments shown in the drawings are only one of the embodiments of the present application, and the actual structure is not limited thereto. In summary, if a person skilled in the art is inspired by it, without departing from the spirit of the present application, without creative design, similar structure and embodiments of the technical solution can be designed, which shall belong to the protection scope of the present application.
Claims
1. A method for natural language structured query based on two-stage trial-and-error learning, characterized in that, The method comprises the following steps: S1, obtaining database schema information of a database, inputting preset samples and the database schema information into a large language model to generate a preliminary question pair, and then sequentially performing data augmentation processing and screening annotation processing on the preliminary question pair to construct a translation original data set; S2, retrieving and processing a plurality of reference question pairs from the translation original data set according to a natural language question to be translated, and inputting the plurality of reference question pairs into the large language model to obtain a learned large language model through context learning; S3, inputting the natural language question to be translated into the learned large language model according to different parameter weight configurations to generate SQL statements of the different parameter weight configurations; S4, performing self-correction processing on the SQL statements of all the parameter weight configurations to obtain a plurality of candidate SQL statements, performing statement execution on each candidate SQL statement to obtain an execution result of the respective candidate SQL statement, and comparing all the execution results to obtain a target SQL statement. 2.The method of claim 1, wherein: The samples and the preliminary question pairs each comprise a natural language question, background knowledge and a SQL statement; and the database schema information comprises a relational structure, a data type and a data sample of the database. 3.The method of claim 1, wherein: The step S1 specifically comprises: S11, obtaining database schema information of a database, inputting preset samples and the database schema information into a large language model to generate a plurality of preliminary question pairs; S12, performing data augmentation processing on all the preliminary question pairs to obtain a plurality of data-augmented preliminary question pairs; S13, executing the SQL statements in the data-augmented preliminary question pairs respectively, and screening out the preliminary question pairs whose SQL statements cannot be executed to obtain a plurality of valid preliminary question pairs; S14, performing difficulty evaluation and keyword extraction on each valid preliminary question pair through a classifier to obtain the difficulty and the keyword of the respective valid preliminary question pair, and combining each valid preliminary question pair with the difficulty and the keyword of the respective valid preliminary question pair to construct a translation original data set. 4.The method of claim 1, wherein: The classifier in the step S14 adopts a BERT neural network model.
5. The method of claim 1, wherein the method further comprises: The step S2 specifically comprises: S21, predicting the correlation probability of a natural language question to be translated and all sub-class schema information in a database through a connector according to the natural language question to be translated, and selecting the sub-class schema information in the database schema information with the maximum correlation probability; S22, retrieving and processing a plurality of reference question pairs from the translation original data set according to the sub-class schema information in the database schema information with the maximum correlation probability; S23, inputting the plurality of reference question pairs into the large language model to obtain a learned large language model through context learning. 6.The method of claim 1, wherein the method further comprises: The step S3 specifically comprises: S31, setting a parameter weight in different dimensions of the natural language to be translated as a parameter weight configuration; S32, setting a plurality of different parameter weight configurations, and inputting the natural language question to be translated into the learned large language model according to the different parameter weight configurations to generate SQL statements of the different parameter weight configurations.
7. The method of claim 1, wherein the method is based on two-stage trial and error learning. The self-correction processing specifically comprises: performing syntax checking processing on all parameter weight configuration combination SQL statements, and checking and correcting table names and column names in all parameter weight configuration combination SQL statements according to database schema information, taking the corrected SQL statements as candidate SQL statements. 8.The method of claim 1, wherein the method further comprises: determining a first set of candidate structures based on the first set of candidate structures and the second set of candidate structures; and determining a final set of candidate structures based on the first set of candidate structures and the second set of candidate structures. The comparison processing specifically comprises: comparing execution results of respective candidate SQL statements, if execution results of any two candidate SQL statements are completely same, then selecting one of the candidate SQL statements with the same execution results as the target SQL statement, if execution results of all candidate SQL statements are not completely same, then selecting a candidate SQL statement generated by a preset optimal parameter weight configuration combination in the selected parameter weight configuration combinations as the target SQL statement. 9.The method of claim 1, wherein: The step S1 is performed in an offline stage, and the steps S2-S4 are all performed in an online stage.