Dual-engine-based sql generation method, system, device and medium

Abstract

The application relates to the technical field of data processing, and specifically provides a SQL generation method, system, device and medium based on a double engine, which comprises the following steps: receiving a natural language query input by a user; using an intention understanding engine to analyze the natural language query in combination with a dialogue context and a business knowledge graph, and generating a structured query intention representation; using a SQL generation and optimization engine to generate at least one candidate SQL statement based on the structured query intention representation and a vectorized database Schema library; performing plan analysis on the at least one candidate SQL statement, and performing performance evaluation and optimization rewriting based on the analysis result to generate a final high-performance SQL statement; and executing the final high-performance SQL statement and returning a query result. The application greatly improves the query success rate and user experience in a complex scenario.

Description

Technical Field

[0001] This invention belongs to the field of data processing technology, specifically relating to a method, system, device, and medium for generating SQL based on a dual-engine architecture. Background Technology

[0002] In today's data-driven decision-making environment, Structured Query Language (SQL) is a core tool for accessing and analyzing databases. However, its complex syntax and specialized database structure requirements pose a high barrier to entry for business users without a technical background. Existing natural language to SQL technologies suffer from the following limitations: template-based methods lack semantic flexibility and struggle to understand complex business query intents; deep learning-based methods, trained on general corpora, perform poorly when faced with domain-specific business terminology and generally suffer from semantic comprehension biases. More seriously, existing technologies focus solely on generating syntactically correct SQL, completely neglecting query execution performance, often leading to performance issues such as full table scans and inefficient joins, severely impacting query efficiency in big data scenarios. Furthermore, when user expressions are ambiguous, the system lacks an effective interactive clarification mechanism, resulting in a high query failure rate. Therefore, there is an urgent need in this field for an intelligent solution that can deeply understand user intent, automatically generate high-performance SQL, and possess domain-adaptive capabilities. Summary of the Invention

[0003] In view of the above-mentioned shortcomings of the prior art, the present invention provides a SQL generation method, system, device and medium based on dual engines to solve the above-mentioned technical problems.

[0004] In a first aspect, the present invention provides a SQL generation method based on a dual-engine architecture, comprising: Receive natural language queries input by the user; By using an intent understanding engine, combined with dialogue context and business knowledge graph, the natural language query is parsed to generate a structured query intent representation; Using the SQL generation and optimization engine, at least one candidate SQL statement is generated based on the structured query intent representation and vectorized database schema library; Execution plan analysis is performed on the at least one candidate SQL statement, and performance evaluation and optimization rewriting are performed based on the analysis results to generate the final high-performance SQL statement; Execute the final high-performance SQL statement and return the query results.

[0005] In an optional implementation, an intent understanding engine is used to parse the natural language query, combining the dialogue context and business knowledge graph, to generate a structured query intent representation, including: It obtains the user's current natural language query, dialogue history information extracted from the context management module, and relevant business entities, attributes, and relationship information retrieved from the business knowledge graph; The large-scale language model is used to perform entity recognition on the current query, identifying the business entities, attribute values, and key information such as time and location mentioned in the query, and the identified entities are semantically disambiguated and standardized in combination with the business knowledge graph. Based on the identified entities and the entity relationships defined in the business knowledge graph, the associations between entities are analyzed to determine the implicit business logic connections in the query. Based on the overall semantic understanding of the query using the large language model, and combined with the identified entities and relationships, the query intent is classified into at least one operation type, including data filtering, aggregation statistics, and sorting display. By integrating the results of named entity recognition, relation extraction, and intent classification, a structured query intent representation is generated, which includes target data fields, filtering conditions and their logical combinations, aggregation function types, sorting fields, and order.

[0006] In an optional implementation, based on the identified entities and the entity relationships defined in the business knowledge graph, the associations between entities are parsed to determine the implicit business logic connections in the query, including: Based on the predefined entity relationship network in the business knowledge graph, an association path is constructed for the identified entities; Calculate the semantic correlation degree between entities in the knowledge graph, and determine the direct or indirect business logic relationship between the entities mentioned in the query based on the association path and semantic correlation degree. When there is no predefined direct relationship between the identified entities, multi-hop path discovery is performed, and implicit business logic connections are deduced through intermediate entity bridging.

