Multi-path rag context weighted enhanced retrieval driven nl2sql generation method and device

Abstract

The present application relates to the technical field of artificial intelligence, in particular to a multi-path RAG context weighted enhanced retrieval driven NL2SQL generation method and device. The method comprises the following steps: receiving and preprocessing a natural language query text of user input to obtain a multi-dimensional query vector; performing multi-path context retrieval in parallel based on the multi-dimensional query vector to obtain a retrieval result set; assembling the retrieval results of the retrieval result set into system prompt words and inputting the system prompt words into a large language model to obtain SQL generation instructions returned by the large language model; and executing the SQL generation instructions. Through virtual key markers and virtual key coverage rules, when a filtering condition string contains a virtual key marker, the large language model is instructed to generate a filtering condition using a text value in an equal comparison and to switch to an identification column and identification value to generate a filtering condition in a range comparison, so that the large language model can generate a correct SQL statement in a range comparison query without understanding the underlying ID coding, thereby solving the problem of understanding the underlying ID coding of the large language model.

Description

Technical Field

[0001] This invention relates to the field of artificial intelligence technology, and in particular to a method and apparatus for generating NL2SQL (Natural Language to SQL) driven by multi-path RAG (Retrieval-Augmented Generation) context-weighted enhanced retrieval. Background Technology

[0002] As enterprises continue to advance their digital transformation, they typically deploy various business systems, including data security management platforms, log and reporting platforms, endpoint behavior analysis platforms, data governance platforms, and operational analysis platforms. Traditional data analysis systems usually require technical personnel to understand database structure, field meanings, and query logic, making them difficult for ordinary business users to use and still requiring the assistance of professional data analysts to complete complex query and analysis tasks. Therefore, intelligent query and analysis systems based on natural language interaction are gradually becoming an important development direction for enterprise data products. Users can directly describe their business needs through natural language, and the system can automatically understand the user's intent and generate corresponding data query statements, thereby improving data utilization efficiency and analytical flexibility.

[0003] In existing Text-to-SQL solutions, filter conditions often correspond to underlying ID encoding. For example, the security level "undetermined" corresponds to sec_id_v=0. Large language models do not understand this encoding mapping. When a user asks for "files with a security level greater than undetermined," the large language model will incorrectly generate sec_name>'undetermined'. This leads to an incorrect lexicographical comparison ('secret' < 'undetermined' is lexicographically correct, but semantically, 'secret' has a higher security level than 'undetermined'). Therefore, due to this lack of understanding of the underlying ID encoding mapping, large language models suffer from a technical problem in generating correct SQL statements. Summary of the Invention

[0004] The main objective of this invention is to provide a multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method and apparatus, which aims to solve the problem of understanding the underlying ID encoding by large language models and ensure that large language models generate correct SQL statements.

[0005] To achieve the above objectives, the present invention proposes a multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method, comprising the following steps: Receive and preprocess the natural language query text input by the user to obtain a multidimensional query vector; Multi-path contextual retrieval is performed in parallel based on multi-dimensional query vectors to obtain a set of retrieval results. Among them, multi-path contextual retrieval includes filtering condition retrieval, which filters out several candidate results and generates a filtering condition string containing text values ​​based on the metadata corresponding to each candidate result. When the identifier column and identifier value in the metadata are both non-null values, the filtering condition string also includes a virtual key marker, an identifier column, and an identifier value. The search results of the search result set are assembled into system prompt words and input into the large language model to obtain the SQL generation instructions returned by the large language model; wherein, the system prompt words have virtual key overriding rules, the virtual key overriding rules are as follows: when the filter condition string contains virtual key markers, the large language model is instructed to use text values ​​to generate filter conditions when comparing equal values, and to switch to the identifier column and identifier values ​​to generate filter conditions when comparing ranges; Execute the SQL generation command.

[0006] In the above-described multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method, the filtering condition retrieval includes the following steps: Based on the multidimensional query vector, a first preset number of candidate results are retrieved from the filter value vector library to obtain the first candidate result set. For each candidate result in the first candidate result set, calculate the character-level partial match score between the original text value of the candidate result and the query text; Based on the character-level partial matching score, the candidate results of the first candidate result set are filtered and reordered to obtain the second candidate result set; Extract the metadata of each candidate result in the second candidate result set, and use the underlying identifier value and identifier column name in the metadata as the values ​​of the first virtual key tag and the second virtual key tag; Based on the key names, table names, field names, and text values ​​extracted from the metadata, assemble them into a basic filter condition string; If both the first virtual key marker and the second virtual key marker are non-empty, then the first virtual key marker, the second virtual key marker, and their respective values ​​are added to the basic filter condition string to obtain the filter condition string with virtual key markers.

