A method and system for generating SQL query statements based on a large language model

Abstract

This invention discloses a method and system for generating SQL query statements based on a large language model. The method includes: 1) constructing a graph structure based on the schema of a target database, generating a schema context vector that integrates the semantics of the database structure based on this graph structure; fusing the schema context vector with the natural language query input by the user to form a schema-enhanced context representation; 2) a multi-stage generation module generating SQL query statements based on the schema-enhanced context representation; in the coarse generation stage, calling the large language model to generate multiple candidate SQL statements based on the schema-enhanced context representation; in the fine selection stage, evaluating the candidate SQL statements; and then selecting the optimal candidate SQL as the final SQL query statement based on the evaluation results of each candidate SQL. This invention effectively avoids SQL generation failures caused by semantic ambiguity and logical errors, significantly improving the accuracy of the final result.

Description

Technical Field

[0001] This invention belongs to the interdisciplinary field of natural language processing and database technology, specifically involving a method and system for generating SQL query statements based on a large language model (LLM). In particular, it is a technology that enhances the large language model's ability to perceive database patterns and combines a multi-stage generation and dynamic feedback optimization mechanism to achieve high accuracy and high reliability in the conversion of natural language to structured query language (NL2SQL). Background Technology

[0002] Natural Language to SQL (NL2SQL) technology is a crucial step in enabling interaction between natural language and databases. In recent years, end-to-end generation methods based on Large Language Models (LLMs) have become mainstream. However, LLMs still face many inherent challenges when dealing with practical NL2SQL tasks: First, the ambiguity of natural language can easily lead LLMs to misunderstand the query intent and generate logically incorrect SQL; second, existing methods do not make sufficient use of database schema information, often resulting in mismatches between the generated results and the actual data structure, leading to problems such as incorrect table names and invalid field references; furthermore, most solutions lack effective dynamic verification and optimization mechanisms, making it impossible to verify and correct the one-time generated results of LLMs, thus compromising reliability.

[0003] To address these challenges, the industry has proposed methods such as template-based LLM fine-tuning and post-processing validation. However, these methods generally suffer from limitations such as poor flexibility, weak generalization ability, and inability to handle deep-seated logical errors. Therefore, there is an urgent need for a new NL2SQL method that can deeply integrate database context and possess multi-stage validation and self-optimization capabilities to significantly improve the accuracy and usability of generated SQL. Summary of the Invention

[0004] To address the core problems of existing natural language to SQL (NL2SQL) methods based on Large Language Models (LLM), such as weak natural language ambiguity resolution, insufficient database schema awareness, and lack of verification and self-optimization mechanisms for generated results, the present invention aims to provide a method and system for generating SQL query statements based on Large Language Models.

[0005] The core of this invention lies in achieving closed-loop optimization from semantic understanding and pattern adaptation to execution feedback through an innovative architecture that integrates multiple modules, mainly including the following:

[0006] 1. Constructing a Schema-Aware Enhancement Module: Addressing the issue of insufficient understanding of database structure by LLMs, a deep metadata parsing and context vector construction mechanism was designed. This module parses database schema information, extracts metadata such as tables, fields, and constraints, and uses graph neural network (GNN) technology to encode complex schema relationships into dense schema context vectors. These vectors are integrated with user queries through attention mechanisms and other methods, providing LLMs with rich structure-aware context and fundamentally reducing generation errors caused by schema confusion.

[0007] 2. Design a multi-stage SQL generation and optimization framework: To address the unreliability of one-time generation results, the generation process is innovatively decomposed into two stages: "coarse generation" and "fine selection." In the coarse generation stage, multiple candidate SQL statements are generated in parallel using a schema-enhanced LLM to cover diverse intent implementation paths. In the fine selection stage, a lightweight validation model is introduced to efficiently score and rank candidate SQL statements through static analysis (syntax and semantic checks) and execution plan simulation (performance and logical evaluation), selecting the optimal candidates and significantly improving the initial quality of the generated results.

[0008] 3. Establishing a Dynamic Feedback and Iterative Correction Mechanism: To overcome the limitations of traditional methods lacking verification, a closed-loop optimization system based on sandbox execution was constructed. The system executes the refined SQL in an isolated sandbox and captures execution feedback (error codes, abnormal results, performance data) in real time. Based on the feedback, a collaborative strategy of a rule engine (handling common errors) and a reinforcement learning model (handling complex logical errors) is adopted to automatically generate corrective actions (such as adjusting the JOIN order and correcting conditional logic), iteratively correcting the SQL until it executes successfully and the result is correct.

