Method and system for improving SQL generation accuracy of LLM large model based on CoT concept

Abstract

The invention relates to the technical field of natural languages, in particular to a method and system for improving SQL generation accuracy of an LLM large model based on the CoT concept. The method comprises the following steps: firstly, preprocessing database metadata, and extracting a table structure and field information; the method comprises the steps that firstly, a complex natural language query task is divided into a plurality of sub-problems with definite semantics on the basis of the Chain-of-Though concept, SQL sub-query statements capable of being independently executed are sequentially generated in a multi-pass SQL mode, and input of each statement is based on an output result or original data summary of the previous statement. A verification mechanism is introduced in the generation process, the grammar and logic validity of the SQL statement is checked, and if a grammar error or no problems of fields and table names are found, the system automatically recognizes and prompts the error, so that the executable performance of the SQL is guaranteed. According to the method, the accuracy and the stability of the large model in the relational database query task are effectively improved, the illusion phenomenon is reduced, and the practicability and the reliability of an intelligent agent system are enhanced.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of natural language processing, in particular to a method and system for improving the accuracy of SQL generation of a large language model (LLM) based on the CoT concept. BACKGROUND

[0002] With the rapid development of artificial intelligence technology, large language models (LLM) have shown significant performance in natural language understanding, information extraction, and text generation. In database query scenarios, LLMs are widely used to automatically translate user input natural language questions into structured query language (SQL) to retrieve target data from relational databases. However, existing large language models still face significant challenges in the SQL generation task. On the one hand, when facing complex query requirements such as multi-table joint, nested subquery, grouping aggregation, window function, etc., the generated SQL statements often have syntax errors, field reference errors, incomplete logical structures, etc., resulting in the generated results cannot be executed by the database or return incorrect data. Especially in enterprise reporting, business monitoring and other scenarios, the accuracy requirement is very high, and the traditional single-pass generation method is difficult to meet the actual use requirements.

[0003] On the other hand, since large language models are essentially based on statistical learning of large-scale corpus, their generation process is prone to "hallucination" phenomenon. Hallucination refers to the output of the model that seems reasonable but actually does not exist or is incorrect. For example, the model may refer to a table name, field name that does not exist in the database, or construct an incorrect function expression, resulting in an unexecutable SQL statement. The hallucination problem is particularly serious in database access scenarios, because the structure and semantics of the database have strict constraints, and any false content will cause the query to fail or the result to be incorrect.

[0004] Therefore, there is an urgent need for a new SQL generation method that can guide the large language model to analyze and structure complex natural language questions step by step, generate multiple rounds of intermediate query steps that can be verified in combination with database structure information, thereby improving the accuracy of SQL generation, reducing the hallucination rate, and improving the actual usability and reliability of large models in structured data access tasks. SUMMARY

[0005] The present disclosure provides a method and system for improving the accuracy of SQL generation of a large language model (LLM) based on the CoT concept.

[0006] In a first aspect, the present disclosure provides a method for improving the accuracy of SQL generation of a large language model (LLM) based on the CoT concept, comprising: The preprocessed database metadata obtains data summary information, and the data summary information is stored in a structured manner; The input natural language is parsed, and the natural language is split into multiple sub-questions in combination with the data summary information; SQL sub-query statements are sequentially generated for the multiple sub-questions; All the SQL sub-query statements are verified; If passed, all the SQL sub-query statements are executed.

[0007] Optionally, after the verification of all the SQL sub-query statements, the method further comprises: If not passed, the input natural language is re-parsed, and the natural language is re-split into multiple sub-questions in combination with the data summary information; New SQL sub-query statements are sequentially generated for the multiple sub-questions; All the new SQL sub-query statements are re-verified until passed.

[0008] Optionally, the obtaining of the data summary information from the preprocessed database metadata and the structured storage of the data summary information further comprise: Table name and field structure information of each data table in the database are extracted to form metadata entries; The metadata entries are stored in a temporary data structure in a manner that the table name is the key and the field structure information is the value; The temporary data structure is saved as structured data summary information.

[0009] Optionally, the parsing of the input natural language and the splitting of the natural language into multiple sub-questions in combination with the data summary information further comprise: The input natural language query statement is subjected to semantic analysis to identify the multi-stage data processing intention contained therein; In combination with the table name and field structure information recorded in the data summary information, it is determined that the input of each stage data processing intention is obtainable; The input of each stage data processing intention is obtainable, including data output from the previous stage data processing intention and / or data obtained from the data summary information.

[0010] Optionally, the natural language is a guided instruction text constructed through a preset prompt word template; the prompt word template is used to guide a large model to parse a task in a chain thinking manner.

[0011] Optionally, the sequentially generating of SQL sub-query statements for the multiple sub-questions further comprises: The plurality of sub-questions are mapped into a plurality of SQL sub-query statements in a multi-pass SQL generation mode according to semantics and data processing sequences, each SQL sub-query statement corresponds to a semantic target of a sub-question and can independently express the query logic thereof. The plurality of SQL sub-query statements have a front-rear dependency relationship, and input data of a rear sub-query statement is derived from output results of a front sub-query statement and / or pre-stored data in the data digest information.

[0012] Optionally, the verifying all the SQL sub-query statements further includes: judging whether the syntax of the SQL sub-query statement is correct; judging whether a table name and a field in the SQL sub-query statement exist in the data digest information or output of a front sub-query statement; If not, the verification fails.

[0013] In a second aspect, the present disclosure provides a system for improving SQL generation accuracy of an LLM large model based on a CoT concept, including: a data preprocessing module configured to preprocess database metadata to obtain data digest information and to store the data digest information in a structured manner; a natural language parsing module configured to parse input natural language and split the natural language into a plurality of sub-questions in combination with the data digest information; an SQL statement generation module configured to generate SQL sub-query statements for the plurality of sub-questions in sequence; a verification module configured to verify all the SQL sub-query statements; an execution module configured to execute all the SQL sub-query statements if the verification passes.

[0014] In a third aspect, the present disclosure provides an electronic device including a processor and a memory in communication connection with the processor; the memory stores computer execution instructions; the processor executes the computer execution instructions stored in the memory to implement the method in the present disclosure.