[0007] In the aforementioned multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method, multi-path context retrieval also includes table structure definition retrieval, business rule retrieval, historical example retrieval, and domain terminology retrieval.

[0008] In the above-mentioned multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method, the recall quantity and confidence threshold for each type of retrieval are configured differently in the multi-path context retrieval.

[0009] In the above-described multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method, executing the SQL generation command includes the following steps: The SQL generation instruction is subjected to virtual column expansion processing to obtain the SQL statement after virtual column expansion; wherein, the virtual column expansion processing is to identify virtual columns marked with virtual columns in the SQL generation instruction and replace the identified virtual columns with their mapped underlying expressions; Execute the SQL statement after expanding the virtual columns.

[0010] In the above-mentioned multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method, the virtual column expansion process includes the following steps: A pre-configured mapping dictionary stores the correspondence between each virtual column name and its underlying expression; Parse SQL generation instructions to generate an abstract syntax tree; Traverse the abstract syntax tree to identify virtual column reference nodes marked with virtual column tags; The underlying expression is looked up from the mapping dictionary based on the name of the virtual column reference node; Parse the underlying expression to obtain the abstract syntax tree subtree corresponding to the underlying expression; Replace the virtual column reference nodes with the abstract syntax tree subtrees corresponding to the underlying expressions; Traverse the GROUP BY, ORDER BY, and LIMIT BY clauses in the abstract syntax tree, replace the virtual column names referenced in the clauses with their corresponding aliases, and obtain the SQL statement after expanding the virtual columns.

[0011] In the above-mentioned multi-path RAG context-weighted enhanced retrieval driven NL2SQL generation method, after receiving the natural language query text, it also includes identifying combined business queries based on the natural language query text. When it is determined to be a combined business query, a derived table routing identifier is added to the natural language query text. The system prompts are injected with the derivative table routing rules required for the combined service based on the derivative table routing identifier.

[0012] In the above-mentioned multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method, the combined business query identification includes the following steps: Predefined set of purposeful actions, set of derived actions, set of sensitive keywords, and set of sequential connectors; Extract elements from the standardized query text that match the set of target actions, the set of derived actions, the set of sensitive keywords, and the set of sequential connectors, and use them as the corresponding target action words, derived action words, sensitive keywords, and sequential connectors; When a target action word, a sequence conjunction word, and a derived action word are present simultaneously, or when a sensitive keyword and a target action word are present simultaneously, or when the same sentence in the query text contains a target action word and a derived action word, it is determined to be a combined business query. When only derived action words exist, the function call constructs the natural language query text into combined business query judgment prompt words; Input the combined business query judgment prompts into the large language model to obtain the judgment results returned by the large language model; and store the standardized query text and the judgment results returned by the large language model into the cache.

[0013] In the above-mentioned multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method, after the system prompt words are input into the large language model, the large language model will also return structured instructions; When a structured instruction is returned, it is parsed. If the instruction type is a table structure request, intermediate query, or user follow-up, the corresponding information completion operation is performed and the context is updated. The large language model is called again until the SQL generation instruction is obtained.

[0014] This application also discloses a multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation apparatus, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, it implements the above-mentioned multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method.

[0015] The technical solution provided by this invention may include the following beneficial effects: The multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method provided in this application enables large language models to generate correct SQL statements in range comparison queries without needing to understand the underlying ID encoding, through virtual key marking and virtual key overriding rules, thus solving the problem of large language models understanding the underlying ID encoding. Attached Figure Description

[0016] 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, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the structures shown in these drawings without creative effort.

[0017] Figure 1 This is a schematic diagram of the process for generating NL2SQL driven by multi-path RAG context-weighted enhanced retrieval in this invention; Figure 2 This is a schematic diagram of the filtering condition retrieval process in this invention; Figure 3 This is a schematic diagram of multi-path RAG context retrieval in this invention; Figure 4 This is a schematic diagram of the virtual column expansion process in this invention; Figure 5 This is a schematic diagram of the framework of the NL2SQL generation device driven by multi-path RAG context-weighted enhanced retrieval in this invention. Detailed Implementation

[0018] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0019] It should be noted that all directional indications (such as up, down, left, right, front, back, etc.) in the embodiments of the present invention are only used to explain the relative positional relationship and movement of each component in a certain specific posture (as shown in the figure). If the specific posture changes, the directional indication will also change accordingly.

[0020] In this invention, unless otherwise explicitly specified and limited, the terms "connection," "fixed," etc., should be interpreted broadly. For example, "fixed" can mean a fixed connection, a detachable connection, or an integral part; it can mean a mechanical connection or an electrical connection; it can mean a direct connection or an indirect connection through an intermediate medium; it can mean the internal communication of two components or the interaction between two components, unless otherwise explicitly limited. Those skilled in the art can understand the specific meaning of the above terms in this invention according to the specific circumstances.

