Method, device and equipment for converting natural language problem into SQL (Structured Query Language) and medium

Abstract

The invention relates to a method, a device, equipment and a medium for converting a natural language question into an SQL (Structured Query Language), and belongs to the technical field of natural language processing. Retrieving and recalling A pieces of sample data with the highest similarity with the query vector in the database as first candidate data; performing diversity duplicate removal on the first candidate data to obtain second candidate data; and inputting the input question and the second candidate data into a large language model to generate an SQL query corresponding to the input question. According to the method and the device, the SQL is dynamically generated, and the accuracy of the generated result is ensured.

Description

Technical Field

[0001] The present invention belongs to the technical field of natural language processing, and in particular relates to a method, device, equipment and medium for converting natural language questions into SQL. Background Art

[0002] Natural language processing (NLP) and database query are two key technologies in modern information management and data processing. Converting natural language questions into structured query language (SQL) queries can greatly improve the convenience of data access and user experience. This conversion not only simplifies user operations, but also enables users without database knowledge to efficiently query the required data.

[0003] The conversion methods in the prior art include rule-based, template-based and large language model-based. The rule-based method uses predefined rules and patterns to convert natural language questions into SQL; the template-based method defines pre-query templates, which can be filled with key information from user input to generate SQL queries; large language models provide fine-tuning methods and prompt methods for generating SQL queries. The prompt method usually predefines a set of fixed sample examples, and has technical problems such as poor dynamic performance and low accuracy. Summary of the invention

[0004] In view of the above analysis, the embodiments of the present invention aim to provide a method, apparatus, device and medium for converting natural language questions into SQL, so as to solve one or more of the above problems existing in the prior art.

[0005] The object of the present invention is achieved in that:

[0006] A first aspect of the present invention provides a method for converting a natural language question into SQL, comprising:

[0007] Get the user's input questions and database;

[0008] Retrieve and recall A sample data with the highest similarity to the query vector from the database as first candidate data;

[0009] Performing diversity deduplication on the first candidate data to obtain second candidate data;

[0010] The input question and the second candidate data are input into a large language model to generate an SQL query corresponding to the input question.

[0011] Furthermore, the database includes sample data, and the sample data includes natural language questions and corresponding SQL queries.

[0012] Furthermore, the retrieving and recalling A sample data with the highest similarity to the query vector in the database as the first candidate data includes: vectorizing the natural language questions in the sample data of the database to obtain sample vectors, and vectorizing the input questions to obtain query vectors; calculating the cosine similarity between the query vector and each sample vector and sorting them from high to low; and taking the sample data corresponding to the first A sample vectors in the sorting result as the first candidate data.

[0013] Furthermore, before the input question is vectorized, the process also includes: identifying keywords in the input question, and removing keywords related to time and holidays in the input question.

[0014] Furthermore, the diversity deduplication of the first candidate data to obtain the second candidate data includes: taking the sample data with the highest cosine similarity in the sorting result as the comparison data; extracting the table name and field name in the SQL query in the comparison data; sequentially traversing the first candidate data except the comparison data, if the table name and field name of the current sample data are different from those of the comparison data, adding the current sample data to the comparison data to update the comparison data, if the table name and field name of the current sample data are the same as those of the comparison data, comparing the table name and field name of the next sample data with the comparison data; until the comparison data includes B sample data, stopping the traversal, and taking the B sample data as the second candidate data.

[0015] Furthermore, it also includes: calculating the longest common substring between each natural language question in the first candidate data and the input question; if the second candidate data does not include the longest common substring, replacing the sample data with the lowest cosine similarity in the second candidate data with the sample data corresponding to the longest common substring.

[0016] Furthermore, the method further includes: checking whether the SQL query corresponding to the generated input question has a grammatical error; and correcting the SQL query corresponding to the input question with a language error according to the SQL query in the database.

[0017] A second aspect of the present invention provides a device for converting a natural language question into SQL, comprising:

[0018] The acquisition module is used to obtain the user's input questions and database;

[0019] A retrieval and recall module, used to retrieve and recall A sample data with the highest similarity to the query vector in the database as first candidate data;

[0020] a deduplication module, configured to perform diversity deduplication on the first candidate data to obtain second candidate data;

