Method for converting natural language into SQL (Structured Query Language) based on execution feedback and iterative optimization

Abstract

The invention provides an execution feedback and iterative optimization-based natural language to SQL (Structured Query Language) conversion method. The method comprises the following steps of: generating a plurality of candidate SQL statements; performing parallel execution on the candidate SQL statements, and capturing grammar error information in execution results and line number classification numbers of different candidate SQL return results; generating an error pattern abstract based on the grammar error information, converting the error pattern abstract into a natural language constraint instruction, and injecting the natural language constraint instruction into a prompt word generated in the next round; when the line number classification number is smaller than or equal to a preset threshold value, a self-consistency voting mechanism is adopted, a candidate SQL set with the largest number of returned same lines is selected, and the SQL with the shortest execution time is selected from the set to serve as final output. According to the method, the accuracy, robustness and execution efficiency of conversion from the natural language to the SQL can be remarkably improved by executing a feedback-driven multi-round iterative optimization mechanism.

Description

Technical Field

[0001] This invention relates to the field of natural language to structured query language technology, and in particular to a natural language to SQL method based on execution feedback and iterative optimization. Background Technology

[0002] The ability to convert Natural Language to Structured Query Language (NL2SQL) serves as a crucial technological support for database interaction and data democratization, and is widely used in intelligent question-answering systems, data analysis platforms, and database management systems. With the development of Large Language Models (LLMs), generative methods based on In-Context Learning (ICL) and Vector Retrieval Recall have gradually become mainstream. Their technical framework covers the entire process from natural language understanding, schema alignment, example retrieval, to SQL generation. Specifically, this technology achieves preliminary modeling of user intent through the collaborative work of semantic parsing, template matching, and example guidance, and leverages the generalization capabilities of LLMs to generate structured query statements, thereby improving the efficiency and accuracy of database access for non-technical personnel.

[0003] However, existing NL2SQL methods directly generate SQL using static prompts without establishing effective execution feedback and iterative optimization mechanisms. This can lead to problems such as syntax errors, semantic deviations, or inconsistent results. Furthermore, in complex scenarios involving multi-table joins and nested queries, they may generate logically flawed SQL statements, impacting system robustness and query reliability. Especially in enterprise database environments, due to complex schema structures, inconsistent field naming, diverse SQL dialects, and user language ambiguity, traditional methods struggle to achieve accurate matching in a single generation and lack the ability to analyze and correct execution results in real time, limiting their effectiveness in practical business scenarios. Summary of the Invention

[0004] The present invention aims to at least partially solve one of the technical problems in the related art.

[0005] This invention proposes a natural language to SQL method based on execution feedback and iterative optimization.

[0006] Another objective of this invention is to propose a natural language to SQL converter based on execution feedback and iterative optimization.

[0007] To achieve the above objectives, this invention proposes a natural language to SQL method based on execution feedback and iterative optimization, comprising: S1, generate multiple candidate SQL statements, which are sampled based on the user's input natural language question, database schema information and a predefined SQL example library; S2, execute the candidate SQL statements in parallel, and capture the syntax error information and the number of rows and categories of the results returned by different candidate SQL statements in the execution results; S3, Generate an error pattern summary based on the grammatical error information, and convert the error pattern summary into a natural language constraint instruction, which is then injected into the prompt words generated in the next round; S4. When the number of rows in each category is less than or equal to a preset threshold, a self-consistent voting mechanism is adopted to select the candidate SQL set that returns the most rows with the same number of rows, and the SQL with the shortest execution time is selected from the set as the final output.

[0008] The natural language to SQL method based on execution feedback and iterative optimization in this invention may also have the following additional technical features: In one embodiment of the present invention, generating multiple candidate SQL statements includes: S11, a diverse sampling strategy based on temperature coefficient adjustment, wherein the temperature coefficient is set during the first round of generation to generate semantically equivalent but structurally differentiated SQL statements; S12 uses a lightweight SQL parser to perform syntax pre-validation on the generated candidate SQL statements and filters out SQL statements with syntax errors.

[0009] In one embodiment of the present invention, the parallel execution of the candidate SQL statements and the capture of syntax error information and the number of rows categorized in the execution results of different candidate SQL statements include: S21, simultaneously submit all candidate SQL statements that have passed the syntax pre-validation to the database for execution, and record the execution time and the number of rows returned for each SQL statement; S22, count the number of rows in the results returned by different candidate SQL statements, where the number of categories is the number of sets of results with different row counts.

[0010] In one embodiment of the present invention, the step of generating an error pattern summary based on the grammatical error information and converting the error pattern summary into a natural language constraint instruction, and injecting it into the prompt words generated in the next round, includes: S31, extract high-frequency error patterns and generate a natural language summary, the summary including error type, scope of impact and correction suggestions; S32, the natural language summary is converted into structured constraint instructions, which are used to restrict the syntax structure or field usage of the SQL generated in the next round.

[0011] In one embodiment of the present invention, when the number of row categories is less than or equal to a preset threshold, a self-consistent voting mechanism is adopted to select the candidate SQL set that returns the most rows with the same number of rows, and the SQL with the shortest execution time in this set is selected as the final output, which includes: S41, Calculate the performance score for each candidate SQL statement, the performance score being a weighted average of execution time and query plan complexity; S42, when outputting the final SQL, an explanation report is generated simultaneously. The explanation report includes the selection criteria, execution performance indicators, and result consistency analysis.

[0012] In one embodiment of the present invention, it further includes: S5, based on vector retrieval recall technology, filters historical query examples from the example database that are semantically similar to the current natural language problem; S6. Based on the SQL structure type and schema matching degree of the historical query examples, perform structural feature filtering on the examples to improve the adaptability of the examples to the current query.

[0013] To achieve the above objectives, a second aspect of the present invention provides a natural language to SQL converter based on execution feedback and iterative optimization, comprising: The candidate SQL generation module is used to generate multiple candidate SQL statements, which are based on diversity sampling of the natural language question input by the user, database schema information and a predefined SQL example library; The parallel execution and result capture module is used to execute the candidate SQL statements in parallel and capture the syntax error information and the number of rows and categories of the results returned by different candidate SQL statements in the execution results; The error pattern processing and prompt word injection module is used to generate an error pattern summary based on the grammatical error information, and convert the error pattern summary into natural language constraint instructions, which are then injected into the prompt words generated in the next round. The self-consistent voting and result selection module is used to select the candidate SQL set that returns the most rows when the number of rows in each category is less than or equal to a preset threshold, and select the SQL with the shortest execution time from the set as the final output.

[0014] The method and apparatus of this invention effectively solve the problem of error accumulation caused by the lack of dynamic verification and correction mechanisms in the natural language to SQL conversion process. Through multi-round iterative optimization driven by execution feedback, the accuracy, consistency and execution efficiency of generated SQL are significantly improved.

[0015] Additional aspects and advantages of the invention will be set forth in part in the description which follows, and in part will be obvious from the description, or may be learned by practice of the invention. Attached Figure Description

