Method and system for automatically generating SQL (Structured Query Language) in power field based on retrieval enhancement generation

Abstract

The invention discloses a power field SQL automatic generation method and system based on retrieval enhancement generation, and relates to the technical field of data processing. The problem that a traditional SQL automatic generation method is low in data processing efficiency and accuracy is solved. The method comprises the following steps of: firstly, coding a question input by a user into a vector, and matching k keys with highest similarity ranking and description information in a pre-coded vector knowledge base; and generating instruction information based on the k keys with the highest ranking and the description information corresponding to the keys, and generating an accurate SQL query statement by the SQL generation model according to the instruction information. The method is mainly applied to converting the natural language problem into the SQL query statement in the electric power field.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing. Background Art

[0002] At present, in the power sector, with the rapid increase of power system data, traditional SQL automatic generation methods are difficult to efficiently cope with complex data requirements and frequent data structure changes. These methods usually rely on manual query writing, which is not only inefficient but also prone to errors. Although large models have demonstrated powerful capabilities in natural language processing, they often have limitations in accurately converting natural language into SQL queries, making it difficult to meet the highly complex and dynamically changing query requirements in the power sector.

[0003] Therefore, a new method and system are needed to optimize the SQL automatic generation process in the power field by using the Retrieval-Augmented Generation (RAG) technology, so as to improve the efficiency and accuracy of data processing. Summary of the invention

[0004] The purpose of the present invention is to solve the problem of low efficiency and accuracy of data processing in traditional SQL automatic generation methods; the present invention provides a method and system for automatically generating SQL in the power field based on retrieval enhanced generation.

[0005] The method for automatically generating SQL in the power field based on retrieval enhancement generation includes the following steps:

[0006] Get natural language questions input by users;

[0007] Use the pre-trained encoding model to extract features from the natural language questions input by the user and encode them into query vectors;

[0008] Based on the query vector, the k keys with the highest similarity ranking and the description information corresponding to the keys are retrieved from a pre-encoded vector knowledge base, wherein the vector knowledge base is implemented based on description data of multiple databases in the power field;

[0009] Generate instruction information based on k keys and description information corresponding to the keys, and generate corresponding SQL query statements according to the instruction information;

[0010] Display the SQL query statement to the user.

[0011] Preferably, the step of constructing a pre-encoded vector knowledge base comprises the following steps:

[0012] Collect database description data in the power sector, pre-process the database description data to remove redundant information and noise, and establish indexes for fields in database tables and table structures;

[0013] After extracting features from each database table and each field through the pre-trained encoding model, the features are encoded into target feature vectors;

[0014] Integrate all target feature vectors to generate a knowledge base in vector representation;

[0015] The database description data includes database tables and table structures.

[0016] Preferably, the implementation method of retrieving the k keys with the highest similarity ranking and the description information corresponding to the keys in the pre-encoded vector knowledge base includes:

[0017] Based on the query vector, a hybrid search strategy is used to search in the vector knowledge base to obtain a plurality of search results; each search result is a target feature vector in the vector knowledge base;

[0018] Calculate the similarity score between the query vector and each search result, and sort them in descending order;

[0019] Determine the most relevant k keys in the database table corresponding to the M search results with the highest ranking, and use the most relevant k keys and the description information corresponding to the keys as the key information of the query vector;

[0020] The k most relevant keys are the k keys with the highest weight ranking in the database table corresponding to the M search results with the highest ranking.

[0021] Preferably, the implementation of generating the instruction information includes:

[0022] The natural language question input by the user, the k keys with the highest similarity ranking and the description information corresponding to the keys are input into the prompt template to generate instruction information.

[0023] Preferably, generating a corresponding SQL query statement according to the instruction information is implemented through an SQL generation model.

[0024] Preferably, the generating system comprises:

[0025] A question acquisition module is used to obtain natural language questions input by users;

[0026] A model encoding module is used to extract features of natural language questions input by users using a pre-trained encoding model and encode them into query vectors;

[0027] A resource search module is used to retrieve the k keys with the highest similarity ranking and the description information corresponding to the keys in a pre-coded vector knowledge base based on the query vector; the vector knowledge base is implemented based on the description data of multiple databases in the power field;

[0028] An instruction generation module, used for generating instruction information based on k keys and description information corresponding to the keys;

[0029] The task execution module is used to generate corresponding SQL query statements according to the instruction information;

[0030] The answer display module is used to display the SQL query statement to the user.

[0031] Preferably, the step of constructing a pre-encoded vector knowledge base comprises the following steps:

[0032] Collect database description data in the power sector, pre-process the database description data to remove redundant information and noise, and establish indexes for fields in database tables and table structures;

[0033] After extracting features from each database table and each field through the pre-trained encoding model, the features are encoded into target feature vectors;

[0034] Integrate all target feature vectors to generate a knowledge base in vector representation;

[0035] The database description data includes database tables and table structures.

[0036] Preferably, the implementation method of retrieving the k keys with the highest similarity ranking and the description information corresponding to the keys in the pre-encoded vector knowledge base includes:

[0037] Based on the query vector, a hybrid search strategy is used to search in the vector knowledge base to obtain a plurality of search results; each search result is a target feature vector in the vector knowledge base;

[0038] Calculate the similarity score between the query vector and each search result, and sort them in descending order;

[0039] Determine the most relevant k keys in the database table corresponding to the M search results with the highest ranking, and use the most relevant k keys and the description information corresponding to the keys as the key information of the query vector;

[0040] The k most relevant keys are the k keys with the highest weight ranking in the database table corresponding to the M search results with the highest ranking.

[0041] Preferably, the implementation of generating the instruction information includes:

[0042] The natural language question input by the user, the k keys with the highest similarity ranking and the description information corresponding to the keys are input into the prompt template to generate instruction information.

[0043] Preferably, the task execution module is implemented using an SQL generation model.

[0044] The beneficial effects brought by the present invention are:

[0045] The SQL automatic generation model based on retrieval-enhanced generation of the present invention can extract relevant information from large-scale data of the power system and generate accurate SQL query statements by combining retrieval and generation mechanisms. Specifically: First, the question input by the user is encoded as a vector, and the k keys with the highest similarity ranking and the description information corresponding to the keys are matched in the pre-encoded vector knowledge base. Then, instruction information is generated based on the k keys with the highest ranking and the description information corresponding to the keys, and the SQL generation model further generates accurate SQL query statements based on the instruction information.

[0046] The present invention, by introducing retrieval enhancement generation technology (i.e., combining language model and information retrieval technology), can accurately parse natural language questions input by users and automatically generate corresponding structured query statements, greatly simplifying the processing flow of complex queries. The present invention not only improves the accuracy of query generation, but also meets the needs of the power sector for complex queries, and provides an efficient and intelligent solution for data query and processing in the power sector.

[0047] The present invention is mainly used for generating SQL query statements for electric quantity data in the electric power field. BRIEF DESCRIPTION OF THE DRAWINGS

[0048] Figure 1 It is a flow chart of the method for automatically generating SQL in the power field based on retrieval enhanced generation according to the present invention;

[0049] Figure 2 This is a principle block diagram of the automatic SQL generation system in the power field based on retrieval enhanced generation described in the present invention. DETAILED DESCRIPTION

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

[0051] It should be noted that, in the absence of conflict, the embodiments of the present invention and the features in the embodiments may be combined with each other.

[0052] In the power sector, with the continuous expansion of data volume and the increasing complexity of query requirements in the power industry, traditional SQL automatic generation methods face challenges such as low efficiency and accuracy and inability to meet dynamic changes. These traditional methods usually rely on manual query writing, which is difficult to quickly adapt to complex query requirements and prone to errors.

[0053] In view of this, this application proposes a method and system for automatic SQL generation in the power field based on retrieval enhancement generation (RAG). By introducing retrieval enhancement generation (RAG) technology, the efficiency of power data query is significantly improved. The system achieves fast and accurate data query through efficient retrieval and reordering strategies, which is suitable for complex power data processing scenarios and provides users with intelligent SQL generation solutions.

[0054] Specific implementation method 1: See Figure 1 This embodiment describes the method for automatically generating SQL in the power field based on search enhancement generation, which includes the following steps:

[0055] Step S1, obtaining a natural language question input by a user;

[0056] Step S2: extract features of the natural language question input by the user using a pre-trained encoding model and encode it into a query vector;

[0057] As a specific example, for example, the question input by the user "How much electricity does user No. 6009638207 use every month in 2023, and what is the corresponding electricity fee?" will be encoded as a vector V q .

[0058] V q =Encode("Check how much electricity the user with account number 6009638207 uses each month in 2023, and what is the corresponding electricity fee?")

[0059] Step S3, based on the query vector, searching the k keys with the highest similarity ranking and the description information corresponding to the keys in a pre-encoded vector knowledge base, wherein the vector knowledge base is implemented based on description data of multiple databases in the electric power field;