[0021] A generation module is used to input the input question and the second candidate data into a large language model to generate an SQL query corresponding to the input question.

[0022] A third aspect of the present invention provides an electronic device, including a memory and a processor, wherein the memory stores a computer program, and when the computer program is executed by the processor, the method for converting a natural language question into SQL as described in any embodiment is implemented.

[0023] A fourth aspect of the present invention provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the method for converting a natural language question into SQL as described in any one of the embodiments is implemented.

[0024] Compared with the prior art, the present invention can at least achieve the following beneficial effects:

[0025] The method for converting natural language questions into SQL provided by the present invention dynamically searches and recalls natural language questions and corresponding SQL queries with high similarity in a database according to an input question, removes duplicates from the recalled results through diversity, and then inputs the input question into a large language model to generate an SQL query corresponding to the input question, thereby realizing dynamic generation of SQL and ensuring the accuracy of the generated results. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] In order to more clearly illustrate the embodiments of this specification or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in the embodiments of this specification. For ordinary technicians in this field, other drawings can also be obtained based on these drawings.

[0027] Figure 1 A flowchart of a method for converting a natural language question into SQL provided in Embodiment 1 of the present invention;

[0028] Figure 2 A schematic diagram of a device for converting a natural language question into SQL provided in Embodiment 2 of the present invention;

[0029] Figure 3 This is a schematic diagram of the electronic device architecture provided in Example 3 of the present invention. DETAILED DESCRIPTION

[0030] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. It should be noted that, in the absence of conflict, the embodiments and features in the embodiments disclosed in this disclosure can be combined, separated, interchanged and / or rearranged with each other. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0031] Example 1

[0032] A specific embodiment of the present invention, as Figure 1 As shown, a method for converting a natural language question into SQL is disclosed, comprising the following steps:

[0033] S1. Obtain the user's input questions and database.

[0034] In this embodiment, the database includes sample data, and the sample data includes natural language questions and corresponding SQL queries, as shown below:

[0035] Natural language question: What is the maximum electricity consumption in September 2024?

[0036] SQL query: select dt,elec from table where dt between'20240901'and'20240930'order by elecdesc limit 1.

[0037] S2. Retrieve and recall A sample data with the highest similarity to the query vector from the database as first candidate data.

[0038] In this embodiment, step S2 specifically includes:

[0039] S201, vectorizing the natural language questions in the sample data of the database to obtain a sample vector, and vectorizing the input question to obtain a query vector;

[0040] Specifically, the vectorization processing of natural language questions is based on the selected semantic embedding model, such as word2vec, bge-m3 or bce-embedding. For each natural language question, the model converts it into a multi-dimensional vector to represent its semantic features. In the vectorization processing of the input question, the natural language question is first standardized through word segmentation or preprocessing steps, the key semantic units are extracted, and the query vector is generated using the same semantic embedding model.

[0041] In some embodiments, before the input question is vectorized, the process further includes: identifying keywords in the input question, and removing keywords related to time and holidays in the input question.

[0042] Specifically, vectorized recall is easily affected by specific keywords. Removing unnecessary information can improve recall accuracy, while keywords about time and holidays have a greater impact on recall accuracy.

[0043] S202, calculating the cosine similarity between the query vector and each sample vector and sorting them from high to low;

[0044] Exemplarily, the similarity between the query vector and each sample vector may also be expressed by calculating the edit distance between the two.

[0045] S203: Use the sample data corresponding to the first A sample vectors in the sorting result as the first candidate data.

[0046] Specifically, in order to improve the accuracy of generated SQL and reduce the amount of calculation, this embodiment selects the top 30 sample vectors in the sorting results, and uses the sample data corresponding to these 30 sample vectors as the first candidate data. In this way, by recalling a small number of sample data similar to the input problem in a targeted manner based on the characteristics of the problem, the model can better understand the semantics and logical structure of the problem and the SQL operations that need to be performed, thereby reducing the difficulty of generating SQL queries and improving the accuracy.

[0047] S3. Perform diversity deduplication on the first candidate data to obtain second candidate data.

[0048] In this embodiment, step S3 specifically includes:

[0049] S301, taking the sample data with the highest cosine similarity in the sorting results as comparison data;