[0016] The above and / or additional aspects and advantages of the present invention will become apparent and readily understood from the following description of the embodiments taken in conjunction with the accompanying drawings, wherein: Figure 1 A flowchart illustrating a natural language to SQL method based on execution feedback and iterative optimization provided in this application embodiment; Figure 2 An architecture diagram of a natural language to SQL method based on execution feedback and iterative optimization provided in this application embodiment; Figure 3 This is a structural diagram of a natural language to SQL converter based on execution feedback and iterative optimization, provided in an embodiment of this application. Detailed Implementation

[0017] It should be noted that, unless otherwise specified, the embodiments and features described in the present invention can be combined with each other. The present invention will now be described in detail with reference to the accompanying drawings and embodiments.

[0018] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0019] The following description, with reference to the accompanying drawings, describes a natural language to SQL method and apparatus based on execution feedback and iterative optimization according to an embodiment of the present invention.

[0020] Example 1 Figure 1 This is a flowchart of a natural language to SQL method based on execution feedback and iterative optimization according to an embodiment of the present invention, such as... Figure 1 As shown, it includes: S1, generate multiple candidate SQL statements, which are based on diversity sampling of the natural language question input by the user, database schema information and a predefined SQL example library.

[0021] Specifically, the step of "generating multiple candidate SQL statements, which are based on the user's input natural language question, database schema information and a predefined SQL example library for diversity sampling" is a key step in the natural language to SQL (NL2SQL) conversion process in this invention, aiming to improve the robustness and accuracy of SQL generation through a diversity generation strategy.

[0022] At the technical implementation level, this step employs a diversity sampling mechanism based on a Large Language Model (LLM). It combines user-input Natural Language Questions (NLQ), database schema information (including table names, column names, data types, primary / foreign key relationships, etc.), and a predefined SQL example library to generate multiple candidate SQL statements with structural differences but semantically equivalent meaning. Specifically, the model controls the generation diversity by adjusting sampling parameters (such as the temperature coefficient, typically set to 0.7~1.2), ensuring that SQL statements with different implementations are generated under the same semantics, such as the difference between explicit JOIN and subquery structures. Furthermore, the model references schema linking results and dynamic Few-Shot examples during the generation process to enhance the structural rationality and syntactic correctness of the generated SQL.

[0023] At the parameter level, this step involves several key parameters: the sampling temperature coefficient T is used to adjust the generation diversity, and the number of candidates N is usually set to 5 to 7 to balance diversity and computational overhead; at the same time, the SQL syntax pre-validation module will filter out invalid statements that do not conform to the SQL standard (such as aggregate queries that lack GROUP BY) to ensure that the candidate SQL has preliminary executability.

[0024] At the application level, this step is widely applicable to enterprise-level database query systems, especially in scenarios such as multi-table joins, complex aggregations, and cross-database dialect conversions. By generating multiple candidate SQLs, the system can further perform parallel verification, thereby improving the accuracy and execution efficiency of the final SQL.

[0025] In terms of technical effectiveness, this step effectively alleviates the uncertainty problem of LLM in generating complex SQL structures through diversity generation and pre-verification mechanisms, providing a sufficient basic candidate set for subsequent execution feedback and iterative optimization, and significantly improving the system's adaptability and stability in real business environments.

[0026] Furthermore, S1 includes: S11, a diversity sampling strategy based on temperature coefficient adjustment, wherein the temperature coefficient is set during the first round of generation to generate semantically equivalent but structurally differentiated SQL statements.

[0027] Specifically, the "diversity sampling strategy based on temperature coefficient adjustment" is the core of the multi-candidate SQL generation mechanism in this invention. It aims to control the randomness in the generation process to achieve semantically equivalent but structurally differentiated SQL statements, thereby improving the effectiveness of subsequent execution feedback and self-consistency optimization. In the first round of generation, the temperature coefficient is set between 0.7 and 1.2. This parameter directly affects the smoothness of the probability distribution during decoding by the Large Language Model (LLM). A higher temperature coefficient results in greater diversity in the model output, but may decrease stability; a lower temperature coefficient leads to output concentrated on high-probability paths, increasing structural similarity but limiting innovation. This invention uses a moderately high temperature value in the first round to generate structurally differentiated SQL candidates while ensuring semantic accuracy, providing richer comparison samples for subsequent execution feedback and iterative optimization.

[0028] At the technical implementation level, this step is based on the autoregressive generation mechanism of LLM. By introducing a temperature-adjusted softmax function during the decoding stage, the probability distribution of the token output by the model is smoothed.

[0029] In practical applications, this step is widely applicable to natural language query scenarios in enterprise-level database environments, especially in cases involving multi-table joins, complex aggregations, and field alias mappings. By generating structurally differentiated SQL, it helps identify potential errors in the model regarding table joins, condition filtering, and aggregation granularity. For example, in queries such as "customer order amount," the model might generate SQL containing JOIN operations or SQL using subqueries. Diversity sampling allows for the exploration of multiple implementation paths simultaneously, improving the robustness of the final output.

[0030] The technical value of this step lies in providing a set of SQL candidate sets that differ in structure but are semantically consistent for subsequent execution feedback and self-consistency optimization, thereby enhancing the model's ability to identify and correct error patterns. By reasonably setting the temperature coefficient, this invention achieves a good balance between generating diversity and controlling semantic bias, significantly improving the accuracy and practicality of the NL2SQL task in complex business scenarios.

[0031] S12 uses a lightweight SQL parser to perform syntax pre-validation on the generated candidate SQL statements and filters out SQL statements with syntax errors.

[0032] Specifically, the step of “using a lightweight SQL parser to perform syntax pre-validation on the generated candidate SQL statements and filtering out SQL statements with syntax errors” is a key quality control step in the NL2SQL process of this invention. Its technical implementation is based on an SQL syntax parsing engine and an error pattern recognition mechanism, aiming to quickly identify and eliminate syntax errors before the SQL statement is executed, thereby improving the efficiency and accuracy of subsequent execution feedback and iterative optimization.

[0033] At the technical implementation level, this step typically employs lightweight SQL parsing tools such as ANTLR, SQLGlot, or SQLite parsers to perform lexical and syntactic analysis on the generated candidate SQL statements. The parser first tokenizes the SQL statement, identifying keywords, identifiers, operators, and syntactic structures. It then constructs an Abstract Syntax Tree (AST) to verify whether it conforms to the target database's SQL syntax specifications (such as SQL-92, SQL:2011, or a specific database dialect). If problems such as structural mismatches, missing clauses (e.g., missing GROUP BY), or incorrect table or column references occur during parsing, the SQL statement is marked as syntactically incorrect and filtered out.

[0034] Regarding parameter metrics, this step typically sets the number of candidate SQL statements (N) to 5-7 to ensure diversity and coverage. Error types for syntax validation include, but are not limited to: missing FROM clauses, incomplete JOIN conditions, WHERE clause syntax errors, mismatches between aggregate functions and GROUP BY, and table alias conflicts. Validation results can output error codes (e.g., ERROR 1064), error locations (row number, column number), and error type classifications, facilitating subsequent injection feedback.

