Method and system for improving NL2SQL accuracy through natural language question rewriting

Abstract

The invention discloses a method and system for improving NL2SQL accuracy through natural language problem rewriting, and relates to the technical field of natural language processing. Aiming at SQL generation errors caused by language uncertainty and database structure differences, the method comprises the following steps: performing lexical analysis, syntactic analysis, semantic disambiguation and information completion on natural language query statements; through synonym replacement and sentence pattern transformation, generating rewritten query statements with consistent semantics from the completed query statements; evaluating the semantic similarity, the structure matching degree and the logic rationality of the original statement and the rewritten statement by utilizing a pre-training language model and database metadata, and screening an optimal rewritten statement; based on a mapping rule base and a query template, automatically generating an SQL statement from the optimal rewriting statement, and optimizing the SQL statement; and verifying a query result returned after the SQL statement is executed, modifying the SQL statement manually when verification fails, and synchronously updating the mapping rule base. According to the method, high-precision conversion from the natural language query statement to the SQL statement is realized.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of natural language processing, in particular to a method and system for improving NL2SQL accuracy through natural language question rewriting. BACKGROUND

[0002] The prior art has many bottlenecks and defects in the conversion of NL2SQL (Natural Language To Structured Query Language, i.e. natural language to SQL).

[0003] The ambiguity of natural language makes the same semantics have multiple forms of expression, and traditional methods are difficult to accurately align semantics and SQL structure, and cannot handle cases such as polysemy and unclear reference. The field naming differences caused by database heterogeneity are easy to cause mapping errors, and traditional solutions also ignore field aliases and synonyms, causing SQL syntax errors. The incompleteness of user input is often missing key conditions, and traditional methods lack an active completion mechanism, in addition, they lack fault tolerance and are difficult to automatically correct syntax errors or logical contradictions in user input. SUMMARY

[0004] The present application provides a method and system for improving NL2SQL accuracy through natural language question rewriting to address the needs and deficiencies of current technology development. Through sentence rewriting, rewritten sentence evaluation, SQL statement generation, and query result verification, the present application solves the SQL generation errors caused by language uncertainty and database structure differences in traditional methods, and realizes high-precision query conversion.

[0005] In a first aspect, the present application provides a method for improving NL2SQL accuracy through natural language question rewriting, which solves the above technical problems by adopting the following technical solutions:

[0006] A method for improving NL2SQL accuracy through natural language question rewriting, comprising the following steps:

[0007] S1. Based on the user-submitted natural language query statement, perform lexical analysis, syntactic analysis, semantic disambiguation, and information completion;

[0008] S2. Replace the natural language query statement that has been subjected to lexical analysis, syntactic analysis, semantic disambiguation, and information completion with a synonym and transform the sentence pattern to generate a rewritten query statement with consistent semantics;

[0009] S3. Use a pre-trained language model and database metadata to evaluate the semantic similarity, structural matching degree, and logical rationality of the original natural language query statement and the rewritten query statement, and select the optimal rewritten query statement based on the evaluation results.

[0010] S4, based on the pre-constructed mapping rule library and the query template, automatically generating the corresponding SQL statement for the optimal rewriting query statement, and optimizing the SQL statement;

[0011] S5, according to the preset rule, verifying the query result returned after executing the SQL statement, if the verification is successful, the user can view the result through the visual interface, if the verification fails, the SQL statement is modified by manual, and the mapping rule library is updated synchronously.

[0012] Optionally, the step S1 specifically comprises:

[0013] S1.1, using a word segmentation tool to disassemble the natural language query statement into words, and labeling the part of speech of each word;

[0014] S1.2, based on the word set labeled with the part of speech, constructing a syntax tree, and identifying the subject-predicate-object structure and the modification relationship between words in the natural language query statement;

[0015] S1.3, first, eliminating the ambiguity in the natural language query statement by combining WordNet and domain dictionary, then analyzing the reference relationship in the natural language query statement through co-reference resolution technology, and finally completing the missing conditions of the natural language query statement by default rules or user prompts.

[0016] Optionally, the step S3 specifically comprises:

[0017] S3.1, using a pre-trained language model to evaluate the semantic similarity between the original natural language query statement and the rewritten query statement: ① first, text preprocessing is performed on the original natural language query statement and the rewritten query statement, including removing irrelevant special symbols, unifying case and adding standard input marks required by the pre-trained language model, to ensure that the preprocessed text completely conforms to the input format specification of the model; ② then, the preprocessed original natural language query statement and the rewritten query statement are respectively processed by the word segmentation tool of the pre-trained language model, the segmented text token is mapped to the ID sequence corresponding to the model vocabulary, and the ID sequence is truncated or padded according to the maximum input length of the model, while the corresponding attention mask is generated, which is used to distinguish the real text token from the padding value, to ensure that the model only encodes and calculates the semantic of the effective text token; ③ then, the generated ID sequence and attention mask are input into the pre-trained language model, and through the semantic encoding layer of the model, the original natural language query statement and the rewritten query statement are respectively converted into 768-dimensional semantic vectors; then the semantic matching degree of the original natural language query statement and the rewritten query statement is calculated one by one through the cosine similarity formula, and the calculation formula is In the formula, q is a 768-dimensional semantic vector corresponding to the original natural language query sentence, and q' is a 768-dimensional semantic vector corresponding to the rewritten query sentence; 4. According to a preset similarity threshold, the similarity calculation result of the rewritten query sentence is filtered, and the rewritten query sentence satisfying the similarity threshold is retained and arranged in descending order of the similarity value, and finally the rewritten query sentence candidate set meeting the semantic matching requirement is output.