[0021] Furthermore, in this invention, descriptions involving "first," "second," etc., are for descriptive purposes only and should not be construed as indicating or implying their relative importance or implicitly specifying the number of technical features indicated. Therefore, a feature defined with "first" or "second" may explicitly or implicitly include at least one of those features. Additionally, the word "and / or" throughout the text means including three parallel solutions; taking "A and / or B" as an example, it includes solution A, solution B, or a solution that simultaneously satisfies A and B. Furthermore, the technical solutions of the various embodiments can be combined with each other, but this must be based on the ability of those skilled in the art to implement them. When the combination of technical solutions is contradictory or impossible to implement, it should be considered that such a combination of technical solutions does not exist and is not within the scope of protection claimed by this invention.

[0022] The following is combined Figure 1This invention describes a multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method according to embodiments of the present invention, comprising the following steps: Receive and preprocess the natural language query text input by the user to obtain a multidimensional query vector; Parallel execution of multi-path contextual retrieval based on multi-dimensional query vectors yields a set of retrieval results. This multi-path contextual retrieval includes a filtering condition retrieval, which filters out several candidate results and generates a filtering condition string containing text values ​​based on the metadata corresponding to each candidate result. When both the identifier column and identifier value in the metadata are non-nullable, the filtering condition string also includes a virtual key marker, an identifier column, and an identifier value. For example, if the user inputs a natural language query text of "query files with a security level greater than undetermined," the retrieval results of the filtering condition retrieval will include a filtering condition string with a virtual key marker, an identifier column, and an identifier value. The table name is defined as follows: "[Security Level] sec_level.sec_name:'Unclassified' ##ID_VAL:0####ID_COL:sec_id_v##". Here, "Security Level" is the key name, "sec_level" is the table name, "sec_name" is the field name, "Unclassified" is the text value, ##ID_VAL## is the first virtual key marker, "0" is the identifier value corresponding to the first virtual key marker, ##ID_COL## is the second virtual key marker, and "sec_id_v" is the identifier column corresponding to the second virtual key marker.

[0023] The search results of the search result set are assembled into system prompt words and input into the large language model to obtain the SQL generation instructions returned by the large language model; wherein, the system prompt words have virtual key overriding rules, the virtual key overriding rules are as follows: when the filter condition string contains virtual key markers, the large language model is instructed to use text values ​​to generate filter conditions when comparing equal values, and to switch to the identifier column and identifier values ​​to generate filter conditions when comparing ranges; For example, the filter condition string is "[Security Level] sec_level.sec_name:'Unclassified' ##ID_VAL:0####ID_COL:sec_id_v##". When the user's natural language query text is "Query files with a security level greater than 'Unclassified'", it belongs to a range comparison. The virtual key overriding rule of the system prompt is triggered, and the filter condition generated by the large language model is "SELECT * FROM outgoing_view WHERE sec_id_v>0", that is, the filter condition is generated by the identifier column and identifier value in the filter condition string. When the user's natural language query text is "Query files with a security level equal to 'Unclassified'", it belongs to an equality comparison. The virtual key overriding rule of the system prompt is triggered, and the filter condition generated by the large language model is "SELECT * FROM outgoing_view WHERE sec_name='Unclassified'", that is, the filter condition is generated by the text value in the filter condition string.

[0024] The multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method provided in this application enables large language models to generate correct SQL statements in range comparison queries without needing to understand the underlying ID encoding, through virtual key marking and virtual key overriding rules, thus solving the problem of large language models understanding the underlying ID encoding.

[0025] Execute the SQL generation instruction. Preferably, executing the SQL generation instruction includes the following steps: performing virtual column expansion processing on the SQL generation instruction to obtain the SQL statement after virtual column expansion; wherein, the virtual column expansion processing involves identifying virtual columns marked with virtual column tags in the SQL generation instruction, replacing the identified virtual columns with their mapped underlying expressions; and executing the SQL statement after virtual column expansion. In this embodiment, by performing virtual column expansion processing on the SQL generation instruction, the large language model only needs to use simplified virtual column names (such as usr_alias_v) during the generation stage, greatly reducing the generation difficulty and error probability. This mechanism can flexibly extend the virtual column definition (only requiring modification of the VIRTUAL_DICT configuration) without retraining or adjusting the large language model.