[0035] In application scenarios, this step is widely used in conversational data analysis systems, intelligent question-answering platforms, and database interactive interfaces. Especially in complex SQL generation scenarios such as multi-table joins and nested queries, it can effectively prevent invalid SQL from entering the execution stage, reducing database resource consumption and response latency.

[0036] In terms of technical effectiveness, this step significantly improves the robustness and execution efficiency of SQL generation, provides a high-quality candidate set for subsequent execution feedback and self-consistency optimization, and is an important guarantee for realizing the "generation-verification-iteration" closed-loop mechanism.

[0037] S2, execute the candidate SQL statements in parallel, and capture the syntax error information in the execution results and the number of rows and categories of the results returned by different candidate SQL statements.

[0038] Specifically, this step involves executing multiple candidate SQL statements in parallel and capturing syntax error information and the number of rows in the different candidate SQL return results. This is one of the core components of the execution feedback and iterative optimization mechanism in this invention.

[0039] At the technical implementation level, this step uses a database connection pool or batch execution interface to concurrently submit 5-7 candidate SQL statements (typically pre-validated) to the target database system for execution. The execution process of each SQL statement is encapsulated into an independent thread or coroutine to ensure execution efficiency and response speed. During execution, the system monitors the database in real time for the execution status code, error messages (such as ERROR 1064: SQL syntax error), and result set metadata (such as the number of rows, columns, and data types).

[0040] At the parameter level, the system sets key thresholds and evaluation criteria. For example, the default value for the number of row count categories, K, is set to 3 to determine whether the results of candidate SQL queries are within an acceptable range of difference. If the number of rows returned by multiple candidate SQL queries differs by more than K categories (e.g., returning 15, 16, or 120 rows), it is considered that there is a logical inconsistency or structural deviation, requiring further diagnosis. In addition, the system records the execution time (in milliseconds) and execution plan complexity (e.g., the number of logical plan nodes based on the query optimizer) for each SQL query, which is used for subsequent performance evaluation and voting mechanisms.

[0041] At the application level, this step is widely applicable to enterprise-level database query scenarios such as multi-table joins, aggregation queries, and complex subqueries. For example, in a supply chain system, a user might ask for "the total order amount for each customer in the last three months." After the system generates multiple candidate SQL statements, parallel execution can quickly identify row count anomalies caused by missing JOIN conditions or incorrect aggregation granularity, thereby triggering the next round of optimization.

[0042] The technical advantage of this step lies in its ability to quickly identify SQL syntax errors and logical deviations through parallel execution and result classification analysis, providing accurate correction criteria for subsequent feedback injection and model regeneration. Simultaneously, the judgment mechanism based on row count and classification count effectively improves the consistency and robustness of SQL generation, serving as a key technical support for achieving self-consistency optimization and iterative correction.

[0043] Furthermore, S2 includes: S21, simultaneously submit all candidate SQL statements that have passed the syntax pre-validation to the database for execution, and record the execution time and the number of rows returned for each SQL statement.

[0044] Specifically, the step of "simultaneously submitting all candidate SQL statements that have passed syntax pre-validation to the database for execution, and recording the execution time and the number of rows returned for each SQL statement" is one of the core components of the execution feedback-driven multi-candidate iterative optimization mechanism in this invention. Its technical implementation is based on a parallel SQL execution framework and a database performance monitoring interface, aiming to quickly identify logical deviations or performance bottlenecks in candidate SQL statements through actual execution results, providing accurate feedback for subsequent model iterations.

[0045] In some implementations, this step first relies on a lightweight SQL syntax parser (such as ANTLR, SQLGlot, etc.) to pre-validate the generated candidate SQL, filtering out statements with obvious syntax errors. The number of candidate SQLs that pass the validation is usually set to 5-7 to ensure diversity and coverage. Subsequently, the system submits these candidate SQLs to the target database instance for execution in parallel through a database connection pool (such as using JDBC, ODBC, or a native database client). During execution, the system captures the execution time (in milliseconds) and the number of rows returned for each SQL statement in real time through the database's execution log interface or performance monitoring tools (such as MySQL's EXPLAIN, PostgreSQL's pg_stat_statements).

[0046] At the parameter level, the execution time acquisition accuracy should be no less than 1 millisecond to support the differentiation of minor differences in SQL performance; the number of rows returned should be obtained immediately after execution to ensure data consistency. In addition, the system should also set an execution timeout threshold (e.g., 5000ms) to prevent prolonged blocking due to complex SQL structures or large data volumes.

[0047] In application scenarios, this step is suitable for enterprise-level database query systems, especially in scenarios involving multi-table joins, aggregate queries, and complex subqueries. By executing in parallel, it can quickly identify logical errors or inefficient SQL queries, thereby improving overall query accuracy and response efficiency. Technically, this step provides crucial evidence for subsequent consistency checks, error pattern analysis, and feedback injection, significantly enhancing the system's robustness and iterative optimization capabilities in complex business environments.

[0048] S22, count the number of rows in the results returned by different candidate SQL statements, where the number of categories is the number of sets of results with different row counts.

[0049] Specifically, the step of "counting the number of rows and categories of differences in the results returned by different candidate SQL statements" is a key step in the feedback-driven multi-candidate iterative decision-making process of this invention. Its core purpose is to quantify the differences in the execution results of candidate SQL statements to help determine whether to enter the majority consensus voting mechanism or further optimize the generation path. Technically, this step first executes the candidate SQL statements (usually 5-7) that have passed syntax pre-validation in parallel, obtaining the execution result set of each SQL statement. Then, the system extracts the number of rows returned by each SQL statement (i.e., the row count of the result set) and, based on the distribution of the row counts, counts the number of difference categories, i.e., the number of sets of results with different row counts. For example, if the candidate SQL statements return 15, 15, 16, and 120 rows respectively, then the number of difference categories is 3 (15, 16, 120), indicating the existence of three different result sizes.

[0050] The parameter settings for this step include a dynamic threshold K (default K=3), used to determine whether the difference in results is within an acceptable range. If the number of categories ≤ K, it indicates that the candidate SQL has high consistency in execution results and can enter the majority voting mechanism; if the number of categories > K, it indicates that there is a significant logical deviation, and further analysis of the root cause of the difference is required, such as missing JOIN conditions, inconsistent WHERE filtering, or different GROUP BY granularity. The system uses the number of difference categories as part of the execution feedback, combined with error logs, execution time, and other indicators, to construct a multi-dimensional evaluation system to guide the context optimization of the next round of SQL generation.

[0051] In practical applications, this step is widely used in enterprise-level database query systems, especially in complex query scenarios such as multi-table joins and conditional aggregations. It can effectively identify inconsistencies in query results caused by model misunderstandings. Through quantitative analysis of row count differences, the system can determine whether to adjust the JOIN strategy, optimize filtering conditions, or correct the aggregation granularity, thereby improving the accuracy and stability of SQL generation.

