Text generation SQL (Structured Query Language) method and system based on difficulty perception instruction and retrieval enhancement

Abstract

The invention discloses a difficulty perception instruction and retrieval enhancement-based text generation SQL method and system, and the method comprises the steps: S1, inputting a natural language question, carrying out the difficulty classification of the question through a text classification model based on a Transform architecture, and obtaining an SQL class corresponding to the input question text; s2, according to the input natural language question, a sample related to the input target question is retrieved from a knowledge base through a double-tower sorting model and used for organizing few-sample learning cue words; s3, dynamically constructing cue words according to the input natural language question, the predicted SQL category and the retrieved sample related to the target question; s4, inputting the cue word into the large language model to generate an executable SQL query statement; according to the method, a special sorting model and a dynamic cue word construction strategy are combined, so that the dynamic few-sample learning cue word which is more related to the question and is more helpful for guiding LLM to correctly solve the question is efficiently constructed.

Description

Technical Field

[0001] The present invention relates to the technical field of SQL generation, and more particularly to a method and system for generating SQL from text based on difficulty-aware instructions and retrieval enhancement. Background Art

[0002] With the rapid development of information technology, databases have become an important tool for storing and managing massive amounts of data. Structured Query Language (SQL) allows users to flexibly access and operate databases, but its complex syntax poses a barrier to use for non-professionals, limiting people's ability to effectively utilize database resources.

[0003] To solve this problem, Text-to-SQL technology came into being. This technology aims to automatically convert natural language questions (NLquestion) into executable SQL query statements, which provides a more friendly interactive interface for relational databases, allowing users to query data without having to master SQL expertise.

[0004] Currently, existing advanced technologies primarily use in-context learning methods based on large language models (LLMs) to achieve natural language to SQL translation. Current LLM-based text-to-SQL methods primarily implement the generation task through two modules: prompt word instruction design and few-shot learning. These methods generally suffer from the following two shortcomings:

[0005] (1) Lack of adaptability of prompt word instructions: Existing methods use fixed and simple prompt word instruction templates, which cannot be dynamically adjusted according to the difficulty and characteristics of the problem; for complex problems, simple instructions are difficult to parse the complex structure of SQL; for simple problems, complex instructions may cause LLM to generate incorrect queries, limiting model performance.

[0006] (2) Insufficient retrieval effect of few-sample examples: The use of manually selected fixed examples lacks adaptability to the target problem and is difficult to expand; and the retrieval method based on natural language similarity only considers the similarity in a single text dimension. This general retrieval strategy fails to optimize the characteristics of the Text-to-SQL task (such as the association between semantics and SQL structure), resulting in insufficient improvement of the final generation effect by examples.

[0007] Therefore, how to achieve difficulty-based adaptive instruction generation in natural language to SQL tasks and improve the question relevance of few-sample retrieval examples is an urgent problem that technicians in this field need to solve. Summary of the Invention

[0008] In view of this, the present invention provides a method and system for generating SQL from text based on difficulty-aware instructions and retrieval enhancement to solve some of the technical problems mentioned in the background technology.

[0009] In order to achieve the above object, the present invention adopts the following technical solutions:

[0010] A method for generating SQL from text based on difficulty-aware instructions and retrieval enhancement includes the following steps:

[0011] S1. Input a natural language question and classify the difficulty of the question using a text classification model based on the Transformer architecture to obtain the SQL category corresponding to the input question text.

[0012] S2. Based on the input natural language question, the dual-tower ranking model is used to retrieve examples related to the input target question from the SQL knowledge base to organize prompt words for few-shot learning;

[0013] S3. Dynamically construct prompt words based on the input natural language question, the SQL category predicted by the text classification model, and examples related to the target question;

[0014] S4. Input the prompt word into the LLM large language model to generate an executable SQL query statement to complete the query of the target question.

[0015] Preferably, the specific content of step S1 is:

[0016] S11. Input the text information of the natural language question into the Transformer-based text classification model, and use the BERT encoder to encode the text information into a latent vector representation;

[0017] S12. The latent vector representation is input to the classification head layer to obtain the scores of each category;