[0026] In the NL2SQL generation method driven by multi-path RAG context-weighted enhanced retrieval provided by this invention, a filter condition string with virtual tags is generated by retrieving filter conditions. In one specific embodiment, preprocessing the user-input natural language query text includes the following processing: standardizing the natural language query text by removing stop words, retroflex endings, colloquial noise, etc., to reduce vector retrieval interference; and further processing the natural language query text... QPerform Chinese word segmentation to extract the keyword set W={w_1,w_2,...,w_n}; perform domain-based standardization on the natural language query text, mapping slang to standard terms. Embedding operations are then used to embed the natural language query text... Q Transform the query into a main query vector v_main, and then embed the natural language query text through an embedding operation. Q f Convert to a filter query vector v_filter, natural language query text Q f For natural language query text Q Remove time / quantity terms from the query text.

[0027] Optionally, the filtering criteria retrieval includes the following steps: Based on the multidimensional query vector, a first preset number of candidate results are retrieved from the filter value vector library to obtain the first candidate result set. For each candidate result in the first candidate result set, calculate the character-level partial match score between the original text value of the candidate result and the query text; Based on the character-level partial matching score, the candidate results of the first candidate result set are filtered and reordered to obtain the second candidate result set; Extract the metadata of each candidate result in the second candidate result set, and use the underlying identifier value and identifier column name in the metadata as the values ​​of the first virtual key tag and the second virtual key tag; Based on the key names, table names, field names, and text values ​​extracted from the metadata, assemble them into a basic filter condition string; If both the first virtual key marker and the second virtual key marker are non-empty, then the first virtual key marker, the second virtual key marker, and their respective values ​​are added to the basic filter condition string to obtain the filter condition string with virtual key markers.

[0028] In this embodiment, the matching accuracy of the filtered values ​​can be improved by using two stages of retrieval: semantic recall and character ranking.

[0029] For example, the algorithm for filtering condition retrieval is as follows: Input: Filter query text Q_f, filter query vector v_filter; Output: Filter criteria list C={(text_i,score_i,meta_i)} with confidence scores; Filter criteria retrieval R_filter using C as the filter criteria.

[0030] The filtering retrieval includes a semantic recall stage, a character refinement stage, and a virtual key binding stage, performed sequentially. The pseudocode for each stage is as follows: Semantic recall phase: C_raw=Search(Filter_Collection,v_filter,K_1,θ_f) Where K_1=30, and θ_f is the vector similarity threshold. The candidate set C_raw={c_1,c_2,...,c_30} is obtained. Character re-sorting stage: For each candidate c_j∈C_raw: Extract candidate raw text values: val_j = c_j.metadata.value Calculate character-level match score: score_char(c_j)=rapidfuzz.partial_ratio(Q_f.lower(),val_j.lower()) If score_char(c_j) < τ, then filter the candidate (τ = 50). Sort the remaining candidates in descending order of score_char, and take the first K_2=7. We obtain C_char={(c_j,score_char(c_j))|score_char(c_j)≥τ,j=1..7} Virtual key binding stage: For each candidate c_j∈C_char that passes the fine-sorting: Extract metadata: table_j=c_j.metadata.table field_j=c_j.metadata.field value_j = c_j.metadata.value key_j=c_j.metadata.key bound_id_val_j = c_j.metadata.bound_id_val / / Underlying ID value, which may be null bound_id_col_j = c_j.metadata.bound_id_col / / ID column name, may be null Assemble the basic filter criteria string: s_j="[{}]{}.{}:'{}'".format(key_j,table_j,field_j,value_j) If both bound_id_val_j and bound_id_col_j are not empty: s_j+="##ID_VAL:{}####ID_COL:{}##".format(bound_id_val_j,bound_id_col_j) Add (s_j, score_char(c_j), meta_j) to the result list C. Return to C For example, in an optional embodiment, after metadata extraction, if key_j is the security level, table_j is sec_level, field_j is sec_name, value_j is undetermined, bound_id_val_j is 0, and bound_id_col_j is sec_id_v, then the basic filter string is: [Security Level]sec_level.sec_name:'Undetermined'. The filter string with a virtual key identifier is: [Security Level]sec_level.sec_name:'Undetermined' ##ID_VAL:0####ID_COL:sec_id_v##.

[0031] As a preferred embodiment, in multi-path contextual retrieval, the recall quantity and confidence threshold for each type of retrieval are configured differently. That is, based on the differences in the importance of each path's contextual information to SQL generation, a differentiated recall quantity and confidence threshold are configured for each retrieval, thereby improving the overall retrieval quality through an implicit weighting strategy.

[0032] In some preferred embodiments, multi-path contextual retrieval includes, in addition to filtering condition retrieval, table structure definition retrieval, business rule retrieval, historical example retrieval, and domain terminology retrieval.

[0033] For table structure definition retrieval, since the table structure definition requires high accuracy, increasing the confidence threshold can prevent irrelevant tables from interfering with SQL generation.

[0034] For business rule retrieval, business rules are global, and a small number of highly relevant rules can cover the constraints. Therefore, the number of recalls and the confidence threshold can be reduced in business rule retrieval.