[0009] 4. A hybrid supervised continuous learning mechanism is proposed: To achieve the system's self-evolution, a hybrid training paradigm integrating reinforcement learning (RL) and contrastive learning is designed. RL uses SQL execution success rate and performance as reward signals to optimize and correct strategies; contrastive learning, on the other hand, constructs sample pairs of correct and incorrect SQL queries to increase their semantic distance and enhance the model's ability to identify correct patterns. This mechanism utilizes runtime data to continuously optimize model parameters, enabling the system to adapt to new query patterns and database changes.

[0010] This invention focuses on improving the accuracy, robustness, efficiency, and deep adaptability to specific database schemas of the transformation. Its core key points are as follows: 1. Multi-stage SQL generation and optimization framework: This invention adopts a three-stage processing flow of "coarse generation → fine screening → dynamic optimization".

[0011] Coarse generation stage: Multiple candidate SQL statements are generated in parallel using schema-enhanced LLM to ensure coverage of the potential effective solution space.

[0012] Fine screening stage: Introduce a lightweight verification model (based on simulated execution plan or pre-execution result analysis) to efficiently sort and initially filter candidate SQL, significantly reducing the cost of subsequent verification.

[0013] Dynamic optimization stage: Based on the results of the fine screening, further verification and correction are carried out to form a closed-loop optimization.

[0014] 2. Efficient Schema Context Awareness Mechanism: A unique method for constructing and fusing schema context vectors is proposed. This method deeply parses the target database schema information, extracting core metadata including tables, fields, data types, and key constraints (especially foreign key relationships); it compresses complex schema information into dense schema context vectors through specific encoding strategies (such as structured text description embedding and graph neural network representation); and it innovatively designs a fusion strategy for schema vectors and user queries (such as concatenation, attention mechanisms, and gating fusion) to ensure that the LLM deeply understands the target database structure when generating SQL, significantly reducing schema-related semantic ambiguity and errors.

[0015] 3. Dynamic Feedback and Iterative Correction Mechanism Based on Sandbox Execution: A secure sandbox environment was designed for the actual verification of candidate SQL statements. This mechanism dynamically captures execution results (such as error logs, exceptions, empty result sets, and performance metrics). Based on the captured feedback information, an error-driven SQL correction algorithm was developed (e.g., automatically adjusting JOIN order / type, supplementing missing WHERE conditions, correcting function / aggregate usage, and handling null value logic, etc.), realizing automated iterative optimization and error recovery of SQL statements, greatly improving the correctness and usability of the final result.

[0016] 4. Hybrid Supervised Training Technique Integrating Reinforcement and Contrastive Learning: A joint optimization model training mechanism is proposed. Reinforcement Learning (RL): The success rate, accuracy, and efficiency of SQL execution in the sandbox environment are used as core reward signals to guide the model in learning to generate better and more reliable SQL. Contrastive Learning: Positive sample (correct SQL) and negative sample (incorrect / suboptimal SQL) pairs are constructed to train the model to distinguish subtle semantic or structural differences, enhancing the model's ability to recognize correct SQL patterns and its robustness. This hybrid supervision mechanism works synergistically, fully utilizing execution feedback and sample comparison information to continuously improve the generalization ability and accuracy of LLM on the NL2SQL task.

[0017] The technical solution of this invention is as follows: A method for generating SQL query statements based on a large language model, comprising the following steps: 1) The pattern-aware enhancement module extracts metadata from the target database, identifies the relationships between tables in the target database based on the metadata, and stores them in a pattern knowledge base. Then, a pre-trained language model is used to encode the metadata into low-dimensional vectors. Next, the pattern schema of the target database is constructed into a graph structure, where nodes are tables and fields in the target database, and edges are the relationships between primary and foreign keys in the target database. The graph neural network is used to perform embedding learning on the graph structure to generate a pattern context vector that integrates the semantics of the database structure. The pattern context vector is fused with the natural language query input by the user through an attention mechanism to form a pattern-enhanced context representation. 2) The multi-stage generation module generates SQL query statements based on the enhanced context representation of the pattern, including a coarse generation stage and a fine screening stage; 21) In the coarse generation stage, the large language model is invoked to generate multiple candidate SQL statements based on the pattern-enhanced context representation, which are used to cover multiple implementation paths of the user's query intent; 22) In the fine screening stage, a lightweight verification model is used to evaluate the candidate SQL statements: First, static analysis is performed on the candidate SQL statements, and the syntax correctness of the candidate SQL is checked using an integrated syntax parser. The existence of field / table names in the target database schema and whether the data types of the values ​​in the query conditions match the corresponding field definitions in the target database are also verified. Then, the candidate SQL statements after static analysis are simulated and executed to estimate the execution cost and efficiency. Finally, the optimal candidate SQL is selected as the final SQL query statement based on the evaluation results of each candidate SQL.

