Natural language query method and system for relational database

Abstract

The application discloses a natural language query method and system for a relational database, and the method comprises the following steps: extracting a table column structure from a local connection database to generate a sub-database based on a natural language question; inputting the sub-database mode and the natural language question into a screening prompt word template to obtain complete prompt words, inputting the complete prompt words into a large language model to obtain a table name and a column name, and then generating a simplified database mode; inputting the natural language question into a prediction prompt word template to obtain a predicted SQL keyword skeleton, converting the predicted SQL keyword skeleton into a prediction vector, screening out an example database, extracting a question and answer pair in the example database, extracting a keyword skeleton of an SQL statement of the question and answer pair, and converting the keyword skeleton into a question and answer pair vector; calculating a similarity to obtain a similarity result and a corresponding prediction vector; inputting the above content into a prompt word template, inputting prompt words into the large language model, and generating an SQL statement by the large language model by using a thinking chain to complete natural language query.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of databases and natural language processing, and particularly relates to a natural language query method and system for a relational database. BACKGROUND

[0002] Natural language query aims to convert natural language into structured SQL statements and is widely used in intelligent data analysis, voice assistants, automated report generation, and other scenarios. Its implementation process integrates multiple technical fields such as natural language processing and databases, aiming to enable users to query complex database information through simple language.

[0003] Natural language understanding technology: The core of natural language query is to deeply understand the natural language input by the user and extract the query intent. This process involves multiple technologies, including text preprocessing, entity recognition, etc. Through these technologies, the system can identify keywords, table names, field names, and various relationship information in natural language.

[0004] Semantic mapping and reasoning: The task of semantic mapping technology is to map the abstract query in natural language to the actual structure of the database. This process is not just a matter of matching words, but also involves deep reasoning about the query intent. For example, the system needs to understand that "get the names of all employees with an age greater than 30" is not only a query about the "employees" table, but also needs to clarify the meanings of the fields "age" and "name", and generate appropriate SQL statements according to the structure of the database.

[0005] Although natural language to SQL technology has made significant progress, there are still many challenges in practical application. The processing of complex queries, the resolution of semantic ambiguity, and the adaptation of database structure are still difficult points of research. In particular, for some unconventional expressions of queries, existing models often have difficulty accurately generating SQL statements that meet expectations. SUMMARY

[0006] The present application provides a natural language query method and system for a relational database, the method comprising:

[0007] Step S1. Obtain external knowledge description and user's natural language question;

[0008] Step S2. Extract table column structure from local connected database according to user selection, generate sub-database based on natural language question;

[0009] Step S3. Construct screening prompt word template, input sub-database schema and natural language question into screening prompt word template to obtain complete prompt word, input complete prompt word into large language model to obtain table name and column name used for this natural language question, and generate simplified database schema according to table name and column name.

[0010] Step S4. Construct a prediction prompt word template, put the natural language question into the prediction prompt word template, and obtain the predicted SQL keyword skeleton;

[0011] Step S5. Convert the SQL keyword skeleton into a prediction vector, select a preset number of example databases from the local connection database, extract question-answer pairs from the example databases, convert the SQL statements of the question-answer pairs into example SQL vectors, and store them in the vector database;

[0012] Step S6. Calculate the similarity between the predicted vector and the example SQL vectors in the vector database, obtain a preset number of similarity results and corresponding example SQL vectors, and use the question-answer pairs corresponding to the SQL vectors as examples;

[0013] Step S7. Obtain a complete prompt word from the simplified database model, natural language question, external knowledge description and the example input prompt word template, input the prompt word into the large language model, and the large language model uses the thinking chain to generate SQL statements to complete the natural language query.

[0014] Optionally, in step S2, extracting a table column structure from the local connection database according to the user's selection to generate the content of the sub-database based on the natural language question specifically includes:

[0015] Obtain all schemas in a locally connected database, where the database schema is structural information of the database;

[0016] Filter tables and columns across all database schemas based on user selections;

[0017] A sub-database is generated based on the screening results.

[0018] Optionally, in step S3, the process of generating a simplified database schema according to the table name and column name specifically includes:

[0019] Determine whether the table name and column name exist in the sub-database schema, and whether the nested relationship between the table and column conforms to the sub-database schema. If both are true, a simplified database schema is generated.

