An intelligent NL2SQL generation and optimization system, application method and storage medium

Abstract

This invention discloses an intelligent NL2SQL generation and optimization system, application method, and storage medium, located at the intersection of natural language processing and database query technologies. Through four core components: a dynamic SQL RAG caching module, an intent-Schema dual-channel alignment module, an incremental Table-Schema perception module, and a multi-table index-guided JOIN optimization module, this system addresses shortcomings of existing technologies in high-frequency query efficiency, fuzzy intent parsing, dynamic schema adaptation, multi-table JOIN performance, and SQL reliability. This invention achieves beneficial effects such as improved query efficiency and resource utilization, enhanced fuzzy intent parsing accuracy and interaction efficiency, and dynamic adaptation of industrial-grade data warehouses. It is particularly suitable for large-model asset queries in enterprise-level operational scenarios.

Description

Technical Field

[0001] The present invention relates to the intersection of natural language processing and database query technology, and specifically to an intelligent conversion of natural language to structured query language (NL2SQL) for enterprise-level data warehouses. The technology is particularly suitable for large-model-based task reasoning, dynamic table structure perception, hybrid enhanced retrieval (RAG) technology, and automated generation and error prevention of multi-table joint queries in operational scenarios. 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 technology primarily relies on the SQL programming capabilities of the large model itself, with weak auxiliary modules. This technology runs directly in business operations, leading to the following prominent issues:

[0003] 1. The end-to-end model was used to generate SQL in real time, but cache optimization was not performed for high-frequency queries, resulting in wasted computing resources when similar queries were repeatedly generated.

[0004] 2. The existing intent recognition module is independent of table structure analysis. As a result, when users perform ambiguous queries, they cannot quickly associate available fields and require multiple rounds of interaction and clarification.

[0005] 3. The traditional NL2SQL system relies on manual pre-configuration of the data warehouse table schema. When the data warehouse table schema changes (such as adding new fields or adjusting table relationships), the model prompt words must be readjusted or the configuration must be manually updated, resulting in service interruption.

[0006] 4. Existing methods fail to effectively model foreign key relationships and index strategies when generating multi-table JOINs, resulting in incorrect JOIN paths or missing association conditions (e.g., failing to recognize that orders.user_id = users.id).

[0007] Therefore, the existing NL2SQL technology still has significant technical gaps in actual business scenarios, especially in terms of adaptability to dynamic environments and execution efficiency, which urgently need breakthroughs. Summary of the Invention

[0008] 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 suitable for large-model asset queries in production-level operation scenarios.

[0009] The object of the present invention is achieved through the following technical solutions:

[0010] In a first aspect, an intelligent NL2SQL generation and optimization system is provided, comprising:

[0011] The dynamic SQL RAG cache module includes an offline-online collaborative architecture and a dynamic update strategy unit. The offline-online collaborative architecture is used to mine high-frequency query patterns and generate parameterized templates, and uses a vectorized semantic retrieval mechanism to achieve efficient SQL reuse. The dynamic update strategy unit is used for hot-sensing elimination and schema change linkage.

[0012] The intent-schema dual-channel alignment module includes 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 expansion. The minimized questioning decision unit is used to achieve interaction optimization based on fuzzy intent detection and dynamic questioning strategies.

[0013] The incremental Table_Schema awareness module includes 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 and perform change analysis by connecting to the data warehouse metadata service; the dynamic association maintenance unit is used to update the relationship graph and handle cascading impacts; and the change response unit is used to implement synchronous updates and cache cleanup operations.

[0014] The multi-table index-guided JOIN optimization module includes 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 build 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; and the JOIN path optimization unit is used to calculate the optimal execution order through a benefit model.

[0015] In some embodiments, the query pattern mining and parameterized template generation include:

[0016] Based on historical query logs, we use a large language model to analyze high-frequency problem patterns and abstractly generate parameterized SQL templates.

[0017] The vectorized semantic retrieval mechanism includes:

[0018] The question pattern and the corresponding SQL template are encoded into a joint semantic vector, and a vector index library is constructed.