[0018] Preferably, the multi-stage generation module is optimized through a dynamic feedback optimization module, and the method is as follows: 1) Execute the best candidate SQL in a database isolated from the production environment in a sandbox, and capture the execution feedback in real time; the database schema in the sandbox is consistent with the target database schema. 2) Correcting the optimal candidate SQL based on feedback: When the feedback error is a common error that can be patterned, the rule engine is invoked to correct the optimal candidate SQL based on the built-in expert knowledge base according to the common error that can be patterned; when the feedback error is a common error that cannot be patterned, it is classified and extracted into a structured error feature vector and input into the reinforcement learning model; the reinforcement learning model takes the error feature vector as the state and outputs the correction action, the action space is defined as the adjustment operation of the SQL structure, and then the corrected optimal candidate SQL is re-input into the database in the sandbox for execution, and the process is repeated until success is achieved; 3) The policy network of the reinforcement learning model is updated using the SQL execution success rate, result accuracy, and execution efficiency as a comprehensive reward.

[0019] Preferably, the feedback includes the error code, exception message, number of rows in the result set, and execution time returned by the database.

[0020] Preferably, the evaluation results include grammatical compliance score, logical integrity score, and performance prediction score.

[0021] Preferably, the metadata includes table name, field name, data type, primary and foreign key constraints, and index information.

[0022] Preferably, the extracted metadata is structured using an abstract syntax tree-based parsing technique to identify the relationships between tables.

[0023] Preferably, the large language model generates multiple candidate SQL statements through bundle search based on the pattern-enhanced contextual representation.

[0024] Preferably, the large language model is GPT-4 or Llama 3.

[0025] A SQL query statement generation system based on a large language model is characterized by including a pattern-aware enhancement module and a multi-stage generation module. The pattern-aware enhancement module is used to extract metadata from the target database, identify the relationships between tables in the target database based on the metadata, and store them in a pattern knowledge base. Then, a pre-trained language model is used to encode the metadata into low-dimensional vectors. Next, the pattern schema of the target database is constructed into a graph structure, where nodes represent tables and fields in the target database, and edges represent the relationships between primary and foreign keys. A graph neural network is used to learn the embedding of this graph structure, generating a pattern context vector that integrates the semantics of the database structure. Finally, an attention mechanism is used to fuse the pattern context vector with the user-input natural language query to form a pattern-enhanced context representation. The multi-stage generation module is used to generate SQL query statements based on the pattern-enhanced context representation, including a coarse generation stage and a fine selection stage; 21) In the coarse generation stage, a large language model is called to generate multiple candidate SQL statements based on the pattern-enhanced context representation, which are used to cover multiple implementation paths of the user's query intent; 22) In the fine selection stage, a lightweight verification model is called to evaluate the candidate SQL statements: first, static analysis processing is performed on the candidate SQL statements, the syntactic correctness of the candidate SQL is checked using an integrated syntax parser, and the existence of field / table names in the target database schema and whether the data type of the values ​​in the query conditions matches the corresponding field definition in the target database are verified; then, the candidate SQL statements after static analysis processing are simulated and the execution cost and efficiency are estimated; then, the optimal candidate SQL is selected as the final SQL query statement based on the evaluation results of each candidate SQL.

[0026] Preferably, it also includes a dynamic feedback optimization module for optimizing the multi-stage generation module. The method is as follows: 1) Execute the optimal candidate SQL in a database in a sandbox isolated from the production environment, and capture the execution feedback in real time; the database schema in the sandbox is consistent with the target database schema; 2) Correct the optimal candidate SQL based on the feedback: when the feedback error is a common error that can be patterned, the rule engine is called to correct the optimal candidate SQL based on the built-in expert knowledge base according to the common error that can be patterned; when the feedback error is a common error that cannot be patterned, it is classified and extracted into a structured error feature vector and input into the reinforcement learning model; the reinforcement learning model takes the error feature vector as the state and outputs the correction action, the action space is defined as the adjustment operation of the SQL structure, and then the corrected optimal candidate SQL is re-input into the database in the sandbox for execution, and the loop iterates until success; 3) Use the SQL execution success rate, result accuracy and execution efficiency as the comprehensive reward, and update the policy network of the reinforcement learning model through the near-end policy optimization algorithm.

