Text2sql accuracy improvement method and system based on multi-path retrieval mode link

Abstract

The application discloses a text2sql accuracy improvement method and system based on a multi-path retrieval mode link, belongs to the natural language processing and database query technical field, and comprises the following steps: collecting and structuring metadata to construct a data knowledge graph, constructing an NLP dictionary and a domain knowledge base and vectorizing storage; receiving a user question and utilizing a large model to combine domain knowledge to perform semantic expansion and ambiguity elimination; recalling metadata nodes from a vector library through three parallel channels of original input, rewritten questions and NLP word segmentation; grouping the recalled nodes, searching and constructing multiple "path subgraphs" representing potential correlation in the knowledge graph; introducing a dynamic weight adjustment mechanism to calculate the correlation score of each path subgraph with the user query and select the path subgraph with the highest score, extract the correlation contained in the path subgraph, and provide the accurate mode link result to a subsequent SQL generation module, so that the accuracy and efficiency of text to SQL conversion are effectively improved.

Description

Technical Field

[0001] This invention relates to the fields of natural language processing and database query technology, specifically to a method and system for improving the accuracy of text2sql based on multi-path retrieval pattern linking. Background Technology

[0002] In today's big data era, enterprises and organizations have accumulated massive amounts of data resources. While this data possesses enormous potential value, the complexity and specialized nature of SQL technology makes it difficult for ordinary users to directly access and analyze it, thus limiting its widespread application and value realization. Database queries typically require users to have professional SQL knowledge, which means that many business personnel without technical backgrounds cannot independently complete data query tasks, severely restricting data analysis efficiency and business decision-making. To address this contradiction, intelligent database query technology has emerged, especially natural language to SQL (text2sql) technology. This technology allows computers to understand questions described by humans in natural language and automatically generate corresponding, executable SQL query statements, greatly lowering the technical barrier to data querying.

[0003] Currently, mainstream intelligent query methods fall into two main categories: one is template-based, which uses predefined query templates and rules or models to fill slots. This method is simple to implement but supports a limited range of query types. The other relies on the model learning the mapping relationship between natural language and SQL statements, directly generating SQL queries from natural language. This method is more flexible but places stricter demands on the model's capabilities. It's worth noting that both methods require pre-determining the data tables related to the user's question. However, widely used open-source benchmark datasets, such as Spider, WikiSQL, and Bird, primarily evaluate the model's ability to generate SQL, neglecting the crucial step of "schema linking." Real-world queries often target large-scale databases with a vast number of tables, making it impossible to input the schema information of all tables into the model. Therefore, accurately identifying relevant data tables becomes a critical challenge.

[0004] A thorough analysis of existing pattern linking methods reveals the following limitations: While RAG-based recall methods are relatively simple to implement, they fail to adequately model the relationships between tables and lack in-depth analysis of the dimensional values ​​implicit in user queries, resulting in poor performance in multi-table tasks. Model fine-tuning methods, while significantly improving pattern linking accuracy, suffer from difficulties in constructing training data and require pre-learning all table information. This approach may underperform with database expansion, making it more suitable for scenarios with relatively fixed table structures. Furthermore, most existing research focuses on the SQL generation stage, often assuming that the target data tables are known or involve only a limited number of tables. This idealized assumption differs significantly from real-world application scenarios.

[0005] In summary, current text2sql technology faces significant challenges in schema linking during practical applications. Schema linking, a core technology in text-to-SQL generation, aims to identify relevant tables and fields from a database based on natural language queries, filtering out irrelevant parts to reduce input complexity and noise interference. Existing methods suffer from insufficient modeling of inter-table relationships, inadequate parsing of implicit information, and limited support for system scalability. Therefore, there is an urgent need to research a schema linking method capable of accurately and efficiently identifying relevant tables and their relationships in large-scale database environments, enabling the true application and value realization of intelligent database query technology in real-world business scenarios. Summary of the Invention

[0006] This invention addresses the shortcomings of existing technologies by providing a method and system for improving the accuracy of text2sql based on multi-path retrieval mode linking. This method enables accurate identification of tables and columns related to user questions in large-scale databases, thereby improving the accuracy of intelligent database queries. Specifically, it is achieved through the following technical solution: In one aspect, the present invention provides a method for improving the accuracy of text2sql based on multi-path retrieval pattern linking, the method comprising: S1: Data preparation and knowledge construction: Collect metadata from the database, integrate and structure the collected metadata into a unified data knowledge graph, and build an NLP dictionary and a professional domain knowledge base. Vectorize the data knowledge graph, NLP dictionary and professional domain knowledge base and store them in a vector library. S2: User Question Semantic Processing: Receives the user's original natural language question input, performs semantic expansion and ambiguity removal on the original user input question based on relevant knowledge in the professional domain knowledge base, and combines a large language model LLM to obtain a precisely rewritten question; S3: Multi-path recall mode linking: Based on the user's original input question, the rewritten question, and NLP word segmentation technology, metadata nodes related to the user's question are recalled from the vector library through three parallel recall channels to complete the initial linking of the recall mode; S4: Path Subgraph Construction: Group the collected metadata nodes related to user intent by table, field, and dimension value, and use the directed path graph in the knowledge graph to construct multiple path subgraphs to represent potential association paths. S5: Pathway Subgraph Scoring and Ranking: For each constructed pathway subgraph, a relevance evaluation is performed, and a relevance score is calculated between each pathway subgraph and the corresponding user query. All pathway subgraphs are then ranked according to their relevance scores. S6: Schema Locking and Output: Based on the calculated score results, select the highest-scoring pathway subgraph to construct the final schema information, including a list of tables, field names, data types, comments, enumeration values, and table relationships, as input to text2sql, thereby improving the accuracy and efficiency of text2sql.