[0007] In an optional implementation, an SQL generation and optimization engine is used to generate at least one candidate SQL statement based on the structured query intent representation and vectorized database schema library, including: The key concepts in the structured query intent representation are vectorized and encoded. The vectorized query concepts are matched with the table names, column names, and comments in the vectorized database schema to determine the most relevant database tables and columns. Based on the matching database schema and the query intent representation, construct the SELECT clause, WHERE clause, GROUP BY clause, and ORDER BY clause respectively; Combine the constructed SQL components to generate at least one syntactically correct candidate SQL statement; When the query concept cannot be precisely matched with the database schema, the most similar schema element is recommended based on the similarity matching result, and the corresponding transformation function is automatically embedded to generate the correct SQL logic.

[0008] In an optional implementation, key concepts in the structured query intent representation are vectorized and encoded, including: The target field names, entity values ​​in the filtering conditions, and business terms involved in aggregation and sorting operations in the structured query intent representation are taken as key concepts. The key concepts are encoded into fixed-dimensional vector representations using a pre-trained language model; During the encoding process, synonyms and abbreviations of business terms are standardized to generate semantically consistent vector features.

[0009] In an optional implementation, execution plan analysis is performed on the at least one candidate SQL statement, and performance evaluation and optimization rewriting are performed based on the analysis results to generate the final high-performance SQL statement, including: Obtain the estimated execution plan for each candidate SQL statement using the database's EXPLAIN command or equivalent interface. Based on the estimated execution plan, the cost estimator analyzes whether there are full table scans, index failures, or high-cost join operations, and estimates their execution costs. The estimated execution cost is compared with a preset performance threshold. If the cost is higher than the threshold, an optimization rewrite is triggered. The candidate SQL is rewritten by applying at least one optimization rule through the optimization rewrite module. The optimization rules include: predicate pushdown, replacing the IN clause with the EXISTS clause, adjusting the order of multi-table joins, or recommending the creation of missing indexes. The rewritten SQL statement is re-evaluated and optimized, forming a closed-loop feedback optimization loop until a SQL statement that meets the performance requirements is generated or the preset iteration limit is reached. From all evaluated candidate SQL statements, the statement with the lowest execution cost is selected as the final high-performance SQL statement.

[0010] In an optional implementation, based on the estimated execution plan, a cost estimator analyzes whether there are full table scans, index failures, or high-cost join operations, and estimates their execution costs, including: Analyze the estimated execution plan to identify whether it contains at least one of the following: a full table scan operation, an index failure scenario, or a high-cost join operation; Based on the identified operation type and its cost weight in the execution plan, the estimated execution cost of the candidate SQL statement is calculated using a pre-defined cost model; The high-cost join operations include nested loop joins that do not use indexes or Cartesian product joins that produce a large number of intermediate results.

[0011] Secondly, the present invention provides a dual-engine-based SQL generation system, comprising: The request receiving module is used to receive natural language queries input by the user; The intent understanding module is used to parse the natural language query by utilizing the intent understanding engine, combined with the dialogue context and business knowledge graph, and generate a structured query intent representation. The statement generation module is used to generate at least one candidate SQL statement based on the structured query intent representation and vectorized database schema library using the SQL generation and optimization engine. The statement optimization module is used to perform execution plan analysis on the at least one candidate SQL statement, and to perform performance evaluation and optimization rewriting based on the analysis results to generate the final high-performance SQL statement. The statement execution module is used to execute the final high-performance SQL statement and return the query results.

[0012] Thirdly, a device is provided, comprising: Memory is used to store the SQL generation program based on the dual-engine architecture; A processor is configured to implement the steps of the dual-engine-based SQL generation method provided in the first aspect when executing the dual-engine-based SQL generation program.

[0013] Fourthly, a computer-readable medium is provided, on which a dual-engine-based SQL generation program is stored, wherein when the dual-engine-based SQL generation program is executed by a processor, it implements the steps of the dual-engine-based SQL generation method provided in the first aspect.