[0027] Compared with the prior art, the technical solution provided by the present invention can bring the following significant advantages: 1. Higher accuracy and reliability: By combining multi-candidate generation of a large language model with fine screening of a validation model, and introducing execution-based dynamic feedback optimization, SQL generation failures caused by semantic ambiguity and logical errors are effectively avoided, significantly improving the accuracy of the final result.

[0028] 2. Enhanced schema adaptability: By injecting deeply encoded schema context vectors into large language models, enabling them to deeply understand database structures, errors caused by schema mismatch (such as table name confusion and field misuse) are fundamentally reduced.

[0029] 3. Superior generalization and self-evolution capabilities: By utilizing a hybrid supervised continuous learning mechanism, the system can learn autonomously from operational feedback, adapt to complex query patterns not covered by training data and new database environments, and improve its applicability in long-tail scenarios.

[0030] 4. Improved interpretability and controllability: Provides a transparent multi-stage processing flow and execution feedback logs, making the generation process and error causes traceable, providing a clear path for manual intervention and debugging, and enhancing the credibility of the system. Attached Figure Description

[0031] Figure 1 This is a flowchart of the present invention. Detailed Implementation

[0032] The present invention will now be described in further detail with reference to the accompanying drawings. The examples given are only for explaining the present invention and are not intended to limit the scope of the present invention.

[0033] The implementation of this system mainly involves the coordinated operation of four core modules: pattern-aware enhancement, multi-stage generation, dynamic feedback optimization, and hybrid supervised learning. The overall system operation flow is as follows: Figure 1 As shown.

[0034] 1. Implementation of the Pattern Awareness Enhancement Module This module aims to enable the LLM to deeply understand the structure and constraints of the target database before generating SQL. Its implementation consists of the following two steps.

[0035] (1) Metadata parsing: The system first connects to the target database and automatically scans the system tables (such as the TABLES and COLUMNS tables in information_schema) to extract all table names, field names, data types, primary and foreign key constraints, and index information. Using the parsing technology based on Abstract Syntax Tree (AST), the extracted metadata is processed in a structured manner to identify the relationships between tables (such as through FOREIGN KEY constraints) and stored in the schema knowledge base.

[0036] (2) Context Vector Construction: A pre-trained language model (such as BERT) is used to encode textual metadata such as table names and field names into low-dimensional vectors. Subsequently, the schema of the entire database is constructed as a graph structure, where nodes represent tables and fields in the target database, and edges represent primary and foreign keys and other relationships in the target database. Graph neural networks (GNNs), such as Relational Graph Convolutional Networks (R-GCN), are used to learn the embeddings of this graph, generating a schema context vector that integrates the semantics of the database structure. This vector is then fused with the user's natural language query through an attention mechanism to form a schema-enhanced context representation.

[0037] 2. Implementation of the multi-stage generation module This module breaks down the SQL generation process into two stages: "coarse generation" and "fine filtering," which are completed by the control and scheduling framework strategically calling the large language model and other components.

[0038] (1) Coarse generation stage: The system calls a large language model (such as GPT-4 or Llama 3), and generates multiple (such as Top-5) candidate SQL statements through beam search based on pattern-enhanced context representation to cover multiple implementation paths of user query intent.

[0039] (2) Fine-tuning stage: A lightweight validation model is introduced to evaluate and quantify candidate SQL from multiple dimensions. This stage first performs static analysis: the integrated parser is used to check the syntactic correctness of candidate SQL, and to verify whether the field / table names exist in the target database schema, and whether the data types of the values ​​in the query conditions match the corresponding field definitions in the target database. Errors are recorded and classified. Then, execution plan simulation is performed: the query plan is obtained through the database's EXPLAIN command, and performance indicators such as estimated execution cost, JOIN operation algorithm and number of rows scanned, and index utilization are extracted from it. Finally, quantitative scoring and screening are performed: based on the static analysis results, a compliance score reflecting syntactic correctness and a logical integrity score reflecting semantic accuracy are calculated; based on the execution plan indicators, a performance prediction score is calculated through normalization and weighting. Finally, the model performs weighted sorting based on these scores to select the optimal candidate SQL.

