A method and apparatus for generating SQL statements

Abstract

The present invention provides a method and device for generating SQL statements, which relates to the field of artificial intelligence technology. The method includes: extracting preliminary field information from the preliminary SQL statement of the query text; determining a first supplementary field according to the semantic matching degree between the field dictionary and the query text; determining a second supplementary field according to the semantic similarity between the query text and the scenario data; fusing the preliminary field information, the first supplementary field, and the second supplementary field, optimizing the preliminary SQL statement into a candidate SQL statement, and determining the target SQL statement according to the execution data of each candidate SQL statement. In the present invention, the first supplementary field and the second supplementary field associated with the query text are mined from the scenario dictionary and the scenario database, so that the model can understand and infer the deep semantics related to the query text to establish a correct relationship with the fields in the database, thereby improving the accuracy of the target SQL.

Description

Technical Field

[0001] The present invention relates to the field of artificial intelligence technology, and in particular to a method and device for generating SQL statements. Background Art

[0002] SQL, as the core language for data query and processing, is widely used in database management systems. Users can interact with databases by writing SQL statements to query and process data. With the increasing demand for intelligence, technologies that convert natural language into SQL statements have emerged. Text2sql is a technology that converts natural language requests into SQL statements. It uses the understanding and analysis capabilities of large language models to generate SQL statements corresponding to user queries.

[0003] Current SQL statement generation technology usually uses models to align user queries with database information in the database. When the user query does not directly mention the fields in the database, the model often finds it difficult to accurately find the relationship between the user query and the database field, causing the generated SQL statement to deviate from user needs. For example, the column name actually corresponding to the user query "most valuable" is "FavoriteCount", and there is no obvious semantic correlation between the two, resulting in the generated SQL statement being inaccurate and difficult to meet user needs. Summary of the invention

[0004] In view of the above problems, the purpose of the present invention is to provide a method and device for generating SQL statements, which can mine the implicit semantic relationship between user queries and database fields and improve the accuracy of SQL statements.

[0005] In order to solve the above technical problems, the present invention provides the following technical solutions:

[0006] In one aspect, the present invention provides a method for generating a SQL statement, the method comprising:

[0007] Extract preliminary field information from the preliminary SQL statement corresponding to the query text;

[0008] Determining a first supplementary field from the field dictionary according to a semantic match between the field dictionary and the query text, the field dictionary including a mapping relationship between preset fields and preset definitions in a scenario database;

[0009] Determining a second supplementary field from the scenario database based on semantic similarity between the query text and scenario data in the scenario database;

[0010] fusing the preliminary field information, the first supplementary field, and the second supplementary field to optimize the preliminary SQL statement into a plurality of candidate SQL statements;

[0011] Determine a target SQL statement from the multiple candidate SQL statements by using the execution data of each of the candidate SQL statements.

[0012] On the other hand, the present invention also provides an SQL statement generation device, which includes:

[0013] An extraction module, configured to extract preliminary field information from a preliminary SQL statement corresponding to a query text;

[0014] A first determination module, configured to determine a first supplementary field from the field dictionary according to the semantic matching degree between the field dictionary and the query text, where the field dictionary includes a mapping relationship between preset fields in a scenario database and preset interpretations;

[0015] A second determination module, configured to determine a second supplementary field from the scenario database based on the semantic similarity between the query text and the scenario data in the scenario database;

[0016] An optimization module, configured to fuse the preliminary field information, the first supplementary field, and the second supplementary field to optimize the preliminary SQL statement into multiple candidate SQL statements;

[0017] A target module, configured to determine a target SQL statement from the multiple candidate SQL statements by using the execution data of each of the candidate SQL statements.

[0018] On the other hand, the present invention also provides an electronic device, including a processor and a memory, where the memory stores multiple instructions; the processor loads the instructions from the memory to execute the steps in any one of the SQL statement generation methods provided by the present invention.

[0019] On the other hand, the present invention also provides a computer-readable storage medium, where the computer-readable storage medium stores multiple instructions, and the instructions are suitable for being loaded by a processor to execute the steps in any one of the SQL statement generation methods provided by the present invention.

[0020] On the other hand, the present invention also provides a computer program product, including a computer program / instructions, and when the computer program / instructions are executed by a processor, the steps in any one of the SQL statement generation methods provided by the present invention are implemented.

[0021] The beneficial effects brought by the technical solution provided by the present invention at least include:

[0022] Embodiments of the present invention can extract preliminary field information from the preliminary SQL statement corresponding to the query text; determine the first supplementary field based on the semantic matching degree between the query text and the field dictionary; determine the second supplementary field based on the semantic similarity between the query text and the scenario data; optimize the preliminary SQL statement into a candidate SQL statement by integrating the preliminary field information, the first supplementary field, and the second supplementary field, and then determine the target SQL statement according to the execution data of the candidate SQL statement. By mining the first supplementary field and the second supplementary field associated with the query text from the scenario dictionary and the scenario database, the model can understand and infer the deep semantics related to the query text to establish a correct relationship with the fields in the database, thereby improving the accuracy of the target SQL. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] To more clearly illustrate the technical solutions in the embodiments of the present invention, the following will briefly introduce the drawings required for the description of the embodiments. Obviously, the drawings in the following description are only some embodiments of the present invention. For those skilled in the art, without creative efforts, other drawings can be obtained based on these drawings.

[0024] Figure 1 is a schematic diagram of the application scenario of the SQL statement generation method provided by the embodiments of the present invention;

[0025] Figure 2 is a schematic flowchart of the SQL statement generation method provided by the embodiments of the present invention;

[0026] Figure 3 is a schematic diagram for determining the first supplementary field provided by the embodiments of the present invention;

[0027] Figure 4 is a schematic diagram for determining the second supplementary field provided by the embodiments of the present invention;

[0028] Figure 5 is a schematic structural diagram of the SQL statement generation device provided by the embodiments of the present invention;

[0029] Figure 6 is a schematic structural diagram of the electronic device provided by the embodiments of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0030] The following will clearly and completely describe the technical solutions in the embodiments of the present invention with reference to the drawings in the embodiments of the present invention. Obviously, the described embodiments are only some of the embodiments of the present invention, rather than all of them. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative efforts belong to the scope of protection of the present invention.

[0031] The present invention provides a method and apparatus for generating SQL statements, which can discover the implicit semantic relationship between user queries and database information, and ensure the generation of accurate SQL statements to meet user requirements.

[0032] It can be understood that in the specific implementation of the present invention, data related to user information and the like is involved, and user permission or consent is required, and the collection, use, and processing of relevant data need to comply with relevant laws, regulations, and standards in relevant countries and regions.

[0033] Refer to Figure 1 , which shows a schematic diagram of the application scenario of the SQL statement generation method. Among them, the application scenario may include a terminal 101 and a server 102. Data exchange can be carried out between the terminal 101 and the server 102 through a network, and a corresponding application program can be installed on the terminal 101. Among them, the terminal 101 can be a mobile phone, a tablet computer, a smart Bluetooth device, a computer, a large screen, etc., a robot, etc.; the server 102 can be a single server or a server cluster composed of multiple servers.

