Natural language to SQL (Structured Query Language) query method and system, electronic equipment and storage medium

Abstract

The invention discloses a natural language to SQL query method and system. The method comprises the steps that table names and column names in a database are vectorized and stored in a vector database; key information in the natural language question is extracted through LLM, retrieval is carried out in a vector database, and a table name, a column name and a value which are most relevant to the key information are returned; inputting the natural language question into a training set database, and selecting a plurality of examples similar to the natural language question and SQL queries corresponding to the examples from the training set database; inputting the SQL query and a natural language question into an LLM (Logical Language Model), and outputting a candidate SQL query and a generation reason; grouping the candidate SQL queries on the basis of execution results of the candidate SQL queries, grouping the candidate SQL queries with the same execution results in the same group, descending according to the number, and reserving the candidate SQL query with the highest query speed in each group in the first N groups as a candidate SQL list for output; and inputting the SQL question and the natural language question into LLM, and selecting the most accurate SQL query from the LLM. According to the method, complex and changeable natural language query can be better processed, so that the query result is more comprehensive and accurate.

Description

Technical Field

[0001] The present invention belongs to the technical field of natural language processing, and specifically relates to a natural language to SQL query conversion method, system, electronic device and storage medium. Background Art

[0002] In the data-driven era, the rapid development of LLM has significantly improved the performance of context-based learning (ICL) methods in text-to-SQL tasks. However, in benchmarks with complex database schemas and queries, such as BIRD, the performance of these methods is still far below that of human experts. This highlights the need to further improve ICL methods to better serve natural language interface database systems.

[0003] A significant limitation of LLMs in many tasks is their high sensitivity to prompt structure and content. Even for semantically identical prompts, the output of LLMs can differ significantly due to differences in sentence order, example selection, and presentation order. In the context of text-to-SQL, changing the pattern representation and the choice of a small number of examples can also lead to significant changes in the output.

[0004] Chinese Patent CN117931841A-A language conversion method and device based on data enhancement and large language model. Data enhancement technology is used to improve the quantity, diversity and quality of training data; in order to improve the effect of model prediction, the Text-To-SQL task is split into steps, and a large model is used to solve each step one by one, so as to obtain the SQL query statement corresponding to the natural language. This method has the advantages of targeted optimization for different stages and strong model generalization ability, and is particularly suitable for the application of Text-To-SQL. However, this method uses the corpus obtained by data enhancement technology to train the Text-To-SQL model, which consumes a lot of computing resources; the SQL queries generated by this method may only cover limited query types and structures, and the system may not be able to generate correct or optimized SQL queries, resulting in a decrease in the accuracy and comprehensiveness of the query results.

[0005] Chinese patent CN111797637A-interactive annotation method and device, including: obtaining the data to be annotated input by the developer, inputting the data to be annotated into the SQL statement output module, obtaining the first SQL statement output by the module; visually displaying the first SQL statement and forming a modifiable display interface; executing the first SQL statement and displaying the query result corresponding to the first SQL statement; in response to the developer's modification operation on the modifiable display interface, regenerating the second SQL statement; executing the second SQL statement and displaying the query result corresponding to the second SQL statement until the developer confirms that the query result corresponding to the second SQL statement is accurate. The developer uses interactive modification operations on the first SQL statement on the modifiable display interface, thereby lowering the threshold for modifying SQL and improving the accuracy of SQL statements. However, this method relies on the developer to manually modify the SQL statement on the modifiable display interface. There is a lack of a mechanism for automatically optimizing SQL queries.

[0006] Chinese Patent CN118093636A-A method for judging the correctness of SQL statement query, including a table and column selection model and a judgment model. The overall process is divided into two stages. The first stage extracts information from the serialized information of the input database to obtain candidate table and column information; the second stage judges the correctness of the SQL statement based on the user question, the candidate table and column, and the candidate SQL statement. The present invention completes the judgment through two stages, wherein the table and column selection model is used for information filtering in the first stage. The table and column selection model can be used to filter the input information in the first stage to obtain more streamlined database structure information. The second stage is different from the existing related technology in that not only the user query question and the post-selected SQL statement two-tuple are input, but also the filtered database structure information. Therefore, the judgment of the correctness of the statement is completed through the ternary information, thereby improving the accuracy of the model judgment. However, this method can only judge the accuracy of the generated SQL, and cannot generate SQL.