[0019] In some embodiments, the heat-aware elimination includes:

[0020] Based on the query frequency statistics of the sliding window, the SQL template priority is dynamically adjusted, and low-frequency SQL templates are downgraded and eliminated;

[0021] The schema change linkage includes:

[0022] By monitoring database DDL events, a table-template dependency graph is established. When a table structure change is detected, the associated template is immediately invalidated.

[0023] In some embodiments, the dual-channel matching is achieved through multi-table schema semantic vectorization and user question semantic expansion, including:

[0024] Encode the schema information of all tables into vector representations;

[0025] Use a large language model to perform intent parsing and synonym expansion on the original question;

[0026] At the same time, the semantic similarity between the question and the Schema information is calculated and weighted to obtain the Top-K candidate table.

[0027] In some embodiments, the fuzzy intent detection includes:

[0028] The fuzzy coefficient is calculated based on schema coverage, field matching, and semantic confidence.

[0029] In some embodiments, the change analysis includes:

[0030] Identify field additions and deletions and changes in foreign key relationships;

[0031] The relationship map update includes:

[0032] Build a global view based on table foreign key dependencies. When field changes are detected, automatically update the associated table node attributes and adjust the inter-table association edges in real time.

[0033] The cascading impact processing includes:

[0034] Automatically mark invalid association conditions;

[0035] The synchronization update and cache cleanup operations are implemented as follows:

[0036] Trigger schema metadata reconstruction, relationship graph reconstruction, and clear the affected dynamic SQL cache.

[0037] In some embodiments, evaluating index returns through cardinality screening and selective calculation includes:

[0038] Prioritize index fields with high cardinality and filter indexes with high filtering ratios. The formula for calculating index benefits is as follows:

[0039] .

[0040] In a second aspect, an application method of an intelligent NL2SQL generation and optimization system is provided, including:

[0041] Based on the question entered by the user, the dynamic SQL RAG cache module determines whether a match is found in the cache template. If so, the cached SQL is returned, executed, and the results are 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 to execute the optimized SQL and return the results.

[0042] In a third aspect, a computer-readable storage medium is provided, wherein 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.

[0043] It should be further explained that the technical features corresponding to the above embodiments can be combined or replaced with each other to form a new technical solution if there is no conflict.

[0044] Compared with the prior art, the present invention has the following beneficial effects:

[0045] 1. Optimize high-frequency query processing efficiency and resource utilization: Parameterize and cache high-frequency queries through a dynamic caching mechanism, reducing the number of repeated model inferences, lowering computing resource consumption and response latency, and improving the throughput of high-concurrency queries in operational scenarios.

[0046] 2. Improving the ability to accurately parse ambiguous intents: Through intent-schema dual-channel alignment and a minimized follow-up questioning strategy, this approach combines table structure metadata (fields, foreign key relationships) with user intent analysis to reduce the need for multiple rounds of interaction, enabling automated field mapping and semantic disambiguation for fuzzy queries, and improving the efficiency of user interaction.

[0047] 3. Achieve dynamic adaptability of industrial-grade data warehouses: Real-time monitoring of data warehouse metadata and real-time perception of table structure changes (such as field additions and deletions, and foreign key adjustments) prevent model invalidation caused by schema updates and reduce manual maintenance costs.

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

[0049] Figure 1 A schematic diagram of an intelligent NL2SQL generation and optimization system according to the present invention;

[0050] Figure 2 This is the execution flow chart of the dynamic SQL RAG cache module of the present invention;

[0051] Figure 3 This is the intention of the present invention -Schema dual-channel alignment module execution flow chart;

[0052] Figure 4 This is the flowchart of the incremental Table_Schema perception module execution of the present invention;

[0053] Figure 5 This is a flowchart of the execution of the multi-table index-guided JOIN optimization module of the present invention;

[0054] Figure 6 The figure is a flow chart of the system application method of the present invention. DETAILED DESCRIPTION

