Natural language structured query generation method and system based on pattern reasoning

Abstract

The invention discloses a natural language structured query generation method and system based on pattern reasoning, and relates to the technical field of natural language processing and database query. The method comprises the steps of mode knowledge graph construction in an offline stage and three-step reasoning generation in an online stage. In the offline stage, through structure analysis, semantic enrichment and data portrait processing, a database mode is converted into a knowledge graph Schema-KG rich in structure and semantic information. The online stage adopts three-step reasoning: firstly, decomposing a natural language of a user into an intention component through LLMParser, and linking the intention component to a graph entity; secondly, reasoning an optimal JOIN path on the Schema-KG based on a graph search algorithm, and generating a mode perception intermediate representation SA-IR; finally, the SA-IR is compiled into an SQL, and pre-verification is executed. According to the method, the accuracy of complex query is remarkably improved through a structured reasoning mechanism, errors during operation are effectively avoided through pre-execution verification, a clear reasoning process is provided through SA-IR intermediate representation, and the interpretability and debugging efficiency of the system are greatly improved.

Description

Technical Field

[0001] This invention relates to the fields of natural language processing and database query technology, and in particular to a method and system for generating structured natural language queries based on pattern reasoning. Background Technology

[0002] As enterprises deepen their digital transformation, more and more business personnel need to retrieve data from databases for analysis and decision-making. However, traditional data query methods require users to master professional query languages ​​such as SQL, which poses a high barrier to entry for non-technical personnel. Text-to-SQL technology has emerged to address this need, allowing users to describe their query requirements using natural language, which the system automatically converts into executable SQL statements. This technology has broad application prospects in business intelligence, data analysis, and intelligent question answering, significantly reducing the technical barriers to data access and improving the efficiency of data utilization for enterprises.

[0003] Existing Text-to-SQL technologies primarily employ deep learning-based methods. For example, CN118312531B discloses a query language generation method, system, electronic device, and storage medium. This method includes: acquiring natural language data input by a user into a large model, and acquiring the names of multiple preset analysis indicators from a pre-generated indicator library, wherein the indicator library is generated based on the original database; using the large model to identify the natural language data based on the indicator names of the multiple preset analysis indicators, determining at least one target analysis indicator included in the natural language data; and using a processing engine to generate structured query language (SQL) data corresponding to the natural language data according to the calculation logic of the target analysis indicators included in the indicator library. Furthermore, traditional Text-to-SQL systems typically use Seq2Seq or direct generation methods based on large language models (LLMs), concatenating the text description of the database schema with the user's question to directly generate an SQL query.

[0004] However, existing technologies suffer from the following fundamental flaws: First, they lack structured reasoning. The schema seen by the model is merely a flat piece of text, failing to truly understand the join relationships (foreign keys), hierarchical relationships (one-to-many), and semantic equivalence between tables (e.g., users.id and orders.user_id pointing to the same entity). This leads to erroneous JOIN operations being the most common cause of failure. Second, they ignore the inherent distribution of data. The model is unaware of the specific data type, cardinality, and possible value range of a column, potentially generating WHERE clauses containing invalid values ​​(e.g., WHERE status='DELIVERED', when the database only contains 'shipped'), or performing meaningless aggregations. Third, they generate inefficient queries. Due to a lack of understanding of the data structure, the model may generate queries that, while syntactically correct, have extremely low execution efficiency, such as choosing the wrong JOIN order or not using indexes. Finally, they suffer from severe uninterpretability issues. When generating erroneous SQL, it is difficult to diagnose which part of the model is misunderstanding the problem, making debugging and optimization extremely challenging. These issues can cause a sharp decline in system performance when dealing with complex enterprise databases with dozens or even hundreds of tables, making them unreliable for real-world production applications. Summary of the Invention

[0005] To address the shortcomings of existing technologies, this invention provides a method and system for generating structured queries in natural language based on pattern reasoning. By constructing a pattern knowledge graph and a three-stage reasoning mechanism, it fundamentally solves the technical problems of traditional Text-to-SQL systems in terms of JOIN operations, data value range verification, query efficiency, and interpretability.

