Mode intermediate generation and compiling method for structured query generation

Abstract

The invention discloses a mode intermediate generation and compiling method for structured query generation. Comprising the following steps: constructing a knowledge graph Schema-KG containing database complete mode information; a special mode perception intermediate representation SA-IR is designed, a JSON format tree structure is adopted, and all database entities quote and forcibly use three-section type identifiers and verify that the three-section type identifiers exist in Schema-KG; the method comprises the following steps of: innovatively carrying out logic linearization on a complex JOIN into an ordered table sequence fromjoin path; the five-step generation process comprises intention analysis, entity linking, Join path discovery, path filling and assembly verification; and providing a deterministic templated compiling mechanism, and converting SA-IR into an SQL of a target dialect. According to the method, common errors such as reference without entities and the like are structurally eradicated, SA-IR serves as a readable query plan to facilitate problem positioning and debugging, complex tasks are decomposed into independent subtasks to reduce the system complexity, a mode of generating multiple compiles at a time supports multiple database systems at extremely low cost, and the method is suitable for large-scale popularization and application. And the practicability and the commercial value of the Text-to-SQL technology are improved.

Description

Technical Field

[0001] This invention relates to the field of computer data processing technology, and in particular to a method for intermediate generation and compilation of patterns for structured query generation. Background Technology

[0002] In today's data-driven era, enterprises and organizations have accumulated massive amounts of structured data, typically stored in relational databases. However, extracting valuable information from these databases requires users to master the Structured Query Language (SQL), a significant hurdle for most non-technical users. Text-to-SQL technology, an automatic conversion technology from natural language to structured queries, aims to enable users to query databases using everyday language, thereby democratizing data access. This technology has broad application prospects in business intelligence, data analysis, and intelligent customer service, significantly lowering the technical barrier to data access and improving organizational data utilization efficiency.

[0003] Existing Text-to-SQL technologies mainly fall into two categories, but both suffer from fundamental flaws. The first category is end-to-end direct generation solutions, which use large neural network models to directly map natural language to SQL strings. This black-box generation process suffers from severe uncontrollability and uninterpretability issues. When generated SQL errors occur, developers cannot diagnose the root cause or make targeted improvements. More seriously, these models lack a deep understanding of database structures, often generating queries that appear syntactically correct but are logically flawed, such as selecting non-existent columns or creating invalid table joins. The second category is generation solutions based on traditional intermediate representations, typically using Abstract Syntax Trees (ASTs) as an intermediate step. However, general-purpose ASTs are primarily designed for compilers, and their structure differs significantly from the intended expression of natural language, making the mapping from natural language to AST equally difficult. Nodes in traditional ASTs are usually in free text form, without mandatory binding to the underlying database schema. Therefore, the validity of entities cannot be verified during the generation phase, and errors are propagated until the final SQL execution phase before being detected.

[0004] The root of these problems lies in the significant semantic gap between the ambiguity and context-dependent nature of natural language and the strictness and determinism of SQL. The same concept in natural language may correspond to different entities in a database; for example, "username" might refer to the `name` field in the `users` table or the `username` field in the `accounts` table. Complex queries involving multi-table joins are often implicit in natural language and require reasoning based on the database schema to determine. Existing solutions attempt to bridge this gap with a single model or a simple intermediate representation, resulting in either sacrificing accuracy or interpretability. Therefore, a new type of intermediate representation is urgently needed, specifically designed to bridge the gap between natural language and SQL, capable of enforcing a step-by-step, verifiable reasoning process, and deeply bound to the underlying database schema to structurally prevent common errors. Summary of the Invention

[0005] To address the aforementioned problems in existing technologies, this invention provides a pattern-aware intermediate representation for structured query generation and its generation and compilation method, aiming to fundamentally solve the problems of accuracy, interpretability, and cross-dialect compatibility in the natural language to SQL conversion process.