[0052] The technical value of this step lies in enhancing the model's ability to perceive the correctness of the query logic by introducing the number of result rows as a feedback signal, thus compensating for the lack of execution verification in traditional NL2SQL methods. Combined with a self-consistency mechanism and a dynamic iteration strategy, this step significantly improves the system's robustness and convergence efficiency in complex business scenarios.

[0053] S3. Generate an error pattern summary based on the grammatical error information, and convert the error pattern summary into a natural language constraint instruction, which is then injected into the prompt word generated in the next round.

[0054] Specifically, the core of this step is to structure the syntax error information generated in the previous SQL generation process, generate an error pattern summary, and convert it into natural language constraint instructions, which are then injected into the prompt words generated in the next round. This guides the Large Language Model (LLM) to avoid similar errors in subsequent iterations, thereby improving the accuracy and robustness of SQL generation.

[0055] At the technical implementation level, this step first involves classifying and identifying syntax errors returned by the database through an error log parsing module. For example, if the error message is "ERROR 1064: You have an error in your SQLsyntax near '...'", the system will extract the error type (such as "GROUP BY missing", "JOIN condition incomplete", "table alias conflict", etc.) and, combined with the SQL fragment in the error context, identify the structural location and semantic cause of the error. Subsequently, the system uses an error pattern clustering algorithm to generate a representative error pattern summary based on the error type, SQL structural features, and schema matching degree. This summary is usually presented in the form of short natural language sentences, such as "must include a GROUP BY clause and unify table aliases".

[0056] At the parameter level, the generation of error pattern summaries depends on error type classification standards (such as SQL-92 syntax specifications), error context window length (usually 10-20 tokens), and semantic compression rate of the summary generation (controlling the summary length, generally 3-5 sentences). When injecting prompt words, the system adopts a dynamic prompt word construction strategy, inserting constraint instructions near the "key decision points" of the prompt words, such as schema descriptions, example SQL, task objectives, etc., to enhance the model's ability to perceive error patterns.

[0057] In application scenarios, this step is widely used in enterprise-level intelligent question-answering systems and database interactive analysis platforms, especially when dealing with scenarios such as multi-table joins, aggregate queries, and complex subqueries. The feedback injection mechanism significantly enhances the model's self-correction capabilities. For example, in a financial risk control system, if a user queries "the top 10 accounts with the highest customer overdue amounts," the model might cause result inflation due to ignoring the JOIN condition. The error summary will then prompt "must include the field linking the customer to the billing table."

[0058] The technical value of this step lies in building an iterative optimization closed loop driven by execution feedback, enabling LLM to have the ability of "error detection - pattern induction - constraint injection - structure correction" during the SQL generation process, thereby effectively improving the syntax accuracy and business consistency of generated SQL. It is a key technical link in realizing a high-precision NL2SQL system.

[0059] Furthermore, S3 includes: S31, extract high-frequency error patterns and generate natural language summaries, the summaries including error types, scope of impact and correction suggestions.

[0060] Specifically, the step of "extracting high-frequency error patterns and generating natural language summaries" is one of the core components of the execution feedback and iterative optimization mechanism in this invention. Its technical implementation is based on structured analysis and pattern summarization of error logs during multiple rounds of SQL generation. In some implementations, this step first captures the execution results of all candidate SQL statements from the previous round in real time, including syntax error information (such as ERROR 1064: SQL syntax error) and logical error characteristics (such as row count differences, missing fields, etc.). Through natural language processing and semantic parsing techniques, the system maps error information to a predefined error type classification system, such as "missing JOIN condition," "aggregate function not bound to GROUP BY," and "column type mismatch." The classification criteria can refer to SQL standards (such as SQL:2011) and error code specifications of database management systems (such as MySQL and PostgreSQL).

[0061] Furthermore, the system employs statistical learning methods to perform frequency analysis on error types, identifying error patterns that occur more frequently than a set threshold (e.g., ≥2 times) in the current round. Simultaneously, by combining the differences in the number of rows in the execution results (e.g., 15 rows vs. 120 rows) and execution time (e.g., >500ms), the system can quantify the scope of the error's impact and determine whether it belongs to a structural error or a performance bottleneck. Finally, the system integrates the above analysis results into a natural language summary, including the error type, scope of impact, and correction suggestions, such as "Warning: 3 / 5 candidate SQL statements have bloated row counts due to missing JOIN conditions (120 vs. 15 rows). Please strictly check the table join logic." This summary is injected as a constraint instruction into the next round of prompts, guiding the large model to generate SQL statements that better conform to the database schema and user intent, thereby significantly improving the system's self-correction capability and generation quality.

[0062] S32, the natural language summary is converted into structured constraint instructions, which are used to restrict the syntax structure or field usage of the SQL generated in the next round.

[0063] Specifically, the core objective of this step is to transform natural language summaries into structured constraint instructions, which are used to restrict the syntax structure or field usage in the next round of SQL generation, thereby improving the accuracy and consistency of the generated SQL. This step is based on the In-Context Learning (ICL) mechanism, which encodes execution feedback information into actionable constraints to guide the Large Language Model (LLM) to avoid identified error patterns in subsequent generation processes.

[0064] At the technical implementation level, this step first performs semantic parsing and pattern recognition on the execution error log. For example, if the SQL generated in the previous round caused an abnormal increase in the number of rows due to the lack of JOIN conditions, the system will extract the error type and convert it into a structured instruction, such as "must include the user_id association between the order table and the customer table". This instruction uses natural language processing techniques (such as dependency parsing and entity recognition) to extract key operation elements (table name, field name, operation type) and map them into the SQL syntax structure. Furthermore, the system can combine SQL syntax tree (AST) analysis to transform constraint instructions into restrictions on the SQL generation path, such as forcing the inclusion of JOIN clauses, limiting the number of tables in the FROM clause, or specifying the granularity level of the GROUP BY field.

[0065] At the parameter level, the generation of constraint instructions depends on the classification accuracy of error logs (e.g., error type identification accuracy must be ≥90%), the semantic matching similarity threshold (e.g., constraint generation is triggered only when cosine similarity is ≥0.75), and a predefined template library of SQL structures (e.g., the priority weights of structures such as JOIN, subqueries, and window functions). Furthermore, the system supports dynamically adjusting constraint strength; for example, allowing for more lenient structural changes in the early stages of iteration (T=0.7), while gradually tightening in subsequent rounds (T=0.3) to enhance the stability of generated SQL.

[0066] In application scenarios, this step is widely applicable to enterprise-level database interaction systems, especially in scenarios involving multi-table joins, complex aggregations, and cross-database dialect adaptation, significantly reducing invalid queries caused by model misunderstandings or syntax errors. By transforming natural language feedback into structured constraints, the system achieves closed-loop optimization from "semantic understanding" to "syntax control," providing an interpretable and iterative enhancement mechanism for NL2SQL tasks.

