A zero-shot text-to-SQL automatic parsing method based on large language model

Abstract

This invention discloses a method for automatic zero-shot text-to-SQL parsing based on a large language model. The method comprises the following steps: defining an optimal problem; inputting the optimal problem into the zero-shot text-to-SQL parsing model for processing to obtain a target SQL statement; wherein the zero-shot text-to-SQL parsing model uses a preset large language model to convert the optimal problem into a number of candidate SQL statements; evaluating and self-ranking the candidate SQL statements to obtain a ranking result; and modifying the ranking result to generate a target SQL statement. This invention improves the robustness and scalability of the system.

Description

Technical Field

[0001] The present invention relates to the field of artificial intelligence technology, and in particular to an automatic parsing method from zero-sample text to SQL based on a large language model. Background Art

[0002] Text-to-SQL goes beyond the technical challenges of natural language processing to enable users to easily interact with databases in their native language. This goal, driven by the growing demand for making extensive data repositories accessible to non-technical users, has transformative potential in industries such as healthcare and finance. Figure 1 A typical text-to-SQL task example is presented. The conversion process includes: interpreting user intent from natural language queries; analyzing the database schema to identify relevant columns; generating precise SQL queries containing appropriate columns, conditions, and functions; resolving ambiguities in data and language expressions; coping with variations in natural language input; and understanding SQL syntax and functions for calculations and data filtering.

[0003] Text-to-SQL methods have evolved from rule-based approaches to more sophisticated technologies. Early rule-based methods used templates to generate SQL queries, which performed well in specific situations but lacked scalability and generalizability. The rise of deep learning has introduced end-to-end models that can automatically learn the text-to-SQL mapping, but these models perform poorly when handling complex queries.

[0004] Methods that leverage pre-trained language models (PLMs) for fine-tuning have shown significant improvements. Notable among these is RESDSQL, which achieves state-of-the-art performance on the Spider dataset by rearranging database schema information and leveraging the results of NatSQL. However, these methods require large amounts of labeled data and are prone to overfitting.

[0005] Recently, approaches based on contextual learning (ICL), particularly those using ChatGPT and GPT-4, have advanced text-to-SQL translation. These methods perform well even without labeled data and are easily scalable to diverse domains. However, they also face the challenge of unstable output, meaning that different SQL statements may be generated for the same prompt.

[0006] Zero-shot learning, proposed in pioneering research, has become a crucial branch of machine learning. It allows for classification of instances of categories for which no labeled examples exist. The recent emergence of large language models (LLMs) such as GPT-3 and GPT-4 has significantly advanced this approach in natural language processing.

[0007] Notable methods in recent years include Meta-Tuning, which optimizes the zero-shot learning objective by fine-tuning pre-trained LLMs on multiple datasets, and Zero-shot Chain of Thought (Zero-shot-CoT), a hinting method for multi-hop reasoning. In addition, Zero-shot Adaptive Prompting improves prediction accuracy by selecting robust self-generated examples based on model confidence.

[0008] Early text-to-SQL approaches were rule-based, but faced scalability challenges due to the need for manual rule design. The rise of deep learning has popularized end-to-end models that can automatically generate SQL, but they still struggle with complex queries. Recent approaches combine language models with database knowledge to better understand user intent, employing pre-trained language models (PLMs) that can generate syntactically correct SQL and adapt to different domains.

[0009] Contextual learning has advanced text-to-SQL technology by leveraging large language models (LLMs) to provide well-defined task cues. Due to their scale and training, LLMs are able to better understand user queries, making them more effective in text-to-SQL applications. However, existing zero-shot learning methods are not yet accurate enough for text-to-SQL tasks, and existing system frameworks are not yet fully mature, resulting in insufficient stability, robustness, and scalability of the framework output. Summary of the Invention

[0010] To solve the above technical problems, the present invention proposes an automatic parsing method for zero-sample text to SQL based on a large language model (LLM), which solves the challenges encountered when using a large language model (LLM) for SQL query generation, ensures grammatical correctness and semantic alignment, and improves accuracy and reliability.

[0011] To achieve the above objectives, the present invention provides a method for automatic parsing of zero-shot text into SQL based on a large language model, comprising:

[0012] Defining an optimal problem, inputting the optimal problem into a zero-shot text-to-SQL parsing model for processing, and obtaining a target SQL statement;

