A method for automatically generating SQL statements from Chinese natural language

Abstract

A method for automatically generating sql statements from Chinese natural language, involving the technical field of large language models, automatically matches the corresponding database based on the original Chinese questions of users through the Chinese description information of the database, and also adds Chinese descriptions to the database table creation statements and sql-question pairs, thereby improving the accuracy of Chinese matching and simplifying the interaction between users and databases. The connection between questions and table contents, and between questions and sql-question pairs is strengthened, and feature vectors are constructed to optimize and eliminate irrelevant database prompt information, thereby improving the matching rate between Chinese natural language query descriptions and data stored in the database.

Description

Technical Field

[0001] The present invention relates to the technical field of large language models, and in particular to a method for automatically generating SQL statements using Chinese natural language. Background Art

[0002] In real life, there are many databases that store information from all walks of life, such as school course selection information, grade information, company accounting information, and personnel turnover. SQL is a database query language with great flexibility and powerful functions. However, for ordinary people, the learning threshold of SQL is relatively high. Even for computer practitioners, it is troublesome to write a large number of SQL statements with guaranteed accuracy for different databases and application scenarios. If we can have a tool that automatically converts our descriptions into SQL query statements and then gives them to the computer to execute, we can easily query the database, which will greatly improve our life and work efficiency.

[0003] Text2SQL is a natural language processing technology that aims to convert natural language expressions into structured query language (SQL) to facilitate interaction and querying with databases. The development of Text2SQL originated in the early 1960s and initially relied mainly on rule-based methods. These methods convert natural language queries into SQL queries through manually written grammar rules and templates, but this approach has limited scalability and adaptability.

[0004] With the advancement of machine learning and natural language processing technologies, statistical and machine learning based methods began to emerge, and researchers began to use corpus data and machine learning algorithms to build Text2SQL models that can automatically convert natural language queries into SQL queries.

[0005] However, traditional Text2sql technology faces several major challenges. First, semantic ambiguity will arise when dealing with synonyms, polysemous words, and fuzzy expressions, making it difficult to ensure accurate SQL generation. Second, rule-based methods rely on manually written grammar rules and templates. For complex queries, a large number of rules and templates are required, which are difficult to maintain and expand. In addition, limited by the size of the data and the complexity of the model, the performance of traditional models is very limited, and lacks the necessary background knowledge support, which also affects the accuracy of the generated SQL. In particular, Text2sql for Chinese tasks is limited by the lack of Chinese open source datasets, which has a great impact on the results. Summary of the invention

[0006] In order to overcome the deficiencies of the above technologies, the present invention provides a method for automatically generating SQL statements from Chinese natural language, which fully utilizes the advantages of a large language model and optimizes the conversion process from natural language to SQL commands by integrating a vector database and a knowledge enhancement strategy.

[0007] The technical solution adopted by the present invention to overcome the technical problems is:

[0008] A method for automatically generating SQL statements using Chinese natural language, comprising:

[0009] S1. Get the Chinese description statement set of the database , , For the Chinese description sentences, , is the total number of Chinese description sentences in the database, Chinese description sentences The corresponding database DDL table creation statement set is , , For the A table creation statement, , For the Chinese description sentences The total number of table creation statements in the corresponding database, Chinese description sentences The corresponding database sql-question question-answer pair set , , For the sql-question question and answer pair, , For the Chinese description sentences The total number of sql-question question-answer pairs corresponding to the database;

[0010] S2. Create table statement set based on DDL Get the DDL table creation statement set after vectorized mapping , according to the Chinese description statement set of the database Get the Chinese description statement set after vectorized mapping ;

[0011] S3. Based on the sql-question question-answer pair set of the database Get the sql-question question-answer pair set after vectorized mapping ;

[0012] S4. Acquisition Chinese natural language questions, and get the Chinese natural language question set , , For the Chinese natural language problem, , is the total number of Chinese natural language questions, based on the Chinese natural language question set Get the Chinese natural language question set after vectorization mapping ;