[0015] In a fourth aspect, the present disclosure provides a computer readable storage medium, the computer readable storage medium stores computer execution instructions, and the computer execution instructions are executed by the processor to implement the method in the present disclosure.

[0016] The present disclosure has the following advantages compared with the prior art: 1) The present application is based on the Chain-of-Thought theory to guide the large model to gradually disassemble the user query intention, thereby avoiding the semantic compression and logical jump problems existing in the traditional one-time SQL generation mode. By explicitly disassembling complex problems into a series of intermediate sub-problems and constructing corresponding SQL sub-queries, the model can more clearly understand the field meaning, table association logic and data aggregation path, and finally realize high-quality SQL generation, significantly improving the semantic accuracy.

[0017] 2) The present application adopts multi-pass SQL syntax structure, which enables the large model to generate executable sub-query statements according to the Chain-of-Thought steps. This structure naturally has the context progression feature, which can explicitly externalize each step of calculation, avoiding the illusion risk brought by one-time construction of complex nested statements (such as generating table names, fields, logical relationships, etc. out of thin air). The input source of each sub-SQL is clear and the structure is closed, significantly reducing error field calls and structural chaos, effectively reducing the illusion probability.

[0018] 3) The present application introduces database structure meta-information as prompt input and establishes syntax and logic checking mechanism to provide stable and accurate generation basis for the large model. After each round of SQL sub-query generation, syntax checking and field existence verification are automatically triggered to ensure that the statement is legal and can be correctly parsed and executed by the database. At the same time, if syntax conflicts or field missing are found, the automatic error correction mechanism can refer to the prompt information in the previous text to repair in time, greatly improving the executability of SQL and the overall stability of the system. BRIEF DESCRIPTION OF DRAWINGS

[0019] The accompanying drawings, which are incorporated in and constitute a part of the specification, illustrate embodiments consistent with the present disclosure and, together with the description, serve to explain the principles of the present disclosure.

[0020] Figure 1 A method for improving the accuracy of LLM large model SQL generation based on the CoT concept provided by the embodiments of the present disclosure; Figure 2 A pre-processing database metadata method provided by the embodiments of the present disclosure; Figure 3 A natural language split sub-problem method provided by the embodiments of the present disclosure; Figure 4 A SQL sub-query statement verification method provided by the embodiments of the present disclosure; Figure 5 A system for improving the accuracy of LLM large model SQL generation based on the CoT concept provided by the embodiments of the present disclosure.

[0021] The specific embodiments of the present disclosure have been shown by the above figures, and will be described in more detail hereinafter. These figures and textual descriptions are not intended to limit the scope of the concept of the present disclosure in any way, but to illustrate the concept of the present disclosure to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0022] The present disclosure will be further described below in conjunction with the accompanying drawings. The following embodiments are only used to more clearly illustrate the technical solutions of the present disclosure, and cannot limit the protection scope of the present disclosure.

[0023] Figure 1 A method for improving the SQL generation accuracy of an LLM large model based on the CoT concept is provided for the embodiments of the present disclosure. Referring to Figure 1 , each step will be discussed in detail in combination with the present embodiment.

[0024] S100, preprocessing database metadata to obtain data summary information, and structuring storage of the data summary information.

[0025] In the present embodiment, the system first performs initialization access to the target relational database to extract the basic structure information of the database, i.e., the metadata. This process includes accessing the metadata interface of the database (such as information_schema or system catalog), reading all available table information, field information, and the primary-foreign key association relationship between fields.

[0026] Specifically, the system will traverse all tables in the database, and for each table, record its table name, table annotation, the names of the contained fields, field types, field annotations, whether it is a primary key or a foreign key, and other structured attributes. At the same time, for the fields with foreign key constraints, the associated target table and target field information will also be extracted to establish the inter-table reference graph.

[0027] Subsequently, the system cleanses and organizes the information extracted above to generate unified data summary information. The data summary information includes but is not limited to: table list, field list, field type mapping, primary-foreign key information, table annotation information, field annotation information, and other contents. All information will be organized in a structured form, such as using the dictionary data structure of Python, JSON, dictionary, or hash table, etc. programmable data structure to support subsequent fast query and context call.

[0028] Meanwhile, to improve the efficiency of the whole chain reasoning, the system will carry out preliminary semantic preprocessing on the natural language query sentence input by the user. The preprocessing includes basic operations such as lexical segmentation, keyword recognition, and syntactic structure analysis, mainly used to identify the query target, the limiting condition, the expected output field and the implied data processing logic involved in the user input. The output of this stage is not the final sub-problem decomposition, but a prior reference for the subsequent sub-problem generation.

[0029] For example, a simple supply chain database table structure is designed as follows: Supplier category table CREATE TABLE supplier_categories ( category_id INT PRIMARY KEY AUTO_INCREMENT, category_name VARCHAR(100) NOT NULL, description TEXT ); Supplier table CREATE TABLE suppliers ( supplier_id INT PRIMARY KEY AUTO_INCREMENT,supplier_name VARCHAR(200) NOT NULL, category_id INT, contact_info TEXT,status ENUM('active','inactive') DEFAULT 'active', FOREIGN KEY (category_id)REFERENCES supplier_categories(category_id) ); Product category table CREATE TABLE product_categories ( category_id INT PRIMARY KEY AUTO_INCREMENT, category_name VARCHAR(100) NOT NULL, parent_category_id INT NULL,FOREIGN KEY (parent_category_id) REFERENCES product_categories(category_id)); Product table CREATE TABLE products ( product_id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(200) NOT NULL, category_id INT, unit_of_measure VARCHAR(50), FOREIGN KEY (category_id) REFERENCES product_categories(category_id) ); Order table CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, order_date DATE NOT NULL, customer_info TEXT, status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending' ); Order details table CREATE TABLE order_items ( item_id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, product_id INT NOT NULL, supplier_id INT NOT NULL, quantity INT NOT NULL, unit_price DECIMAL(10, 2) NOT NULL, total_price DECIMAL(12, 2) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (product_id) REFERENCES products(product_id), FOREIGN KEY(supplier_id) REFERENCES suppliers(supplier_id) ).

[0030] S200, parsing the input natural language, and combining the data abstract information to split the natural language into multiple sub-questions.