[0040] 3. Implementation of the dynamic feedback optimization module This module aims to perform closed-loop verification and optimization of the optimal candidate SQL output by the multi-stage generation module, forming an iterative cycle of "execution-feedback-correction" to improve the accuracy and reliability of the final result. The specific steps are as follows: (1) Sandbox Execution and Feedback Feature Extraction: The optimal candidate SQL selected through rigorous screening is executed in a database isolated from the production environment within a sandbox. The database schema in this sandbox is completely consistent with the target database, but may contain anonymized test data or subsets of data to ensure execution security. The system captures execution feedback in real time, including error codes returned by the database (such as MySQL errors 1146 and 1054), exception messages, number of rows in the result set, execution time, etc., and classifies and extracts them into structured error feature vectors.

[0041] (2) Error-driven iterative correction: The system uses a rule engine and a reinforcement learning (RL) model to collaboratively correct SQL based on the nature of the feedback error.

[0042] Rules engine: Built-in expert knowledge base to handle common errors that can be patterned. For example, if the error is "column does not exist" and the column name is similar to a column name in a related table, a correction strategy of "supplementing the table name prefix" will be automatically generated.

[0043] When the reported error is a common, pattern-based error (such as "column does not exist" or "table does not exist"), the rules engine is invoked for processing. This engine has a built-in expert knowledge base that can automatically generate correction strategies based on error patterns. For example, if the error is "column does not exist" and the column name is similar to a column name in a related table, a correction action of "adding a prefix to the table name" will be automatically generated.

[0044] When the reported error is a complex or unpredictable logical error (such as poor performance due to poor join order or omission of null value handling logic), it is classified and extracted into a structured error feature vector, which is then input into a reinforcement learning (RL) model for processing. This model uses the error feature vector as the state and outputs corrective actions. The action space is defined as adjustments to the SQL structure, such as adjusting the JOIN order, modifying operators in the WHERE condition, or adding IS NOT NULL conditions.

[0045] The corrected SQL is re-entered into the sandbox for execution, and the process is repeated until it executes successfully and produces the correct result.

[0046] (3) Hybrid supervised learning and continuous evolution: The system continuously optimizes itself by utilizing the data generated during the execution process.

[0047] Reinforcement learning training: The SQL execution result serves as the comprehensive reward, driving model optimization. Specifically, the execution success rate (R_success) is a Boolean value (1 for success, 0 for failure); the result accuracy (R_accuracy) is calculated by comparing the similarity between the execution result and the expected result (e.g., the Jaccard similarity coefficient); and the execution efficiency (R_efficiency) is the reciprocal of the execution time, normalized. The comprehensive reward function can be designed as: Reward = w1 * R_success + w2 * R_accuracy + w3 * R_efficiency, where w1, w2, and w3 are preset weights. The system updates the policy network of the reinforcement learning correction model based on this reward signal using the Proximal Policy Optimization (PPO) algorithm.

[0048] Contrastive learning: Automatically constructs training sample pairs containing the same natural language query, a correct SQL (the final successful version), and an incorrect SQL (the version that fails during iteration). By contrastive learning, the semantic distance between the correct SQL and the query is reduced, while the distance between the incorrect SQL and the query is reduced. Its loss function and RL reward are co-optimized to improve model stability and discriminative power.

[0049] 4. System Operation Example Taking a multi-table related query for financial risk control as an example, a user can input: "Find high-risk customer information with transaction amounts exceeding 50,000 yuan in the last 3 months, excluding frozen accounts."

[0050] The pattern awareness enhancement module parses the risk control database schema, extracts the table structure and foreign key constraints of CUSTOMERS, TRANSACTIONS, RISK_LEVELS, etc., and constructs the schema context vector.

[0051] The multi-stage generation module first generates multiple candidate SQL statements (such as different versions of explicit JOIN or subqueries), and after evaluation by a lightweight validation model, selects the candidate with the optimal execution plan.

[0052] The dynamic feedback optimization module encountered a "return empty set" exception during sandbox execution. Error analysis revealed that the case where risk_score was NULL was not handled. The RL model generated a corrective action: "append condition AND risk_score IS NOTNULL". After the correction, the SQL execution succeeded.

[0053] The successful SQL statement and the correction process were recorded as training samples. The model was updated through comparative learning and reinforcement learning, enabling the system to gradually learn to handle the semantic trap of "null values".

[0054] Through the above implementation methods, the present invention achieves closed-loop optimization from semantic understanding and pattern adaptation to execution feedback, significantly improving the accuracy and practicality of NL2SQL tasks.