[0006] To address the aforementioned problems, the technical solution of this invention is as follows: A method for generating structured queries in natural language based on pattern reasoning includes offline pattern knowledge graph construction and online three-step reasoning generation. The offline phase includes: receiving database DDL, foreign key constraint information, and data statistics; extracting tables, columns, primary keys, and foreign keys as nodes and edges of the graph through structural analysis, where tables and columns are entity nodes, has_column is the relation, and primary key-foreign key pairs are modeled as joinable_with relation edges; analyzing column names, comments, and data content through LLM analysis to infer and add semantic relationship tags; performing data profiling analysis on key columns, recording data type, nullability, cardinality, and unique value list information, and attaching it to the graph node attributes; and outputting a schema knowledge graph (Schema-KG). The online phase consists of three steps: First, query deconstruction and entity linking, where the user's question is decomposed into intent components and linked to entities in the Schema-KG using LLMParser; second, join path reasoning and plan generation, where a graph-based planner executes a graph search algorithm on the Schema-KG to find the optimal path to the join tables based on the path cost function Cost(Path) = w1·Σedge + w2·N_tables, generating a schema-aware intermediate representation SA-IR; and third, SQL compilation and verification, where SA-IR is compiled into SQL code specific to the database dialect and a pre-execution verification process is performed.

[0007] 2. The method according to claim 1, characterized in that the pattern-aware intermediate representation SA-IR adopts a structured representation in JSON format, including a select field, a from_join_path field, a where field, and a group_by field; wherein the select field records the columns to be queried and their aggregate functions, the from_join_path field records the table access order and JOIN conditions, the where field records the filtering conditions, and the group_by field records the grouping basis.

[0008] Furthermore, the graph search algorithm executed by the graph-based planner is either Dijkstra's algorithm or A* algorithm; the edge_weight in the path cost function is determined according to the relation type, the weight of foreign key joins is lower than the weight of semantic inference joins, and N_tables represents the number of tables in the path.

[0009] Furthermore, the pre-execution verification process includes: checking whether the value in the WHERE clause is within the value range of the data profile; when verification fails, the error information is fed back to the Join path reasoning and plan generation steps to repair and regenerate the plan, forming a pre-execution verification and repair loop.

[0010] Furthermore, during query destructuring, LLMParser decomposes the SELECT target into a combination of entity links and aggregation operations, and the FILTER condition into a triple structure of column name, operator, and value. Each component is linked to the corresponding graph node in the Schema-KG.

[0011] A natural language structured query generation system based on pattern reasoning, comprising: The schema knowledge graph builder is configured to receive DDL, foreign key constraint information and data statistics from the database during the offline phase, and construct and output the schema knowledge graph Schema-KG through structural analysis, semantic enrichment and data profiling. The LLMParser module is configured to decompose the user's natural language question into intent components, link each component to the corresponding entity in the Schema-KG, and output the query intent structure linked to the graph nodes; The graph-based planner is configured to receive table entities in the query intent structure, perform a graph search algorithm on the Schema-KG, calculate the optimal Join path based on the path cost function, and generate a schema-aware intermediate representation SA-IR containing explicit Join paths. The SQL compiler and validator are configured to compile SA-IR into SQL code in a specific database dialect, perform pre-execution validation, and trigger a repair loop when validation fails.

[0012] Furthermore, the pattern knowledge graph builder includes: a structure analysis unit, which extracts tables, columns, primary keys, and foreign keys to construct the graph structure; a semantic enrichment unit, which infers semantic relationships through LLM and adds the semantically_equivalent tag; and a data profiling unit, which analyzes the data type, cardinality, and value range of columns and records them as node attributes.

[0013] Furthermore, the system also includes a persistent storage module configured to store generated executable SQL queries and knowledge graph instances for use by downstream AI applications.