[0031] In this embodiment, after obtaining the user's natural language query, the system enters the second stage - parsing the input natural language and combining the data digest information to split it into multiple sub-questions. The goal of this stage is to divide complex, general or multi-level query requests into several data processing sub-tasks with clear semantic goals and independent execution based on the Chain-of-Thought concept, thereby establishing a logical framework for subsequent SQL sub-query generation.

[0032] First, the system receives a segment of natural language input from the user, such as: "Please calculate the sales of each product by each supplier per month, and calculate the proportion of the supplier in the category and the proportion in the total monthly sales." Such queries often contain multiple data processing intentions, such as table association, data aggregation, grouping statistics, and proportion calculation, etc. If a single SQL is generated directly, it is easy to cause logical confusion or illusion phenomenon. Therefore, the system needs to perform layer-by-layer deconstruction on the natural language through a semantic parsing mechanism.

[0033] The semantic parsing mechanism includes steps such as lexical analysis, syntax tree construction, and intent recognition. The system identifies multiple key operation semantics in the natural language, such as "statistical sales", "monthly aggregation", "category summary", "proportion calculation", etc., and generates corresponding sub-question structures according to these semantic nodes. Each sub-question should have a clear input source, a clear output target, and an independent processing logic.

[0034] During the generation of sub-questions, the system will combine the data digest information obtained in the first step to determine whether the fields and tables involved in the user's intention exist. For example, the system confirms that the fields "supplier", "product", "sales" exist in the data tables of suppliers, products, and orders, and determines the feasible JOIN path based on the foreign key relationship between fields. If a semantic operation involves multiple fields, the system will locate the related tables through the field-to-table reverse index mechanism to assist in establishing the data source path required by the sub-question.

[0035] Finally, the system will split the natural language query into an ordered series of sub-questions, each sub-question structure should include: ① the semantic goal of this sub-question (such as "construct a product-supplier-month basis sales table"); ② the input data source (such as the association result of the orders table and the products table); ③ the expected output field (such as supplier_id, product_id, order_month, total_sales, etc.); ④ the logical dependency relationship between this sub-question and the previous sub-question.

[0036] Through this process, the system completes the conversion from natural language to structured sub-problem sequence, laying a solid foundation for subsequent sub-query generation and integration using multi-pass SQL technology, ensuring that the query task has logical chain reasoning continuity and controllability.

[0037] In an embodiment, the natural language is a guiding instruction text constructed by a preset prompt word template; the prompt word template is used to guide the large model to analyze the task in a chain-of-thought manner.

[0038] In an embodiment, the natural language input is not the original question text freely expressed by the user, but a guiding instruction generated based on a preset prompt word template. The prompt word template is designed by the system in advance and has a clear semantic structure and step-by-step guidance content, aiming to prompt the large language model to understand and decompose the user's task layer by layer according to the chain-of-thought reasoning path. Specifically, the prompt word template first guides the model to identify the overall query goal at the question presentation stage, and then gradually prompts it to break down each logical sub-step, clearly indicating the data source required for each step and the expected output. In this way, the understanding depth of the large model when facing complex queries and the accuracy of SQL generation are significantly improved. Specifically, the large model can be guided by the prompt instruction to generate a multi-pass SQL syntax that perfectly matches the chain-of-thought concept to start generating SQL step by step.

[0039] A simplified prompt word template example is as follows: You are an outstanding data analyst, proficient in MySQL syntax. Yourgoal is to answer users' questions using multi-pass SQL. After receiving a user's question, you first analyze the question, formulate a problem-solving plan, break the question down step by step into sub-questions that can be easily answered, and finally answer the original question based on the results of the sub-questions. The database table schema you have is as follows: {table_schema} The user's question is: {user_question} The Chain-of-Thought concept advocates breaking down complex tasks into a series of progressively reasoning intermediate steps, thereby improving the model's accuracy and logical consistency in complex tasks. Multi-pass SQL syntax naturally aligns with this concept, transforming complex queries into multiple dependent subqueries, each of which can be executed independently and express a specific semantic goal. This step-by-step construction and verification approach to SQL allows large language models to aggregate the final answer layer by layer while maintaining semantic clarity. The prompt word template built upon this concept no longer relies on a one-time "black-box" process of generating complex SQL, but instead guides the model through a structured, multi-round reasoning and generation process, possessing a clear logical path and controllable errors. Therefore, this prompt word template not only demonstrates a high degree of compatibility between Chain-of-Thought and the hierarchical construction of SQL syntax, but also possesses significant practical engineering application value and methodological innovation.

[0040] S300. For the multiple sub-problems, generate SQL sub-query statements in sequence.

[0041] In this embodiment, the system sequentially generates corresponding SQL subquery statements based on the multiple structured sub-problems parsed in step S200. This process employs a multi-pass SQL generation method to ensure that each SQL statement matches the semantic target of its corresponding sub-problem one-to-one and possesses syntactic and logical independence and executability. Simultaneously, different subqueries are connected through intermediate results, enabling the layered construction and step-by-step reasoning of complex query tasks, consistent with the Chain-of-Thought principle.