[0035] In historical example retrieval, historical examples are used to heuristically format the data. Too many historical examples can interfere with the generation logic, thus reducing the number of recalls.

[0036] For domain terminology retrieval, term mapping needs a certain generalization ability. If the threshold is too high, it will miss slang mappings. Therefore, the confidence threshold can be lowered.

[0037] More specifically, the pseudocode for table structure definition retrieval, business rule retrieval, historical example retrieval, and domain terminology retrieval are as follows: For table structure definition retrieval: For each word segment w_i, calculate the embedding vector v_i = Embed(w_i). Perform a batch vector search: R_ddl_raw = Search_Batch(DDL_Collection, {v_i}, K_ddl, θ_ddl) The batch results are deduplicated based on their content to obtain R_ddl Configuration: K_ddl=5, θ_ddl=0.8 For business rule retrieval: R_rule=Search(Rule_Collection,v_main,K_rule,θ_rule) Configuration: K_rule = 2, θ_rule = 0.5 For historical example retrieval: R_fewshot = Search(SQL_Collection, v_main, K_fewshot, θ_fewshot) Configuration: K_fewshot = 3, θ_fewshot = 0.7 Domain terminology retrieval: Construct a two-space query vector: `arget_vectors = {"term_vec": v_main, "desc_vec":v_main}` R_glossary=Prefetch_Search(Glossary_Collection,target_vectors, K_glossary,θ_glossary) Configuration: K_glossary = 7, θ_glossary = 0.5 Returns R = {R_ddl, R_rule, R_fewshot, R_filter, R_glossary} As an optional embodiment, the virtual column expansion process includes the following steps: A pre-configured mapping dictionary stores the correspondence between each virtual column name and its underlying expression; Parse SQL generation instructions to generate an abstract syntax tree; Traverse the abstract syntax tree to identify virtual column reference nodes marked with virtual column tags; The underlying expression is looked up from the mapping dictionary based on the name of the virtual column reference node; Parse the underlying expression to obtain the abstract syntax tree subtree corresponding to the underlying expression; Replace the virtual column reference nodes with the abstract syntax tree subtrees corresponding to the underlying expressions; Traverse the GROUP BY, ORDER BY, and LIMIT BY clauses in the abstract syntax tree, replace the virtual column names referenced in the clauses with their corresponding aliases, and obtain the SQL statement after expanding the virtual columns.

[0038] For example, the SQL generation command is: SELECT u.usr_alias_v AS “username”, COUNT(*) AS “outbound quantity” FROM outgoing_view_f AS o JOIN user AS u ON o.doc_usr_id = u.usr_id GROUP BY “username” Use sqlglot to parse SQL generation commands and generate an abstract syntax tree; Traversing the abstract syntax tree, we find that "u.usr_alias_v" ends with "_v" (_v is a virtual column identifier), meaning that u.usr_alias_v is a virtual column.

[0039] Find "usr_alias_v → "if(empty(usr_alias),usr_name, usr_alias)" in the VIRTUAL_DICT (mapping dictionary). Replace the `usr_alias_v` node with the abstract syntax tree subtree of `if(empty(usr_alias), usr_name, usr_alias)` to obtain the SQL statement after expanding the virtual columns. The SQL statement after expanding the virtual columns is as follows: SELECT if(empty(u.usr_alias), u.usr_name, u.usr_alias) AS “username”, COUNT(*) AS “outbound quantity” FROM outgoing_view_f AS o JOIN user AS u ON o.doc_usr_id = u.usr_id GROUP BY “username” In query auditing scenarios, many queries involve combined business semantics. For example, "sensitive files sent after decryption" involves a multi-step operation chain of "decrypt first, then send," requiring a derived chain summary table such as `trace_search`. Since the initial retrieval context often does not include the `trace_search` table, large language models are unable to generate SQL.

[0040] The present invention further includes, upon receiving the natural language query text, identifying combined business queries based on the natural language query text. When a combined business query is determined, a derivative table routing identifier is added to the natural language query text. The system prompt word is then injected with the derivative table routing rules required for the combined business based on the derivative table routing identifier. This enables the subsequent retrieval and recall of the derivative link summary table.

[0041] For example, if the user inputs a natural language query text of "querying sensitive files that have been decrypted and then sent out," after the combined business query identification process, it is determined to be a combined business query. The query text then becomes "in the derived table, query for sensitive files that have been decrypted and then sent out." The system prompt then injects the necessary derived table routing rules based on the derived table routing identifier.

[0042] The combined service query identification includes the following steps: Predefined set of purposeful actions, set of derived actions, set of sensitive keywords, and set of sequential connectors; Extract elements from the standardized query text that match the set of target actions, the set of derived actions, the set of sensitive keywords, and the set of sequential connectors, and use them as the corresponding target action words, derived action words, sensitive keywords, and sequential connectors; for example, sequential connectors can be extracted in the actual business logic according to the chronological order of the actions.