[0018] S13. Convert the scores of each category into probabilities of each category through the SOFTMAX calculation layer. The SQL category with the highest probability is the model's prediction result, and the SQL category corresponding to the input question text is obtained.

[0019] Preferably, before step S11, the method further includes: performing supervised training on a text classification model based on a Transformer architecture on a public SQL dataset annotated with category labels, wherein the text classification model includes a BERT encoder with text semantic understanding capabilities and a classification head layer for SQL domain knowledge classification.

[0020] Preferably, the specific content of step S2 is:

[0021] S21. Organize the question-SQL pair examples and database structure information of the public dataset into a knowledge base;

[0022] S22. Use the SQL category labels predicted by the text classification model to filter examples in the knowledge base;

[0023] S23. The target question text to be queried and the sample question-SQL text in the knowledge base are input to both ends of the dual-tower ranking model. The inputs at both ends are encoded into latent vector representations through the BERT encoder, and are respectively transformed into query representation and sample representation by two fully connected layers responsible for capturing different information. Finally, the cosine similarity score between the two sets of query and sample vectors is calculated as a measure of the relevance between the sample and the query, and several samples related to the target question are output.

[0024] Preferably, before step S21, it also includes using a public dataset to train a dual-tower ranking model for SQL knowledge base sample retrieval, the dual-tower ranking model including a BERT pre-trained encoder model and two fully connected layers for query representation transformation and sample representation transformation respectively.

[0025] Preferably, the specific content of step S3 is:

[0026] S31. Design different prompt word templates for different SQL difficulty tags. The prompt word template consists of fixed guide information and dynamic embedded information.

[0027] S32. Get the corresponding prompt word template according to the predicted SQL category;

[0028] S33. In the embedding information position reserved in the prompt word template, based on the target problem to be solved and the samples related to the input target problem selected by the ranking model, that is, the few-shot learning information, the database structure, natural language question, and SQL query information are dynamically embedded to obtain the corresponding final prompt word.

[0029] A system for generating SQL based on text with difficulty-aware instructions and retrieval enhancement, based on the aforementioned method for generating SQL based on text with difficulty-aware instructions and retrieval enhancement, comprising: a difficulty classification module, a sample retrieval module, a dynamic prompt word construction module, and a large prediction model; the difficulty classification module comprises a text classifier based on a Transformer architecture for question difficulty classification; the sample retrieval module comprises a dual-tower ranking model for sample retrieval and an SQL knowledge base;

[0030] A text classifier based on the Transformer architecture, including a BERT encoder capable of understanding text semantics and a classification head layer for SQL domain knowledge classification. It is used to classify the difficulty of input natural language questions and obtain the SQL category corresponding to the input question text;

[0031] The dual-tower ranking model includes a BERT pre-trained encoder model and two fully connected layers for query representation transformation and sample representation transformation respectively. It is used to retrieve samples related to the input target question from the SQL knowledge base based on the input natural language question, and is used to organize prompt words for few-shot learning.

[0032] Dynamic prompt word construction module, used to dynamically construct prompt words based on the input natural language question, the SQL category predicted by the text classification model, and the samples related to the retrieved target question;

[0033] The large language model is used to generate executable SQL query statements based on the prompt words to complete the query for the target question.

[0034] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements a method for generating SQL from text based on difficulty-aware instructions and retrieval enhancement.

[0035] A processing terminal includes a memory and a processor. The memory stores a computer program that can be run on the processor. When the processor executes the computer program, the method for generating SQL based on difficulty-aware instructions and retrieval-enhanced text is implemented.

[0036] Through the above technical solution, it can be seen that compared with the existing technology, the present invention discloses a method and system for generating SQL from text based on difficulty-aware instructions and retrieval enhancement. By combining a dedicated ranking model with a dynamic prompt word construction strategy, dynamic few-sample learning prompt words that are more relevant to the problem and more helpful in guiding the LLM to correctly answer the problem are efficiently constructed; through difficulty-adaptive instructions, the SQL generation accuracy for complex problems is improved, and excessive prompts are avoided for simple problems; the ranking model is jointly vectorized by NL-SQL, and the relevance of retrieval examples far exceeds traditional similarity retrieval. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are merely embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying any creative work.