[0055] The technical solutions of the present invention are described clearly and completely below in conjunction with the accompanying drawings. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all of the embodiments. The components of the embodiments of the present application generally described and shown in the drawings herein can be arranged and designed in various different configurations. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0056] It should be noted that the defects existing in the solutions in the above-mentioned prior art are the results obtained by the inventor after practice and careful research. Therefore, the discovery process of the above-mentioned problems and the solutions proposed in the embodiments of this application below for the above-mentioned problems should be the contributions made by the inventor to this application in the process of invention and creation, and should not be understood as technical contents known to technical personnel in this field.

[0057] 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 this application clearer, the following explains the relevant professional terms that may be involved in the present invention:

[0058] 1. NL2SQL (Natural Language to SQL) is a technology that automatically converts natural language questions into database query language (SQL).

[0059] 2. SQL refers to Structured Query Language, a standard programming language for managing relational databases. It supports operations such as data query (SELECT), insert (INSERT), update (UPDATE), and delete (DELETE).

[0060] 3. RAG stands for Retrieval-Augmented Generation, which combines information retrieval and large model generation techniques to first retrieve relevant data and then generate more accurate output based on the retrieval results.

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

[0062] 5. Schema is a model / database structure that describes the structure of the entire database (including multiple tables and their relationships).

[0063] 6. JOIN refers to table connection, which is an operation in SQL to merge data from multiple tables through association conditions.

[0064] 7. DDL refers to Data Definition Language, which is an SQL statement used to define or modify the database structure, including creating, deleting, and modifying tables or indexes.

[0065] In response to the technical problems pointed out in the background technology, the embodiments provided by the present invention are as follows:

[0066] In one exemplary embodiment, an intelligent NL2SQL generation and optimization system for enterprise-level operational scenarios is proposed. Through four core modules: dynamic caching, intent-schema collaborative parsing, incremental schema awareness, and multi-table index guidance, this system addresses existing shortcomings in high-frequency query efficiency, fuzzy intent parsing, dynamic schema adaptation, multi-table JOIN performance, and SQL reliability. The components and implementation details of each module are as follows:

[0067] 1. Dynamic SQL RAG Cache Module:

[0068] 1.1. Offline-online collaborative architecture:

[0069] This architecture uses high-frequency query pattern mining and parameterized template generation technology, combined with vectorized semantic retrieval mechanism, to achieve efficient SQL reuse. It adopts offline-online collaborative architecture:

[0070] Offline pattern mining: Based on historical query logs, the large language model (LLM) is used to analyze high-frequency problem patterns and abstractly generate parameterized SQL templates (such as SELECT {field} FROM {table} WHERE {time_col}>?), significantly improving the template's semantic coverage.

[0071] Vectorized retrieval: Encode the question pattern and the corresponding SQL template into a joint semantic vector (Text2SQLEmbedding) to build a vector index library. When the user asks a question, the semantic similarity is calculated in real time to match the nearest template, and accurate reuse is achieved through threshold judgment, such as Figure 2 As shown in the figure, when the similarity is greater than the set threshold, the cached SQL is returned, otherwise it enters the intent-Schema dual-channel alignment module for intent parsing.

[0072] 1.2. Dynamic update strategy unit:

[0073] Popularity-aware elimination: Based on query frequency statistics in a sliding window, template priorities are dynamically adjusted, and low-frequency templates are downgraded and eliminated (automatically removed if not used for 7 days).

[0074] Schema change linkage: By monitoring database DDL events, a table-template dependency graph is established. When a table structure change is detected, the associated template is immediately invalidated (for example, field deletion triggers template elimination).

[0075] 2. Intent-Schema Dual-Channel Alignment Module:

[0076] 2.1.Schema-aware intent understanding unit:

[0077] Multi-table Schema semantic vectorization: Encode the schema information of all tables (table name, field name, field type, field comment) into vector representation.

[0078] User question semantic expansion: Use LLM to perform intent parsing and synonym expansion on the original question.

[0079] Dual-channel matching mechanism: Simultaneously calculate the semantic similarity between the question and the table name and field name, and weight them to obtain the Top-K candidate tables. 2.2. Minimize the number of follow-up decision units:

[0080] Fuzzy intent detection: The fuzzy coefficient is calculated based on schema coverage, field matching, and semantic confidence. Specifically, the fuzzy coefficient is calculated using a weighted arithmetic average to reduce the impact of fluctuations in a single dimension. The formula is as follows:

[0081] FC = w1 · Schema coverage + w2 · Field matching + w3 · Semantic confidence

[0082] Among them, w1, w2, and w3 represent weights.

[0083] Dynamic questioning strategy:

[0084] A. When the fuzzy coefficient is greater than the threshold θ1, active clarification is triggered;

[0085] B. When the key field is missing, the prompt "No relevant data" is directly returned;

[0086] C. For edge cases (θ2 < fuzziness coefficient < θ1), suggest options instead of open questions. This is shown in Table 1. θ1 represents the high uncertainty threshold, which is the 90th percentile of the historical query FC (only the 10% of the most uncertain queries trigger proactive clarification). θ2 represents the low uncertainty threshold, which is the 60th percentile of the historical query FC (the 40% of queries below this value are automatically executed). This application uses a dynamic calibration mechanism, recalculating the quantiles every 24 hours using a sliding window (e.g., the most recent 1000 queries).

[0087] Table 1 Examples of dynamic follow-up questions

[0088] Scenario Processing Logic Example Similar field matching Trigger proactive clarification and provide candidate field options "Amount related fields: amount / total, please confirm which one you want to count?" Key fields are missing Return to the prompt directly and recommend related tables "The current table does not have a sales field. It is recommended to query the product table (products.sales)" Multi-field ambiguity Return multiple results in parallel and annotate confidence levels "Result 1: Statistics by amount (confidence level 85%); Result 2: Statistics by price (confidence level 72%)

[0089] Context-aware interaction optimization:

[0090] A. Multi-turn conversation state tracking: maintaining user intent, confirmed schemas, and points to be clarified;

[0091] B. Follow-up question generation: Automatically generate specific and concise clarification questions based on missing schema information;

[0092] C. User feedback learning: Record user correction behaviors to optimize subsequent matching weights.

[0093] The implementation process of the intent-schema dual-channel alignment module is as follows Figure 3 As shown, the candidate SQL is finally generated.

[0094] 3. Incremental Table_Schema Awareness Module:

[0095] 3.1 Real-time change capture unit:

[0096] Metadata monitoring: By connecting to the data warehouse metadata service (Hive Metastore / MySQL binlog), table structure change events (such as ALTER TABLE operations) can be captured in real time.

[0097] Change analysis: Accurately identify key operations such as adding and deleting fields (ADD / DROP COLUMN) and changing foreign key relationships (ADD / DROPFOREIGN KEY).

[0098] 3.2. Dynamic association maintenance unit:

[0099] Relationship graph update: Build a global view based on table foreign key dependencies. When field changes are detected, automatically update the properties of related table nodes (such as field additions and deletions) and adjust the association edges between tables in real time (such as removing the corresponding connection when a foreign key is deleted).

[0100] Cascading impact processing: Automatically mark invalid association conditions (for example, when the product_id field is deleted from the main table, the JOIN operation based on this field in the child table is prohibited).

[0101] 3.3. Change Response Unit:

[0102] Synchronous update: triggers schema metadata reconstruction and relationship graph reconstruction.

[0103] Cache cleaning: Clear the affected dynamic SQL cache (such as query templates involving changed fields).

[0104] The implementation process of the incremental Table_Schema awareness module is as follows Figure 4 shown.

[0105] 4. Multi-table index-guided JOIN optimization module:

[0106] 4.1. Foreign key relationship modeling unit:

[0107] Relationship graph construction: Based on foreign key constraints between tables and existing index information, the large model automatically builds a structured relationship network and clarifies the main table-child table association path (such as orders.user_id → users.id).

[0108] 4.2. Index selection mechanism unit:

[0109] Evaluation dimensions:

[0110] Cardinality screening: give priority to index fields with high cardinality (many unique values);

[0111] Selective calculation: filter indexes with high filtering ratio (number of filtered rows / total number of rows). Profit formula:

[0112]

[0113] 4.3.Join Path Optimization Unit