[0006] To address the aforementioned issues, this invention provides a method for intermediate schema generation and compilation for structured query generation, comprising the following steps: Construct a schema knowledge graph (Schema-KG), which contains identifier information for all databases, tables, and columns of the target database, as well as foreign key relationship information between tables. The input natural language query is converted into a pattern-aware intermediate representation, SA-IR. SA-IR is a tree structure in JSON format, containing fields for select, from_join_path, where, group_by, order_by, and limit. Specifically: the select field is an array of SelectItem objects, each containing a column, agg, and alias attribute. The value of the column attribute is a three-part identifier in the format schema.table.column; the from_join_path field is an ordered list of JoinNode objects, each containing a table and on attribute. The value of the table attribute is an identifier in the format schema.table; and the where field is a recursively defined tree structure containing a logic attribute and a conditions array. SA-IR is compiled into SQL query statements for the target database. The compilation process includes: traversing the select fields to generate SELECT clauses, traversing the from_join_path fields to generate FROM and JOIN clauses, recursively processing the where fields to generate WHERE clauses, and processing the group_by, order_by, and limit fields to generate corresponding SQL clauses.

[0007] Furthermore, the steps of converting the input natural language query into a pattern-aware intermediate representation (SA-IR) include: Large language models are used to parse the intent and entities of natural language queries, identifying query intent and semantic entities. The identified semantic entities are linked to database objects in the Schema-KG. Each semantic entity is linked to a GroundedColumn in the format schema.table.column or a GroundedTable in the format schema.table. Collect all linked tables to form a target table set, and execute a graph search algorithm on the Schema-KG to calculate the optimal path connecting all tables in the target table set; The optimal path is converted into an ordered list of JoinNode objects with the from_join_path field. The first JoinNode in the list has a null on attribute, and the on attribute of subsequent JoinNodes contains the join conditions with the preceding table. The linked entities, the transformed paths, and the identified filtering conditions are assembled into a complete SA-IR instance.

[0008] Furthermore, the graph search algorithm employs an improved Dijkstra's shortest path algorithm. The path cost function comprehensively considers path length, connection type, table size, and index existence. The algorithm maintains a priority queue and expands the search from each table in the target table set until it finds the minimum cost path connecting all target tables.

[0009] Furthermore, in the tree structure of the where field, the root node is a WhereClause object, which contains a logic attribute with a value of AND or OR and a conditions array. Each element of the conditions array is a SimpleCondition object or a WhereClause object. The SimpleCondition object contains a column attribute, an op attribute, and a value attribute, where the value of the column attribute must be a GroundedColumn that exists in the Schema-KG.

[0010] Furthermore, when compiling the from_join_path field, the table attribute of the first JoinNode object in the list is extracted to generate the FROM clause. Starting from the second JoinNode object, the process is repeated sequentially. For each JoinNode object, a join clause in the format "LEFT JOIN table_name ON join_condition" is generated, where table_name comes from the table attribute and join_condition comes from the on attribute.

[0011] Furthermore, the agg property of the SelectItem object is an enumeration of aggregate functions, including SUM, AVG, COUNT, MAX, and MIN. When the agg property is not null, the compilation generates an aggregate expression in the format "AGG_FUNCTION(table.column)"; when the alias property is not null, "ASalias_name" is added to generate a column alias.

[0012] Furthermore, the method also includes integrity verification of the generated SA-IR. The verification includes checking that the GroundedColumn corresponding to all column attribute values ​​exists in the Schema-KG, checking that the GroundedTable corresponding to all table attribute values ​​exists in the Schema-KG, and ensuring that the SA-IR structure conforms to the predefined syntax specification.

[0013] Furthermore, the compilation process supports multiple SQL dialects. By configuring different function templates, keyword mappings, and syntax rules, the same SA-IR instance can be compiled into SQL query statements for PostgreSQL, MySQL, SQL Server, or Oracle database systems.

[0014] The core innovation of this invention lies in proposing a novel concept: Pattern-Aware Intermediate Representation (SA-IR). SA-IR is not a general programmatic representation, but rather a domain-specific language designed specifically to solve Text-to-SQL problems. It employs a tree structure in JSON format, containing all the core components required for a single query, including select lists, join paths, filtering conditions, grouping, sorting, and quantity limits. The biggest difference from traditional intermediate representations is that all database entity references in SA-IR must be "grounded," meaning each column reference must use the three-part identifier format of `schema.table.column`, each table reference must use the format of `schema.table`, and these identifiers must actually exist in a pre-built schema knowledge graph. This mandatory grounding requirement structurally eliminates the possibility of references that do not contain entities.

[0015] Another key innovation of this invention is the linearization of the `from_join_path` field. Traditional SQL's JOIN clauses employ a nested structure, which, while flexible, is complex and prone to errors. This invention decouples and linearizes the complex, potentially mesh-like, join logic into an ordered sequence of tables. This sequence is precisely the optimal path calculated on a schema knowledge graph by a specialized Join path discovery algorithm. This design completely separates the complex reasoning of "pathfinding" from the simple execution of "walking," making the generation and compilation of the join logic extremely simple and deterministic.

