Intelligent NL2SQL generation and optimization system, application method and storage medium

Abstract

The invention discloses an intelligent NL2SQL generation and optimization system, an application method and a storage medium, and belongs to the crossing field of natural language processing and database query technology.According to the system, four core components including a dynamic SQL RAG cache module, an intention-Schema double-channel alignment module, an incremental TableSchema sensing module and a multi-table index guide JOIN optimization module are used for optimizing the NL2SQL generation and optimization system; the defects of the prior art in the aspects of high-frequency query efficiency, fuzzy intention analysis, dynamic Schema adaptation, multi-table JOIN performance and SQL credibility are overcome. The method has the beneficial effects that the query efficiency and the resource utilization rate are improved, the accuracy and the interaction efficiency of fuzzy intention analysis are enhanced, the dynamic adaptation of industrial level bins is realized, and the method is particularly suitable for large-model asset query in an enterprise-level operation scene.

Description

Technical Field

[0001] The present invention relates to the cross - field of natural language processing and database query technology, and specifically relates to an intelligent conversion from natural language to structured query language (NL2SQL) for enterprise - level data warehouses, especially applicable to task reasoning based on large models, dynamic table structure perception, hybrid enhanced retrieval (RAG) technology, and automated generation and error prevention of multi - table joint queries in an operation scenario. Background Art

[0002] Natural Language to SQL (NL2SQL) is a technology that automatically converts natural language questions into database query language (SQL). Existing large - model NL2SQL technologies mainly rely on the SQL programming ability of the large model itself, and the construction of surrounding auxiliary modules is relatively weak. When directly running in the business operation scenario, the following problems are prominent: 1. Using an end - to - end model to generate SQL in real - time without caching optimization for high - frequency queries, resulting in waste of computing resources when repeatedly generating similar queries.

[0003] 2. The existing intent recognition module is independent of table structure analysis, resulting in the inability to quickly associate available fields when users make fuzzy queries, and multiple rounds of interaction are required for clarification.

[0004] 3. Traditional NL2SQL systems rely on manually pre - setting the data warehouse table structure (Table Schema) in advance. When the data warehouse table structure changes (such as adding fields, adjusting table relationships), it is necessary to re - adjust the model prompt words or manually update the configuration, resulting in service interruption.

[0005] 4. In the generation of multi - table JOINs, the existing methods do not effectively model foreign key relationships and index strategies, resulting in incorrect JOIN paths or missing association conditions (such as not recognizing orders.user_id = users.id).

[0006] Therefore, the existing NL2SQL technologies still have significant technical gaps in the actual business scenario, especially in terms of adaptability to dynamic environments and execution efficiency, and urgent breakthroughs are needed. Summary of the Invention

[0007] The purpose of the present invention is to overcome the technical problems existing in the prior art, and provide an intelligent NL2SQL generation and optimization system, application method, and storage medium, which are adapted to the interrogation of large - model assets in the production - level operation scenario.

[0008] The purpose of the present invention is achieved through the following technical solutions: In the first aspect, an intelligent NL2SQL generation and optimization system is provided, including: Dynamic SQL RAG Cache Module, including an offline-online collaborative architecture and a dynamic update policy unit. The offline-online collaborative architecture is used to achieve high-frequency query pattern mining and parameterized template generation through the offline-online collaborative architecture, and adopts a vectorized semantic retrieval mechanism to achieve efficient SQL reuse; the dynamic update policy unit is used for heat perception elimination and Schema change linkage; Intent-Schema Dual-channel Alignment Module, including a Schema-aware intent understanding unit and a minimized questioning decision unit. The Schema-aware intent understanding unit is used to achieve dual-channel matching through multi-table Schema semantic vectorization and user question semantic extension; the minimized questioning decision unit is used to achieve interaction optimization based on fuzzy intent detection and dynamic questioning strategies; Incremental Table_Schema Perception Module, including a real-time change capture unit, a dynamic association maintenance unit, and a change response unit. The real-time change capture unit is used to capture table structure change events in real time by docking with the data warehouse metadata service and perform change parsing; the dynamic association maintenance unit is used for relationship graph update and cascade impact processing; the change response unit is used to implement synchronous update and cache cleaning operations; Multi-table Index-guided JOIN Optimization Module, including a foreign key relationship modeling unit, an index optimization mechanism unit, and a JOIN path optimization unit. The foreign key relationship modeling unit is used to construct a structured relationship network based on inter-table foreign key constraints and existing index information; the index optimization mechanism unit is used to evaluate index benefits through cardinality screening and selectivity calculation; the JOIN path optimization unit is used to calculate the optimal execution order through a benefit model.

