Large model Text-to-SQL conditional clause improvement method and system based on fuzzy recall field value enhancement

Abstract

The invention relates to the technical field of natural language processing and database query, and particularly discloses a large model Text-to-SQL conditional clause improvement method and device based on fuzzy recall field value enhancement, which are used for solving the problem of SQL query errors caused by inaccurate field values input by a user. According to the main scheme, the method comprises the following steps: extracting and recognizing date, field name and field value keywords in user input through a large model entity; 2) dynamically acquiring related field values from a database by combining a multi-stage recall mechanism of ElasticSearch (ES) fuzzy matching and vector semantic retrieval, and improving the recall accuracy through threshold screening; (3) organizing recall field values into structured contexts, injecting the structured contexts into a large model cue word template, and guiding the structured contexts to generate SQL condition clauses (such as WHERE clauses) containing accurate field values; and (4) changing a real-time synchronous database into an ES and vector database to ensure data consistency.

Description

Technical Field

[0001] The present invention relates to the technical fields of natural language processing, database query, and information retrieval. Specifically, it relates to a method and system for improving the conditional clauses of the large model Text-to-SQL based on enhanced fuzzy recall field values, which is used to solve the problem of query errors caused by inaccurate user input field values. Background Art

[0002] Existing Text-to-SQL technologies can generate structured SQL query statements through the SQL code learning and context understanding capabilities of large language models. However, when there are differences between the field values input by users and the actual values in the database (such as abbreviations, typos, or non-standard names), the generated SQL conditional clauses (such as WHERE brand='**Cha Ji') are likely to not match the accurate values in the database (such as WHERE brand='CHAGEE**Cha Ji'), resulting in incorrect query results. Traditional methods use large models to directly generate, and when the results are incorrect, they rely on database execution feedback or static methods (fixed limited context rule prompt words or examples, or fine-tuning large models based on a fixed field value database) to correct, which cannot effectively solve the above problems because they have the following defects respectively:

[0003] Defects of direct generation by large models: Large models lack the dynamic retrieval ability for database field values and are prone to generating incorrect conditional clauses due to inaccurate input keywords.

[0004] Reliance on execution feedback: It can only capture syntax or execution errors and cannot handle the problem that the query ends normally due to conditional errors but the actual SQL statement is incorrect.

[0005] Limitations of static methods: It is difficult to cover diverse user inputs (such as abbreviations, aliases, and colloquial expressions of field values) and variable database field values (such as real-time updates of database records, resulting in dynamic changes of field values) in real scenarios. Summary of the Invention

[0006] The purpose of the present invention is to solve the following technical problems:

[0007] Aiming at the problem that the traditional Text-to-SQL generates inaccurate WHERE clauses due to inaccurate user input field values (such as abbreviations, typos) and dynamic changes in the database, a dual recall mechanism combining large model entity extraction with ElasticSearch fuzzy matching and vector semantic retrieval is proposed to dynamically enhance context information to improve the accuracy of conditional clause generation, and a real-time synchronization mechanism is used to ensure the consistency between the recalled values and the database.

[0008] To achieve the above purpose, the present invention adopts the following technical solutions:

[0009] The present invention provides an improved method for the conditional clause of the large model Text-to-SQL based on the enhancement of fuzzy recall field values, including the following steps:

[0010] a) Perform large model entity extraction processing on the user input to identify keywords and their types, where the types include date, field name, and field value;

[0011] b) For the keyword of the field name type obtained in step a), recall the corresponding field value through two methods: ES and vector, where ES represents ElasticSearch;

[0012] c) Use the fuzzy recall field value obtained in step (b) as the context to input into the large language model to generate an SQL conditional clause containing the accurate field value;

[0013] d) In response to the dynamic change of the source database field value, synchronize and update the ES index and vector database records in real time or at regular intervals to ensure the consistency between the recalled field value in step (b) and the actual field value in the database.

[0014] In the above solution, step b) includes the following sub-steps:

[0015] b1) Preparation stage: Based on the full amount of database field value records, construct an initial ES index and vector database for ES fuzzy recall and vector recall queries;