[0042] First, the system generates SQL queries starting with the first sub-problem in the sequence of sub-problems. Taking "Constructing a basic sales table for products, suppliers, and months" as an example, the system identifies the input tables for this sub-problem as orders, order_items, products, and suppliers, and the query fields as supplier_id, product_id, order_month, quantity, and sales. Based on this, the system generates the following SQL structure: CREATE TEMPORARY TABLE base_sales_data ASSELECT o.order_id, DATE_TRUNC('month', o.order_date) AS order_month, -- Aggregate by month p.product_id, p.product_name, p.product_category, s.supplier_id, s.supplier_name, s.supplier_category_id, s.supplier_category, SUM(oi.quantity) AS total_quantity, -- Total sales of this product by the supplier in the current month. SUM(oi.quantity * oi.unit_price) AS total_sales -- Total sales of this product by this supplier in the current month. FROM orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id JOIN suppliers s ON p.supplier_id = s.supplier_id --Associate supplier information GROUP BY order_month, p.product_id, s.supplier_id, --group by finest granularity This SQL statement completes the data construction for the first sub-problem, storing the results in a temporary table `base_sales_data` to provide input for the next sub-problem. Next, the system generates a second subquery to handle the sub-problem of "aggregating sales by supplier category and month, and calculating the total sales for the entire month." At this point, the system recognizes that the input for this sub-problem comes from the `base_sales_data` temporary table, and the goal is to group and summarize sales by `supplier_category_id` and `order_month`. An example of the generated SQL is as follows: CREATE TEMPORARY TABLE category_summaries AS SELECT order_month, supplier_category_id, supplier_category, SUM(total_sales) AS supplier_category_monthly_sales, -- Total monthly sales by category SUM(total_sales) OVER (PARTITION BY order_month) AS monthly_total_sales -- Total monthly sales FROM base_sales_data GROUP BY order_month, supplier_category_id, supplier_category; Finally, for the sub-problem of "combining the results of the first two steps to calculate the sales percentage," the system generates a third SQL subquery to integrate the multiple source tables and calculate the ratio. This SQL uses a JOIN operation to join the first two temporary tables and generates the final result: SELECT --Basic Dimensions: Suppliers, Products, Months b.supplier_id, b.supplier_name, b.supplier_category, b.product_id, b.product_name, b.order_month, -- Base metrics: quantity sold, sales amount b.total_quantity, b.total_sales, -- Aggregated metrics: category monthly sales, overall monthly sales c.supplier_category_monthly_sales, c.monthly_total_sales, -- Percentage metrics: supplier's share in category, supplier's share in overall (b.total_sales / c.supplier_category_monthly_sales) * 100 AS supplier_category_percentage, (b.total_sales / c.monthly_total_sales) *100 AS overall_percentage FROM base_sales_data b JOIN category_summaries c ON b.supplier_category_id = c.supplier_category_id AND b.order_month = c.order_month ORDER BY b.order_month DESC, b.supplier_category, b.total_sales DESC; Throughout the S300 step, the generation process of all subqueries is based on the Chain-of-Thought thought chain, with logic progressing layer by layer and semantics gradually unfolding. Each subquery can be run independently and produce clear data results, providing a clear data basis for subsequent subqueries. This multi-pass SQL generation method not only improves the stability and accuracy of complex query statements, but also significantly reduces the risk of semantic errors and hallucinations generated by large models.

[0043] S400, verifying all the SQL subquery statements.

[0044] In this embodiment, the step S400 is used to verify all the SQL subquery statements generated in step S300 one by one, to ensure their correctness in terms of syntax structure and logical reference. This verification process is not only used to exclude errors at the syntax level, but also to ensure the correctness of the contextual dependency of the query logic, to avoid semantic illusion and structural mismatch problems, thereby laying a reliable foundation for subsequent SQL execution.

[0045] Firstly, the system performs syntax checking on each SQL subquery statement. This process can call a local SQL parser or a database driver interface to quickly parse the structure of the SQL statement, to determine whether there are errors such as mismatched parentheses, misspelled keywords, incorrect statement structure, and the like. If the syntax check fails, the system immediately marks the SQL as a failed state and no longer enters the subsequent verification process.

[0046] Secondly, the system performs logical reference verification, mainly including the following two aspects: First, verify whether all the table names referenced in the SQL statement exist in the data table structure set recorded in the data summary information, or whether they are temporary tables generated by the previous subquery statement. For example, if the table category_summaries is referenced in the current SQL, the system needs to confirm that this table has been successfully defined in the previous subquery through CREATE TEMPORARY TABLE. If the referenced table name is not found in the data summary information and the historical temporary table record, it is determined that the SQL logic is not valid.

[0047] Second, verify whether the fields referenced in the SQL statement exist in the corresponding table structure. Specifically, if the total_sales field is referenced in a SQL statement, the system will compare the base_sales_data table structure to confirm whether it contains this field. It should be noted that for the case of referencing a temporary table in the previous step, the field check should be based on the definition structure of the temporary table, rather than limited to the original database table. If the referenced field is not registered in the target table structure, the system also determines that the SQL logic cannot be executed.

[0048] On the premise that the above two types of verification are passed, the system marks the SQL subquery statement as passed and enters the execution preparation state.

[0049] S500, if passed, execute all the SQL subquery statements.

[0050] In this embodiment, step S500 is used to sequentially execute all SQL subquery statements after they have passed the verification in step S400, ultimately generating the complete query results. This step is the implementation link of the entire Chain-of-Thought concept and multi-pass SQL method, ensuring that the SQL statements generated at each level in the preceding stages are correctly parsed and form the target data output.

[0051] First, the system sorts the SQL subqueries generated in step S300 according to their execution order. Because this invention uses a structured generation method for multi-pass SQL, each SQL statement typically depends on the output of the previous statement (such as an intermediate temporary table). Therefore, its execution must strictly follow the semantic decomposition order of the subproblems. Before execution, the system constructs an execution graph and analyzes the dependencies between each SQL subquery to ensure there is no risk of reverse execution due to references to undefined tables or fields.

[0052] Secondly, the system executes SQL statements one by one using a preset database connection engine (such as PostgreSQL, MySQL, ClickHouse, etc.). For each statement, the system monitors its execution status, records execution time and resource consumption, and captures any potential runtime exceptions. For statements containing `CREATE TEMPORARY TABLE`, the system registers the generated temporary table into the execution context after execution for subsequent SQL statements to use. All SQL execution is completed within the same session to ensure the validity of the temporary table and the consistency of the data context.

[0053] Finally, after all SQL statements are successfully executed, the system extracts the execution result of the final target SQL as the response output to the user's natural language query. This result can be a complete summary table, a combination of multiple dimension indicators, or processed report data. The system can further export the result to formats such as CSV and JSON, or directly push it to downstream visualization platforms (such as BI systems).

[0054] In one embodiment, after verifying all the SQL subquery statements, the method further includes: if the verification fails, re-parse the input natural language and, in conjunction with the data feed information, re-divide the natural language into multiple sub-problems; generate new SQL subquery statements sequentially for the multiple sub-problems; and re-verify all the new SQL subquery statements until the verification passes.

[0055] When all SQL subquery statements fail validation in step S400, the system will automatically initiate a correction and reconstruction process. This process aims to effectively repair the query statements and achieve logical closure by re-parsed natural language input and adjusting the SQL generation strategy.