[0009] In some embodiments, the query pattern mining and parameterized template generation includes: Based on historical query logs, using a large language model to analyze high-frequency problem patterns and abstractly generate parameterized SQL templates; The vectorized semantic retrieval mechanism includes: Encoding the problem pattern and the corresponding SQL template into a joint semantic vector and constructing a vector index library.

[0010] In some embodiments, the heat perception elimination includes: Based on a sliding window to statistically query frequencies, dynamically adjust the priorities of SQL templates, and demote and eliminate low-frequency SQL templates; The Schema change linkage includes: By listening to database DDL events, establishing a table-template dependency relationship graph, and when a table structure change is detected, immediately invalidate the associated templates.

[0011] In some embodiments, the dual-channel matching implemented through multi-table Schema semantic vectorization and user question semantic extension includes: Encoding the Schema information of all tables into vector representations; Using a large language model to perform intent parsing and synonym expansion on the original question; Simultaneously calculating the semantic similarity between the question and the Schema information, and obtaining the Top-K candidate tables through weighting.

[0012] In some embodiments, the fuzzy intent detection includes: Calculating a fuzzy coefficient based on Schema coverage rate, field matching degree, and semantic confidence level.

[0013] In some embodiments, the change parsing includes: Identifying field addition, deletion, and changes in foreign key relationships; The relationship graph update includes: Constructing a global view based on the table foreign key dependency relationship. When field changes are detected, automatically updating the associated table node attributes and adjusting the inter-table association edges in real time; The cascading impact processing includes: Automatically marking invalid association conditions; The implementation of synchronous update and cache cleaning operations includes: Triggering Schema metadata reconstruction, relationship graph reconstruction, and clearing the affected dynamic SQL cache.

[0014] In some embodiments, the evaluation of index benefits through cardinality filtering and selective calculation includes: Prioritizing high-cardinality index fields, filtering indexes with a high filtering ratio. The calculation formula for index benefits is as follows: .

[0015] In a second aspect, an application method of an intelligent NL2SQL generation and optimization system is provided, including: According to the question input by the user, the dynamic SQL RAG cache module determines whether a cached template is matched. If so, returns the cached SQL, executes the SQL, and returns the result; otherwise, sequentially executes the intent-Schema dual-channel alignment module, the incremental Table_Schema perception module, and the multi-table index-guided JOIN optimization module, executes the optimized SQL, and returns the result.

[0016] In a third aspect, a computer-readable storage medium is provided. The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the application method described in the second aspect is implemented.

[0017] It should be further noted that the technical features corresponding to the above embodiments can be combined or replaced with each other without conflict to form a new technical solution.

[0018] Compared with the prior art, the beneficial effects of the present invention are as follows: 1. Optimize the processing efficiency of high-frequency queries and resource utilization rate: By parameterizing and caching high-frequency query parameters through a dynamic caching mechanism, the number of repeated model inferences is reduced, the consumption of computing resources and response latency are decreased, and the throughput capacity of high-concurrency queries in the operation scenario is improved.

[0019] 2. Improve the accurate parsing ability of fuzzy intents: Through the dual-channel alignment of intent-Schema and the minimization of follow-up questioning strategy, specifically combined with table structure metadata (fields, foreign key relationships) and user intent analysis, the need for multi-round interactions is reduced, and automatic field mapping and semantic disambiguation of fuzzy queries are achieved, which can improve the interaction efficiency of user questions.