[0067] The technical value of this step lies in its effective solution to the problem of "generation ends" in SQL generation in LLM. By transforming execution feedback into operable structured instructions, it significantly improves the robustness and accuracy of the model in complex query scenarios, and is a key link in realizing execution feedback-driven iterative optimization.

[0068] S4. When the number of rows in each category is less than or equal to a preset threshold, a self-consistent voting mechanism is adopted to select the candidate SQL set that returns the most rows with the same number of rows, and the SQL with the shortest execution time is selected from the set as the final output.

[0069] Specifically, when the number of rows categorized is less than or equal to a preset threshold, the system employs a self-consistency voting mechanism to determine the final SQL output. This mechanism filters based on the consistency of execution results among multiple candidate SQLs, aiming to improve the accuracy and stability of the generated SQL. In some implementations, this step first counts the number of rows returned after execution of all candidate SQLs that have passed syntax pre-validation, and then divides the results into several categories based on the differences in the number of rows. If the number of categories (i.e., the number of candidate SQLs with different row counts) does not exceed a preset threshold K (usually K=3), then the current candidate SQL is considered to have high consistency in logical structure, and the system enters the majority consistency filtering stage.

[0070] In terms of specific operation, the system first calculates the number of rows returned by each candidate SQL statement and groups SQL statements with the same number of rows into the same category. Then, it counts the number of candidates in each category and selects the set of candidates returning the most rows as the majority consistency set. Within this set, a performance optimization strategy is further introduced: the SQL statement with the shortest execution time among those with the same number of rows is selected as the final output. Execution time is typically evaluated based on the actual time (in milliseconds) from the database's EXPLAIN ANALYZE statement or execution logs, and can be weighted and scored in conjunction with query plan complexity (such as the number of JOINs, subquery nesting levels, and index usage). For example, the formula `score = 0.7 * exec_time + 0.3 * plan_complexity` can be used to comprehensively measure the execution efficiency and structural rationality of the SQL statement.

[0071] This step plays a crucial role in decision-making and convergence within the overall technical solution. Through a self-consistent voting mechanism, the system can identify the logically most consistent and performance-optimized query statement among SQL generated from multiple paths, effectively avoiding errors caused by model generation biases or inaccurate schema understanding. In practical applications, this mechanism is particularly suitable for query scenarios in enterprise databases involving multiple table joins, complex filtering conditions, or aggregation operations, such as analyzing customer order amount distribution in supply chain systems or statistically analyzing abnormal patterns in transaction flows in financial systems. Through this step, the system achieves automatic SQL optimization and final output without manual intervention, significantly improving the robustness and usability of the NL2SQL system.

[0072] Furthermore, S4 includes: S41, calculate the performance score for each candidate SQL statement, the performance score being a weighted average of execution time and query plan complexity.

[0073] Specifically, the step of "calculating the performance score of each candidate SQL statement, which is derived by weighting execution time and query plan complexity" is a key performance evaluation step in the multi-candidate SQL generation and iterative optimization mechanism of this invention. Its technical implementation is based on the dual dimensions of database execution feedback and query plan analysis, aiming to select the optimal execution path from multiple SQL statements with equivalent structures but different execution efficiencies.

[0074] At the technical implementation level, this step first involves the database execution engine actually executing each candidate SQL statement, recording the complete execution time from submission to return result, typically quantified in milliseconds (ms). Simultaneously, the system calls the database's EXPLAIN command to obtain the SQL's execution plan. Based on metrics such as the number of operation nodes, join type (e.g., Nested Loop Join, Hash Join, Merge Join), and scan method (e.g., Full Table Scan, Index Scan) in the execution plan, an evaluation model for query plan complexity is constructed. Complexity calculation can employ a weighted graph traversal algorithm, assigning different complexity weights to each operation node (e.g., a JOIN operation weight of 1.5 and a subquery weight of 2.0), ultimately yielding a comprehensive complexity value.

[0075] At the application level, this step is widely applicable to enterprise-level database query systems, especially in scenarios involving multi-table joins, aggregate queries, and complex subqueries. Through a performance scoring mechanism, it can effectively avoid inefficient or erroneous SQL queries caused by model generation biases. For example, in a supply chain system, when a user queries "the average order amount per customer in the last three months," the system generates multiple SQL candidates. This step then filters out the SQL with the shortest execution time and lowest planning complexity, thereby improving query response speed and system throughput.

[0076] The technical value of this step lies in providing a basis for decision-making in the subsequent majority voting mechanism by quantitatively evaluating the execution efficiency and structural complexity of the SQL, ensuring that the final output SQL is not only syntactically correct but also has good performance. Furthermore, by combining execution feedback and self-consistency optimization, this scoring mechanism can dynamically adjust weights to adapt to different database environments and query loads, significantly improving the robustness and practicality of the NL2SQL system.

[0077] S42, when outputting the final SQL, an explanation report is generated simultaneously. The explanation report includes the selection criteria, execution performance indicators, and result consistency analysis.

[0078] Specifically, in the NL2SQL technical solution of this invention, the step of simultaneously generating an interpretation report when outputting the final SQL is a key link in realizing the system's interpretability and iterative optimization closed loop. This step, through a structured output method, systematically records and presents the decision-making basis, execution performance, and result consistency analysis during the SQL generation process, thereby enhancing users' trust in the generated SQL and providing data support for subsequent model optimization.

[0079] At the technical implementation level, this step first extracts the execution logs of each SQL statement based on the execution feedback from multiple candidate SQL statements, including key performance indicators such as syntax error information, execution time, and number of rows returned. Subsequently, the system uses a Natural Language Generation (NLG) module to perform correlation analysis with the schema information, Few-Shot examples, and ICL (Information Learning Context) samples used by the model in the prompts, generating explanatory text. For example, if a candidate SQL statement causes an abnormal increase in the number of rows due to a missing JOIN condition, the system will automatically identify this error pattern and indicate in the explanation report that "Candidate SQL 3 / 5 caused an 8-fold increase in the number of rows in the result set due to a missing join condition between the order table and the customer table's user_id."

[0080] At the parameter level, this step involves multiple quantitative metrics, including but not limited to: execution time (in milliseconds), number of rows returned, error type classification (such as syntax errors, schema mismatches, missing JOIN conditions, etc.), and SQL structure complexity (such as the number of JOINs, subquery nesting levels, and frequency of aggregate function usage). Furthermore, the system uses a dynamic threshold K (default K=3) to determine whether the number of result categories is acceptable; if it exceeds this threshold, further difference analysis and feedback injection are triggered.

[0081] In application scenarios, this step is widely applicable to enterprise-level intelligent question-answering systems, data analysis platforms, and database management systems. For example, in a financial risk control system, when a user asks for "the number of overdue customers in the last three months," the system not only outputs the SQL but also explains why a specific JOIN method was chosen, how the time range is mapped to the database fields, and points out failed cases in the candidate SQL due to incorrect time formats.