[0034] The user can send a query text to the server 102 through the terminal 101. The server 102 can extract preliminary field information from the preliminary SQL statement corresponding to the query text; determine a first supplementary field from the field dictionary according to the semantic matching degree between the field dictionary and the query text, where the field dictionary includes the mapping relationship between the preset fields and preset interpretations in the scenario database; determine a second supplementary field from the scenario database based on the semantic similarity between the query text and the scenario data in the scenario database; fuse the preliminary field information, the first supplementary field, and the second supplementary field to optimize the preliminary SQL statement into multiple candidate SQL statements; use the execution data of each candidate SQL statement to determine the target SQL statement from multiple candidate SQL statements. The server 102 can send the determined target SQL statement to the terminal 101 to display the target SQL statement to the user.

[0035] In this embodiment, a method for generating SQL statements is provided. As Figure 2 shown, the specific process of this SQL statement generation method can be as follows:

[0036] S110. Extract preliminary field information from the preliminary SQL statement corresponding to the query text.

[0037] The query text is the original query data input by the user, and the preliminary SQL statement is the result obtained by processing the query text using the existing text2sql technology. As an implementation method, when generating the preliminary SQL statement corresponding to the query text, the query data may be standardized to obtain a standardized query; database information corresponding to the target database may be obtained; the standardized query and the database information corresponding to the target database may be merged with a preset prompt word template to obtain a preset prompt word; the preset prompt word may be input into a large language model to obtain the preliminary SQL statement.

[0038] Among them, the standardization processing can be to use the semantic parsing ability of the large language model to eliminate descriptions and modal particles with low information content in the query text, and further refine the query text to make the information expressed in the query text clearer and more accurate.

[0039] Then, the database information of the target database, namely, the schema information, is obtained, which may include a table name set, a field name set, a field type set, primary key and foreign key information, and sample value data of a random sampling of table data samples. The database information of the target database and the standardized query are filled into the preset prompt word template to obtain the preset prompt word.

[0040] In the embodiment of the present invention, the preset prompt word template may be:

[0041] “### Role: You are a Python pseudocode interpreter that also has the ability to output SQL executable statements. Key Points You do not need to actually call the Python interpreter to execute the following code. Give the corresponding answer based on the ideas of the following code writing.

[0042] def get_preliminary_SQL(schema_info:str, question:str) -> str:

[0043] """The function of this function is to understand the meaning of the user question question based on the provided input parameters, and combined with the given schema_info, please translate the user quesiton into an execution statement that conforms to the SQLite syntax.

[0044] schema_info:str is the database schema information associated with question, including table name, column name and its corresponding value type and value sampling, primary key and foreign key information.

[0045] question:str is the question entered by the user.

[0046] SQL: str is the answer to this task, that is, the SQLite executable statement that meets the requirements of the question.

[0047] Only the executable SQL for the user question "question" needs to be output, without outputting other content.

[0048] """

[0049] # Please combine the schema_info information and translate "question" into an executable statement that conforms to SQL syntax.

[0050] # Table name: T_i

[0051] # Column [Column Type, (Value1,Value2,Value3)] in Table T_i

[0052] F_1[FT_1,(Value_f1_1, Value_f1_2, Value_f1_3)],F_2[FT_2,( Value_f2_1, Value_f2_2, Value_f2_3)],...

[0053] # primary keys

[0054] T_i.primary_k1, (T_i.primary_k1, T_i.parmary_k2)

[0055] # foreign keys

[0056] T_i.primary = T_j.primary_k1

[0057] # User question "question"

[0058] question: {Q_"normalized"}

[0059] SQL: ”

[0060] By inputting a preset prompt into the large language model, the preliminary SQL statement corresponding to the query text can be generated. For example, if the query text is: Among the posts that were voted by user 1465, what is the id of the most valuable post; the preliminary SQL statement is: SELECT p.Id FROM posts p.JOIN votes v ON p.Id = v.PostId WHERE v.UserId = 1465 ORDER BY p.Score DESC LIMIT 1.

[0061] The field information contained in the preliminary SQL statement can be extracted to obtain the preliminary field information. As an implementation, the field names in the preliminary SQL statement can be extracted by regular matching to obtain the preliminary field information. As an implementation, corresponding prompts can be designed to use the large language model to extract the preliminary field information from the preliminary SQL statement. For example, the database information of the target database and the preliminary SQL statement can be filled into the extraction template to generate an extraction prompt; the extraction prompt is input into the large language model to guide the large language model to extract the preliminary field information from the preliminary SQL statement.

[0062] The extraction template is a preset prompt template for extracting field information. The extraction template may include multiple slots for filling in the necessary information required for extracting field information, such as the preliminary SQL and the database information of the target database. The target database is the database used when generating the preliminary SQL statement.

[0063] By filling the preliminary SQL statement and the database information of the target database into the corresponding slots in the extraction template, an extraction prompt can be obtained. By inputting the extraction prompt into the large language model, the large language model can be guided to analyze and process the preliminary SQL statement and output the extracted preliminary field information.

[0064] It should be noted that the prompts used in the embodiments of the present invention are all in the form of pseudo-functions. Among them, the prompts in the form of pseudo-functions can reduce the hallucination of the large language model and enhance its understanding ability compared with the prompts in the form of conventional texts, and thus can ensure the accuracy of the model output.

[0065] S120. Determine a first supplementary field from the field dictionary according to the semantic matching degree between the field dictionary and the query text.

[0066] The field dictionary includes the mapping relationship between the preset fields in the scenario database and the preset interpretations. The field dictionary can be obtained in advance using the content in the scenario database. Among them, the scenario database is a database under a specified scenario, used to store business data under the specified scenario; the preset field is a field in the scenario database, and the preset interpretation is the interpretation of the preset field in the corresponding scenario or a general interpretation. The preset interpretation can be obtained by performing semantic understanding and analysis on the data under the preset field in the scenario data.

[0067] Among them, the field dictionary is in the form of key-value pairs, where the key is the preset field name and the value is the corresponding field interpretation. For example, "debit_card_specializing|customers|CustomerID": "# 'CustomerID is an integer column in the customers table, which is used to identify the customer.'"

[0068] The first supplementary field is a field in the field dictionary with a relatively high semantic matching degree with the query text. It may be a part of the SQL statement corresponding to the query text. According to the semantic matching degree between the field dictionary and the query text, the first supplementary field can be determined.

[0069] In some embodiments, when determining the first supplementary field from the field dictionary according to the semantic matching degree between the field dictionary and the query text, the preset field and the preset interpretation can be concatenated to obtain a concatenated text; the concatenated text and the query text are both vectorized to obtain a concatenated vector and a query vector; the cosine similarity between the concatenated vector and the query vector is calculated as the semantic matching degree; the preset fields are sorted in descending order of the semantic matching degree, and the top preset number of preset fields are selected as the first supplementary fields.

