A Text-to-SQL Method Based on Bidirectional Retrieval and Multi-level Relevance Fusion Truncation

Abstract

This invention discloses a Text-to-SQL method based on bidirectional retrieval and multi-level relevance fusion and truncation, belonging to the field of natural language processing and database retrieval technology. The method includes: extracting keywords from the query question; using the question and keywords as search terms, performing table-first retrieval and column-first retrieval, merging the search results into an initial pattern term set; for each pattern term, calculating statement-level relevance and token-level relevance, weighted summing and normalizing, and filtering pattern terms based on a threshold; performing shortest path completion on the selected table nodes in the unweighted pattern graph; and inputting the pattern information and question combination into a large language model to generate an SQL statement. This invention improves recall through multi-level bidirectional retrieval, fuses statement-level and token-level relevance for pattern filtering, avoids the problem of statement-level vectors being insensitive to short texts and local features, and combines pattern graph completion of multi-table JOIN paths, significantly improving the accuracy and robustness of Text-to-SQL in complex database scenarios.

Description

Technical Field

[0001] This invention belongs to the interdisciplinary field of natural language processing and database retrieval technology, specifically involving a pattern linking method for Text-to-SQL tasks, particularly a method that employs bidirectional table column retrieval driven by both questions and keywords, adaptive threshold truncation that integrates statement-level and token-level relevance, and shortest path completion based on an unweighted pattern graph containing table column nodes. Background Technology

[0002] Text-to-SQL refers to the automatic conversion of users' natural language questions into Structured Query Language (SQL) statements that can be executed on relational databases. This technology is significant for lowering the barrier to database queries and enabling efficient interaction between non-technical personnel and data.

[0003] Currently, Text-to-SQL methods based on Large Language Models (LLMs) have become mainstream. These methods typically require inputting database schema information as part of the contextual prompts into the LLM. However, real-world industrial databases can contain hundreds or even thousands of tables. Directly using the complete database schema can lead to problems such as excessively long prompts, exceeding LLM input length limits, irrelevant tables interfering with model understanding, increased computational costs, and inference latency. Therefore, accurately selecting the tables and fields most relevant to the user's problem from the massive database schema—i.e., schema linking—becomes crucial for improving Text-to-SQL performance.

[0004] Most pattern linking methods proposed in existing academic papers are detached from real-world production scenarios. Their core idea is to directly utilize the semantic understanding capabilities of Large Language Models (LLMs) to select the most relevant pattern items to the user's question through one or more inferences within a complete database schema. While these methods can achieve high accuracy and recall on public datasets (such as Spider and Bird), they suffer from the following prominent problems in practical industrial applications: high cost, difficulty in large-scale deployment; susceptibility to "context dilution" due to limitations in model context length; and lack of explicit modeling of structured relationships. Summary of the Invention

[0005] To address the aforementioned issues, this invention does not rely on LLM for direct pattern selection. Instead, it designs a lightweight method that does not require calling a large model for pattern linking: through bidirectional table column retrieval, multi-level relevance fusion, and shortest path completion based on the pattern graph, it achieves high-precision pattern linking at extremely low cost. Moreover, the entire process is interpretable, debuggable, and easy to deploy.