[0020] 3. Achieve the dynamic adaptability of industrial-level data warehouses: Monitor the data warehouse metadata in real time. By perceiving changes in the table structure in real time (such as field addition, deletion, and foreign key adjustment), the invalidation of the model caused by Schema updates is avoided, and the manual maintenance cost is reduced.

[0021] 4. Improve the accuracy and execution performance of multi-table joint queries: Optimize the generation of JOIN paths based on the foreign key relationship graph and index strategy, reduce logical errors, and improve the execution efficiency of the generated SQL. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] Figure 1 It is a schematic diagram of an intelligent NL2SQL generation and optimization system of the present invention; Figure 2 It is an execution flowchart of the dynamic SQL RAG caching module of the present invention; Figure 3 It is an execution flowchart of the intent-Schema dual-channel alignment module of the present invention; Figure 4 It is an execution flowchart of the incremental Table_Schema perception module of the present invention; Figure 5 It is an execution flowchart of the multi-table index-guided JOIN optimization module of the present invention; Figure 6 It is a schematic diagram of the method flow for the application of the system of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0023] The technical solution of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all of the embodiments. Usually, the components of the embodiments of the present application described and illustrated herein can be arranged and designed in various different configurations. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the scope of protection of the present invention.

[0024] It should be noted that all the defects existing in the above prior art solutions are the results obtained by the inventor through practice and careful research. Therefore, the discovery process of the above problems and the solutions proposed by the embodiments of the present application hereinafter for the above problems should be the contributions made by the inventor to the present application during the invention creation process, rather than being understood as the technical content known to those skilled in the art.

[0025] Based on the above relevant statements, since this embodiment involves technologies related to natural language processing and database query, in order to make the objectives, technical solutions, and advantages of the embodiments of the present application clearer, the following explains the relevant professional terms that the present invention may involve: 1. NL2SQL (Natural Language to SQL) is a technology that automatically converts natural language questions into database query language (SQL).

[0026] 2. SQL refers to Structured Query Language, which is a standard programming language for managing relational databases and supports operations such as data query (SELECT), insertion (INSERT), update (UPDATE), and deletion (DELETE).

[0027] 3. RAG refers to Retrieval-Augmented Generation, a technology that combines information retrieval and large model generation. It first retrieves relevant data and then generates more accurate outputs based on the retrieval results.

[0028] 4. Table Schema refers to the table structure, which is used to define the organizational structure of a database table, including metadata such as field names, data types, and constraints (such as primary keys and foreign keys).

[0029] 5. Schema is the schema / database structure, which describes the structure of the entire database (including multiple tables and their relationships).

[0030] 6. JOIN refers to table joining, which is an operation in SQL to merge and query data from multiple tables through association conditions.

[0031] 7. DDL refers to Data Definition Language, which is used to define or modify the structure of a database. SQL statements include creating, deleting, and modifying tables or indexes, etc.

[0032] In view of the technical problems pointed out in the background art, the embodiments provided by the present invention are as follows: In an exemplary embodiment, an intelligent NL2SQL generation and optimization system for enterprise-level operation scenarios is proposed. Through four core modules: dynamic caching, intent-Schema collaborative parsing, incremental Schema awareness, and multi-table index guidance, it solves the deficiencies of the prior art in terms of high-frequency query efficiency, fuzzy intent parsing, dynamic Schema adaptation, multi-table JOIN performance, and SQL credibility. The composition and implementation details of each module are as follows: 1. Dynamic SQL RAG Caching Module: 1.1. Offline-Online Collaborative Architecture: This architecture realizes efficient SQL reuse through high-frequency query pattern mining and parameterized template generation technology, combined with a vectorized semantic retrieval mechanism. The offline-online collaborative architecture is adopted: Offline mode mining: Based on historical query logs, use a large language model (LLM) to analyze high-frequency problem patterns and abstractly generate parameterized SQL templates (such as SELECT {field} FROM {table} WHERE {time_col}>?), significantly improving the semantic coverage ability of the templates.