[0007] Chinese Patent CN116521711A-A Text-to-SQL method, system and medium, a Text-to-SQL method, system and medium, relates to the field of database technology, and aims at the problem of long length and database structure in the prior art, and the problem of low accuracy when generating SQL. This application aims at the problem of long length and the difficulty of model memory information and weak information correspondence ability in the database structure. This application solves the problem of difficulty in memory information and weak information correspondence ability through information marking and application of SQL grammar rules, thereby solving the problem of long length and database structure in the prior art, and the problem of low accuracy when generating SQL, and improves the accuracy of SQL generation. However, this method mainly relies on information marking and SQL grammar rules, and the generated SQL query may lack diversity, and it is difficult to handle complex and changeable natural language queries, which affects the comprehensiveness and accuracy of the generated SQL query; this method relies on information marking and SQL grammar rules to solve the problem of difficulty in memory information and weak information correspondence ability. If the marking and rules are not defined properly, the generated SQL query may be inaccurate. In addition, the maintenance and update costs of the rules are high, and the flexibility and adaptability of the system are poor. Summary of the invention

[0008] In view of the above problems, the present invention provides a natural language to SQL query method, system and storage medium, which are intended to better handle complex and changeable natural language queries and ensure the comprehensiveness and accuracy of query results.

[0009] According to a first aspect of an embodiment of the present disclosure, a method for converting a natural language query to an SQL query is provided, the method comprising the following steps:

[0010] Vectorize the table names and column names in the database and store them in the vector database;

[0011] Use LLM to extract key information from natural language questions;

[0012] Searching the vector database based on the key information, and returning table names, column names and values ​​related to the key information;

[0013] Inputting the natural language question into a training set database, and selecting several examples similar to the natural language question and their corresponding SQL queries from the training set database;

[0014] Input the examples and their corresponding SQL queries and the natural language question into the LLM, and the LLM outputs candidate SQL queries and reasons for generating the candidate SQL queries;

[0015] Execute the candidate SQL queries, group the candidate SQL queries based on the execution results, group the candidate SQL queries with the same execution results into the same group, sort the candidate SQL queries in descending order according to the number of candidate SQL queries in each group, retain the candidate SQL queries with the fastest query speed in each group in the top N groups, and output a candidate SQL list;

[0016] The candidate SQL list and the natural language question are input into the LLM, and the LLM selects an accurate SQL query from the candidate SQL list.

[0017] In some embodiments, the several examples and their corresponding SQL queries and the natural language questions are input into the LLM, and the LLM outputs candidate SQL queries and the reasons for generating the candidate SQL queries. The specific implementation process includes: by changing the selection and order of several examples, a plurality of different SQL generation prompts are generated, each SQL generation prompt contains a different combination and order of examples, guiding the LLM to generate a plurality of candidate SQL queries.

[0018] In some embodiments, several examples similar to the natural language question and their corresponding SQL queries are selected from the training set database, specifically by selecting based on question cosine similarity and / or based on masked question cosine similarity.

[0019] In some embodiments, the key information in the natural language question includes keywords, key phrases, entities, and attributes.

[0020] In some embodiments, the table names and column names in the database are vectorized and stored in a vector database. The specific implementation process includes: using a BERT model to embed the table names and column names and store them in a FAISS vector database.

[0021] According to a second aspect of an embodiment of the present disclosure, a natural language to SQL query system is provided, the system comprising:

[0022] A pattern linking module is used to vectorize the table names and column names in the database and store them in a vector database; extract key information in the natural language question using LLM; and search the vector database based on the key information to return the table names, column names and values ​​related to the key information;

[0023] A candidate SQL generation module is used to input the natural language question into a training set database, select several examples similar to the natural language question and their corresponding SQL queries from the training set database; and input the several examples and their corresponding SQL queries and the natural language question into the LLM, and the LLM outputs candidate SQL queries and the reasons for generating the candidate SQL queries;