[0056] First, the system attributes errors to the SQL subquery statements that fail the verification. Error types generally include: syntax structure errors, table names or field names inconsistent with data feed information, references to intermediate results that have not yet been generated, etc. Based on these error types, the system automatically generates feedback information and triggers a re-parsing process for the original natural language.

[0057] Second, the system re-analyzes the natural language input semantically and, in combination with the structured stored data feed information, conducts a new round of Chain-of-Thought-style disassembly of the problem to generate a new set of sub-questions. This process not only considers the original problem semantics but also integrates the structural information that failed in the previous verification, thereby achieving semantic optimization and context enhancement. For example, if the atomic problem omits a data aggregation field, a semantic hint of "aggregation by certain dimension" is introduced in the re-disassembly.

[0058] Subsequently, the system uses a multi-pass SQL generation mechanism to construct new SQL subquery statements for the new set of sub-questions. Each SQL statement independently achieves the semantic goal of the current sub-question and maintains the input-output dependency relationship between statements to form a logically transferable SQL statement chain.

[0059] Finally, the system performs the verification step in S400 again on the newly generated SQL subquery statements. If the verification still fails, the system will continue to iteratively execute the above process until all SQL subquery statements pass the verification. This process can be automatically completed within one session or manually intervened and corrected after prompting user interaction feedback.

[0060] Through the above-mentioned automated error identification, semantic re-parsing, and SQL reconstruction mechanisms, the system significantly improves the fault tolerance and recovery ability of large models in the face of complex natural language query tasks. This "failure- attribution-reconstruction-verification" iterative mode fully embodies the role of the Chain-of-Thought concept in the closed loop and is highly compatible with the multi-pass SQL syntax structure, providing a solid guarantee for the final generation of executable and semantically accurate SQL statements.

[0061] Figure 2 A pre-processing database metadata method provided by an embodiment of the present disclosure is shown in a schematic diagram. The specific embodiments of the present application are further described below. Figure 2

[0062] S110, extract the table name and field structure information of each data table in the database to form a metadata entry.

[0063] ​In the implementation step S110, the system first performs a structured analysis on the target database, extracts the basic structure information of each data table in the database, and organizes it into standardized metadata entries. The core of this process is to identify the table name of each data table and its contained field structure information, providing structural support for subsequent natural language parsing and SQL statement generation.

[0064] Specifically, the system traverses all accessible data tables in the current database by accessing the metadata system of the database (such as information_schema in MySQL). For each data table, the system extracts its field name, field data type, primary key identification, whether it allows null, and possible foreign key identification, etc. These information constitutes the complete field structure description of the table.

[0065] In actual operation, the system organizes the structure information of each table into metadata entries in a unified format. For example, for a data table named orders, its fields include order_id (integer, primary key), order_date (date type), customer_id (integer, possibly a foreign key), the system will attach these fields in list form under the table name, forming a metadata entry containing the table name and field structure.

[0066] S120, store the metadata entries in a temporary data structure with table name as key and field structure information as value.

[0067] In the implementation step S120, the system organizes the metadata entries extracted in the previous stage, and stores them in a temporary data structure using the mapping method of "table name as key and field structure information as value". The data structure usually chooses a data mapping form with high retrieval ability, such as hash table or key-value dictionary structure. In actual storage process, each table name is used as a unique identifier to locate the field set of the table, while the corresponding field structure information is saved in the form of list or nested item, including field name, field type, whether it is a primary key, whether it allows null, whether it is a foreign key and its association, etc.

[0068] S130, save the temporary data structure as structured data summary information.

[0069] In the implementation step S130, the system further persists the temporary data structure constructed in step S120 to form structured data summary information. Specifically, this process converts the hash table or dictionary form of table-field mapping structure into a unified data summary format, and stores it in the specified cache module, in-memory database or local file system, ensuring that it can be called at any time in the subsequent SQL subquery generation and verification phase.

[0070] The saved data summary information not only includes the name and field structure of each table, but also additional meta-information such as foreign key association between tables, field annotations, field type descriptions, etc., to enhance the context awareness ability of the large model in understanding natural language semantics and generating multi-step SQL. The structured information is usually represented in Python, JSON, YAML or a proprietary intermediate format, with good extensibility and parsing efficiency.

[0071] Figure 3 A natural language splitting sub-problem method provided by an embodiment of the present disclosure is shown in a schematic diagram. The specific embodiments of the present application will be further described with reference to the accompanying drawings. Figure 3

[0072] In S210, the input natural language query sentence is subjected to semantic analysis to identify the multi-stage data processing intention contained therein.

[0073] In the implementation of step S210, the system first receives the natural language query sentence input by the user, and performs semantic analysis processing on the sentence. This processing process aims to identify the multi-stage data processing target implied in the sentence, thereby laying the foundation for subsequent sub-problem splitting and SQL sub-query generation.

[0074] Semantic analysis generally includes three levels of morphological analysis, syntactic parsing and semantic modeling. The system uses a large language model or a dedicated natural language understanding module to comprehensively understand the keywords, syntax structure and context semantics in the query sentence. For example, for the user input "Please calculate the sales of each supplier category per month and calculate the proportion of the total sales", the system will identify the multi-stage data processing intention implied therein: the first stage is "aggregating sales by supplier category", the second stage is "summarizing by month", and the third stage is "calculating the proportion relationship".

[0075] After identifying the intentions of each stage, the system labels them as logical sub-task nodes and assigns each node a unique identifier and a preliminary semantic label, such as "category sales summary", "time dimension aggregation", "proportion calculation", etc. These labels serve as input for subsequent step S220, helping the large model to generate matching SQL sub-queries in combination with data summary information.

[0076] Through this step, the system effectively captures the task hierarchical structure in the natural language query, laying a semantic foundation for implementing Chain-of-Thought based step-by-step reasoning, thereby enhancing the accuracy and controllability of multi-step query understanding.

[0077] ​S220, determine that the input of each stage data processing intention is available according to the table name and field structure information recorded in the data digest information. The input of each stage data processing intention is available, including data output from the data processing intention of the previous stage and / or data obtained from the data digest information.

[0078] In step S220, the system further combines the data digest information generated in the preprocessing stage to confirm the availability of the input data of each stage on the basis of completing natural language semantic analysis and identifying multiple data processing stage intentions.