[0043] When a target action word, a sequence conjunction word, and a derived action word are present simultaneously, or when a sensitive keyword and a target action word are present simultaneously, or when the same sentence in the query text contains a target action word and a derived action word, it is determined to be a combined business query. When only derived action words exist, the function call constructs the natural language query text into combined business query judgment prompt words; Input the combined business query judgment prompts into the large language model to obtain the judgment results returned by the large language model; and store the standardized query text and the judgment results returned by the large language model into the cache.

[0044] Taking data security as an example, the combined business query identification algorithm is as follows: Input: Natural language query text Q; Predefined: The set of target actions D = {"Send to external drive", "Upload", "Copy to external drive", "Copy to network drive", ...}; The set of derived actions A = {"decrypt", "rename", "change extension", "compress", "remove watermark", ...}; The set of sensitive keywords M = {"sensitive", "sensitive document", "sensitive content"}; The set of sequential conjunctions C = {"after", "after", "then", "again", "and", "and"}; Output: whether it is a combined service flag is_combination, and the routing table name table_name.

[0045] Specifically, the steps of the combined business query identification algorithm include: Step 1: Query Standardization: Q_norm = Normalize(Q) / / Remove whitespace characters Step 2: Motion Extraction has_destination = any(d ∈ Q_norm for d ∈ D) has_derived = any(a ∈ Q_norm for a ∈ A) has_sensitive = any(m ∈ Q_norm for m ∈ M) Step 3: Rule layer combination determination: / / Judgment condition 1: Derivative action + conjunction + purpose action for a in A: if a ∈ Q_norm: for d in D: pattern = a + [optional conjunction] + d if Regex_Match(Q_norm, pattern): return (True, “trace_search”) / / Judgment Criterion 2: Sensitive content + Purposeful action if has_sensitive and has_destination: return (True, “trace_search”) / / Judgment condition 3: Derivative actions and target actions appear in the same sentence if has_derived and has_destination: return (True, “trace_search”) Step 4: AI-based fallback judgment: If config.TABLE_SELECTOR_USE_AI_FALLBACK = True: Furthermore, the judgment result is uncertain (a derivative action verb appears, but the rule does not match the combination pattern): prompt = Build_AI_Prompt(Q) is_combination = LLM_Classify(prompt) Cache_Result(Q_norm, is_combination) if is_combination: return (True, "trace_search") return (False, default_table) For example, the natural language query text is "query sensitive files that have been decrypted and then sent out". During the identification process, "decrypt" (derived action word) + "send out" (target action word) are extracted. This meets the judgment conditions in the rule layer and is determined to be a combined business query. Therefore, it is marked as a combined business query, and the query becomes "in the derived table, query sensitive files that have been decrypted and then sent out". The system prompt word is then injected with routing rules from the `trace_search` table.

[0046] When the initial retrieval context does not contain the `trace_search` table, SQL generation will fail. Therefore, this application also provides an algorithm for completing missing table structures through multiple rounds of dialogue.

[0047] In this embodiment, after the system prompt is input into the large language model, the large language model will also return a structured instruction. When a structured instruction is returned, the structured instruction is parsed. When the instruction type is a table structure request, intermediate query, or user follow-up question, the corresponding information completion operation is performed and the context is updated. The large language model is called again until the SQL generation instruction is obtained.

[0048] For example, the algorithm for completing missing table structures in a multi-turn dialogue is as follows: Input: User query text Q, system suggestion word P, session state State Output: Executable SQL statement or follow-up message Algorithm steps: Step 1: Initialization turns = 0 max_turns = MAX_TURNS / / e.g., 9 turns Step 2: Looping Dialogue while turns <max_turns: response = LLM_Generate(P, Q, State) Response fetches = Parse_Response(response) / / Parse into an array of commands for fetch in fetches: if fetch.type == SQL_GENERATION: / / type=1 return fetch.sql / / Retrieve SQL, end elif fetch.type == DDL_REQUEST: / / type=2 / / Retrieve the structure of the missing table from the DDL vector library missing_tables = fetch.table_names for t in missing_tables: if t not in State.loaded_tables: ddl = Search_DDL(t) State.loaded_tables.add(t) P += ddl / / Append to the context of the prompt word elif fetch.type == MID_QUERY: / / type=3 / / Execute intermediate SQL to retrieve dynamic data mid_sql = fetch.sql mid_result = Execute_SQL(mid_sql) State.mid_results.append(mid_result) P += Format_Result(mid_result) / / Inject context elif fetch.type == USER_CLARIFY: / / type=4 / / Return to follow-up message and wait for user reply return fetch.question / / Interactive follow-up question turns += 1 / / SQL not obtained even after reaching the maximum round return Error("Max turns exceeded") In this embodiment, the algorithm for completing missing table structures through multi-turn dialogue achieves automatic context expansion using the type=2 (DDL request) and type=3 (intermediate query) instructions. Even if the initial context does not contain the table structure required for the combined business logic, the system can automatically complete it through multiple rounds of interaction, ultimately generating the correct SQL statement. This significantly expands the query complexity boundary that the system can support.