[0014] The beneficial effects of this invention are as follows: the SQL generation method, system, device, and medium based on dual engines provided by this invention significantly improve the semantic parsing accuracy of complex queries by integrating business knowledge graphs with context-aware deep intent understanding; the introduction of an execution plan-aware optimization feedback mechanism fundamentally solves the pain point of "syntactically correct but inefficient execution" in natural language to SQL technology; the adaptive mechanism based on vectorized schema matching effectively reduces the migration cost of the system in different business domains; at the same time, the interactive clarification function actively guides users to clarify their needs, greatly improving the query success rate and user experience in complex scenarios, realizing the transformation from a passive tool to an intelligent assistant. Attached Figure Description

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

[0016] Figure 1 This is a schematic flowchart of a method according to an embodiment of the present invention.

[0017] Figure 2 This is a schematic block diagram of a system according to an embodiment of the present invention.

[0018] Figure 3 This is a schematic diagram of the structure of a device provided in an embodiment of the present invention. Detailed Implementation

[0019] To enable those skilled in the art to better understand the technical solutions of this invention, the technical solutions of the embodiments of this invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this invention, and not all embodiments. Based on the embodiments of this invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this invention.

[0020] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention pertains. The terminology used herein in the description of the invention is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention.

[0021] The SQL generation method based on dual engines provided in this embodiment of the invention is executed by a computer device, and correspondingly, the SQL generation system based on dual engines runs on the computer device.

[0022] Figure 1 This is a schematic flowchart illustrating a method according to an embodiment of the present invention. Wherein, Figure 1 The executing entity can be a dual-engine-based SQL generation system. Depending on different requirements, the order of steps in this flowchart can be changed, and some can be omitted.

[0023] like Figure 1 As shown, the method includes: S1. Receive natural language queries input by the user; S2. Using an intent understanding engine, combined with the dialogue context and business knowledge graph, the natural language query is parsed to generate a structured query intent representation; S3. Using the SQL generation and optimization engine, based on the structured query intent representation and vectorized database schema library, generate at least one candidate SQL statement; S4. Perform execution plan analysis on the at least one candidate SQL statement, and perform performance evaluation and optimization rewriting based on the analysis results to generate the final high-performance SQL statement; S5. Execute the final high-performance SQL statement and return the query results.

[0024] In one embodiment of the present invention, based on step S1, the following will provide a possible embodiment and describe its specific implementation in a non-limiting manner.

[0025] Provide at least one of the following input methods: Text input interface: Provides a query text box to receive natural language query text entered by the user; Voice input interface: integrates a voice recognition engine to convert users' voice queries into text in real time; File upload interface: Supports uploading text files containing batch query requirements; Conversational interactive interface: Receives continuous query input from the user in a continuous conversational session.

[0026] After receiving query input, the user interface module preprocesses the input, including text cleaning, special character filtering, and encoding standardization. Simultaneously, this module binds the received query to the current session identifier and appends metadata such as timestamps and user identifiers, forming a complete query request that is then passed to the subsequent intent understanding engine.

[0027] In one embodiment of the present invention, based on step S2, the following will provide a possible embodiment and describe its specific implementation in a non-limiting manner.

[0028] S201. Information Acquisition Steps: The system receives the user's current natural language query through the system interface and simultaneously extracts dialogue history information (including mentioned time ranges, geographical conditions, business entities, etc.) from the session storage of the context management module. In parallel, it retrieves relevant business entities, attributes, and relationship information from the business knowledge graph (built on graph databases such as Neo4j). The knowledge graph predefines entity types (such as "customer" and "product") and their relationships (such as "purchase" and "belong to") within the domain.

[0029] S202. Entity Recognition and Standardization Steps: Using a large-scale language model (such as BERT or GPT series) fine-tuned with domain corpus, named entity recognition is performed on the current query to identify key information such as business entities (e.g., "sales revenue"), attribute values ​​(e.g., "2023"), time, and location. After recognition, semantic disambiguation (e.g., disambiguating "apple" into "fruit" or "company") and standardization (e.g., standardizing "last year" into a specific date range) are performed in conjunction with the business knowledge graph to ensure that the entity identifiers are consistent with the canonical names in the knowledge graph.