[0020] Optionally, in step S4, the predicted SQL keyword skeleton is a character string consisting of keywords and symbols in the SQL grammar except for custom content.

[0021] The present invention discloses a natural language query system for a relational database, the system comprising:

[0022] Data acquisition module, used to obtain external knowledge descriptions and users' natural language questions;

[0023] A sub-database generation module is used to extract table column structures from the local connection database according to user selection and generate a sub-database based on natural language questions;

[0024] A simplified schema extraction module is used to construct a screening prompt word template, input the sub-database schema and the natural language question into the screening prompt word template to obtain a complete prompt word, input the complete prompt word into the large language model, obtain the table name and column name used for this natural language question, and generate a simplified database schema based on the table name and column name;

[0025] The skeleton prediction module is used to build a prediction prompt word template, put the natural language question into the prediction prompt word template, and obtain the predicted SQL keyword skeleton;

[0026] A vector conversion module is used to convert the SQL keyword skeleton into a prediction vector, filter a preset number of example databases from the local connection database, extract question-answer pairs from the example databases, convert the SQL statements of the question-answer pairs into example SQL vectors, and store them in the vector database;

[0027] A similarity calculation module is used to calculate the similarity between the predicted vector and the example SQL vectors in the vector database, obtain a preset number of similarity results and corresponding example SQL vectors, and use the question-answer pairs corresponding to the SQL vectors as examples;

[0028] The statement generation module is used to obtain a complete prompt word from the simplified database model, natural language question, external knowledge description and the example input prompt word template, input the prompt word into the large language model, and the large language model uses the thinking chain to generate SQL statements to complete the natural language query.

[0029] Optionally, the workflow of the sub-database generation module specifically includes:

[0030] Obtain all schemas in a locally connected database, where the database schema is structural information of the database;

[0031] Filter tables and columns across all database schemas based on user selections;

[0032] A sub-database is generated based on the screening results.

[0033] Optionally, in the simplified schema extraction module, the process of generating a simplified database schema according to the table name and column name specifically includes:

[0034] Determine whether the table name and column name exist in the sub-database schema, and whether the nested relationship between the table and column conforms to the sub-database schema. If both are true, a simplified database schema is generated.

[0035] Optionally, in the skeleton prediction module, the predicted SQL keyword skeleton is a character string consisting of keywords and symbols in the SQL grammar except for custom content.

[0036] Compared with the prior art, the present invention has the following beneficial effects:

[0037] The present invention reduces the difficulty of model reasoning by establishing a sub-database and pattern screening, enhances the model reasoning ability by finding similar question-answer pair examples and thinking chain methods in vector space, and minimizes the probability of generation errors through pattern verification and result verification methods. BRIEF DESCRIPTION OF THE DRAWINGS

[0038] In order to more clearly illustrate the technical solution of the present invention, the following briefly introduces the drawings required for use in the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0039] Figure 1 This is a method step diagram of a natural language query method for a relational database according to an embodiment of the present invention. DETAILED DESCRIPTION

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

[0041] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, the present invention is further described in detail below with reference to the accompanying drawings and specific embodiments.

[0042] Example 1

[0043] A natural language query method for relational databases, such as Figure 1 As shown, the method includes:

[0044] Step S1. Obtain external knowledge description and user's natural language questions.

[0045] The user inputs the table name and column name to be used later, which preliminarily delimits the data range, inputs the natural language question for subsequent SQL generation, and inputs the external knowledge description to help the large model understand and reason. For example, the user inputs the question: "Please list the names of each student and their average test scores." The selected table columns are: student(student_name, test_score, student_id).

[0046] External knowledge is mainly used to help the large model understand some knowledge in professional fields, such as jargon or small-scale conventional rules, etc. Such knowledge is usually not within the training range of general large models, so additional explanations are needed to help the model reason.

[0047] Step S2. Extract the table column structure from the locally connected database according to the natural language question and external knowledge description, and generate a sub-database based on the natural language question.

[0048] First, get the schema of the locally connected database. Database schema usually refers to the structure information of the database, such as which tables exist in the database, the names of the columns in the table, data types, foreign key relationships, etc. Then filter the structure within the selected range in step one from the schema, and organize it into a new sub-database.