[0055] This embodiment uses multi-table join queries in a financial risk control scenario as an application example. The user requirement is: "Find the names, mobile phone numbers, and all transaction records of customers whose transaction amounts exceed 50,000 yuan in the last three months and are marked as high-risk, while excluding frozen accounts." The system uses the SQL generation accuracy improvement method based on a large language model described in this invention to automatically generate and optimize accurate and executable SQL query statements. The implementation flowchart is shown in the patent document. Figure 1 .

[0056] (1) Pattern perception and enhancement stage The system initiates the pattern awareness enhancement module, connects to the risk control database, and parses its schema metadata. It extracts the structure, field types, and primary / foreign key constraints (e.g., TRANSACTIONS.cust_id is a foreign key related to CUSTOMERS.cust_id) of the key tables (CUSTOMERS, TRANSACTIONS, RISK_LEVELS, ACCOUNT_STATUS). Simultaneously, it integrates business rules (e.g., risk_score > 80 defines "high risk," and status_code = 'FROZEN' indicates a frozen account). Subsequently, it uses a graph neural network (GNN) to encode this pattern information into a dense pattern context vector, which is then fused with the user's natural language query to form an enhanced input representation.

[0057] --Example of extracting key metadata-- Table: CUSTOMERS Columns: cust_id (PK), name, phone, account_status_id (FK→ACCOUNT_STATUS.id) Table: TRANSACTIONS Columns: trans_id (PK), cust_id (FK→CUSTOMERS.cust_id), amount, trans_time Table: RISK_LEVELS Columns: risk_id (PK), cust_id (FK→CUSTOMERS.cust_id), risk_score,update_time Table: ACCOUNT_STATUS Columns: id (PK), status_code (values: 'ACTIVE','FROZEN','CLOSED') constraint: A score of RISK_LEVELS.risk_score > 80 is defined as "high risk". - ACCOUNT_STATUS.status_code = 'FROZEN' indicates frozen. To illustrate this clearly, a structured textual description (example) of constructing a pattern context vector is provided below: schema_context = """ [Table CUSTOMERS] Customer Information: cust_id (primary key), name (name), phone (phone number), account status id (foreign key → ACCOUNT_STATUS.id) [Table TRANSACTIONS] Transaction records: trans_id (primary key), cust_id (foreign key → CUSTOMERS.cust_id), amount (amount), trans_time (time) [Table RISK_LEVELS] Risk Level: risk_id (primary key), cust_id (foreign key → CUSTOMERS.cust_id), risk_score (score), update_time (update time) [Rule] Risk score > 80 indicates high risk; ACCOUNT STATUS.status code='FROZEN' indicates freeze. """ Subsequently, the system encodes the structured description into a 768-dimensional vector, concatenates it with the user query, and inputs it into the Large Language Model (LLM).

[0058] (2) Multi-stage generation and screening stage Coarse generation: The Large Language Model (LLM) generates multiple candidate SQL statements based on the context of pattern enhancement. For example: Candidate SQL1: SELECT c.name, c.phone, t.* FROM CUSTOMERS c JOIN TRANSACTIONS t ON c.cust_id = t.cust_id JOIN RISK_LEVELS r ON c.cust_id = r.cust_id WHERE t.trans_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH) AND t.amount > 50000 AND r.risk_score > 80 AND c.account_status_id NOT IN ( SELECT id FROM ACCOUNT_STATUS WHERE status_code = 'FROZEN' ).

[0059] Candidate SQL2: SELECT c.name, c.phone, t.* FROM CUSTOMERS c, TRANSACTIONS t, RISK_LEVELS r WHERE c.cust_id = t.cust_id AND c.cust_id = r.cust_id AND t.amount > 50000 AND r.risk_score > 80 AND t.trans_time BETWEEN NOW()-INTERVAL 3 MONTH AND NOW() AND EXISTS ( SELECT 1 FROM ACCOUNT_STATUS a WHERE a.id = c.account_status_id AND a.status_code != 'FROZEN' ).

[0060] Fine-tuning: A lightweight validation model performs static analysis and execution plan simulation evaluation on candidate SQL statements. It was found that candidate SQL1 did not filter update times with risk levels, potentially leading to the use of expired data; candidate SQL2 used an older join syntax that produces a Cartesian product, posing a performance risk. The validation model scores the candidate SQL statements (SQL1 scores 70 points, SQL2 scores 65 points) and automatically adds the optimization condition AND r.update_time >= DATE_SUB(NOW(),INTERVAL 3 MONTH) to SQL1 with the higher score, outputting it as the optimal candidate to the next stage.