[0082] The technical value of this step lies in significantly improving the interpretability of the system and the user interaction experience by making the model decision-making process transparent. At the same time, it provides structured feedback for subsequent contextual learning and iterative optimization, forming a closed-loop optimization mechanism, thereby enhancing the robustness and accuracy of the model in complex business scenarios.

[0083] The natural language to SQL method based on execution feedback and iterative optimization in this invention effectively bridges the semantic and structural gap between natural language problems and precise SQL queries in specific database environments. Through a multi-round iterative optimization mechanism driven by execution feedback, it significantly improves the accuracy, robustness, and executability of the generated SQL.

[0084] Furthermore, it also includes: S5, based on vector retrieval recall technology, filters historical query examples from the example database that are semantically similar to the current natural language problem.

[0085] Specifically, the step of "filtering historical query examples that are semantically similar to the current natural language question from the example database based on vector retrieval recall technology" is a key step in realizing In-Context Learning (ICL) and Retrieval-Augmented Generation (RAG) in this invention. Its technical implementation principle is based on semantic embedding and vector similarity calculation. It aims to efficiently recall examples from the historical SQL example library that are highly semantically consistent with the current natural language question (NLQ) through semantic matching rather than traditional keyword matching, thereby providing high-quality contextual support for subsequent SQL generation in the Large Language Model (LLM).

[0086] In terms of specific operation, this step first encodes the current user-input natural language question (NLQ_current) into a high-dimensional semantic vector using a pre-trained text embedding model (such as Sentence-BERT, RoBERTa, or a domain-customized embedding model). Simultaneously, historical query texts in the example database also need to be pre-vectorized and stored in a vector database (such as FAISS, Milvus, Pinecone, etc.). During retrieval, the system calculates the cosine similarity between the NLQ_current vector and each query vector in the example database to initially recall the Top-100 examples with high semantic similarity.

[0087] Furthermore, the system introduces a structural feature filtering mechanism to perform a secondary screening of the initially recalled examples. For example, if the current NLQ contains aggregation operations (such as "average sales"), historical SQL statements containing aggregation functions such as SUM and AVG are prioritized for recall; if multi-table joins are involved, the system matches the structure type of the JOIN operation in the example. In addition, the system also calculates the table / column name matching degree between the current database schema and the example schema to ensure that the recalled examples and the target database are structurally transferable.

[0088] Regarding parameter settings, the similarity threshold for vector retrieval is typically set between 0.75 and 0.85 to ensure that the recalled examples are significantly relevant to the current question in the semantic space. The number of coarse recalls from the Top-100 can be dynamically adjusted based on the database size and system response latency requirements, while the final fine-ranked Top-3 examples are used to construct prompts, guiding the LLM to generate more accurate SQL statements.

[0089] This step plays a crucial role in the overall technical solution, providing high-quality contextual examples for subsequent multi-candidate generation and self-consistency optimization, significantly improving the model's generation accuracy and robustness in complex query scenarios. Through vector retrieval recall, the system can overcome the limitations of traditional keyword-based matching, achieving more intelligent and user-intent-oriented SQL generation guidance, which is a key support for the execution feedback and iterative optimization mechanism of this invention.

[0090] S6. Based on the SQL structure type and schema matching degree of the historical query examples, perform structural feature filtering on the examples to improve the adaptability of the examples to the current query.

[0091] Specifically, this step aims to improve the adaptability of examples to the current Natural Language Queries (NLQ) by analyzing the matching degree between the SQL structure type of historical query examples and the current database schema, and filtering the examples based on structural features. This step is a key part of the "Dynamic Few-Shot Example Recall" process in this invention, and its technical implementation is based on a dual semantic and structural matching mechanism.

[0092] At the technical implementation level, the first step is to identify the structure type of historical SQL examples, such as determining whether they contain JOIN operations, aggregate functions (e.g., SUM, COUNT), subqueries, window functions, etc. Simultaneously, a schema alignment algorithm is used to calculate the matching degree between the table names and column names involved in the examples and the current database schema. Schema matching degree can be comprehensively evaluated based on string similarity (e.g., Levenshtein distance) and the cosine similarity of semantic embedding vectors (e.g., table / column description vectors generated by models such as BERT and RoBERTa). In some implementations, graph structure matching can also be introduced to analyze the topological consistency of foreign key relationships between tables and query paths.

[0093] Regarding parameter metrics, structure type matching employs a classification label system, such as JOIN type (INNER, LEFT, FULL), aggregation granularity (number of GROUP BY fields), and query depth (number of nested subqueries). The schema matching threshold can be set to 0.7~0.9 (cosine similarity) to filter out low-relevance examples. Furthermore, the top-N number of examples recalled is typically set to 100, and the top-3 are retained as the final input after structure filtering.

[0094] In application scenarios, this step is widely applicable to NL2SQL systems in enterprise-level database environments, especially in scenarios such as multi-table joins, complex aggregations, and cross-schema queries. Filtering by structural features can significantly reduce the interference of invalid examples and improve the structural accuracy and execution efficiency of generated SQL.

[0095] In terms of technical effectiveness, this step effectively solves the problem of "semantic similarity but structural mismatch" in traditional example retrieval. Through the dual constraints of structural features and schema, it ensures that the examples are highly adapted to the current query in terms of syntax structure and data model, thereby improving the accuracy and robustness of subsequent large model-generated SQL.

[0096] In summary, the beneficial effects of the present invention are as follows: Large-Model-Driven Filtering and Recall: Compared to traditional fine-grained ranking methods that lack context and execution feedback, this invention utilizes a large model to complete the coarse-grained template filtering. Simultaneously, detailed execution feedback information is incorporated into the prompts to correct potential misselections or omissions in previous rounds, thereby significantly improving the accuracy of template filtering.

[0097] Self-consistent optimization mechanism: This invention establishes a self-consistent mechanism based on multiple candidate options. When the execution result of the candidate option has no syntax errors and the number of categories does not exceed the preset N categories, the iteration process ends, and the candidate option with the shortest execution time is selected as the final result from the majority of answers through majority voting.

[0098] Execution Feedback and Iterative Optimization: This invention employs a multi-round generation strategy to execute the SQL generation task. The execution results of the SQL statements generated in each round are summarized and refined, then integrated into the design of prompts for the next round. This guides the selection of templates and examples, as well as the SQL generation process. This mechanism effectively provides execution feedback, ensuring that the large model has ample room for reflection and correction, thereby significantly improving the consistency and accuracy of the generated SQL.

[0099] Example 2 Figure 2 This is an architecture diagram of the natural language to SQL method based on execution feedback and iterative optimization of the present invention, as shown below. Figure 2As shown, the process includes the following: Input and Table Recall: Users submit Natural Language Questions (NLQs) as input to the entire process. In the table recall phase, the input consists of the NLQ and the database schema (all table names and descriptions). Core steps include: Semantic matching, calculating the semantic similarity between the NLQ and the table names / descriptions, and extracting keywords from the NLQ for precise matching with the table names; Multi-path recall, fusing semantic similarity scores, keyword matching frequency, and table association weights to generate a candidate table set; Dynamic pruning, retaining the Top-K high-confidence tables to reduce interference from irrelevant tables; Large-model filtering recall, further optimizing the candidate table set through feedback injection and large-model ranking. The final output is a high-confidence candidate table set (Top-K).