[0070] In some embodiments, in order to make full use of the preset fields and field interpretations, the semantic matching degree can include a first matching degree and a second matching degree. When determining the first supplementary field from the field dictionary according to the semantic matching degree between the field dictionary and the query text, a standardized query obtained by standardizing the query text can be obtained; for each preset field in the field dictionary, the first matching degree between the standardized query and the preset field is calculated; the second matching degree between the standardized query and the preset interpretation corresponding to the preset field is calculated; the first supplementary field is determined from the preset fields in the field dictionary by combining the first matching degree and the second matching degree.

[0071] Among them, reference can be made to Figure 3, showing a schematic diagram of determining the first supplementary field. Standardization processing refers to preprocessing the query text, using the semantic parsing ability of the large language model to remove descriptions and modal particles with low information content in the query text, further refine the problem, and generate a standardized query. Standardized queries are clearer and more direct, can better express problems, and are conducive to improving subsequent processing efficiency and accuracy.

[0072] The field dictionary contains multiple preset fields and the field interpretation corresponding to each preset field. When calculating the semantic matching degree, for each preset field, the first matching degree between the standardized query and the preset field can be calculated. Optionally, a word embedding model can be used to convert the standardized query into a query vector, the preset field into a field vector, and the cosine similarity between the query vector and the field vector is calculated as the first matching degree, then a first matching degree can be calculated for each preset field. Each preset field also has a corresponding preset interpretation. For each preset interpretation, a word embedding model can also be used to convert the preset interpretation into an interpretation vector, and the cosine similarity between the interpretation vector and the query vector is calculated as the second matching degree. Thus, each preset field has a first matching degree, and each preset interpretation has a second matching degree.

[0073] The first supplementary field can be selected from the preset fields by using the first matching degree and the second matching degree. As an implementation method, the first matching degree corresponding to the preset field is added with the second matching degree of the preset interpretation corresponding to the preset field as the target matching degree of the preset field. The preset fields are sorted in descending order according to the target matching degree, and a preset number of preset fields with the highest ranking are selected as the first supplementary fields.

[0074] As another implementation, the preset fields may be sorted in descending order according to the first matching degree, and a preset number of preset fields with the highest ranking may be selected as the first candidate fields; the preset interpretations may be sorted in descending order according to the second matching degree, and the preset fields corresponding to the preset number of preset interpretations with the highest ranking may be selected as the second candidate fields; and the first candidate field and the second candidate field may be merged and deduplicated to serve as the first supplementary field.

[0075] The first supplementary field is determined based on the semantic matching degree between the query text and the field dictionary. The field interpretation of the field dictionary can be used to mine fields that are strongly associated with the query text, which can be used to subsequently enhance the model's understanding of the query text to improve the accuracy of the target SQL.

[0076] S130: Determine a second supplementary field from the scenario database based on the semantic similarity between the query text and the scenario data in the scenario database.

[0077] The scenario database stores specific data under a specified scenario, and these data are scenario data. The second supplementary field is a field in the scenario database that is relatively similar to the query text, and it has a probability of being part of the SQL statement corresponding to the query text. Using the query text and the scenario data, the second supplementary field can be determined.

[0078] In some embodiments, to determine the second supplementary field from the scenario database, it can be to perform word segmentation on the standardized query corresponding to the query text to obtain multiple query words; obtain the scenario data corresponding to each scenario field in the scenario database; use a specified hash function to map each scenario data to a scenario index, and establish an association relationship between the scenario index and the scenario field to obtain a hash table; perform a semantic query process on the hash table according to each query index to obtain the second supplementary field.

[0079] Among them, reference can be made to Figure 4 , which shows a schematic diagram of determining the second supplementary field. Perform word segmentation on the standardized query, and extract common storage types in the database such as Chinese and English nouns, numbers, dates, etc. from it, and use the extracted segmented words as query words. For the scenario database, the scenario data stored under all scenario fields in the scenario database can be obtained.

[0080] For the scenario data corresponding to each scenario field, a specified hash function can be used to map the scenario data to a scenario index. The specified hash function is a hash function set in advance and meeting specific conditions. The specified hash function can map two originally similar data to the same hash value, and map two originally dissimilar data to different hash values. By using the specified hash function to calculate each scenario data, the hash value corresponding to the scenario data can be obtained. If there are two relatively similar scenario data, after being processed by the specified hash function, the obtained hash values are the same.

[0081] After being processed by the specified hash function, each scenario data is mapped to a hash value, and this hash value can be recorded as the scenario index. Since there is a corresponding relationship between the scenario field and the scenario data, and there is also a corresponding relationship between the scenario data and the scenario index, there is also an association relationship between the scenario field and the scenario index. The association relationship between each scenario field and the scenario index can be recorded as a hash table.

[0082] For each query term obtained by word segmentation from the query text, the same specified hash function can be used to map the query term to a hash value, denoted as the query index. Based on the nearest neighbor of each query index in the hash table, a second supplementary field can be determined. Optionally, for each of the query indexes, the scenario field in the hash table with the same scenario index as the query index can be used as the first scenario field; based on the similarity between each of the first scenario fields and the query text, a second scenario field can be determined from the first scenario fields; the second scenario fields corresponding to the query indexes are merged to obtain the second supplementary field.

[0083] For each query index, the query index can be used to query in the hash table, and the scenario field with the same scenario index as the query index is used as the first scenario field. For each of the first scenario fields, the semantic similarity between the query text and the first scenario field is calculated.

[0084] For example, the query text and the first scenario field can be converted into vectors, and then the cosine similarity between the two vectors can be calculated as the semantic similarity. The larger the semantic similarity, the more similar the query text and the first scenario field are. The first scenario field with the largest semantic similarity is used as the second scenario field. In this way, a corresponding second scenario field can be selected for each query index. After merging and removing duplicates of the second scenario fields of all query indexes, it can be used as the second supplementary field.

[0085] Similar data can be mapped to the same hash value through the specified hash function. That is, the scenario fields in the scenario database can be divided into multiple small sets. For example, Figure 4 in which there may be multiple scenario fields corresponding to each scenario index, and the scenario fields within each small set have a high similarity. When obtaining data similar to the query text, the query index can be used to quickly lock the small set. For example, Figure 4 the set corresponding to scenario index 2 in which. Only by traversing within the small set to filter out the second supplementary field can the calculation amount be effectively reduced and the processing efficiency be improved.

[0086] S140. Integrate the preliminary field information, the first supplementary field, and the second supplementary field to optimize the preliminary SQL statement into multiple candidate SQL statements.

[0087] Using the existing field information extracted from the preliminary SQL statement, the first supplementary field that is semantically matched with the query text selected from the field dictionary, and the second supplementary field with similar semantics found in the scenario database, the preliminary SQL statement can be supplemented and enhanced to optimize the preliminary SQL statement to obtain multiple candidate SQL statements.

[0088] As an implementation manner, the preliminary SQL statement, the first supplementary field, and the second supplementary field can be directly input into the large language model, and the large language model is used for analysis and processing to directly give multiple candidate SQL statements.