[0013] The zero-sample text-to-SQL parsing model uses a preset large language model to convert the optimal problem into several candidate SQL statements, evaluates and self-ranks the candidate SQL statements, obtains ranking results, modifies the ranking results, and generates the target SQL statement.

[0014] Preferably, the optimization problem is defined as:

[0015]

[0016] Where Q is a natural language question, S is the database schema information, Y* is the ideal query, and f is the conversion function. is the loss function.

[0017] Preferably, the zero-shot text-to-SQL parsing model includes a generator, an evaluator, a weighted self-ranking module, and a self-correction module;

[0018] The generator is configured to convert the user's query intent into a structured SQL query using natural language processing technology in combination with database schema information, and automatically generate a number of candidate SQL statements;

[0019] The evaluator is used to determine whether each generated candidate SQL statement meets the expected query requirements through predefined rules or performance-based indicators, and evaluate the candidate SQL statements;

[0020] Weighted self-ranking module: used to perform weighted ranking on the evaluated SQL statements;

[0021] Self-correction module: used to improve and optimize SQL statements that result in empty lists or redundant results after weighted sorting.

[0022] Preferably, the optimal question is input into a zero-shot text-to-SQL parsing model for processing, including:

[0023] Generate several candidate SQL statements through the generator;

[0024] Based on the evaluator and predefined rules, the candidate SQL statements are evaluated to obtain evaluation SQL statements;

[0025] Perform weighted sorting on the evaluated SQL statements through a weighted self-sorting module to obtain a sorting result;

[0026] The sorting result is corrected using a self-correction module, and a corrected SQL statement is output, namely, the target SQL statement.

[0027] Preferably, generating a number of candidate SQL statements includes:

[0028] Converting the user's natural language query into a structured SQL statement, constructing a first prompt instruction by combining the description of the optimal problem with the database schema information, and inputting the first prompt instruction into the large language model;

[0029] A number of candidate SQL statements are generated based on the first prompt instruction, and the number of generated candidate SQL statements is controlled by adjusting a hyperparameter.

[0030] Preferably, the method of converting the user's natural language query into a structured SQL statement is:

[0031] G:(Q,S)→Y(2)

[0032] Where Q is a natural language question, S is the database schema information, Y is the generated SQL statement list, and G is the generator;

[0033] The method for generating several candidate SQL statements is as follows:

[0034] Y=G(Q,S;N)(3)

[0035] Where G(Q,S;N) is the generator function.

[0036] Preferably, the method for evaluating the candidate SQL statements is:

[0037] E:Y×R e →A(4)

[0038] Where R e is a rule containing specific SQL clause restrictions, Y is the list of generated SQL statements, A represents the validity of the predefined rule for Y, and E is the evaluator;

[0039] The evaluator must meet the conditions of formula (5):

[0040]

[0041] Where y is the SQL statement generated by G, contains LEFT JOIN, IN, OR is the predefined rule included, E(y, R e ) are SQL statements that the evaluator determines do not comply with the predefined rules.

[0042] Preferably, weighted sorting of the evaluated SQL statements includes:

[0043] Inputting the evaluated SQL statements into a database, executing and obtaining query results for each SQL statement, and clustering the evaluated SQL statements according to the query results to obtain clustering results;

[0044] Construct a second prompt instruction and input it into ChatGPT to obtain the ranking result of the clustered SQL statements, wherein the second prompt instruction includes the user's question, database schema, generated SQL statements and execution results;

[0045] The ranking results of the SQL statements in each category are weighted by the number of SQL statements in each category in the clustering results to obtain the final ranking result.

[0046] Preferably, the method for improving and optimizing SQL statements that result in empty lists or redundant results after weighted sorting is:

[0047] SC:(Y ranked ,ES,Q,S)→Y corrected (6)

[0048] Where SC is self-correction, Y ranked is the result of executing SQL statement, Q is natural language question, S is database schema information, Y corrected This is the revised SQL statement.

[0049] Compared with the prior art, the present invention has the following advantages and technical effects:

[0050] (1) This paper addresses the challenges encountered when using LLM for SQL query generation, ensuring syntactic correctness and semantic alignment, thereby improving accuracy and reliability; it proposes a zero-shot text-to-SQL parsing framework, ZEST, which enhances the interpretation of complex or ambiguous user queries and aligns SQL statements with user intent and database structure;