[0006] This invention is implemented using the following technical solution: a Text-to-SQL method based on bidirectional retrieval and multi-level relevance fusion truncation, which includes the following steps: S1: Parse the metadata of the target relational database, extract all table names, field names, field types and comment information in the database schema, and store them into a preset schema information vector database, and create table vector indexes and field vector indexes; at the same time, based on the primary key-foreign key relationship in the database, pre-construct an unweighted schema diagram representing the connection relationship between tables; S2: Receive the user's natural language query question, and extract at least one keyword from the query question using the first major language model or keyword extraction algorithm; S3: Using the query question and each extracted keyword as search terms, perform table-first search and column-first search independently; merge the candidate table set and candidate field set obtained by using all search terms (i.e., query question and each keyword) through table-first search and column-first search to obtain the initial pattern item set; Among them, table-first retrieval means: retrieving a set of candidate tables in the table vector index with the current retrieval item, and then retrieving a set of candidate fields within the range of the candidate table set; Among them, column-first retrieval means: retrieving a set of candidate fields in the field vector index with the current search term, and then extracting the set of candidate tables to which the set of candidate fields belongs; S4: For each pattern item in the initial pattern item set, calculate the statement-level relevance and token-level relevance respectively; Among them, statement-level relevance is calculated using the cosine similarity between the overall semantic vector of the query question (or keywords) and the text description vector of the pattern item; Among them, the token-level relevance divides the text description of the query question (or keyword) and the pattern item into several tokens, calculates the maximum similarity between each query token and the pattern item token, and then sums and normalizes them to obtain the token-level relevance score. The statement-level relevance and token-level relevance are weighted and summed to obtain the comprehensive relevance score of the pattern item, and the comprehensive relevance scores of all pattern items are normalized; all pattern items with a comprehensive relevance score greater than the threshold are selected as the filtered pattern item set. S5: Taking the table nodes in the filtered pattern item set as the starting point, calculate the shortest path between any two starting points in the unweighted pattern graph, and add the intermediate table nodes and foreign key column nodes passed through by the shortest path to the pattern item candidate set. S6: Combine the table structure, field information, and inter-table connections contained in the final pattern item set with the user's natural language query question, input the second language model, and generate and output the corresponding SQL query statement.

[0007] As a further improvement to the above scheme, step S1 specifically includes: parsing the metadata of the target relational database, extracting all table names, field names, field types, and comment information, organizing them into text descriptions for each table or column, generating table-level feature vectors and field-level feature vectors using a pre-trained embedding model, storing them in a vector database, and establishing table vector indexes and field vector indexes. Regarding the construction of the unweighted schema graph, an unweighted schema graph is pre-constructed based on the primary key-foreign key relationships in the database. The graph nodes include table nodes and column nodes; edges include table-column dependent edges (indicating that a column belongs to a certain table) and column-column foreign key connection edges (connecting a foreign key column to the primary key column it references). The edges in the graph are not weighted and are only used to represent all possible paths connecting tables through columns.

[0008] As a further improvement to the above scheme, the keyword extraction in step S2 specifically includes: using the first language model in conjunction with prompt words to extract core entities, condition words, and aggregate words related to the database query. If the LLM extraction result is none, then the process switches to rule-based fallback: performing word segmentation and part-of-speech tagging on the query question, and extracting words with parts of speech such as nouns, proper nouns, gerunds, or noun phrases as keywords. If there are still no keywords, then the query question itself is used as the sole search term by default.

[0009] As a further improvement to the above scheme, step S3 specifically includes: using the query question itself and each keyword as search terms, and independently executing the following two strategies for each search term: Table-first retrieval: Using the current search term, search the table vector index for the top N tables with the highest similarity to obtain a candidate table set; then, within the field range contained in these candidate tables, use the same search term to search the field vector index for the top M fields with the highest similarity to obtain a candidate field set. Column-first retrieval: Search the field vector index for the P fields with the highest similarity to the current search term to obtain a candidate field set; then extract the tables to which these fields belong to obtain a candidate table set; The candidate tables and candidate fields obtained from all search terms using the two strategies are merged and deduplicated to obtain the initial pattern item set.

[0010] As a further improvement to the above scheme, step S4 specifically includes: For each pattern item in the initial pattern item set, calculate its comprehensive relevance score to the current search item (taking the maximum value among all search items); Statement-level relevance: The entire search term is input into the embedding model to obtain the query vector, and the pattern term text description is used to obtain the pattern vector. Cosine similarity is then calculated. ; Token-level relevance: Borrowing from ColBERT, the text of the search term and the pattern term are segmented separately. For each query token, the cosine similarity of its embedding vectors with all tokens in the pattern term is calculated, and the maximum value is taken as the matching score for that token, as shown in the formula below: Sum the matching scores of all query tokens to get the original cumulative score, then divide by the number of query tokens to get the final score. , , ; Weighted fusion: to obtain a comprehensive score. , where α is the preset weight (default 0.5); Normalization and Threshold Truncation: The overall score of all pattern items is min-max normalized and mapped to the [0,1] interval. A relevance threshold θ (default 0.6) is set, retaining all pattern items with normalized scores greater than θ. If no item exceeds the threshold, the item with the highest score is retained.