[0049] As a preferred embodiment, this application also provides a multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation system to execute the above-described multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method, including: The query preprocessing module is used to receive and preprocess the natural language query text input by the user; The five-way parallel contextual search engine includes a table structure definition search engine, a business rule search engine, a historical example search engine, a filter condition search engine, and a domain terminology search engine. The prompt word assembler is used to format search results into system prompt words according to a template. A multi-turn dialogue controller is used to parse the structured instructions returned by the large language model and execute state transitions; The SQL normalization engine is used to perform virtual column expansion processing on SQL generation commands; The SQL executor is used to execute SQL statements after virtual columns have been expanded.

[0050] The system described in the above embodiments of the present invention can be used to execute the corresponding method embodiments of the present invention and achieve the technical effects achieved by the above method embodiments of the present invention, which will not be repeated here.

[0051] In this embodiment of the invention, the relevant functional modules can be implemented using a hardware processor.

[0052] like Figure 5As shown, another aspect of the present invention discloses a multi-channel RAG context-weighted enhanced retrieval driven NL2SQL generation device 500, including a processor 501 and a memory 502. The processor 501 and the memory 502 are connected, for example, via a bus 503. Further, the electronic device 500 may also include a transceiver 504. It should be noted that in practical applications, the transceiver 504 is not limited to one, and the structure of the multi-channel RAG context-weighted enhanced retrieval driven NL2SQL generation device 500 does not constitute a limitation on the embodiments of this application. The processor 501 is used in the embodiments of this application to implement the multi-channel RAG context-weighted enhanced retrieval driven NL2SQL generation method. The processor 501 can be a CPU, a general-purpose processor, a DSP, an ASIC, an FPGA, or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. It can implement or execute various exemplary logic blocks, modules, and circuits described in conjunction with the disclosure of this application. The processor 501 can also be a combination that implements computing functions, such as including one or more microprocessor combinations, a combination of a DSP and a microprocessor, etc. Bus 503 may include a pathway for transmitting information between the aforementioned components. Bus 503 may be a PCI bus or an EISA bus, etc. Bus 503 may be divided into address bus, data bus, control bus, etc. For ease of representation, Figure 5 The term "bus" is represented by a single thick line, but this does not imply that there is only one bus or one type of bus. The memory 502 can be ROM or other types of static storage devices capable of storing static information and instructions, RAM or other types of dynamic storage devices capable of storing information and instructions, or it can be EEPROM, CD-ROM or other optical disc storage, optical disk storage (including compressed optical disks, laser discs, optical discs, digital universal optical discs, Blu-ray discs, etc.), magnetic disk storage media or other magnetic storage devices, or any other medium capable of carrying or storing desired program code in the form of instructions or data structures and accessible by a computer, but is not limited thereto. The memory 502 is used to store application code that executes the scheme of this application, and its execution is controlled by the processor 501. The processor 501 is used to execute the application code stored in the memory 502 to implement the multi-channel RAG context-weighted enhanced retrieval driven NL2SQL generation method provided by this invention.

[0053] The above description is only a preferred embodiment of the present invention and does not limit the patent scope of the present invention. All equivalent structural transformations made under the concept of the present invention using the contents of the present invention specification and drawings, or direct / indirect applications in other related technical fields, are included within the patent protection scope of the present invention.

Claims

1. A multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation method, characterized by: Includes the following steps: Receive and preprocess the natural language query text input by the user to obtain a multidimensional query vector; Multi-path contextual retrieval is performed in parallel based on multi-dimensional query vectors to obtain a set of retrieval results. Among them, multi-path contextual retrieval includes filtering condition retrieval, which filters out several candidate results and generates a filtering condition string containing text values ​​based on the metadata corresponding to each candidate result. When the identifier column and identifier value in the metadata are both non-null values, the filtering condition string also includes a virtual key marker, an identifier column, and an identifier value. The search results of the search result set are assembled into system prompt words and input into the large language model to obtain the SQL generation instructions returned by the large language model; wherein, the system prompt words have virtual key overriding rules, the virtual key overriding rules are as follows: when the filter condition string contains virtual key markers, the large language model is instructed to use text values ​​to generate filter conditions when comparing equal values, and to switch to the identifier column and identifier values ​​to generate filter conditions when comparing ranges; Execute the SQL generation command; The filtering criteria retrieval includes the following steps: Based on the multidimensional query vector, a first preset number of candidate results are retrieved from the filter value vector library to obtain the first candidate result set. For each candidate result in the first candidate result set, calculate the character-level partial match score between the original text value of the candidate result and the query text; Based on the character-level partial matching score, the candidate results of the first candidate result set are filtered and reordered to obtain the second candidate result set; Extract the metadata of each candidate result in the second candidate result set, and use the underlying identifier value and identifier column name in the metadata as the values ​​of the first virtual key tag and the second virtual key tag; Based on the key names, table names, field names, and text values ​​extracted from the metadata, assemble them into a basic filter condition string; If both the first virtual key marker and the second virtual key marker are non-empty, then the first virtual key marker, the second virtual key marker, and their respective values ​​are added to the basic filter condition string to obtain the filter condition string with virtual key markers.