[0051] (2) In ZEST, we designed an integrated evaluation and optimization process to screen and optimize SQL statements, ensuring that they comply with standard SQL specifications and are better aligned with user queries and database contexts. Through extensive experimental verification using the Spider dataset and its variants, we demonstrated the ability of our model to handle diverse query complexities and database schemas, promoting the development of text-to-SQL conversion in zero-shot learning scenarios. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] The accompanying drawings, which constitute part of this application, are intended to provide a further understanding of this application. The exemplary embodiments and descriptions of this application are intended to explain this application and do not constitute an improper limitation on this application. In the accompanying drawings:

[0053] Figure 1 This is a diagram of a text-to-SQL task in the background technology of the present invention;

[0054] Figure 2 This is a diagram of the large language model architecture of an embodiment of the present invention;

[0055] Figure 3 Schematic diagram of a failure case of an embodiment of the present invention. DETAILED DESCRIPTION

[0056] It should be noted that, in the absence of conflict, the embodiments and features of the embodiments in this application can be combined with each other. The present application will be described in detail below with reference to the accompanying drawings and in combination with the embodiments.

[0057] It should be noted that the steps shown in the flowcharts of the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions, and that, although a logical order is shown in the flowcharts, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0058] The present invention proposes an automatic parsing method from zero-sample text to SQL based on a large language model, which is characterized by: Figure 2 ,include:

[0059] Define the optimal problem, input the optimal problem into the zero-shot text-to-SQL parsing model for processing, and obtain the target SQL statement;

[0060] Among them, the zero-sample text-to-SQL parsing model uses a preset large language model to convert the optimal problem into several candidate SQL statements, evaluates and self-ranks the candidate SQL statements, obtains the ranking results, modifies the ranking results, and generates the target SQL statement.

[0061] This embodiment proposes a zero-shot text-to-SQL parsing model (ZEST) that uses a large language model (LLM) for contextual learning. The model consists of four key components: generation, evaluation, a weighted self-ranking module, and a self-correction module, each of which aims to improve the accuracy of the SQL queries generated by the LLM. The generation component interprets user queries and converts them into SQL statements. The evaluation module assesses the accuracy of these SQL statements. The weighted self-ranking module prioritizes queries based on accuracy and relevance. Finally, the self-correction module optimizes the highest-ranked queries to better align with user intent and the specific requirements of the database. By separating and specializing these functions, ZEST enables more precise control and optimization at each stage of the text-to-SQL conversion process, thereby improving the robustness and scalability of the system.

[0062] Furthermore, the optimal problem is defined as:

[0063]

[0064] Where Q is a natural language question, S is the database schema information, Y* is the ideal query, and f is the conversion function. is the loss function.

[0065] Specifically, the problem definition is: The goal of text-to-SQL parsing is to construct an optimal problem whose purpose is to minimize the difference between the generated SQL query and the ideal SQL query.

[0066] Given a natural language question Q and a database schema S = (T, C, R), where T represents tables, C represents columns, and R represents relations, the task is to generate an SQL query Y that accurately reflects the syntactic and semantic intent of Q on S.

[0067] This optimization problem can be expressed mathematically as:

[0068]

[0069] Where f is a transformation function that maps the question Q and the database schema S into an SQL query Y; and Denotes a loss function that quantifies the deviation between the generated query Y and the ideal query Y*. Equation (1) emphasizes the importance of the generated query in terms of accuracy in expressing user intent and alignment with the structure and constraints of the database schema.

[0070] Furthermore, the zero-shot text-to-SQL parsing model includes:

[0071] Generator: Its main responsibility is to automatically generate SQL statements. This part uses advanced natural language processing technology combined with database schema information to convert the user's query intent into structured SQL queries.

[0072] Evaluator: This is used to evaluate the quality of SQL statements generated by the generator. It uses a series of predefined rules or performance-based indicators to determine whether each generated statement meets the expected query requirements.

[0073] Weighted Self-Ranking Module: This module ranks SQL statements after evaluation, prioritizing accuracy, query intent satisfaction, database schema understanding, and the impact of query assumptions. It compares query results for accuracy and completeness to ensure logically clear ranking, favoring concise SQL statements.

[0074] Self-correction module: This module is used to further refine and optimize SQL statements that result in empty lists or redundant results after weighted sorting. This step ensures that the statements accurately reflect the requirements.

[0075] Through the coordinated work of these four parts, high-quality SQL statements can be automatically generated, thus achieving state-of-the-art zero-performance on the Spider dataset.