[0011] As a further improvement to the above scheme, step S5, pattern graph completion, specifically includes: taking the table nodes in the filtered pattern item set as the starting point, performing shortest path completion on the unweighted pattern graph constructed in step S1. For any two starting table nodes u and v, use breadth-first search (BFS) to calculate the shortest path from u to v. The path alternates between table nodes, column nodes, foreign key edges, and dependent edges. If the number of table nodes in the path is less than a preset threshold (default 3), then all table nodes, column nodes, dependent edges, and foreign key edges on the path are added to the current schema subgraph. The final set of schema items obtained after completion contains the tables and their fields directly related to the user's question, as well as all intermediate tables and column-level foreign key relationships required to join these tables, including the complete JOIN path.

[0012] As a further improvement to the above solution, step S6 specifically includes: formatting the table structure, field list, and inter-table join relationships (explicitly providing foreign key column pairs) in the final schema subgraph according to a preset prompt word template, combining them with the user's natural language query question, and inputting them into the second major language model (such as a finely tuned CodeLlama-13B-Instruct for SQL generation). The model generates the corresponding SQL query statement. The generated SQL can be further subjected to syntax validation and database compatibility checks; if the validation fails, the model is called again for correction.

[0013] Compared to existing pattern linking techniques, the pattern linking method of this invention, which combines bidirectional retrieval based on tables and fields with multi-level relevance fusion and truncation, has the following advantages:

[0014] 1. Improve the recall and precision of pattern links: By using a dual search hierarchy of questions and keywords, as well as a two-way complementary strategy of table priority and column priority, user intent is captured from both macro and micro levels, effectively avoiding omissions or noise from a single dimension.

[0015] 2. More scientific relevance assessment and adaptive filtering: Introducing multi-level relevance fusion at both the statement and token levels avoids the problem of statement-level vectors being insensitive to short texts and local features. Combining normalization and threshold truncation, it can adaptively select relevant pattern items based on the dynamic semantic distribution of each query, avoiding the shortcomings of fixed-number truncation.

[0016] 3. Automatically discover and complete implicit foreign key JOIN paths: Construct an unweighted schema graph containing table nodes and column nodes, and use the Shortest Path First (BFS) algorithm to automatically discover the complete paths connecting tables through foreign key columns. The completed schema item set directly provides the specific column pair information required for the JOIN, enabling the SQL generation model to correctly construct multi-table join queries.

[0017] 4. Reduce LLM input length and inference burden: The final output pattern item set is highly concise and structurally complete, significantly shortening the prompt word length, allowing LLM to focus on SQL logic generation, improving generation quality and execution success rate.

[0018] 5. High interpretability: Each step (keyword extraction, bidirectional retrieval, score fusion, threshold truncation, path completion) has clear basis and intermediate results, which is convenient for debugging and verification. Attached Figure Description

[0019] Figure 1 This is a flowchart illustrating the overall process of the method of the present invention. Detailed Implementation

[0020] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and do not limit the scope of protection of this invention.

[0021] Phase 1: Vectorized Storage of Schema Information and Construction of Schema Graph. This phase involves offline preprocessing of the target relational database to construct vector indexes and schema graphs.

[0022] Step S101: Obtain all table names, table comments, field names, field types, field comments, and primary key-foreign key constraints of the target database by parsing the database's DDL file or querying the system's data dictionary; this information will be used to generate the description text for the schema items.

[0023] Step S102: Generate table-level and field-level vectors based on the pattern item information obtained in the previous step, and store them in the vector database.

[0024] For each table, its table name, the concatenation of all field names, and table comments are combined into a structured text description, in a format such as: "Table [Table Name], [Table Comment], Fields include [Field 1 Name][Field 1 Type], [Field 2 Name][Field 2 Type]...". This description is input into a pre-trained embedding model (such as bge-large-zh), which outputs a fixed-dimensional table-level feature vector. All table-level vectors are stored in a vector database to build a table vector index.

[0025] For each field, its field name, the name of the table it belongs to, the field type, and the field comment are combined into a text description, in the format of: "Field[Field Name], Table [Table Name], Type [Field Type], [Field Comment]". This is then converted into a field-level feature vector using the same embedding model, stored in a vector database, and a field vector index is built.