[0014] This invention proposes a three-stage Text-to-SQL generation system based on schema-grounded reasoning. First, in an offline stage, the system performs in-depth analysis and enrichment of the database schema, constructing a schema knowledge graph. Then, in an online stage, the user's question is progressively decomposed and mapped to a schema-aware intermediate representation (SA-IR). Finally, this structured and validated IR is compiled into the final SQL.

[0015] Specifically, the offline pattern knowledge graph construction includes: inputting the database's DDL, foreign key constraint information, and data statistics; extracting tables, columns, primary keys, and foreign keys as nodes and edges of the graph through structural analysis, with Table and Column as entity nodes, has_column as relations, and PK-FK pairs modeled as strong relation edges of joinable_with; using LLM-assisted semantic enrichment, analyzing column names, comments, and data content to infer and add semantic relations, such as users.id and orders.user_id being tagged with semantically_equivalent; performing data profiling, analyzing key columns, recording their data type, nullability, cardinality, unique value list, etc., and attaching them to the graph's node attributes; and finally outputting a pattern knowledge graph rich in structural, semantic, and data distribution information.

[0016] The online phase involves three steps of inference generation: Step 1: Query deconstruction and entity linking. A dedicated LLMParser decomposes the user's query into intent components and links them to entities in the Schema-KG, outputting a preliminary query intent structure linked to graph nodes. Step 2: Join path inference and plan generation. A graph-based planner receives all relevant table entities, executes a graph search algorithm (such as Dijkstra's or A*) on the Schema-KG, finds the shortest and most reasonable path connecting these tables based on the path cost function, and outputs a schema-aware intermediate representation (SA-IR) containing explicit join paths. Step 3: SQL compilation and verification. The highly structured SA-IR is translated into SQL code specific to the database dialect, and a pre-execution verification process is performed to check whether the values ​​in the WHERE clause are within the value range of the data profile, ultimately outputting a verified, highly probable, and efficient executable SQL.

[0017] The key technological innovations of this invention include: Schema Knowledge Graph (Schema-KG) transforms flat schema descriptions into rich, multi-dimensional knowledge networks; Schema-Aware Intermediate Representation (SA-IR), as a human-readable structured representation between natural language and SQL, forces the model to first consider "what to do" rather than "how to write"; Graph Search-based Join path discovery algorithm transforms the ambiguous "how to join" problem into an explicit graph theory problem of solving the shortest path on the Schema-KG; and the pre-execution verification and repair loop mechanism uses the data profile of the Schema-KG for inspection, intercepting a large number of low-level errors caused by value range errors.

[0018] The beneficial effects of this invention are: Through structured Join path reasoning and entity linking, the system can generate more complex and accurate SQL queries, especially in multi-table Join scenarios, where the error rate is significantly reduced and the accuracy is greatly improved. The pre-execution verification mechanism can effectively avoid runtime errors caused by invalid values, making the system more stable and reliable on real business data, and enhancing its robustness and reliability. When problems occur, it is possible to clearly trace whether the error occurred in the "entity linking", "path reasoning" or "SQL compilation" stage. SA-IR itself is a clear "reasoning process report", which greatly facilitates system debugging and iteration. The graph-based planner can integrate the database cost model in the future, selecting the path with the lowest estimated execution cost among multiple feasible Join paths, thereby generating SQL with better performance, and has the potential for performance optimization. Attached Figure Description

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

[0020] Figure 1 This is a system architecture diagram of the present invention, showing the overall structure of the system; Figure 2 The flowchart for constructing the pattern knowledge graph of this invention illustrates the three processing steps in the offline stage: structural analysis, semantic enrichment, and data profiling; it also explains the transformation process from input (DDL, foreign key constraints, and data statistics) to output. Figure 3 This is a flowchart illustrating the online three-step reasoning process of the present invention. It shows the three key steps of online processing and their output examples; Figure 4 This is an example diagram of the schema knowledge graph of the present invention, which specifically illustrates the graph structure of the Schema-KG; Figure 5 This diagram illustrates the SA-IR intermediate representation conversion example of the present invention, fully demonstrating the conversion process from natural language query to SA-IR and then to SQL. Detailed Implementation