[0089] As an implementation manner, the preliminary field information, the first supplementary field, and the second supplementary field can be fused to generate auxiliary optimization data; the auxiliary optimization data, the query text, the preliminary SQL statement, and the database information of the target database are filled into the optimization template to obtain an optimization prompt; based on the optimization prompt, the large language model is guided to optimize the preliminary SQL statement to obtain multiple optimized candidate SQL statements.

[0090] The auxiliary optimization data is data used to assist in SQL optimization. During the optimization process of the large language model, it can provide more information related to the query text to improve the understanding and optimization ability of the large language model and ensure that a more accurate target SQL statement is obtained subsequently.

[0091] As an implementation manner, when generating the auxiliary optimization data, duplicate removal processing can be performed on the preliminary field information, the first supplementary field, and the second supplementary field to obtain specified fields; for each of the specified fields, the specified data corresponding to the specified field is obtained according to the database name, the table name, and the field name; the specified data, the preliminary field information, the first supplementary field, and the second supplementary field are used as the auxiliary optimization data.

[0092] The preliminary field information, the first supplementary field, and the second supplementary field are merged, and the duplicate fields are removed to obtain specified fields. Among them, the specified fields are sorted in the format of database|table name|field name. For each specified field, the database indicated by the database name in the specified field can be connected, and non-null values are extracted in the database according to the table name and the field name. The extraction quantity of the non-null values can be set according to actual needs. In the embodiments of the present invention, the extraction quantity can be 5. If the number of non-null values in the database is less than 5, all of them are extracted. For a single non-null value, its length is restricted. If it exceeds the restricted length, truncation processing is performed. The restricted length can also be set according to actual needs. In the embodiments of the present invention, the restricted length is 50 characters. The extracted non-null values can be organized in a dictionary form, that is, the key is the specified field and the value is the non-null value, to form the specified data.

[0093] Finally, the specified data, the preliminary field information, the first supplementary field, and the second supplementary field are used as the auxiliary optimization data.

[0094] The optimization template is a preset prompt template for generating optimization prompts. The optimization template may include multiple slots to be filled, such as auxiliary slots, query slots, preliminary slots, database slots, etc. By filling the auxiliary optimization data into the auxiliary slots, the query text into the query slots, the preliminary SQL statement into the preliminary slots, and the database information of the target database into the database slots, an optimization prompt can be obtained.

[0095] Among them, the optimization template provided by the embodiments of the present invention is specifically as follows:

[0096] " Role: You are a Python pseudocode interpretation device. Key points You do not need to actually call the Python interpreter to execute the following code. Based on the following code writing ideas, give corresponding answers.

[0097] def rewrite_SQL(preliminary_SQL:str, question:str, updated_column_names:str, LSH_column_names:str, few-shot:str, schema_info:str, schema_relations:str, value_format: str) -> str:

[0098] """The task you need to complete is to check whether the preliminary_SQL can meet the requirements of the question based on the user's question and preliminary_SQL, combined with the provided other parameter information. If not, modify it according to the other parameter information.

[0099] preliminary_SQL:str is the preliminary SQL, which needs to be evaluated for correctness in combination with the question and other parameter information.

[0100] question:str is the user's question, and the return needs to be able to solve the requirements of this problem.

[0101] updated_column_names:str are possible alternative SQL database names | table names | column names.

[0102] LSH_column_names:str are some SQL database names | table names | column names that are numerically similar to the user's question content.

[0103] few-shot:str are some similar case references.

[0104] schema_info: str is the original schema information related to the question.

[0105] schema_relations: str is the table association information related to schema_info.

[0106] value_format: str is the format reference for some field value samples.

[0107] return_SQL: str is the answer corresponding to the requirements of this task. Only output the answer that can be accurately executed by the SQL terminal.

[0108] """

[0109] preliminary_SQL: { preliminary_SQL}

[0110] question: { question}

[0111] updated_column_names: { updated_column_names}

[0112] LSH_column_names: { LSH_column_names}

[0113] few-shot: { few-shot}

[0114] schema_info: { schema_info}

[0115] schema_relations: { schema_relations}

[0116] value_format: {VF}

[0117] return_SQL: ”

[0118] Input the optimization prompt into the large language model so that the large language model can use the information provided in the optimization prompt to optimize the preliminary SQL statement. To ensure the accuracy of the target SQL statement, the request parameters of the large language model can be set so that the large language model can return multiple optimized SQL statements, that is, multiple candidate SQL statements.

[0119] In some embodiments, adding example samples to the prompt can, to a certain extent, improve the model's understanding ability. To further improve the accuracy of the candidate SQL statements, the auxiliary optimization data can also include example samples. Adding the example samples as part of the auxiliary optimization data to the optimization prompt can improve the understanding ability of the large language model, and thus improve the accuracy of the candidate SQL statements.

[0120] Among them, the example samples can be generated in a specific manner. Optionally, when obtaining the example samples, the syntax structure information in the preliminary SQL statement can be extracted to obtain the first structure information; each word segment in the query text can be masked with a specified probability to obtain the second structure information; the SQL log corresponding to the scenario database is obtained, and the SQL log includes multiple historical query pairs, and the historical query pair includes a historical query and a corresponding plurality of historical SQL statements; the first structure information and the second structure information are respectively matched with each historical query pair to obtain the first matching data and the second matching data; the example samples are determined from the multiple historical query pairs by using the first matching data and the second matching data.

[0121] For the preliminary SQL statement, a regular expression can be written to extract the syntax structure information from the preliminary SQL statement as the first structure information. For example, the query text is: Among the posts that were voted by user 1465, what is the id of the most valuable post? The preliminary SQL statement is: SELECT p.Id FROM posts p.JOIN votes v ON p.Id = v.PostId WHERE v.UserId = 1465 ORDER BY p.Score DESC LIMIT 1. The first structure information extracted from the preliminary SQL statement can be "SELECT _ WHERE _ ORDER BY LIMIT 1".

[0122] For the query text, each word segment in the query text can be masked according to a specified probability to obtain the second structure information. Among them, the specified probability can be set according to actual needs. In the embodiments of the present invention, the specified probability can be 10%. Specifically, each word segment in the query text can be randomly masked with a probability of 10% independently to obtain the second structure information. For example, the second structure information corresponding to the query text in the foregoing example can be "Among the posts _ were voted by user _, what is the id of the most valuable _?".

[0123] Among them, the SQL log of the scenario database can be pre-generated offline and stored at a specified location. When needed, it can be directly retrieved from the specified location, or it can be generated in real time when needed. In the embodiment of the present invention, to improve the generation efficiency of the target SQL, the pre-offline generation method is adopted. That is, before obtaining the SQL log of the scenario database, the operation log of the scenario database can also be sliced to obtain multiple log fragments; for each of the log fragments, historical database information is obtained by using the field information corresponding to the log fragment; with all the historical database information and the log fragment, an extraction prompt is constructed; the extraction prompt is used to guide the large language model to extract historical query pairs from the log fragment to obtain the SQL log.

[0124] Obtain the operation log of the scenario database in the historical time period, slice the operation log by paragraph to obtain multiple log fragments, where the historical time period is the time period before the current time, and can be specifically set according to actual needs.