[0013] S5. Get the same as Chinese natural language problem after vectorization mapping The most similar Chinese description sentence is obtained according to the most similar Chinese description sentence. Chinese natural language problem after vectorization mapping The top 10 most similar table creation statements and Chinese natural language problem after vectorization mapping The top 10 most similar Chinese natural language questions;

[0014] S6. Calculate the Chinese natural language problem With sql-question Similarity of Chinese natural language questions in , according to the similarity Get with Chinese natural language problem The second most similar top 10 Chinese natural language questions;

[0015] S7. Perform two-way fusion recall using the first top 10 Chinese natural language questions and the second top 10 Chinese natural language questions to obtain the third top 10 Chinese natural language questions;

[0016] S8. Reorder the third top 10 Chinese natural language questions to obtain reordered top 10 Chinese natural language questions, and use the reordered top 10 Chinese natural language questions to obtain database SQL query statements using a large language model.

[0017] Furthermore, each table creation statement in step S1 has its corresponding Chinese description, and each field in each table creation statement has its corresponding Chinese description.

[0018] Furthermore, the set of SQL statements in the SQL-question question-answer pair in the database in step S1 is , , For the sql-question question and answer sql statements, the Chinese natural language question set in the sql-question question-answer pair of the database is , , For the sql-question question and answer Chinese natural language problem, .

[0019] Further, step S2 includes the following steps:

[0020] S2-1. Use the embedding method to embed Create table statement Perform vectorized mapping to obtain the first Create table statement ,all The vectorized mapped table creation statements constitute the vectorized mapped DDL table creation statement set , , , for dimensional real number space;

[0021] S2-2. Use the embedding method to embed Chinese description sentences Perform vectorized mapping to obtain the first Chinese description sentences ,all The Chinese description statements after vectorization mapping constitute the Chinese description statement set after vectorization mapping , , .

[0022] Furthermore, in step S3, the embedding method is used to embed the first Chinese natural language problem Perform vectorized mapping to obtain the first Chinese natural language problem , the sql-question question-answer pair after vectorization mapping is , ,all The vectorized mapped sql-question question-answer pairs constitute the vectorized mapped sql-question question-answer pair set , .

[0023] Furthermore, in step S4, the embedding method is used to embed Chinese natural language problem Perform vectorized mapping to obtain the first Chinese natural language problem ,all The Chinese natural language questions after vectorization mapping constitute the Chinese natural language question set after vectorization mapping. , .

[0024] Further, step S5 includes the following steps:

[0025] S5-1. Calculate the Chinese natural language problem after vectorization mapping The Chinese description statement set after vectorization mapping middle The cosine similarity of each of the Chinese description sentences after vectorization mapping is calculated, and the maximum cosine similarity value is the first Chinese description sentences As the Chinese natural language problem after vectorization mapping The most similar vectorized Chinese description sentence, , after vectorized mapping Chinese description sentences The corresponding Chinese description sentences As the most similar Chinese description sentence;

[0026] S5-2. Calculate the Chinese natural language problem after vectorization mapping With Chinese description sentences DDL table creation statement set after vectorized mapping middle The cosine similarity of the table building statements after vectorization mapping is Sort the cosine similarity values ​​in descending order, and take the table creation statements corresponding to the vectorized mapping of the first 10 cosine similarity values ​​after sorting. , according to the table creation statements corresponding to each vectorized mapped table creation statement As the top 10 most similar table creation statements;

[0027] S5-3. Calculate the Chinese natural language problem after vectorization mapping With sql-question question and answer middle The cosine similarity of the Chinese natural language questions after vectorization mapping is The cosine similarity values ​​are sorted in descending order, and the first 10 cosine similarity values ​​after sorting are taken to correspond to the vectorized mapped Chinese natural language questions. , according to the Chinese natural language questions corresponding to each vectorized natural language question As the first top 10 Chinese natural language problem.

[0028] Further, in step S6, the formula

[0029] Calculate the similarity , where and All are parameters. , For the The term in Chinese natural language problem The word frequency in , For the Chinese natural language problem The total number of distinct terms in , For the sql-question question and answer Chinese natural language problem The number of words, For the The term is the first in the sql-question Chinese natural language problem The word frequency in For sql-question question and answer The average length of Chinese natural language questions;