[0114] Optimization algorithm: Calculate the optimal execution order through the profit model.

[0115] Typical optimization scenarios:

[0116] Prioritize JOIN with small tables: Reduce the size of intermediate result sets;

[0117] Index-guided JOIN: Prioritizes using established index fields for joins.

[0118] The implementation process of the multi-table index-guided JOIN optimization module is as follows Figure 5 As shown in the figure, after optimization, an execution plan is generated.

[0119] In another exemplary embodiment, referring to Figure 6 , provides an application method of an intelligent NL2SQL generation and optimization system, including:

[0120] Based on the question entered by the user, the dynamic SQL RAG cache module determines whether a match is found in the cache template. If so, the cached SQL is returned, executed, and the results are 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 to execute the optimized SQL and return the results.

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

[0122] 1. Scenario Description

[0123] In an e-commerce enterprise operations analysis scenario, users need to use natural language to query information about the top 10 products by sales in the past 30 days. This involves three tables: orders, products, and users, and involves dynamic schema changes (such as the addition of a discount_amount field).

[0124] 2. Dynamic SQL RAG cache module processing

[0125] 1) Cache retrieval

[0126] User input question: "List the names and sales of the 10 products with the highest sales in the past month."

[0127] The system matches the cache template through vectorized retrieval. The execution program example is as follows:

[0128] ```sql

[0129] SELECT {product_name}, SUM({amount_field})

[0130] FROM {table1} JOIN {table2} ON {join_condition}

[0131] WHERE {time_field}>NOW() - INTERVAL 30 DAY

[0132] GROUP BY {product_name} ORDER BY SUM({amount_field}) DESC LIMIT10

[0133] ``` .

[0134] Hit result: When a hit occurs, fill in the large model extraction parameters and return the result directly.

[0135] Non-hit result: When a non-hit occurs, the intent parsing process begins.

[0136] 2) Template update

[0137] Offline analysis discovered a new high-frequency query: "Net sales statistics after discount".

[0138] Generate a new template and execute the program as follows:

[0139] ```sql

[0140] SELECT p.product_name, SUM(o.total_amount * (1 - o.discount_rate))

[0141] FROM orders o JOIN products p ON o.product_id = p.id

[0142] WHERE o.create_time>?

[0143] GROUP BY p.product_name ORDER BY 2 DESC LIMIT 10

[0144] ```.

[0145] 3. Intent-Schema Dual-Channel Alignment Module Processing

[0146] 1) Semantic analysis

[0147] LLM Extension Question Intent:

[0148] Original question → "Highest sales volume" → Expanded to "Largest total sales volume or largest number of orders".

[0149] Synonym mapping: "sales" → ["sales", "order_count", "amount"].

[0150] 2) Schema matching

[0151] The vectorized matching results are shown in Table 2:

[0152] Table 2 Vectorized matching results

[0153] Candidate fields Similarity Source Table total_amount 0.92 orders sales 0.85 products

[0154] Fuzzy coefficient calculation:

[0155] Coverage: 85% (missing discount_rate field weight);

[0156] Confidence: 0.88 → Trigger edge case handling (θ1=0.9, θ2=0.8).

[0157] 3) Dynamic follow-up questions

[0158] The system returns options to confirm the statistical caliber, including the following two options:

[0159] 1. Statistics by original price sales (using total_amount)

[0160] 2. Calculate the net amount after discount (need to be combined with the discount_rate field). If the user selects option 2, the `discount_rate` field is added to the query criteria.

[0161] 4. Incremental Schema-aware module processing

[0162] 1) Change Capture

[0163] Monitor MySQL binlog and discover changes to the orders table. The following example shows the execution program:

[0164] ```sql

[0165] ALTER TABLE orders ADD COLUMN discount_rate DECIMAL(5,2) DEFAULT0;

[0166] ``` .

[0167] 2) Cascading Updates

[0168] Update the relationship graph: add discount_rate as a node attribute of the orders table.

[0169] Invalidation operation: Clear 3 cached templates involving orders.amount.

[0170] 5. Multi-table index-guided JOIN optimization module processing