[0076] Furthermore, the optimal question is input into the zero-shot text-to-SQL parsing model for processing, including:

[0077] Generate several candidate SQL statements through the generator;

[0078] Based on the evaluator and predefined rules, the candidate SQL statements are evaluated to obtain evaluation SQL statements;

[0079] Perform weighted sorting on the evaluated SQL statements through a weighted self-sorting module to obtain a sorting result;

[0080] The sorting result is corrected using a self-correction module, and a corrected SQL statement is output, namely, the target SQL statement.

[0081] Furthermore, several candidate SQL statements are generated, including:

[0082] Convert the user's natural language query into a structured SQL statement, combine the optimal problem description with the database schema information, build the first prompt instruction, and input the zero-sample text to SQL parsing model;

[0083] A number of candidate SQL statements are generated based on the first prompt instruction, and the number of generated candidate SQL statements is controlled by adjusting a hyperparameter.

[0084] Specifically, to automatically generate SQL statements from natural language questions Q, this example builds a large language model (LLM) based on the ChatGPT generator. For each question Q and corresponding database schema S, the question description is combined with the database schema information to construct a first prompt instruction, which is then input into the LLM. Based on this prompt, the LLM generates N SQL statements at a time, where N is an adjustable hyperparameter that controls the number of generated SQL statements. The value of N is adjusted experimentally to evaluate its impact on the overall framework performance.

[0085] By referencing previous work, we optimized the prompt structure within the generator, aiming to improve the quality and diversity of generated SQL statements. Since LLMs can sacrifice quality while maintaining answer diversity, and given the same prompts, the output SQL statements can be correct or incorrect, generating multiple SQL statements aims to leverage the subsequent evaluator and self-ranking module to select the correct SQL statements, addressing the issue of unstable LLM output.

[0086] In this example, the generator is represented by G, which plays a key role in the overall framework. Its task is to convert the user's natural language query into a structured SQL statement. This process can be expressed mathematically as follows:

[0087] G:(Q,S)→Y(2)

[0088] Among them, Q represents the user's natural language question, S represents the database schema information, and Y is the generated SQL statement list.

[0089] For each query Q and corresponding database S, a hint is constructed that combines these two elements. This hint is then fed into ChatGPT, which generates multiple SQL statements. The number of generated statements is controlled by a hyperparameter N, which is experimentally tuned to optimize the performance of the overall framework. Therefore, the generator function G produces a list Y of N SQL statements based on Q and S, which can be described as:

[0090] Y=G(Q,S;N)(3)

[0091] Here, G(Q, S; N) represents a generator function that generates a set of N SQL statements in a single iteration based on the natural language query Q and the database schema S.

[0092] The structure of prompts in the generator is optimized to improve the diversity and quality of SQL statements. The reason for generating multiple SQL statements in one iteration is to take into account the variability of ChatGPT output, so that subsequent modules (evaluator and weighted self-ranking module) can filter and refine these statements, address the challenge of output variability, and enhance overall reliability.

[0093] Furthermore, the method for evaluating candidate SQL statements is:

[0094] E:Y×R e →A(4)

[0095] Where R e is a rule containing specific SQL clause restrictions, Y is the list of generated SQL statements, A represents the validity of the predefined rule for Y, and E is the evaluator;

[0096] The evaluator must meet the conditions of formula (5):

[0097]

[0098] Where y is the SQL statement generated by G, contains LEFT JOIN, IN, OR is the predefined rule included, E(y, R e ) are SQL statements that the evaluator determines do not comply with the predefined rules.

[0099] Specifically, to evaluate the quality of the SQL statements generated by the generator, this embodiment uses a set of predefined rules that are consistent with the prompts used by the generator to ensure that each SQL statement meets the expected query requirements. Specifically, these rules prohibit the use of left joins, IN, or OR clauses, as their inclusion may lead to inaccurate query results, thereby affecting the final result. If the generated SQL statement contains any of these disallowed keywords, the evaluator module will filter them out to maintain the integrity of the SQL statement.

[0100] The evaluator is a function that evaluates the SQL statements generated by the generator. It applies predefined rules to output quality assessment results, specifically:

[0101] E:Y×R e →A

[0102] Among them, R e is a rule containing specific SQL clause restrictions, Y is the generated SQL statement list, and A represents the validity of the predefined rule for Y.

[0103] The assessor ensures that:

[0104]