[0100] Column Recall: The input to the column recall stage is the NLQ (Novel Language Quotient), the set of tables to be recalled, and all column names and descriptions within the tables. The core steps are as follows: Direct column name matching: Identifying phrases in the NLQ that overlap with column names; Value-column association: Detecting explicit values ​​in the NLQ and associating them with the corresponding columns; Semantic extended matching: Calculating the semantic similarity between the NLQ context and the column names / descriptions, combined with column data type constraints; Cross-table column association: Associating columns from multiple tables through foreign key paths; Large model filtering and recall: Injecting historical feedback and having the large model make decisions based on prompt words, excluding invalid columns. Finally, a set of high-confidence candidate columns is output, while low-confidence columns and NLQ fragments that cannot be associated with the schema are filtered out.

[0101] Dynamic Few-Shot Example Recall: In the dynamic Few-Shot example recall phase, the input is the current user query NLQ_current and the example database (including historical queries, SQL, and execution results). First, multimodal initial screening is performed. Semantic vector recall is used to calculate the cosine similarity between NLQ_current and historical queries' embeddings, recalling a top-100 coarsely selected set of examples. Next, structural feature filtering is performed, matching SQL structure types and aligning schema similarity to further filter out examples more relevant to the current query.

[0102] Output and Validation: In the output and validation phase, structured schema linking results are output. The column recall results are filtered to remove low-confidence columns with similarity below a threshold. Simultaneously, NLQ fragments that cannot be associated with the schema, such as those without corresponding time descriptions, are rejected. These steps ensure that the final schema linking results have high accuracy and reliability, providing a solid foundation for subsequent SQL generation and execution.

[0103] Furthermore, it also includes: The key optimization points in the large-scale model fine-ranking stage lie in its input and operation process. Inputs include a coarsely selected example set and historical execution feedback, i.e., the SQL error log from the previous round. In the large-scale model operation, the first step is prompt word design, integrating information such as the current query, database context, and previous error feedback into the prompt words to provide comprehensive guidance for the model. The second step is feedback-driven decision-making, prioritizing syntactically sound or structurally simple SQL examples based on syntax errors or execution timeouts in historical feedback. Finally, the top-3 fine-ranked examples and their selection rationale are output, providing high-quality references for subsequent steps.

[0104] In the Self-Consistency multi-candidate iterative decision-making process, multi-path candidate generation is performed first. The input is the enhancement context of the current round, including the schema linking results and dynamic Few-Shot examples. Regarding the generation strategy, diversity sampling is employed, generating multiple semantically equivalent but structurally different SQL candidates by adjusting the temperature coefficient. Simultaneously, syntax pre-validation is performed, using a lightweight SQL parser to filter out candidates with syntax errors, ensuring that the SQL candidates entering the next stage have basic syntactic correctness.

[0105] In the feedback-driven iterative decision-making phase, the input is a pre-validated set of candidate SQL statements. During the dynamic decision-making process, candidate SQL statements are executed in parallel, capturing two key signals in real time: syntax errors and the number of execution result categories. If a syntax error is found, the process enters the syntax error branch, extracts error logs, aggregates error types, extracts high-frequency error patterns to generate a natural language summary, and then transforms the error summary into constraint instructions that are injected into the next round of prompts, providing targeted guidance for subsequent generation.

[0106] If the candidate SQL has no syntax errors, the process proceeds to the no-syntax-error branch for result consistency checks. First, a threshold check is performed, comparing the number of result categories with a dynamic threshold K to determine if the result differences are within acceptable limits. If the number of result categories is less than or equal to K, a voting mechanism is triggered, using majority consistency filtering and performance optimization to determine the final SQL and end the iteration. If the number of result categories is greater than K, the process proceeds to root cause analysis of result differences, diagnosing key dimensions such as join logic, filtering conditions, and aggregation granularity, generating a difference report, and converting the report into preventative instructions for the next round of prompts.

[0107] When all candidate SQL statements are syntax-free and the number of result categories is less than or equal to K, a voting termination mechanism is triggered. The voting rule follows the majority consensus principle, counting the candidate SQL statements that return the same number of rows, and selecting the SQL statement with the shortest execution time from the majority set as the final output. Simultaneously, an explanation report is generated to clearly demonstrate the basis and process of SQL generation, improving the interpretability and credibility of the results.

[0108] In non-converged scenarios, i.e., when the number of categories is greater than K or there are syntax errors, iterative regeneration is performed. Feedback compression technology is employed to convert error logs or result discrepancies into natural language instructions, directly guiding the model to adjust its generation strategy. Simultaneously, some high-quality candidates are retained as base templates to focus on the generation path, dynamically adjusting diversity, reducing the temperature coefficient to decrease randomness, strengthening error avoidance, and gradually optimizing the SQL generation results until convergence conditions are met.

[0109] Example 3 To achieve the above embodiments, such as Figure 3 As shown, this embodiment also provides a natural language to SQL converter 10 based on execution feedback and iterative optimization, including: The candidate SQL generation module 100 is used to generate multiple candidate SQL statements, which are based on diversity sampling of natural language questions input by the user, database schema information and a predefined SQL example library; The parallel execution and result capture module 200 is used to execute the candidate SQL statements in parallel and capture the syntax error information and the number of rows and categories of the results returned by different candidate SQL statements in the execution results; The error pattern processing and prompt word injection module 300 is used to generate an error pattern summary based on the grammatical error information, and convert the error pattern summary into natural language constraint instructions, which are then injected into the prompt words generated in the next round. The self-consistent voting and result selection module 400 is used to select the candidate SQL set that returns the most rows when the number of row categories is less than or equal to a preset threshold, and select the SQL with the shortest execution time from the set as the final output.

[0110] Furthermore, the candidate SQL generation module is also used for: A diverse sampling strategy based on temperature coefficient adjustment is used, wherein the temperature coefficient is set during the first round of generation to generate semantically equivalent but structurally differentiated SQL statements. A lightweight SQL parser is used to perform syntax pre-validation on the generated candidate SQL statements, filtering out SQL statements with syntax errors.

[0111] Furthermore, the parallel execution and result capture module is also used for: Simultaneously submit all candidate SQL statements that pass the syntax pre-validation to the database for execution, and record the execution time and the number of rows returned for each SQL statement; The number of categories with different row counts in the results returned by different candidate SQL statements is counted, where the number of categories is the number of sets of results with different row counts.

[0112] Furthermore, the error pattern handling and prompt word injection module is also used for: Extract high-frequency error patterns and generate natural language summaries, which include error types, scope of impact, and correction suggestions; The natural language summary is converted into structured constraint instructions, which are used to restrict the syntax structure or field usage of the SQL generated in the next round.