[0171] 1) Path optimization

[0172] The relationship graph discovers the foreign key path:

[0173] orders.product_id → products.id (indexed)

[0174] orders.user_id → users.id (no index)

[0175] Profit calculation:

[0176] - products.id index coverage: 100% → benefit value = 0.95

[0177] - users.id full table scan → benefit = 0.3

[0178] 2) Generate an execution plan. The execution program example is as follows:

[0179] ```sql

[0180] / * Optimized SQL * /

[0181] SELECT p.product_name, SUM(o.total_amount * (1 - o.discount_rate))AS net_sales

[0182] FROM products p

[0183] INNER JOIN orders o ON p.id = o.product_id -- prioritize index joins

[0184] WHERE o.create_time>'2024-11-01'

[0185] GROUP BY p.product_name ORDER BY net_sales DESC LIMIT 10;

[0186] ```.

[0187] 6. Evaluation Results

[0188] 1) Performance indicators

[0189] Query response time: reduced from 1.2s to 0.4s (index-guided optimization contributed 60%).

[0190] Intent parsing accuracy: 92% (increased by 15% through dynamic questioning).

[0191] 2) Schema change adaptability

[0192] The newly added field discount_rate takes only 200ms from detection to availability.

[0193] 3) Output example, see Table 3.

[0194] Table 3 Output example

[0195] product_name net_sales SmartphoneX 584200.00 Bluetooth Headset Pro 320150.00

[0196] 7. Demonstration of technological advantages

[0197] High-frequency query optimization: Second queries with the same pattern hit the cache, reducing response time to 0.1s.

[0198] Dealing with ambiguous intent: Reduce open-ended questions by 70% through follow-up questions.

[0199] Dynamic adaptability: Automatically reconstruct queries after schema changes without manual intervention.

[0200] JOIN performance improvement: Index selection increases the efficiency of multi-table joins by 3 times.

[0201] In another exemplary embodiment, based on the same inventive concept, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores a computer program. When the computer program is executed by a processor, the computer program implements the application method of the intelligent NL2SQL generation and optimization system provided in the embodiment of the present invention, and accordingly implements the corresponding functions of each module. Based on this understanding, the technical solution of this embodiment, or the part that contributes to the prior art, or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods of each embodiment of the present invention. The aforementioned storage medium includes various media that can store program code, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk.

[0202] In another exemplary embodiment, based on the same inventive concept, a terminal is provided, including a memory and a processor. The memory stores computer instructions that can be executed on the processor. When the processor executes the computer instructions, it executes the application method of the intelligent NL2SQL generation and optimization system provided in an embodiment of the present invention, and accordingly realizes the corresponding functions of each module.

[0203] The processor may be a single-core or multi-core central processing unit or a specific integrated circuit, or one or more integrated circuits configured to implement the present invention.

[0204] Embodiments of the subject matter and functional operations described in this specification may be implemented in: 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 thereof. Embodiments of the subject matter described in this specification may be implemented as one or more computer programs, i.e., one or more modules of computer program instructions encoded on a tangible, non-transitory program carrier for execution by a data processing apparatus or to control the operation of the data processing apparatus. Alternatively or in addition, the program instructions may be encoded on an artificially generated propagated signal, such as a machine-generated electrical, optical, or electromagnetic signal, that is generated to encode and transmit information to a suitable receiver apparatus for execution by the data processing apparatus.

[0205] The processes and logic flows described in this specification can be performed 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 logic flows can also be performed by, and apparatus can be implemented as, special purpose logic circuitry, such as an FPGA (field programmable gate array) or an ASIC (application-specific integrated circuit).

[0206] Processors suitable for executing computer programs include, for example, general-purpose and / or special-purpose microprocessors, or any other type of central processing unit. Typically, a central processing unit will receive instructions and data from a read-only memory and / or 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. Typically, a computer will also include one or more mass storage devices for storing data, such as magnetic disks, magneto-optical disks, or optical disks, or the computer will be operably coupled to such a mass storage device to receive data from it or to transmit data to it, or both. However, a computer does not necessarily have such a device. 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 a few.