[0079] Specifically, the system will traverse each input information required by each sub-problem or data processing stage one by one, and match and verify against the data table names and field structures recorded in the data digest information. The data digest information is a structured metadata set that records the names, field lists, field types, primary and foreign key relationships, and other information of all tables in the database. The system determines whether the fields referred to in the current sub-problem exist in the database according to this information, and confirms whether these fields have the ability to be directly queried or derived from the previous stage results.

[0080] For example, if a sub-problem needs to refer to fields "supplier_category" and "order_month", the system will find the tables to which these two fields belong in the data digest information, and confirm whether these tables can support aggregation operations by "month" and "category". At the same time, if the required fields of a stage are intermediate fields generated by the output of the previous stage SQL sub-query, the system will also track the output structure of the previous sub-query to confirm that the fields are continuously available in the process.

[0081] The execution of this step ensures that the input data of each stage in the task chain is available in terms of semantics and physical structure, thereby ensuring that the generation of subsequent SQL sub-queries will not have the problem of missing data sources or semantic breaks. This verification process further enhances the logical rigor and engineering executability of the combination of multi-pass SQL and Chain-of-Thought thinking methods.

[0082] Further, the multi-pass SQL generation method maps the multiple sub-questions to multiple SQL sub-query statements one by one according to the semantic and data processing order, and each SQL sub-query statement corresponds to the semantic goal of a sub-question and can independently express its query logic. The multiple SQL sub-query statements have a front-back dependency relationship, and the input data of the subsequent sub-query statement comes from the output result of the previous sub-query statement and the pre-stored data in the data digest information.

[0083] Figure 4 A SQL subquery statement verification method provided by an embodiment of the present disclosure is shown in the figure. The specific embodiments of the present disclosure are further described below. Figure 4 The specific embodiments of the present disclosure are further described below.

[0084] S410, determining whether the syntax of the SQL subquery statement is correct.

[0085] In step S410, the system first checks the syntax of each SQL subquery statement generated by the large model. This step aims to ensure that the generated SQL statement meets the standard syntax specifications supported by the target database, such as the syntax requirements of MySQL, PostgreSQL, or other relational databases.

[0086] Specifically, the system uses an embedded or integrated SQL parser to parse each statement, identifies key syntax units such as SELECT, FROM, WHERE, JOIN, GROUP BY, etc., and detects whether the syntax format meets the standard writing method, including but not limited to whether the parentheses are matched in pairs, whether the keyword spelling is correct, whether the field alias is standardized, etc.

[0087] S420, determining whether the table name and field in the SQL subquery statement exist in the data digest information or the output of the previous subquery statement.

[0088] In step S420, the system performs logical verification on the table name and field name referenced by each SQL subquery statement to determine whether it exists in the database metadata recorded in the data digest information or can be obtained from the output result of the previous subquery statement.

[0089] First, the system parses all table names appearing in the current subquery statement and compares them with the data digest information formed in step S100. If a table name does not exist in the table structure set of the data digest information, and the table name is not derived from the result of the previous subquery statement (such as an alias named as a temporary table or intermediate result), it is determined that the table is not identifiable, and the subquery statement is not verified.

[0090] Subsequently, the system further verifies each field name referenced in the subquery statement. For fields directly read from a database table, the system checks whether they exist in the data digest field structure of the corresponding table; for fields referencing the previous subquery statement, the system checks whether the field exists in the SELECT projection of the previous statement. If the field name is not found in both, it is considered "unidentifiable field", and the statement is not verified.

[0091] S430, if not, the verification fails.

[0092] In step S430, the system determines whether the SQL subquery statement passes the verification according to the judgment results of the aforementioned syntax verification (S410) and logical structure verification (S420).

[0093] If a syntax error is found in step S410, such as missing keywords, unmatched parentheses, illegal SQL structure splicing, or in step S420, the table name or field name cannot be identified in the data summary information or the previous subquery output, it is determined that the current subquery statement verification fails. At this time, the statement will not be submitted for execution, but will be entered into the subsequent error feedback or reconstruction process as a correction object.

[0094] On the contrary, if the judgments of S410 and S420 are both correct, the verification passes.

[0095] Figure 5 A system schematic diagram for improving the accuracy of LLM large model SQL generation based on the CoT concept is provided for the embodiments of the present disclosure. Now, combined with Figure 5 , the specific embodiments of the present application are further described.

[0096] The data preprocessing module is used for preprocessing database metadata to obtain data summary information, and structuring and storing the data summary information.

[0097] In this embodiment, the system first performs initialization access to the target relational database to extract the basic structure information of the database, i.e., the metadata. This process includes accessing the metadata interface of the database (such as information_schema or system catalog), reading all available table information, field information, and the primary-foreign key association relationship between fields.

[0098] Specifically, the system will traverse all tables in the database, and for each table, record its table name, table annotation, field name, field type, field annotation, whether it is a primary key or a foreign key, and other structured attributes. At the same time, for the fields with foreign key constraints, the associated target table and target field information will also be extracted to establish the inter-table reference relationship graph.

[0099] Subsequently, the system cleans and organizes the information extracted above to generate unified data summary information. The data summary information includes but is not limited to: table list, field list, field type mapping, primary-foreign key information, table annotation information, field annotation information, and other contents. All information will be organized in a structured form, such as using Python dictionary data structure, JSON, dictionary, or hash table, etc. programmable data structure to support subsequent fast query and context call.

[0100] At the same time, in order to improve the overall chain reasoning efficiency, the system will carry out preliminary semantic preprocessing on the natural language query sentence input by the user. The preprocessing includes basic operations such as lexical segmentation, keyword recognition, and syntactic structure analysis, which are mainly used to identify the query target, the limiting condition, the expected output field and the implied data processing logic involved in the user input. The output of this stage is not the final sub-problem decomposition, but a priori reference for subsequent sub-problem generation.

[0101] The natural language analysis module is used to analyze the input natural language and split the natural language into multiple sub-questions in combination with the data abstract information.

[0102] In this embodiment, after obtaining the natural language query of the user, the system enters the second stage of analyzing the input natural language and splitting it into multiple sub-questions in combination with the data abstract information. The goal of this stage is to decompose complex, general or multi-level query requests into several data processing sub-tasks with clear semantic targets and independent execution based on the Chain-of-Thought concept, thereby establishing a logical framework for subsequent SQL sub-query generation.