[0018] S3.2, evaluating the structural matching degree of the original natural language query sentence and the rewritten query sentence by using database metadata: establishing a database metadata mapping table, verifying the matching of entities, attributes and database fields of each rewritten query sentence in the candidate set, and filtering out the rewritten query sentences with non-existent fields or unmatched attributes in the candidate set;

[0019] S3.3, evaluating the logical rationality of the original natural language query sentence and the rewritten query sentence by using database metadata: checking the condition logic self-consistency and operation compliance of each rewritten query sentence in the candidate set, using regular expression to exclude unsafe queries, and finally selecting the rewritten query sentence with the highest similarity value as the optimal rewritten query sentence.

[0020] Preferably, the pre-trained language model adopts a pre-trained Transformer architecture, and the semantic vector distance is calculated by cosine similarity.

[0021] Optionally, the step S4 specifically comprises:

[0022] S4.1, constructing a mapping rule library of natural language and SQL elements, which contains mapping rules of operation words and SQL elements, operators and SQL elements, and time expressions and SQL elements; at the same time, designing three types of query templates of simple, conditional and aggregation which support dynamic filling of parameters, and providing adaptation basis for different types of natural language query sentences;

[0023] S4.2, structurally analyzing the optimal rewritten query sentence, extracting key metadata in the query sentence according to the mapping rules in the mapping rule library; based on the characteristics of the key metadata, matching the only suitable template from the three types of query templates; filling the extracted key metadata accurately according to the preset placeholders of the matched template, and generating a SQL statement with legal syntax and logical correspondence after filling all the placeholders;

[0024] S4.3, analyze the execution plan of the SQL statement by using the database EXPLAIN tool, and carry out targeted optimization: adjust the JOIN execution order of the SQL statement, add an index to the query field that exceeds the preset query frequency, combine the repeated query conditions in the SQL statement, automatically add a LIMIT 100 OFFSET 0 limit to the SQL statement with large data volume, and finally complete the overall optimization of the SQL statement.

[0025] In a second aspect, the application provides a system for improving the accuracy of NL2SQL through natural language question rewriting, which solves the above technical problems by adopting the following technical solutions:

[0026] A system for improving the accuracy of NL2SQL through natural language question rewriting, comprising:

[0027] A question preprocessing module for performing morphological analysis, syntactic analysis, semantic disambiguation, and information completion based on a natural language query statement submitted by a user;

[0028] A rewritten statement generation module for generating a rewritten query statement with consistent semantics by synonym replacement and sentence transformation on the natural language query statement that has been subjected to morphological analysis, syntactic analysis, semantic disambiguation, and information completion;

[0029] A rewritten evaluation and screening module for evaluating the semantic similarity, structural matching degree, and logical rationality of the original natural language query statement and the rewritten query statement by using a pre-trained language model and database metadata, and screening the optimal rewritten query statement according to the evaluation results;

[0030] An SQL generation and optimization module for automatically generating a corresponding SQL statement from the optimal rewritten query statement based on a pre-constructed mapping rule library and query template, and performing targeted optimization on the SQL statement;

[0031] A result verification and feedback module for verifying the query result returned after executing the SQL statement according to preset rules, and if the verification is successful, providing the user with a visual interface for viewing, and if the verification fails, manually modifying the SQL statement and synchronously updating the mapping rule library.

[0032] Optionally, the question preprocessing module specifically comprises:

[0033] A morphological analysis unit for using a word segmentation tool to disassemble the natural language query statement into words and label the part of speech of each word;

[0034] A syntactic analysis unit for constructing a syntactic tree based on the set of words with labeled parts of speech, identifying the subject-predicate-object structure and the modification relationship between words in the natural language query statement;

[0035] The disambiguation completion unit is used to eliminate ambiguity in the natural language query statement by combining WordNet and a domain dictionary, analyze the reference relationship in the natural language query statement by co-reference resolution technology, and complete the missing conditions of the natural language query statement by default rules or user prompts.

[0036] Optionally, the involved rewriting evaluation screening module specifically comprises:

[0037] The semantic similarity evaluation unit is used to evaluate the semantic similarity between the original natural language query statement and the rewritten query statement by using a pre-trained language model, and the specific evaluation process is as follows: ①first, the original natural language query statement and the rewritten query statement are pre-processed, including removing irrelevant special symbols, unifying case and adding standard input marks required by the pre-trained language model, to ensure that the pre-processed text fully complies with the input format specifications of the model; ②then, the pre-processed original natural language query statement and the rewritten query statement are respectively processed by the tokenizer of the pre-trained language model, the tokenized text is mapped to the ID sequence corresponding to the model vocabulary, and the ID sequence is truncated or padded according to the maximum input length of the model, while the corresponding attention mask is generated, which is used to distinguish the real text token from the padding value, to ensure that the model only performs semantic encoding calculation on the effective text token; ③then, the generated ID sequence and attention mask are input into the pre-trained language model, and the original natural language query statement and the rewritten query statement are respectively converted into 768-dimensional semantic vectors through the semantic encoding layer of the model; then the semantic matching degree of the original natural language query statement and the rewritten query statement is calculated one by one through the cosine similarity formula, and the calculation formula is In the formula, q is the 768-dimensional semantic vector corresponding to the original natural language query statement, and q' is the 768-dimensional semantic vector corresponding to the rewritten query statement; ④according to the preset similarity threshold, the similarity calculation results of the rewritten query statement are screened, the rewritten query statements satisfying the similarity threshold are retained, and the rewritten query statements are arranged in descending order of similarity value, and finally the rewritten query statement candidate set meeting the semantic matching requirements is output.