[0033] Vectorized retrieval: Encode the problem pattern and the corresponding SQL template into a joint semantic vector (Text2SQLEmbedding), and construct a vector index library. When the user asks a question, calculate the semantic similarity in real time to match the nearest template, and achieve precise reuse through threshold determination. As Figure 2 shown, when the similarity is greater than the set threshold, return the cached SQL, otherwise enter the intent-Schema dual-channel alignment module for intent parsing.

[0034] 1.2. Dynamic Update Strategy Unit: Popularity-aware elimination: Based on the sliding window to count the query frequency, dynamically adjust the template priority, and demote and eliminate low-frequency templates (such as automatically removing them if not used for 7 days).

[0035] Schema change linkage: By listening to database DDL events, establish a table-template dependency relationship graph. When a table structure change is detected, immediately invalidate the associated template (such as field deletion triggering template elimination).

[0036] 2. Intent-Schema Dual-Channel Alignment Module: 2.1 Schema-Aware Intent Understanding Unit: Multi-table Schema Semantic Vectorization: Encode the Schema information (table name, field name, field type, field comment) of all tables into vector representations.

[0037] User Question Semantic Expansion: Use LLM to perform intent parsing and synonym expansion on the original question.

[0038] Dual-channel Matching Mechanism: Calculate the semantic similarity between the question and table names, field names simultaneously, and obtain the Top-K candidate tables by weighting. 2.2. Minimized Follow-up Decision Unit: Fuzzy Intent Detection: Calculate the fuzzy coefficient based on Schema coverage rate, field matching degree, and semantic confidence. Specifically, the weighted arithmetic mean method is used to calculate the fuzzy coefficient to reduce the impact of single-dimensional fluctuations. The formula is as follows: FC = w1·Schema coverage rate + w2·Field matching degree + w3·Semantic confidence where w1, w2, and w3 represent weights.

[0039] Dynamic Follow-up Strategy: A. When the fuzzy coefficient > threshold θ1, trigger active clarification; B. When keyword fields are missing, directly return a "no relevant data" prompt; C. Provide suggested options instead of open-ended questions for boundary cases (θ2 < fuzzy coefficient < θ1). Specifically, as shown in Table 1. θ1 represents the high uncertainty boundary threshold, taking the 90th percentile of the historical query FC (only 10% of the highest uncertainty queries trigger active clarification), θ2 represents the low uncertainty boundary threshold, taking the 60th percentile of the historical query FC (40% of the queries below this value are fully automated). This application adopts a dynamic calibration mechanism to recalculate the percentile every 24 hours using a sliding window (e.g., the last 1000 queries).

[0040] Table 1 Dynamic Follow-up Example Scenario Processing Logic Example Similar Field Matching Trigger Active Clarification and Provide Candidate Field Options "Amount-related fields: amount / total, please confirm which one to count?" Missing Key Field Directly Return Hint and Recommend Related Tables "The current table has no sales amount field. It is recommended to query the product table (products.sales)" Multi-field Ambiguity Parallel Return Multiple Results and Mark Confidence "Result 1: Count by amount (confidence 85%); Result 2: Count by price (confidence 72%)"

[0041] Context-Aware Interaction Optimization: A. Multi-round Dialogue State Tracking: Maintain user intent, confirmed Schema, and points to be clarified; B. Follow-up Question Generation: Automatically generate specific and concise clarification questions based on missing Schema information; C. User Feedback Learning: Record user correction behavior to optimize subsequent matching weights.

[0042] The implementation process of the Intent-Schema Dual-channel Alignment Module is as Figure 3 shown, and finally generate the candidate SQL.

[0043] 3. Incremental Table_Schema Perception Module: 3.1 Real-time Change Capture Unit: Metadata Monitoring: By connecting to the data warehouse metadata service (Hive Metastore / MySQL binlog), real-time capture of table structure change events (such as ALTER TABLE operations).