[0061] (3) Dynamic feedback and iterative correction stage The system executes the optimal candidate SQL after careful screening in the database sandbox, and the execution feedback result is "empty result set returned". The dynamic feedback optimization module catches this exception and initiates error analysis: Analysis revealed that the trans_time field contained invalid data for future times, and the risk_score field may contain NULL values, causing the conditional filtering to malfunction.

[0062] The rules engine works in conjunction with the reinforcement learning (RL) correction model to generate correction policies: adding conditions to exclude future timestamps and handle null values.

[0063] The corrected SQL is: ... WHERE ... AND t.trans_time BETWEEN DATE_SUB(NOW(), INTERVAL 3 MONTH) AND NOW() -- Excludes future time AND r.risk_score IS NOT NULL -- Exclude risk-free score records. AND r.risk_score > 80 ... The revised SQL was re-executed in the sandbox and successfully returned 32 high-risk transaction records that met the requirements, with an 87% improvement in execution performance (number of rows scanned).

[0064] (4) Hybrid learning and evolution stage The system uses this successful case as a training sample pair for hybrid supervised learning: Reinforcement Learning (RL): The policy network of the RL-corrected model is updated with reward signals of successful execution and performance improvement (reward +1.5).

[0065] Contrastive learning: Construct sample pairs (user query, corrected SQL, and erroneous SQL without handling NULL values), and train the model by contrastive loss so that it learns to identify and avoid semantic traps caused by "null values".

[0066] Contrast learning sample construction: Positive sample: Corrected SQL Negative sample: Error SQL that initially omitted NULL handling train_data.append( anchor_query = "Find high-risk customer transactions in the last 3 months...", positive = "SELECT ... AND risk_score IS NOT NULL ...", negative = "SELECT ... AND risk_score > 80 ..." # Unprocessed NULL ) → The model learns to recognize semantic traps caused by "null values ​​in fields". Final result: The system of this invention can automatically generate and optimize a complex multi-table join query SQL that would take 20 minutes to write manually in 8 seconds. The generated result has passed the audit verification of risk control business and accurately reflects the user's intent.

[0067] As can be seen from the above embodiments, the method of the present invention can effectively solve the problem of natural language to SQL conversion in complex business scenarios through pattern awareness, multi-stage generation, dynamic feedback loop and continuous learning, and significantly improve the accuracy and efficiency of generation.

[0068] Although specific embodiments of the invention have been disclosed for illustrative purposes to aid in understanding and implementing the invention, those skilled in the art will understand that various substitutions, variations, and modifications are possible without departing from the spirit and scope of the invention and the appended claims. Therefore, the invention should not be limited to the content disclosed in the preferred embodiments, and the scope of protection claimed by the invention is defined by the claims.

Claims

1. A method for generating SQL query statements based on a large language model, comprising the following steps: 1) The pattern-aware enhancement module extracts metadata from the target database, identifies the relationships between tables in the target database based on the metadata, and stores them in a pattern knowledge base. Then, a pre-trained language model is used to encode the metadata into low-dimensional vectors. Next, the pattern schema of the target database is constructed into a graph structure, where nodes are tables and fields in the target database, and edges are the relationships between primary and foreign keys in the target database. The graph neural network is used to perform embedding learning on the graph structure to generate a pattern context vector that integrates the semantics of the database structure. The pattern context vector is fused with the natural language query input by the user through an attention mechanism to form a pattern-enhanced context representation. 2) The multi-stage generation module generates SQL query statements based on the enhanced context representation of the pattern, including a coarse generation stage and a fine screening stage; 21) In the coarse generation stage, the large language model is invoked to generate multiple candidate SQL statements based on the pattern-enhanced context representation, which are used to cover multiple implementation paths of the user's query intent; 22) In the fine screening stage, a lightweight verification model is used to evaluate the candidate SQL statements: First, static analysis is performed on the candidate SQL statements, and the syntax correctness of the candidate SQL is checked using an integrated syntax parser. The existence of field / table names in the target database schema and whether the data types of the values ​​in the query conditions match the corresponding field definitions in the target database are also verified. Then, the candidate SQL statements after static analysis are simulated and executed to estimate the execution cost and efficiency. Finally, the optimal candidate SQL is selected as the final SQL query statement based on the evaluation results of each candidate SQL.

