A SQL generation method and system based on dynamic schema anchoring and business rule injection

Abstract

The application discloses a kind of SQL generation method and system based on dynamic Schema anchoring and business rule injection, belong to natural language processing and database query technical field.The method includes: receiving the natural language question input by user, determine the business domain to which the natural language question belongs;According to the business domain, the structure information of database table is dynamically acquired, and structured Schema context is generated;Read the rule set corresponding to the business domain from the business rule library, the rule set includes at least one of calculation formula rule, mandatory fuzzy matching field list and specific value constraint rule.The application can dynamically adapt to database table structure change, and complex business caliber is forced to act on SQL generation process in a pluggable manner, which significantly improves the accuracy and end-to-end success rate of vertical field Text-to-SQL.

Description

Technical Field

[0001] This invention belongs to the field of natural language processing and database query technology. Specifically, it relates to a method and system for automatically converting natural language problems into executable SQL statements that conform to complex business criteria. It can be applied to data analysis scenarios in vertical fields such as financial leasing and financial risk control, which have multiple table relationships, dynamic table structures, and strong business rule constraints. Background Technology

[0002] As enterprises accelerate the process of data assetization, business personnel in positions such as operations, risk control, and finance frequently need to extract various report metrics from data warehouses. Most business personnel lack SQL writing skills, and data retrieval usually requires submission to the technical team, resulting in high communication costs and long response times. In recent years, Text-to-SQL technology based on Large Language Models (LLMs) has developed rapidly. Users can input natural language questions, and the model can directly generate SQL, alleviating the data retrieval bottleneck to some extent.

[0003] However, when it comes to actually running Text-to-SQL in a production environment, existing technologies still have several shortcomings. First, there's the dependency on static table structures. Most solutions embed the table structure when constructing prompts; once fields or tables are added to the database, or relationships are adjusted, the model is completely unaware, resulting in SQL that is either syntactically outdated or logically flawed. Second, business rules are easily overlooked. Scenarios like finance and leasing involve numerous complex calculations. For example, the "total number of approved reviews" needs to be summed from both the pre-review and review tables. The model often relies on field names to guess semantics, frequently generating SQL that doesn't conform to business definitions. Third, there's a lack of rigid constraint validation. Without a dedicated module for rule-level SQL validation, even if the syntax is correct, empty results might be found because fuzzy matching was used instead of equality matching where it should be, or a fixed filtering condition might be missed, leading to statistical inaccuracies. Fourth, there's a lack of automatic correction capabilities. When SQL is generated incorrectly, the system often only returns error messages, forcing users to repeatedly modify and retry, resulting in very low interaction efficiency. Fifth, entity alias handling is inadequate. Users often use abbreviations when asking questions, and since there are no completely identical records in the database, the query result is naturally empty. Summary of the Invention

[0004] The technical problem this invention aims to solve is to address the shortcomings of existing Text-to-SQL technologies, such as difficulty in adapting to dynamic table structures, difficulty in enforcing complex business rules, and lack of rigid verification and automatic repair capabilities. This invention provides a SQL generation method and system based on dynamic schema anchoring and business rule injection, which ensures that the generated SQL remains correct and stable in production environments with frequent changes in table structures and complex business definitions.

[0005] To solve the above-mentioned technical problems, the present invention adopts the following technical solution.

[0006] In a first aspect, the present invention provides a SQL generation method based on dynamic schema anchoring and business rule injection, comprising the following steps:

[0007] Step S1, Business Domain Identification: Receive the natural language question input by the user, and determine the business domain to which the question belongs through keyword matching or a lightweight classification model.

[0008] Step S2, Dynamic Schema Anchoring: Based on the pre-configured table list of the business domain, connect to the database in real time to obtain the field names, field types and field comments of each table, and automatically infer the relationship between tables based on the principle of complete matching of column names and compatible data types, and format the above information into a structured schema context.

[0009] Step S3, Business Rule Injection: Read the rule set corresponding to the business domain from the business rule library. The rule set includes at least one of the following: calculation formula rule, forced fuzzy matching field list, and specific value constraint rule; merge the rule set with the Schema context obtained in step S2 to construct structured prompt words.