[0105] Where y is the SQL statement generated by G, contains LEFT JOIN, IN, OR is the predefined rule included, E(y, R e ) are SQL statements that the evaluator determines do not comply with the predefined rules.

[0106] This ensures that only the rule R e The SQL statement is considered valid, thus maintaining the integrity of the results produced by the generator.

[0107] Furthermore, the evaluated SQL statements are weighted and ranked, including:

[0108] Inputting the evaluated SQL statements into a database, executing and obtaining query results for each SQL statement, and clustering the evaluated SQL statements according to the query results to obtain clustering results;

[0109] Construct a second prompt instruction and input it into ChatGPT to obtain the ranking result of the clustered SQL statements, wherein the second prompt instruction includes the user's question, database schema, generated SQL statements and execution results;

[0110] The ranking results of the SQL statements in each category are weighted by the number of SQL statements in each category in the clustering results to obtain the final ranking result.

[0111] Specifically, in order to select SQL focusing on aspects such as query intent realization and database architecture understanding from the generated candidate SQLs, this embodiment adopts a weighted self-ranking method.

[0112] Denoting the weighted self-ranking module as W, this process can be expressed as:

[0113] W:(Y filtered ,Q,S,EO)→Y ranked

[0114] Among them, Y filtered is a list of SQL statements filtered by the evaluator E, the user's natural language query is represented by Q, and S represents the database schema. EO refers to the execution result set of each SQL statement in Y, Y ranked It is a list of SQL statements sorted by the weighting mechanism W.

[0115] Consider the frequency score calculation of each type of SQL statement, where the score for the i-th type (i∈{1,……,|Counter|}) is defined as:

[0116] FS[i]=min(log(1+Counter[i]),τ rep )

[0117] Among them, Counter[i] represents the count of the i-th type of SQL statement in list Y, τ rep is a predefined duplicate scoring threshold and Counter is a dictionary.

[0118] The ranking score is calculated for each SQL statement, where the value of j is in the list {1,...,|Y ranked All elements in |} are defined as follows:

[0119]

[0120] In this equation, Y ranked [j] is the list Y ranked The ranking position of the j-th SQL statement in .

[0121] Table 1

[0122]

[0123] The weighted self-ranking algorithm process is described in Table 1. First, the SQL statements filtered by the evaluation module are input into the database to execute and obtain the query results for each SQL statement. Second, the generated SQL statements are clustered based on the query results to reduce duplicate SQL statements. This approach has the advantage of reducing subsequent computational complexity and minimizing the risk of errors. The result of clustering is that the query results of SQL statements within each cluster are identical. This is because, for ChatGPT's self-ranking, a large number of duplicate SQL statements can create a strong illusion for ChatGPT, causing it to believe that these incorrect SQL statements are correct, resulting in incorrect SQL statements being ranked higher and correct SQL statements being ranked lower. Next, a second prompt instruction is constructed, containing the user's question, the database schema, the generated SQL statements, and their execution results. This prompt instruction is input into ChatGPT to obtain the ranking results of the clustered SQL statements. Finally, the ranking results of the SQL statements within each category are weighted based on the number of SQL statements in each category in the clustering results to obtain the final ranking result.

[0124] Furthermore, the method for improving and optimizing SQL statements that result in empty lists or redundant results after weighted sorting is:

[0125] SC:(Y ranked ,ES,Q,S)→Y corrected (6)

[0126] Where Y ranked is the result of executing SQL statement, Q is user query, S is database mode information, Y corrected This is the revised SQL statement.

[0127] Specifically, we denote self-correction as SC, which aims to improve and optimize SQL statements ranked by the weighted self-ranking module. It mainly focuses on correcting SQL statements that lead to empty results or redundant execution results.

[0128] The self-correcting operation can be expressed mathematically as follows:

[0129] SC:(Y ranked ,ES,Q,S)→Y corrected

[0130] Among them, ES represents Y ranked The execution result of the SQL statement in , Q is the natural language question, S is the database schema information, Y corrected This is the revised SQL statement.

[0131] The self-correction algorithm flow is summarized in Table 2. It inputs the sorted SQL statement Y rankedThe output is a modified SQL statement Y. corrected , the statement can produce non-empty execution results and meet the user's requirements, including the possible elimination of duplicate results. The algorithmic steps are designed to ensure that the generated SQL statement is not only syntactically and semantically accurate, but also closely aligned with the specific query requirements.

[0132] Table 2