[0103] Firstly, the system receives a natural language question input by the user, for example: “Please count the sales of each product by each supplier per month, and calculate the proportion of the supplier in the category and the proportion in the monthly sales.” Such a query often contains multiple data processing intentions, such as table association, data aggregation, grouping statistics, and proportion calculation, etc. If a single SQL is directly generated, it is easy to cause logical confusion or illusion. Therefore, the system needs to perform layer-by-layer deconstruction on the natural language through a semantic analysis mechanism.

[0104] The semantic analysis mechanism includes steps such as lexical analysis, syntax tree construction, and intent recognition. The system identifies multiple key operation semantics in the natural language, such as “count sales”, “aggregate by month”, “aggregate by category”, “calculate proportion”, etc., and generates corresponding sub-question structures according to these semantic nodes. Each sub-question should have a clear input source, an explicit output target, and an independent processing logic.

[0105] During the sub-question generation process, the system will combine the data abstract information obtained in the first step to determine whether the fields and tables involved in the user's intention exist. For example, the system confirms that the fields “supplier”, “product”, “sales” etc. exist in the data tables of suppliers, products and orders, and determines the feasible JOIN path by combining the foreign key relationships between the fields. If a semantic operation involves multiple fields, the system will locate the related tables through the field-to-table reverse index mechanism to assist in establishing the data source path required by the sub-question.

[0106] Finally, the system disassembles the natural language query into an ordered sequence of sub-questions, each of which should include: ① the semantic goal of this sub-question (e.g., "build a product-supplier-month-based sales table"); ② the input data source (e.g., the join result of the orders table and the products table); ③ the expected output fields (e.g., supplier_id, product_id, order_month, total_sales, etc.); and ④ the logical dependency relationship between this sub-question and the previous one.

[0107] Through this process, the system completes the conversion from natural language to a structured sequence of sub-questions, laying a solid foundation for subsequent sub-query generation and integration using multi-pass SQL techniques, ensuring that the query task has logical chain-like reasoning continuity and controllability.

[0108] The SQL statement generation module is configured to generate SQL sub-query statements for the plurality of sub-questions in sequence.

[0109] In this embodiment, the system generates SQL sub-query statements corresponding to the plurality of structured sub-questions parsed in step S200 in sequence. This process uses the multi-pass SQL method to ensure that each SQL statement matches the semantic goal of its corresponding sub-question one by one and has independence and executability in syntax and logic. At the same time, different sub-queries are connected through intermediate results to realize the layer-by-layer construction and step-by-step reasoning of complex query tasks, in line with the Chain-of-Thought concept.

[0110] The verification module is configured to verify all the SQL sub-query statements.

[0111] In this embodiment, the verification module is configured to verify all the SQL sub-query statements generated by the SQL statement generation module to ensure their correctness in terms of syntax structure and logical reference. This verification process not only eliminates syntax-level errors but also ensures the correctness of the context-dependent relationship of the query logic, avoiding semantic illusions and structural mismatches, thereby laying a reliable foundation for subsequent SQL execution.

[0112] First, the system performs syntax checking on each SQL sub-query statement. This process can call a local SQL parser or database driver interface to quickly parse the SQL statement structure and determine whether there are low-level errors such as mismatched parentheses, misspelled keywords, incorrect statement structure, etc. If the syntax check fails, the system immediately marks the SQL as failed and does not enter the subsequent verification process.

[0113] Secondly, the system performs logical reference verification, mainly including the following two aspects: Firstly, it verifies whether all table names referenced in the SQL statement exist in the data table structure set recorded in the data summary information or are temporary tables generated by the previous subquery statement. For example, if the table category_summaries is referenced in the current SQL, the system needs to confirm that the table has been successfully defined by CREATE TEMPORARY TABLE in the previous subquery. If the referenced table name is not found in the data summary information and the historical temporary table record, it is determined that the SQL logic is not valid.

[0114] Secondly, it verifies whether the fields referenced in the SQL statement exist in the corresponding table structure. Specifically, if the total_sales field is referenced in a SQL statement, the system will compare the base_sales_data table structure to confirm whether it contains the field. It should be noted that for the case of referencing the temporary table in the previous step, the field verification should be based on the definition structure of the temporary table, not limited to the original database table. If the referenced field is not registered in the target table structure, the system also determines that the SQL logic cannot be executed.

[0115] Under the premise that the above two types of verification are passed, the system marks the SQL subquery statement as passed and enters the execution preparation state.

[0116] The execution module is configured to execute all the SQL subquery statements if they pass the verification.

[0117] In the embodiment, the execution module is configured to sequentially execute the SQL subquery statements after they pass the verification of the verification module, and finally generate a complete query result. This step is the landing link of the entire Chain-of-Thought concept and multi-pass SQL method, which ensures that the previously generated SQL statements at all levels are correctly parsed and form the target data output.

[0118] Firstly, the system sorts the SQL subquery statements generated by the SQL statement generation module according to the execution order. Since the present application adopts a structured generation method of multi-pass SQL, each SQL statement usually depends on the output result of the previous statement (such as an intermediate temporary table), so its execution must strictly follow the semantic decomposition order of the sub-problems. The system will build an execution graph before execution to analyze the dependency relationship between the SQL subqueries and ensure that there is no risk of reverse execution of referencing undefined tables or fields.

[0119] Secondly, the system uses a preset database connection engine (such as PostgreSQL, MySQL, ClickHouse, etc.) to execute SQL statements one by one. For each statement, the system monitors its execution status, records the execution time and resource consumption, and captures possible running exceptions. For statements containing CREATE TEMPORARY TABLE, the system registers the generated temporary table into the execution context after execution, for subsequent SQL statements to call. All SQL executions are completed within the same session to ensure the effectiveness of the temporary table and data context consistency.

[0120] Finally, the system extracts the execution result of the final target SQL after all SQL statements are successfully executed as the response output of the user natural language query. The result can be a complete summary table, a combination structure of multiple dimension indicators, or processed report data. The system can further export the result in CSV, JSON, etc. format, or directly push it to the downstream visualization platform (such as BI system).