[0125] For each log fragment, the log fragment can be matched to the corresponding database name, table name, and field name as its corresponding field information. After obtaining the field information, relevant SQL naming can be executed to obtain the database information corresponding to all the databases that have appeared, including table names, field names, primary and foreign key information, etc., as historical database information.

[0126] The extraction prompt is the prompt used to extract historical query pairs from the log fragment. The extraction prompt can be pre-set with an extraction template, and all the log fragments and historical database information are filled into the extraction template to generate the extraction prompt. The extraction template provided by the embodiment of the present invention is specifically as follows:

[0127] " Role: You are a python pseudocode interpretation device. Key points You don't need to actually call the python interpreter to execute the following code. Combine the following code writing ideas to give the corresponding answer.

[0128] def extract_question_sql_pair(raw_sentence:str, schema:str) -> str

[0129] """Your task is to extract possible SQL statements from the raw_sentence according to the schema table creation information, parse and infer the semantics of the raw_sentence, summarize the purpose and requirements corresponding to the SQL statement, and change the requirements that the SQL can meet into questions and output. If no SQL can be extracted, return "Unable to extract SQL."

[0130] raw_sentence:str may contain SQL execution statements. If there are SQL execution statements, extract and output the SQL statement.

[0131] schema:str is the schema table creation information that the raw_sentence may be associated with. It can be used as an auxiliary reference when extracting possible SQL statements or summarizing the requirements corresponding to the SQL statement.

[0132] extracted_SQL: Please output the SQL statement you extracted from the raw_sentence after this.

[0133] question:Please output the requirement description corresponding to the extracted_SQL after this, and change it into a question.

[0134] """

[0135] raw_sentence: {log_sentence_i}

[0136] schema: {schema_info_base}

[0137] ## Note that you don't need to output anything else, just give the answers corresponding to extracted_SQL and question.

[0138] # extracted_SQL:

[0139] # question: ”

[0140] Input the extraction prompt words into the large language model to guide the large language model to extract historical SQL and the historical queries corresponding to the historical SQL, form historical query pairs, and use all the historical query pairs as SQL logs.

[0141] The SQL log may include multiple historical query pairs, and each historical query pair includes a historical query and the corresponding historical SQL statement. By calculating the cosine similarity between the first structural information and each historical query pair, the first similarity of each historical query pair can be obtained. By calculating the cosine similarity between the second structure and each historical query pair, the second similarity of each historical query pair can be obtained.

[0142] Using the first similarity and the second similarity of the historical query pairs, the historical query pairs can be sorted respectively, and the top specified number of historical query pairs with the highest similarity can be selected as sample examples. The specified number is the number of sample examples and can be set according to actual needs.

[0143] Taking the sample examples as part of the auxiliary optimization data, generating optimization prompt words in the aforementioned manner, and inputting the optimization prompt words into the large language model to optimize the preliminary SQL statement to obtain multiple candidate SQL statements output by the large language model.

[0144] S150. Using the execution data of each of the candidate SQL statements, determine the target SQL statement from the multiple candidate SQL statements.

[0145] To ensure the accuracy of the target SQL statement, it is necessary to verify the obtained candidate SQL statements to ensure that the candidate SQL statements can be executed normally. When the candidate SQL statement passes the verification, it can be used as the target SQL statement.

[0146] As an implementation manner, when determining the target SQL statement from the candidate SQL statements, for each of the candidate SQL statements, execute the candidate SQL statement to obtain the corresponding execution data; if the execution data does not contain error information, determine the candidate SQL statement as an intermediate SQL statement; if the execution data contains error information, use the error information to repair the candidate SQL statement; if the repair is successful and the execution data of the repaired candidate SQL statement does not contain error information, use the repaired candidate SQL statement as the intermediate SQL statement; determine the target SQL statement from the intermediate SQL statements according to a preset rule.

[0147] For each candidate SQL statement, the candidate SQL statement can be executed, and the corresponding execution data can be obtained. The execution data may include the execution time, execution duration, and result. Among them, the result may include successful execution or execution error. If the execution is in error, the corresponding error information can be obtained.

[0148] If the execution data does not contain error information, the candidate SQL statement can be directly used as the intermediate SQL statement. If the execution data contains error information, it indicates that an exception occurred during the execution of the candidate SQL statement. The semantic parsing ability of the large language model can be utilized to attempt to repair the candidate SQL statement using the error information.

[0149] Optionally, a repair prompt can be constructed and input into the large language model to obtain the corresponding repaired SQL statement. If the repair is successful, the repaired candidate SQL statement can be obtained. Verify the repaired candidate SQL statement. If the corresponding execution data does not contain error information, the repaired candidate SQL statement can be used as the intermediate SQL statement.

[0150] Among them, the repair prompt has a pre-set repair template, and the corresponding data can be filled into the repair template to obtain the repair prompt. The repair template provided in the embodiments of the present invention is specifically as follows:

[0151] " Role: You are a python pseudocode interpretation device. Key points You do not need to actually call the python interpreter to execute the following code. Based on the following code writing ideas, give the corresponding answer.

[0152] def debug_SQL(question:str, error_msg:str, bug_sql:str,schema_info:str, schema_relations:str) -> str

[0153] """The task you need to complete is to troubleshoot errors and repair the problematic SQL statement based on the user's question and the problematic SQL, combined with the provided terminal error information and schema information.

[0154] question:str is the user's question, and the return needs to be able to meet the requirements of solving the problem.

[0155] error_msg:str is the error message reported by the terminal execution of bug_sql.

[0156] bug_sql:str is the problematic SQL this time, and its syntax needs to be repaired.

[0157] schema_info:str is the original schema information related to the question.

[0158] schema_relations:str is the table association information related to schema_info.

[0159] return_SQL: str is the answer corresponding to the requirements of this task. Only output answers that can be accurately executed by the SQL terminal.

[0160] """

[0161] question: { question}

[0162] error_msg: {e_i}

[0163] bug_sql: {rewrited_SQL_i}

[0164] schema_info: { schema_info}

[0165] schema_relations: { schema_relations}

[0166] return_SQL:”

[0167] It should be noted that the repair can be executed multiple times. If the execution data of the candidate SQL statement after repair still contains error information, the error information can be used to continue the repair until the maximum number of repairs is reached. If the repair is still not successful after reaching the maximum number of repairs, the candidate SQL statement can be directly discarded.

[0168] Based on the foregoing, the intermediate SQL statement can be the original candidate SQL statement or the candidate SQL statement after repair. For the candidate SQL statement after repair, a repair mark can be added for distinction.

[0169] As an implementation method, any one of the intermediate SQL statements can be directly selected as the target SQL statement. As another implementation method, in order to ensure the best execution effect of the target SQL statement, the occurrence times of each intermediate SQL statement can be counted, and the intermediate SQL statement with the most occurrence times is used as the target SQL statement; if the occurrence times are the same, the intermediate SQL statement with the shortest execution duration is used as the target SQL statement; if the execution durations are the same, an intermediate SQL statement without a repair mark is randomly selected as the target SQL statement preferentially.