[0024] A candidate SQL screening module is used to execute the candidate SQL queries, group the candidate SQL queries based on the execution results, group the candidate SQL queries with the same execution results into the same group, sort them in descending order according to the number of candidate SQL queries in each group, retain the candidate SQL queries with the fastest query speed in each group in the top N groups, and output a candidate SQL list; and input the candidate SQL list and the natural language question into the LLM, and the LLM selects an accurate SQL query from the candidate SQL list.

[0025] In some embodiments, in the candidate SQL generation module, several examples similar to the natural language question and their corresponding SQL queries are selected from the training set database, specifically by selecting based on question cosine similarity and / or based on masked question cosine similarity.

[0026] In some embodiments, in the candidate SQL generation module, the several examples and their corresponding SQL queries and the natural language questions are input into the LLM, and the LLM outputs the candidate SQL queries and the reasons for generating the candidate SQL queries. The specific implementation process includes: by changing the selection and order of several examples, a plurality of different SQL generation prompts are generated, each SQL generation prompt contains a different combination and order of examples, guiding the LLM to generate a plurality of candidate SQL queries.

[0027] According to a third aspect of an embodiment of the present disclosure, an electronic device is provided, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of the above-mentioned natural language to SQL query method when executing the program.

[0028] According to a fourth aspect of an embodiment of the present disclosure, a non-temporary computer-readable storage medium is provided, on which computer instructions are stored. When the instructions are executed by a processor, the steps of the above-mentioned natural language to SQL query method are implemented.

[0029] The embodiments of the present disclosure provide a method, system and storage medium for converting natural language to SQL query. The existing methods do not use the sensitivity of LLM to prompt to generate multiple candidate SQL queries, which makes it difficult to handle complex and changeable natural language queries, affecting the comprehensiveness and accuracy of generated SQL queries; they rely on training professional models, require a large amount of labeled data and computing resources; the SQL generation process may require manual participation and cannot be fully automated. Improve the accuracy and efficiency of pattern linking through vectorization and advanced retrieval technology. In the data preparation stage, the table names and column names in the database are vectorized and stored in the vector database, and the keywords, key phrases, entities and attributes in the natural language questions are extracted using LLM, and then retrieved in the vector database. This method not only improves the accuracy of pattern linking, but also can quickly retrieve related table names and column names; multiple prompts are designed through diversified example selection strategies to increase the diversity of generated SQL queries. Two strategies based on question cosine similarity and masked question cosine similarity are used to select a small number of examples from the training data set to generate multiple prompts. This method can capture the semantic similarity and structural similarity of natural language questions, improve the coverage and accuracy of generated SQL queries; by performing result grouping and aggregation and screening based on the number of grouped SQL queries, the screening accuracy of candidate SQL queries is improved and the candidate SQL list is streamlined, reducing resource consumption. In summary, the beneficial effects of the method of the present invention include:

[0030] Through multiple example selection strategies, multiple different SQL generation prompts are generated, which improves the diversity and accuracy of generated SQL queries. It can better handle complex and changeable natural language queries and ensure the comprehensiveness and accuracy of query results.

[0031] In the data preparation stage, the table names and column names in the database are vectorized and stored in the vector database. LLM is used to extract keywords, key phrases, entities and attributes in natural language questions for efficient retrieval. This improves the accuracy and efficiency of pattern linking and enables quick retrieval of relevant table names and column names.

[0032] By executing the result grouping and filtering mechanism, the optimal SQL query is selected, which improves the robustness and accuracy of the query. The generated SQL query can be dynamically adjusted and optimized to ensure the superiority of the final selected SQL query in execution efficiency and result accuracy.

[0033] It is to be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present disclosure. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present invention and, together with the description, serve to explain the principles of the present invention.

[0035] Figure 1 This is a flow chart of a method for converting natural language to SQL query in an embodiment of the present invention;

[0036] Figure 2 It is a logic block diagram of the natural language to SQL query method in an embodiment of the present invention;

[0037] Figure 3 This is a schematic diagram of the structure of a natural language to SQL query system in an embodiment of the present invention;

[0038] Figure 4 It is a schematic diagram of an electronic device in an embodiment of the present invention. DETAILED DESCRIPTION