[0044] Change Parsing: Accurately identify key operations such as field addition and deletion (ADD / DROP COLUMN) and foreign key relationship changes (ADD / DROP FOREIGN KEY).

[0045] 3.2 Dynamic Association Maintenance Unit: Relationship Graph Update: Based on the table foreign key dependency relationship, construct a global view. When a field change is detected, automatically update the associated table node attributes (such as field addition and deletion) and real-time adjust the inter-table association edges (such as removing the corresponding connection when a foreign key is deleted).

[0046] Cascading Impact Handling: Automatically mark invalid association conditions (such as when the product_id field is deleted from the main table, prohibit JOIN operations based on this field in the sub-table).

[0047] 3.3 Change Response Unit: Synchronous Update: Trigger Schema metadata reconstruction and relationship graph reconstruction.

[0048] Cache Cleaning: Empty the affected dynamic SQL cache (such as query templates involving changed fields).

[0049] The implementation process of the incremental Table_Schema perception module is as Figure 4 shown.

[0050] 4. Multi-table Index-guided JOIN Optimization Module: 4.1 Foreign Key Relationship Modeling Unit: Relationship Graph Construction: Based on the inter-table foreign key constraints and existing index information, the large model automatically constructs a structured relationship network, clarifying the main table - sub-table association path (such as orders.user_id → users.id).

[0051] 4.2 Index Selection Optimization Mechanism Unit: Evaluation Dimensions: Cardinality Filtering: Prioritize selecting index fields with high cardinality (many unique values); Selectivity Calculation: Filter indexes with a high filtering ratio (number of filtered rows / total number of rows), benefit formula: 4.3. JOIN Path Optimization Unit Optimization Algorithm: Calculate the optimal execution order through the benefit model.

[0052] Typical Optimization Scenarios: Small Table First JOIN: Reduce the size of the intermediate result set; Index-Guided JOIN: Prioritize using the established index fields for association.

[0053] The implementation process of the multi-table index-guided JOIN optimization module is as Figure 5 shown. Finally, after optimization, an execution plan is generated.

[0054] In another exemplary embodiment, referring to Figure 6 , a method for applying an intelligent NL2SQL generation and optimization system is provided, including: Based on the problem input by the user, the dynamic SQL RAG cache module determines whether a cached template is matched. If so, the cached SQL is returned, the SQL is executed, and the result is returned; otherwise, the intent-Schema dual-channel alignment module, the incremental Table_Schema perception module, and the multi-table index-guided JOIN optimization module are executed in sequence, and the optimized SQL is executed and the result is returned.

[0055] In another exemplary embodiment, based on the above system and application method, an example of NL2SQL generation and optimization for multi-dimensional analysis of sales data is provided.

[0056] 1. Scenario Description Select an operation analysis scenario of an e-commerce enterprise. The user needs to query the information of the top 10 products with the highest sales in the past 30 days through natural language, involving the association of three tables: `orders` (order table), `products` (product table), and `users` (user table), and there are dynamic Schema changes (such as adding a `discount_amount` field).

[0057] 2. Processing by the Dynamic SQL RAG Cache Module 1) Cache Retrieval User input question: "List the names and sales amounts of the top 10 products with the highest sales in the last month."

[0058] The system matches the cached template through vectorized retrieval. The execution program example is as follows: ```sql SELECT {product_name}, SUM({amount_field}) FROM {table1} JOIN {table2} ON {join_condition} WHERE {time_field}>NOW() - INTERVAL 30 DAY GROUP BY {product_name} ORDER BY SUM({amount_field}) DESC LIMIT10 ``` 。

[0059] Hit result: When hitting, fill in the parameters extracted by the large model and directly return the result.

[0060] Non-hit result: When not hitting, enter the intent parsing process.

[0061] 2) Template update Offline analysis found a new high-frequency query: "Statistics by net sales after discount".