[0038] Figure 1 A schematic diagram of a method for generating SQL from text based on difficulty-aware instructions and retrieval enhancement provided by the present invention;

[0039] Figure 2 Schematic diagram of the text classification model provided by the present invention;

[0040] Figure 3 This is a schematic diagram of the double-tower sorting model provided by the present invention. DETAILED DESCRIPTION

[0041] The following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of the present invention.

[0042] The embodiment of the present invention discloses a method for generating SQL based on difficulty-aware instructions and retrieval-enhanced text, such as Figure 1 , including the following steps:

[0043] S1. Input a natural language question and classify the difficulty of the question using a text classification model based on the Transformer architecture to obtain the SQL category corresponding to the input question text.

[0044] S2. Based on the input natural language question, the dual-tower ranking model is used to retrieve examples related to the input target question from the SQL knowledge base to organize prompt words for few-shot learning;

[0045] S3. Dynamically construct prompt words based on the input natural language question, the SQL category predicted by the text classification model, and examples related to the target question;

[0046] S4. Input the prompt word into the LLM large language model to generate an executable SQL query statement to complete the query of the target question.

[0047] In order to further implement the above technical solutions, Figure 2 , the specific content of step S1 is:

[0048] S11. Input the text information of the natural language question into the Transformer-based text classification model, and use the BERT encoder to encode the text information into a latent vector representation;

[0049] S12. The latent vector representation is input to the classification head layer to obtain the scores of each category;

[0050] S13. The scores of each category are converted into probabilities of each category through the SOFTMAX calculation layer. The SQL category with the highest probability is the model's prediction result. The SQL category corresponding to the input question text is obtained. The predicted label can be used for subsequent knowledge base filtering and prompt word assembly.

[0051] In order to further implement the above technical solution, before step S11, it also includes: supervised training of a text classification model based on the Transformer architecture on a public SQL dataset annotated with category labels, where the text classification model includes a BERT encoder with text semantic understanding capabilities and a classification head layer for SQL domain knowledge classification.

[0052] In order to further implement the above technical solution, the specific content of step S2 is:

[0053] S21. Organize the public dataset’s question-SQL pair examples and database structure information (such as table names, table relationships, column names and column attributes of each table) into a knowledge base;

[0054] S22. Use the SQL category labels predicted by the text classification model to filter examples in the knowledge base;

[0055] S23. The target question text to be queried and the sample question-SQL text in the knowledge base are input to both ends of the dual-tower ranking model. The inputs at both ends are encoded into latent vector representations through the BERT encoder, and are respectively transformed into query representation and sample representation by two fully connected layers responsible for capturing different information. Finally, the cosine similarity score between the two sets of query and sample vectors is calculated as a measure of the relevance between the sample and the query, and several samples related to the target question are output.

[0056] In order to further implement the above technical solution, before step S21, it also includes using a public dataset to train a dual-tower ranking model for SQL knowledge base sample retrieval, and the dual-tower ranking model includes a BERT pre-trained encoder model and two fully connected layers for query representation transformation and sample representation transformation respectively.

[0057] In order to further implement the above technical solutions, Figure 3 , the specific content of step S3 is:

[0058] S31. Design different prompt word templates for different SQL difficulty tags. The prompt word templates consist of fixed guiding information and dynamic embedded information. The embedded information includes database structure information, natural language questions, and SQL queries. When generating the final prompt word, the problem to be solved and the corresponding knowledge base recall samples are dynamically embedded.

[0059] S32. Get the corresponding prompt word template according to the predicted SQL category;

[0060] S33. In the embedding information position reserved in the prompt word template, based on the target problem to be solved and the samples related to the input target problem selected by the ranking model, that is, the few-shot learning information, the database structure, natural language question, and SQL query information are dynamically embedded to obtain the corresponding final prompt word.

[0061] In this embodiment, the SQL difficulty label is predicted by the difficulty classification module, i.e., the text classification model, so that the complexity of the dynamically generated prompt words can be adapted to different problems according to the different predicted SQL categories. At the same time, the samples used for few-shot learning are dynamically retrieved from the knowledge base according to the target problem by the sample retrieval module, i.e., the dual-tower ranking model. Therefore, these samples are highly relevant to the target problem and are more helpful for LLM to solve the target problem.