[0039] The present invention will be further described in detail below in conjunction with the accompanying drawings and embodiments. It is to be understood that the specific embodiments described herein are only used to explain the present invention, rather than to limit the present invention. It should also be noted that, for ease of description, only parts related to the present invention, rather than all structures, are shown in the accompanying drawings.

[0040] It should be mentioned before discussing the exemplary embodiments in more detail that some exemplary embodiments are described as processes or methods depicted as flow charts. Although the flow charts describe the steps as sequential processes, many of the steps therein can be implemented in parallel, concurrently or simultaneously. In addition, the order of the steps can be rearranged. The process can be terminated when its operation is completed, but can also have additional steps not included in the accompanying drawings. The process can correspond to a method, function, procedure, subroutine, subprogram, etc.

[0041] Some terminology descriptions based on the embodiments of the present invention:

[0042] SQL: Structured Query Language.

[0043] Text-to-SQL: Convert natural language questions into SQL database queries.

[0044] LLM: Large Language Model.

[0045] BIRD: A dataset of 12,751 unique question-SQL pairs across 95 large databases covering 37 professional domains.

[0046] Few-shot Learning: A learning paradigm in which the model is trained on only a small number of examples.

[0047] Zero-shot Learning: A learning paradigm in which the model has not seen any training examples when making predictions.

[0048] In-context Learning: A learning method where a model learns to perform a task by given a few examples in context.

[0049] The present invention provides a method, system and storage medium for converting natural language to SQL query, and provides the following embodiments:

[0050] Example 1 is used to illustrate the natural language to SQL query method, see Figure 1 , which is a flow chart of the natural language to SQL query method, the specific steps include:

[0051] S1. Vectorize the table names and column names in the database and store them in the vector database;

[0052] Specifically, the table names and column names in the database are vectorized and stored in the vector database. The specific implementation process includes: using the BERT model to embed the table names and column names and store them in the FAISS vector database.

[0053] S2, extract key information from natural language questions using LLM;

[0054] Specifically, the key information in the natural language question includes keywords, key phrases, entities and attributes.

[0055] S3. Search the vector database based on the key information and return the table name, column name and value most relevant to the key information;

[0056] S4, inputting the natural language question into a training set database, and selecting several examples similar to the natural language question and their corresponding SQL queries from the training set database;

[0057] Specifically, the selection is performed based on question cosine similarity and / or based on mask question cosine similarity.

[0058] S5, inputting the several examples and their corresponding SQL queries and the natural language question into the LLM, and the LLM outputs candidate SQL queries and reasons for generating the candidate SQL queries;

[0059] Specifically, by changing the selection and order of several examples, a plurality of different SQL generation prompts are generated, each SQL generation prompt contains a different combination and order of examples, and guides the LLM to generate a plurality of candidate SQL queries.

[0060] S6. Execute the candidate SQL queries, group the candidate SQL queries based on the execution results, group the candidate SQL queries with the same execution results into the same group, sort the candidate SQL queries in descending order according to the number of candidate SQL queries in each group, retain the candidate SQL queries with the fastest query speed in each group in the top N groups, and output a candidate SQL list;

[0061] S7. Input the candidate SQL list and the natural language question into the LLM, and the LLM selects the most accurate SQL query from the candidate SQL list.

[0062] In the specific implementation process, Figure 2 As shown in Figure 1, in order to improve the accuracy and robustness of LLM in the Text-To-SQL task, multiple prompts are used to generate various candidate answers and effectively aggregate these answers. This method first performs pattern linking through multiple prompts to select tables and columns related to the question, then generates a variety of candidate SQL queries, and finally filters the candidate queries based on the SQL query execution results, and selects the best query through a multi-selection selection mechanism.

[0063] (1) Schema link (establishing database schema)

[0064] 1) In the data preparation stage, the table names and column names in the database are vectorized and stored in the vector database.

[0065] 2) Use LLM to extract keywords, key phrases, entities and attributes in natural language questions.

[0066] 3) Use the keywords obtained in 2) to search in the vector database in 1) and return the table name, column name and value most relevant to each keyword. Specifically, embedding is used for related retrieval, by converting keywords into high-dimensional vectors, and then similarity retrieval is performed based on the distance between vectors.

[0067] (2) Candidate SQL Generation