[0050] Specifically, the sample data with the highest cosine similarity, that is, the natural language question most similar to the input question, is selected from the first candidate data as comparison data for subsequent deduplication operations, further improving the accuracy of subsequent large language model generation of SQL queries.

[0051] S302, extracting the table name and field name in the SQL query in the comparison data;

[0052] S303, sequentially traverse the first candidate data except the comparison data, if the table name and field name of the current sample data are different from those of the comparison data, add the current sample data to the comparison data to update the comparison data, if the table name and field name of the current sample data are the same as those of the comparison data, compare the table name and field name of the next sample data with those of the comparison data;

[0053] Specifically, by sequentially traversing the sample data other than the comparison data in the first candidate data, compare whether the table name and field name of the corresponding SQL query are the same as the table name and field name of the comparison data. If they are not the same, the sample data currently being traversed is added to the comparison data to form new comparison data. If they are completely consistent and the similarity of their natural language questions is not significantly improved (for example, the ROUGE value is lower than the threshold value of 0.9), it will not be added to the comparison data, and then compare whether the table name and field name of the next sample data of the sample data currently being traversed are the same as those of the new comparison data.

[0054] S304 , until the comparison data includes B sample data, stop traversing and take the B sample data as the second candidate data.

[0055] Exemplarily, in order to further reduce the amount of calculation of the model, B sample data in the first candidate data are used as the second candidate data, preferably, B=A / 6.

[0056] In some embodiments, further comprising:

[0057] S305, calculating the longest common substring between each natural language question in the first candidate data and the input question;

[0058] Specifically, for each natural language question in the first candidate data, a dynamic programming algorithm is used to calculate the longest common substring length with the input question, and the longest common substring of each question is recorded.

[0059] S306: If the second candidate data does not include the longest common substring, replace the sample data with the lowest cosine similarity in the second candidate data with the sample data corresponding to the longest common substring.

[0060] Specifically, the second candidate data is traversed to check whether there is a sample with the longest common substring with the input question. If not, the sample data with the lowest similarity in the second candidate data is replaced with the sample data corresponding to the longest common substring to ensure that the key semantic features of the input question are covered.

[0061] S4: Input the input question and the second candidate data into a large language model to generate an SQL query corresponding to the input question.

[0062] Specifically, the input question and the natural language question from the second candidate data are concatenated with the SQL query as a prompt, and input into a large language model, such as GPT or Llama, and the model generates an SQL query based on the context prompt.

[0063] Compared with the prior art, the method for converting natural language questions into SQL provided in this embodiment dynamically retrieves and recalls natural language questions and corresponding SQL queries with high similarity in the database according to the input questions, deduplicates the recalled results through diversity, and then inputs the input questions into a large language model to generate SQL queries corresponding to the input questions, thereby realizing dynamic generation of SQL and ensuring the accuracy of the generated results.

[0064] In some embodiments, further comprising:

[0065] S5. Check whether the SQL query corresponding to the generated input question has any syntax error.

[0066] If there is a grammatical error, the SQL query corresponding to the input problem with the language error is corrected according to the SQL query in the database.

[0067] Specifically, the generated SQL query is syntax-verified, for example, checking whether the field name and table name are correct and whether they comply with the database's syntax rules. If an error is detected, corrections are made by referring to the SQL query samples stored in the database, and a query statement that meets the requirements is regenerated.

[0068] Example 2

[0069] This embodiment provides a device for converting natural language questions into SQL, such as Figure 2 As shown, including:

[0070] The acquisition module is used to obtain the user's input questions and database;

[0071] A retrieval and recall module, used to retrieve and recall A sample data with the highest similarity to the query vector in the database as first candidate data;

[0072] a deduplication module, configured to perform diversity deduplication on the first candidate data to obtain second candidate data;

[0073] A generation module is used to input the input question and the second candidate data into a large language model to generate an SQL query corresponding to the input question.

[0074] Example 3

[0075] This embodiment provides an electronic device, such as Figure 3As shown, it includes a memory and a processor, the memory stores a computer program, and when the computer program is executed by the processor, the method for converting a natural language question into SQL as described in any of the above embodiments is implemented.