[0030] Will The similarity values ​​are sorted in descending order, and the Chinese natural language questions corresponding to the first 10 similarity values ​​after sorting are taken. As the second top 10 Chinese natural language problem.

[0031] Furthermore, in step S7, the Reciprocal Rank Fusion algorithm is used to perform two-way fusion recall on the first top 10 Chinese natural language questions and the second top 10 Chinese natural language questions to obtain the third top 10 Chinese natural language questions. .

[0032] Further, step S8 includes the following steps:

[0033] S8-1. Input the third top 10 Chinese natural language questions into the bge-reranker model for reranking, and output the reranked top 10 Chinese natural language questions ;

[0034] S8-2. Top 10 Chinese natural language questions based on re-ranking Corresponding top10sql-question question and answer pairs ;

[0035] S8-3. Top10sql-question questions and answers , No. Chinese natural language problem , the top 10 most similar table creation statements Perform a concatenation operation, input the concatenation result into the LLM large language model, and output the database SQL query statement.

[0036] The beneficial effects of the present invention are: there is no need to specify the database in advance, and the corresponding database can be automatically matched based on the user's original Chinese question through the Chinese description information of the database. In addition, the Chinese description is also added to the database table creation statement and the sql-question pair, which improves the Chinese matching accuracy and simplifies the interaction between the user and the database. Secondly, for the original question raised by the user, the Text2SQL method designed in this paper strengthens the connection between the question and the table content, and the question and the sql-question pair after embedding vectorization, BM25 statistical algorithm, RRF multi-way fusion algorithm and rerank reordering, constructs a feature vector to optimize the irrelevant database prompt information, and improves the matching rate between the Chinese natural language query description and the data stored in the database. At the same time, it gives full play to the advantages of large-scale language models, optimizes the conversion process from natural language to SQL commands by integrating vectorization technology and knowledge enhancement strategies, and provides a simple database query solution for non-technical users. DETAILED DESCRIPTION

[0037] The present invention is further described below.

[0038] A method for automatically generating SQL statements using Chinese natural language, comprising:

[0039] S1. Get the Chinese description statement set of the database , , For the Chinese description sentences, , is the total number of Chinese description sentences in the database, Chinese description sentences The corresponding database DDL table creation statement set is , , For the A table creation statement, , For the Chinese description sentences The total number of table creation statements in the corresponding database, Chinese description sentences The corresponding database sql-question question-answer pair set , , For the sql-question question and answer pair, , For the Chinese description sentences The total number of sql-question question-answer pairs corresponding to the database.

[0040] S2. Create table statement set based on DDL Get the DDL table creation statement set after vectorized mapping , according to the Chinese description statement set of the database Get the Chinese description statement set after vectorized mapping .

[0041] S3. Based on the sql-question question-answer pair set of the database Get the sql-question question-answer pair set after vectorized mapping .

[0042] S4. Acquisition Chinese natural language questions, and get the Chinese natural language question set , , For the Chinese natural language problem, , is the total number of Chinese natural language questions, based on the Chinese natural language question set Get the Chinese natural language question set after vectorization mapping .

[0043] S5. Get the same as Chinese natural language problem after vectorization mapping The most similar Chinese description sentence is obtained according to the most similar Chinese description sentence. Chinese natural language problem after vectorization mapping The top 10 most similar table creation statements and Chinese natural language problem after vectorization mapping The top 10 most similar Chinese natural language questions.

[0044] S6. Calculate the Chinese natural language problem With sql-question Similarity of Chinese natural language questions in , according to the similarity Get with Chinese natural language problem The second most similar top10 Chinese natural language questions.

[0045] S7. Perform two-way fusion recall using the first top 10 Chinese natural language questions and the second top 10 Chinese natural language questions to obtain the third top 10 Chinese natural language questions.

[0046] S8. Reorder the third top 10 Chinese natural language questions to obtain reordered top 10 Chinese natural language questions, and use the reordered top 10 Chinese natural language questions to obtain database SQL query statements using a large language model.