[0026] Step S103: Based on the primary key-foreign key relationships in the database, pre-construct an unweighted schema graph. The construction rules for this graph are as follows:

[0027] Nodes: There are two types of nodes—table nodes and column nodes. A table node is created for each table in the database; a column node is created for each column in each table.

[0028] Edges: There are two types of edges—table-column dependent edges and column-column foreign key connection edges. For each column node, an undirected edge (dependent edge) is added between its parent table node and the column node, indicating that the column belongs to that table. For each primary key-foreign key constraint relationship in the database (i.e., a foreign key column in one table references a primary key column in another table), an undirected edge (foreign key edge) is added between the two column nodes, indicating that a foreign key reference relationship exists between the two columns.

[0029] All edges in the graph have no weights and are used only to indicate the presence or absence of connections. This graph is used in subsequent stages to discover implicit JOIN paths between tables using the shortest path.

[0030] Phase Two: Keyword Extraction. This phase receives the user's natural language query and extracts keywords for subsequent retrieval. Keyword extraction is performed using the following two methods: Keywords refer to core entities, conditional terms, and aggregation terms related to the database query.

[0031] Keyword extraction based on large language models: Input the user's question into the first large language model (such as Qwen-14B-Instruct, GPT-3.5-Turbo, etc.) and configure appropriate prompt words. The model will return one or more keywords.

[0032] The rule-based processing method based on part-of-speech tagging: A word segmentation tool (such as jieba) is used to segment and tag the user's question. Words with the parts of speech of nouns (n), proper nouns (nr, ns, nt), and gerunds (vn) are extracted as keywords. If no keywords are found after rule-based processing, the user's question itself is used as the sole search term by default.

[0033] For example, for the question "Query the name of the product with the highest sales in a certain region", the model may output "region", "sales amount", and "product name".

[0034] Phase 3: Two-way retrieval of questions and keywords. In this phase, the user's question itself and each extracted keyword are used as search terms, and "table-first search" and "column-first search" are performed independently for each search term.

[0035] Step S301: Perform a table-first search for each search term. Using the current search term (e.g., a complete user question), perform a semantic similarity search (usually using cosine similarity) in the table vector index, returning the top N tables with the highest similarity (N is a preset fixed value, e.g., 10). This yields a candidate table set T_set. Limiting the search scope to the fields of all tables in T_set, search the field vector index using the same search term, returning the top M fields with the highest similarity (M is a preset fixed value, e.g., 20). This yields a candidate field set C_set.

[0036] Step S302: Perform column-first retrieval for each search term. Use the current search term to perform a semantic similarity search in the field vector index (unlimited table range), and return the top P fields with the highest similarity (P is a preset fixed value, such as 20). Obtain the candidate field set C_set2. Extract the table to which each field in C_set2 belongs, forming the candidate table set T_set2 (automatically deduplicated).

[0037] Step S303: Merge and deduplicate the search results. After performing the bidirectional search on the user question itself and each keyword, merge and deduplicate the candidate table set and candidate field set obtained from all search terms to obtain the initial pattern item set.

[0038] Phase 4: Multi-level relevance fusion and adaptive threshold truncation. In this phase, for each pattern item (table or field) in the initial pattern item set, its relevance score with all search terms (user questions and each keyword) is calculated, and the maximum value is taken as the comprehensive raw score of that pattern item; the score calculation combines statement-level relevance and token-level relevance.

[0039] Step S401: Input the entire search term into the embedding model (e.g., bge-base-en-v1.5) to obtain the query vector q_sent, input the text description of the pattern term into the same model to obtain the pattern vector p, and calculate the cosine similarity.

[0040] Step S402, Token-level relevance calculation. The search terms are segmented (Chinese using jieba, English using spaces and punctuation) to obtain a token sequence. The text description of the pattern item is segmented to obtain a token sequence. The same embedding model is used to obtain the embedding vector for each token. For each query token... Calculate its relationship with all tokens in the pattern item. The cosine similarity is used, and the maximum value is taken as the matching score for that token. The matching scores of all query tokens are summed, and then divided by the number of query tokens m to obtain the token-level relevance score.