[0030] S203. Relationship Extraction and Path Discovery Steps: Based on the identified entities, utilize the predefined entity relationship network in the business knowledge graph to construct association paths for the entities (e.g., calculate the shortest path using Dijkstra's algorithm). Employ graph embedding techniques (such as TransE) to calculate the semantic relevance between entities, and combine path length and relevance score to determine direct or indirect business logic connections between entities (e.g., a "customer-purchase-product" chain). When there is no direct relationship between entities, initiate multi-hop path discovery (e.g., bridge "customer" and "product" through the intermediate entity "order") to deduce implicit relationships (e.g., "customer indirectly purchases product").

[0031] S204. Intent Classification Steps: Based on the overall semantic encoding of the query using a large language model, and combined with the identified entities and relationships, a softmax classifier is used to classify the query intent into predefined types, including at least one of the following: data filtering (such as WHERE condition queries), aggregation statistics (such as SUM and COUNT operations), and sorting display (such as ORDER BY operations). Knowledge graph features are incorporated into the classification model during training to improve domain accuracy.

[0032] S205. Structured Representation Generation Step: Integrate the outputs of the preceding steps and generate a standardized, structured query intent representation using template filling or sequence generation. This representation uses JSON or XML format and explicitly includes the target data field (e.g., ["sales_amount"]), filter conditions and their logical combinations (e.g., {"field": "date", "operator":">", "value": "2023-01-01"}), aggregate function type (e.g., {"function": "SUM", "field": "sales"}), and sorting field and order (e.g., {"field": "revenue", "order": "DESC"}). This representation serves as an intermediate interface for the SQL generation engine to directly call.

[0033] The entire process is implemented through a modular design, with large language models deployed using the Hugging FaceTransformers library and knowledge graph access achieved through Cypher queries, ensuring high cohesion and low coupling.

[0034] In one embodiment of the present invention, based on step S3, the following will provide a possible embodiment and describe its specific implementation in a non-limiting manner.

[0035] S301. Concept Vectorization Encoding Steps: Extract key concepts from the structured query intent representation (JSON format), including: Target field name (e.g., "monthly sales"); entity values ​​in the filter criteria (e.g., "Beijing region"); business terms involved in aggregation and sorting operations (e.g., "year-on-year growth rate").

[0036] The above concepts are encoded into 384-dimensional vector representations using a pre-trained Sentence-BBERT model. Before encoding, business terms are standardized using a thesaurus (built on WordNet) and a domain-specific dictionary. For example, "revenue" is standardized to "income" and "YTD" is expanded to "year-to-date" to ensure semantically consistent vector features are generated.

[0037] S302. Schema Vector Matching Steps: The vectorized query concept is matched against a pre-built vectorized schema library for similarity. The schema library is stored in the FAISS vector database and contains vectorized representations of all table names, column names, and annotations in the database. A cosine similarity algorithm is used to calculate the similarity between the query concept and schema elements. A similarity threshold (e.g., 0.75) is set, and only matching results exceeding the threshold are retained. For composite concepts such as "monthly sales," multi-column matching is supported (e.g., matching both the "month" and "sales" columns simultaneously).

[0038] S303. SQL Component Construction Steps: Based on the matching results and query intent representation, construct the SQL component according to the following rules: The SELECT clause maps the target field to a specific database column and automatically adds the necessary table aliases. The WHERE clause converts the filtering conditions into SQL predicates, preserving the original logical combination (AND / OR). The GROUP BY clause: determines the grouping columns and validates their validity based on aggregation requirements; The ORDER BY clause maps the sorting field, supporting multi-column sorting and specifying the sorting direction.

[0039] S304. Candidate Statement Generation and Adaptation Steps: Use the Apache Calcite SQL parser to combine the constructed SQL components into syntactically correct candidate SQL statements. When the query concept and schema do not match exactly: Recommend the most similar schema elements based on similarity matching results (e.g., map "customer age" to the "age" column); Automatically embed the corresponding conversion functions (such as automatically adding the DATE_FORMAT() function to date-related queries); It supports generating multiple candidate statements (such as different JOIN paths or WHERE condition orders).

[0040] The entire process is protected by both a template engine and syntax validation, ensuring that the generated candidate SQL not only conforms to the syntax rules but also remains true to the original query intent.

[0041] In one embodiment of the present invention, based on step S4, the following will provide a possible embodiment and describe its specific implementation in a non-limiting manner.

[0042] S401. Execution Plan Acquisition Steps: Connect to the target database via JDBC and execute the EXPLAIN ANALYZE command (in a MySQL environment) or an equivalent query plan retrieval interface (such as EXPLAIN (ANALYZE, BUFFERS) for each candidate SQL statement. The system captures the complete execution plan tree returned by the database optimizer, including the type of each operation node, estimated number of rows, cost estimate, and index usage, and parses it into structured JSON data for subsequent analysis.

[0043] S402. Performance Analysis and Cost Assessment Steps: The execution plan analyzer traverses the execution plan tree to identify performance bottleneck patterns: Full table scan: Detects Seq Scan or Table Access Full operations; Index invalidation: Identify column conditions that have indexes but are not in use (by comparing WHERE conditions with available indexes); High-cost joins: marked Nested Loop Joins that do not use indexes and HashJoins that generate more than the threshold number of rows.

[0044] The cost estimator calculates the execution cost based on a pre-defined weighted model: Full table scan cost = Number of table records × Full table scan weight factor (0.1) Index failure cost = Number of failed indexes × Index failure weight factor (0.3) Connection cost = Σ(Number of output rows × Connection type weight) The cost model is calibrated using historical query data to ensure the accuracy of the estimates.

[0045] S403. Optimization Trigger Judgment Steps: Compare the calculated execution cost with a preset performance threshold (configurable, default value is 1000 cost units). When the cost exceeds the threshold, the optimization rewrite process is triggered immediately. Simultaneously, the system records the trigger reason (e.g., "Full table scan detected") for subsequent optimization decisions.

[0046] S404. Intelligent SQL Rewriting Steps: The optimization rewriting module applies targeted optimization rules based on detected performance issues. Predicate pushdown: Pushes the WHERE condition down to the subquery or JOIN condition; Subquery optimization: Convert IN subqueries into EXISTS subqueries or JOIN operations; Join order adjustment: Selectively rearrange the JOIN order based on table size and filter conditions; Index suggestion: When a missing index is identified, generate and record a CREATE INDEX suggestion statement.

[0047] The rewriting process uses the Apache Calcite optimizer framework to ensure grammatical correctness.

[0048] S405. Iterative Optimization Control Steps: The rewritten SQL immediately enters a new round of performance evaluation (S401-S404), forming a closed-loop feedback. The system sets a maximum number of iterations (default 5) to prevent infinite loops. Cost improvement is recorded in each iteration, and the system terminates prematurely if the cost decrease is less than 5% after two consecutive iterations.

[0049] S406. Final Statement Selection Steps: Select the statement with the lowest execution cost from all candidate SQL statements (including the original statement and the optimized version) as the final output. Simultaneously, generate an optimization report, including cost comparisons, applied optimization rules, and the percentage performance improvement for user reference.

[0050] In one embodiment of the present invention, based on step S5, a possible embodiment will be given below, and its specific implementation will be described in a non-limiting manner.

[0051] Reliable connections are obtained through a verified database connection pool (such as HikariCP), and the final selected high-performance SQL statement is executed using PreparedStatement, effectively preventing SQL injection attacks. The system sets an execution timeout threshold (default 30 seconds) using the JDBC setQueryTimeout() method to avoid long-running queries burdening the database.

[0052] After successful execution, the query results are retrieved row by row through the ResultSet object and processed according to the following strategy: Result set size evaluation: For small result sets (<1000 rows), return the entire set directly; for large result sets, enable pagination, returning 100 records per page by default. Data type conversion: Convert raw database types (such as Timestamp, Decimal) to JSON-friendly formats (ISO8601 strings, Float numbers); Metadata extraction: Simultaneously obtain metadata information such as column names, data types, and number of rows in the result set.

[0053] In some embodiments, the dual-engine-based SQL generation system may include multiple functional modules composed of computer program segments. The computer programs for each program segment in the dual-engine-based SQL generation system may be stored in the memory of a computer device and executed by at least one processor to perform (see details). Figure 1 (Description) Features based on dual-engine SQL generation.

[0054] In this embodiment, the dual-engine-based SQL generation system can be divided into multiple functional modules according to the functions it performs, such as... Figure 2 As shown. The module referred to in this invention is a series of computer program segments that can be executed by at least one processor and perform a fixed function, and is stored in memory. In this embodiment, the functions of each module will be described in detail in subsequent embodiments.

[0055] The request receiving module is used to receive natural language queries input by the user; The intent understanding module is used to parse the natural language query by utilizing the intent understanding engine, combined with the dialogue context and business knowledge graph, and generate a structured query intent representation. The statement generation module is used to generate at least one candidate SQL statement based on the structured query intent representation and vectorized database schema library using the SQL generation and optimization engine. The statement optimization module is used to perform execution plan analysis on the at least one candidate SQL statement, and to perform performance evaluation and optimization rewriting based on the analysis results to generate the final high-performance SQL statement. The statement execution module is used to execute the final high-performance SQL statement and return the query results.

[0056] Figure 3 The dual-engine-based SQL generation method provided in this application embodiment can be applied to devices. Those skilled in the art will understand that the device structure involved in the embodiments of this invention does not constitute a limitation on the device. A device may include more or fewer components than illustrated, or combine certain components, or have different component arrangements. In the embodiments of this invention, the device includes, but is not limited to, laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The device may also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices, and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the embodiments of this application described and / or claimed herein.

[0057] The device 300 may include a processor 310, a memory 320, and a communication unit 330. These components communicate via one or more buses. Those skilled in the art will understand that the server structure shown in the figure does not constitute a limitation of the present invention. It may be a bus topology or a star topology, and may include more or fewer components than shown, or combine certain components, or have different component arrangements.

[0058] The memory 320 can be used to store execution instructions of the processor 310. The memory 320 can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic storage, flash memory, magnetic disk, or optical disk. When the execution instructions in the memory 320 are executed by the processor 310, the device 300 is able to perform some or all of the steps in the above method embodiments.

[0059] The processor 310 serves as the control center of the storage device, connecting various parts of the electronic device via various interfaces and lines. It executes software programs and / or modules stored in the memory 320, and calls data stored in the memory to perform various functions of the electronic device and / or process data. The processor can be composed of integrated circuits (ICs), such as a single packaged IC or multiple packaged ICs with the same or different functions connected together. For example, the processor 310 may consist only of a central processing unit (CPU). In this embodiment of the invention, the CPU may have a single processing core or include multiple processing cores.

[0060] The communication unit 330 is used to establish a communication channel, enabling the storage device to communicate with other devices. It can receive user data sent by other devices or send user data to other devices.

[0061] The present invention also provides a computer medium, wherein the computer medium may store a program, which, when executed, may include some or all of the steps provided in the embodiments of the present invention. The medium may be a magnetic disk, an optical disk, read-only memory (ROM), or random access memory (RAM), etc.

[0062] Those skilled in the art will clearly understand that the techniques in the embodiments of the present invention can be implemented using software plus necessary general-purpose hardware platforms. Based on this understanding, the technical solutions in the embodiments of the present invention, or the parts that contribute to the prior art, can be embodied in the form of a software product. This computer software product is stored in a medium such as a USB flash drive, a portable hard drive, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk, or any other medium capable of storing program code. It includes several instructions to cause a computer device (which may be a personal computer, a server, or a second device, network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention.

[0063] The same or similar parts between the various embodiments in this specification can be referred to mutually. In particular, the device embodiments are basically similar to the method embodiments, so the description is relatively simple, and the relevant parts can be referred to the description in the method embodiments.

[0064] In the embodiments provided by this invention, it should be understood that the disclosed systems and methods can be implemented in other ways. For example, the system embodiments described above are merely illustrative; for instance, the division of modules is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple modules or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between systems or modules may be electrical, mechanical, or other forms.

[0065] The modules described as separate components may or may not be physically separate. The components shown as modules may or may not be physical modules; that is, they may be located in one place or distributed across multiple network modules. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0066] In addition, the functional modules in the various embodiments of the present invention can be integrated into one processing module, or each module can exist physically separately, or two or more modules can be integrated into one module.

[0067] Although the present invention has been described in detail with reference to the accompanying drawings and preferred embodiments, the present invention is not limited thereto. Various equivalent modifications or substitutions can be made to the embodiments of the present invention by those skilled in the art without departing from the spirit and essence of the invention, and such modifications or substitutions should all be within the scope of the present invention. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should also be covered within the protection scope of the present invention.

Claims

1. A SQL generation method based on a dual-engine architecture, characterized in that, include: Receive natural language queries input by the user; By using an intent understanding engine, combined with dialogue context and business knowledge graph, the natural language query is parsed to generate a structured query intent representation; Using the SQL generation and optimization engine, at least one candidate SQL statement is generated based on the structured query intent representation and vectorized database schema library; Execution plan analysis is performed on the at least one candidate SQL statement, and performance evaluation and optimization rewriting are performed based on the analysis results to generate the final high-performance SQL statement; Execute the final high-performance SQL statement and return the query results; Using the SQL generation and optimization engine, based on the structured query intent representation and vectorized database schema library, at least one candidate SQL statement is generated, including: The key concepts in the structured query intent representation are vectorized and encoded. The vectorized query concepts are matched with the table names, column names, and comments in the vectorized database schema to determine the most relevant database tables and columns. Based on the matching database schema and the query intent representation, construct the SELECT clause, WHERE clause, GROUP BY clause, and ORDER BY clause respectively; Combine the constructed SQL components to generate at least one syntactically correct candidate SQL statement; When the query concept cannot be precisely matched with the database schema, the most similar schema element is recommended based on the similarity matching result, and the corresponding conversion function is automatically embedded to generate the correct SQL logic. The key concepts in the structured query intent representation are vectorized and encoded, including: The target field names, entity values ​​in the filtering conditions, and business terms involved in aggregation and sorting operations in the structured query intent representation are taken as key concepts. The key concepts are encoded into fixed-dimensional vector representations using a pre-trained language model; During the encoding process, synonyms and abbreviations of business terms are standardized to generate semantically consistent vector features; Execution plan analysis is performed on the at least one candidate SQL statement, and performance evaluation and optimization rewriting are performed based on the analysis results to generate the final high-performance SQL statement, including: Obtain the estimated execution plan for each candidate SQL statement using the database's EXPLAIN command or equivalent interface. Based on the estimated execution plan, the cost estimator analyzes whether there are full table scans, index failures, or high-cost join operations, and estimates their execution costs. The estimated execution cost is compared with a preset performance threshold. If the cost is higher than the threshold, an optimization rewrite is triggered. The candidate SQL is rewritten by applying at least one optimization rule through the optimization rewrite module. The optimization rules include: predicate pushdown, replacing the IN clause with the EXISTS clause, adjusting the order of multi-table joins, or recommending the creation of missing indexes. The rewritten SQL statement is re-evaluated and optimized, forming a closed-loop feedback optimization loop until a SQL statement that meets the performance requirements is generated or the preset iteration limit is reached. From all evaluated candidate SQL statements, the statement with the lowest execution cost is selected as the final high-performance SQL statement.

2. The method according to claim 1, characterized in that, Using an intent understanding engine, combined with dialogue context and business knowledge graph, the natural language query is parsed to generate a structured query intent representation, including: It obtains the user's current natural language query, dialogue history information extracted from the context management module, and relevant business entities, attributes, and relationship information retrieved from the business knowledge graph; A large language model is used to perform entity recognition on the current query, identifying the business entities, attribute values, and key information such as time and location mentioned in the query. The identified entities are then semantically disambiguated and standardized in conjunction with the business knowledge graph. Based on the identified entities and the entity relationships defined in the business knowledge graph, the associations between entities are analyzed to determine the implicit business logic connections in the query. Based on the overall semantic understanding of the query using the large language model, and combined with the identified entities and relationships, the query intent is classified into at least one operation type, including data filtering, aggregation statistics, and sorting display. By integrating the results of named entity recognition, relation extraction, and intent classification, a structured query intent representation is generated, which includes target data fields, filtering conditions and their logical combinations, aggregation function types, sorting fields, and order.

3. The method according to claim 2, characterized in that, Based on the identified entities and the entity relationships defined in the business knowledge graph, the associations between entities are parsed to determine the implicit business logic connections in the query, including: Based on the predefined entity relationship network in the business knowledge graph, an association path is constructed for the identified entities; Calculate the semantic correlation degree between entities in the knowledge graph, and determine the direct or indirect business logic relationship between the entities mentioned in the query based on the association path and semantic correlation degree. When there is no predefined direct relationship between the identified entities, multi-hop path discovery is performed, and implicit business logic connections are deduced through intermediate entity bridging.

4. The method according to claim 1, characterized in that, Based on the estimated execution plan, a cost estimator is used to analyze whether there are full table scans, index failures, or high-cost join operations, and to estimate their execution costs, including: Analyze the estimated execution plan to identify whether it contains at least one of the following: a full table scan operation, an index failure scenario, or a high-cost join operation; Based on the identified operation type and its cost weight in the execution plan, the estimated execution cost of the candidate SQL statement is calculated using a pre-defined cost model; The high-cost join operations include nested loop joins that do not use indexes or Cartesian product joins that produce a large number of intermediate results.

5. A SQL generation system based on a dual-engine architecture, characterized in that, include: The request receiving module is used to receive natural language queries input by the user; The intent understanding module is used to parse the natural language query by utilizing the intent understanding engine, combined with the dialogue context and business knowledge graph, and generate a structured query intent representation. The statement generation module is used to generate at least one candidate SQL statement based on the structured query intent representation and vectorized database schema library using the SQL generation and optimization engine. The statement optimization module is used to perform execution plan analysis on the at least one candidate SQL statement, and to perform performance evaluation and optimization rewriting based on the analysis results to generate the final high-performance SQL statement. The statement execution module is used to execute the final high-performance SQL statement and return the query results; Using the SQL generation and optimization engine, based on the structured query intent representation and vectorized database schema library, at least one candidate SQL statement is generated, including: The key concepts in the structured query intent representation are vectorized and encoded. The vectorized query concepts are matched with the table names, column names, and comments in the vectorized database schema to determine the most relevant database tables and columns. Based on the matching database schema and the query intent representation, construct the SELECT clause, WHERE clause, GROUP BY clause, and ORDER BY clause respectively; Combine the constructed SQL components to generate at least one syntactically correct candidate SQL statement; When the query concept cannot be precisely matched with the database schema, the most similar schema element is recommended based on the similarity matching result, and the corresponding conversion function is automatically embedded to generate the correct SQL logic. The key concepts in the structured query intent representation are vectorized and encoded, including: The target field names, entity values ​​in the filtering conditions, and business terms involved in aggregation and sorting operations in the structured query intent representation are taken as key concepts. The key concepts are encoded into fixed-dimensional vector representations using a pre-trained language model; During the encoding process, synonyms and abbreviations of business terms are standardized to generate semantically consistent vector features; Execution plan analysis is performed on the at least one candidate SQL statement, and performance evaluation and optimization rewriting are performed based on the analysis results to generate the final high-performance SQL statement, including: Obtain the estimated execution plan for each candidate SQL statement using the database's EXPLAIN command or equivalent interface. Based on the estimated execution plan, the cost estimator analyzes whether there are full table scans, index failures, or high-cost join operations, and estimates their execution costs. The estimated execution cost is compared with a preset performance threshold. If the cost is higher than the threshold, an optimization rewrite is triggered. The candidate SQL is rewritten by applying at least one optimization rule through the optimization rewrite module. The optimization rules include: predicate pushdown, replacing the IN clause with the EXISTS clause, adjusting the order of multi-table joins, or recommending the creation of missing indexes. The rewritten SQL statement is re-evaluated and optimized, forming a closed-loop feedback optimization loop until a SQL statement that meets the performance requirements is generated or the preset iteration limit is reached. From all evaluated candidate SQL statements, the statement with the lowest execution cost is selected as the final high-performance SQL statement.

6. A SQL generation device based on a dual-engine architecture, characterized in that, include: Memory is used to store the SQL generation program based on the dual-engine architecture; A processor, configured to implement the steps of the dual-engine-based SQL generation method as described in any one of claims 1-4 when executing the dual-engine-based SQL generation program.

7. A computer-readable medium storing a computer program, characterized in that, The readable medium stores a dual-engine-based SQL generation program, which, when executed by a processor, implements the steps of the dual-engine-based SQL generation method as described in any one of claims 1-4.

Images