[0133]

[0134] This paper addresses the challenges encountered when using LLM for SQL query generation, ensuring syntactic correctness and semantic alignment, thereby improving accuracy and reliability. The main contributions are as follows:

[0135] A zero-shot text-to-SQL parsing framework, ZEST, is proposed to enhance the interpretation of complex or ambiguous user queries and align SQL statements with user intent and database structure.

[0136] An integrated evaluation and optimization process is designed in ZEST to screen and optimize SQL statements, ensuring that they comply with standard SQL specifications and are better aligned with user queries and database context.

[0137] Through extensive experimental validation using the Spider dataset and its variants, we demonstrate our ability to handle diverse query complexity and database schemas, advancing the field of text-to-SQL conversion in zero-shot learning scenarios.

[0138] In order to more clearly express the technical solution of the present invention, the following comparative experiments are provided to illustrate the beneficial effects of the present invention:

[0139] Experimental setup

[0140] Datasets: Experiments were conducted on four datasets: Spider, Spider-DK, Spider-SYN, and Spider-Realistic. Spider is a large-scale, cross-domain text-to-SQL dataset designed to advance natural language interfaces for databases. It contains 10,181 queries and 5,693 unique SQL queries, covering 200 databases in 138 domains. This dataset challenges the model's ability to generalize to novel SQL queries and unseen architectures.

[0141] Spider-DK, Spider-SYN, and Spider-Realistic are specialized subsets of Spider. Spider-DK tests domain knowledge, Spider-SYN examines synonym robustness, and Spider-Realistic challenges the model to infer SQL queries from natural language without explicit column names.

[0142] Baseline Models: To evaluate the proposed framework, we compare our method with several baseline models, covering fine-tuning, few-shot, and zero-shot methods. For fine-tuning, we consider RATSQL, which uses relation-aware self-attention for architecture encoding and linking; PICARD, which constrains the autoregressive decoder via incremental parsing; Graphix, a hybrid model with graph-aware layers; and RESDSQL, which decouples architecture linking and skeleton parsing. Few-shot methods include SC-Prompt, which decomposes text-to-SQL translation into simpler subtasks, and DIN-SQL, which decomposes the generation problem into subproblems. For zero-shot methods, we study ChatGPT-SQL, a baseline on the Spider dataset that converts natural language to SQL without additional labeled data, and C3+ChatGPT, which uses optimized prompts to generate SQL.

[0143] Evaluation Metrics: Following previous research, we evaluated the performance of our experimental models across the entire dataset using the most commonly used metrics: execution accuracy (EX) and test suite accuracy (TS). EX focuses on the semantic correctness of the predicted query. For each query, we compare the results of executing the predicted query against the actual query on a selected database instance. TS serves as the official evaluation metric for the Spider dataset. By evaluating EX on a selected set of randomly generated databases, TS provides an upper bound on the semantic accuracy of the predicted query.

[0144] Experimental results

[0145] Table 3

[0146]

[0147] Table 4

[0148]

[0149] Table 3 shows the excellent performance of the proposed model on the Spider dataset validation set, highlighting the significant improvement in accuracy compared to the baseline model. Specifically, the model achieved 84.0% in execution accuracy (EX) and 82.2% in translation accuracy (TS). It is worth noting that the proposed method outperforms almost all baseline models, only differing by 0.1% in EX and 0.6% in TS from RESDSQL, thus confirming the effectiveness of the proposed framework. In the zero-shot setting, the proposed model improves EX by 11.7% and TS by 12% compared to ChatGPT-SQL. In addition, compared with C3+ChatGPT, EX is improved by 2.2% and TS is improved by 2.4%. This performance is particularly significant considering that the proposed model focuses on zero-shot scenarios, while DIN-SQL adopts a few-shot-oriented approach.

[0150] The DIN-SQL model uses GPT-4, which, while superior to GPT-Turbo-3.5, suffers from slower inference speed and higher cost. Our model, using the more cost-effective GPT-Turbo-3.5, outperforms DIN-SQL by 1.2% in EX and 0.5% in TS. This marks state-of-the-art zero-shot performance on the Spider dataset. Furthermore, our model produces more stable output than the baseline model, reducing the unreliability associated with ChatGPT's variability.