[0021] The present invention will now be described in detail with reference to the accompanying drawings and specific embodiments. It should also be noted that, to make the embodiments more comprehensive, the following embodiments are the best and preferred embodiments, and those skilled in the art can use other alternative methods to implement some well-known technologies; moreover, the accompanying drawings are only for more specific description of the embodiments and are not intended to specifically limit the present invention.

[0022] It should be noted that the use of terms such as "an embodiment," "an embodiment," "an exemplary embodiment," and "some embodiments" in the specification indicates that the described embodiment may include a specific feature, structure, or characteristic, but not every embodiment necessarily includes that specific feature, structure, or characteristic. Furthermore, when a specific feature, structure, or characteristic is described in connection with an embodiment, implementing such a feature, structure, or characteristic in conjunction with other embodiments (whether explicitly described or not) should be within the knowledge of those skilled in the art.

[0023] Generally, terms can be understood at least partly from their use in context. For example, depending at least partly on the context, the term "one or more" as used herein can be used to describe any feature, structure, or characteristic in a singular sense, or a combination of features, structures, or characteristics in a plural sense. Additionally, the term "based on" can be understood not necessarily to convey an exclusive set of factors, but rather, alternatively, depending at least partly on the context, to allow for the presence of other factors that are not necessarily explicitly described.

[0024] See Figures 1 to 5 As shown Implementation Method 1: Natural Language Structured Query Generation System Based on Pattern Reasoning This embodiment provides a natural language structured query generation system based on pattern reasoning. The system adopts a three-stage schema-grounded reasoning architecture, including an offline phase of pattern knowledge graph construction and an online phase of three-step reasoning generation. The overall system architecture comprises four core modules: a pattern knowledge graph builder, an LLM parser, a graph-based planner, and an SQL compiler and validator.

[0025] In the offline phase, the schema knowledge graph builder receives DDL (Data Definition Language) information, foreign key constraint information, and data statistics from the database as input. The construction process begins with structural analysis, extracting tables, columns, primary keys (PK), and foreign keys (FK) as nodes and edges in the graph. Tables and columns are treated as entity nodes, `has_column` as a relation, and PK-FK pairs are modeled as a strong joinable-with edge. Next, semantic enrichment processing is performed, using LLM analysis of column names, comments, and data content to infer and add semantic relationships; for example, `users.id` and `orders.user_id` are labeled `semantically_equivalent`. Finally, data profiling analysis is performed, deeply analyzing key columns to record their data type, nullability, cardinality, and unique value list (for low-cardinality columns), and appending this information to the graph's node attributes. The result is a schema knowledge graph (Schema-KG) rich in structural, semantic, and data distribution information.

[0026] The online processing flow consists of three key steps. The first step is Query Deconstruction & Entity Linking, performed by a dedicated LLM Parser. This module does not directly generate SQL; instead, it breaks down the user's query into intent components and links them to entities in the Schema-KG. For example, for the query "Query the names and total order amount of VIP users in Nanjing who placed orders last month," the LLM will output a preliminary query intent structure: SELECT_Targets: [users.name (entity link), SUM(orders.amount) (entity link + aggregation)], FILTER_Conditions: [users.city = 'Nanjing', users.level = 'VIP', orders.create_time in 'last month'].

[0027] The second step is Join Path Inference & Plan Generation, executed by a graph-based planner. The planner receives all table entities involved in the previous step (e.g., users, orders) and then performs a graph search algorithm (e.g., Dijkstra or A*) on the schema-kg to find the shortest and most efficient path connecting these tables. The path cost function is defined as Cost(Path) = w1·Σedge + w2·N_tables, where edge_weight is determined by the relation type (lower weight for FK joins, higher weight for semantic inference joins), and N_tables is the number of tables in the path. This step outputs a structured schema-aware intermediate representation (SA-IR) containing the explicit join path, in JSON format, including structured fields such as select, from_join_path, where, and group_by.