[0016] b2) ES recall: Use the keyword obtained in step a) to fuzzy recall the field value from ES, merge the recall results, take the top N in descending order according to the ES fuzzy matching score score, and then perform threshold screening by calculating the vector similarity between the keyword and the recall result to filter out semantically irrelevant field values, obtaining the ES recall field value result;

[0017] b3) Vector recall: Use the keyword obtained in step a) to perform semantic similarity retrieval from the vector database, retrieve the top N database field values that are most semantically relevant to the input keyword, and perform screening according to the similarity threshold to obtain the result of the field value that is most semantically relevant;

[0018] b4) Finally, merge and deduplicate the field value results of steps b2) and b3) as the final recalled field value result.

[0019] In the above solution, the detailed sub-steps of step c) are as follows:

[0020] (c1) Context structured integration: Organize the result obtained in step b) into a json format according to the KV structure of (field name: field value) to obtain the field name-field value context;

[0021] (c2) Prompt Template Injection: Design a prompt template that includes natural language questions, database schemas, recall field value contexts, and SQL generation examples. Dynamically inject the (field name: field value) json-structured context described in step c1) into specific positions to prompt the large model to write the correct WHERE clause;

[0022] (c3) Inference Process Guidance: In the prompt, use the chain-of-thought prompting technique, requiring the large model to first map the user input keywords to field values, and then gradually generate the SQL statement to increase the accuracy of the WHERE statement;

[0023] (c4) Semantic Consistency Verification: Reverse-match the generated SQL conditional clause with the recall field value, eliminate abnormal outputs that contain unrecalled field values, and ensure the effectiveness of semantic constraints. At the same time, parse the generated SQL to obtain the left value (field name) and right value (field value) of the WHERE statement. If there is no (field name: field value) correspondence in the recall results, according to the recall rate of the field value, it can be directly determined as an abnormal output to increase the ability to capture abnormal results.

[0024] The present invention also provides an improved device for large model Text-to-SQL conditional clauses based on enhanced fuzzy recall field values, including:

[0025] Entity Extraction Module, used to perform large model entity extraction processing on the user input, identify keywords and their types, and the types include three types: date, field name, and field value;

[0026] Field Value Recall Module, used to recall the corresponding field values for the field name type keywords obtained by the entity extraction module through two methods: ES and vector, where ES represents ElasticSearch;

[0027] SQL Generation Module, used to input the fuzzy recall field values obtained by the field value recall module as context into the large language model to generate SQL conditional clauses containing accurate field values;

[0028] Data Synchronization Module, used to respond to the dynamic changes of the source database field values, and synchronize and update the ES index and vector database records in real time or at regular intervals to ensure the consistency between the recall field values in the field value recall module and the actual field values in the database.

[0029] For the above device, the field value recall module includes:

[0030] Initial Index Construction Sub-module, used to build the initial ES index and vector database based on the full amount of database field value records for ES fuzzy recall and vector recall queries;

[0031] ES recall sub-module, which is used to fuzzily recall field values from ES using the keywords obtained by the entity extraction module, merge the recall results, take the top N in descending order according to the ES fuzzy matching score score, and then perform threshold screening by calculating the vector similarity between the keywords and the recall results to filter out semantically irrelevant field values and obtain the ES recall field value results;

[0032] Vector recall sub-module, which is used to perform semantic similarity retrieval from the vector database using the keywords obtained by the entity extraction module, retrieve the top N database field values that are most semantically relevant to the input keywords, and perform screening according to the similarity threshold to obtain the results of the most semantically relevant field values;

[0033] Result merging sub-module, which is used to merge and deduplicate the field value results of the ES recall sub-module and the vector recall sub-module as the final recall field value results.

[0034] For the above-mentioned device, the SQL generation module includes:

[0035] Context structured integration sub-module, which is used to organize the results obtained by the field value recall module into a json format according to the KV structure of (field name: field value) to obtain the field name-field value context;