[0170] The SQL statement generation solution provided by the embodiments of the present invention can be applied to various scenarios. For example, taking the medical and health scenario as an example, adopting the solution provided by the embodiments of the present invention can more effectively mine potentially related fields from the user's query text, and use these fields to enhance the optimization of the preliminary SQL statement by the model, thereby generating a more accurate target SQL statement.

[0171] Through the method provided by the embodiments of the present invention, the first supplementary field related to the query text can be mined from the field dictionary constructed in advance based on the data in the scenario database, combined with the interpretation of the field, and the second supplementary field related to the query text can be mined by using the data specifically stored in the scenario database. The first supplementary field and the second supplementary field can be used in the subsequent optimization of the preliminary SQL statement to enhance the model's understanding and reasoning ability of the query text, so as to mine the fields having an implicit relationship with the query text, realize the supplementation or correction of the SQL statement, and further improve the accuracy of the generated target SQL statement.

[0172] To better implement the above method, the embodiments of the present invention further provide an SQL statement generation device. The SQL statement generation device can be specifically integrated in an electronic device, and the electronic device can be a device such as a terminal or a server. Among them, the terminal can be a device such as a mobile phone, a tablet computer, a smart Bluetooth device, a notebook computer, or a personal computer; the server can be a single server or a server cluster composed of multiple servers.

[0173] For example, in this embodiment, taking the SQL statement generation device being specifically integrated in the server as an example, the method of the embodiments of the present invention will be described in detail.

[0174] For example, as Figure 5 shown, the SQL statement generation device 200 may include an extraction module 210, a first determination module 220, a second determination module 230, an optimization module 240, and a target module 250.

[0175] The extraction module 210 is configured to extract preliminary field information from the preliminary SQL statement corresponding to the query text;

[0176] The first determination module 220 is configured to determine a first supplementary field from the field dictionary according to the semantic matching degree between the field dictionary and the query text, where the field dictionary includes the mapping relationship between the preset fields in the scenario database and the preset interpretations;

[0177] The second determination module 230 is configured to determine a second supplementary field from the scenario database based on the semantic similarity between the query text and the scenario data in the scenario database;

[0178] The optimization module 240 is configured to fuse the preliminary field information, the first supplementary field, and the second supplementary field to optimize the preliminary SQL statement into multiple candidate SQL statements;

[0179] The target module 250 is configured to determine a target SQL statement from the multiple candidate SQL statements by using the execution data of each candidate SQL statement.

[0180] In some embodiments, the first determination module 220 is specifically configured to:

[0181] Obtain a standardized query obtained by performing standardized processing on the query text;

[0182] For each preset field in the field dictionary, calculate a first matching degree between the standardized query and the preset field;

[0183] Calculate a second matching degree between the standardized query and the preset paraphrase corresponding to the preset field;

[0184] Combine the first matching degree and the second matching degree to determine a first supplementary field from the preset fields in the field dictionary.

[0185] In some embodiments, the second determination module 230 is specifically configured to:

[0186] Perform word segmentation on the standardized query corresponding to the query text to obtain a plurality of query words;

[0187] Obtain the scenario data corresponding to each scenario field in the scenario database;

[0188] Use a specified hash function to map each piece of scenario data to a scenario index, and establish an association relationship between the scenario index and the scenario field to obtain a hash table;

[0189] Perform hash mapping processing on each query word using the specified hash function to obtain a query index;

[0190] According to each query index, perform semantic query processing in the hash table to obtain a second supplementary field.

[0191] In some embodiments, the second determination module 230 is specifically configured to:

[0192] For each query index, use the scenario field in the hash table whose scenario index is the same as the query index as the first scenario field;

[0193] Based on the semantic similarity between each first scenario field and the query text, determine a second scenario field from the first scenario fields;

[0194] Merge the second scenario fields corresponding to the query index to obtain a second supplementary field.

[0195] In some embodiments, the optimization module 240 is specifically configured to:

[0196] Fuse the preliminary field information, the first supplementary field, and the second supplementary field to generate auxiliary optimization data;

[0197] Fill the auxiliary optimization data, the query text, the preliminary SQL statement, and the database information of the target database into an optimization template to obtain an optimization prompt;

[0198] Based on the optimization prompt, guide a large language model to optimize the preliminary SQL statement to obtain multiple candidate SQL statements after optimization.

[0199] In some embodiments, the optimization module 240 is specifically configured to:

[0200] Deduplicate the preliminary field information, the first supplementary field, and the second supplementary field to obtain specified fields, where the specified fields include database names, table names, and field names;

[0201] For each of the specified fields, obtain the specified data corresponding to the specified field according to the database name, the table name, and the field name;

[0202] Use the specified data, the preliminary field information, the first supplementary field, and the second supplementary field as auxiliary optimization data.

[0203] In some embodiments, the auxiliary optimization data further includes example samples, and the SQL statement generation device 200 further includes an example sample acquisition module, and the example sample acquisition module is specifically configured to:

[0204] Extract the syntax structure information in the preliminary SQL statement to obtain first structure information;

[0205] Cover each word segment in the query text with a specified probability to obtain second structure information;

[0206] Obtain the SQL log corresponding to the scenario database, where the SQL log includes multiple historical query pairs, and the historical query pair includes a historical query and the corresponding historical SQL statement;

[0207] Calculate the similarity between the first structure information and the second structure information and each historical query pair respectively to obtain the first similarity and the second similarity of the historical query pair;

[0208] Use the first similarity and the second similarity of each historical query pair to determine example samples from multiple historical query pairs.

[0209] In some embodiments, before obtaining the SQL log corresponding to the scenario database, the example sample acquisition module is specifically configured to:

[0210] Slice the operation log of the scenario database to obtain multiple log segments;

[0211] For each of the log segments, obtain historical database information by using the field information corresponding to the log segment;

[0212] Construct an extraction prompt word with the historical database information and the log segment;

[0213] Use the extraction prompt word to guide a large language model to extract historical query pairs from the log segment to obtain SQL logs.

[0214] In some embodiments, the target module 250 is specifically configured to:

[0215] For each of the candidate SQL statements, execute the candidate SQL statement to obtain the execution data corresponding to the candidate SQL statement;

[0216] If the execution data does not contain error information, determine the candidate SQL statement as an intermediate SQL statement;

[0217] If the execution data contains error information, perform repair processing on the candidate SQL statement by using the error information;

[0218] If the repair is successful and the execution data of the repaired candidate SQL statement does not contain error information, use the repaired candidate SQL statement as the intermediate SQL statement;

[0219] Determine a target SQL statement from the intermediate SQL statements according to a preset rule.

[0220] In specific implementation, each of the above modules can be implemented as an independent entity, or can be combined arbitrarily to be implemented as the same or several entities. For the specific implementation of each of the above modules, reference can be made to the foregoing method embodiments, which will not be elaborated herein.