[0028] The third step is SQL Compilation and Validation, handled by the SQL compiler and validator. The compiler translates the highly structured SA-IR into SQL code specific to the database dialect (such as PostgreSQL or MySQL), a relatively deterministic conversion process. After generating the final SQL, the validator performs a pre-execution validation process, such as checking whether the values ​​in the WHERE clause are within the value range of the data profile (e.g., checking if 'VIP' is a valid value for the users.level column). If validation fails, the system feeds back the error information to the previous step, performing planned repairs and regeneration, forming a validation and repair loop. Finally, a validated, highly probable, and efficient executable SQL statement is output, and a knowledge graph instance is persistently stored for downstream AI applications to query.

[0029] Example 1 To better illustrate the implementation effect of this invention, the following description uses a specific enterprise-level e-commerce database query scenario. Assume that an e-commerce platform database contains multiple interconnected tables such as users (user table), orders (order table), products (product table), and order_items (order item table).

[0030] When a user enters a natural language query, "Query the names and total order amounts of VIP users who placed orders in Nanjing last month," the system first identifies, from the pattern knowledge graph already built in the offline phase, that the user table contains columns such as id, name, city, and level, and the orders table contains columns such as id, user_id, amount, and create_time, and that users.id and orders.user_id have a foreign key relationship.

[0031] During the query deconstruction phase, the LLM Parser breaks down the query into: an aggregation (SUM) with the target fields users.name and orders.amount, and filtering conditions including users.city equal to 'Nanjing', users.level equal to 'VIP', and orders.create_time within the previous month's time range. The entity linking process maps "Name" to the users.name column, "Order Amount" to the SUM(orders.amount) aggregate function, "Nanjing Region" to the condition users.city='Nanjing', and "VIP Users" to the condition users.level='VIP'.

[0032] During the Join path reasoning phase, the graph-based planner performs a search on the Schema-KG and finds that the users and orders tables are directly joined through a foreign key relationship of users.id=orders.user_id, resulting in the lowest computational cost. Therefore, this Join path is selected. The system-generated SA-IR intermediate representation contains the complete query structure: the select field includes users.name (no aggregation) and orders.amount (SUM aggregation), from_join_path specifies that the users table is accessed first, and then the orders table is joined using the users.id=orders.user_id condition. The where condition includes three filtering conditions, and group_by specifies grouping by users.name.

[0033] During the SQL compilation and validation phase, the compiler converts SA-IR into a standard SQL statement: `SELECT users.name, SUM(orders.amount) as total_amount FROM users INNER JOIN orders ON users.id = orders.user_id WHERE users.city = 'Nanjing' AND users.level = 'VIP' AND orders.create_time BETWEEN '2023-10-01' AND '2023-10-31' GROUP BY users.name`. The validator then checks if 'Nanjing' is within the value range of the `users.city` column, if 'VIP' is a valid value for `users.level`, and if the date range is reasonable. After successful validation, the system outputs the final executable SQL query.

[0034] By employing this pattern-based reasoning approach, the system successfully avoids common problems in traditional Text-to-SQL systems, such as JOIN errors and invalid value errors. The generated queries are not only syntactically correct but also semantically accurate in reflecting the user's intent, while execution efficiency is optimized. The entire reasoning process is fully recorded through the SA-IR intermediate representation, facilitating problem diagnosis and system optimization, and fully demonstrating the significant advantages of this invention in terms of accuracy, reliability, interpretability, and performance optimization.

[0035] This invention encompasses any substitutions, modifications, equivalent methods, and solutions made within the spirit and scope of this invention. To provide the public with a thorough understanding of this invention, specific details are described in detail in the following preferred embodiments; however, those skilled in the art will fully understand the invention even without these details. Furthermore, to avoid unnecessary misunderstanding of the essence of this invention, well-known methods, processes, procedures, components, and circuits are not described in detail.

