A text-to-sql method and system based on hierarchical multi-candidate SQL scoring
By constructing a three-level progressive filtering system through a hierarchical multi-candidate SQL scoring method, the accuracy and adaptability issues of existing Text-to-SQL solutions are solved, achieving efficient and accurate data querying and reducing operation and maintenance costs.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- NANJING SINOVATIO TECHNOLOGY CO LTD
- Filing Date
- 2026-03-03
- Publication Date
- 2026-06-02
AI Technical Summary
Existing Text-to-SQL solutions have shortcomings in complex natural language understanding, database schema adaptation, and industry domain knowledge integration, resulting in low accuracy of generated SQL and high operation and maintenance costs, making it difficult to meet the data query needs of business personnel without technical backgrounds.
A hierarchical multi-candidate SQL scoring method is adopted. Through table retrieval, mSchema synthesis, multi-candidate SQL generation, and multi-level scoring filtering, a three-level progressive filtering system is constructed, which includes execution-level adaptive initial screening, probability-level dynamic truncation, and semantic-level adversarial selection. This system eliminates logical errors and improves the generation accuracy.
It significantly improves the accuracy and robustness of Text-to-SQL generation, lowers the threshold for data querying, adapts to complex scenarios, and reduces operation and maintenance costs.
Smart Images
Figure CN122132431A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of database automatic question answering technology, and in particular to a Text-to-SQL method and system based on hierarchical multi-candidate SQL scoring. Background Technology
[0002] In the current era of accelerated digital transformation, enterprise data assets are growing exponentially. While SQL is a core tool for data querying and analysis, its limited usage has become a key bottleneck restricting business personnel from efficiently utilizing data. Business personnel without technical backgrounds (such as operations, marketing, and finance) often rely on data engineers to fulfill their query needs due to unfamiliarity with SQL syntax and table structures. This leads to frequent problems such as "data request queuing" and "request transmission deviations," with average data acquisition cycles lasting several hours or even days, severely impacting business decision-making efficiency.
[0003] From a technical perspective, current mainstream Text-to-SQL solutions suffer from three major pain points: First, they lack the ability to understand complex natural language, especially when dealing with multi-condition joins, fuzzy queries, and ambiguous statements, resulting in low accuracy. Second, they have poor adaptability to database schemas; when the data table structure changes (such as adding fields or adjusting table joins), the model needs to be retrained on a large scale to adapt, leading to extremely high maintenance costs. Third, they lack the integration of industry-specific knowledge; in professional scenarios such as finance and healthcare, it is difficult to identify the correspondence between domain-specific terms (such as "non-performing loan rate" and "DRG payment") and data fields, resulting in a significant reduction in the usability of the generated SQL. Summary of the Invention
[0004] Purpose of the invention: This invention provides a Text-to-SQL method and system based on hierarchical multi-candidate SQL scoring, which can reduce the threshold for data querying and improve the accuracy and scenario adaptability of Text-to-SQL generation.
[0005] Technical solution: The Text-to-SQL method based on hierarchical multi-candidate SQL scoring described in this invention includes the following steps:
[0006] Step 1: Receive queries while online and perform table recall;
[0007] Step 2: Perform mSchema synthesis, which includes not only table structure information, but also sample data of table fields and the internal and external key relationships between tables;
[0008] Step 3: Select m reference examples, use one example each time, and generate n SQL statements using the large model, that is, generate a total of m*n SQL statements. Set the temperature parameter of the large model to 1 and the sampling parameter to n.
[0009] Step 4: Filter some SQL queries using a first-level scoring system;
[0010] Step 5: Use a two-level scoring system to filter out some SQL queries;
[0011] Step 6: Use a three-level scoring system to obtain the cumulative score for each SQL statement;
[0012] Step 7: Based on the scores of each SQL statement obtained in the previous step, sum up the scores of each SQL statement to obtain the total score of each SQL statement. Finally, select the SQL statement with the highest score as the final SQL statement and return the result.
[0013] Furthermore, in step 1, if the table structure and table field enumeration values are vectorized in offline mode, it will be easier to use in online mode later. The vectorized table structure includes the table name, table comments, table fields, and table field comments. The vectorized table field enumeration values include the deduplicated values of character fields. The vector database is divided into two collections to store the table structure content and table field enumeration values respectively. Each collection, in addition to containing the vectorized content, also includes table information, such as the database ID, database name, and schema name, to facilitate filtering for table retrieval and field enumeration value retrieval in online mode.
[0014] Furthermore, in step 1, key query elements of the query problem are extracted based on the big language model. Based on these key query elements, table structures and table field enumeration values in the vector library with high similarity to the key query elements are selected. The big language model is then used to determine which tables are relevant to the current query problem, thereby achieving table recall.
[0015] Furthermore, in step 4, when using the first-level scoring method, the database interface is called to perform pre-execution or Explain syntax analysis on the generated candidate SQL. SQL that cannot be executed or reports errors is directly marked as invalid and removed. For executable SQL that passes the hard filtering, the database optimizer obtains the estimated cost value TotalCost of each candidate SQL query plan and calculates the cost distribution statistics of all candidate SQL in the current batch. Based on these statistics, a dynamic truncation interval is set, and outliers that significantly deviate from the overall preferred distribution interval are automatically removed.
[0016] Furthermore, in step 5, when using secondary scoring, the candidate SQLs retained in step 4 are subjected to a second probabilistic cleaning, and the comprehensive confidence of each SQL is calculated. This confidence includes two parts: the first part is the cumulative log probability of the sequence when the large language model generates the SQL, and the second part is the model's own probability when the large language model reflects on and judges the SQL. After fusing the two, the confidence score distribution curve of all candidate SQLs is obtained. Based on the gradient change characteristics of the curve, the retention range is determined, and the candidate subset located in the high confidence flat interval is selected to enter the next round.
[0017] Furthermore, in step 6, when using a three-level scoring system, the candidate SQLs retained in step 5 are paired up, and a large language model is used as the judge to determine the relative semantic accuracy of the two pairs. The winners accumulate points. Using an offline-trained SQL-to-DSL mapping model, the candidate SQLs are converted into a standardized domain-specific language DSL. The converted DSLs are then subjected to the same pairwise logical deduction and accuracy judgment. The adversarial winning frequencies of the two dimensions are superimposed, and the SQL with the largest cumulative adversarial advantage is directly selected as the final output.
[0018] Furthermore, the SQL-to-DSL process includes two stages: offline training and online inference; in the offline stage, a large-scale...<SQL, DSL> Align the corpus and train a dedicated sequence mapping model to enable it to normalize diverse SQL syntax into standard logical expressions. During the online inference phase, the generated DSL statements are standardized and customized for the parameters of the data processing functions: for pandas functions with multiple input paradigms, they are fixed as a unique input paradigm to ensure that the output is in a definite JSON format or other structured data format.
[0019] Furthermore, the input stream of DSL is defined as a directed acyclic graph structure: the initial input is a data table object, and the input of subsequent steps is based on the output of the operation results of the previous steps; multiple input objects are supported during the operation, but the output of each operator is constrained to a single result, thereby ensuring the determinism and comparability of the logical chain and providing a precise intermediate expression for semantic adversarial.
[0020] Accordingly, a Text-to-SQL system based on hierarchical multi-candidate SQL scoring includes: a table retrieval module, an mSchema synthesis module, a multi-candidate SQL generation module, a three-level scoring filtering module, and a result return module. The table retrieval module receives query questions in an online state and performs table retrieval. The mSchema synthesis module performs mSchema synthesis, which includes not only table structure information but also sample data of table fields and the internal and external key relationships between tables. The multi-candidate SQL generation module selects m reference examples, using one example each time, and generates n SQL statements using a large model, i.e., a total of m*n SQL statements are generated. The temperature parameter of the large model is set to 1, and the sampling parameter is set to n. The three-level scoring filtering module obtains the cumulative score of each SQL statement by filtering a portion of the SQL statements. The result return module accumulates the individual scores of each SQL statement based on the obtained scores, obtains the total score of each SQL statement, and finally selects the SQL statement with the highest score as the final SQL statement and returns the result.
[0021] Beneficial effects: Compared with the prior art, the present invention has the following significant advantages: It constructs a three-level progressive filtering system including "execution layer adaptive initial screening", "probabilistic layer dynamic truncation" and "semantic layer adversarial optimization", which completely avoids the dependence on manually preset thresholds. Through a progressive data-driven strategy, it effectively eliminates "syntactically correct but logically incorrect" illusion SQL. While ensuring a high degree of automation, it significantly improves the accuracy and robustness of the final SQL generation. Attached Figure Description
[0022] Figure 1 This is a schematic diagram of the method flow of the present invention.
[0023] Figure 2 This is a flowchart of the Text-to-SQL table retrieval process of the present invention.
[0024] Figure 3 This is a flowchart of the SQL to DSL conversion process of the present invention. Detailed Implementation
[0025] like Figure 1 As shown, a Text-to-SQL method based on hierarchical multi-candidate SQL scoring includes the following steps:
[0026] The system operates in two states: offline and online. The offline state is used to vectorize the table structure and table field enumeration values, facilitating subsequent use in the online state. The vectorized table structure includes the table name, table comments, table fields, and table field comments. The vectorized table field enumeration values include duplicate values for character fields, excluding numeric and time-based field enumeration values. Milvus is selected as the vector database. Milvus contains two collections, storing the table structure content and table field enumeration values respectively. Each collection, in addition to containing the vectorized content, also includes table information, such as the database ID, database name, and schema name, facilitating filtering for table retrieval and field enumeration value retrieval in the online state.
[0027] Step 1: Receive the query in the online status and begin implementing Text-to-SQL. For example... Figure 2 As shown, in the table recall stage, the key query elements of the query problem are first extracted based on the big language model. Based on the key query elements, the table structures and table field enumeration values in the vector library with high similarity to the key query elements are selected. The big language model is then used to determine which tables are relevant to the current query problem, thus achieving table recall.
[0028] Step 2: mSchema synthesis. mSchema is a variant of the table structure schema. In addition to table structure information, it also contains sample data of table fields and the domestic and foreign key relationships between tables.
[0029] Step 3: Generation of multiple candidate SQL statements based on the large model. Select m examples, using one example each time, and generate n SQL statements using the large model, resulting in a total of m*n SQL statements. The temperature parameter of the large model is set to 1, and the sampling parameter is set to n. In practice, both m and n are set to 16.
[0030] Step 4: Filter some SQL queries using a first-level scoring system. In this step, an adaptive initial screening based on execution cost is implemented. Specifically, the system abandons scoring methods that rely on physical execution time (e.g., 30 seconds or 5 minutes) and instead uses the cost estimate (Total Cost) provided by the database optimizer as an objective basis. In the executability scoring stage, the system calls the database interface to perform Explain execution plan analysis on candidate SQL queries. SQL queries that return errors or cannot generate an execution plan are directly marked as invalid and removed. In the execution efficiency scoring stage, the system obtains the estimated cost value for each executable SQL query and uses a non-linear normalization function to map the cost to a performance coefficient between 0 and 1; the lower the cost, the higher the coefficient. Subsequently, the system calculates the statistical distribution characteristics (e.g., mean and standard deviation) of the performance coefficients of all candidate SQL queries in the current batch, sets a dynamic cutoff interval (e.g., removing outliers below "mean - standard deviation"), and adaptively filters out inefficient SQL queries with resource consumption significantly higher than the average level, rather than relying on a preset fixed score threshold.
[0031] Step 5: Filter a subset of SQL queries using a two-stage scoring system. This step involves weighting two parts of the score: the log probability carried by the large model when generating the SQL, and the log probability carried by the model itself when scoring each SQL query individually based on the large model. It's important to note that, to correspond with the log probability carried by the large model when generating the SQL, the portion scored individually based on the large model is not the result generated by the large model, but rather the log probability carried by the generated result. In implementation, the system converts these two log probabilities into linear probabilities and then weights and fuses them according to preset configurable weight ratios (e.g., 39% and 61%) to obtain a comprehensive confidence score. Subsequently, the system sorts all candidate SQL queries by their comprehensive confidence scores and dynamically determines the cutoff position based on the gradient change characteristics of the score curve (e.g., identifying the "elbow point" position where the score decrease rate abruptly changes), adaptively retaining a subset of SQL queries in the high confidence interval, thus replacing a fixed quantity limit.
[0032] Step 6: Obtain the cumulative score for each SQL statement using a three-level scoring system. This part includes weighted scoring from two parallel modules: "SQL Source Code Level Adversarial" and "DSL Logic Level Adversarial." In the SQL Source Code Level Adversarial module, candidate SQL statements selected in the previous step are paired up, and a large language model is used to determine which one is semantically closer to the user's intent. The winner receives 1 point, and the loser receives 0 points. The DSL Logic Level Adversarial module differs from the previous module in that the SQL statements are converted to DSL before evaluation, but the large model prompt template used is basically the same as the previous module. For example... Figure 3As shown, the SQL-to-DSL process includes offline training of the SQL-to-DSL model and online inference of the corresponding DSL statements. The syntax of the DSL statements is similar to that of pandas in Python, but the parameters of each pandas function are modified or customized as needed based on the actual application scenario. Furthermore, for pandas functions with diverse paradigms, some flexibility is sacrificed to fix a single input paradigm to facilitate determining the JSON-formatted output. The initial input of the DSL is a table, and subsequent inputs are the outputs based on the results of the previous steps. Multiple inputs are allowed, but only one output can be produced. The naming convention for the output dataframe (df) is as follows: it begins with df, contains the operation command of the current operator, and ends with a sequence number. Taking the GroupbyAgg operation as an example, the naming convention for the output df is df_groupby_agg_{i}, where i represents the order of the current operation in the same output DSL. For example, if it is ranked first, it is named df_groupby_agg_0, and so on. This strict naming and paradigm constraint ensures the accuracy of logical comparisons. In practice, the SQL source code-level adversarial score and the DSL logic-level adversarial score are accumulated according to a preset ratio (e.g., each accounting for half).
[0033] Step 7: Based on the scores obtained in the previous step, sum up the individual scores of each SQL statement to obtain the total score for each SQL statement. Finally, select the SQL statement with the highest score as the final SQL statement. The Text-to-SQL system executes this SQL statement and returns the result to the next module of the structured automatic question answering system.
[0034] This invention effectively solves the problem of instability in judgment caused by the reliance on absolute score thresholds in existing technologies by constructing a two-dimensional adversarial game mechanism that operates in parallel at both the "SQL source code level" and the "DSL logic level". This invention adopts a tournament-style relative competition strategy and combines it with an offline-trained SQL-to-DSL mapping model to transform unstructured SQL statements into DSL logical "fingerprints" with unique input paradigms and deterministic output structures. This mechanism masks the apparent differences in SQL dialects, enabling the system to go beyond grammatical appearances and directly conduct pairwise selection based on logical essence. This process does not require setting manual score standards but automatically locks the optimal solution with the greatest relative advantage through multiple rounds of game, significantly improving the robustness of selection in complex grammatical scenarios.
[0035] This invention implements adaptive performance evaluation based on cost distribution characteristics. It adopts a quantification mechanism based on database optimizer cost and further introduces an adaptive initial screening strategy based on the cost distribution characteristics of the current query group. The system can automatically identify and remove abnormally high-cost SQL statements (outliers) that significantly deviate from the preferred range, rather than relying on a preset fixed score line. This data-driven screening method decouples the scoring criteria from the physical environment, ensuring the objectivity and fairness of the evaluation. It can also effectively intercept resource-exhausting inefficient statements while retaining the necessary overhead of complex queries, thus ensuring the stability of the system's online operation.
[0036] This invention introduces a dynamic truncation mechanism based on confidence distribution characteristics, which significantly improves the intelligence and reliability of model selection. It integrates the dual indicators of "log probability of generation process" and "self-reflection discrimination probability". The system abandons the traditional fixed-number truncation of Top-N, and instead dynamically determines the retention range by analyzing the gradient change of the comprehensive confidence curve (such as identifying distribution inflection points). This strategy enables the system to adaptively determine the size of the high-confidence candidate set to retain based on the quality distribution of each generation result, effectively reducing the random errors caused by manually set parameters.
Claims
1. A Text-to-SQL method based on hierarchical multi-candidate SQL scoring, characterized in that, Includes the following steps: Step 1: Receive queries while online and perform table recall; Step 2: Perform mSchema synthesis, which includes not only table structure information, but also sample data of table fields and the internal and external key relationships between tables; Step 3: Select m reference examples, use one example each time, and generate n SQL statements using the large model, that is, generate a total of m*n SQL statements. Set the temperature parameter of the large model to 1 and the sampling parameter to n. Step 4: Filter some SQL queries using a first-level scoring system; Step 5: Use a two-level scoring system to filter out some SQL statements; Step 6: Use a three-level scoring system to obtain the cumulative score for each SQL statement; Step 7: Based on the scores of each SQL statement obtained in the previous step, sum up the scores of each SQL statement to obtain the total score of each SQL statement. Finally, select the SQL statement with the highest score as the final SQL statement and return the result.
2. The Text-to-SQL method based on hierarchical multi-candidate SQL scoring as described in claim 1, characterized in that, In step 1, if the table structure and table field enumeration values are vectorized in the offline state, it will be easier to use in the online state later.
3. The Text-to-SQL method based on hierarchical multi-candidate SQL scoring as described in claim 2, characterized in that, The vectorized content of the table structure includes the table name, table remarks, table fields, and table field remarks; the vectorized content of the table field enumeration values includes the deduplicated values of character fields; in the vector database, it is divided into two collections to store the table structure content and the table field enumeration values respectively.
4. The Text-to-SQL method based on hierarchical multi-candidate SQL scoring as described in claim 1, characterized in that, In step 1, key query elements of the query problem are extracted based on the big language model. Based on these key query elements, table structures and table field enumeration values in the vector library with high similarity to the key query elements are selected. The big language model is then used to determine which tables are relevant to the current query problem, thus achieving table recall.
5. The Text-to-SQL method based on hierarchical multi-candidate SQL scoring as described in claim 1, characterized in that, In step 4, when using the first-level scoring method, the database interface is called to perform pre-execution or Explain syntax analysis on the generated candidate SQL. SQL that cannot be executed or reports an error is directly marked as invalid and removed. For executable SQL that passes the hard filtering, the database optimizer obtains the estimated cost value (Total Cost) of each candidate SQL query plan and calculates the cost distribution statistics of all candidate SQL in the current batch. Based on these statistics, a dynamic truncation interval is set, and outliers that significantly deviate from the overall preferred distribution interval are automatically removed.
6. The Text-to-SQL method based on hierarchical multi-candidate SQL scoring as described in claim 1, characterized in that, In step 5, when using secondary scoring, the candidate SQLs retained in step 4 are subjected to a second probabilistic cleaning. The comprehensive confidence of each SQL is calculated, which includes two parts: the first part is the cumulative log probability of the sequence when the large language model generates the SQL, and the second part is the model's own probability when the large language model reflects on and judges the SQL. After fusing the two, the confidence score distribution curve of all candidate SQLs is obtained. Based on the gradient change characteristics of the curve, the retention range is determined, and the candidate subset located in the high confidence flat interval is selected to enter the next round.
7. The Text-to-SQL method based on hierarchical multi-candidate SQL scoring as described in claim 1, characterized in that, In step 6, when using a three-level scoring system, the candidate SQLs retained in step 5 are paired up, and a large language model is used as the judge to determine the relative semantic accuracy of the two pairs. The winners accumulate points. Using an offline-trained SQL-to-DSL mapping model, candidate SQLs are converted into standardized domain-specific language DSLs. The converted DSLs are then subjected to the same pairwise logical deduction and accuracy judgment. The adversarial win frequencies of the two dimensions are superimposed, and the SQL with the largest cumulative adversarial advantage is directly selected as the final output.
8. The Text-to-SQL method based on hierarchical multi-candidate SQL scoring as described in claim 7, characterized in that, The process of converting SQL to DSL includes two stages: offline training and online inference. In the offline stage, a large-scale...<SQL, DSL> Align the corpus and train a dedicated sequence mapping model to enable it to normalize diverse SQL syntax into standard logical expressions. During the online inference phase, the generated DSL statements are standardized and customized for the parameters of the data processing functions: for pandas functions with multiple input paradigms, they are fixed as a unique input paradigm to ensure that the output is in a definite JSON format or other structured data format.
9. The Text-to-SQL method based on hierarchical multi-candidate SQL scoring as described in claim 7, characterized in that, The input stream of DSL is defined as a directed acyclic graph structure: the initial input is a data table object, and the input of subsequent steps is based on the output of the operation results of the previous steps; multiple input objects are supported during the operation, but the output of each operator is constrained to a single result, thereby ensuring the determinism and comparability of the logical chain and providing a precise intermediate expression for semantic adversarial.
10. A system for implementing the Text-to-SQL method based on hierarchical multi-candidate SQL scoring as described in claim 1, characterized in that, include: The system includes a table recall module, an mSchema synthesis module, a multi-candidate SQL generation module, a three-level scoring filtering module, and a result return module. The table recall module receives query questions while the system is online and performs table recall. The mSchema synthesis module performs mSchema synthesis, which includes not only table structure information but also sample data of table fields and the internal and external key relationships between tables. The multi-candidate SQL generation module selects m reference examples, using one example at a time, and generates n SQL statements using the large model, resulting in a total of m*n SQL statements. The temperature parameter of the large model is set to 1, and the sampling parameter is set to n. The three-level scoring filtering module filters some SQL statements and obtains the cumulative score of each SQL statement. The result return module adds the individual scores of each SQL statement to obtain the total score of each SQL statement, and finally selects the SQL statement with the highest score as the final SQL statement and returns the result.