[0036] Prompt template injection sub-module, which is used to design a prompt template containing natural language questions, database schema, recall field value context and SQL generation examples, and dynamically inject the (field name: field value) json structured context described by the context structured integration sub-module into a specific position to prompt the large model to write the correct WHERE clause;

[0037] Inference process guidance sub-module, which is used to adopt the thought chain prompting technology in the prompt words, requiring the large model to first correspond the user input keywords with the field values, and then gradually generate the SQL statement to increase the accuracy of the WHERE statement;

[0038] Semantic consistency verification sub-module, which is used to perform reverse matching between the generated SQL conditional clause and the recalled field values, eliminate the abnormal outputs containing unrecalled field values, and ensure the validity of semantic constraints. At the same time, parse the generated SQL to obtain the left value (field name) and right value (field value) of the WHERE statement. If there is no corresponding relationship of (field name: field value) in the recall results, according to the high and low recall rate of the field values, it can be directly determined as an abnormal output, increasing the ability to capture abnormal results.

[0039] Since the present invention adopts the above technical means, it has the following beneficial effects:

[0040] Improve the accuracy of the conditional clause: In the case of inaccurate field values input by the user, significantly improve the accuracy of the Text-to-SQL conditional clause.

[0041] Multi-stage combined recall mechanism: Based on the relatively accurate entity extraction keyword tokenization of the large model, combined with multiple methods such as ES string fuzzy matching and vectorized recall, taking into account both accuracy and recall rate.

[0042] Lightweight integration: The main body of the method is to provide an external field value fuzzy recall tool for the large model. After recalling the candidate set information, it is directly incorporated into the context through prompt words. Combined with a small number of example prompts, the effect of generating SQL conditional clauses by the large model can be enhanced without additional model fine-tuning training; or only need to fine-tune the text semantic embedding model which is much smaller in scale than the large model, significantly reducing the computational resource overhead.

[0043] Dynamic scalability and high coverage: It can achieve rapid expansion of database field values and high coverage of database field values by synchronizing the changes of database field values to ES and vector databases in real time. Brief Description of the Drawings

[0044] Figure 1 It is a schematic diagram of the overall system flow of the present invention;

[0045] Figure 2 It is a schematic diagram of the dynamic construction of ES index, vector database and the process of fuzzy recall of field values;

[0046] Figure 3 It is an example of input entity extraction prompt words;

[0047] Figure 4 It is an example of the design of the prompt word template. Detailed Description of the Invention

[0048] The following will give a detailed description of the embodiments of the present invention. Although the present invention will be described and explained in conjunction with some specific embodiments, it should be noted that the present invention is not limited to these embodiments only. On the contrary, any modifications or equivalent replacements made to the present invention should be covered within the scope of the claims of the present invention.

[0049] In addition, in order to better illustrate the present invention, numerous specific details are given in the following detailed description. Those skilled in the art will understand that the present invention can be implemented without these specific details.

[0050] The present invention provides an improved method for generating SQL conditional clauses from text by a large model based on enhancing fuzzy recall of field values, which is characterized by including the following steps:

[0051] a) Identify keywords and their types from the user input through the entity extraction module, and the types include date, field name and field value;

[0052] Specific example, entity extraction:

[0053] Adopt a large model-based named entity recognition (NER) module to extract entities and their corresponding types (such as date, field names (metrics or dimensions), field values, etc.) from the user input in combination with the database schema context.

[0054] Example: Input entity extraction prompt words, such as Figure 1 ;

[0055] Entity extraction result: (Note: Among them, 2025-02-20 is only an example, and the actual value is the real-time accurate value inferred by the large model based on the provided dynamic date).

[0056]

[0057] b) Based on the keywords extracted in step (a), perform fuzzy matching recall on the database field values through ElasticSearch (abbreviated as ES), and perform threshold screening by calculating the vector similarity between the keywords and the recall results, so as to filter out semantically irrelevant field values;

[0058] Specific example, field value fuzzy recall

[0059] b1) Preparation stage: Based on the full amount of database field value records, build an initial ES index and vector database;

[0060] To facilitate those skilled in the art to better understand the technical concept of the present invention, the following example is further provided for illustration:

[0061] Preparation stage 1: Build field value ES index records

[0062] Build the ES index and records of the database field values in the following manner:

[0063] 1. Create an ES index that contains both fields and field values, and the field values support fuzzy matching (match inverted index matching, wildcard prefix and suffix query, fuzzy edit distance matching, etc.):

[0064]

[0065] 2. Store all non-date and non-numeric (such as metrics) fields and values of the database table except the id and date columns in the ES index in pairs, for example:

[0066] select 'brand' as 'columnName', brand as 'columnValue'

[0067] from sales

[0068] UNION

[0069] select 'city' as 'columnName', city as 'columnValue'

[0070] from sales

[0071] UNION

[0072] select'store' as 'columnName','store' as 'columnValue'

[0073] from sales;

[0074] 3. Continuously monitor database changes and synchronously modify the ES index records when the database field values change.

[0075] Preparation Phase 2: Field Value Vector Database

[0076] Using the same query method as above, after querying all database field names and field values, use a semantic embedding model (such as BERT) to vectorize all field values in a certain dimension (such as 512 dimensions), and store them in a vector database (such as FAISS) with these vectors as indexes. Subsequently, also continuously monitor database changes and synchronously modify the vector database records when the database field values change.

[0077] For example, the records stored in the vector database are shown in the following table:

[0078] Vector (index) Field name Field value [0.3324,0.7384,-0.3459,0.9123,…] brand CHAGEE **Cha Ji [-0.4252,0.7584,0.2510,0.4956,…] brand **Cha [0.2952,0.8541,0.2561,0.5920,…] store **Cha Ji Flagship Store

[0079] b2) String Fuzzy Recall Based on ES

[0080] Input each keyword in the field value keyword list obtained by entity extraction into ES respectively, and generate preliminary recall results through fuzzy matching algorithms, including inverted index matching, wildcard query, edit distance matching, etc. And through a series of filtering and screening operations, obtain the final ES fuzzy recall results. The specific operation method is as follows:

[0081] First, use the field value keyword list extracted by entities for ES fuzzy recall input and go through the following steps:

[0082] 1. Recall: Recall a large number of field values with each keyword to ensure the recall rate;

[0083] 2. Merge: Merge the recall results of all keywords by field grouping;

[0084] 3. Primary screening: In each field group, sort in descending order according to the ES fuzzy score, and only retain the top N field values with the highest scores.

[0085] 4. Fine screening: Calculate the vector similarity between the keyword and each of its recall results, and screen according to the threshold

[0086] Specifically, the operations in the fine screening stage are as follows: Use a pre-trained text embedding model (such as BERT) to calculate the semantic vectors of all keywords and field values respectively, and then calculate the semantic similarity (such as cosine similarity) between each keyword and each of its recalled field values. Set a similarity threshold (for example: 0.8, which can be dynamically adjusted according to the recall rate and accuracy of the test results. If the recall rate is low, the threshold can be appropriately lowered; if the accuracy is low, the threshold can be appropriately increased), and screen out the field values below the similarity threshold.

[0087] Example: After performing ES recall on the entity extraction field value list ["**Chaggee", "Beijing"] according to the above steps, the following candidate values are obtained:

[0088]

[0089] Example: Perform semantic similarity screening on the above ES recall field value results:

[0090] For ["**Chaggee"]: [["CHAGEE**Chaggee", "**Chaggee Flagship Store"]],

[0091] Calculate:

[0092] vector **茶姬 = BERT("**Chaggee")

[0093] vector CHAGEE**茶姬 = BERT("CHAGEE**Chaggee")

[0094] vector **茶姬旗舰店 = BERT("**Chaggee Flagship Store")

[0095] cos_similarity(vector **茶姬 , vector CHAGEE**茶姬 ) = 0.89 > 0.8, retain "CHAGEE**Chaggee"

[0096] cos_similarity(vector **茶姬 , vector **茶姬旗舰店 ) = 0.78 < 0.8, screen out "**Chaggee Flagship Store"

[0097] The same applies to other {<keyword>: [<corresponding ES recall results>]}