[0007] Specifically, in S1: The metadata includes data table structure information, historical SQL execution records, ETL processes, business metric definitions, and professional business terminology. The process involves integrating and structuring the collected metadata into a unified data knowledge graph, simultaneously constructing an NLP dictionary and a domain-specific knowledge base, and vectorizing the data knowledge graph, NLP dictionary, and domain-specific knowledge base and storing them in a vector library. S101: Construct a data knowledge graph based on data table structure information and SQL parsing results. This includes constructing a single-table graph structure using data table structure information, parsing SQL statements using JSqlParser / ANTLR tools based on historical SQL execution records and ETL processes to identify the relationships between tables, structuring the relationships between tables into RDF triples through the single-table graph structure, and storing them in the knowledge graph to obtain the data knowledge graph. S102: Constructing the NLP dictionary: Based on rules and large language model LLM, identify the dimension value field column in the data table structure information, query all dimension values ​​and perform generalization processing, redefine the NLP dictionary structure, and put the processed dimension values ​​into the newly defined NLP dictionary for storage; S103: Construct a professional domain knowledge base based on indicator caliber algorithms and business terminology; S104: Construct a vector index, which vectorizes the data knowledge graph, NLP dictionary, and professional domain knowledge base using a vector model and stores them in a vector library.

[0008] Furthermore, in S101, the RDF triple includes a table node triple, a table association triple, and a dimension triple, wherein the table node triple includes an attribute, a containment, and a field; the table association triple includes a field, a table association, and a field; and the dimension triple includes an attribute, a value, and a dimension value.

[0009] Furthermore, in S102: The rule-based recognition includes recognizing character types, having a number of values ​​less than or equal to 100, and having a dimension value field column length less than or equal to 10 characters; The LLM-based recognition includes constructing prompt words from field descriptions and sampled partial values, which are then recognized by LLM. The process of generalizing all retrieved dimension values ​​includes: LLM semantic generalization of dimension values ​​to align them with natural language, and generalized translation of non-Chinese dimension values ​​to conform to the user's questioning habits. The redefined NLP dictionary structure involves redefining the original definition of "word frequency" into "dimension value, field column ID group, and weight value", where the weight value is 1 by default.

[0010] Specifically, in S2: The process involves semantically expanding and disambiguating user questions based on relevant knowledge from a professional domain knowledge base, combined with a large-scale language model (LLM), to obtain accurately rewritten questions. Specifically, this includes: By using vector retrieval, the reordering model retrieves relevant domain knowledge from a domain knowledge base. It combines the retrieved knowledge with the user input to obtain prompt words. These prompt words are then fed into the LLM for semantic rewriting and deep semantic association, forming a rewritten question. This provides a foundation for accurate semantic matching in subsequent retrievals.

[0011] Specifically, in S3: The process of recalling metadata nodes related to user questions from the vector library through three parallel recall channels to complete the initial linking of the recall mode includes: User input vector retrieval: Vectorize the user's original input question and retrieve semantically similar metadata nodes from the vector library, including tables, fields, and dimension values; Semantic rewriting vector recall: The rewritten question is vectorized and retrieved again in the vector library to capture deeper semantic relationships; NLP segmentation for precise targeting and recall: Using NLP segmentation technology, keywords in the query are precisely matched, and the dimension values ​​and table names that match exactly are retrieved directly from the NLP dictionary.

[0012] Furthermore, when recalling data via user input vectors and semantic rewriting vectors, a strategy combining full-text recall and text chunk recall can be employed. Specifically, a threshold can be set based on the character length of the query intent text. When the length is less than or equal to the set threshold, full-text recall is used; when it is greater than the set threshold, text chunk recall is used. Full-text recall involves vectorizing the query intent text into a full-text vector and then searching the vector library to obtain the search data. Text chunking retrieval involves segmenting and quantizing the query intent text, then searching a vector database. For each segment, the top 10 results are retrieved. Finally, all segmented retrieval results are fed into a re-ranking model for rearrangement. The data.

[0013] Furthermore, the query intent text is segmented, and the segment size is the median value of the text length of all nodes in the knowledge graph, in order to prevent the loss of key information due to semantic sparsity caused by text length.

[0014] Specifically, S4 also includes: S401: Traverse the dimension value group, find the field to which each dimension value belongs, form a 'field-dimensional value' association path, and add the dimension value to the corresponding field group; S402: Traverse the table group, find the field belonging to the corresponding form from the field group, and form the 'table-field' association path; S403: Take a table node from the table group to form a single-node graph. Starting from the current node, search the knowledge graph for nodes in the remaining node group that have a reachable path from the current node to form a path subgraph. S404: Repeat step S403 to associate all table relationships and form multiple pathway graphs.

[0015] Specifically, in S5: The relevance evaluation of each constructed path subgraph, and the calculation of the relevance score between each path subgraph and the corresponding user query, also includes: A dynamic weight adjustment mechanism was introduced, which adjusts the weight of each recall channel in the total score in real time based on the average score performance of each channel. Among them, the recall channels with reliable and accurate performance receive higher weights, which can more accurately reflect the matching degree between the channel subgraph and the user query.