[0038] The structural matching degree evaluation unit is used to establish a database metadata mapping table, verify the matching of entities, attributes and database fields of each rewritten query statement in the candidate set, and filter out the rewritten query statements in the candidate set whose fields do not exist or attributes do not match.

[0039] The logical reasonableness evaluation unit is used to check the self-consistency of the condition logic and the compliance of the operation of each rewritten query statement in the candidate set, and to exclude unsafe queries by using regular expressions, finally to select the rewritten query statement with the highest similarity value as the optimal rewritten query statement and output.

[0040] Preferably, the pre-trained language model adopts a pre-trained Transformer architecture, and the semantic vector distance is calculated by cosine similarity.

[0041] Optionally, the SQL generation optimization module specifically includes:

[0042] A rule base construction unit is configured to construct a mapping rule base of natural language and SQL elements, the mapping rule base including mapping rules of operation words and SQL elements, operators and SQL elements, and time expressions and SQL elements;

[0043] A query template design unit is configured to design three types of query templates of simple, condition, and aggregation that support parameter dynamic filling, and provide adaptation basis for different types of natural language query statements;

[0044] A SQL statement generation unit is configured to perform structured analysis on the optimal rewritten query statement, extract key metadata in the query statement according to the mapping rules in the mapping rule base, match a unique template from the three types of query templates based on the features of the key metadata, accurately fill the extracted key metadata according to the preset placeholders of the matched template, and generate a SQL statement that is grammatically correct and logically corresponding after filling all the placeholders;

[0045] A SQL statement optimization unit is configured to analyze the execution plan of the SQL statement by using a database EXPLAIN tool, and perform targeted optimization, including adjusting the JOIN execution order of the SQL statement, adding an index for a query field that exceeds a preset query frequency, merging repeated query conditions in the SQL statement, and automatically adding a LIMIT 100 OFFSET 0 limit to a SQL statement for large data volume query, to finally complete the overall optimization of the SQL statement.

[0046] Compared with the prior art, the method and system for improving the NL2SQL accuracy rate through natural language question rewriting of the present application have the following beneficial effects:

[0047] The present application solves the SQL generation errors caused by language uncertainty and database structure differences in traditional methods through sentence rewriting, rewritten sentence evaluation, SQL statement generation, and query result verification, and realizes high-precision conversion of natural language query statements to SQL statements; and is suitable for intelligent data query, enterprise-level data analysis, and other scenarios. BRIEF DESCRIPTION OF DRAWINGS

[0048] FIG. 1 is a method flowchart of the present application; Figure 1 FIG. 2 is a system module connection block diagram of the present application.

[0049] FIG. 3 is a system module connection block diagram of the present application. Figure 2 FIG. 4 is a system module connection block diagram of the present application. DETAILED DESCRIPTION

[0050] In order to make the technical solutions, the technical problems solved and the technical effects of the present application clearer, the technical solutions of the present application are described clearly and completely in the following in combination with specific embodiments.

[0051] Embodiment one:

[0052] Referring to the accompanying drawings Figure 1 The embodiment proposes a method for improving the accuracy of NL2SQL through natural language question rewriting, which includes the following steps:

[0053] S1, based on the natural language query statement submitted by the user, performing morphological analysis, syntactic analysis, semantic disambiguation and information completion; this operation specifically includes:

[0054] S1.1, using a word segmentation tool (such as spaCy) to disassemble the natural language query statement into words, and labeling the part of speech (noun, verb, time word, etc.) of each word;

[0055] S1.2, based on the set of words labeled with part of speech, construct a syntactic tree to identify the subject-predicate-object structure and the modification relationship between words in the natural language query statement;

[0056] Example: input "query the product with sales of more than 5 million in January 2024", the word segmentation result is [query, January 2024, sales, more than, 5 million, product], the syntactic analysis determines that "query" is the main verb, "product" is the object, and the rest are adjectives.

[0057] S1.3, first eliminate the ambiguity in the natural language query statement by combining WordNet and domain dictionary (such as "inventory" is distinguished as "inventory table" or "inventory quantity"), then analyze the reference relationship in the natural language query statement through co-reference resolution technology (such as "the department" is associated with the previous "department ID = 001"), and finally complete the missing conditions of the natural language query statement through default rules or user prompts (if the user does not specify the time, the default is to complete "the last 30 days"; if the field is not clear, prompt the user to choose, such as "sales: total sales / daily sales").

[0058] S2, for the natural language query statement after morphological analysis, syntactic analysis, semantic disambiguation and information completion, generate multiple rewritten query statements with consistent semantics through synonym replacement (such as "product"→"commodity") and sentence transformation (such as active sentence→passive sentence).