[0062] Generate a new template, and the execution program example is as follows: ```sql SELECT p.product_name, SUM(o.total_amount * (1 - o.discount_rate)) FROM orders o JOIN products p ON o.product_id = p.id WHERE o.create_time>? GROUP BY p.product_name ORDER BY 2 DESC LIMIT 10 ```。

[0063] 3. Intent-Schema dual-channel alignment module processing 1) Semantic parsing LLM extended question intent: Original question → "Highest sales volume" → Extended to "Largest total sales or most orders".

[0064] Synonym mapping: "Sales volume" → ["sales", "order_count", "amount"].

[0065] 2) Schema matching The vectorized matching results are shown in Table 2: Table 2 Vectorized matching results Candidate Field Similarity Source Table total_amount 0.92 orders sales 0.85 products

[0066] Fuzzy coefficient calculation: Coverage rate: 85% (missing the weight of the discount_rate field); Confidence level: 0.88 → Trigger boundary case handling (θ1 = 0.9, θ2 = 0.8).

[0067] 3) Dynamic follow-up question The system returns options to confirm the statistical caliber, including the following two options: 1. Statistic by original price sales amount (using total_amount) 2. Statistic by net amount after discount (need to combine with the discount_rate field). Among them, when the user selects option 2, supplement the `discount_rate` field to the query condition.

[0068] 4. Incremental Schema awareness module processing 1) Change capture Monitor the MySQL binlog to discover changes in the orders table. The execution program example is as follows: ```sql ALTER TABLE orders ADD COLUMN discount_rate DECIMAL(5,2) DEFAULT0; ```

[0069] 2) Cascade update Update the relationship graph: Add discount_rate as a node attribute of the orders table.

[0070] Invalidation operation: Clear 3 cache templates related to orders.amount.

[0071] 5. Multi-table index-guided JOIN optimization module processing 1) Path optimization The relationship graph discovers foreign key paths: orders.product_id → products.id (index already built) orders.user_id → users.id (no index) Benefit calculation: - Index coverage of products.id: 100% → Benefit value = 0.95 - Full table scan of users.id → Benefit value = 0.3 2) Execution plan generation. The execution program example is as follows: ```sql / * Optimized SQL * / SELECT p.product_name, SUM(o.total_amount * (1 - o.discount_rate))AS net_sales FROM products p INNER JOIN orders o ON p.id = o.product_id -- Use index association preferentially WHERE o.create_time>'2024-11-01' GROUP BY p.product_name ORDER BY net_sales DESC LIMIT 10; ```。

[0072] 6. Evaluation Results 1) Performance Metrics Query response time: Reduced from the original 1.2s to 0.4s (60% contribution from index-guided optimization); Accuracy of intent parsing: 92% (15% improvement through dynamic questioning).

[0073] 2) Adaptability to Schema Changes The new field discount_rate took only 200ms from detection to availability.

[0074] 3) Output example, as shown in Table 3.

[0075] Table 3 Output Example product_name net_sales Smartphone X 584200.00 Bluetooth Headset Pro 320150.00

[0076] 7. Demonstration of Technical Advantages Optimization of high-frequency queries: The response time is reduced to 0.1s when the same pattern is queried a second time and the cache is hit.

[0077] Handling of fuzzy intents: 70% of open-ended questions are reduced through option-based questioning.

[0078] Dynamic adaptation ability: Automatically reconstructs the query after Schema changes without manual intervention.

[0079] Improvement in JOIN performance: The efficiency of multi-table association is increased by 3 times through index selection.

[0080] In another exemplary embodiment, based on the same inventive concept, a computer-readable storage medium is provided. The computer-readable storage medium stores a computer program. When the computer program is executed by a processor, it implements the application method of the intelligent NL2SQL generation and optimization system provided by the embodiments of the present invention, and correspondingly implements the functions of each module. Based on such an understanding, the technical solution of this embodiment, in essence, or the part that contributes to the prior art, or a part of this technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to enable a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods of the various embodiments of the present invention. The foregoing storage medium includes various media that can store program codes, such as USB flash drives, mobile hard disks, read-only memories (ROMs), random access memories (RAMs), magnetic disks, or optical discs.