[0121] According to the embodiments of the present disclosure, the present disclosure also provides an electronic device, which can include a processor, a communications interface, a memory, and a communications bus, wherein the processor, the communications interface, and the memory can communicate with each other through the communications bus. The processor can invoke the logic instructions in the memory to execute the soft authorization implementation method based on the configuration software.

[0122] In addition, the logic instructions in the memory described above can be implemented in the form of a software functional unit and sold or used as an independent product, which can be stored in a computer readable storage medium. Based on this understanding, the technical solutions of the present disclosure essentially or say the part that contributes to the prior art or part of the technical solutions can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a plurality of instructions to make a computer device (which can be a personal computer, a server, or a network device, etc.) execute all or part of the steps of the method described in various embodiments of the present disclosure. The foregoing storage medium includes: a U disk, a mobile hard disk, a read-only memory (ROM, Read-Only Memory), a random access memory (RAM, Random Access Memory), a magnetic disk or an optical disk, and various media that can store program codes.

[0123] On the other hand, the present disclosure also provides a non-transitory computer readable storage medium having a computer program stored thereon, which is executed by a processor to implement the soft authorization implementation method based on the configuration software provided by the above-mentioned methods.

[0124] The device embodiments described above are merely illustrative, wherein the units described as separate components can or can not be physically separated, and the components displayed as units can or can not be physical units, i.e., can be located in one place, or can be distributed to multiple network units. Part or all of the modules can be selected to achieve the purpose of the embodiment scheme according to actual needs. Those skilled in the art can understand and implement it without creative labor.

[0125] Through the description of the above embodiments, those skilled in the art can clearly understand that the embodiments can be realized by means of software and the necessary general hardware platform, and of course can also be realized by hardware. Based on such understanding, the above technical solutions can be embodied in the form of a software product, which can be stored in a computer readable storage medium, such as a ROM / RAM, a magnetic disk, an optical disk, etc., and includes a number of instructions to make a computer device (which can be a personal computer, a server, or a network device, etc.) execute the methods described in each embodiment or some parts of the embodiments.

[0126] It should be understood that the above embodiments are only used to illustrate the technical solutions of the present disclosure, rather than limit them; although the present disclosure has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that they can still modify the technical solutions recorded in the foregoing embodiments, or make equivalent replacement for part of the technical features; and these modifications or replacements do not make the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present disclosure.

Claims

1. A method for improving the accuracy of SQL generation of an LLM large model based on the CoT concept, characterized in that, The method comprises the following steps: preprocessing database metadata to obtain data summary information and structurally storing the data summary information; parsing the input natural language and splitting the natural language into multiple sub-questions in combination with the data summary information; generating SQL sub-query statements for the multiple sub-questions in sequence; verifying all the SQL sub-query statements; if the verification is passed, executing all the SQL sub-query statements.

2. The method for improving the accuracy of SQL generation of an LLM large model based on the CoT concept according to claim 1, characterized in that, After the step of verifying all the SQL sub-query statements, the method further comprises the following steps: if the verification is not passed, re-parsing the input natural language and re-splitting the natural language into multiple sub-questions in combination with the data summary information; generating new SQL sub-query statements for the multiple sub-questions in sequence; re-verifying all the new SQL sub-query statements until the verification is passed.

3. The method for improving the accuracy of SQL generation of an LLM large model based on the CoT concept according to claim 1, characterized in that, The step of preprocessing database metadata to obtain data summary information and structurally storing the data summary information further comprises the following steps: extracting table names and field structure information of each data table in the database to form metadata entries; storing the metadata entries in a temporary data structure with the table names as keys and the field structure information as values; saving the temporary data structure as structured data summary information.

4. The method for improving the accuracy of SQL generation of an LLM large model based on the CoT concept according to claim 3, characterized in that, The step of parsing the input natural language and splitting the natural language into multiple sub-questions in combination with the data summary information further comprises the following steps: performing semantic analysis on the input natural language query statement to identify multiple-stage data processing intentions contained therein; determining, in combination with the table names and field structure information recorded in the data summary information, that the input of each-stage data processing intention is obtainable; the input of each-stage data processing intention comprises data output from a previous-stage data processing intention and / or data obtained from the data summary information.

5. The method for improving the accuracy of SQL generation of an LLM large model based on the CoT concept according to claim 4, characterized in that, The natural language is a guided instruction text constructed through a preset prompt word template; the prompt word template is used to guide a large model to analyze a task in a chain-like thinking manner.

6. The method for improving the accuracy of SQL generation of an LLM large model based on the CoT concept according to claim 1, characterized in that, The step of generating SQL sub-query statements for the multiple sub-questions in sequence further comprises the following steps: adopting a multi-pass SQL generation mode to map the multiple sub-questions to multiple SQL sub-query statements in sequence according to semantics and data processing order, each SQL sub-query statement corresponding to a semantic target of a sub-question and being capable of independently expressing the query logic thereof; there is a front-rear dependency relationship between the multiple SQL sub-query statements, and the input data of a rear sub-query statement is derived from the output result of a front sub-query statement and / or pre-stored data in the data summary information.

7. The method for improving the accuracy of SQL generation of an LLM large model based on the CoT concept according to claim 1, characterized in that, The step of verifying all the SQL sub-query statements further comprises the following steps: judging whether the syntax of the SQL sub-query statement is correct; judging whether the table name and the field in the SQL sub-query statement exist in the data summary information or the output of a previous sub-query statement; if not, the verification is not passed.

8. A system for improving the accuracy of SQL generation of an LLM large model based on the CoT concept, characterized in that, The method comprises the following steps: a data preprocessing module for preprocessing database metadata to obtain data summary information and structurally storing the data summary information; a natural language parsing module for parsing the input natural language and splitting the natural language into multiple sub-questions in combination with the data summary information; The SQL statement generation module is configured to generate SQL subquery statements for the plurality of sub-questions in sequence. The verification module is configured to verify all the SQL subquery statements. The execution module is configured to execute all the SQL subquery statements if the verification is passed.

9. An electronic device comprising: A processor, and a memory in communication connection with the processor; characterized in that: The memory stores computer execution instructions; The processor executes the computer execution instructions stored in the memory to implement the steps of the method according to any one of claims 1-7.

10. A computer-readable storage medium having stored thereon a computer program, characterized in that, The program is executed by the processor to implement the steps of the method according to any one of claims 1-7.

Images