[0041] Step S403: After weighted summation of the results, normalization and threshold truncation are performed. A comprehensive score is obtained by weighted summation of statement-level relevance and token-level relevance. The comprehensive raw scores of all initial pattern items are then normalized using a min-max method, mapping them to the [0,1] interval. A relevance threshold θ is set (value range [0,1], default 0.6). All pattern items with normalized scores greater than θ are retained as the filtered pattern item set. If no pattern item exceeds the threshold, only the pattern item with the highest score is retained.

[0042] Phase 5: Shortest Path Completion Based on the Unweighted Schema Graph. All table nodes are extracted from the schema item set filtered in Phase 4 to form the source node set V_start. If the set contains only one table node, path completion is unnecessary. Path completion is performed only when the set contains two or more table nodes. For any two distinct nodes u and v in V_start, perform BFS on the unweighted schema graph to calculate the shortest path from u to v (by the number of edges traversed). Since the graph contains both table nodes and column nodes, BFS will naturally traverse between them. A path length threshold L is set (default 3). If the number of table nodes on the shortest path is ≤ L, all nodes (table nodes and column nodes) on that path are added to the current schema item set. For multiple source node pairs, elements on all paths are merged, and duplicates are automatically removed. The final generated schema item set contains the tables and their fields directly related to the user's question, as well as all intermediate tables and column-level foreign key relationships required to join these tables, including the complete JOIN path.

[0043] Phase 6: SQL statement generation.

[0044] Step S601: Construct prompt words. Format the schema information in the final schema subgraph according to the preset prompt word template. The template typically includes: a table definition section listing all table names and their fields; a join section explicitly specifying foreign key column pairs (e.g., "Table A.Foreign Key Column = Table B.Primary Key Column"); a user question section containing the original natural language query; and an instruction section requesting the model to generate an SQL statement.

[0045] Step S602: Generate the initial SQL. Input the above prompts into the second major language model (e.g., CodeLlama-13B-Instruct fine-tuned for Text-to-SQL tasks, or other base models fine-tuned using QLoRA). The model outputs the corresponding SQL query statement.

[0046] Step S603 involves validating and correcting the generated SQL statement. A SQL syntax parsing tool (such as sqlparse) is used for syntax validation. If the validation fails, the second language model is called again (temperature parameters can be adjusted or prompt words can be reconstructed based on the validation results) for correction and generation.

[0047] Through the above six stages, this invention achieves fully automated transformation from natural language problems to executable SQL statements.

[0048] The above description is merely a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, several improvements and equivalent substitutions can be made without departing from the spirit and principle of the present invention, and these improvements and equivalent substitutions should also be considered within the scope of protection of the present invention.

Claims

1. The method according to claim 1, characterized in that, The specific method for constructing the unweighted schema graph in step S1 includes: parsing the database metadata and extracting all primary key-foreign key constraint relationships; taking each table and its primary and foreign keys as nodes, for each table with a direct foreign key reference relationship, a Text-to-SQL method based on bidirectional retrieval and multi-level relevance fusion truncation is characterized by including the following steps: S1: Parse the metadata of the target relational database, extract all table names, field names, field types and comment information in the database schema, and store them into a preset schema information vector database, and create table vector indexes and field vector indexes; at the same time, based on the primary key-foreign key relationship in the database, pre-construct an unweighted schema diagram representing the connection relationship between tables; S2: Receive the user's natural language query question, and extract at least one keyword from the query question using the first major language model or keyword extraction algorithm; S3: Using the query question and each extracted keyword as search terms, perform table-first search and column-first search independently; merge the candidate table set and candidate field set obtained by using all search terms (i.e., query question and each keyword) through table-first search and column-first search to obtain the initial pattern item set; Among them, table-first retrieval means: retrieving a set of candidate tables in the table vector index with the current retrieval item, and then retrieving a set of candidate fields within the range of the candidate table set; Among them, column-first retrieval means: retrieving a set of candidate fields in the field vector index with the current search term, and then extracting the set of candidate tables to which the set of candidate fields belongs; S4: For each pattern item in the initial pattern item set, calculate the statement-level relevance and token-level relevance respectively; Among them, statement-level relevance is calculated using the cosine similarity between the overall semantic vector of the query question (or keywords) and the text description vector of the pattern item; Among them, the token-level relevance divides the text description of the query question (or keyword) and the pattern item into several tokens, calculates the maximum similarity between each query token and the pattern item token, and then sums and normalizes them to obtain the token-level relevance score. The statement-level relevance and token-level relevance are weighted and summed to obtain the comprehensive relevance score of the pattern item, and the comprehensive relevance scores of all pattern items are normalized; all pattern items with a comprehensive relevance score greater than the threshold are selected as the filtered pattern item set. S5: Taking the table nodes in the filtered pattern item set as the starting point, calculate the shortest path between any two starting points in the unweighted pattern graph, and add the intermediate table nodes and foreign key column nodes passed through by the shortest path to the pattern item candidate set. S6: Combine the table structure, field information, and inter-table connections contained in the final pattern item set with the user's natural language query question, input the second language model, and generate and output the corresponding SQL query statement.