[0060] Specifically, the construction of the pre-encoded vector knowledge base includes the following steps:

[0061] Collect database description data in the power sector, pre-process the database description data to remove redundant information and noise, and establish indexes for fields in database tables and table structures;

[0062] After extracting features from each database table and each field through the pre-trained encoding model, the features are encoded into target feature vectors;

[0063] Integrate all target feature vectors to generate a knowledge base in vector representation;

[0064] The database description data includes database tables and table structures.

[0065] Furthermore, the implementation method of retrieving the k keys with the highest similarity ranking and the description information corresponding to the keys in the pre-encoded vector knowledge base includes:

[0066] The first stage: Based on the query vector, a hybrid search strategy is used to search in the vector knowledge base to obtain multiple search results; each search result is a target feature vector in the vector knowledge base;

[0067] Specifically, the hybrid search strategy uses the BM25 search strategy. BM25 (Best Matching 25) is an information retrieval function based on term frequency-inverse document frequency (TF-IDF). Its calculation formula is as follows:

[0068]

[0069] Where Q is the user query vector (i.e., the user query question is expressed in vector form), D is the document vector, i.e., each document in the pre-encoded vector knowledge base, where "document" refers to the table structure in the database, including table name, field name and field type, etc. This information is converted into a feature vector representation for text processing; q i is the i-th word in the user's query; f(q i ,D) is word q i The frequency of words in document D; |D| is the length of document D (i.e., the total number of words in the document); avgdl is the average length of all documents in the database; k1 and b are adjustment parameters, usually k1 = 1.2 and b = 0.75; IDF(q i ) is the word q i Inverse document frequency, which is calculated as follows:

[0070]

[0071] Where N is the total number of documents in the library (i.e., vector knowledge base); n(q i ) contains the word q i The number of documents;

[0072] The second stage is reranking, which calculates the similarity score between the query vector and each search result, and sorts them in descending order.

[0073] Based on the data provided in the first stage, a token-level similarity score is calculated between the query and the document.

[0074] njk=GoldenSQLs(n,j,k) (3);

[0075] lk=QueryEmbedding(l,k) (4);

[0076] GoldenSQLs(n,j,k) represents the embedding vector information of each token in each sentence in the document; n represents the first dimension of the matrix, corresponding to the number of sentences in the document; j represents the second dimension of the matrix, corresponding to the number of tokens in each sentence; k represents the third dimension of the matrix, corresponding to the dimension of the embedding vector of each token.

[0077] QueryEmbedding(l,k) represents the embedding vector of each token in the query. l represents the first dimension of the matrix, corresponding to the number of tokens in the query; k represents the second dimension of the matrix, corresponding to the dimension of the embedding vector of each token.

[0078] The calculation formula is as follows:

[0079] nlj=njk·lk (5);

[0080] The calculation results in a three-dimensional matrix nlj, where nlj represents the similarity score between each token in the query and each token in the document, i.e., a token similarity score matrix. The n dimension represents the nth sentence in the document, and the lj dimension represents the dot product result of the nth token in the query and the jth token in the sentence, i.e., there are a total of n token similarity score matrices.

[0081]

[0082] max_score nl represents the highest similarity score among the nth sentence in the query;

[0083] The calculation formula of the gold score list is implemented through cosine similarity, and the calculation formula is as follows:

[0084]

[0085] Among them, gold scores Represents a list of cosine similarity scores between all documents and the query vector, each score represents the similarity score of a document. Q·D represents the dot product of the query vector Q and the document vector D; ‖Q‖ represents the norm of the query vector Q (i.e., the length of the vector); ‖D‖ represents the norm of the document vector D (i.e., the length of the vector). For each document D u Calculate the similarity score:

[0086]

[0087] After calculating the similarity scores of all documents, we get the gold score list:

[0088] gold scores =[similarity_scores1,similarity_scores2,…,similarity_scores x ] (9); Take the top M similarity scores as the final retrieval results, M<x.

[0089] The third stage is to determine the most relevant k keys in the database table corresponding to the M search results with the highest ranking, and use the most relevant k keys and the description information corresponding to the keys as the key information of the query vector;

[0090] The k most relevant keys are the k keys with the highest weight ranking in the database table corresponding to the M search results with the highest ranking.

[0091] Step S4, generate instruction information based on k keys and their corresponding description information; in specific application, fill in the pre-designed instruction information (prompt) template based on the user's natural language question and the most relevant k keys and their corresponding description information corresponding to the top M search results in the gold score list. For example: "f" user question:

[0092] {user_query}\n\nPlease generate the following SQL statement:\nSELECT{fields}FROM{table}WHERE{conditions}; The meaning of the above SQL statement is: select {field} from {table} to {condition};

[0093] {user_query}\n\n, user_query is the user query, and \n is a line break character.

[0094] Step S5: Generate a corresponding SQL query statement according to the instruction information; specifically, generating the corresponding SQL query statement can be implemented by using an SQL generation model in the prior art.

[0095] Step S6: Display the SQL query statement to the user.

[0096] In this embodiment, by introducing retrieval enhancement generation technology (i.e., combining language model and information retrieval technology), the efficiency of data query in the power field is significantly improved. First, the method of the present invention can accurately parse the natural language questions input by the user, and automatically generate corresponding structured query statements, which greatly simplifies the processing flow of complex queries. Secondly, by using the pre-trained coding model and the efficient BM25 algorithm, the system can quickly retrieve the most relevant information from the huge power data, and ensure the accuracy and relevance of the returned results through re-ranking technology. Finally, the present invention improves the efficiency and accuracy of data query in the power field, adapts to the complex and changeable power data processing needs, and provides a set of efficient and intelligent solutions for the power field.

[0097] Specific implementation method II, see Figure 2 To describe this embodiment, the power field SQL automatic generation system based on search enhancement generation described in this embodiment, the generation system 100 includes:

[0098] The question acquisition module 110 is used to acquire the natural language question input by the user; the user can input the natural language question through the system interface, and the system captures it and transmits it to the subsequent modules.

[0099] The model encoding module 120 is used to use a pre-trained encoding model to extract features from the natural language questions input by the user and encode them into query vectors; this module uses natural language processing technology to convert natural language questions into machine-processable target feature vector representations.

[0100] The resource search module 130 is used to retrieve the k keys with the highest similarity ranking and the description information corresponding to the keys in a pre-encoded vector knowledge base based on the query vector; the vector knowledge base is implemented based on the description data of multiple databases in the power field; it covers the relevant tables, fields and descriptions of various databases in the power field.

[0101] Furthermore, the construction of the pre-encoded vector knowledge base comprises the following steps:

[0102] Collect database description data in the power sector, pre-process the database description data to remove redundant information and noise, and establish indexes for fields in database tables and table structures;

[0103] After extracting features from each database table and each field through the pre-trained encoding model, the features are encoded into target feature vectors;

[0104] Integrate all target feature vectors to generate a knowledge base in vector representation;

[0105] The database description data includes database tables and table structures.

[0106] Furthermore, the implementation method of retrieving the k keys with the highest similarity ranking and the description information corresponding to the keys in the pre-encoded vector knowledge base includes:

[0107] Based on the query vector, a hybrid search strategy is used to search in the vector knowledge base to obtain multiple search results; each search result is a target feature vector in the vector knowledge base; in specific applications, the hybrid search strategy uses the BM25 search strategy.

[0108] Calculate the similarity score between the query vector and each search result, and sort them in descending order;

[0109] Determine the most relevant k keys in the database table corresponding to the M search results with the highest ranking, and use the most relevant k keys and the description information corresponding to the keys as the key information of the query vector;

[0110] The k most relevant keys are the k keys with the highest weight ranking in the database table corresponding to the M search results with the highest ranking.

[0111] An instruction generation module 140, configured to generate instruction information based on k keys and description information corresponding to the keys;

[0112] Specifically, the implementation method of generating instruction information includes:

[0113] The natural language question input by the user, the k keys with the highest similarity ranking and the description information corresponding to the keys are input into the prompt template to generate instruction information.

[0114] The task execution module 150 is used to generate a corresponding SQL query statement according to the instruction information; specifically, the task execution module is implemented using an SQL generation model in the prior art.

[0115] The answer display module 160 is used to display the SQL query statement to the user.

[0116] Although the present invention is described herein with reference to specific embodiments, it should be understood that these embodiments are merely examples of the principles and applications of the present invention. It should therefore be understood that many modifications may be made to the exemplary embodiments and that other arrangements may be devised without departing from the spirit and scope of the present invention as defined by the appended claims. It should be understood that the various dependent claims and features described herein may be combined in a manner different from that described in the original claims. It should also be understood that features described in conjunction with individual embodiments may be used in other described embodiments.

Claims