[0016] Furthermore, the dynamic weight adjustment mechanism specifically includes: Let the result set of each pathway subgraph be , ,in, Indicates the first path in the path subgraph Relevance score of NLP recall for each node; Indicates the first path in the path subgraph Relevance score of node vector recall; Indicates the first path in the path subgraph Relevance score of knowledge graph recall for each node; Indicates the first path in the path subgraph The relevance score of all recalls for each node; Indicates the first path in the path subgraph One node; Indicates the first path in the path subgraph NLP recall nodes for each node; Indicates the first path in the path subgraph The vector recall node of each node; Indicates the first path in the path subgraph The knowledge graph of each node recalls the node; , A natural number, representing the total number of nodes; Set the recall weight based on NLP matching as The vector recall weights based on user input are: The recall weight based on semantic expansion is The adjustment factor is Let the dynamic weight adjustment value for the recall weight of NLP matching be denoted as . The dynamic weight adjustment value for the user input vector recall weight is... Dynamic weight adjustment value for recall weight of semantic expansion ; Based on the adjusted dynamic weights, the adjusted scoring formula is obtained, and the relevance scores of all pathway subgraphs are calculated.

[0017] Furthermore, dynamically adjust the weight values. , , The calculation expression is,

[0018] In the formula, This indicates the number of nodes recalled by NLP; Indicates the number of nodes recalled by the vector; This represents the number of nodes recalled by the knowledge graph.

[0019] Furthermore, the adjusted scoring formula expression is as follows:

[0020] In the formula, This indicates the path subgraph after weight adjustment. Relevance score of each node; This represents the total number of nodes.

[0021] Based on the adjusted scoring formula, the relevance score set of all pathway subgraphs is calculated { , ... }

[0022] In another aspect, based on the same inventive concept as the first aspect of the present invention, a text2sql accuracy improvement system based on multi-path retrieval pattern linking is provided, the system comprising: The data processing module is used to collect metadata from the database, integrate the collected metadata and structure it into a unified data knowledge graph, and build an NLP dictionary and a professional domain knowledge base. The data knowledge graph, NLP dictionary and professional domain knowledge base are vectorized and stored in a vector library. The semantic processing module receives the user's original natural language question, and performs semantic expansion and ambiguity removal on the user's original input question based on relevant knowledge in the professional domain knowledge base and combined with the large language model LLM, to obtain the accurately rewritten question; The recall module is used to recall metadata nodes related to the user's question from the vector library through three parallel recall channels based on the user's original input question, the rewritten question, and NLP word segmentation technology, thus completing the initial linking of the recall mode. The subgraph construction module is used to group the collection of metadata nodes related to user intent by table, field, and dimension value, and use the directed path graph in the knowledge graph to construct multiple path subgraphs for these recalled metadata nodes to represent potential association paths. The scoring and ranking module is used to evaluate the relevance of each constructed path subgraph, calculate the relevance score of each path subgraph to the corresponding user query, and sort all path subgraphs according to the relevance score. The schema output module is used to select the highest-scoring pathway subgraph based on the calculated score to construct the final schema information, including a list of tables, field names, data types, comments, enumeration values, and table relationships, as input to text2sql, thereby improving the accuracy and efficiency of text2sql.

[0023] Compared with the prior art, the present invention has the following advantages: First, by combining three parallel recall channels—NLP word segmentation for precise targeting, user's original input vector recall, and semantic expansion vector recall—it is possible to capture user query intent more comprehensively from multiple dimensions, significantly improving the recall coverage and accuracy of relevant metadata nodes (such as tables, fields, and dimension values).

[0024] Secondly, by utilizing a dynamic weight adjustment mechanism, the weight allocation of each recall channel in the final score can be optimized in real time based on its actual performance (number of recall nodes and average score), ensuring that the scoring results more objectively and reliably reflect the matching degree between the path subgraph and the query.

[0025] Finally, compared with existing methods of dependency model fine-tuning or simple RAG recall, this invention not only fully considers the complex relationships between data tables (through knowledge graph modeling), but also performs in-depth analysis and generalization processing on the implicit dimension value information in user queries. This enables it to accurately and efficiently identify relevant data tables and relationship structures even in large-scale, dynamically changing data table environments, thereby providing more accurate and complete pattern information for subsequent text2sql generation. This effectively solves key problems in existing technologies such as insufficient modeling of inter-table relationships, weak implicit information parsing capabilities, and limited system scalability.

[0026] Other features and advantages of the invention will be set forth in the description which follows, and will be apparent in part from the description, or may be learned by practicing the invention. The objects and other advantages of the invention may be realized and obtained by means of the structures pointed out in the description, claims and drawings. Attached Figure Description

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

[0028] Figure 1 This is a flowchart illustrating a text2sql accuracy improvement method based on multi-path retrieval mode linking in Embodiment 1 of the present invention; Figure 2 This is a schematic diagram of the data knowledge graph structure in Embodiment 1 of the present invention; Figure 3 This is a schematic diagram of the process from multi-path recall results to the construction of multiple path subgraphs in Embodiment 2 of the present invention; Figure 4 This is a schematic diagram of the pathway subgraph structure result in Embodiment 2 of the present invention. Detailed Implementation

[0029] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, 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 some embodiments of the present invention, not all 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.