[0059] Example: the original question "filter users with age greater than 30" can be rewritten as "find records of users with age greater than 30", "which users have age greater than 30" and so on.

[0060] S3. Using a pre-trained language model (e.g., a pre-trained Transformer architecture, specifically BERT, with 5GB of general text (Wikipedia) + 1GB of domain-specific text from a database, and a fine-tuning strategy of supervised training on an annotated NL2SQL dataset) and database metadata, evaluate the semantic similarity, structural matching, and logical rationality of the original natural language query and the rewritten query. Based on the evaluation results, select the optimal rewritten query. This operation specifically includes:

[0061] S3.1. Evaluate the semantic similarity between the original natural language query and the rewritten query using the BERT model: ① First, preprocess the original and rewritten query statements, including removing irrelevant special characters, unifying capitalization, and adding standard input markers required by the BERT model (such as [CLS] prefix markers and [SEP] separator markers), ensuring that the preprocessed text fully conforms to the model's input format specifications; ② Then, use the BERT model's tokenizer to segment the preprocessed original and rewritten query statements separately, mapping the segmented text tokens to ID sequences corresponding to the model's vocabulary, and truncating or padding the ID sequences according to the model's maximum input length (PAD operation), while simultaneously generating corresponding attention masks. The mask is used by the model to distinguish between real text tokens and padding values ​​(PADs), ensuring that the model only performs semantic encoding calculations on valid text tokens; ③ Subsequently, the generated ID sequence and attention mask are input into the BERT model, and the model's semantic encoding layer (usually using the output vector corresponding to the [CLS] position as the global semantic representation of the text) transforms the original natural language query and the rewritten query into 768-dimensional semantic vectors respectively; then, the semantic matching degree between the original natural language query and the rewritten query is calculated one by one using the cosine similarity formula, the calculation formula is as follows: In the formula, q is the 768-dimensional semantic vector corresponding to the original natural language query statement, and q' is the 768-dimensional semantic vector corresponding to the rewritten query statement; ④ Based on the preset similarity threshold (which can be set to 0.85), the similarity calculation results of the rewritten query statements are filtered, and the rewritten query statements that meet the similarity threshold are retained and sorted in descending order of similarity value. Finally, a candidate set of rewritten query statements that meet the semantic matching requirements is output.

[0062] S3.2 Utilize database metadata to evaluate the structural matching degree between the original natural language query statement and the rewritten query statement: Establish a database metadata mapping table, verify the matching of the entity, attribute and database field of each rewritten query statement in the candidate set, and filter out the rewritten query statements in the candidate set that do not have fields or whose attributes do not match.

[0063] S3.3 Utilize database metadata to evaluate the logical rationality of the original natural language query statement and the rewritten query statement: check the conditional logic consistency of each rewritten query statement in the candidate set (e.g., the contradiction of "time is in 2023 and 2024") and the legality of operators (e.g., the syntax error of "age = greater than 30"). Use regular expressions to exclude unsafe queries (e.g., "SELECT * FROM *"), and finally select the rewritten query statement with the highest similarity value as the optimal rewritten query statement.

[0064] S4. Based on a pre-built mapping rule base and query template, the optimal rewritten query statement is automatically generated into a corresponding SQL statement, and the SQL statement is optimized accordingly. This operation specifically includes:

[0065] S4.1 Initially, a mapping rule base for natural language and SQL elements was constructed by manually annotating 100,000+ NL2SQL samples. Subsequently, remote supervised learning was used to automatically expand the mapping rule base (such as extracting the "city → city" mapping from the annotated data).

[0066] This mapping rule base includes mapping rules between operators and SQL elements (such as "search / retrieve" → SELECT, "filter / select" → WHERE), mapping rules between operators and SQL elements (such as "greater than / greater than" → >, "at least / not less than" → > =), and mapping rules between time expressions and SQL elements (such as "2024 year Q1" → DATE_FORMAT(order_time,'%YQ%m') = '2024-Q1').

[0067] Specific example of the mapping rule base: rewrite "sales amount greater than 5 million" as "sales_amount>5,000,000";

[0068] At the same time, a simple query template that supports dynamic parameter filling (such as "Query {entity}" →) is designed.