[0068] 1) Multiple example selection. We input the natural language question of the test sample and select a small number of examples from the training dataset to generate multiple prompts. We use two selection strategies: based on question cosine similarity and based on masked question cosine similarity, and then output candidate SQL queries generated by different prompts.

[0069] 2) SQL generation. Input a small number of examples, database schema (database table name and column name information), example table content, and natural language questions, use LLM to generate SQL queries, explain the reasoning steps in the generation process, and output candidate SQL queries and their generation reasons. In this process, by changing the selection and presentation order of a small number of examples, multiple different SQL generation prompts are generated. Each prompt contains different example combinations and orders, thereby generating multiple candidate SQL queries.

[0070] (3) Candidate SQL Screening

[0071] 1) Candidate SQL aggregation. Input candidate SQL queries, group the candidate queries based on the execution results, group the SQL queries with the same execution results into the same group, sort them according to the number of SQL queries in each group, retain the fastest SQL queries in the three groups with the largest number of SQL queries, and output the streamlined candidate SQL list.

[0072] 2) Final selection. Input the streamlined candidate SQL list, database schema, and natural language question, use LLM to select the most accurate query from the candidate list, and output the final selected SQL query. Specifically, the large language model prompt (Prompt) composed of the SQL list, database schema, and natural language question is given to the selection model, and the selection model makes the selection.

[0073] Example Take a natural language question: "List the names of all employees and their departments." Give an example:

[0074] (1) Mode link

[0075] 1) Data preparation

[0076] Steps: Vectorize the table names and column names in the database and store them in the vector database.

[0077] Operation: Use the BERT model to embed the table name and column name and store them in the FAISS vector database.

[0078] 2.) Keyword extraction

[0079] Steps: LLM is used to extract keywords, key phrases, entities and attributes in natural language questions.

[0080] Operation: Use LLM to extract keywords and entities, such as "employee", "department", and "name".

[0081] 3) Vector search

[0082] Steps: Use the extracted keywords to search in the vector database and return the table name, column name and value most relevant to each keyword.

[0083] Operation: Search the vector database and return the table name and column name, such as "employees", "departments", "employee_name", "department_name".

[0084] (2) Candidate SQL Generation

[0085] 1) Multiple sample options

[0086] Procedure: Input a natural language question and select a small number of examples from the training dataset to generate a variety of prompts using both question cosine similarity-based and masked question cosine similarity-based methods.

[0087] Action: Calculate the cosine similarity of the problem, select a sample problem similar to the test problem and the corresponding SQL query. For example, select the sample problem "List the names of all students and their classes".

[0088] 2) SQL Generation

[0089] Steps: Input a small number of examples, database schemas, sample table contents, and natural language questions, use LLM to generate SQL queries, and explain the reasoning steps in the generation process.

[0090] Action: Generate multiple candidate SQL queries using different prompts, each containing a different combination and order of examples.

[0091] (3) Candidate SQL Screening

[0092] 1) Candidate SQL aggregation

[0093] Steps: Input candidate SQL queries into the database, group the candidate queries based on the execution results, group the SQL queries with the same execution results into the same group, sort them according to the number of SQL queries in each group, and retain the fastest SQL queries in the three groups with the largest number of SQL queries.

[0094] Operation: Execute candidate SQL queries and group them by execution results. For example, SQL1: "SELECT employees.name, departments.name FROM employees JOIN departments ON employees.department_id=departments.id;" and SQL2: "SELECT e.name, d.name FROM employees AS e JOIN departments AS d ON e.department_id=d.id;" have the same query results, so the two SQLs are classified into the same group. Further, assuming that SQL1 executes faster, SQL1 is added to the candidate SQL list as the representative SQL of the reorganization. Finally, 3 SQLs are retained in the candidate SQL list.

[0095] 2) Final selection

[0096] Steps: Input the streamlined candidate SQL list, database schema, and natural language questions, and use LLM to select the most accurate query from the candidate list.

[0097] Action: Use LLM to score and select candidate SQL queries to obtain the final SQL "SELECT employees.name, departments.name FROM employees JOIN departments ON employees.department_id = departments.id;".

[0098] Another embodiment is used to illustrate the natural language to SQL query system, see Figure 3 , the system 300 comprises:

[0099] The pattern linking module 310 is used to vectorize the table names and column names in the database and store them in the vector database; extract key information in the natural language question using LLM; and search the vector database based on the key information to return the table name, column name and value most relevant to the key information;

[0100] A candidate SQL generation module 320 is configured to input the natural language question into a training set database, select several examples similar to the natural language question and their corresponding SQL queries from the training set database; and input the several examples and their corresponding SQL queries and the natural language question into an LLM, and the LLM outputs candidate SQL queries and reasons for generating the candidate SQL queries;

[0101] The candidate SQL screening module 330 is used to execute the candidate SQL queries in the vector database, group the candidate SQL queries based on the execution results, group the candidate SQL queries with the same execution results into the same group, sort them in descending order according to the number of candidate SQL queries in each group, retain the candidate SQL queries with the fastest query speed in each of the top N groups, and output a candidate SQL list; and input the candidate SQL list and the natural language question into the LLM, and the LLM selects the most accurate SQL query from the candidate SQL list.

[0102] In the candidate SQL generation module 320, several examples similar to the natural language question and their corresponding SQL queries are selected from the training set database, specifically by selecting based on question cosine similarity and / or based on masked question cosine similarity.

[0103] In the candidate SQL generation module 320, the several examples and their corresponding SQL queries and the natural language questions are input into the LLM, and the LLM outputs the candidate SQL queries and the reasons for generating the candidate SQL queries. The specific implementation process includes: by changing the selection and order of several examples, a plurality of different SQL generation prompts are generated, each SQL generation prompt contains a different combination and order of examples, guiding the LLM to generate a plurality of candidate SQL queries.

[0104] In addition to the upper module, the system 300 may also include other components. However, since these components are irrelevant to the content of the embodiment of the present disclosure, their illustration and description are omitted here.

[0105] The other specific working processes of the natural language to SQL query system 300 refer to the description of the above-mentioned natural language to SQL query method embodiment and will not be repeated here.

[0106] Another embodiment is used to illustrate that the system of the present invention can also be used with the help of Figure 4 The architecture of the computing device shown is implemented. Figure 4 The architecture of the computing device is shown. Figure 4 As shown, a computer system 410, a system bus 430, one or more CPUs 440, an input / output 420, a memory 450, etc. The memory 450 can store various data or files used for computer processing and / or communication and program instructions including the natural language to SQL query method of the embodiment executed by the CPU. Figure 4 The architecture shown is only exemplary and can be adjusted according to actual needs when implementing different devices. Figure 4One or more components in the system. The memory 450, as a computer-readable storage medium, can be used to store software programs, computer executable programs and modules, such as program instructions / modules corresponding to the natural language to SQL query method in the embodiment of the present invention (for example, the pattern linking module 310, the candidate SQL generation module 320 and the candidate SQL screening module 330 in the natural language to SQL query system 300). One or more CPUs 440 execute various functional applications and data processing of the system of the present invention by running the software programs, instructions and modules stored in the memory 450, that is, to implement the above-mentioned natural language to SQL query method, which includes the following steps:

[0107] Vectorize the table names and column names in the database and store them in the vector database;

[0108] Use LLM to extract key information from natural language questions;

[0109] Searching the vector database based on the key information, returning the table name, column name and value most relevant to the key information;

[0110] Inputting the natural language question into a training set database, and selecting several examples similar to the natural language question and their corresponding SQL queries from the training set database;

[0111] Input the examples and their corresponding SQL queries and the natural language question into the LLM, and the LLM outputs candidate SQL queries and reasons for generating the candidate SQL queries;

[0112] Execute the candidate SQL queries in the vector database, group the candidate SQL queries based on the execution results, group the candidate SQL queries with the same execution results into the same group, sort the candidate SQL queries in descending order according to the number of candidate SQL queries in each group, retain the candidate SQL queries with the fastest query speed in each group in the top N groups, and output a candidate SQL list;

[0113] The candidate SQL list and the natural language question are input into the LLM, and the LLM selects the most accurate SQL query from the candidate SQL list.

[0114] Of course, the processor of the server provided in the embodiment of the present invention is not limited to executing the method operations described above, but can also execute related operations in the natural language to SQL query method provided in any embodiment of the present invention.