[0081] In another exemplary embodiment, based on the same inventive concept, a terminal is provided, including a memory and a processor. A computer instruction that can run on the processor is stored on the memory. When the processor runs the computer instruction, it executes the application method of the intelligent NL2SQL generation and optimization system provided by the embodiments of the present invention, and correspondingly implements the functions of each module.

[0082] The processor can be a single-core or multi-core central processing unit or a specific integrated circuit, or an integrated circuit configured to implement one or more of the present invention.

[0083] The embodiments of the subject matter and the functional operations described in this specification can be implemented in the following: tangibly embodied computer software or firmware, computer hardware including the structures disclosed in this specification and their structural equivalents, or a combination of one or more of them. The embodiments of the subject matter described in this specification can be implemented as one or more computer programs, that is, one or more modules in computer program instructions encoded on a tangible non-transitory program carrier to be executed by a data processing device or to control the operation of the data processing device. Alternatively or additionally, the program instructions can be encoded on an artificially generated propagated signal, such as a machine-generated electrical, optical, or electromagnetic signal, which is generated to encode and transmit information to a suitable receiver device for execution by the data processing device.

[0084] The processes and logical flows described in this specification can be executed by one or more programmable computers executing one or more computer programs to perform the corresponding functions by operating on input data and generating output. The processes and logical flows can also be executed by special-purpose logic circuitry, such as an FPGA (Field Programmable Gate Array) or ASIC (Application Specific Integrated Circuit), and the apparatus can also be implemented as special-purpose logic circuitry.

[0085] Processors suitable for executing computer programs include, for example, general and / or special-purpose microprocessors, or any other type of central processing unit. Generally, the central processing unit will receive instructions and data from a read-only memory and / or a random access memory. The basic components of a computer include a central processing unit for implementing or executing instructions and one or more memory devices for storing instructions and data. Generally, a computer will also include one or more mass storage devices for storing data, such as magnetic disks, magneto-optical disks, or optical disks, etc., or the computer will be operatively coupled to such mass storage devices to receive data therefrom or transfer data thereto, or both. However, a computer is not necessarily required to have such devices. In addition, a computer can be embedded in another device, such as a mobile phone, a personal digital assistant (PDA), a mobile audio or video player, a game console, a global positioning system (GPS) receiver, or a portable storage device such as a universal serial bus (USB) flash drive, to name just a few.

[0086] It should be understood that each block in a flowchart or block diagram can represent a module, a segment of a program, or a part of code that contains one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions noted in the blocks can occur in a different order than noted in the drawings. For example, two consecutive blocks can actually be executed substantially in parallel, and they can sometimes be executed in the reverse order, depending on the functions involved. It should also be noted that each block in the block diagram and / or flowchart, and combinations of blocks in the block diagram and / or flowchart, can be implemented by a special-purpose hardware-based system that performs the specified functions or actions, or can be implemented by a combination of special-purpose hardware and computer instructions.

[0087] The above specific embodiments are detailed descriptions of the present invention. It cannot be determined that the specific embodiments of the present invention are limited only to these descriptions. For those of ordinary skill in the technical field to which the present invention pertains, without departing from the concept of the present invention, several simple deductions and substitutions can still be made, and all should be regarded as belonging to the protection scope of the present invention.

Claims