[0047] The method of automatically generating SQL statements from Chinese natural language of the present invention combines a large language model, a vector database and multiple algorithms to achieve efficient conversion from natural language to SQL query. The accuracy and efficiency of the Text2SQL task are improved by using technologies such as RAG model training, semantic retrieval, text recall matching and rerank result reordering. At the same time, its goal is to give full play to the advantages of large language models, optimize the conversion process from natural language to SQL commands by integrating vector databases and knowledge enhancement strategies, provide a simple database query solution for non-technical users, and open up a new direction for the cross-application of natural language processing technology and database query technology.

[0048] In one embodiment of the present invention, each table creation statement in step S1 has its corresponding Chinese description, and each field in each table creation statement has its corresponding Chinese description.

[0049] In one embodiment of the present invention, the set of SQL statements in the SQL-question question-answer pair in the database in step S1 is , , For the sql-question question and answer sql statements, the Chinese natural language question set in the sql-question question-answer pair of the database is , , For the sql-question question and answer Chinese natural language problem, .

[0050] In one embodiment of the present invention, step S2 comprises the following steps:

[0051] S2-1. Use the embedding method to embed Create table statement Perform vectorized mapping to obtain the first Create table statement ,all The vectorized mapped table creation statements constitute the vectorized mapped DDL table creation statement set , , , for dimensional real number space.

[0052] S2-2. Use the embedding method to embed Chinese description sentences Perform vectorized mapping to obtain the first Chinese description sentences ,all The Chinese description statements after vectorization mapping constitute the Chinese description statement set after vectorization mapping , , .

[0053] In one embodiment of the present invention, step S3 uses an embedding method to embed the first Chinese natural language problem Perform vectorized mapping to obtain the first Chinese natural language problem , the sql-question question-answer pair after vectorization mapping is , ,all The vectorized mapped sql-question question-answer pairs constitute the vectorized mapped sql-question question-answer pair set , .

[0054] In one embodiment of the present invention, step S4 uses an embedding method to embed Chinese natural language problem Perform vectorized mapping to obtain the first Chinese natural language problem ,all The Chinese natural language questions after vectorization mapping constitute the Chinese natural language question set after vectorization mapping. , .

[0055] In one embodiment of the present invention, step S5 comprises the following steps:

[0056] S5-1. Calculate the Chinese natural language problem after vectorization mapping The Chinese description statement set after vectorization mapping middle The cosine similarity of each of the Chinese description sentences after vectorization mapping is calculated, and the maximum cosine similarity value corresponding to the first Chinese description sentences As the Chinese natural language problem after vectorization mapping The most similar vectorized Chinese description sentence, , after vectorized mapping Chinese description sentences The corresponding Chinese description sentences As the most similar Chinese description sentence.

[0057] S5-2. Calculate the Chinese natural language problem after vectorization mapping With Chinese description sentences DDL table creation statement set after vectorized mapping middle The cosine similarity of the table building statements after vectorization mapping is Sort the cosine similarity values ​​in descending order, and take the table creation statements corresponding to the vectorized mapping of the first 10 cosine similarity values ​​after sorting. , according to the table creation statements corresponding to each vectorized mapped table creation statement As the top 10 most similar table creation statements.

[0058] S5-3. Calculate the Chinese natural language problem after vectorization mapping With sql-question question and answer middle The cosine similarity of the Chinese natural language questions after vectorization mapping is The cosine similarity values ​​are sorted in descending order, and the first 10 cosine similarity values ​​after sorting are taken to correspond to the vectorized mapped Chinese natural language questions. , according to the Chinese natural language questions corresponding to each vectorized natural language question As the first top 10 Chinese natural language problem.

[0059] In one embodiment of the present invention, in step S6, the formula

[0060] Calculate the similarity , where and All are parameters. , For the The term in Chinese natural language problem The word frequency in , For the Chinese natural language problem The total number of distinct terms in , For the sql-question question and answer Chinese natural language problem The number of words, For the The term is the first in the sql-question Chinese natural language problem The word frequency in For sql-question question and answer The average length of Chinese natural language questions;