[0115] The memory 450 may mainly include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application required for at least one function; the data storage area may store data created according to the use of the terminal, etc. In addition, the memory 450 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some instances, the memory 450 may further include a memory remotely arranged relative to one or more CPUs 440, and these remote memories may be connected to the device via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0116] The input / output 420 may be used to receive input digital or character information and generate key signal input related to user settings and function control of the device. The input / output 420 may also include a display device such as a display screen.

[0117] The embodiment of the present invention also provides a non-temporary computer-readable storage medium, on which a computer program is stored. When the computer program is executed by the processor, the natural language to SQL query method described in the above embodiment is implemented. The computer-readable storage medium of the embodiment of the present invention can adopt any combination of one or more computer-readable media. The computer-readable medium can be a computer-readable signal medium or a computer-readable storage medium. The computer-readable storage medium can be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples of computer-readable storage media (a non-exhaustive list) include: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In this document, a computer-readable storage medium can be any tangible medium containing or storing a program, which can be used by or in combination with an instruction execution system, device or device.

[0118] Computer-readable signal media may include data signals propagated in baseband or as part of a carrier wave, which carry computer-readable program code. Such propagated data signals may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. Computer-readable signal media may also be any computer-readable medium other than a computer-readable storage medium, which may send, propagate, or transmit a program for use by or in conjunction with an instruction execution system, apparatus, or device.

[0119] The program code contained on the storage medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.

[0120] In addition, other specific working processes of a non-temporary computer-readable storage medium refer to the description of the above-mentioned natural language to SQL query method embodiment and will not be repeated here.

[0121] In summary, the technical solutions provided by the above embodiments provide a natural language to SQL query method and system, a natural language to SQL query method, system and storage medium. The existing methods do not utilize the sensitivity of LLM to prompt to generate multiple candidate SQL queries, which makes it difficult to handle complex and changeable natural language queries, affecting the comprehensiveness and accuracy of generated SQL queries; they rely on training professional models, require a large amount of annotated data and computing resources; the SQL generation process may require manual participation and cannot be fully automated. Improve the accuracy and efficiency of pattern linking through vectorization and advanced retrieval technology. In the data preparation stage, the table names and column names in the database are vectorized and stored in the vector database, and the keywords, key phrases, entities and attributes in the natural language questions are extracted using LLM, and searched in the vector database. This method not only improves the accuracy of pattern linking, but also can quickly retrieve related table names and column names; multiple prompts are designed through diversified example selection strategies to increase the diversity of generated SQL queries. Two strategies based on question cosine similarity and masked question cosine similarity are used to select a small number of examples from the training data set to generate multiple prompts. This method can capture the semantic similarity and structural similarity of natural language questions, improve the coverage and accuracy of generated SQL queries; by performing result grouping and aggregation and screening based on the number of grouped SQL queries, the screening accuracy of candidate SQL queries is improved and the candidate SQL list is streamlined, reducing resource consumption. In summary, the beneficial effects of the method of the present invention include:

[0122] Through multiple example selection strategies, multiple different SQL generation prompts are generated, which improves the diversity and accuracy of generated SQL queries. It can better handle complex and changeable natural language queries and ensure the comprehensiveness and accuracy of query results.

[0123] In the data preparation stage, the table names and column names in the database are vectorized and stored in the vector database. LLM is used to extract keywords, key phrases, entities and attributes in natural language questions for efficient retrieval. This improves the accuracy and efficiency of pattern linking and enables quick retrieval of relevant table names and column names.

[0124] By executing the result grouping and filtering mechanism, the optimal SQL query is selected, which improves the robustness and accuracy of the query. The generated SQL query can be dynamically adjusted and optimized to ensure the superiority of the final selected SQL query in execution efficiency and result accuracy.

[0125] In this document, the terms "comprises," "comprising" or any other variations thereof are intended to cover non-exclusive inclusion, such that a step or method that includes a series of elements includes not only those elements, but also includes other elements not explicitly listed, or also includes elements inherent to such step or method.

[0126] The above contents are further detailed descriptions of the present invention in combination with specific preferred embodiments, and it cannot be determined that the specific implementation of the present invention is limited to these descriptions. For ordinary technicians in the technical field to which the present invention belongs, several simple deductions or substitutions can be made without departing from the concept of the present invention, which should be regarded as falling within the protection scope of the present invention.