[0010] Step S4, Candidate SQL Generation: Input the structured prompt words into the large language model, obtain the model output text, and extract candidate SQL from the output text.

[0011] Step S5, rule verification: Use a finite state machine to perform rule verification on the candidate SQL. The verification content includes: whether the forced fuzzy matching field uses fuzzy matching operators, whether the fields required by the calculation formula are referenced, and whether specific constraints exist.

[0012] Step S6, Automatic Repair: If the verification fails and the number of retries has not reached the preset limit, the violation type is analyzed, the corresponding repair instruction is selected from the repair template library and appended to the original prompt word, the large language model is called again to generate new candidate SQL and verify it again until the verification passes or the retry limit is reached.

[0013] Step S7, Feedback Learning: Vectorize the finally verified natural language questions, correct SQL and business domains and store them in the vector database; when a new query is received, retrieve the k most similar successful cases as minority sample examples and integrate them into the prompt words.

[0014] Secondly, the present invention provides an SQL generation system based on dynamic schema anchoring and business rule injection, including a business domain identification module, a dynamic schema anchoring module, a business rule base module, a prompt word construction module, a large language model calling module, an SQL extractor, a rule verification module, an automatic repair module, and a feedback learning module.

[0015] Compared with the prior art, the present invention has the following beneficial effects:

[0016] It obtains the latest schema in real time, eliminating the need for manual synchronization after table structure changes, and the decrease in SQL generation accuracy is significantly lower than that of traditional methods.

[0017] By using a closed loop of "rule injection + verification + repair", business terms such as calculation formulas, fuzzy matching, and fixed constraints are forcibly applied to the SQL generation process in a pluggable manner, which significantly improves the accuracy of calculation of common indicators.

[0018] For fields such as dealer names that are prone to abbreviation, fuzzy matching is enforced to effectively avoid empty results caused by equality matching, and the query recall rate is significantly improved.

[0019] The initial pass rate and the overall pass rate after automatic repair both reached a high level, reducing manual intervention and improving interaction efficiency.

[0020] Adding a new business domain only requires configuring the rule base and table list; no modification to the core code is required. Attached Figure Description

[0021] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the accompanying drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the structures shown in these drawings without creative effort.

[0022] Figure 1 This is a diagram showing the overall module architecture of the system of the present invention;

[0023] Figure 2 This is an overall flowchart of the method for achieving the result of this invention;

[0024] Figure 3 This is a flowchart of the dynamic schema anchoring algorithm of the present invention;

[0025] Figure 4 This is the state transition diagram of the rule-based finite state machine (FSM) for the present invention;

[0026] Figure 5 This is an internal flowchart of the automatic repair module of the present invention;

[0027] Figure 6 This is a schematic diagram of the Feedback Learning and Retrieval Enhancement Generation (RAG) of the present invention. Detailed Implementation

[0028] The present invention will be further described in detail below with reference to the accompanying drawings and specific embodiments. The following embodiments are used to illustrate the present invention, but are not intended to limit the scope of the present invention.

[0029] Example 1: Overall Method Flow

[0030] This embodiment provides a complete implementation process for a SQL generation method based on dynamic schema anchoring and business rule injection, which can be combined with... Figure 2 understand.

[0031] Step S1, Business Domain Identification. The system receives a natural language question input from the user, such as "What is the pre-approval pass rate after review for Henan Gucheng in 2025?". The system pre-maintains a business domain dictionary, where each business domain corresponds to several keywords. For example, the pre-approval / review domain (denoted as ys_fy) corresponds to the keywords "pre-approval" and "review," the migration rate domain corresponds to "migration rate" and "rollover rate," the operational statistics domain corresponds to "operation" and "daily active users," and the order mileage domain corresponds to "order" and "mileage." The system segments the natural language question and matches it with the keywords of each business domain. The business domain with the highest matching degree is the business domain to which the question belongs. If keyword matching cannot distinguish, a lightweight classification model (such as a classifier based on TF-IDF and logistic regression) is called for determination. The output of business domain identification is the business domain identifier.