[0030] Example 1: In this embodiment, please refer to Figure 1 This paper provides a method for improving the accuracy of text2sql based on multi-path retrieval pattern linking. The method includes the following steps: Step S1: Data preparation and knowledge construction: Collect metadata from the database, integrate and structure the collected metadata into a unified data knowledge graph, and at the same time, build an NLP dictionary and a professional domain knowledge base, vectorize the data knowledge graph, NLP dictionary and professional domain knowledge base and store them in a vector library. Furthermore, the metadata includes data table structure information, historical SQL execution records, ETL processes, business metric definitions, and professional business terminology; Furthermore, step S1 also includes: Step S101: Construct a data knowledge graph based on the data table structure information and SQL parsing results. This includes constructing a single-table graph structure using the data table structure information, parsing SQL statements (such as join on statements) using JSqlParser / ANTLR tools based on historical SQL execution records and ETL processes to identify the relationships between tables, structuring the relationships between tables into RDF triples through the single-table graph structure and storing them in the knowledge graph to obtain the data knowledge graph. Furthermore, the RDF triples include table node triples, table association triples, and dimension triples, wherein the table node triples include an expression, contain, and field; the table association triples include a field, table association, and field; and the dimension triples include an expression, value, and dimension value.

[0031] Furthermore, in this embodiment, historical SQL statements are analyzed when constructing the knowledge graph. For example, if 'SELECT ...' exists in the historical SQL... In this application, a query like 'FROM order_table o JOIN product_table p ON o.product_id = p.product_id' will be parsed using tools such as 'JSqlParser' or 'ANTLR' to identify the relationship between 'order_table' and 'product_table' through the 'product_id' field. This relationship is then structured into RDF triples and stored in a knowledge graph. When a user query involves 'order' and 'product', the subsequent S4 step can utilize this known relationship to connect 'order_table' and 'product_table' to construct a meaningful pathway subgraph, such as 'order' table → 'product' table.

[0032] Furthermore, in this embodiment, as Figure 2 As shown, Figure 2 This is a schematic diagram of the data knowledge graph structure, by Figure 2 It can be seen that the data knowledge graph includes three types of nodes and three types of node relationships, forming a three-layer semantic network, in which: The three types of nodes are: table node (t), which represents the basic unit of data storage and corresponds to the data table in the database; field node (f), which represents the column in the table, with each field node belonging to a table node, reflecting the "inclusion" relationship; and dimension value node (d), which represents the specific value or classification dimension of the field and forms a "value" relationship with the field node. The three types of relationships are: table nodes and field nodes are connected by containment edges; field nodes and dimension value nodes are connected by value-taking edges; and field nodes can be further connected by association edges to represent foreign keys, data flow, or business logic relationships.

[0033] Step S102: Construct NLP dictionary: Based on rules and large language model LLM, identify the dimension value field column in the data table structure information, query all dimension values ​​and perform generalization processing, redefine the NLP dictionary structure and put the processed dimension values ​​into the newly defined NLP dictionary for storage; Furthermore, in the dimension value field column of the rule-based and large language model LLM recognition table structure, rule-based recognition includes recognizing character types and the number of values ​​being less than or equal to 100, and the length of the dimension value field column being less than or equal to 10 characters; LLM-based recognition includes constructing prompt words from field descriptions and sampled values, which are then recognized by LLM.

[0034] Furthermore, all retrieved dimension values ​​are generalized, including: LLM semantic generalization of dimension values ​​to align them with natural language, and generalized translation of non-Chinese dimension values ​​to conform to the user's questioning habits.

[0035] Furthermore, the redefined NLP dictionary structure involves redefining the original "word frequency" of the NLP dictionary as "dimension value, field column ID group, and weight value," where the weight value defaults to 1. See Table 1 below for an example of the newly defined NLP dictionary structure: Table 1: Examples of New Definition Structures in NLP Dictionaries

[0036]

[0037] Furthermore, the storage structure of the dimension value field is shown in Table 2 below: Table 2: Storage structure of dimension value fields.

[0038]

[0039] Step S103: Construct a professional domain knowledge base based on indicator caliber algorithms and business terminology; Furthermore, the storage structure of the aforementioned professional domain knowledge base is as follows: {query: 'Technical terminology / indicator algorithm', desc: 'Explanation'}; Furthermore, in this embodiment, the storage structure of the professional domain knowledge base is exemplified as follows: {query:'auxiliary products',desc:'eSim, Universal Studios, Disney, Qingyunze, Happy Valley one-day tour'}; {query: 'Popular Products', desc: 'max (order quantity) order by desc limt 3'}.

[0040] Step S104: Construct a vector index by vectorizing the data knowledge graph, NLP dictionary, and professional domain knowledge base using a vector model and storing them in a vector library.

[0041] Step S2: Semantic processing of user questions: Receive the user's original natural language question input, and based on relevant knowledge in the professional domain knowledge base, combine it with a large language model LLM to perform semantic expansion and ambiguity removal on the user's original input question to obtain a accurately rewritten question; Furthermore, the process of semantically expanding and disambiguating user questions based on relevant knowledge in a professional domain knowledge base, combined with a large-scale language model (LLM), to obtain accurately rewritten questions, specifically includes: using vector retrieval, a reordering model to obtain professional domain knowledge related to user intent from the professional domain knowledge base, combining the retrieved knowledge with user input to obtain prompt words, inputting the prompt words into the LLM for semantic rewriting and deep semantic association, forming rewritten questions, and thus providing an accurate semantic matching foundation for subsequent retrieval.