[0113] Furthermore, the self-consistent voting and result selection module is also used for: Calculate a performance score for each candidate SQL statement, the performance score being a weighted average of execution time and query plan complexity; When the final SQL is output, an explanation report is generated simultaneously. The explanation report includes the selection criteria, execution performance indicators, and result consistency analysis.

[0114] Furthermore, it also includes: The vector retrieval recall module is used to filter historical query examples that are semantically similar to the current natural language question from the example database based on vector retrieval recall technology. The structural feature filtering module is used to filter the historical query examples based on their SQL structure type and schema matching degree, so as to improve the adaptability of the examples to the current query.

[0115] The natural language to SQL converter based on execution feedback and iterative optimization according to embodiments of the present invention effectively bridges the semantic and structural gap between natural language problems and precise SQL queries in specific database environments. Through a multi-round iterative optimization mechanism driven by execution feedback, it significantly improves the accuracy, robustness and executability of generated SQL.

[0116] In the description of this specification, the references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., refer to specific features, structures, materials, or characteristics described in connection with that embodiment or example, which are included in at least one embodiment or example of the present invention. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples. Moreover, without contradiction, those skilled in the art can combine and integrate the different embodiments or examples described in this specification, as well as the features of different embodiments or examples.

[0117] Furthermore, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include at least one of that feature. In the description of this invention, "a plurality of" means at least two, such as two, three, etc., unless otherwise explicitly specified.

Claims

1. A natural language to SQL method based on execution feedback and iterative optimization, characterized in that, include: S1, generate multiple candidate SQL statements, which are sampled based on the user's input natural language question, database schema information and a predefined SQL example library; S2, execute the candidate SQL statements in parallel, and capture the syntax error information and the number of rows and categories of the results returned by different candidate SQL statements in the execution results; S3, Generate an error pattern summary based on the grammatical error information, and convert the error pattern summary into a natural language constraint instruction, which is then injected into the prompt words generated in the next round; S4. When the number of rows in each category is less than or equal to a preset threshold, a self-consistent voting mechanism is adopted to select the candidate SQL set that returns the most rows with the same number of rows, and the SQL with the shortest execution time is selected from the set as the final output.

2. The method as described in claim 1, characterized in that, The generation of multiple candidate SQL statements includes: S11, a diverse sampling strategy based on temperature coefficient adjustment, wherein the temperature coefficient is set during the first round of generation to generate semantically equivalent but structurally differentiated SQL statements; S12 uses a lightweight SQL parser to perform syntax pre-validation on the generated candidate SQL statements and filters out SQL statements with syntax errors.

3. The method as described in claim 1, characterized in that, The parallel execution of the candidate SQL statements, capturing syntax error information in the execution results and the number of rows categorized in the different candidate SQL return results, includes: S21, simultaneously submit all candidate SQL statements that have passed the syntax pre-validation to the database for execution, and record the execution time and the number of rows returned for each SQL statement; S22, count the number of rows in the results returned by different candidate SQL statements, where the number of categories is the number of sets of results with different row counts.

4. The method as described in claim 1, characterized in that, The step of generating an error pattern summary based on the grammatical error information, and converting the error pattern summary into a natural language constraint instruction, and injecting it into the prompt words generated in the next round, includes: S31, extract high-frequency error patterns and generate a natural language summary, the summary including error type, scope of impact and correction suggestions; S32, the natural language summary is converted into structured constraint instructions, which are used to restrict the syntax structure or field usage of the SQL generated in the next round.

5. The method as described in claim 1, characterized in that, When the number of rows categorized is less than or equal to a preset threshold, a self-consistent voting mechanism is adopted to select the candidate SQL set that returns the most rows with the same number of rows, and the SQL with the shortest execution time in this set is selected as the final output, including: S41, Calculate the performance score for each candidate SQL statement, the performance score being a weighted average of execution time and query plan complexity; S42, when outputting the final SQL, an explanation report is generated simultaneously. The explanation report includes the selection criteria, execution performance indicators, and result consistency analysis.

6. The method as described in claim 1, characterized in that, Also includes: S5, based on vector retrieval recall technology, filters historical query examples from the example database that are semantically similar to the current natural language problem; S6. Based on the SQL structure type and schema matching degree of the historical query examples, perform structural feature filtering on the examples to improve the adaptability of the examples to the current query.

7. A natural language to SQL converter based on execution feedback and iterative optimization, characterized in that, include: The candidate SQL generation module is used to generate multiple candidate SQL statements, which are based on diversity sampling of the natural language question input by the user, database schema information and a predefined SQL example library; The parallel execution and result capture module is used to execute the candidate SQL statements in parallel and capture the syntax error information and the number of rows and categories of the results returned by different candidate SQL statements in the execution results; The error pattern processing and prompt word injection module is used to generate an error pattern summary based on the grammatical error information, and convert the error pattern summary into natural language constraint instructions, which are then injected into the prompt words generated in the next round. The self-consistent voting and result selection module is used to select the candidate SQL set that returns the most rows when the number of rows in each category is less than or equal to a preset threshold, and select the SQL with the shortest execution time from the set as the final output.

8. The apparatus as claimed in claim 7, characterized in that, The candidate SQL generation module is also used for: A diverse sampling strategy based on temperature coefficient adjustment is used, wherein the temperature coefficient is set during the first round of generation to generate semantically equivalent but structurally differentiated SQL statements. A lightweight SQL parser is used to perform syntax pre-validation on the generated candidate SQL statements, filtering out SQL statements with syntax errors.

9. The apparatus as claimed in claim 7, characterized in that, The parallel execution and result capture module is also used for: Simultaneously submit all candidate SQL statements that pass the syntax pre-validation to the database for execution, and record the execution time and the number of rows returned for each SQL statement; The number of categories with different row counts in the results returned by different candidate SQL statements is counted, where the number of categories is the number of sets of results with different row counts.

10. The apparatus as claimed in claim 7, characterized in that, The error mode processing and prompt word injection module is also used for: Extract high-frequency error patterns and generate natural language summaries, which include error types, scope of impact, and correction suggestions; The natural language summary is converted into structured constraint instructions, which are used to restrict the syntax structure or field usage of the SQL generated in the next round.

11. The apparatus as claimed in claim 7, characterized in that, The self-consistent voting and result selection module is also used for: Calculate a performance score for each candidate SQL statement, the performance score being a weighted average of execution time and query plan complexity; When the final SQL is output, an explanation report is generated simultaneously. The explanation report includes the selection criteria, execution performance indicators, and result consistency analysis.

12. The apparatus as claimed in claim 7, characterized in that, Also includes: The vector retrieval recall module is used to filter historical query examples that are semantically similar to the current natural language question from the example database based on vector retrieval recall technology. The structural feature filtering module is used to filter the historical query examples based on their SQL structure type and schema matching degree, so as to improve the adaptability of the examples to the current query.

Images