[0032] Step S2, Dynamic Schema Anchoring. Obtain a pre-configured list of tables based on the business domain identifier. Execute a table structure description statement (such as MySQL's DESCRIBE statement or querying the information_schema view) on each table in the list to obtain field names, field types, and field comments. Then, infer the relationships between tables: for any two tables, iterate through their fields; if there are two fields with exactly matching column names (ignoring case) and compatible data types, use these two fields as candidate join keys to form a table relationship. Format the field information of each table and the table relationships into a structured schema context, such as using JSON format, including tables and relations fields. The implementation process of step S2 can be combined with... Figure 3 understand.

[0033] Step S3, Business Rule Injection. Read the rule set corresponding to this business domain from the business rule library. The rule set includes three types of rules:

[0034] (1) Calculation formula rules, used to define the calculation scope of indicators. For example, two formulas in the pre-review and review domain:

[0035] Formula 1: Total number of approvals after reconsideration = Sum of approvals in the preliminary review forms + Sum of approvals in the reconsideration forms;

[0036] Formula 2: Preliminary review pass rate after reconsideration = (sum of the number of preliminaries approved in the preliminary review form + total number of preliminaries approved after reconsideration) ÷ sum of the number of preliminary review forms × 100%.

[0037] The above formula can be formally expressed as:

[0038]

[0039]

[0040] (2) Forced fuzzy matching field list, used to limit fields that are likely to appear in abbreviations or aliases. For example, fields DealerName and company_name must use LIKE fuzzy matching in the WHERE condition, and equal value matching is prohibited.

[0041] (3) Specific value constraint rules are used to limit the fixed filtering conditions that must be added when generating SQL. For example, when querying loan_amount, the condition MOB=0 must be added.

[0042] Step S3 merges the rule set with the schema context generated in step S2 to construct structured prompt words. The prompt words sequentially include system role settings, a list of business formulas, a list of fields for forced fuzzy matching, a list of specific value constraints, real-time table structure information, and user questions, and explicitly require the model to output only SQL.

[0043] Step S4: Candidate SQL Generation. The structured prompts are sent to a large language model (such as DeepSeek or GPT series models) to obtain the model's output text. Since the model output may contain explanatory text or Markdown code block markers, the system extracts pure SQL statements from the output text using regular expressions as candidate SQL statements.

[0044] Step S5, Rule Validation. A finite state machine is used to validate the candidate SQL statements. The state transition relationships of the finite state machine can be combined with... Figure 4 Understood. The validator checks in the following order:

[0045] First, check the field for forced fuzzy matching. If there is an equality match for the forced fuzzy matching field in the candidate SQL, then enter the equality match violation state.

[0046] Next, check specific constraints. If a candidate SQL is missing a condition corresponding to a specific value constraint rule, it will enter the state of missing specific constraints.

[0047] Finally, check the fields required for the calculation formula. If the candidate SQL does not reference all the fields involved in a certain formula, then it enters the state of missing fields required for the formula.

[0048] If the candidate SQL passes all the above checks, it enters a no-violation state and is deemed to have passed the verification.

[0049] Step S6, Automatic Repair. If the verification in step S5 fails, the automatic repair process is initiated. This process can be combined with... Figure 5 The system analyzes the violation type and matches the corresponding repair instruction from the repair template library. The repair template library has three pre-defined repair instructions: for equality matching violations, the instruction requires changing the equality matching of a specified field to a LIKE fuzzy match; for missing formula fields, the instruction lists the fields required by the formula and requires them to be completed in the SELECT or aggregate; for missing constraints, the instruction requires adding the constraint to the WHERE clause. The repair instruction is appended to the end of the original structured prompt, and the large language model is called again to generate new candidate SQL and validate it again. The number of retries is incremented by one in each round of repair, with a preset maximum of two; when the maximum number of retries is reached and validation still fails, an error message is returned.

[0050] Step S7, Feedback Learning. The final validated natural language questions, corresponding SQL statements, and business domains are vectorized and stored in a vector database. When a new natural language question is received, the similarity between the question and each record in the vector database is calculated. The k most similar successful cases (k = 3 in this invention) are retrieved and incorporated as few-shot examples into the prompt words to assist the model in generating more accurate SQL. The overall approach of feedback learning and retrieval-enhanced generation can be combined. Figure 6 understand.

[0051] To theoretically characterize the core mechanism of this invention, a mathematical model is established below for the rule verification and optimization selection process in the method.

[0052] Let the natural language problem input by the user be... The business domain is The current schema of the database is Business domain in the business rule base The corresponding rule set is Rule set Includes a set of calculation formula rules Forced fuzzy matching field set and specific value constraint set .

[0053] Rule compliance loss function Used to measure candidate SQL For the set of rules The degree of conformity is defined as:

[0054]

[0055] The specific meaning of "satisfying the rules" is: for each calculation formula , The SELECT clause or aggregate expression contains All fields involved; for each field in the forced fuzzy matching process. , In the WHERE clause, all references to this field use the LIKE operator; for each specific constraint... , The constraint is included in the WHERE clause.

[0056] Large Language Model The process of generating SQL can be viewed as a conditional probability distribution, denoted as... Given a problem, schema, and rules, output the probability of candidate SQL statements. Semantic loss is defined as the negative log-likelihood.

[0057]

[0058] Considering both rule compliance and semantic matching requirements, this invention selects the optimal SQL. satisfy:

[0059]

[0060] in As the balance coefficient, this invention takes To reflect the priority of rules. Because The value can be 0 or positive infinity. The above optimization is equivalent to: selecting the SQL with the minimum semantic loss from the set of candidate SQLs that satisfy all the rules; if there is no candidate SQL that satisfies the rules, the problem is unsolvable and the system returns an error message.

[0061] In the feedback learning process, cosine similarity is used to retrieve similar cases. Successful cases are included. The embedding vector is denoted as New problems The embedding vector is denoted as Then the similarity is defined as:

[0062]

[0063] Selecting those with similarity exceeding a threshold The cases constitute a small sample set (Threshold in this invention) Taking 0.85), the conditional probability of the enhanced retrieval model can be approximated as:

[0064]

[0065] Example 2: Inquiry into the pre-approval and review pass rate of financial leasing

[0066] This embodiment uses the query of "preliminary review pass rate" in the context of financial leasing as an example to illustrate the complete implementation process of the present invention.

[0067] The environment configuration is as follows: the database is MySQL 8.0, which includes the pre-review table t_renheche_ys and the review table t_ys_fy; the business rule base is configured according to the pre-review and review domains; the large language model uses DeepSeek-V2; the vector storage uses Chroma; and the embedding model uses BGE-large-zh.

[0068] The user entered a natural language question: "What is the pre-approval rate for Henan Gucheng's reconsideration in 2025?"

[0069] The implementation steps are as follows:

[0070] (1) Business domain identification. The question is segmented into words, and the keywords "preliminary review" and "reconsideration" are matched, and the business domain is determined to be the preliminary review and reconsideration domain ys_fy.

[0071] (2) Dynamic Schema Anchoring. Execute the DESCRIBE statement on table t_renheche_ys to obtain the fields: DealerName (VARCHAR), product_line (VARCHAR), pre_orders (INT), pre_pass_orders (INT), review_pass_orders (INT), create_time (DATETIME); execute the DESCRIBE statement on table t_ys_fy to obtain the fields: DealerName (VARCHAR), review_pass_orders (INT), product_line (VARCHAR). Through column name exact match and type compatibility checks, it is inferred that the two tables can be joined by LEFT JOIN using the DealerName and product_line fields. (3) Business Rule Injection. Read the rules of the pre-review / reconsideration domain from the rule base: the calculation formula rules are Formula 1 and Formula 2; the forced fuzzy matching field is DealerName; the specific value constraint is the time filter condition YEAR(create_time)=2025 (this constraint is filled by the year parameter in the user's question). Integrate the above rules with the schema context to construct prompt words.

[0072] (4) Candidate SQL generation. The large language model generates SQL based on prompt words, and the extracted candidate SQL is as follows:

[0073] SELECT(SUM(ys.pre_pass_orders)+SUM(ys.review_pass_orders)+SUM(fy.review_pass_orders)) / SUM(ys.pre_orders)*100 AS post_review_rate FROM t_renheche_ys ys LEFT JOIN t_ys_fy fy ON ys.DealerName=fy.DealerName ANDys.ProductLine=fy.ProductLine WHERE ys.DealerName LIKE'%Henan Gucheng%'AND YEAR(ys.create_time)=2025

[0074] (5) Rule verification. Finite state machine check passed: DealerName uses LIKE fuzzy matching, all fields involved in the formula are referenced, time constraints exist, and the system enters a no-violation state.

[0075] (6) Output and feedback. Execute SQL and return the query result; vectorize the question, the correct SQL and the business domain and store them in the vector database.

[0076] If the candidate SQL generated in step (4) uses exact matching, for example, WHERE ys.DealerName='Henan Gucheng', the finite state machine enters an exact matching violation state; the automatic repair module appends the instruction "Please use LIKE fuzzy matching for the DealerName field, for example, DealerName LIKE '%Henan Gucheng%'" to the original prompt; re-invoke the large language model to generate a new candidate SQL, and output it after passing the verification again.

[0077] Third Embodiment: System Structure and Module Interaction

[0078] This embodiment describes the system composition of the present invention, which can be combined with Figure 1 for understanding. The system includes:

[0079] A business domain identification module, configured to receive a natural language question input by a user and output a business domain identifier;

[0080] A dynamic Schema anchoring module, configured to acquire a table list according to the business domain identifier, acquire table structure information in real time, infer associations between tables, and generate a structured Schema context;

[0081] A business rule base module, configured to store rule sets corresponding to each business domain, and support adding, modifying and querying rules;

[0082] A prompt construction module, configured to integrate the Schema context and the rule set to construct a structured prompt;

[0083] A large language model invocation module, configured to invoke the large language model to generate candidate SQL, and support repeated invocation in the repair phase;

[0084] An SQL extractor, configured to extract a pure SQL statement from the output text of the model;

[0085] A rule verification module, configured to perform rule verification on the candidate SQL by using a finite state machine, and output a verification result and a violation type;

[0086] An automatic repair module, configured to select a repair instruction according to the violation type, append the repair instruction to the prompt and re-invoke the large language model;

[0087] A feedback learning module, configured to vectorize successful cases and store them in the database, and retrieve similar cases as few-shot examples when a new query arrives.

[0088] The data flow between modules is as follows: the business domain identification module outputs the business domain identifier to the dynamic schema anchoring module and the business rule base module; the dynamic schema anchoring module outputs the schema context, and the business rule base module outputs the rule set, both of which are then merged into the prompt word construction module; the prompt word construction module outputs structured prompt words to the large language model calling module; the large language model calling module outputs the raw output to the SQL extractor; the SQL extractor outputs candidate SQL to the rule validation module; the rule validation module outputs the successful SQL when validation passes, and sends the candidate SQL and violation type to the automatic repair module when validation fails; the automatic repair module generates a repaired prompt word and sends it back to the large language model calling module; the feedback learning module receives successful cases from the rule validation module and provides a few sample examples to the prompt word construction module.

Claims

1. A SQL generation method based on dynamic schema anchoring and business rule injection, characterized in that, Includes the following steps: Receive a natural language question input by the user and determine the business domain to which the natural language question belongs; Dynamically obtain the structure information of the database tables based on the business domain, and generate a structured schema context; Read the rule set corresponding to the business domain from the business rule base. The rule set includes at least one of the following: calculation formula rules, forced fuzzy matching field list, and specific value constraint rules. The schema context is fused with the rule set to construct structured prompt words, and the structured prompt words are input into the large language model to generate candidate SQL. The candidate SQL is validated according to rules to determine whether it conforms to all rules in the rule set. If the verification passes, the candidate SQL will be output. If the verification fails, an automatic repair process is initiated. After appending the repair instruction corresponding to the violation type to the structured prompt, the large language model is called again to generate new candidate SQL and verify it again, until the verification passes or the number of retries reaches the preset limit.

2. The SQL generation method based on dynamic schema anchoring and business rule injection according to claim 1, characterized in that, The structure information of the database tables is dynamically obtained based on the business domain, including: Obtain the list of pre-configured tables for the business domain; For each table in the table list, execute the table structure description statement or query the information schema view to obtain the field names, field types, and field comments; Based on the principle of exact column name matching and data type compatibility, the relationships between tables are inferred; The field information and the associated relationships are formatted into the structured schema context.

3. The SQL generation method based on dynamic schema anchoring and business rule injection according to claim 1, characterized in that, The list of fields for mandatory fuzzy matching is used to restrict the use of fuzzy matching operators for specified fields in the WHERE condition, and prohibits the use of equality matching operators; the specific value constraint rule is used to restrict the fixed filtering conditions that must be added to the WHERE condition when generating SQL.

4. The SQL generation method based on dynamic schema anchoring and business rule injection according to claim 1, characterized in that, The candidate SQL is validated according to rules, including: Extract candidate SQL from the output of the large language model; A finite state machine is used to perform rule validation on the candidate SQL. The states of the finite state machine include no violation, equal value matching violation, missing fields required by the formula, missing specific constraints, and fatal error. Based on the syntax structure of the candidate SQL and the state transition driven by the rule set, the verification is deemed successful when the candidate SQL enters a non-violation state.

5. The SQL generation method based on dynamic schema anchoring and business rule injection according to claim 1, characterized in that, Initiate the automatic repair process, including: Analyze the violation type and match the corresponding repair instruction from the repair template library; Append the repair instruction to the end of the structured prompt; Recall the large language model to generate new candidate SQL; Increment the number of retries by one, and determine whether the number of retries has reached the preset upper limit.

6. The SQL generation method based on dynamic schema anchoring and business rule injection according to claim 5, characterized in that, The preset limit is two attempts; when the number of retries reaches the preset limit and the verification still fails, an error message is returned.

7. The SQL generation method based on dynamic schema anchoring and business rule injection according to claim 1, characterized in that, It also includes a feedback learning step: vectorizing the validated natural language questions, corresponding SQL and business domains and storing them in a vector database; when a new natural language question is received, retrieving the k most similar successful cases from the vector database and incorporating the successful cases as minority sample examples into the structured prompt words.

8. A SQL generation system based on dynamic schema anchoring and business rule injection, characterized in that, include: The business domain identification module is used to receive natural language questions input by the user and determine the business domain to which the natural language questions belong; The dynamic schema anchoring module is used to dynamically obtain the structure information of the database table based on the business domain and generate a structured schema context. The business rule base module is used to store the rule set corresponding to each business domain. The rule set includes at least one of the following: calculation formula rules, forced fuzzy matching field list, and specific value constraint rules. The prompt word construction module is used to merge the Schema context with the rule set to construct structured prompt words; The large language model invocation module is used to input the structured prompt words into the large language model and generate candidate SQL; The SQL extraction and rule validation module is used to extract candidate SQL from the output of the large language model and perform rule validation on the candidate SQL. An automatic repair module is used to append repair instructions to the structured prompt words when the verification fails, and to re-invoke the large language model; The feedback learning module is used to collect user feedback on the generated SQL results and update the rule set in the business rule base and the configuration of the dynamic schema anchoring based on the feedback information.

9. The SQL generation system based on dynamic schema anchoring and business rule injection according to claim 8, characterized in that, The dynamic schema anchoring module also includes an inter-table relationship inference unit, which generates inter-table relationships by comparing the column names and data type compatibility of different tables.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the SQL generation method based on dynamic schema anchoring and business rule injection as described in any one of claims 1 to 7.

Images