[0016] In terms of methodology, this invention decomposes the entire Text-to-SQL task into three distinct stages. The first stage is the generation process from natural language to SA-IR, including five steps: intent and entity parsing, entity linking, join path discovery, path filling, and final assembly. Each step has clearly defined input / output and verification mechanisms. The second stage is the representation and verification of SA-IR itself, ensuring that the generated intermediate representation is structurally and semantically correct. The third stage is the compilation process from SA-IR to SQL, a deterministic templated transformation that generates corresponding SQL statements based on the dialect characteristics of the target database.

[0017] The beneficial effects of this invention are: First, regarding accuracy and reliability, SA-IR's structured constraints and forced grounding mechanism fundamentally eliminate common problems such as selecting non-existent columns and generating incorrect Join relationships. Second, in terms of interpretability and debuggability, SA-IR itself is a human-readable query plan. When the final SQL fails, developers can directly inspect the fields of SA-IR to quickly pinpoint whether the problem lies in natural language parsing, entity linking, or path discovery. Third, regarding system complexity, this invention decomposes a complex end-to-end problem into multiple sub-problems with single responsibilities that are easier to implement and optimize, significantly reducing the overall system complexity. Finally, in terms of cross-database compatibility, the "generate SA-IR once, compile SQL everywhere" model enables the system to support multiple database systems such as PostgreSQL, MySQL, SQL Server, and Oracle at extremely low cost, possessing high engineering value and business flexibility. This approach not only enhances the practicality of Text-to-SQL technology but also opens up new directions for the integration of natural language processing and database technology. Attached Figure Description

[0018] 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.

[0019] Figure 1 This is a system architecture diagram of the present invention, illustrating the complete processing flow from natural language query to SQL statement; Figure 2 This is a diagram of the SA-IR data structure of the present invention, illustrating the internal structure design of SA-IR; Figure 3 The flowchart for the natural language to SA-IR generation process of this invention illustrates the specific process of the five-step generation process; Figure 4 The SA-IR to SQL compilation flowchart of this invention illustrates the deterministic compilation process; Figure 5 This is a schematic diagram of the Join path discovery algorithm of the present invention, illustrating how the algorithm finds the optimal connection path in the graph structure of the Schema-KG. Detailed Implementation

[0020] 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.

[0021] 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.

[0022] 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.

[0023] See Figures 1 to 5 As shown This invention provides a pattern-aware intermediate representation for structured query generation and its generation and compilation method. The technical solution of this invention will be described in detail below with reference to specific implementation methods and embodiments.

[0024] I. System Overall Architecture Implementation The system implementation of this invention comprises the specific construction of three core modules. First is the Schema-KG construction module, which is responsible for extracting complete schema information from the target database, including the names and data types of all databases, tables, and columns, as well as foreign key relationships and index information between tables, constructing a graph structure that can be queried quickly. This graph is stored using an adjacency list data structure, where each node represents a database entity (such as a table or column), and edges represent relationships between entities (such as foreign key constraints). Second is the SA-IR generation engine module, which integrates a Large Language Model (LLM) for natural language understanding, an entity linker for mapping concepts in natural language to database entities, and a join path discovery component based on graph algorithms. Finally, there is the SQL compiler module, which contains compilation templates for different SQL dialects, enabling deterministic conversion of SA-IR into SQL statements for the target database system.

[0025] II. Specific Implementation of SA-IR Data Structure SA-IR uses JSON format as its serialization representation, and its core data structure is implemented in the system through an object model. The top-level SA-IR object contains six key fields, each with strict type constraints and validation rules. The `select` field is an array of `SelectItem` objects, each containing three attributes: the `column` attribute must be of type `GroundedColumn`, with a value formatted as a three-part identifier "schema.table.column", which the system verifies for its existence in the Schema-KG during generation; the `agg` attribute is an optional aggregate function enumeration value, supporting five standard aggregate functions: SUM, AVG, COUNT, MAX, and MIN; and the `alias` attribute specifies an alias for the output column. The `from_join_path` field is the core innovation of this invention; it is an ordered list of `JoinNode` objects, each containing table attributes (must be of type `GroundedTable`, formatted as "schema.table") and an `on` attribute (a string describing the join condition). The first `JoinNode` in the list has a `null` `on` attribute, indicating that it is the main table being queried, and subsequent `JoinNodes` describe the join relationships with the preceding tables in turn.