[0207] It should be understood that each box in the flow chart or block diagram can represent a module, program segment or a part of code, and the part of the module, program segment or code comprises one or more executable instructions for realizing the logical function of the provision. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a sequence different from that marked in the accompanying drawings. For example, two consecutive boxes can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram and / or flow chart, and the combination of the boxes in the block diagram and / or flow chart can be implemented with a dedicated hardware-based system that performs the function or action of the provision, or can be implemented with a combination of dedicated hardware and computer instructions.

[0208] The above specific implementation methods are detailed descriptions of the present invention. It cannot be considered that the specific implementation methods of the present invention are limited to these descriptions. For ordinary technicians in the technical field to which the present invention belongs, they can make several simple deductions and substitutions without departing from the concept of the present invention, which should be regarded as falling within the scope of protection of the present invention.

Claims

1. An intelligent NL2SQL generation and optimization system, characterized by: include: The dynamic SQL RAG cache module includes an offline-online collaborative architecture and a dynamic update strategy unit. The offline-online collaborative architecture is used to mine high-frequency query patterns and generate parameterized templates, and uses a vectorized semantic retrieval mechanism to achieve efficient SQL reuse. The dynamic update strategy unit is used for heat-aware elimination and schema change linkage; The high-frequency query pattern mining and parameterized template generation include: based on historical query logs, using a large language model to analyze high-frequency question patterns and abstractly generate parameterized SQL templates; The vectorized semantic retrieval mechanism includes: Encode the question pattern and the corresponding SQL template into a joint semantic vector and build a vector index library; The heat-aware elimination includes: Based on the query frequency statistics of the sliding window, the SQL template priority is dynamically adjusted, and low-frequency SQL templates are downgraded and eliminated; The schema change linkage includes: By monitoring database DDL events, a table-template dependency graph is established. When a table structure change is detected, the associated template is immediately invalidated. The intent-schema dual-channel alignment module includes 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 expansion. The minimized questioning decision unit is used to achieve interaction optimization based on fuzzy intent detection and dynamic questioning strategies. The incremental Table_Schema awareness module includes 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 and perform change analysis by connecting to the data warehouse metadata service; the dynamic association maintenance unit is used to update the relationship graph and handle cascading impacts; the change response unit is used to implement synchronous updates and cache cleanup operations; the relationship graph update and cascading impact handling include: Build a global view based on table foreign key dependencies. When field changes are detected, automatically update the associated table node attributes and adjust the inter-table association edges in real time; automatically mark invalid association conditions; The multi-table index-guided JOIN optimization module includes 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 build 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; the evaluation of index benefits through cardinality screening and selectivity calculation includes: Prioritize index fields with high cardinality and filter indexes with high filtering ratios. The formula for calculating index benefits is as follows: 。 2. The intelligent NL2SQL generation and optimization system according to claim 1, characterized in that: The dual-channel matching is achieved through multi-table schema semantic vectorization and user question semantic expansion, including: Encode the schema information of all tables into vector representations; Use a large language model to perform intent parsing and synonym expansion on the original question; At the same time, the semantic similarity between the question and the Schema information is calculated and weighted to obtain the Top-K candidate table.

3. The intelligent NL2SQL generation and optimization system according to claim 2, characterized in that: The fuzzy intention detection includes: The fuzzy coefficient is calculated based on schema coverage, field matching, and semantic confidence.

4. The intelligent NL2SQL generation and optimization system according to claim 1, characterized in that: The change analysis includes: Identify field additions and deletions and changes in foreign key relationships; The synchronization update and cache cleanup operations are implemented as follows: Trigger schema metadata reconstruction, relationship graph reconstruction, and clear the affected dynamic SQL cache.

5. An application method of the intelligent NL2SQL generation and optimization system according to any one of claims 1 to 4, characterized in that: include: Based on the question entered by the user, the dynamic SQL RAG cache module determines whether it matches the cache template. If so, the cached SQL is returned, executed, and the results are 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 to execute the optimized SQL and return the results.

6. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the application method described in claim 5 is implemented.

Images