[0061] Will The similarity values ​​are sorted in descending order, and the Chinese natural language questions corresponding to the first 10 similarity values ​​after sorting are taken. As the second top 10 Chinese natural language problem.

[0062] In one embodiment of the present invention, in step S7, the Reciprocal Rank Fusion algorithm is used to perform two-way fusion recall on the first top 10 Chinese natural language questions and the second top 10 Chinese natural language questions to obtain the third top 10 Chinese natural language questions. .

[0063] In one embodiment of the present invention, step S8 includes the following steps:

[0064] S8-1. Input the third top 10 Chinese natural language questions into the bge-reranker model for reranking, and output the reranked top 10 Chinese natural language questions The bge-reranker model is used to further optimize the ranking of search results to improve the relevance of search results. Reranked top 10 Chinese natural language questions Improve its matching degree with user query semantics, thereby optimizing the ranking results.

[0065] S8-2. Top 10 Chinese natural language questions based on re-ranking Corresponding top10sql-question question and answer pairs .

[0066] S8-3. Top10sql-question questions and answers , No. Chinese natural language problem , the top 10 most similar table creation statements Perform a concatenation operation, input the concatenation result into the LLM large language model, and output the database SQL query statement.

[0067] Finally, it should be noted that the above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art can still modify the technical solutions described in the aforementioned embodiments or replace some of the technical features therein by equivalents. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A method for automatically generating SQL statements from Chinese natural language, characterized in that: include: S1. Get the Chinese description statement set of the database , , For the Chinese description sentences, , is the total number of Chinese description sentences in the database, Chinese description sentences The corresponding database DDL table creation statement set is , , For the A table creation statement, , For the Chinese description sentences The total number of table creation statements in the corresponding database, Chinese description sentences The corresponding database sql-question question-answer pair set , , For the sql-question question and answer pair, , For the Chinese description sentences The total number of sql-question question-answer pairs corresponding to the database; S2. Create table statement set based on DDL Get the DDL table creation statement set after vectorized mapping , according to the Chinese description statement set of the database Get the Chinese description statement set after vectorized mapping ; S3. Based on the sql-question question-answer pair set of the database Get the sql-question question-answer pair set after vectorized mapping ; S4. Acquisition Chinese natural language questions, and get the Chinese natural language question set , , For the Chinese natural language problem, , is the total number of Chinese natural language questions, based on the Chinese natural language question set Get the Chinese natural language question set after vectorization mapping ; S5. Get the same as Chinese natural language problem after vectorization mapping The most similar Chinese description sentence is obtained according to the most similar Chinese description sentence. Chinese natural language problem after vectorization mapping The top 10 most similar table creation statements and Chinese natural language problem after vectorization mapping The top 10 most similar Chinese natural language questions; S6. Calculate the Chinese natural language problem With sql-question Similarity of Chinese natural language questions in , according to the similarity Get with Chinese natural language problem The second most similar top 10 Chinese natural language questions; S7. Perform two-way fusion recall using the first top 10 Chinese natural language questions and the second top 10 Chinese natural language questions to obtain the third top 10 Chinese natural language questions; S8. Reorder the third top 10 Chinese natural language questions to obtain reordered top 10 Chinese natural language questions, and use the reordered top 10 Chinese natural language questions to obtain database SQL query statements using a large language model.

2. The method for automatically generating SQL statements from Chinese natural language according to claim 1, characterized in that: In step S1, each table creation statement has its corresponding Chinese description, and each field in each table creation statement has its corresponding Chinese description.

3. The method for automatically generating SQL statements from Chinese natural language according to claim 1, characterized in that: The set of SQL statements in the SQL-question question-answer pair of the database in step S1 is , , For the sql-question question and answer sql statements, the Chinese natural language question set in the sql-question question-answer pair of the database is , , For the sql-question question and answer Chinese natural language problem, .