[0026] The `where` field uses a recursively defined tree structure to represent complex filtering conditions. The root node is a `WhereClause` object containing a `logic` property (with a value of "AND" or "OR") and an array of conditions. Each element in the `conditions` array can be a `SimpleCondition` (leaf node) or another `WhereClause` (subtree), allowing for the expression of arbitrarily complex logical combinations. The `SimpleCondition` object contains three properties: `column` (of type `GroundedColumn`), `op` (operators such as "=", "!=", ">", "<", "LIKE", "IN", "BETWEEN", etc.), and `value` (comparison value). The `group_by` field is an array of `GroundedColumn` objects, specifying the column used for grouping. The `order_by` field is an array of `OrderByItem` objects, each containing two properties: `column` (of type `GroundedColumn`) and `direction` ("ASC" or "DESC"). The `limit` field is an integer or null, used to limit the number of results returned.

[0027] III. Specific Implementation of SA-IR Generation Method The SA-IR generation process consists of five meticulously designed steps, each with its own specific implementation details. The first step is intent and entity parsing. The system uses a pre-trained large language model (such as GPT-4 or Claude) to analyze the input natural language query. Specifically, a prompt template containing task descriptions, database schema information, and few-shot examples is constructed to guide the model in recognizing the query intent (such as retrieval, aggregation, ranking, etc.) and the involved semantic entities (such as "user name", "order amount", "Shanghai region", etc.). The model's output is structured into an intermediate representation containing fields such as intent (intent type), entities (entity list), and filters (filtering conditions).

[0028] The second step is entity linking, a crucial step in ensuring SA-IR is "grounded." The system implements an entity linker based on similarity calculation, which matches each semantic entity identified in the previous step with the actual database object in the Schema-KG. The linker employs a combination of strategies: first, it performs exact matching, checking if the entity name exactly matches the database object name; second, it performs fuzzy matching, using an edit distance algorithm to calculate similarity; then, it performs semantic matching, using a pre-trained word vector model to calculate semantic similarity; finally, it considers contextual information, such as the entity's data type and the semantics of the table it belongs to. Each entity is linked to one or more candidate database objects, and the system selects the best match based on a comprehensive score.

[0029] The third step is join path discovery, which is the core algorithm implementation of this invention. After entity links are completed, the system collects all involved tables to form a target table set, and then executes a graph search algorithm on the schema-KG to find the optimal path connecting these tables. The specific implementation of the algorithm adopts an improved Dijkstra's shortest path algorithm, where the cost function of the path considers not only the path length (the number of tables traversed), but also the join type (preferring foreign key joins), table size (preferring smaller tables), and the existence of indexes (preferring joins with indexes). The algorithm maintains a priority queue, starting from each table in the target table set and gradually expanding the search until a minimum-cost path connecting all target tables is found. If multiple equivalent paths exist, the system selects the optimal one according to predefined heuristic rules.

[0030] The fourth step is to populate the `from_join_path` field, converting the optimal path found in the previous step into the SA-IR `from_join_path` representation. In practice, the system creates a list of `JoinNode` objects in the order of the paths, with the first table serving as the primary table (its `on` attribute is null). Each subsequent table contains join conditions with the preceding tables. The join conditions are generated based on the foreign key information stored in the Schema-KG, formatted as "table1.column1 = table2.column2".