[0042] Furthermore, in this embodiment, when the user inputs "What are the most popular products among the ancillary products in the last three months?", the system retrieves the domain knowledge "Ancillary products: eSim, Universal Studios, Disney, Qingyunze, Happy Valley day trip, popular products = max(order volume) order by desclimt 3" after vector retrieval and rearrangement. This retrieved domain knowledge and the user input are combined into a prompt word, which is then fed into the LLM model and expanded to "Query the top 3 products by order volume among the ancillary products eSim, Universal Studios, Disney, Qingyunze, and Happy Valley day trip in the last three months".

[0043] Step S3: Multi-path recall mode linking: Based on the user's original input question, the rewritten question, and NLP word segmentation technology, metadata nodes related to the user's question are recalled from the vector library through three parallel recall channels to complete the initial linking of the recall mode; Furthermore, the process of recalling metadata nodes related to user questions from the vector library through three parallel recall channels to complete the initial linking of the recall mode includes: User input vector retrieval: Vectorize the user's original input question and retrieve semantically similar metadata nodes from the vector library, including tables, fields, and dimension values; Semantic rewriting vector recall: The rewritten question is vectorized and retrieved again in the vector library to capture deeper semantic relationships; NLP segmentation for precise targeting and recall: Using NLP segmentation technology, keywords in the query are precisely matched, and the dimension values ​​and table names that match exactly are retrieved directly from the NLP dictionary.

[0044] Furthermore, when recalling data via user input vectors and semantic rewriting vectors, a strategy combining full-text recall and text chunk recall can be employed. Specifically, a threshold can be set based on the character length of the query intent text. When the length is less than or equal to the set threshold, full-text recall is used; when it is greater than the set threshold, text chunk recall is used. Full-text recall involves vectorizing the query intent text into a full-text vector and then searching the vector library to obtain the search data. Text chunking retrieval involves segmenting and quantizing the query intent text, then searching a vector database. For each segment, the top 10 results are retrieved. Finally, all segmented retrieval results are fed into a re-ranking model for rearrangement. The data.

[0045] Furthermore, the query intent text is segmented, and the segment size is the median value of the text length of all nodes in the knowledge graph, in order to prevent the loss of key information due to semantic sparsity caused by text length.

[0046] Step S4: Path Subgraph Construction: Group the recalled metadata nodes related to user intent by table, field, and dimension value, and use the directed path graph in the knowledge graph to construct multiple path subgraphs to represent potential association paths. Furthermore, step S4 also includes: Step S401: Traverse the dimension value group, find the field to which each dimension value belongs, form a 'field-dimensional value' association path, and add the dimension value to the corresponding field group; Step S402: Traverse the table group, find the field belonging to the corresponding form from the field group, and form the 'table-field' association path; Step S403: Take a table node from the table group to form a single-node graph. Starting from the current node, search the knowledge graph for nodes in the remaining node group that have a reachable path from the current node to form a path subgraph. Step S404: Repeat step S403 to associate all table relationships and form multiple pathway diagrams.

[0047] Step S5: Pathway Subgraph Scoring and Ranking: Evaluate the relevance of each constructed pathway subgraph, calculate the relevance score of each pathway subgraph to the corresponding user query, and sort all pathway subgraphs according to the relevance score; Furthermore, the relevance evaluation of each constructed path subgraph and the calculation of the relevance score between each path subgraph and the corresponding user query also include: introducing a dynamic weight adjustment mechanism, which adjusts the weight of each recall channel in the total score in real time based on the average score performance of each recall channel, wherein recall channels with reliable and accurate performance receive higher weights; this facilitates an accurate reflection of the matching degree between the path subgraph and the user query. Furthermore, the dynamic weight adjustment mechanism specifically includes: Let the result set of each pathway subgraph be , ,in, Indicates the first path in the path subgraph Relevance score of NLP recall for each node; Indicates the first path in the path subgraph Relevance score of node vector recall; Indicates the first path in the path subgraph Relevance score of knowledge graph recall for each node; Indicates the first path in the path subgraph The relevance score of all recalls for each node; Indicates the first path in the path subgraph One node; Indicates the first path in the path subgraph NLP recall nodes for each node; Indicates the first path in the path subgraph The vector recall node of each node; Indicates the first path in the path subgraph The knowledge graph of each node recalls the node; , A natural number, representing the total number of nodes; Set the recall weight based on NLP matching as The vector recall weights based on user input are: The recall weight based on semantic expansion is The adjustment factor is Let the dynamic weight adjustment value for the recall weight of NLP matching be denoted as . The dynamic weight adjustment value for the user input vector recall weight is... Dynamic weight adjustment value for recall weight of semantic expansion ; Based on the adjusted dynamic weights, the adjusted scoring formula is obtained, and the relevance scores of all pathway subgraphs are calculated.

[0048] Furthermore, dynamically adjust the weight values. , , The calculation expression is,

[0049] In the formula, This indicates the number of nodes recalled by NLP; Indicates the number of nodes recalled by the vector; This represents the number of nodes recalled by the knowledge graph.

[0050] Furthermore, the adjusted scoring formula expression is as follows:

[0051] In the formula, This indicates the path subgraph after weight adjustment. Relevance score of each node; This represents the total number of nodes.

[0052] Based on the adjusted scoring formula, the relevance score set of all pathway subgraphs is calculated { , ... }