[0221] As can be seen from the above, the SQL statement generation device of this embodiment can extract preliminary field information from the preliminary SQL statement corresponding to the query text; determine the first supplementary field based on the semantic matching degree between the query text and the field dictionary; determine the second supplementary field based on the semantic similarity between the query text and the scenario data; optimize the preliminary SQL statement into a candidate SQL statement by integrating the preliminary field information, the first supplementary field, and the second supplementary field, and then determine the target SQL statement according to the execution data of the candidate SQL statement. By mining the first supplementary field and the second supplementary field associated with the query text from the scenario dictionary and the scenario database, the model can understand and infer the deep semantics related to the query text to establish a correct relationship with the fields in the database, thereby improving the accuracy of the target SQL.

[0222] An embodiment of the present invention further provides an electronic device, which may be a device such as a terminal, a server, etc. Among them, the terminal may be a mobile phone, a tablet computer, a smart Bluetooth device, a laptop computer, a personal computer, and so on; the server may be a single server or a server cluster composed of multiple servers, and so on.

[0223] In some embodiments, the SQL statement generation device may also be integrated in multiple electronic devices. For example, the SQL statement generation device may be integrated in multiple servers, and the method of the SQL statement generation device of the present invention may be implemented by multiple servers.

[0224] In this embodiment, the electronic device in this embodiment will be described in detail by taking the example that the electronic device is a server. For example, as Figure 6 shown, it shows a schematic structural diagram of the electronic device involved in the embodiment of the present invention. Specifically:

[0225] The electronic device may include a processor 310 with one or more processing cores, a memory 320 with one or more computer-readable storage media, a power supply 330, an input module 340, a communication module 350, and other components. Those skilled in the art can understand that Figure 6 the structure of the electronic device shown in does not constitute a limitation on the electronic device, and it may include more or fewer components than shown, or combine certain components, or have different component arrangements. Among them:

[0226] The processor 310 is the control center of the electronic device, connecting various parts of the entire electronic device through various interfaces and lines. By running or executing software programs and / or modules stored in the memory 320, and by calling data stored in the memory 320, it executes various functions of the electronic device and processes data. In some embodiments, the processor 310 may include one or more processing cores; in some embodiments, the processor 310 may integrate an application processor and a modem processor. Among them, the application processor mainly processes the operating system, user interface, application programs, etc., and the modem processor mainly processes wireless communication. It can be understood that the above-mentioned modem processor may not be integrated into the processor 310 either.

[0227] The memory 320 can be used to store software programs and modules. The processor 310 executes various functional applications and data processing by running the software programs and modules stored in the memory 320. The memory 320 may mainly include a program storage area and a data storage area. Among them, the program storage area can store an operating system, application programs required for at least one function (such as a sound playback function, an image playback function, etc.); the data storage area can store data created according to the use of the electronic device. In addition, the memory 320 may include high-speed random access memory and may also include non-volatile memory, such as at least one magnetic disk storage device, a flash memory device, or other volatile solid-state storage devices. Accordingly, the memory 320 may also include a memory controller to provide the processor 310 with access to the memory 320.

[0228] The electronic device further includes a power supply 330 for powering each component. In some embodiments, the power supply 330 may be logically connected to the processor 310 through a power management system, so as to implement functions such as management of charging, discharging, and power consumption management through the power management system. The power supply 330 may also include any components such as one or more DC or AC power supplies, a recharge system, a power failure detection circuit, a power converter or inverter, and a power status indicator.

[0229] The electronic device may further include an input module 340, which can be used to receive input digital or character information, and generate keyboard, mouse, joystick, optical or trackball signal inputs related to user settings and function controls.

[0230] The electronic device may further include a communication module 350. In some embodiments, the communication module 350 may include a wireless module. The electronic device can perform short-range wireless transmission through the wireless module of the communication module 350, thereby providing users with wireless broadband Internet access. For example, the communication module 350 can be used to help users send and receive emails, browse the web, and access streaming media, etc.

[0231] Although not shown, the electronic device may further include a display unit, etc., which will not be elaborated here. Specifically, in this embodiment, the processor 310 in the electronic device will load the executable files corresponding to the processes of one or more application programs into the memory 320 according to the following instructions, and the processor 310 will run the application programs stored in the memory 320, thereby implementing the steps in the methods of the embodiments of the present invention.

[0232] For the specific implementation of the above operations, reference can be made to the previous embodiments, which will not be elaborated here.

[0233] As described above, the electronic device provided by the embodiment of the present invention can extract preliminary field information from the preliminary SQL statement corresponding to the query text; determine the first supplementary field based on the semantic matching degree between the query text and the field dictionary; determine the second supplementary field based on the semantic similarity between the query text and the scenario data; optimize the preliminary SQL statement into a candidate SQL statement by integrating the preliminary field information, the first supplementary field, and the second supplementary field, and then determine the target SQL statement according to the execution data of the candidate SQL statement. The first supplementary field and the second supplementary field associated with the query text are mined from the scenario dictionary and the scenario database, so that the model can understand and infer the deep semantics related to the query text to establish a correct relationship with the fields in the database, thereby improving the accuracy of the target SQL.

[0234] Those of ordinary skill in the art can understand that all or part of the steps in the various methods of the above embodiments can be completed by instructions or by controlling related hardware through instructions. The instructions can be stored in a computer-readable storage medium and loaded and executed by a processor.

[0235] Therefore, an embodiment of the present invention provides a computer-readable storage medium, in which multiple instructions are stored, and the instructions can be loaded by a processor to execute the steps in any one of the SQL statement generation methods provided by the embodiments of the present invention.

[0236] Among them, the storage medium may include: read-only memory (ROM, Read Only Memory), random access memory (RAM, Random Access Memory), magnetic disk or optical disk, etc.

[0237] According to an aspect of the present invention, there is provided a computer program product or a computer program, the computer program product or the computer program includes computer programs / instructions, and the computer programs / instructions are stored in a computer-readable storage medium. The processor of the electronic device reads the computer programs / instructions from the computer-readable storage medium, and the processor executes the computer programs / instructions, so that the electronic device executes the methods provided in the various optional implementation manners of the SQL statement generation aspect in the above embodiments.

[0238] Since the instructions stored in the storage medium can execute the steps in any one of the SQL statement generation methods provided by the embodiments of the present invention, the beneficial effects that can be achieved by any one of the SQL statement generation methods provided by the embodiments of the present invention can be realized. For details, see the previous embodiments and will not be repeated here.

[0239] The above has introduced in detail a method and device for generating SQL statements provided by an embodiment of the present invention. Specific examples are used in this article to elaborate on the principle and implementation manner of the present invention. The description of the above embodiments is only used to help understand the method and its core idea of the present invention; at the same time, for those skilled in the art, according to the idea of the present invention, there will be changes in the specific implementation manner and application scope. In summary, the content of this specification should not be construed as a limitation to the present invention.

Claims