1. The method for automatically generating SQL in the power field based on retrieval enhancement generation is characterized by: The method comprises the following steps: Get natural language questions input by users; Use the pre-trained encoding model to extract features from the natural language questions input by the user and encode them into query vectors; Based on the query vector, the k keys with the highest similarity ranking and the description information corresponding to the keys are retrieved from a pre-encoded vector knowledge base, wherein the vector knowledge base is implemented based on description data of multiple databases in the power field; Generate instruction information based on k keys and description information corresponding to the keys, and generate corresponding SQL query statements according to the instruction information; Display the SQL query statement to the user.

2. The method for automatically generating SQL in the power field based on search enhancement generation according to claim 1 is characterized in that: The construction of the pre-encoded vector knowledge base comprises the following steps: Collect database description data in the power sector, pre-process the database description data to remove redundant information and noise, and establish indexes for fields in database tables and table structures; After extracting features from each database table and each field through the pre-trained encoding model, the features are encoded into target feature vectors; Integrate all target feature vectors to generate a knowledge base in vector representation; The database description data includes database tables and table structures.

3. The method for automatically generating SQL in the power field based on search enhancement generation according to claim 1 is characterized in that: The implementation method of retrieving the k keys with the highest similarity ranking and the description information corresponding to the keys in the pre-encoded vector knowledge base includes: Based on the query vector, a hybrid search strategy is used to search in the vector knowledge base to obtain a plurality of search results; each search result is a target feature vector in the vector knowledge base; Calculate the similarity score between the query vector and each search result, and sort them in descending order; Determine the most relevant k keys in the database table corresponding to the M search results with the highest ranking, and use the most relevant k keys and the description information corresponding to the keys as the key information of the query vector; The k most relevant keys are the k keys with the highest weight ranking in the database table corresponding to the M search results with the highest ranking.

4. The method for automatically generating SQL in the power field based on search enhancement generation according to claim 1 is characterized in that: The implementation methods of generating instruction information include: The natural language question input by the user, the k keys with the highest similarity ranking and the description information corresponding to the keys are input into the prompt template to generate instruction information.

5. The method for automatically generating SQL in the power field based on search enhancement generation according to claim 1 is characterized in that: The corresponding SQL query statement is generated according to the instruction information through the SQL generation model.

6. The automatic generation system of SQL in the power field based on retrieval enhancement generation is characterized by: The build system includes: A question acquisition module is used to obtain natural language questions input by users; A model encoding module is used to extract features of natural language questions input by users using a pre-trained encoding model and encode them into query vectors; A resource search module is used to retrieve the k keys with the highest similarity ranking and the description information corresponding to the keys in a pre-coded vector knowledge base based on the query vector; the vector knowledge base is implemented based on the description data of multiple databases in the power field; An instruction generation module, used for generating instruction information based on k keys and description information corresponding to the keys; The task execution module is used to generate corresponding SQL query statements according to the instruction information; The answer display module is used to display the SQL query statement to the user.

7. The automatic generation system of SQL in the power field based on search enhancement generation according to claim 6 is characterized in that: The construction of the pre-encoded vector knowledge base comprises the following steps: Collect database description data in the power sector, pre-process the database description data to remove redundant information and noise, and establish indexes for fields in database tables and table structures; After extracting features from each database table and each field through the pre-trained encoding model, the features are encoded into target feature vectors; Integrate all target feature vectors to generate a knowledge base in vector representation; The database description data includes database tables and table structures.

8. The automatic generation system of SQL in the power field based on search enhancement generation according to claim 6 is characterized in that: The implementation method of retrieving the k keys with the highest similarity ranking and the description information corresponding to the keys in the pre-encoded vector knowledge base includes: Based on the query vector, a hybrid search strategy is used to search in the vector knowledge base to obtain a plurality of search results; each search result is a target feature vector in the vector knowledge base; Calculate the similarity score between the query vector and each search result, and sort them in descending order; Determine the most relevant k keys in the database table corresponding to the M search results with the highest ranking, and use the most relevant k keys and the description information corresponding to the keys as the key information of the query vector; The k most relevant keys are the k keys with the highest weight ranking in the database table corresponding to the M search results with the highest ranking.

9. The automatic generation system of SQL in the power field based on search enhancement generation according to claim 6 is characterized in that: The implementation methods of generating instruction information include: The natural language question input by the user, the k keys with the highest similarity ranking and the description information corresponding to the keys are input into the prompt template to generate instruction information.

10. The automatic generation system of SQL in the power field based on search enhancement generation according to claim 6 is characterized in that: The task execution module is implemented using the SQL generation model.

Images