[0031] The fifth step is final assembly, where the system combines all the components generated in the previous steps into a complete SA-IR instance. This includes populating the selected fields with the linked entities (and adding the corresponding `agg` attribute if it's an aggregation query), converting the filtering conditions into a nested structure of the `where` clause, and identifying and setting attributes such as `group_by`, `order_by`, and `limit`. After assembly, the system performs an integrity verification to ensure that all `GroundedColumn` and `GroundedTable` exist in the Schema-KG, all required fields are populated, and the structure conforms to the SA-IR syntax definition.

[0032] IV. Specific Implementation of the SA-IR Compilation Method The compilation from SA-IR to SQL is a deterministic templated transformation process. The compiler implementation includes the following specific steps. First, the SQL builder is initialized, which is a data structure that maintains the various clauses of the SQL statement, including string buffers such as select_clause, from_clause, where_clause, group_by_clause, order_by_clause, and limit_clause.

[0033] When compiling the SELECT clause, the SA-IR select array is traversed, and each SelectItem is processed: if the agg attribute is not null, an aggregate expression in the format "AGG_FUNCTION(table.column)" is generated; if the alias attribute is not null, "AS alias_name" is added; multiple selections are separated by commas. For example, {"column":"orders.amount", "agg":"SUM", "alias":"total_amount"} will be compiled into "SUM(orders.amount) AS total_amount".

[0034] Compiling the FROM and JOIN clauses is the core of the entire compilation process, fully demonstrating the advantages of the from_join_path design. The compiler retrieves the first JoinNode from the from_join_path list and generates "FROM table_name" as the FROM clause. Then, it iterates from the second JoinNode onwards, generating "LEFT JOIN table_name ON join_condition" for each node. This linear design allows even complex multi-table joins to be compiled using a simple loop. For example, from_join_path [{"table": "users"}, {"table": "orders", "on": "users.id = orders.user_id"}, {"table": "products", "on": "orders.product_id = products.id"}] will be compiled into "FROM users LEFT JOIN orders ON users.id = orders.user_id LEFT JOIN products ON orders.product_id = products.id".

[0035] Compiling the WHERE clause requires recursively processing the tree structure of the WHERE field in SA-IR. For SimpleCondition, it directly generates a comparison expression in the format "column operator value," where the type of value determines whether to add quotes (for string types) or other formatting (for date types, list types, etc.). For WhereClause, it recursively compiles each element in its condition array, then connects them with the logical operator (AND or OR) specified by the logic attribute, and encloses them in parentheses to maintain the correct precedence.

[0036] Compiling the GROUP BY, ORDER BY, and LIMIT clauses is relatively simple. The GROUP BY clause is generated by concatenating all column names in the group_by array; the ORDER BY clause iterates through the order_by array and generates "column_name ASC / DESC" for each OrderByItem; the LIMIT clause directly uses the value of the limit field.

[0037] Finally, the compiler concatenates all clauses according to the standard SQL syntax order: SELECT ... from ... where ... group by ... ordering method ... limit ..., generating the final SQL query string. The system also implements compiler adapters for different SQL dialects, enabling the generation of SQL statements for various database systems such as PostgreSQL, MySQL, SQL Server, and Oracle by configuring different function templates, keyword mappings, and syntax rules.

[0038] Example 1 To better illustrate the implementation effects of the present invention, a complete embodiment will be described in detail below. Assume there is an e-commerce database containing a users table (fields: id, name, city, level, create_time), an orders table (fields: id, user_id, product_id, amount, status, create_time), and a products table (fields: id, name, category, price).

[0039] The user inputs a natural language query: "Query the names and total order amount of VIP users in Shanghai who placed orders last month".

[0040] The system first performs intent and entity parsing. The LLM identifies this as an aggregate query, requiring the selection of "user name" and "total order amount" (which needs to be summed). The filtering conditions include "Shanghai region", "last month", "VIP user" and the implicit "order placed" status.

[0041] During the entity linking phase, the system links "User Name" to users.name, "Order Amount" to orders.amount, "Shanghai Region" to users.city, "VIP User" to users.level, and "Last Month" requires filtering by date range for orders.create_time.

[0042] The join path discovery algorithm identifies the two tables that need to be joined, users and orders. It finds the foreign key relationship between users.id and orders.user_id in the schema-KG and generates the path [users, orders].

[0043] The SA-IR generated by the system is as follows: { "select": [ {"column": "db1.users.name", "agg": null, "alias": "user_name"}, {"column": "db1.orders.amount", "agg": "SUM", "alias": "total_amount"} , "from_join_path": {"table": "db1.users", "on": null}, {"table": "db1.orders", "on": "users.id = orders.user_id"} , "where": { "logic": "AND", "conditions": {"column": "db1.users.city", "op": "=", "value": "Shanghai"}, {"column": "db1.users.level", "op": "=", "value": "VIP"}, {"column": "db1.orders.create_time", "op": "BETWEEN", "value": ["2024-01-01", "2024-01-31"]}, {"column": "db1.orders.status", "op": "!=", "value": "cancelled"} }, "group_by": ["db1.users.name"], "order_by": [{"column": "total_amount", "direction": "DESC"}], "limit": null } The compiler compiles this SA-IR into the final SQL: ​SELECT users.name AS user_name, SUM(orders.amount) AS total_amount FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE users.city = 'Shanghai' AND users.level = 'VIP' AND orders.create_time BETWEEN '2024-01-01' AND '2024-01-31' AND orders.status != 'cancelled' GROUP BY users.name ORDER BY total_amount DESC As can be seen from this embodiment, the present invention successfully converts complex natural language queries into accurate and executable SQL statements through the intermediate representation SA-IR. The entire process is interpretable, debuggable, and adaptable to different database dialects.

[0044] 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.

[0045] 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 schema intermediate generation and compilation method for structured query generation, characterized in that, The method comprises the following steps: constructing a schema knowledge graph Schema-KG, which contains identifier information of all databases, tables and columns of a target database and foreign key relationship information between tables; converting an input natural language query into a schema-aware intermediate representation SA-IR, which is a tree structure in JSON format and contains a select field, a from_join_path field, a where field, a group_by field, an order_by field and a limit field, wherein the select field is an array of SelectItem objects, each SelectItem object contains a column attribute, an agg attribute and an alias attribute, and the value of the column attribute is a three-part identifier in the schema.table.column format; the from_join_path field is an ordered list of JoinNode objects, each JoinNode object contains a table attribute and an on attribute, and the value of the table attribute is an identifier in the schema.table format; the where field is a recursively defined tree structure and contains a logic attribute and a conditions array; compiling the SA-IR into a SQL query statement of the target database, and the compiling process comprises generating a SELECT clause by traversing the select field, generating a FROM clause and a JOIN clause by traversing the from_join_path field, recursively processing the where field to generate a WHERE clause, and processing the group_by, order_by and limit fields to generate corresponding SQL clauses.

2. The method of claim 1, wherein, The step of converting the input natural language query into the schema-aware intermediate representation SA-IR comprises: performing intent and entity parsing on the natural language query using a large language model to identify query intent and semantic entities; performing entity linking on the identified semantic entities and database objects in the Schema-KG, and each semantic entity is linked to a GroundedColumn in the schema.table.column format or a GroundedTable in the schema.table format; collecting all linked tables to form a target table set, performing a graph search algorithm on the Schema-KG to calculate an optimal path connecting all tables in the target table set; converting the optimal path into an ordered list of JoinNode objects in the from_join_path field, and the on attribute of the first JoinNode in the list is null, and the on attributes of subsequent JoinNode objects contain connection conditions with previous tables; assembling the linked entities, the converted path and the identified filtering conditions into a complete SA-IR instance.

3. The method of claim 2, wherein, The graph search algorithm employs an improved Dijkstra's shortest path algorithm. The path cost function comprehensively considers path length, connection type, table size, and index existence. The algorithm maintains a priority queue and expands the search from each table in the target table set until it finds the minimum cost path connecting all target tables.

4. The method of claim 1, wherein, In the tree structure of the where field, the root node is a WhereClause object, which contains a logic attribute with a value of AND or OR and a conditions array. Each element of the conditions array is a SimpleCondition object or a WhereClause object. The SimpleCondition object contains a column attribute, an op attribute, and a value attribute, where the value of the column attribute must be a GroundedColumn that exists in the Schema-KG.

5. The method of claim 1, wherein, When compiling the from_join_path field, the table attribute of the first JoinNode object in the list is taken out to generate the FROM clause. Starting from the second JoinNode object, the list is traversed sequentially. For each JoinNode object, a join clause in the format "LEFT JOIN table_name ON join_condition" is generated, where table_name comes from the table attribute and join_condition comes from the on attribute.

6. The method of claim 1, wherein, The agg property of the SelectItem object is an enumeration of aggregate functions, including SUM, AVG, COUNT, MAX, and MIN. When the agg property is not null, the compilation generates an aggregate expression in the format "AGG_FUNCTION(table.column)"; when the alias property is not null, "ASalias_name" is added to generate a column alias.

7. The method of claim 1, wherein, The method also includes performing integrity verification on the generated SA-IR. The verification includes checking that the GroundedColumn corresponding to all column attribute values ​​exists in the Schema-KG and that the GroundedTable corresponding to all table attribute values ​​exists in the Schema-KG, ensuring that the SA-IR structure conforms to the predefined syntax specifications.

8. The method of claim 1, wherein, The compilation process supports multiple SQL dialects. By configuring different function templates, keyword mappings, and syntax rules, the same SA-IR instance can be compiled into SQL query statements for PostgreSQL, MySQL, SQL Server, or Oracle database systems.

Images