[0036] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. A method for generating a natural language structured query based on schema inference, characterized in that, The mode knowledge graph construction including an offline stage and three-step reasoning generation in an online stage; The offline stage includes: receiving DDL, foreign key constraint information and data statistical information of a database; extracting tables, columns, primary keys and foreign keys as nodes and edges of a graph through structure analysis, wherein the tables and columns are entity nodes, has_column is a relationship, and the primary key-foreign key pair is modeled as a relationship edge of joinable_with; analyzing column names, annotations and data content through LLM to infer and add semantic relationship labels; performing data portrait analysis on key columns to record data types, whether null, cardinality and unique value list information, and appending to graph node attributes; and outputting a schema knowledge graph Schema-KG; The online stage includes three steps: first, query deconstruction and entity linking, which decomposes a user question into intent components and links to entities in Schema-KG through an LLMParser; second, join path reasoning and plan generation, which performs a graph search algorithm on Schema-KG based on a graph planner, finds an optimal path of connected tables according to a path cost function Cost(Path) = w1·Σedge+w2·N_tables, and generates a schema-aware intermediate representation SA-IR; and third, SQL compilation and verification, which compiles SA-IR into SQL code in a specific database dialect and performs a pre-execution verification process.

2. The method of claim 1, wherein, The schema-aware intermediate representation SA-IR adopts a structured representation in JSON format, including a select field, a from_join_path field, a where field and a group_by field; wherein the select field records columns to be queried and their aggregation functions, the from_join_path field records table access order and JOIN conditions, the where field records filtering conditions, and the group_by field records grouping basis.

3. The method of claim 1, wherein, The graph search algorithm performed by the graph planner is Dijkstra's algorithm or A* algorithm; edge_weight in the path cost function is determined according to the relationship type, and the weight of foreign key connection is lower than that of semantic inference connection; and N_tables represents the number of tables in the path.

4. The method of claim 1, wherein, The pre-execution verification process includes: checking whether the values in the WHERE clause are within the value range of the data portrait; when the verification fails, feeding back error information to the join path reasoning and plan generation step to repair and regenerate the plan, forming a pre-execution verification and repair cycle.

5. The method of claim 1, wherein, The LLMParser, in query deconstruction, decomposes SELECT targets into a combination of entity linking and aggregation operations, and decomposes FILTER conditions into a triple structure of column name, operator and value, and each component is linked to the corresponding graph node in Schema-KG.

6. A pattern-inference-based natural language structured query generation system, characterized by, The method comprises: a mode knowledge graph constructor configured to receive DDL, foreign key constraint information and data statistical information of a database in an offline stage, and construct and output a schema knowledge graph Schema-KG through structure analysis, semantic enrichment and data portrait processing; and The LLMParser module is configured to decompose a user natural language question into intent components and link each component to a corresponding entity in the Schema-KG, outputting a query intent structure linked to graph nodes; The graph-based planner is configured to receive table entities in the query intent structure, execute a graph search algorithm on the Schema-KG, calculate an optimal Join path according to a path cost function, and generate a schema-aware intermediate representation SA-IR containing the explicit Join path; The SQL compiler and verifier is configured to compile the SA-IR into SQL code in a specific database dialect, perform pre-execution verification, and trigger a repair loop if verification fails.

7. The system of claim 6, wherein, The schema knowledge graph builder includes a structure analysis unit that extracts tables, columns, primary keys, and foreign keys to construct a graph structure; a semantic enrichment unit that infers semantic relationships and adds semantically_equivalent labels through LLM; and a data portrait unit that analyzes column data types, cardinality, and value ranges and records them as node attributes.

8. The system of claim 6, wherein, The system also includes a persistent storage module configured to store the generated executable SQL queries and knowledge graph instances for use by downstream AI application queries.

Images