[0151] Recent research has shown that existing models are vulnerable to perturbations in text-to-SQL tasks, as removing or replacing explicit pattern items with semantically consistent terms (e.g., synonyms) increases the difficulty of pattern linking. Consequently, more research is currently focused on improving model robustness. To validate the robustness of our proposed model, we evaluated it on three challenging Spider variant datasets: Spider-DK, Spider-Syn, and Spider-Realistic.

[0152] Table 4 shows the EX metric performance of our model and two other zero-shot methods on the Spider-DK, Spider-Syn, and Spider-Realistic datasets. According to previous work, the TS test is applicable only to the Spider dataset. Therefore, for the Spider-DK, Spider-Syn, and Spider-Realistic datasets, only EX was used as the performance metric. Our model achieved 73.8%, 70.2%, and 84.1% performance on these datasets, respectively, outperforming the other two methods on all datasets. Specifically, ZEST outperformed ChatGPT-SQL and C3+ChatGPT by 11.2% and 2.2%, respectively, on the SPIDER-DK dataset. On the SPIDER-SYN dataset, ZEST outperformed these two methods by 11.6% and 1.8%, respectively. On the SPIDER-REALISTIC dataset, ZEST outperformed ChatGPT-SQL and C3+ChatGPT by 20.7% and 4.0%, respectively. These results highlight the effectiveness of our model, particularly in handling the real-world complexity of the SPIDER-REALISTIC dataset.

[0153] Ablation experiment:

[0154] Table 5

[0155]

[0156] We conducted an ablation experiment on the validation set of the Spider dataset, and the results are shown in Table 2. We removed each module of the Zero-Shot End-to-End Text-to-SQL Transformation (ZEST) model and compared their performance on exact matching (EX).

[0157] As can be seen from Table 5, ZEST performs better than the model with the removed modules in terms of exact matching. This indicates that each module of ZEST contributes to the improvement of model performance.

[0158] First, we attempted to remove the weighted self-ranking module. As shown in Table 5, removing this module resulted in a 1% performance drop, demonstrating its importance in optimizing the model's output. Since this module is primarily responsible for ranking generated SQL statements to select those that best align with user intent, this performance drop suggests that weighted self-ranking contributes to ensuring output quality, albeit a modest one.

[0159] Next, we removed the evaluator. This removal resulted in a 0.3% performance drop, a minimal change. The evaluator's function is to filter out SQL statements that don't conform to predefined rules, ensuring output accuracy. This small performance drop suggests that this module doesn't play a critical role in ensuring output accuracy.

[0160] Finally, we removed the self-correction module. The results show that this also resulted in a 1% drop in exact matching performance, indicating that it plays a key role in improving the overall performance of the model and the quality of its output.

[0161] Error analysis:

[0162] To better understand the remaining issues of ZEST failure, we analyzed cases in the Spider validation set where ZEST's SQL predictions differed from the actual SQL execution results. We then manually inspected these failure cases and classified them into five categories, such as Figure 3 These categories are discussed below.

[0163] Schema Linking: This category includes the most query failures, including cases where the model fails to recognize the column name, table name, or entity mentioned in the question. For example, if a user asks, "Which month had the highest sales in 2020?", the model mistakenly selects the "Product Name" column instead of the "Sales" column for the query. In some cases, the query requires an aggregate function, but selects a matching column name.

[0164] Joins: This category includes queries that require a join operation, but the model cannot identify all necessary tables or the correct foreign keys to connect them. Join operations are complex and require the model to understand the relational structure of the database. Difficulty identifying the necessary tables or correct foreign keys may be due to insufficient understanding of these relationships or an inability to infer the join based on the query context.

[0165] Grouping: This error occurs when the model fails to recognize that a grouping clause is needed or uses the wrong column for grouping. This error indicates a challenge in recognizing when data needs to be aggregated based on a specific attribute. ZEST may have difficulty understanding the intent behind the grouping or identifying the correct grouping column, which may be due to ambiguity in the query language or a lack of understanding of the data structure.

[0166] Nested: In this category, the golden query used nested or set operations, but the model failed to recognize the nested structure or failed to detect the correct nested or set operation.

[0167] Other: This category, accounting for 23.53%, is an umbrella term for errors that don't fit into the previously defined categories, such as queries with extra predicates, missing predicates, or inappropriate use of the DISTINCT or DESC keywords. Issues such as inappropriate use of SQL keywords or missing predicates indicate a lack of understanding of SQL syntax and semantics. This may stem from the zero-shot setting or inherent limitations of ChatGPT's natural language understanding capabilities, particularly in interpreting more subtle or complex query requirements.