[0098] b3) Semantic Relevance Recall Based on Vector Similarity

[0099] For the keyword list of field values extracted by entities, convert them into semantic vectors respectively through the same semantic embedding model (such as BERT), and use each keyword separately for vectorized recall. Through the similarity nearest neighbor search algorithm, retrieve the field value results with the TopN similarity (such as cosine similarity) to each keyword vector respectively, merge and deduplicate the recall results of all keywords, and then merge and deduplicate them with the ES results obtained in b2).

[0100] Example Results:

[0101]

[0102]

[0103] c) Large Model Text-to-SQL with Enhanced Field Value Context

[0104] With the help of prompt engineering, input the above fuzzy recall results as context information into the large model for SQL generation. Prompt techniques such as Chain of Thought (CoT) and Complex Task Decomposition and Execution (such as Plan And Execute) can be used to assist SQL generation.

[0105] Example of Prompt Template Design is as Figure 4 shown:

[0106] Example of SQL Generation Results:

[0107] Query SQL Generated by LLM Based on Enhanced Accurate Field Value Context:

[0108] SELECT sales FROM table WHERE brand='CHAGEE**Cha Ji' AND date='2025-02-16'.

[0109] Compare with the Query SQL Generated without Enhancement:

[0110] SELECT sales FROM table WHERE brand='**Cha Ji' AND date='2025-02-16'.

[0111] d) Monitor Database Changes and Synchronize Updates

[0112] When the database records are updated, it is necessary to synchronize and update the records in the ES or vector database. The specific implementation plan is as follows:

[0113] 1. Listen for INSERT, UPDATE, and DELETE events on the target table, write the changed data information to a temporary table, or directly send the database binlog log to a message queue (such as Kafka, RabbitMQ, etc.) for consumption.

[0114] 2. Create consumer tasks (db2es, db2vdb). According to the real-time requirements, you can choose to synchronously block and listen or periodically listen to the content of the temporary table or message queue. When there are record changes, synchronize them to the ES and vector database. (The ES and vector database records synchronously store the same auto-incrementing id value as the target table, which is convenient for duplicate checking, deletion, and addition operations).

Claims

1. An improved method for the conditional clause of the large model Text-to-SQL based on enhancing the fuzzy recall field value, characterized in that, It includes the following steps: a) Perform large model entity extraction processing on the user input to identify keywords and their types, where the types include date, field name, and field value; b) For the keyword of the field name type obtained in step a), recall the corresponding field value through two methods: ES (ElasticSearch) and vector; c) Use the fuzzy recalled field value obtained in step (b) as the context to input into the large language model to generate an SQL conditional clause containing the accurate field value; d) In response to the dynamic change of the source database field value, synchronize and update the ES index and vector database records in real-time or at regular intervals to ensure the consistency between the recalled field value in step (b) and the actual field value in the database.

2. An improved method for the conditional clause of the large model Text-to-SQL based on enhancing the fuzzy recall field value according to claim 1, characterized in that, Among them, step b) includes the following sub-steps: b1) Preparation stage: Based on the full-volume field value records of the database, construct the initial ES index and vector database for ES fuzzy recall and vector recall queries; b2) ES recall: Use the keyword obtained in step a) to fuzzily recall the field value from ES, merge the recall results, take the top N in descending order according to the ES fuzzy matching score score, and then perform threshold screening by calculating the vector similarity between the keyword and the recall result to filter out semantically irrelevant field values, obtaining the ES recall field value result; b3) Vector recall: Use the keyword obtained in step a) to perform semantic similarity retrieval from the vector database, retrieve the top N database field values that are most semantically relevant to the input keyword, and perform screening according to the similarity threshold to obtain the result of the field value that is most semantically relevant; b4) Finally, merge and deduplicate the field value results of steps b2) and b3) as the final recalled field value result.