4. The method for automatically generating SQL statements from Chinese natural language according to claim 3, characterized in that: Step S2 includes the following steps: S2-1. Use the embedding method to embed Create table statement Perform vectorized mapping to obtain the first Create table statement ,all The vectorized mapped table creation statements constitute the vectorized mapped DDL table creation statement set , , , for dimensional real number space; S2-2. Use the embedding method to embed Chinese description sentences Perform vectorized mapping to obtain the first Chinese description sentences ,all The Chinese description statements after vectorization mapping constitute the Chinese description statement set after vectorization mapping , , .

5. The method for automatically generating SQL statements from Chinese natural language according to claim 4, characterized in that: In step S3, the embedding method is used to embed the first Chinese natural language problem Perform vectorized mapping to obtain the first Chinese natural language problem , the sql-question question-answer pair after vectorization mapping is , ,all The vectorized mapped sql-question question-answer pairs constitute the vectorized mapped sql-question question-answer pair set , .

6. The method for automatically generating SQL statements from Chinese natural language according to claim 1, characterized in that: In step S4, the embedding method is used to embed Chinese natural language problem Perform vectorized mapping to obtain the first Chinese natural language problem ,all The Chinese natural language questions after vectorization mapping constitute the Chinese natural language question set after vectorization mapping. , .

7. The method for automatically generating SQL statements from Chinese natural language according to claim 5, characterized in that: Step S5 includes the following steps: S5-1. Calculate the Chinese natural language problem after vectorization mapping The Chinese description statement set after vectorization mapping middle The cosine similarity of each of the Chinese description sentences after vectorization mapping is calculated, and the maximum cosine similarity value is the first Chinese description sentences As the Chinese natural language problem after vectorization mapping The most similar vectorized Chinese description sentence, , after vectorized mapping Chinese description sentences The corresponding Chinese description sentences As the most similar Chinese description sentence; S5-2. Calculate the Chinese natural language problem after vectorization mapping With Chinese description sentences DDL table creation statement set after vectorized mapping middle The cosine similarity of the table building statements after vectorization mapping is Sort the cosine similarity values ​​in descending order, and take the table creation statements corresponding to the vectorized mapping of the first 10 cosine similarity values ​​after sorting. , according to the table creation statements corresponding to each vectorized mapped table creation statement As the top 10 most similar table creation statements; S5-3. Calculate the Chinese natural language problem after vectorization mapping With sql-question question and answer middle The cosine similarity of the Chinese natural language questions after vectorization mapping is The cosine similarity values ​​are sorted in descending order, and the first 10 cosine similarity values ​​after sorting are taken to correspond to the vectorized mapped Chinese natural language questions. , according to the Chinese natural language questions corresponding to each vectorized natural language question As the first top 10 Chinese natural language problem.

8. The method for automatically generating SQL statements from Chinese natural language according to claim 7, characterized in that: In step S6, the formula Calculate the similarity , where and All are parameters. , For the The term in Chinese natural language problem The word frequency in , For the Chinese natural language problem The total number of distinct terms in , For the sql-question question and answer Chinese natural language problem The number of words, For the The term is the first in the sql-question Chinese natural language problem The word frequency in For sql-question question and answer The average length of Chinese natural language questions; Will The similarity values ​​are sorted in descending order, and the Chinese natural language questions corresponding to the first 10 similarity values ​​after sorting are taken. As the second top 10 Chinese natural language problem.

9. The method for automatically generating SQL statements from Chinese natural language according to claim 1, characterized in that: In step S7, the Reciprocal Rank Fusion algorithm is used to perform two-way fusion recall on the first top 10 Chinese natural language questions and the second top 10 Chinese natural language questions to obtain the third top 10 Chinese natural language questions. .

10. The method for automatically generating SQL statements from Chinese natural language according to claim 9, characterized in that: Step S8 includes the following steps: S8-1. Input the third top 10 Chinese natural language questions into the bge-reranker model for reranking, and output the reranked top 10 Chinese natural language questions ; S8-2. Top 10 Chinese natural language questions based on re-ranking Corresponding top10sql-question question and answer pairs ; S8-3. Top10sql-question questions and answers , No. Chinese natural language problem , the top 10 most similar table creation statements Perform a concatenation operation, input the concatenation result into the LLM large language model, and output the database SQL query statement.

Images