2. Add an undirected edge between the foreign key column and the primary key column node it references, with no weight assigned to the edge; the unweighted schema graph is used to represent all possible foreign key connection paths between tables in the database, providing a graph structure basis for shortest path completion in subsequent steps.

3. The method according to claim 1, characterized in that, The specific method for keyword extraction in step S2 is as follows: input the query question into the first language model and configure prompt words to instruct the model to output core entities related to the database query; if the number of extracted keywords is zero, the rule-based method is used by default to perform word segmentation and part-of-speech tagging on the query question, and extract nouns, proper nouns and noun phrases as keywords; if there are still no keywords after rule-based processing, the query question itself is used as the only search term.

4. The method according to claim 1, characterized in that, In step S3, for each search item, the number of candidate tables N and candidate fields M returned by table-first search, and the number of candidate fields P returned by column-first search are all preset fixed values; when there are multiple search items, all search results are merged and deduplicated.

5. The method according to claim 1, characterized in that, The calculation method for statement-level relevance in step S4 is as follows: input the query question or keywords into the embedding model to obtain the query vector, input the text description of the pattern item into the same embedding model to obtain the pattern vector, and calculate the cosine similarity. The token-level relevance is calculated as follows: the query question or keywords are segmented to obtain a token sequence, and the text description of the pattern item is segmented to obtain a token sequence; for each query token, the cosine similarity of its embedding vector with all tokens in the pattern item is calculated, and the maximum value is taken as the matching score of the query token; the matching scores of all query tokens are summed to obtain the original cumulative score; finally, the original cumulative score is divided by the number of query tokens for normalization to obtain the token-level relevance score. The obtained statement-level relevance scores and token-level relevance scores are weighted and summed, and then normalized. Dynamic filtering is performed based on the comprehensive score, with the relevance threshold being a preset fixed threshold ranging from [0,1] and having a default value of 0.6; or a dynamic threshold method can be used, which is calculated based on the mean and standard deviation of the comprehensive scores of all pattern items.

6. The method according to claim 1, characterized in that, In step S5, the unweighted pattern graph is a bipartite association graph containing table nodes and column nodes. During path search, table nodes and column nodes are traversed alternately. The shortest path completion adopts a breadth-first search algorithm, using the selected table nodes as source nodes, and traversing between table nodes and column nodes in the graph to calculate the shortest path between any two source table nodes (based on the number of edges traversed). If the number of table nodes in the shortest path is less than a preset threshold, all table nodes and column nodes on the path are added to the pattern item set to form a complete executable JOIN path.

7. The method according to claim 1, characterized in that, In step S6, the second large language model is a large language model that has been efficiently fine-tuned for the Text-to-SQL task. The fine-tuning training dataset includes natural language questions, corresponding database schema subgraph information, and standard SQL statements. According to the preset prompt template, the database table structure definition, field list, inter-table connection relationship and user natural language query question are filled in sequentially. The prompt template also includes the instruction text "Please generate the corresponding SQL query statement according to the above database pattern"; During the SQL generation process, if the second language model outputs multiple candidate SQL statements, the statement with the highest confidence is selected as the final output; or, the final pattern set and the user question are input into the model multiple times, and the SQL statement with the highest frequency of occurrence is selected through a voting mechanism. Before being delivered to the user, the generated SQL statement undergoes syntax validation and database compatibility checks. If the validation fails, the validation result needs to be combined with the previous prompts and re-entered into the second language model for correction and generation.

Images