2. The NL2SQL generation method driven by multi-path RAG context-weighted enhanced retrieval according to claim 1, characterized in that: Multi-path contextual retrieval also includes table structure definition retrieval, business rule retrieval, historical example retrieval, and domain terminology retrieval.

3. The NL2SQL generation method driven by multi-path RAG context-weighted enhanced retrieval according to any one of claims 1-2, characterized in that: In multi-path contextual retrieval, the recall quantity and confidence threshold for each retrieval category are configured differently.

4. The NL2SQL generation method driven by multi-path RAG context-weighted enhanced retrieval according to claim 1, characterized in that: Executing SQL generation commands involves the following steps: The SQL generation instruction is subjected to virtual column expansion processing to obtain the SQL statement after virtual column expansion; wherein, the virtual column expansion processing is to identify virtual columns marked with virtual columns in the SQL generation instruction and replace the identified virtual columns with their mapped underlying expressions; Execute the SQL statement after expanding the virtual columns.

5. The NL2SQL generation method driven by multi-path RAG context-weighted enhanced retrieval according to claim 4, characterized in that: The virtual column expansion process includes the following steps: A pre-configured mapping dictionary stores the correspondence between each virtual column name and its underlying expression; Parse SQL generation instructions to generate an abstract syntax tree; Traverse the abstract syntax tree to identify virtual column reference nodes marked with virtual column tags; The underlying expression is looked up from the mapping dictionary based on the name of the virtual column reference node; Parse the underlying expression to obtain the abstract syntax tree subtree corresponding to the underlying expression; Replace the virtual column reference nodes with the abstract syntax tree subtrees corresponding to the underlying expressions; Traverse the GROUP BY, ORDER BY, and LIMIT BY clauses in the abstract syntax tree, replace the virtual column names referenced in the clauses with their corresponding aliases, and obtain the SQL statement after expanding the virtual columns.

6. The NL2SQL generation method driven by multi-path RAG context-weighted enhanced retrieval according to claim 1, characterized in that: After receiving the natural language query text, the process also includes identifying combined business queries based on the natural language query text. When a combined business query is determined, a derived table routing identifier is added to the natural language query text. The system prompts are injected with the derivative table routing rules required for the combined service based on the derivative table routing identifier.

7. The NL2SQL generation method driven by multi-path RAG context-weighted enhanced retrieval according to claim 6, characterized in that: The combined service query identification includes the following steps: Predefined set of purposeful actions, set of derived actions, set of sensitive keywords, and set of sequential connectors; Extract elements from the standardized query text that match the set of target actions, the set of derived actions, the set of sensitive keywords, and the set of sequential connectors, and use them as the corresponding target action words, derived action words, sensitive keywords, and sequential connectors; When a target action word, a sequence conjunction word, and a derived action word are present simultaneously, or when a sensitive keyword and a target action word are present simultaneously, or when the same sentence in the query text contains a target action word and a derived action word, it is determined to be a combined business query. When only derived action words exist, the function call constructs the natural language query text into combined business query judgment prompt words; Input the combined business query judgment prompts into the large language model to obtain the judgment results returned by the large language model; and store the standardized query text and the judgment results returned by the large language model into the cache.

8. The NL2SQL generation method driven by multi-path RAG context-weighted enhanced retrieval according to claim 1, characterized in that: After the system prompts the input words into the large language model, the large language model will also return structured instructions; When a structured instruction is returned, it is parsed. If the instruction type is a table structure request, intermediate query, or user follow-up, the corresponding information completion operation is performed and the context is updated. The large language model is called again until the SQL generation instruction is obtained.

9. A multi-path RAG context-weighted enhanced retrieval-driven NL2SQL generation device, characterized in that, The method includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the program, implements the NL2SQL generation method driven by the multi-path RAG context-weighted enhanced retrieval as described in any one of claims 1-8.

Images