[0076] Example 4

[0077] This embodiment provides a computer-readable storage medium on which a computer program is stored. When the program is executed by a processor, the method for converting a natural language question into SQL as described in any of the above embodiments is implemented.

[0078] Computer readable storage media include permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. Information can be computer readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disk read-only memory (CD-ROM), digital versatile disk (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer readable media does not include temporary computer readable media (transitory media), such as modulated data signals and carrier waves.

[0079] The professionals should further realize that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented by electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the composition and steps of each example have been generally described in the above description according to the function. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of the present invention.

[0080] The steps of the method or algorithm described in conjunction with the embodiments disclosed herein may be implemented using hardware, a software module executed by a processor, or a combination of the two. The software module may be placed in a random access memory (RAM), a memory, a read-only memory (ROM), an electrically programmable ROM, an electrically erasable programmable ROM, a register, a hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art.

[0081] The specific implementation methods described above further illustrate the objectives, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above description is only a specific implementation method of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A method for converting natural language questions into SQL, characterized in that: include: Get the user's input questions and database; Retrieve and recall A sample data with the highest similarity to the query vector from the database as first candidate data; Performing diversity deduplication on the first candidate data to obtain second candidate data; The input question and the second candidate data are input into a large language model to generate an SQL query corresponding to the input question.

2. The method for converting natural language questions into SQL according to claim 1, characterized in that: The database includes sample data, and the sample data includes natural language questions and corresponding SQL queries.

3. The method for converting natural language questions into SQL according to claim 2, characterized in that: The step of retrieving and recalling A sample data with the highest similarity to the query vector from the database as first candidate data includes: Performing vectorization processing on the natural language questions in the sample data of the database to obtain a sample vector, and performing vectorization processing on the input question to obtain a query vector; Calculate the cosine similarity between the query vector and each sample vector and sort them from high to low; The sample data corresponding to the first A sample vectors in the sorting result are used as the first candidate data.

4. The method for converting natural language questions into SQL according to claim 3, characterized in that: Before the input question is vectorized, the process also includes: identifying keywords in the input question, and removing keywords related to time and holidays in the input question.

5. The method for converting natural language questions into SQL according to claim 3, characterized in that: The performing diversity deduplication on the first candidate data to obtain second candidate data includes: The sample data with the highest cosine similarity in the sorting results is used as comparison data; Extracting table names and field names in the SQL query in the comparison data; Sequentially traverse the first candidate data except the comparison data, if the table name and field name of the current sample data are different from those of the comparison data, add the current sample data to the comparison data to update the comparison data, if the table name and field name of the current sample data are the same as those of the comparison data, compare the table name and field name of the next sample data with those of the comparison data; When the comparison data includes B sample data, the traversal is stopped and the B sample data are taken as the second candidate data.

6. The method for converting natural language questions into SQL according to claim 1, characterized in that: Also includes: Calculate the longest common substring between each natural language question in the first candidate data and the input question; If the second candidate data does not include the longest common substring, the sample data with the lowest cosine similarity in the second candidate data is replaced with the sample data corresponding to the longest common substring.

7. The method for converting natural language questions into SQL according to any one of claims 1 to 6, characterized in that: Also includes: Check whether the SQL query corresponding to the generated input question has any syntax errors; The SQL query corresponding to the input problem with the language error is corrected according to the SQL query in the database.

8. A device for converting natural language questions into SQL, characterized in that: The device comprises: The acquisition module is used to obtain the user's input questions and database; A retrieval and recall module, used to retrieve and recall A sample data with the highest similarity to the query vector in the database as first candidate data; a deduplication module, configured to perform diversity deduplication on the first candidate data to obtain second candidate data; A generation module is used to input the input question and the second candidate data into a large language model to generate an SQL query corresponding to the input question.

9. An electronic device, characterized in that: The method comprises a memory and a processor, wherein the memory stores a computer program, and when the computer program is executed by the processor, the method for converting a natural language question into SQL as claimed in any one of claims 1 to 7 is implemented.

10. A storage medium, characterized in that: A computer program is stored thereon, and when the program is executed by a processor, the method for converting a natural language question into SQL as described in any one of claims 1 to 7 is implemented.

Images