[0053] Step S6: Schema Locking and Output: Based on the calculated score results, select the path subgraph with the highest score to construct the final schema information, including table list, field names, data types, comments, enumeration values, and table relationships, as input to text2sql, thereby improving the accuracy and efficiency of text2sql.

[0054] Example 2: In this embodiment, the construction of the pathway subgraph in step S4 of Embodiment 1 is further explained, such as... Figure 3 and Figure 4 As shown: Figure 3 This flowchart illustrates the entire process from multi-path recall results to the construction of multiple pathway subgraphs, clearly explaining the specific steps involved in building the pathway subgraphs: Metadata node collections are grouped by table, field, and dimension values, and can be represented as: Table group: Rt = { , ,......}; Field group: Rf = { , ,......}; Dimension group: Rd = { , ,......}

[0055] 1. Input: Metadata node set for multi-way recall: The starting point of this process is the set of all metadata nodes related to the user issue obtained after the S3 multi-way recall steps. Before the process begins, these node sets will be grouped according to "table, field, dimension value": Table group (Rt): For example, {order table, product table, user table}; Field group (Rf): For example, {order id, product id, username}; Dimension group (Rd): For example, {Waiting, Waiting, Popular Products}.

[0056] 2. Constructing 'Field-Dimension Value' Associations: The process first traverses the dimension value group (Rd). For each dimension value (e.g., "Waiting"), the system searches for its corresponding field node (e.g., "Order Status") in the knowledge graph. Once found, a 'Field-Dimension Value' association path is formed between them (e.g., "Order Status → Waiting"). Simultaneously, each dimension value is added to the node information of its corresponding field, enriching the context of the field group.

[0057] 3. Establish 'table-field' relationships: Next, the process traverses the table group (Rt). For each table (such as the "order table"), it searches for the fields belonging to that table (such as "order id" and "product id") from the updated field group (Rf), thereby establishing the 'table-field' relationship path (such as "order table → order id").

[0058] 4. Initial Graph and Search: The process selects a table node (e.g., "Order Table") from the table group (Rt) as the starting point. Using this node as the starting point, it performs a breadth-first search or path search algorithm within the pre-built data knowledge graph. The goal is to find other relevant metadata nodes (including other tables, fields, and dimension values) that have a reachable path to the current node ("Order Table").

[0059] Example: Suppose that in the knowledge graph, the "Order Table" is related to the "Product Table" through the field "Product ID", and the "Product Table" is related to the "Product Category Table". In this step, these related nodes will be extracted from the knowledge graph to form a preliminary pathway subgraph, for example: "Order Table → Product ID → Product Table → Category ID → Product Category Table".

[0060] 5. Repeated Associations: The system will repeatedly execute step 4, traversing all table nodes in the table group, using each node as a starting point to search and construct a subgraph. This ensures that all potential table relationships are considered and that multiple independent path subgraphs are constructed, each representing a possible query path.

[0061] Repeat steps 4-5 to iteratively construct the pathway subgraph: This is the core iterative step of the process.

[0062] 6. Output multiple pathway subgraphs: The final output of the process is a series of pathway subgraphs, each containing the tables, fields, dimension values ​​associated with the user query and their inter-table connections. These subgraphs will be evaluated and sorted in subsequent steps.

[0063] In this embodiment, as Figure 4 As shown, Figure 4A concrete example of a pathway subgraph structure is presented, visually demonstrating the composition and internal relationships of a high-scoring pathway subgraph (e.g., the highest-scoring pathway subgraph) after step S4 is completed. This includes: Node Types and Grouping: The diagram clearly shows nodes grouped by table, field, and dimension values: Table Node: such as order table, product table.

[0064] Field Node: such as product ID, order status, product name.

[0065] Dimension Value Node: such as waiting, waiting, popular products.

[0066] Relationship Building Path: The diagram clearly illustrates how connections are established between nodes through the steps described in S401 and S402: 'Field-Dimension Value' Association Path: Shows how a dimension node (such as "Waiting") is connected to its parent field node (such as "Order Status") via a value edge.

[0067] 'Table-Field' Association Path: Shows how table nodes (such as "Order Table") are connected to other nodes (such as "Product ID" and "Order Status") through contain edges.

[0068] Demonstrating the final output path: This diagram provides users with a clear visual representation, illustrating which tables, fields, and dimension values ​​are linked together to form a complete query path. The entire sub-diagram represents the pattern structure extracted by the system from multi-path retrieval, which is highly matched to the user's query.

[0069] In summary Figure 3 and Figure 4 Together, they elucidated the construction logic and final form of the pathway subgraph: Figure 3 Describe "how to build" (dynamic process). Figure 4 The examples show "What it looks like" (static results examples). They visually complement the methods and outcomes of step S4, from the raw recall results to the generation of structured pathway subgraphs that can be used for scoring, and are an important part of the visualization of the entire multipath retrieval pattern linking method.