[0069] SELECT * FROM {table_name}, conditional query templates (e.g., "{entity} in {attribute} {operator} {value}" → "SELECT * FROM {table_name} WHERE {column} {operator} {value}"), aggregate query templates (e.g., "{aggregate function} {attribute} in {entity}" →

[0070] The function “SELECT{agg_func}({column})FROM{table_name}” provides adaptation criteria for different types of natural language query statements.

[0071] S4.2 Perform structured parsing on the optimal rewritten query statement, extract key metadata from the query statement based on the mapping rules in the mapping rule base; based on the characteristics of the key metadata, match the uniquely suitable template from the three types of query templates; accurately fill the extracted key metadata according to the preset placeholders of the matching template, and after completing the filling of all placeholders, generate a syntactically valid and logically corresponding SQL statement.

[0072] S4.3 Analyze the execution plan of SQL statements using the database EXPLAIN tool and carry out targeted optimization: adjust the JOIN execution order of SQL statements, add indexes for query fields that exceed the preset query frequency (such as order_at, amt), merge duplicate query conditions in SQL statements (such as "amt>5 million AND amt>4 million" → "amt>5 million"), automatically add LIMIT 100OFFSET 0 limit to SQL statements that query large amounts of data, and finally complete the overall optimization of SQL statements.

[0073] The following is an example of automatically generating the corresponding SQL statement from the optimal rewritten query statement:

[0074] Optimal rewrite of the query statement: Query records in the products table where amt > 5000000 and order_at is in January 2024;

[0075] Generate the corresponding SQL statement:

[0076] SELECT * FROM products

[0077] WHERE amt>5000000

[0078] AND DATE_FORMAT(order_at,'%Y-%m')='2024-01'

[0079] S5. Verify the query results returned after executing the SQL statement according to preset rules. If the verification is successful, the results will be displayed to the user through a visual interface. If the verification fails, the SQL statement will be modified manually and the mapping rule library will be updated synchronously.

[0080] The method of this embodiment has the following advantages: ① The accuracy rate is significantly improved: Verified by more than 100,000 test samples, the accuracy rate of SQL generation has increased from 72% of the traditional method to 89%, especially in complex conditional queries, with an increase of more than 20%; ② The robustness is enhanced: It supports the completion of incomplete questions (coverage rate of 90%+), correction of syntax errors (such as automatically supplementing the "de" structure), and the error tolerance rate for noisy inputs is increased by 50%; ③ The scalability is optimized: The rule library and templates are maintained independently. When adding a new database type, only the metadata mapping needs to be updated, and the adaptation period is shortened from 2 weeks to 1 day; ④ The user experience is improved: Interactive rewriting suggestions are provided, and users can manually select the optimal version, reducing the query failure rate by 60%.

[0081] Embodiment 2:

[0082] Refer to the attached Figure 2 , this embodiment proposes a system for improving the accuracy rate of NL2SQL through natural language question rewriting, which includes:

[0083] A question preprocessing module, which is used to perform lexical analysis, syntactic analysis, semantic disambiguation, and information completion based on the natural language query statement submitted by the user;

[0084] A rewritten statement generation module, which is used to generate a semantically consistent rewritten query statement for the natural language query statement after lexical analysis, syntactic analysis, semantic disambiguation, and information completion through synonym replacement (such as "product" → "commodity") and sentence pattern transformation (such as active sentence → passive sentence);

[0085] A rewritten evaluation and screening module, which is used to evaluate the semantic similarity, structural matching degree, and logical rationality of the original natural language query statement and the rewritten query statement by using a pre-trained language model (such as BERT) and database metadata, and screen the optimal rewritten query statement according to the evaluation results;

[0086] An SQL generation optimization module, which is used to automatically generate the corresponding SQL statement for the optimal rewritten query statement based on the pre-constructed mapping rule library and query template, and perform targeted optimization on the SQL statement;

[0087] A result verification and feedback module, which is used to verify the query result returned after executing the SQL statement according to the preset rules. If the verification is successful, it is provided for the user to view through the visual interface. If the verification fails, the SQL statement is modified manually, and the mapping rule library is updated synchronously.

[0088] In this embodiment, the involved question preprocessing module specifically includes:

[0089] A lexical analysis unit, which is used to disassemble the natural language query statement into words by using a word segmentation tool (such as spaCy) and mark the词性 (noun, verb, time word, etc.) of each word;

[0090] The syntactic analysis unit is used to construct a syntactic tree based on a set of vocabularies labeled with parts of speech, and to identify the subject-verb-object structure and the modification relationships between words in natural language query statements;

[0091] The disambiguation and completion unit first combines WordNet and the domain dictionary to eliminate ambiguity in natural language queries (e.g., distinguishing "inventory" from "inventory table" or "inventory quantity"). Then, it uses coreference resolution technology to parse the referential relationships in the natural language query (e.g., "this department" is associated with the preceding "department ID=001"). Finally, it completes the missing conditions of the natural language query through default rules or user prompts (if the user does not specify a time, it defaults to "last 30 days"; if the field is unclear, it prompts the user to select, such as "sales revenue: total sales revenue / average daily sales revenue").

[0092] In this embodiment, the rewrite evaluation and screening module specifically includes:

[0093] The semantic similarity evaluation unit is used to evaluate the semantic similarity between the original natural language query and the rewritten query using a pre-trained language model (e.g., a pre-trained Transformer architecture, specifically BERT, with 5GB of general text (Wikipedia) + 1GB of domain-specific text from a database, and a fine-tuning strategy of supervised training on an annotated NL2SQL dataset). The specific evaluation process is as follows: ① First, the original and rewritten query statements are pre-processed, including removing irrelevant special characters, unifying capitalization, and adding standard input markers required by the pre-trained language model (e.g., [CLS] prefix marker, [SEP] separator marker), ensuring that the pre-processed text fully conforms to the model's input format specifications; ② Then, the pre-trained language model (e.g., BERT) is used to segment the pre-processed original and rewritten query statements using its associated tokenizer. The segmented text tokens are mapped to ID sequences corresponding to the model's vocabulary, and the ID sequences are truncated or padded (PAD) according to the model's maximum input length. Simultaneously, a corresponding attention mask is generated. The mask is used by the model to distinguish between real text tokens and padding values ​​(PADs), ensuring that the model only performs semantic encoding calculations on valid text tokens; ③ Subsequently, the generated ID sequence and attention mask are input into a pre-trained language model (such as BERT). Through the model's semantic encoding layer (usually using the output vector corresponding to the [CLS] position as the global semantic representation of the text), the original natural language query and the rewritten query are transformed into 768-dimensional semantic vectors respectively; then, the semantic matching degree between the original natural language query and the rewritten query is calculated one by one using the cosine similarity formula, the calculation formula is as follows: In the formula, q is the 768-dimensional semantic vector corresponding to the original natural language query statement, and q' is the 768-dimensional semantic vector corresponding to the rewritten query statement; ④ Based on the preset similarity threshold, the similarity calculation results of the rewritten query statements are filtered, the rewritten query statements that meet the similarity threshold are retained, and they are arranged in descending order of similarity value, and finally the candidate set of rewritten query statements that meet the semantic matching requirements is output.

[0094] The structure matching evaluation unit is used to establish a database metadata mapping table, verify the matching of entities, attributes and database fields of each rewritten query statement in the candidate set, and filter out rewritten query statements in the candidate set that do not exist or whose attributes do not match.

[0095] The logical rationality evaluation unit checks the conditional logical consistency (e.g., the contradiction of "time is in 2023 and 2024") and operator legality (e.g., the syntax error of "age = greater than 30") of each rewritten query statement in the candidate set. It uses regular expressions to exclude unsafe queries (e.g., "SELECT * FROM *") and finally selects the rewritten query statement with the highest similarity value as the optimal rewritten query statement and outputs it.

[0096] In this embodiment, the SQL generation optimization module specifically includes:

[0097] The rule base construction unit is used to build a mapping rule base between natural language and SQL elements (initially, the mapping rule base between natural language and SQL elements is built by manually annotating 100,000+ NL2 SQL samples, and subsequently automatically expanded by remote supervised learning (such as extracting the mapping of "city" from the annotated data). This mapping rule base includes mapping rules between operators and SQL elements (such as "search / retrieve" → SELECT, "filter / select" → WHERE), mapping rules between operators and SQL elements (such as "greater than / more than" → >, "at least / not less than" → =), and mapping rules between time expressions and SQL elements (such as "2024 year Q1" → DATE_FORMAT(order_time,'%YQ%m') = '2024-Q1').

[0098] The query template design unit is used to design simple query templates that support dynamic parameter filling (such as "Query {entity}" → "SELECT * FROM {table_name}"), conditional query templates (such as "{entity} in {attribute}{operator}{value}" → "SELECT * FROM {table_name} WHERE {column}{operator}{value}"), and aggregate query templates (such as "{aggregate function}{attribute} in {entity}" → "SELECT {agg_func}({column}) FROM {table_name}"), providing adaptation criteria for different types of natural language query statements.

[0099] The SQL statement generation unit is used to perform structured parsing of the optimal rewritten query statement, extract key metadata from the query statement based on the mapping rules in the mapping rule base, match a uniquely suitable template from three types of query templates based on the characteristics of the key metadata, accurately fill the extracted key metadata according to the preset placeholders of the matching template, and generate a syntactically valid and logically corresponding SQL statement after all placeholders are filled.

[0100] The SQL statement optimization unit utilizes the database EXPLAIN tool to analyze the execution plan of SQL statements and perform targeted optimizations: adjusting the JOIN execution order of SQL statements, adding indexes to query fields that exceed the preset query frequency (such as order_at, amt), merging duplicate query conditions in SQL statements (such as "amt>5 million AND amt>4 million" → "amt>5 million"), and automatically adding a LIMIT 100OFFSET 0 limit to SQL statements that query large amounts of data, ultimately completing the overall optimization of the SQL statement.

[0101] The following is an example of automatically generating the corresponding SQL statement from the optimal rewritten query statement:

[0102] Optimal rewrite of the query statement: Query records in the products table where amt > 5000000 and order_at is in January 2024;

[0103] Generate the corresponding SQL statement:

[0104] SELECT * FROM products

[0105] WHERE amt>5000000

[0106] AND DATE_FORMAT(order_at,'%Y-%m')='2024-01'

[0107] In summary, the method and system of this invention for improving the accuracy of NL2SQL by rewriting natural language problems solves the SQL generation errors caused by language uncertainty and database structure differences in traditional methods by rewriting statements, evaluating rewritten statements, generating SQL statements, and verifying query results. It achieves high-precision conversion of natural language query statements to SQL statements.

[0108] The above specific examples illustrate the principles and implementation methods of the present invention in detail. These embodiments are merely for the purpose of helping to understand the core technical content of the present invention. Based on the above specific embodiments of the present invention, any improvements and modifications made to the present invention by those skilled in the art without departing from the principles of the present invention should fall within the patent protection scope of the present invention.

Claims

1. A method for improving NL2SQL accuracy by rewriting natural language questions, characterized in that, Includes the following steps: S1. Based on the natural language query statement submitted by the user, perform lexical analysis, syntactic analysis, semantic disambiguation and information completion; S2. For natural language query statements that have undergone lexical analysis, syntactic analysis, semantic disambiguation and information completion, generate semantically consistent rewritten query statements through synonym replacement and sentence transformation; S3. Using pre-trained language models and database metadata, evaluate the semantic similarity, structural matching degree and logical rationality of the original natural language query statement and the rewritten query statement, and select the optimal rewritten query statement based on the evaluation results. S4. Based on the pre-built mapping rule base and query template, the optimal rewritten query statement is automatically generated into the corresponding SQL statement, and the SQL statement is optimized accordingly. S5. Verify the query results returned after executing the SQL statement according to preset rules. If the verification is successful, the results will be displayed to the user through a visual interface. If the verification fails, the SQL statement will be modified manually and the mapping rule library will be updated synchronously.

2. The method for improving NL2SQL accuracy by rewriting natural language problems according to claim 1, characterized in that, Step S1 specifically includes: S1.1 Use word segmentation tools to break down natural language query statements into words and label the part of speech of each word; S1.2 Based on the set of words with labeled parts of speech, construct a syntax tree to identify the subject-verb-object structure and the modification relationship between words in natural language query statements; S1.3 First, combine WordNet and domain dictionary to eliminate ambiguity in natural language query statements. Then, use coreference resolution technology to parse the referential relationships in natural language query statements. Finally, use default rules or user prompts to complete the missing conditions of natural language query statements.

3. The method for improving NL2SQL accuracy by rewriting natural language problems according to claim 1, characterized in that, Step S3 specifically includes: S3.

1. Evaluate the semantic similarity between the original natural language query statement and the rewritten query statement using a pre-trained language model: ① First, perform text preprocessing on the original natural language query statement and the rewritten query statement, including removing irrelevant special symbols, unifying case, and adding standard input tokens required by the pre-trained language model, ensuring that the preprocessed text fully conforms to the input format specification of the model; ② Then, through the tokenizer配套 with the pre-trained language model, perform tokenization on the preprocessed original natural language query statement and the rewritten query statement respectively, map the tokenized text tokens to the ID sequences corresponding to the model vocabulary, and perform truncation or padding operations on the ID sequences according to the maximum input length of the model. At the same time, generate the corresponding attention mask, which is used by the model to distinguish real text tokens from padding values, ensuring that the model only performs semantic encoding calculations on valid text tokens; ③ Subsequently, input the generated ID sequences and attention masks into the pre-trained language model. Through the semantic encoding layer of the model, convert the original natural language query statement and the rewritten query statement into 768-dimensional semantic vectors respectively; then calculate the semantic matching degree between the original natural language query statement and the rewritten query statement one by one through the cosine similarity formula. The calculation formula is In the formula, q is the 768-dimensional semantic vector corresponding to the original natural language query statement, and q' is the 768-dimensional semantic vector corresponding to the rewritten query statement; ④ According to the preset similarity threshold, screen the similarity calculation results of the rewritten query statements, retain the rewritten query statements that meet the similarity threshold, and sort them in descending order of similarity values. Finally, output the candidate set of rewritten query statements that meet the semantic matching requirements; S3.2 Utilize database metadata to evaluate the structural matching degree between the original natural language query statement and the rewritten query statement: Establish a database metadata mapping table, verify the matching of the entity, attribute and database field of each rewritten query statement in the candidate set, and filter out the rewritten query statements in the candidate set that do not have fields or whose attributes do not match. S3.3 Utilize database metadata to evaluate the logical rationality of the original natural language query statement and the rewritten query statement: check the conditional logic consistency and operator validity of each rewritten query statement in the candidate set, use regular expressions to exclude unsafe queries, and finally select the rewritten query statement with the highest similarity value as the optimal rewritten query statement.

4. The method for improving NL2SQL accuracy by rewriting natural language problems according to claim 3, characterized in that, The pre-trained language model adopts a pre-trained Transformer architecture and calculates the semantic vector distance through cosine similarity.

5. The method for improving NL2SQL accuracy by rewriting natural language problems according to claim 1, characterized in that, Step S4 specifically includes: S4.1 Construct a mapping rule base for natural language and SQL elements. This mapping rule base includes mapping rules for operation words and SQL elements, operators and SQL elements, and time expressions and SQL elements. At the same time, design three types of query templates that support dynamic parameter filling: simple, conditional, and aggregate, to provide adaptation basis for different types of natural language query statements. S4.2 Perform structured parsing on the optimal rewritten query statement, extract key metadata from the query statement based on the mapping rules in the mapping rule base; based on the characteristics of the key metadata, match the uniquely suitable template from the three types of query templates; accurately fill the extracted key metadata according to the preset placeholders of the matching template, and after completing the filling of all placeholders, generate a syntactically valid and logically corresponding SQL statement. S4.3 Analyze the execution plan of the SQL statement using the database EXPLAIN tool and carry out targeted optimization: adjust the JOIN execution order of the SQL statement, add indexes to query fields that exceed the preset query frequency, merge duplicate query conditions in the SQL statement, automatically add LIMIT 100OFFSET 0 limit to SQL statements that query large amounts of data, and finally complete the overall optimization of the SQL statement.

6. A system for improving NL2SQL accuracy by rewriting natural language problems, characterized in that, It includes: The question preprocessing module is used to perform lexical analysis, syntactic analysis, semantic disambiguation, and information completion based on the natural language query statements submitted by users. The rewritten query generation module is used to generate semantically consistent rewritten query statements from natural language query statements that have undergone lexical analysis, syntactic analysis, semantic disambiguation, and information completion through synonym replacement and sentence transformation. The rewrite evaluation and filtering module is used to evaluate the semantic similarity, structural matching degree and logical rationality of the original natural language query statement and the rewritten query statement using a pre-trained language model and database metadata, and to select the optimal rewritten query statement based on the evaluation results. The SQL generation and optimization module is used to automatically generate corresponding SQL statements from the optimal rewritten query statements based on a pre-built mapping rule base and query templates, and to perform targeted optimization on the SQL statements. The result verification and feedback module is used to verify the query results returned after executing the SQL statement according to preset rules. If the verification is successful, the results are displayed to the user through a visual interface. If the verification fails, the SQL statement is modified manually and the mapping rule library is updated synchronously.

7. The system for improving NL2SQL accuracy by rewriting natural language problems according to claim 6, characterized in that, The problem preprocessing module specifically includes: The lexical analysis unit is used to break down natural language queries into words using word segmentation tools and label the part of speech of each word. The syntactic analysis unit is used to construct a syntactic tree based on a set of vocabularies labeled with parts of speech, and to identify the subject-verb-object structure and the modification relationships between words in natural language query statements; The disambiguation and completion unit is used to first eliminate ambiguity in natural language queries by combining WordNet and domain dictionaries, then parse the referential relationships in natural language queries through coreference resolution technology, and finally complete the missing conditions of natural language queries through default rules or user prompts.

8. A system for improving NL2SQL accuracy by rewriting natural language problems according to claim 6, characterized in that, The rewrite evaluation and screening module specifically includes: A semantic similarity evaluation unit is used to evaluate the semantic similarity between the original natural language query statement and the rewritten query statement by using a pre-trained language model. The specific evaluation process is as follows: ① First, perform text preprocessing on the original natural language query statement and the rewritten query statement, including removing irrelevant special symbols, unifying case, and adding standard input tokens required by the pre-trained language model, to ensure that the preprocessed text fully complies with the input format specifications of the model; ② Then, through the tokenizer provided with the pre-trained language model, perform tokenization on the preprocessed original natural language query statement and the rewritten query statement respectively, map the tokenized text tokens to the ID sequences corresponding to the model vocabulary, and perform truncation or padding operations on the ID sequences according to the maximum input length of the model. At the same time, generate corresponding attention masks, which are used by the model to distinguish real text tokens from padding values, ensuring that the model only performs semantic encoding calculations on valid text tokens; ③ Subsequently, input the generated ID sequences and attention masks into the pre-trained language model. Through the semantic encoding layer of the model, convert the original natural language query statement and the rewritten query statement into 768-dimensional semantic vectors respectively; then calculate the semantic matching degree between the original natural language query statement and the rewritten query statement one by one through the cosine similarity formula. The calculation formula is where q is the 768-dimensional semantic vector corresponding to the original natural language query statement, and q' is the 768-dimensional semantic vector corresponding to the rewritten query statement; ④ According to the preset similarity threshold, screen the similarity calculation results of the rewritten query statements, retain the rewritten query statements that meet the similarity threshold, and sort them in descending order according to the similarity value. Finally, output the candidate set of rewritten query statements that meet the semantic matching requirements; The structure matching evaluation unit is used to establish a database metadata mapping table, verify the matching of entities, attributes and database fields of each rewritten query statement in the candidate set, and filter out rewritten query statements in the candidate set that do not exist or whose attributes do not match. The logical rationality evaluation unit is used to check the conditional logical consistency and operator legality of each rewritten query statement in the candidate set, use regular expressions to exclude unsafe queries, and finally select the rewritten query statement with the highest similarity value as the optimal rewritten query statement and output it.

9. A system for improving NL2SQL accuracy by rewriting natural language problems according to claim 8, characterized in that, The pre-trained language model adopts a pre-trained Transformer architecture and calculates the semantic vector distance through cosine similarity.

10. A system for improving NL2SQL accuracy by rewriting natural language problems according to claim 6, characterized in that, The SQL generation optimization module specifically includes: The rule base construction unit is used to build a mapping rule base between natural language and SQL elements. This mapping rule base contains mapping rules between operator words and SQL elements, operators and SQL elements, and time expressions and SQL elements. The query template design unit is used to design three types of query templates that support dynamic parameter filling: simple, conditional, and aggregate, providing an adaptation basis for different types of natural language query statements. The SQL statement generation unit is used to perform structured parsing of the optimal rewritten query statement, extract key metadata from the query statement based on the mapping rules in the mapping rule base, match a uniquely suitable template from three types of query templates based on the characteristics of the key metadata, accurately fill the extracted key metadata according to the preset placeholders of the matching template, and generate a syntactically valid and logically corresponding SQL statement after all placeholders are filled. The SQL statement optimization unit is used to analyze the execution plan of SQL statements using the database EXPLAIN tool and optimize them accordingly: adjusting the JOIN execution order of SQL statements, adding indexes to query fields that exceed the preset query frequency, merging duplicate query conditions in SQL statements, and automatically adding LIMIT 100OFFSET 0 limits to SQL statements that query large amounts of data, ultimately completing the overall optimization of SQL statements.

Images