Claims

1. A natural language to SQL query method, characterized in that: The method comprises the following steps: Vectorize the table names and column names in the database and store them in the vector database; Use LLM to extract key information from natural language questions; Searching the vector database based on the key information, and returning table names, column names and values ​​related to the key information; Inputting the natural language question into a training set database, and selecting several examples similar to the natural language question and their corresponding SQL queries from the training set database; Input the examples and their corresponding SQL queries and the natural language question into the LLM, and the LLM outputs candidate SQL queries and reasons for generating the candidate SQL queries; Execute the candidate SQL queries, group the candidate SQL queries based on the execution results, group the candidate SQL queries with the same execution results into the same group, sort the candidate SQL queries in descending order according to the number of candidate SQL queries in each group, retain the candidate SQL queries with the fastest query speed in each group in the top N groups, and finally output a candidate SQL list; The candidate SQL list and the natural language question are input into the LLM, and the LLM selects an accurate SQL query from the candidate SQL list.

2. The natural language to SQL query method according to claim 1, characterized in that: The several examples and their corresponding SQL queries and the natural language questions are input into the LLM, and the LLM outputs candidate SQL queries and the reasons for generating the candidate SQL queries. The specific implementation process includes: by changing the selection and order of several examples, a plurality of different SQL generation prompts are generated, each SQL generation prompt contains a different combination and order of examples, and guides the LLM to generate a plurality of candidate SQL queries.

3. The natural language to SQL query method according to claim 1, characterized in that: A number of examples similar to the natural language question and their corresponding SQL queries are selected from the training set database, specifically by selecting based on question cosine similarity and / or based on masked question cosine similarity.

4. The natural language to SQL query method according to claim 1, characterized in that: The key information in the natural language question includes keywords, key phrases, entities and attributes.

5. The natural language to SQL query method according to claim 1, characterized in that: The table names and column names in the database are vectorized and stored in the vector database. The specific implementation process includes: using the BERT model to embed the table names and column names and storing them in the FAISS vector database.

6. A natural language to SQL query system, characterized in that: The system comprises: A pattern linking module is used to vectorize the table names and column names in the database and store them in a vector database; extract key information in the natural language question using LLM; and search the vector database based on the key information to return the table names, column names and values ​​related to the key information; A candidate SQL generation module is used to input the natural language question into a training set database, select several examples similar to the natural language question and their corresponding SQL queries from the training set database; and input the several examples and their corresponding SQL queries and the natural language question into the LLM, and the LLM outputs candidate SQL queries and the reasons for generating the candidate SQL queries; A candidate SQL screening module is used to execute the candidate SQL queries, group the candidate SQL queries based on the execution results, group the candidate SQL queries with the same execution results into the same group, sort the candidate SQL queries in descending order according to the number of candidate SQL queries in each group, retain the candidate SQL queries with the fastest query speed in each group in the top N groups, and finally output a candidate SQL list; and The candidate SQL list and the natural language question are input into the LLM, and the LLM selects an accurate SQL query from the candidate SQL list.

7. The natural language to SQL query system according to claim 6, characterized in that: In the candidate SQL generation module, several examples similar to the natural language question and their corresponding SQL queries are selected from the training set database, specifically by selecting based on question cosine similarity and / or based on masked question cosine similarity.

8. The natural language to SQL query system according to claim 6, characterized in that: In the candidate SQL generation module, the several examples and their corresponding SQL queries and the natural language questions are input into the LLM, and the LLM outputs the candidate SQL queries and the reasons for generating the candidate SQL queries. The specific implementation process includes: by changing the selection and order of several examples, a plurality of different SQL generation prompts are generated, each SQL generation prompt contains a different example combination and order, and guides the LLM to generate a plurality of candidate SQL queries.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the program, the steps of the natural language to SQL query method according to any one of claims 1 to 5 are implemented.

10. A non-transitory computer-readable storage medium having computer instructions stored thereon, characterized in that: When the instructions are executed by the processor, the steps of the natural language to SQL query method as described in any one of claims 1 to 5 are implemented.

Images