[0070] Example 3: In this embodiment, a text2sql accuracy improvement system based on multi-path retrieval pattern linking is also provided, the system comprising: The data processing module is used to collect metadata from the database, integrate the collected metadata and structure it into a unified data knowledge graph, and build an NLP dictionary and a professional domain knowledge base. The data knowledge graph, NLP dictionary and professional domain knowledge base are vectorized and stored in a vector library. The semantic processing module receives the user's original natural language question, and performs semantic expansion and ambiguity removal on the user's original input question based on relevant knowledge in the professional domain knowledge base and combined with the large language model LLM, to obtain the accurately rewritten question; The recall module is used to recall metadata nodes related to the user's question from the vector library through three parallel recall channels based on the user's original input question, the rewritten question, and NLP word segmentation technology, thus completing the initial linking of the recall mode. The subgraph construction module is used to group the collection of metadata nodes related to user intent by table, field, and dimension value, and use the directed path graph in the knowledge graph to construct multiple path subgraphs for these recalled metadata nodes to represent potential association paths. The scoring and ranking module is used to evaluate the relevance of each constructed path subgraph, calculate the relevance score of each path subgraph to the corresponding user query, and sort all path subgraphs according to the relevance score. The schema output module is used to select the highest-scoring pathway subgraph based on the calculated score to construct the final schema information, including a list of tables, field names, data types, comments, enumeration values, and table relationships, as input to text2sql, thereby improving the accuracy and efficiency of text2sql.

[0071] Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A method for improving the accuracy of text2sql based on multi-way retrieval pattern linking, characterized in that, The method includes: Data preparation and knowledge construction: Collect metadata from the database, integrate and structure the collected metadata into a unified data knowledge graph, and build an NLP dictionary and a professional domain knowledge base. Vectorize the data knowledge graph, NLP dictionary and professional domain knowledge base and store them in a vector library. User question semantic processing: Receive the user's original natural language question, and based on relevant knowledge in the professional domain knowledge base, combine it with a large language model LLM to perform semantic expansion and ambiguity removal on the user's original input question to obtain a accurately rewritten question; Multi-channel recall mode linking: Based on the user's original input question, the rewritten question, and NLP word segmentation technology, metadata nodes related to the user's question are recalled from the vector library through three parallel recall channels to complete the initial linking of the recall mode; Pathway subgraph construction: The set of metadata nodes related to user intent is grouped by table, field, and dimension value, and the directed path graph in the knowledge graph is used to construct multiple path subgraphs to represent potential association paths. Pathway subgraph scoring and ranking: For each constructed pathway subgraph, a relevance evaluation is performed, and a relevance score is calculated between each pathway subgraph and the corresponding user query. All pathway subgraphs are then ranked according to their relevance scores. Pattern locking and output: Based on the calculated score results, the highest-scoring path subgraph is selected to construct the final pattern information, including a list of tables, field names, data types, comments, enumeration values, and table relationships, which serve as input to text2sql, thereby improving the accuracy and efficiency of text2sql.

2. The method for improving the accuracy of text2sql based on multi-way retrieval mode linking as described in claim 1, characterized in that: The metadata includes data table structure information, historical SQL execution records, ETL processes, business metric definitions, and professional business terminology. The process involves integrating and structuring the collected metadata into a unified data knowledge graph, simultaneously constructing an NLP dictionary and a domain-specific knowledge base, and vectorizing the data knowledge graph, NLP dictionary, and domain-specific knowledge base and storing them in a vector library. The data knowledge graph is constructed based on the data table structure information and SQL parsing results. This includes using the data table structure information to construct a single-table graph structure, using historical SQL execution records and ETL processes to parse SQL statements using JSqlParser / ANTLR tools to identify the relationships between tables, and structuring the relationships between tables into RDF triples through the single-table graph structure and storing them in the knowledge graph to obtain the data knowledge graph. Building an NLP dictionary: Based on rules and large language model LLM, identify the dimension value field column in the data table structure information, query all dimension values ​​and perform generalization processing, redefine the NLP dictionary structure, and put the processed dimension values ​​into the newly defined NLP dictionary for storage; A professional domain knowledge base is built based on indicator caliber algorithms and business terminology; Construct a vector index by vectorizing the data knowledge graph, NLP dictionary, and professional domain knowledge base using a vector model and storing them in the vector library.

3. The method for improving the accuracy of text2sql based on multi-way retrieval mode linking as described in claim 2, characterized in that: The RDF triples include table node triples, table association triples, and dimension triples. The table node triples include an attribute, a containment attribute, and a field; the table association triples include a field, a table association attribute, and a field; and the dimension triples include an attribute, a value, and a dimension value.

4. The method for improving the accuracy of text2sql based on multi-way retrieval mode linking as described in claim 2, characterized in that: The rule-based recognition includes recognizing character types, having a number of values ​​less than or equal to 100, and having a dimension value field column length less than or equal to 10 characters; The LLM-based recognition includes constructing prompt words from field descriptions and sampled partial values, which are then recognized by LLM. The process of querying all dimension values ​​and generalizing them includes: LLM performing semantic generalization on the dimension values ​​to align them with natural language, and generalizing and translating non-Chinese dimension values ​​to make them conform to the user's questioning habits; The redefined NLP dictionary structure is to redefine the original definition of "word frequency" as "dimension value, field column ID group, and weight value", where the weight value is 1 by default.

5. The method for improving the accuracy of text2sql based on multi-way retrieval mode linking as described in claim 1, characterized in that, The process of recalling metadata nodes related to user questions from the vector library through three parallel recall channels to complete the initial linking of the recall mode includes: User input vector retrieval: Vectorize the user's original input question and retrieve semantically similar metadata nodes from the vector library, including tables, fields, and dimension values; Semantic rewriting vector recall: The rewritten question is vectorized and retrieved again in the vector library to capture deeper semantic relationships; NLP segmentation for precise targeting and recall: Using NLP segmentation technology, keywords in the query are precisely matched, and the dimension values ​​and table names that match exactly are retrieved directly from the NLP dictionary.