[0062] A system for generating SQL from text based on difficulty-aware instructions and retrieval enhancement, based on a method for generating SQL from text based on difficulty-aware instructions and retrieval enhancement, comprising: a difficulty classification module, a sample retrieval module, a dynamic prompt word construction module, and a large prediction model. The difficulty classification module includes a text classifier based on the Transformer architecture for question difficulty classification, and the sample retrieval module includes a dual-tower ranking model for sample retrieval and an SQL knowledge base.

[0063] A text classifier based on the Transformer architecture, including a BERT encoder capable of understanding text semantics and a classification head layer for SQL domain knowledge classification. It is used to classify the difficulty of input natural language questions and obtain the SQL category corresponding to the input question text;

[0064] The dual-tower ranking model includes a BERT pre-trained encoder model and two fully connected layers for query representation transformation and sample representation transformation respectively. It is used to retrieve samples related to the input target question from the SQL knowledge base based on the input natural language question, and is used to organize prompt words for few-shot learning.

[0065] Dynamic prompt word construction module, used to dynamically construct prompt words based on the input natural language question, the SQL category predicted by the text classification model, and the samples related to the retrieved target question;

[0066] The large language model is used to generate executable SQL query statements based on the prompt words to complete the query for the target question.

[0067] In another embodiment, take the target problem statement to be solved as: "Get the names of customers who have made more than five purchases." as an example:

[0068] Based on the text input, the text classifier for difficulty classification predicts the SQL difficulty category as "requires subquery." The example retrieval module then uses this predicted category to filter the knowledge base and uses the dual-tower ranking model to retrieve the most relevant question-SQL pairs. The prompt word construction module then dynamically constructs corresponding prompt words based on the target question, the predicted SQL category, and the retrieved examples. Specifically, the following are the prompt words:

[0069] “

[0070] <Several examples with the same structure as below> x N

[0071] Problem: Get the names of customers who have made more than five purchases.

[0072] <Database structure corresponding to the problem>

[0073] Let's think about this step by step. This problem can be solved by solving the subproblem: "Number of purchases per customer." The SQL query for this subproblem is "SELECT customer_id, COUNT() as purchase_count FROM orders GROUP BY customer_id HAVING COUNT() > 5;." Therefore, the query for the original problem is as follows:

[0074] ”

[0075] The dynamically generated prompt words are input into different large language models to generate predicted SQL to complete the query for the target question.

[0076] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements a method for generating SQL from text based on difficulty-aware instructions and retrieval enhancement.

[0077] A processing terminal includes a memory and a processor. The memory stores a computer program that can be run on the processor. When the processor executes the computer program, a method for generating SQL based on difficulty-aware instructions and retrieval-enhanced text is implemented.

[0078] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on the differences from other embodiments. Reference can be made to the common and similar parts between the various embodiments. For the devices disclosed in the embodiments, since they correspond to the methods disclosed in the embodiments, the description is relatively simple, and the relevant parts can be referred to the method description.

[0079] The above description of the disclosed embodiments is intended to enable one skilled in the art to implement or use the present invention. Various modifications to these embodiments will be readily apparent to one skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention is not limited to the embodiments shown herein but is intended to conform to the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for generating SQL from text based on difficulty-aware instructions and retrieval enhancement, characterized in that: The following steps are involved: S1. Input a natural language question and classify the difficulty of the question using a text classification model based on the Transformer architecture to obtain the SQL category corresponding to the input question text. S2. Based on the input natural language question, the dual-tower ranking model is used to retrieve examples related to the input target question from the SQL knowledge base to organize prompt words for few-shot learning; S3. Dynamically construct prompt words based on the input natural language question, the SQL category predicted by the text classification model, and examples related to the target question; S4. Input the prompt word into the large language model to generate an executable SQL query statement to complete the query of the target question.