1. An intelligent NL2SQL generation and optimization system, characterized in that, including: A dynamic SQL RAG cache module, including an offline-online collaborative architecture and a dynamic update policy unit. The offline-online collaborative architecture is used to realize high-frequency query pattern mining and parameterized template generation through the offline-online collaborative architecture, and adopts a vectorized semantic retrieval mechanism to achieve efficient SQL reuse; the dynamic update policy unit is used for heat perception elimination and Schema change linkage; An intent-Schema dual-channel alignment module, including a Schema-aware intent understanding unit and a minimized questioning decision unit. The Schema-aware intent understanding unit is used to achieve dual-channel matching through multi-table Schema semantic vectorization and user question semantic extension; the minimized questioning decision unit is used to achieve interaction optimization based on fuzzy intent detection and dynamic questioning strategies; An incremental Table_Schema perception module, including a real-time change capture unit, a dynamic association maintenance unit, and a change response unit. The real-time change capture unit is used to capture table structure change events in real time by docking with the data warehouse metadata service and perform change parsing; the dynamic association maintenance unit is used for relationship graph update and cascade impact processing; The change response unit is used to implement synchronous update and cache cleaning operations; A multi-table index-guided JOIN optimization module, including a foreign key relationship modeling unit, an index optimization mechanism unit, and a JOIN path optimization unit. The foreign key relationship modeling unit is used to construct a structured relationship network based on inter-table foreign key constraints and existing index information; the index optimization mechanism unit is used to evaluate index benefits through cardinality screening and selectivity calculation; the JOIN path optimization unit is used to calculate the optimal execution order through a benefit model.

2. The intelligent NL2SQL generation and optimization system according to claim 1, wherein The query pattern mining and parameterized template generation includes: Based on historical query logs, using a large language model to analyze high-frequency problem patterns and abstractly generate parameterized SQL templates; The vectorized semantic retrieval mechanism includes: Encoding the problem pattern and the corresponding SQL template into a joint semantic vector and constructing a vector index library.

3. An intelligent NL2SQL generation and optimization system according to claim 2, characterized in that, The heat perception elimination includes: Based on a sliding window to count the query frequency, dynamically adjust the priority of SQL templates, and degrade and eliminate low-frequency SQL templates; The Schema change linkage includes: By listening to database DDL events, establishing a table-template dependency relationship graph, and when a table structure change is detected, immediately invalidate the associated template.

4. An intelligent NL2SQL generation and optimization system according to claim 1, characterized in that The dual-channel matching through multi-table Schema semantic vectorization and user question semantic extension includes: Encoding the Schema information of all tables into vector representations; Using a large language model to perform intent parsing and synonym expansion on the original question; Simultaneously calculating the semantic similarity between the question and the Schema information, and weighting to obtain the Top-K candidate tables.

5. An intelligent NL2SQL generation and optimization system according to claim 4, characterized in that The fuzzy intent detection includes: Calculating a fuzzy coefficient based on Schema coverage, field matching degree, and semantic confidence.

6. The intelligent NL2SQL generation and optimization system according to claim 1, characterized in that The change parsing includes: Identifying field additions, deletions, and changes in foreign key relationships; The relationship graph update includes: Construct a global view based on the foreign key dependencies outside the table. When a field change is detected, automatically update the attributes of the associated table nodes and adjust the association edges between tables in real time; The cascade impact processing includes: Automatically mark the invalid association conditions; The implementation of synchronous update and cache cleaning operations includes: Trigger the Schema metadata reconstruction, relationship graph reconstruction, and empty the affected dynamic SQL cache.

7. An intelligent NL2SQL generation and optimization system according to claim 1, characterized in that The evaluation of index benefits through cardinality filtering and selective calculation includes: Prioritize the selection of high-cardinality index fields, filter the indexes with a high filtering ratio, and the calculation formula for index benefits is as follows: 。 8. A method for applying an intelligent NL2SQL generation and optimization system according to any one of claims 1-7, characterized in that Include: According to the question input by the user, determine whether a cache template is matched through the dynamic SQL RAG cache module. If so, return the cached SQL, execute the SQL, and return the result; Otherwise, execute the intent-Schema dual-channel alignment module, incremental Table_Schema awareness module, and multi-table index-guided JOIN optimization module in sequence, execute the optimized SQL, and return the result.

9. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by a processor, it implements the application method described in claim 8.

Images