[0049] The representation form of the database schema can be in the form of a table creation statement, for example:

[0050]

[0051] It should be noted that for different schema representation forms, different models have different understanding abilities. For example, the openai model, because it uses the format table(column, column, …) during training, it is more effective to use this format, for example:

[0052] student(student_id,student_name,test_score)

[0053] Step S3. Build a filtering prompt template, input the sub-database schema and the natural language question into the filtering prompt template to get the complete prompt, input the complete prompt into the large language model to get the table name and column name used for this natural language question, and generate a simplified database schema according to the table name and column name.

[0054] Design a screening prompt word template, put the sub-database schema obtained in step 2 and the natural language question input by the user in step 1 into the prompt word template, and submit the complete prompt word to the large model. The large model determines which tables and columns are needed to generate the SQL for this question. Get the table name and column name returned by the model. For example, the table name returned here is: student, and the column name is: student_name, test_score.

[0055] After the model returns the result, verify whether the result appears illusory. The specific verification aspect is whether the returned table name exists in the sub-database schema.

[0056] Whether the column name in the table exists in the sub-database schema, and whether the nesting relationship of the table and the column conforms to the sub-database schema. If the table creation statement format is used, whether the foreign key relationship and data type of the column conform to the sub-database schema.

[0057] If there is no illusion, a simplified database schema is generated. The simplified database schema follows the same format as the previous step. For example, the simplified database schema here is: student(student_name, test_score).

[0058] Step S4. Construct a prediction prompt word template and put the natural language question into the prediction prompt word template to obtain a predicted SQL keyword skeleton.

[0059] Design a prediction prompt word template and put the user's natural language question into the prompt word. Submit the complete prompt word to the large model. The large model predicts the SQL skeleton for this question. The SQL skeleton is a string composed only of keywords and symbols in the SQL syntax, and does not include custom elements such as table names and column names. This operation aims to exclude the interference of personalized data and only focus on the syntax of the SQL corresponding to the current question. For example, for the above example, the prediction here is SELECT#FROM#GROUP BY#(# represents empty).

[0060] Step S5. Convert the SQL keyword skeleton into a prediction vector, select a preset number of example databases from the local connection database, extract the question and answer pairs in the example databases, and store the example SQL vectors converted from the question and answer pairs in the vector database.

[0061] The skeleton is converted into a vector by the native model of the vector database Chorma, and the three most similar examples in the vector space are matched.

[0062] Specifically, the following steps are included: constructing a vector dataset, embedding the SQL skeleton, and retrieving similar examples.

[0063] Step 5-1, first manually screen out the 10 example databases with the most complex patterns in the Spider dataset, select representative question and answer pairs in each database, use the native model of vector database Chroma to convert SQL statements into example SQL vectors, store them in the vector database, and form a vector dataset.

[0064] Step 5-2, put the user's natural language question into the prompt word, use the large model to predict its corresponding SQL skeleton, and use the same method as step 5-1 to embed the skeleton into the vector space.

[0065] The generation process of the large language model is as follows:

[0066] Input representation: Assume the input prompt word sequence is: X = {x1, x2,..., x n}, where x i represents the i-th token in the input sequence. The input token is transformed into a vector representation by the embedding layer (Embedding Layer) and positional encoding (PositionalEncoding):

[0067] E i = Embed(x i ) + PosEnc(i)

[0068] The representation matrix of the input sequence is: E = [E1, E2,..., E n ].

[0069] Self-attention mechanism (Self-Attention): The weight of each token in the input sequence is calculated by the self-attention mechanism. The query (Q), key (K) and value (V) vectors are obtained by linear transformation:

[0070] Q = EW Q , K = EW K , V = EW V ;

[0071] where W Q , W K , WV are trainable parameter matrices. Attention weights are calculated by dot product attention:

[0072]

[0073] where d k is the dimension of the key vector, used to scale the dot product. Multi-head attention mechanism (Multi-HeadAttention): Multi-head attention captures information in different subspaces by parallel computing through multiple attention heads:

[0074] MultiHead(Q, K, V) = Concat(head1,...,head h )W O

[0075] Each attention head is computed as:

[0076] head i = Attention(Q i , K i , V i )

[0077] where W0is the output transformation matrix.

[0078] Feed-Forward Network (FFN): Each token’s attention output is processed through a feed-forward neural network:

[0079] FFN(x) = ReLU(xW1+ b1)W2+ b2

[0080] where W1, W2, b1, b2are trainable parameters.

[0081] Layer Normalization & Residual Connection:

[0082] Add a residual connection and layer normalization at each layer:

[0083] Output = LayerNorm(x + SubLayer(x))

[0084] Decoding and Generation: For the generation task, the decoder uses the previous output tokens as input and generates the probability distribution of the next token:

[0085] P(y t | y ＜t , X) = Softmax(h t W out + b out )

[0086] where h t is the decoder’s hidden state at time step t, W out and b out are output layer parameters.

[0087] Recursive Generation Process: The generation sequence is unfolded in a self-recursive manner:

[0088] y t+1 = argmax((P(yt |y ＜t , X))

[0089] until the end of the generation mark [EOS] is generated.

[0090] Step 5-3, return the first three examples similar to the predicted skeleton in the vector database. As an example of subsequent generation. Here the cosine similarity is used as the similarity index for the judgment.

[0091] In the vector database, the cosine similarity (Cosine Similarity) is a commonly used measurement method to measure the similarity of two vectors in the direction of the vector space. Its value is between -1 and 1, where:

[0092] 1: indicates that the two vectors are exactly the same (consistent in direction).

[0093] 0: indicates that there is no similarity between the two vectors (direction orthogonal).

[0094] -1: indicates that the two vectors are completely opposite in direction.

[0095] The calculation formula of the cosine similarity is as follows:

[0096]

[0097] Where: represents the dot product of the vector and the vector and is the modulus of the vector and , that is:

[0098]

[0099] Step S6. Calculate the similarity of the predicted vector and the example SQL vector in the vector database, get a predetermined number of similarity results and the corresponding example SQL vector, and take the SQL vector corresponding to the question and answer pair as an example.

[0100] Put the simplified database schema, natural language question, external knowledge description and three examples into the generation prompt word. From this embodiment, put the simplified database schema obtained in step three, the user natural language question obtained in step one, the external knowledge description and the three examples obtained in step five into the prompt word template, and submit it to the large model.

[0101] For example, the similar examples here are:

[0102] ​1. {"question":"What is the total profit for each year?", "answer":"SELECT year,SUM(profit)AS profit FROM sales GROUP BY year;"},

[0103] 2. {"question":"Please give the total profit for each year and also provide a summary of the total profit for all years","answer":"SELECT year,SUM(profit)AS profit FROM sales GROUP BY year WITHROLLUP;"},

[0104] 3. {"question":"Please list the total profit for each year, each country, and each product.","answer":"SELECT year,country,product,SUM(profit)AS profit FROM sales GROUP BY year,country,product;"}

[0105] Step S7. Input the simplified database model, natural language question, external knowledge description and example question-answer pair into the prompt word template to obtain a complete prompt word, input the prompt word into the large language model, and the large language model uses the thinking chain to generate SQL statements to complete the natural language query.

[0106] The generated prompt words are given to the big model, and the thinking chain is used in the prompt words to enhance the reasoning ability of the big model and generate the required results.

[0107] Using thought chains in prompts means guiding the model to arrive at an answer through step-by-step reasoning and analysis, given a clear problem or task. Using thought chains can help the model approach complex problems more clearly and systematically, avoiding skipping key steps and generating more accurate and logical results.

[0108] The process of the thinking chain in this method is to first analyze the user problem, then extract pattern information, then make inferences, and finally draw conclusions.

[0109] The large model generates multiple results corresponding to user questions under higher randomness parameters and finds the best solution based on the consensus algorithm.

[0110] The randomness parameter is the model temperature (Temperature), and the choice of temperature should be obtained through repeated testing based on specific circumstances.

[0111] The randomness or determinism of the temperature control model output directly affects the diversity and creativity of the generated content. When the temperature is high, the generated text is more random, and the model will explore more possible outputs. This often leads to more creative or diverse results, but may also contain more uncertainty or contextually inappropriate content.

[0112] The model generates multiple results at a suitable high temperature, and after embedding the results in a vector space, returns the SQL with the highest similarity to the remaining results. For example, multiple results are generated here:

[0113] 1. SELECT student_name, AVG(test_score) FROM student GROUP BY student_name;

[0114] 2. SELECT student_name, test_score FROM student GROUP BY student_name;

[0115] 3. SELECT student_name, AVG(test_score) FROM student GROUP BY student_name ORDER BY student_name;

[0116] The result with the highest similarity is selected as result 1.

[0117] The generated SQL is executed in the database to check for syntax correctness. If correct, the SQL is output; if incorrect, an error prompt is returned to the large model to guide the large model to modify the correct result and output the SQL.

[0118] If the execution fails, error information is recorded, and the information and SQL statement are put into the correction prompt word to guide the large model to modify the SQL. If it still fails after three times, it is directly returned to the large model to regenerate. If it still fails after three times of regeneration, it directly outputs the generation failure information. For example, the final successful SQL generated here is: SELECT student_name, AVG(test_score) FROM student GROUP BY student_name.

[0119] Embodiment Two

[0120] A natural language query system for a relational database, the system comprising:

[0121] a data acquisition module for obtaining external knowledge descriptions and user natural language questions.

[0122] The user enters the table and column names they wish to use later to initially define the data scope, a natural language question for subsequent SQL generation, and external knowledge descriptions to aid the large-scale model's understanding and reasoning. For example, if the user enters the question "Please list each student's name and the average score across all their tests," the selected table columns are student (student_name, test_score, student_id).

[0123] External knowledge is mainly used to help large models understand knowledge in some professional fields, such as jargon or customary rules in a small range. This type of knowledge is usually not within the training scope of general large models, so additional explanations are required to help the model perform reasoning.

[0124] The sub-database generation module is used to extract the table column structure from the local connection database according to the natural language question and the external knowledge description, and generate a sub-database based on the natural language question.

[0125] First, retrieve the entire schema of the locally connected database. The database schema generally refers to the database's structural information, such as the tables, column names, data types, and foreign key relationships. Then, filter out the structure within the selected range in step 1 from the schema and organize it into a new sub-database.

[0126] In this embodiment, the database schema is represented by a table creation statement format, for example:

[0127] CREATE TABLE student(

[0128] student_id int primary key,

[0129] student_name text,

[0130]

[0131] It should be noted that different models have different understanding capabilities for different pattern representations. For example, the OpenAI model uses the table (column, column, ...) format during training, so this format is more effective. For example:

[0132] student(student_id,student_name,test_score).

[0133] The simplified schema extraction module is used to construct a screening prompt word template. The sub-database schema and the natural language question are input into the screening prompt word template to obtain the complete prompt word. The complete prompt word is then input into the large language model to obtain the table and column names used for this natural language question. Based on the table and column names, a simplified database schema is generated. For example, the simplified schema here is: student(test_score, student_name).

[0134] Design a screening prompt template, add the sub-database schema and the natural language question entered by the user to the prompt template, and submit the complete prompt to the main model. The main model determines which tables and columns are required to generate the SQL statement for this question and obtains the table and column names returned by the model. For example, the table name returned here is "student" and the column names are "student_name" and "test_score".

[0135] After the model returns the result, verify whether the result is hallucinated. Specifically, verify whether the returned table name exists in the child database schema.

[0136] Check whether the column names in the table exist in the sub-database schema and whether the nested relationship between the table and columns complies with the sub-database schema. If the table creation statement format is used, check whether the foreign key relationship and data type of the column complies with the sub-database schema.

[0137] If there are no hallucinations, a simplified database schema is generated. The simplified database schema follows the same format as the previous step. For example, the simplified database schema here is: student(student_name,test_score).

[0138] The skeleton prediction module is used to construct a prediction prompt word template. It then places the natural language question into the prediction prompt word template to obtain the predicted SQL keyword skeleton. For example, in the above example, the prediction is SELECT#FROM#GROUP BY# (# represents an empty string).

[0139] After designing a prediction prompt template, place the user's natural language question into the prompt. This complete prompt is then fed into the big model, which predicts the SQL skeleton for the question. The SQL skeleton is a string consisting only of keywords and symbols in SQL syntax, excluding custom elements such as table and column names. This operation eliminates interference from personalized data and focuses solely on the SQL syntax corresponding to the current question.

[0140] A vector conversion module is used to convert the SQL keyword skeleton into a prediction vector, filter out a preset number of example databases from the locally connected database, extract question-answer pairs from the example databases, convert the SQL statements of the question-answer pairs into example SQL vectors, and store them in the vector database; the skeleton is converted into a vector using an embedding model, and the three most similar examples are matched in the vector space.

[0141] Specifically, it is divided into the following steps: constructing a vector dataset, embedding the SQL skeleton, and retrieving similar examples.

[0142] First, we manually screened the 10 sample databases with the most complex patterns in the Spider dataset and selected representative question-answer pairs from each database. We then used the native model of the vector database Chroma to convert SQL statements into sample SQL vectors and stored them in the vector database to form a vector dataset.

[0143] The user's natural language question is placed in the prompt word, and the large model predicts its corresponding SQL skeleton. The skeleton is embedded into the vector space using the same method as before.

[0144] The generation process of the large language model is as follows:

[0145] Input representation: Assume that the input prompt word sequence is: X = {x1, x2, ..., x n}, where x i Represents the i-th token in the input sequence. The input token is transformed into a vector representation through the embedding layer and positional encoding:

[0146] E i =Embed(x i )+PosEnc(i)

[0147] The representation matrix of the input sequence is: E=[E1,E2,...,E n ], i is the token number of the input sequence.

[0148] Self-Attention Mechanism: The self-attention mechanism calculates the weight of each token in the input sequence. The query (Q), key (K), and value (V) vectors are obtained by linear transformation:

[0149] Q=EW Q ,K=EW K ,V=EW V ;

[0150] Among them, W Q ,W K,WV is a trainable parameter matrix. The attention weight is calculated by dot product attention:

[0151]

[0152] Among them, d k Is the dimension of the key vector, used to scale the dot product. Multi-Head Attention Mechanism: Multi-Head Attention captures information from different subspaces through parallel computation of multiple attention heads:

[0153] MultiHead(Q,K,V)=Concat(head1,...,head h )W O

[0154] Each attention head is calculated as:

[0155] head i =Attention(Q i , K i , V i )

[0156] Where W0 is the output transformation matrix.

[0157] Feed-Forward Network (FFN): The attention output of each token is processed through a feed-forward neural network:

[0158] FFN(x)=ReLU(xW1+b1)W2+b2

[0159] Among them, W1, W2, b1, b2 are trainable parameters.

[0160] Layer Normalization & Residual Connection:

[0161] Add residual connections and layer normalization to each layer:

[0162] Output=LayerNorm(x+SubLayer(x))

[0163] Decoding and Generation: For generation tasks, the decoder uses the previous output token as input and generates a probability distribution over the next token:

[0164] P(y t |y ＜t ,X)=Softmax(h t W out +b out )

[0165] Among them, h t is the hidden state of the decoder at time step t, W out and b out are the output layer parameters.

[0166] Recursive generation process: The generated sequence is expanded in an autoregressive manner:

[0167] y t+1 =argmax((P(y t |y ＜t , X))

[0168] Until the end marker [EOS] is generated.

[0169] Return the top three examples in the vector dataset that are similar to the predicted skeleton. This serves as an example for subsequent generation. Here, cosine similarity is used as the similarity metric.

[0170] In vector databases, cosine similarity is a commonly used metric to measure the directional similarity between two vectors in vector space. Its value ranges from -1 to 1, where:

[0171] 1: Indicates that the two vectors are exactly the same (same direction).

[0172] 0: Indicates that there is no similarity between the two vectors (their directions are orthogonal).

[0173] -1: Indicates that the two vectors are in completely opposite directions.

[0174] The calculation formula of cosine similarity is as follows:

[0175]

[0176] in: Representative vector and vector dot product and is a vector and The model is:

[0177]

[0178] A similarity calculation module is used to calculate the similarity between the predicted vector and the example SQL vectors in the vector database, obtain a preset number of similarity results and corresponding example SQL vectors, and use the question-answer pairs corresponding to the SQL vectors as examples; for example, the similar examples here are:

[0179] 1. {"question":"What is the total profit for each year?", "answer":"SELECT year,SUM(profit)AS profit FROM sales GROUP BY year;"},

[0180] 2. {"question":"Please give the total profit for each year and also provide a summary of the total profit for all years","answer":"SELECT year,SUM(profit)AS profit FROM sales GROUP BY year WITHROLLUP;"},

[0181] 3. {"question":"Please list the total profit for each year, each country, and each product","answer":"SELECT year,country,product,SUM(profit)AS profit FROM sales GROUP BY year,country,product;"}.

[0182] The simplified database schema, natural language question, external knowledge description, and three examples are placed into the generated prompt word. Based on this embodiment, the simplified database schema obtained in step 3, the user's natural language question obtained in step 1, the external knowledge description, and the three examples obtained in step 5 are placed into the prompt word template and submitted to the large model.

[0183] The statement generation module is used to input the simplified database model, natural language question, external knowledge description and prediction vector into the prompt word template to obtain a complete prompt word, input the prompt word into the large language model, and the large language model uses the thinking chain to generate SQL statements to complete the natural language query.

[0184] The generated prompt words are given to the big model, and the thinking chain is used in the prompt words to enhance the reasoning ability of the big model and generate the required results.

[0185] Using thought chains in prompts means guiding the model to arrive at an answer through step-by-step reasoning and analysis, given a clear problem or task. Using thought chains can help the model approach complex problems more clearly and systematically, avoiding skipping key steps and generating more accurate and logical results.

[0186] The process of the thinking chain in this method is to first analyze the user problem, then extract pattern information, then make inferences, and finally draw conclusions.

[0187] The large model generates multiple results corresponding to user questions under higher randomness parameters and finds the best solution based on the consensus algorithm.

[0188] The randomness parameter is the model temperature (Temperature), and the choice of temperature should be obtained through repeated testing based on specific circumstances.

[0189] Temperature controls the randomness or determinism of the model's output, directly impacting the diversity and creativity of the generated content. When the temperature is higher, the generated text is more random, and the model explores more possible outputs. This generally leads to more creative or diverse results, but may also include more uncertainty or out-of-context content.

[0190] At a suitable high temperature, the model generates multiple results. After embedding the results into a vector space, the SQL with the highest similarity to the remaining results is returned. For example, multiple results are generated here:

[0191] 1.SELECT student_name,AVG(test_score)FROM student GROUP BY student_name;

[0192] 2.SELECT student_name,test_score FROM student GROUP BY student_name;

[0193] 3.SELECT student_name,AVG(test_score)FROM student GROUP BY student_name ORDER BY student_name;

[0194] Select the result with the highest similarity, which is result 1.

[0195] The generated SQL is executed in the database to check the syntax correctness. If it is correct, the SQL is output. If it is wrong, the error prompt is submitted to the big model to guide the big model to modify the correct result and output the SQL.

[0196] If the execution fails, the error message is recorded, and the message and SQL statement are placed in the correction prompt to guide the large model to correct the SQL. If it fails after three times, it will directly return to the large model for regeneration. If it still fails after three regenerations, the generation failure message will be directly output.

[0197] For example, the example here finally successfully generates the SQL: SELECT student_name, AVG(test_score) FROM student GROUP BY student_name.

[0198] The embodiments described above are merely descriptions of preferred embodiments of the present invention and are not intended to limit the scope of the present invention. Without departing from the spirit of the present invention, various modifications and improvements made to the technical solutions of the present invention by persons skilled in the art should fall within the scope of protection defined by the claims of the present invention.

Claims

1. A natural language query method for a relational database, characterized in that: The method comprises: Step S1. Obtain external knowledge description and user's natural language question; Step S2. Extracting the table structure from the local connection database according to the user's selection and generating a sub-database based on the natural language question; Step S3: Construct a screening prompt word template, input the sub-database schema and the natural language question into the screening prompt word template to obtain a complete prompt word, input the complete prompt word into the large language model, obtain the table name and column name used for this natural language question, and generate a simplified database schema based on the table name and column name, specifically including: Determine whether the table name and column name exist in the sub-database schema, and whether the nested relationship between the table and column conforms to the sub-database schema. If both are true, generate a simplified database schema. Step S4. Construct a prediction prompt word template, put the natural language question into the prediction prompt word template, and obtain the predicted SQL keyword skeleton; Step S5. Convert the SQL keyword skeleton into a prediction vector, select a preset number of example databases from the local connection database, extract question-answer pairs from the example databases, convert the SQL statements of the question-answer pairs into example SQL vectors, and store them in the vector database; Step S6. Calculate the similarity between the predicted vector and the example SQL vectors in the vector database, obtain a preset number of similarity results and corresponding example SQL vectors, and use the question-answer pairs corresponding to the SQL vectors as examples; Step S7. The simplified database model, the natural language question, the external knowledge description, and the example input prompt word template are combined to obtain a complete prompt word, and the prompt word is input into the large language model. The large language model uses the thought chain to generate an SQL statement to complete the natural language query; The generated prompt words are given to the large model, and the thought chain is used in the prompt words to enhance the reasoning ability of the large model and generate the required results; The specific chain of thought is: Analyze user questions, extract pattern information, then make inferences and draw conclusions; The large model generates multiple results corresponding to the user's questions under a high randomness parameter and finds the best solution based on the consensus algorithm; The randomness parameter is the model temperature, and the choice of temperature should be based on repeated tests according to specific circumstances; The randomness or determinism of the temperature control model output directly affects the diversity and creativity of the generated content; When the temperature is higher, the generated text is more random and the model explores more possible outputs.

2. The natural language query method for a relational database according to claim 1, characterized in that: In step S2, extracting the table structure from the local connection database according to the user's selection to generate the content of the sub-database based on the natural language question specifically includes: Obtain all schemas in a locally connected database, where the database schema is structural information of the database; Filter tables and columns across all database schemas based on user selections; A sub-database is generated based on the screening results.

3. The natural language query method for a relational database according to claim 1, characterized in that: In step S4, the predicted SQL keyword skeleton is a character string consisting of keywords and symbols in the SQL grammar except for the custom content.

4. A natural language query system for a relational database, the system being used to implement the natural language query method according to any one of claims 1 to 3, characterized in that the system include: Data acquisition module, used to obtain external knowledge descriptions and users' natural language questions; A sub-database generation module is used to extract table column structures from the local connection database according to user selection and generate a sub-database based on natural language questions; A simplified schema extraction module is used to construct a screening prompt word template, input the sub-database schema and the natural language question into the screening prompt word template to obtain a complete prompt word, input the complete prompt word into the large language model, obtain the table name and column name used for this natural language question, and generate a simplified database schema based on the table name and column name; The skeleton prediction module is used to build a prediction prompt word template, put the natural language question into the prediction prompt word template, and obtain the predicted SQL keyword skeleton; A vector conversion module is used to convert the SQL keyword skeleton into a prediction vector, filter a preset number of example databases from the local connection database, extract question-answer pairs from the example databases, convert the SQL statements of the question-answer pairs into example SQL vectors, and store them in the vector database; A similarity calculation module is used to calculate the similarity between the predicted vector and the example SQL vectors in the vector database, obtain a preset number of similarity results and corresponding example SQL vectors, and use the question-answer pairs corresponding to the SQL vectors as examples; The statement generation module is used to obtain a complete prompt word from the simplified database model, natural language question, external knowledge description and the example input prompt word template, input the prompt word into the large language model, and the large language model uses the thinking chain to generate SQL statements to complete the natural language query.

5. The natural language query system for a relational database according to claim 4, characterized in that: The workflow of the sub-database generation module specifically includes: Obtain all schemas in a locally connected database, where the database schema is structural information of the database; Filter tables and columns across all database schemas based on user selections; A sub-database is generated based on the screening results.

6. The natural language query system for a relational database according to claim 5, characterized in that: In the simplified schema extraction module, the process of generating a simplified database schema based on table names and column names specifically includes: Determine whether the table name and column name exist in the sub-database schema, and whether the nested relationship between the table and column conforms to the sub-database schema. If both are true, a simplified database schema is generated.

7. The natural language query system for a relational database according to claim 6, characterized in that: In the skeleton prediction module, the predicted SQL keyword skeleton is a character string consisting of keywords and symbols in the SQL grammar except for custom content.

Images