2. The method according to claim 1, characterized in that, The multi-stage generation module is optimized through a dynamic feedback optimization module, and the method is as follows: 1) Execute the best candidate SQL in a database isolated from the production environment in a sandbox, and capture the execution feedback in real time; the database schema in the sandbox is consistent with the target database schema. 2) Correcting the optimal candidate SQL based on feedback: When the feedback error is a common error that can be patterned, the rule engine is invoked to correct the optimal candidate SQL based on the built-in expert knowledge base according to the common error that can be patterned; when the feedback error is a common error that cannot be patterned, it is classified and extracted into a structured error feature vector and input into the reinforcement learning model; the reinforcement learning model takes the error feature vector as the state and outputs the correction action, the action space is defined as the adjustment operation of the SQL structure, and then the corrected optimal candidate SQL is re-input into the database in the sandbox for execution, and the process is repeated until success is achieved; 3) Using SQL execution success rate, result accuracy, and execution efficiency as a comprehensive reward, the policy network of the reinforcement learning model is updated through a near-end policy optimization algorithm.

3. The method according to claim 2, characterized in that, The feedback includes the error code returned by the database, the exception message, the number of rows in the result set, and the execution time.

4. The method according to claim 1, 2, or 3, characterized in that, The evaluation results include grammatical compliance score, logical integrity score, and performance prediction score.

5. The method according to claim 1, 2, or 3, characterized in that, The metadata includes table name, field name, data type, primary and foreign key constraints, and index information.

6. The method according to claim 1, 2, or 3, characterized in that, The abstract syntax tree-based parsing technique is used to structure the extracted metadata and identify the relationships between tables.

7. The method according to claim 1, 2, or 3, characterized in that, The large language model generates multiple candidate SQL statements through bundle search based on the pattern-enhanced contextual representation.

8. The method according to claim 1, characterized in that, The large language model is either GPT-4 or Llama 3.

9. A SQL query statement generation system based on a large language model, characterized in that, Includes a pattern awareness enhancement module and a multi-stage generation module; The pattern-aware enhancement module is used to extract metadata from the target database, identify the relationships between tables in the target database based on the metadata, and store them in a pattern knowledge base; then, a pre-trained language model is used to encode the metadata into a low-dimensional vector; then, the pattern schema of the target database is constructed into a graph structure, where nodes are tables and fields in the target database, and edges are the relationships between primary and foreign keys in the target database; and a graph neural network is used to embed the graph structure to generate a pattern context vector that integrates the semantics of the database structure. The pattern context vector is fused with the natural language query input by the user through an attention mechanism to form a pattern-enhanced context representation; The multi-stage generation module is used to generate SQL query statements based on the pattern-enhanced context representation, including a coarse generation stage and a fine selection stage; 21) In the coarse generation stage, a large language model is called to generate multiple candidate SQL statements based on the pattern-enhanced context representation, which are used to cover multiple implementation paths of the user's query intent; 22) In the fine selection stage, a lightweight verification model is called to evaluate the candidate SQL statements: first, static analysis processing is performed on the candidate SQL statements, the syntactic correctness of the candidate SQL is checked using an integrated syntax parser, and the existence of field / table names in the target database schema and whether the data type of the values ​​in the query conditions matches the corresponding field definition in the target database are verified; then, the candidate SQL statements after static analysis processing are simulated and the execution cost and efficiency are estimated; then, the optimal candidate SQL is selected as the final SQL query statement based on the evaluation results of each candidate SQL.

10. The system according to claim 9, characterized in that, It also includes a dynamic feedback optimization module for optimizing the multi-stage generation module. The method is as follows: 1) Execute the optimal candidate SQL in a database isolated from the production environment in a sandbox, and capture the execution feedback in real time; the database schema in the sandbox is consistent with the target database schema; 2) Correct the optimal candidate SQL based on the feedback: when the feedback error is a common error that can be patterned, the rule engine is called to correct the optimal candidate SQL based on the built-in expert knowledge base according to the common error that can be patterned; when the feedback error is a common error that cannot be patterned, it is classified and extracted into a structured error feature vector and input into the reinforcement learning model; the reinforcement learning model takes the error feature vector as the state and outputs the correction action, the action space is defined as the adjustment operation of the SQL structure, and then the corrected optimal candidate SQL is re-input into the database in the sandbox for execution, and the loop is iterated until success; 3) Use the SQL execution success rate, result accuracy and execution efficiency as the comprehensive reward, and update the policy network of the reinforcement learning model through the near-end policy optimization algorithm.

Images