[0168] The above are merely preferred embodiments of the present application, but the scope of protection of the present application is not limited thereto. Any changes or substitutions that can be easily conceived by a person skilled in the art within the technical scope disclosed in this application should be included in the scope of protection of the present application. Therefore, the scope of protection of the present application should be based on the scope of protection of the claims.

Claims

1. A zero-shot text to SQL automatic parsing method based on a large language model, characterized in that: include: Defining an optimal problem, inputting the optimal problem into a zero-shot text-to-SQL parsing model for processing, and obtaining a target SQL statement; The zero-shot text-to-SQL parsing model uses a preset large language model to convert the optimal problem into several candidate SQL statements, evaluates and self-ranks the candidate SQL statements, obtains ranking results, modifies the ranking results, and generates the target SQL statement; The zero-shot text-to-SQL parsing model includes a generator, an evaluator, a weighted self-ranking module, and a self-correction module; The generator is configured to convert the user's query intent into a structured SQL query using natural language processing technology in combination with database schema information, and automatically generate a number of candidate SQL statements; The evaluator is used to determine whether each generated candidate SQL statement meets the expected query requirements through predefined rules or performance-based indicators, and evaluate the candidate SQL statements; Weighted self-ranking module: used to perform weighted ranking on the evaluated SQL statements; Self-correction module: used to improve and optimize SQL statements that result in empty lists or redundant results after weighted sorting; The method for evaluating the candidate SQL statements is: （4） Where, R e For rules that contain specific SQL clause restrictions, Y For the generated SQL statement list, A Indicates the validity of the predefined rules for Y, E For the assessor; The evaluator must meet the conditions of formula (5): =’Invalid’(5) Where, y The SQL statement generated for G contains LEFT JOIN, IN, OR as predefined rules. E ( y , R e ) is the SQL statement determined by the evaluator not to comply with the predefined rules; The evaluated SQL statements are weightedly sorted, including: Inputting the evaluated SQL statements into a database, executing and obtaining query results for each SQL statement, and clustering the evaluated SQL statements according to the query results to obtain clustering results; Construct a second prompt instruction and input it into ChatGPT to obtain the ranking result of the clustered SQL statements, wherein the second prompt instruction includes the user's question, database schema, generated SQL statements and execution results; The ranking results of the SQL statements in each category are weighted by the number of SQL statements in each category in the clustering results to obtain the final ranking result.

2. The automatic parsing method for zero-shot text to SQL based on a large language model according to claim 1, characterized in that: The optimization problem is defined as: （1） Where, Q For natural language problems, S For database schema information, Y * is the ideal query, f is the conversion function, ( ) is the loss function.

3. The automatic parsing method for zero-shot text to SQL based on a large language model according to claim 1, characterized in that: The optimal problem is input into the zero-shot text-to-SQL parsing model for processing, including: Generate several candidate SQL statements through the generator; Based on the evaluator and predefined rules, the candidate SQL statements are evaluated to obtain evaluation SQL statements; Perform weighted sorting on the evaluated SQL statements through a weighted self-sorting module to obtain a sorting result; The sorting result is corrected using a self-correction module, and a corrected SQL statement is output, namely, the target SQL statement.

4. The automatic parsing method for zero-shot text to SQL based on a large language model according to claim 3, characterized in that: Generate several candidate SQL statements, including: Converting the user's natural language query into a structured SQL statement, constructing a first prompt instruction by combining the description of the optimal problem with the database schema information, and inputting the first prompt instruction into the large language model; A number of candidate SQL statements are generated based on the first prompt instruction, and the number of generated candidate SQL statements is controlled by adjusting a hyperparameter.

5. The automatic parsing method for zero-shot text to SQL based on a large language model according to claim 4 is characterized in that: The method of converting the user's natural language query into a structured SQL statement is: （2） Where, Q For natural language problems, S For database schema information, Y For the generated SQL statement list, G For the generator; The method for generating several candidate SQL statements is as follows: （3） Where, is a generator function.

6. The automatic parsing method for zero-shot text to SQL based on a large language model according to claim 1, characterized in that: Methods for completing and optimizing SQL statements that result in empty lists or redundant results after weighted sorting are: （6） Where, SC For self-correction, Y ranked The result of executing the SQL statement. Q For natural language problems, S For database schema information, Y corrected For the revised SQL statement, E For the evaluator.

Images