6. The method for improving the accuracy of text2sql based on multi-way retrieval mode linking as described in claim 5, characterized in that: When using user input vector recall and semantic rewriting vector recall, a strategy combining full-text recall and text chunk recall is employed. Specifically, a threshold is set based on the character length of the query intent text. When the length is less than or equal to the set threshold, full-text recall is used; when it exceeds the set threshold, text chunk recall is used. Full-text recall involves vectorizing the query intent text into a full-text vector and then searching the vector library to obtain the search data. Text chunking retrieval involves segmenting and quantizing the query intent text, then searching a vector database. For each segment, the top 10 results are retrieved. Finally, all segmented retrieval results are fed into a re-ranking model for rearrangement. The data.

7. The method for improving the accuracy of text2sql based on multi-way retrieval mode linking as described in claim 6, characterized in that: The text segmentation retrieval method uses a segment size equal to the median length of all nodes in the knowledge graph to prevent the loss of key information due to semantic sparsity caused by text length.

8. The method for improving the accuracy of text2sql based on multi-way retrieval mode linking as described in claim 1, characterized in that, The construction of the pathway subgraph also includes: Iterate through the dimension value groups, find the field to which each dimension value belongs, form a 'field-dimensional value' association path, and add the dimension value to the corresponding field group; Traverse the table group, find the field belonging to the corresponding form in the field group, and form the 'table-field' association path; Take a table node from the table group to form a single-node graph. Starting from the current node, search the knowledge graph for nodes in the remaining node group that have a reachable path from the current node to form a path subgraph. Repeated operations associate all table relationships, forming multiple pathway graphs.

9. The method for improving the accuracy of text2sql based on multi-way retrieval pattern linking as described in claim 1, characterized in that, The relevance evaluation of each constructed path subgraph, and the calculation of the relevance score between each path subgraph and the corresponding user query, also includes: A dynamic weight adjustment mechanism was introduced, which adjusts the weight of each recall channel in the total score in real time based on the average score performance of each channel. Among them, the recall channels with reliable and accurate performance receive higher weights, which can more accurately reflect the matching degree between the channel subgraph and the user query.

10. The method for improving the accuracy of text2sql based on multi-way retrieval pattern linking as described in claim 9, characterized in that, The dynamic weight adjustment mechanism includes: Let the result set of each pathway subgraph be , ,in, Indicates the first path in the path subgraph Relevance score of NLP recall for each node; Indicates the first path in the path subgraph Relevance score of node vector recall; Indicates the first path in the path subgraph Relevance score of knowledge graph recall for each node; Indicates the first path in the path subgraph The relevance score of all recalls for each node; Indicates the first path in the path subgraph One node; Indicates the first path in the path subgraph NLP recall nodes for each node; Indicates the first path in the path subgraph The vector recall node of each node; Indicates the first path in the path subgraph The knowledge graph of each node recalls the node; , A natural number, representing the total number of nodes; Set the recall weight based on NLP matching as The vector recall weights based on user input are: The recall weight based on semantic expansion is The adjustment factor is Let the dynamic weight adjustment value for the recall weight of NLP matching be denoted as . The dynamic weight adjustment value for the user input vector recall weight is... Dynamic weight adjustment value for recall weight of semantic expansion ; Based on the adjusted dynamic weights, the adjusted scoring formula is obtained, and the relevance scores of all pathway subgraphs are calculated.

11. The method for improving the accuracy of text2sql based on multi-way retrieval mode linking as described in claim 10, characterized in that: Dynamically adjust weight values , , The calculation expression is, In the formula, This indicates the number of nodes recalled by NLP; Indicates the number of nodes recalled by the vector; This indicates the number of nodes recalled by the knowledge graph; The adjusted scoring formula is as follows: In the formula, This indicates the path subgraph after weight adjustment. Relevance score of each node; This represents the total number of nodes.

12. A text2sql accuracy improvement system based on multi-way retrieval pattern linking, characterized in that, The system includes: The data processing module is used to collect metadata from the database, integrate the collected metadata and structure it into a unified data knowledge graph, and build an NLP dictionary and a professional domain knowledge base. The data knowledge graph, NLP dictionary and professional domain knowledge base are vectorized and stored in a vector library. The semantic processing module receives the user's original natural language question, and performs semantic expansion and ambiguity removal on the user's original input question based on relevant knowledge in the professional domain knowledge base and combined with the large language model LLM, to obtain the accurately rewritten question; The recall module is used to recall metadata nodes related to the user's question from the vector library through three parallel recall channels based on the user's original input question, the rewritten question, and NLP word segmentation technology, thus completing the initial linking of the recall mode. The subgraph construction module is used to group the collection of metadata nodes related to user intent by table, field, and dimension value, and use the directed path graph in the knowledge graph to construct multiple path subgraphs for these recalled metadata nodes to represent potential association paths. The scoring and ranking module is used to evaluate the relevance of each constructed path subgraph, calculate the relevance score of each path subgraph to the corresponding user query, and sort all path subgraphs according to the relevance score. The schema output module is used to select the highest-scoring pathway subgraph based on the calculated score to construct the final schema information, including a list of tables, field names, data types, comments, enumeration values, and table relationships, as input to text2sql, thereby improving the accuracy and efficiency of text2sql.

Images