2. The method for generating SQL based on text based on difficulty perception instructions and retrieval enhancement according to claim 1, characterized in that: The specific content of step S1 is: S11. Input the text information of the natural language question into the Transformer-based text classification model, and use the BERT encoder to encode the text information into a latent vector representation; S12. The latent vector representation is input to the classification head layer to obtain the scores of each category; S13. Convert the scores of each category into probabilities of each category through the SOFTMAX calculation layer. The SQL category with the highest probability is the model's prediction result, and the SQL category corresponding to the input question text is obtained.

3. The method for generating SQL based on text based on difficulty perception instructions and retrieval enhancement according to claim 2, characterized in that: Before step S11, the method also includes: performing supervised training on a text classification model based on a Transformer architecture on a public SQL dataset annotated with category labels, where the text classification model includes a BERT encoder with text semantic understanding capabilities and a classification head layer for SQL domain knowledge classification.

4. The method for generating SQL based on text based on difficulty perception instructions and retrieval enhancement according to claim 1, characterized in that: The specific content of step S2 is: S21. Organize the question-SQL pair examples and database structure information of the public dataset into a knowledge base; S22. Use the SQL category labels predicted by the text classification model to filter examples in the knowledge base; S23. The target question text to be queried and the sample question-SQL text in the knowledge base are input to both ends of the dual-tower ranking model. The inputs at both ends are encoded into latent vector representations through the BERT encoder, and are respectively transformed into query representation and sample representation by two fully connected layers responsible for capturing different information. Finally, the cosine similarity score between the two sets of query and sample vectors is calculated as a measure of the relevance between the sample and the query, and several samples related to the target question are output.

5. The method for generating SQL based on difficulty-aware instructions and retrieval-enhanced text according to claim 4, characterized in that: Before step S21, it also includes using a public dataset to train a dual-tower ranking model for SQL knowledge base sample retrieval, where the dual-tower ranking model includes a BERT pre-trained encoder model and two fully connected layers for query representation transformation and sample representation transformation, respectively.

6. The method for generating SQL based on difficulty-aware instructions and retrieval-enhanced text according to claim 1, characterized in that: The specific content of step S3 is: S31. Design different prompt word templates for different SQL difficulty tags. The prompt word template consists of fixed guide information and dynamic embedded information. S32. Get the corresponding prompt word template according to the predicted SQL category; S33. In the embedding information position reserved in the prompt word template, based on the target problem to be solved and the samples related to the input target problem selected by the ranking model, that is, the few-shot learning information, the database structure, natural language question, and SQL query information are dynamically embedded to obtain the corresponding final prompt word.

7. A system for generating SQL based on difficulty-aware instructions and retrieval-enhanced text, characterized in that: A method for generating SQL from text based on difficulty-aware instructions and retrieval enhancement according to any one of claims 1 to 6, comprising: a difficulty classification module, a sample retrieval module, a dynamic prompt word construction module, and a large prediction model, wherein the difficulty classification module comprises a text classifier based on a Transformer architecture for question difficulty classification, and the sample retrieval module comprises a dual-tower ranking model for sample retrieval and an SQL knowledge base; A text classifier based on the Transformer architecture, including a BERT encoder capable of understanding text semantics and a classification head layer for SQL domain knowledge classification. It is used to classify the difficulty of input natural language questions and obtain the SQL category corresponding to the input question text; The dual-tower ranking model includes a BERT pre-trained encoder model and two fully connected layers for query representation transformation and sample representation transformation respectively. It is used to retrieve samples related to the input target question from the SQL knowledge base based on the input natural language question, and is used to organize prompt words for few-shot learning. Dynamic prompt word construction module, used to dynamically construct prompt words based on the input natural language question, the SQL category predicted by the text classification model, and the samples related to the retrieved target question; The large language model is used to generate executable SQL query statements based on the prompt words to complete the query for the target question.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the method for generating SQL from text based on difficulty-aware instructions and retrieval enhancement as described in any one of claims 1 to 6 is implemented.

9. A processing terminal comprising a memory and a processor, wherein the memory stores a computer program that can be run on the processor, characterized in that: When the processor executes the computer program, the method for generating SQL based on difficulty-aware instructions and retrieval-enhanced text as described in any one of claims 1 to 6 is implemented.

Images