1. A method for generating a SQL statement, characterized in that: The method comprises: Filling the database information of the target database and the preliminary SQL statement corresponding to the query text into the extraction template to construct an extraction prompt word in a pseudo-function form, and using the extraction prompt word to guide the large language model to extract preliminary field information from the preliminary SQL statement, the extraction template is a preset prompt word template for extracting field information; Determining a first supplementary field from the field dictionary according to a semantic matching degree between the field dictionary and the query text, the field dictionary including a mapping relationship between a preset field and a preset interpretation in a scenario database, the preset interpretation being an explanation of the preset field in a corresponding scenario, the semantic matching degree including a first matching degree between the query text and the preset field and a second matching degree between the query text and the preset interpretation; Determining a second supplementary field from the scenario database based on semantic similarity between the query text and scenario data in the scenario database; The preliminary field information, the first supplementary field, and the second supplementary field are integrated to optimize the preliminary SQL statement into a plurality of candidate SQL statements, including: Deduplication processing is performed on the preliminary field information, the first supplementary field, and the second supplementary field to obtain a designated field, wherein the designated field includes a database name, a table name, and a field name; for each designated field, a number of non-null values ​​are extracted from the database according to the database name, the table name, and the field name to obtain designated data, wherein the length of each non-null value does not exceed a limited length; grammatical structure information in the preliminary SQL statement is extracted to obtain first structure information; each word segment in the query text is masked with a specified probability to obtain second structure information; an SQL log corresponding to the scenario database is obtained, wherein the SQL log includes a plurality of historical query pairs, wherein the historical query pair includes a historical query and a corresponding historical SQL statements; calculating the similarity between the first structural information and the second structural information and each historical query pair respectively, to obtain the first similarity and the second similarity of the historical query pair; determining example samples from multiple historical query pairs using the first similarity and the second similarity of each historical query pair; using the example samples, the specified data, the preliminary field information, the first supplementary field, and the second supplementary field as auxiliary optimization data; filling the auxiliary optimization data, the query text, the preliminary SQL statement, and the database information of the target database into an optimization template to obtain optimization prompt words; based on the optimization prompt words, guiding the large language model to optimize the preliminary SQL statement to obtain multiple optimized candidate SQL statements; The target SQL statement is determined from the plurality of candidate SQL statements using the execution data of each of the candidate SQL statements.

2. The method according to claim 1, characterized in that The determining the first supplementary field from the field dictionary according to the semantic matching degree between the field dictionary and the query text includes: Obtaining a standardized query obtained by standardizing the query text; For each preset field in the field dictionary, calculating a first matching degree between the standardized query and the preset field; Calculating a second degree of match between the standardized query and a preset interpretation corresponding to the preset field; In combination with the first matching degree and the second matching degree, a first supplementary field is determined from preset fields in the field dictionary.

3. The method according to claim 1, characterized in that The determining a second supplementary field from the scenario database based on the semantic similarity between the query text and the scenario data in the scenario database includes: Performing word segmentation processing on the standardized query corresponding to the query text to obtain multiple query words; Obtain the scene data corresponding to each scene field in the scene database; Mapping each of the scene data into a scene index using a specified hash function, and establishing an association relationship between the scene index and the scene field to obtain a hash table; Performing hash mapping processing on each of the query words using the specified hash function to obtain a query index; According to each of the query indexes, semantic query processing is performed in the hash table to obtain a second supplementary field.

4. The method according to claim 3, characterized in that The performing semantic query processing in the hash table according to each of the query indexes to obtain a second supplementary field includes: For each of the query indexes, taking a scene field in the hash table whose scene index is the same as the query index as a first scene field; Determine a second scenario field from the first scenario fields based on the semantic similarity between each of the first scenario fields and the query text; The second scenario fields corresponding to the query index are merged to obtain a second supplementary field.

5. The method according to claim 1, characterized in that Before obtaining the SQL log corresponding to the scenario database, the method further includes: Slice the operation log of the scene database to obtain multiple log segments; For each of the log segments, obtaining historical database information using field information corresponding to the log segment; Constructing extraction prompt words based on the historical database information and the log fragments; The extraction prompt words are used to guide the large language model to extract historical query pairs from the log segments to obtain SQL logs.

6. The method according to claim 1, characterized in that The step of using the execution data of each candidate SQL statement to determine a target SQL statement from the plurality of candidate SQL statements includes: For each of the candidate SQL statements, execute the candidate SQL statement to obtain execution data corresponding to the candidate SQL statement; If the execution data does not include error information, determining the candidate SQL statement as an intermediate SQL statement; If the execution data contains error information, repair the candidate SQL statement using the error information; If the repair is successful and the execution data of the repaired candidate SQL statement does not contain error information, the repaired candidate SQL statement is used as the intermediate SQL statement; A target SQL statement is determined from the intermediate SQL statements according to preset rules.

7. A device for generating SQL statements, the device being used to implement the method according to any one of claims 1 to 6, characterized in that: The device comprises: An extraction module, used to fill the database information of the target database and the preliminary SQL statement corresponding to the query text into an extraction template to construct an extraction prompt word in the form of a pseudo function, and use the extraction prompt word to guide the large language model to extract preliminary field information from the preliminary SQL statement, wherein the extraction template is a preset prompt word template for extracting field information; A first determination module is used to determine a first supplementary field from the field dictionary according to a semantic matching degree between the field dictionary and the query text, wherein the field dictionary includes a mapping relationship between a preset field and a preset interpretation in a scenario database, the preset interpretation is an explanation of the preset field in a corresponding scenario, and the semantic matching degree includes a first matching degree between the query text and the preset field and a second matching degree between the query text and the preset interpretation; A second determination module, configured to determine a second supplementary field from the scenario database based on semantic similarity between the query text and scenario data in the scenario database; An optimization module, used to merge the preliminary field information, the first supplementary field, and the second supplementary field to optimize the preliminary SQL statement into a plurality of candidate SQL statements, including: Deduplication processing is performed on the preliminary field information, the first supplementary field, and the second supplementary field to obtain a designated field, wherein the designated field includes a database name, a table name, and a field name; for each designated field, a number of non-null values ​​are extracted from the database according to the database name, the table name, and the field name to obtain designated data, wherein the length of each non-null value does not exceed a limited length; grammatical structure information in the preliminary SQL statement is extracted to obtain first structure information; each word segment in the query text is masked with a specified probability to obtain second structure information; an SQL log corresponding to the scenario database is obtained, wherein the SQL log includes a plurality of historical query pairs, wherein the historical query pair includes a historical query and a corresponding historical SQL statements; calculating the similarity between the first structural information and the second structural information and each historical query pair respectively, to obtain the first similarity and the second similarity of the historical query pair; determining example samples from multiple historical query pairs using the first similarity and the second similarity of each historical query pair; using the example samples, the specified data, the preliminary field information, the first supplementary field, and the second supplementary field as auxiliary optimization data; filling the auxiliary optimization data, the query text, the preliminary SQL statement, and the database information of the target database into an optimization template to obtain optimization prompt words; based on the optimization prompt words, guiding the large language model to optimize the preliminary SQL statement to obtain multiple optimized candidate SQL statements; The target module is used to determine a target SQL statement from the multiple candidate SQL statements by using the execution data of each candidate SQL statement.

Images