3. An improved method for the conditional clause of the large model Text-to-SQL based on enhancing the fuzzy recall field value according to claim 1, characterized in that, Among them, the detailed sub-steps of step c) are as follows: (c1) Context structured integration: Organize the result obtained in step b) into a json format according to the KV structure of (field name: field value) to obtain the field name-field value context; (c2) Prompt template injection: Design a prompt template that includes natural language questions, database schema, recalled field value context, and SQL generation examples, and dynamically inject the (field name: field value) json structured context described in step c1) into a specific position to prompt the large model to write the correct WHERE clause; (c3) Inference process guidance: Adopt the thought chain prompting technology in the prompt word, require the large model to first correspond the user input keyword with the field value, and then gradually generate the SQL statement to increase the accuracy of the WHERE statement; (c4) Semantic consistency verification: Reverse-match the generated SQL conditional clause with the recalled field value, eliminate abnormal outputs containing unrecalled field values to ensure the effectiveness of semantic constraints. At the same time, parse the generated SQL to obtain the left value (field name) and right value (field value) of the WHERE statement. If there is no (field name: field value) corresponding relationship in the recall result, according to the high and low recall rate of the field value, it can be directly determined as an abnormal output to increase the ability to capture abnormal results.

4. An improved device for the conditional clause of the large model Text-to-SQL based on enhancing the fuzzy recall field value, characterized in that, It includes: The entity extraction module is used to perform large model entity extraction processing on user input, identify keywords and their types therein, and the types include date, field name, and field value; The field value recall module is used to recall the corresponding field values for the field name type keywords obtained by the entity extraction module through two methods: ES and vector, where ES represents ElasticSearch; The SQL generation module is used to use the fuzzy recall field values obtained by the field value recall module as context to input into the large language model to generate a SQL conditional clause containing accurate field values; The data synchronization module is used to respond to the dynamic changes of the source database field values, and synchronize and update the ES index and vector database records in real time or at regular intervals to ensure the consistency between the recalled field values in the field value recall module and the actual field values in the database.

5. The device for improving the conditional clause of the large model Text-to-SQL based on enhancing the fuzzy recall field value according to claim 4, wherein Among them, the field value recall module includes: The initial index construction sub-module is used to construct the initial ES index and vector database based on the full amount of database field value records for ES fuzzy recall and vector recall queries; The ES recall sub-module is used to fuzzily recall field values from ES with the keywords obtained by the entity extraction module, merge the recall results, take the top N in descending order according to the ES fuzzy matching score score, and then perform threshold screening by calculating the vector similarity between the keywords and the recall results to filter out semantically irrelevant field values to obtain the ES recall field value results; The vector recall sub-module is used to perform semantic similarity retrieval from the vector database with the keywords obtained by the entity extraction module, retrieve the top N database field values that are most semantically relevant to the input keywords, and perform screening according to the similarity threshold to obtain the results of the most semantically relevant field values; The result merging sub-module is used to merge and deduplicate the field value results of the ES recall sub-module and the vector recall sub-module as the final recalled field value results.

6. The large model Text-to-SQL conditional clause improvement device based on fuzzy recall field value enhancement according to claim 4, characterized in that, Among them, the SQL generation module includes: The context structured integration sub-module is used to organize the results obtained by the field value recall module into a json format according to the KV structure of (field name: field value) to obtain the field name-field value context; The prompt template injection sub-module is used to design a prompt template containing natural language questions, database schema, recalled field value context, and SQL generation examples, and dynamically inject the (field name: field value) json structured context described by the context structured integration sub-module into a specific position to prompt the large model to write the correct WHERE clause; The inference process guiding sub-module is used to adopt the thought chain prompting technology in the prompt words, requiring the large model to first correspond the user input keywords with the field values, and then gradually generate the SQL statement to increase the accuracy of the WHERE statement; The semantic consistency verification sub-module is used to perform reverse matching on the generated SQL conditional clause and the recalled field values, and eliminate abnormal outputs containing unrecalled field values to ensure the effectiveness of semantic constraints; At the same time, the left value and right value of the WHERE statement are parsed from the generated SQL. If there is no corresponding relationship of (field name: field value) in the recall results, according to the high and low recall rates of the field values, it can be directly determined as an abnormal output, increasing the ability to capture abnormal results.

Images