SQL statement generation method and device

Abstract

The invention provides an SQL statement generation method and device, and relates to the technical field of artificial intelligence. The method comprises the following steps: extracting preliminary field information from a preliminary SQL statement of a query text; determining a first supplementary field according to the semantic matching degree of the field dictionary and the query text; determining a second supplementary field according to the semantic similarity between the query text and the scene data; and fusing the preliminary field information, the first supplementary field and the second supplementary field, optimizing the preliminary SQL statement into candidate SQL statements, and determining a target SQL statement according to execution data of each candidate SQL statement. According to the method and the device, the first supplementary field and the second supplementary field which are associated with the query text are mined from the scene dictionary and the scene database, so that the model can understand and infer deep semantics associated with the query text so as to establish a correct relationship with the fields in the database, and the accuracy of the target SQL is further improved.

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] The target SQL statement is determined from the plurality of candidate SQL statements using the execution data of each candidate SQL statement.

[0012] On the other hand, the present invention also provides a SQL statement generating device, the device comprising:

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

[0014] A first determination module, configured 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;

[0015] 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;

[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 a plurality of candidate SQL statements;

[0017] 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.

[0018] On the other hand, the present invention further provides an electronic device, comprising a processor and a memory, wherein the memory stores a plurality of instructions; the processor loads 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 further provides a computer-readable storage medium, wherein the computer-readable storage medium stores a plurality of instructions, wherein 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 further provides a computer program product, including a computer program / instruction, which implements the steps in any one of the SQL statement generation methods provided by the present invention when executed by a processor.

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

[0022] 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 scene 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 scene dictionary and the scene 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. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative work.

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

[0025] Figure 2 It is a flowchart of a method for generating a SQL statement provided by an embodiment of the present invention;

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

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

[0028] Figure 5 It is a structural diagram of a SQL statement generating device provided by an embodiment of the present invention;

[0029] Figure 6 It is a schematic diagram of the structure of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

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

[0031] The present invention provides a method and device for generating SQL statements, which can mine the implicit semantic relationship between user queries and database information, and ensure that accurate SQL statements are generated to meet user needs.

[0032] It is understandable that in the specific implementation of the present invention, data related to user information, etc., needs to obtain user permission or consent, and the collection, use and processing of relevant data need to comply with relevant laws, regulations and standards of relevant countries and regions.

[0033] See also Figure 1 , showing a schematic diagram of an application scenario of the SQL statement generation method. The application scenario may include a terminal 101 and a server 102, where data can be exchanged between the terminal 101 and the server 102 via a network, and a corresponding application program may be installed on the terminal 101. The terminal 101 may be a mobile phone, a tablet computer, a smart Bluetooth device, a computer, a large screen device, a robot, etc.; the server 102 may be a single server or a server cluster consisting of multiple servers.

[0034] The user can send the query text to the server 102 through the terminal 101, and the server 102 can extract the preliminary field information from the preliminary SQL statement corresponding to the query text; determine the first supplementary field from the field dictionary according to the semantic matching between the field dictionary and the query text, wherein the field dictionary includes the mapping relationship between the preset field and the preset interpretation in the scenario database; determine the second supplementary field from the scenario database based on the semantic similarity between the query text and the scenario data in the scenario database; 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; use the execution data of each candidate SQL statement to determine the target SQL statement from the 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 a SQL statement is provided, such as Figure 2 As shown, the specific process of the 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 input parameters provided, 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 of the user's question question needs to be output, and no other content needs to be output.

[0048] """

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

[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

[0058] question: {Q_"normalized"}

[0059] SQL: "

[0060] Input the preset prompt words into the large language model to generate the preliminary SQL statement corresponding to the query text. 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.

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

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

[0063] Fill the preliminary SQL statement and the database information of the target database into the corresponding slots in the extraction template to obtain the extraction prompt word. Input the extraction prompt word into the large language model to guide the large language model to analyze and process the preliminary SQL statement and output the extracted preliminary field information.

[0064] It should be noted that the prompt words used in the embodiments of the present invention are all in pseudo-function form. Compared with prompt words in conventional text form, prompt words in pseudo-function form can reduce the hallucination of the large language model, enhance its understanding ability, and thus ensure the accuracy of the model output.

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

[0066] The field dictionary includes the mapping relationship between the preset fields and preset interpretations in the scenario database, and 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, which is 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 explanation or general explanation of the preset field in the corresponding scenario. The preset interpretation can be obtained after semantic understanding and analysis of the data under the preset field in the scenario data.

[0067] The field dictionary is in the form of a key-value pair, where the key is the preset field name and the value is the corresponding field definition. 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 that has a high semantic match with the query text, and may be a part of the SQL statement corresponding to the query text. The first supplementary field can be determined based on the semantic match between the field dictionary and the query text.

[0069] In some embodiments, when determining the first supplementary field from the field dictionary based on the semantic matching degree between the field dictionary and the query text, the preset field and the preset interpretation can be spliced ​​to obtain a spliced ​​text; the spliced ​​text and the query text are both vectorized to obtain a spliced ​​vector and a query vector; the cosine similarity between the spliced ​​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 a preset number of preset fields with the top rankings are selected as the first supplementary fields.

[0070] In some embodiments, in order to make full use of preset fields and field interpretations, the semantic matching degree may include a first matching degree and a second matching degree. According to the semantic matching degree between the field dictionary and the query text, when determining the first supplementary field from the field dictionary, it can be to obtain a standardized query obtained after standardizing the query text; for each preset field in the field dictionary, calculate the first matching degree between the standardized query and the preset field; calculate the second matching degree between the standardized query and the preset interpretation corresponding to the preset field; and determine the first supplementary field from the preset field of the field dictionary in combination with the first matching degree and the second matching degree.

[0071] Among them, see 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, which is the scenario data. The second supplementary field is a field in the scenario database that is similar to the query text and is likely to be part of the SQL statement corresponding to the query text. The second supplementary field can be determined using the query text and the scenario data.

[0078] In some embodiments, determining the second supplementary field from the scene database can be by performing word segmentation processing on the standardized query corresponding to the query text to obtain multiple query words; obtaining scene data corresponding to each scene field in the scene database; using a specified hash function to map each of the scene data to a scene index, and establishing an association relationship between the scene index and the scene field to obtain a hash table; using the specified hash function to perform hash mapping processing on each of the query words to obtain a query index; and performing semantic query processing in the hash table based on each of the query indexes to obtain the second supplementary field.

[0079] Among them, see Figure 4 , showing a schematic diagram of determining the second supplementary field. The standardized query is segmented, and common storage types in the database such as Chinese and English nouns, numbers, and dates are extracted, and the extracted segmented words are used as query words. For the scene database, the scene data stored under all scene fields in the scene database can be obtained.

[0080] For the scene data corresponding to each scene field, the scene data can be mapped to a scene index using a specified hash function. The specified hash function is a pre-set hash function that meets 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 scene data, the hash value corresponding to the scene data can be obtained. If there are two relatively similar scene data, the hash values ​​obtained after the two scene data are processed by the specified hash function are the same.

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

[0082] For each query word obtained from the query text, the same specified hash function can be used to map the query word to a hash value, which is recorded as a 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 query index, a scene field in the hash table with the same scene index as the query index is used as the first scene field; based on the similarity between each of the first scene fields and the query text, a second scene field is determined from the first scene field; and the second scene field corresponding to the query index is merged to obtain a second supplementary field.

[0083] For each query index, the query index can be used to search in the hash table, and the scene field with the same scene index as the query index is used as the first scene field. For each of the first scene fields, the semantic similarity between the query text and the first scene 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, where the greater the semantic similarity, the more similar the query text and the first scenario field are. The first scenario field with the greatest semantic similarity is used as the second scenario field. In this way, each query index can filter out the corresponding second scenario field, and after the second scenario fields of all query indexes are merged and deduplicated, they can be used as the second supplementary field.

[0085] By specifying a hash function, similar data can be mapped to the same hash value, that is, the scene fields in the scene database can be divided into multiple small sets, for example, Figure 4 In , each scene index may correspond to multiple scene fields, and the scene fields in each small set have 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 middle is the set corresponding to scene index 2. Only the small set is traversed to filter out the second supplementary field, which can effectively reduce the amount of calculation and improve processing efficiency.

[0086] S140: 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.

[0087] By utilizing 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 from the scenario database, the preliminary SQL statement can be supplemented and enhanced to achieve optimization of the preliminary SQL statement, so as to obtain multiple candidate SQL statements.

[0088] As an implementation mode, the preliminary SQL statement, the first supplementary field, and the second supplementary field may be directly input into the large language model, and the large language model may be used to perform analysis and processing to directly provide a plurality of candidate SQL statements.

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

[0090] 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 capabilities of the large language model and ensure that more accurate target SQL statements are obtained later.

[0091] As an implementation method, when generating auxiliary optimization data, the preliminary field information, the first supplementary field and the second supplementary field may be deduplicated to obtain designated fields; for each designated field, the designated data corresponding to the designated field may be obtained according to the database name, the table name and the field name; and the designated data, the preliminary field information, the first supplementary field and the second supplementary field may be used as auxiliary optimization data.

[0092] The preliminary field information, the first supplementary field and the second supplementary field are merged, and the repeated fields are removed to obtain the specified field. The specified field is obtained by sorting 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 ​​can be extracted in the database according to the table name and field name. The number of non-null values ​​to be extracted can be set according to actual needs. In an embodiment of the present invention, the number of extractions can be 5. If the number of non-null values ​​in the database is less than 5, all are extracted. For a single non-null value, its length is limited. If it exceeds the limit length, it is truncated. The limit length can also be set according to actual needs. In an embodiment of the present invention, the limit length is 50 characters. The extracted non-null values ​​can be organized in a dictionary form, that is, the key is the specified field value and the non-null value constitutes the specified data.

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

[0094] The optimization template is a preset prompt word template for generating optimization prompt words. The optimization template may include multiple slots to be filled, such as auxiliary slots, query slots, preliminary slots, database slots, etc. Auxiliary optimization data is filled into the auxiliary slots, query text is filled into the query slots, preliminary SQL statements are filled into the preliminary slots, and database information of the target database is filled into the database slots, and optimization prompt words can be obtained.

[0095] The optimization template provided by the embodiment of the present invention is as follows:

[0096] “### Role: You are a Python pseudocode interpreter. 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.

[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 preliminary_SQL can meet the quesiton requirements based on the user's question and preliminary_SQL, combined with other parameter information provided. If not, modify it based on other parameter information.

[0099] preliminary_SQL:str is the preliminary SQL, which needs to be combined with question and other parameter information to evaluate its correctness.

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

[0101] updated_column_names:str is the possible alternative SQL database name | table name | column name.

[0102] LSH_column_names:str is a set of SQL database names, table names, and column names that are similar in value to the content of the user's question.

[0103] few-shot:str is a reference to some similar cases.

[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 of some field value samples.

[0107] return_SQL:str is the answer to the task. Only the answers that can be accurately executed by the SQL terminal are output.

[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] The optimization hint words are input into the large language model so that the large language model can optimize the preliminary SQL statement using the information provided in the optimization hint words. In order 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 implementations, adding example samples to the prompt words can improve the model's understanding ability to a certain extent. In order to further improve the accuracy of the candidate SQL statements, the auxiliary optimization data can also include example samples. Adding the example samples to the optimization prompt words as part of the auxiliary optimization data can improve the understanding of the large language model, thereby improving the accuracy of the candidate SQL statements.

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

[0121] For the preliminary SQL statement, a regular expression can be written to extract grammatical 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 postsp.JOIN votes v ON p.Id = v.PostId WHERE v.UserId = 1465 ORDER BY p.Score DESCLIMIT 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 in the query text may be masked according to a specified probability to obtain the second structural information. The specified probability may be set according to actual needs. In the embodiment of the present invention, the specified probability may be 10%. Specifically, each word in the query text may be randomly masked with an independent probability of 10% to obtain the second structural information. For example, the second structural information corresponding to the query text in the above example may be "Among the posts _ werevoted by user _, what is the id of the most valuable _?".

[0123] Among them, the SQL log of the scene database can be generated offline in advance and stored in a designated location. It can be directly taken out from the designated location when needed, or it can be generated in real time when needed. In an embodiment of the present invention, in order to improve the generation efficiency of the target SQL, a pre-offline generation method is adopted. That is, before obtaining the SQL log of the scene database, the operation log of the scene database can also be sliced ​​to obtain multiple log fragments; for each of the log fragments, the historical database information is obtained using the field information corresponding to the log fragment; with all the historical database information and the log fragments, the extraction prompt words are constructed; using the extraction prompt words, the large language model is guided to extract historical query pairs from the log fragments to obtain the SQL log.

[0124] The operation log of the scene database in the historical time period is obtained, and the operation log is sliced ​​into sections to obtain multiple log fragments. The historical time period is the time period before the current time, which can be set according to actual needs.

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

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

[0127] “### Role: You are a Python pseudocode interpreter. 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.

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

[0129] """The task you need to complete is to extract possible SQL statements from raw_sentence according to the schema table information, parse and infer the semantics of raw_sentence, summarize the purpose and requirements corresponding to the SQL statement, and change the requirements that SQL can meet into questions and output them. If SQL cannot be extracted, "Unable to extract SQL." is returned.

[0130] raw_sentence:str may contain SQL execution statements. If there are SQL execution statements, the SQL statements will be extracted and output.

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

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

[0133] question: Please output the requirement description corresponding to extracted_SQL and rewrite it into a question sentence.

[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 answer to extracted_SQL and question.

[0138] # extracted_SQL:

[0139] # question: "

[0140] The extraction prompt words are input into the large language model to guide the large language model to extract the historical SQL and the historical query corresponding to the historical SQL as the historical query pair, and all the historical query pairs are used as SQL logs.

[0141] The SQL log may include multiple historical query pairs, each of which includes a historical query and a historical SQL statement corresponding to the historical query. The first structure information is subjected to cosine similarity calculation with each historical query pair to obtain the first similarity of each historical query pair, and the second structure is subjected to cosine similarity calculation with each historical query pair to obtain the second similarity of each historical query pair.

[0142] By using the first similarity and the second similarity of the historical query pairs, the historical query pairs can be sorted respectively, and the first 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, which can be set according to actual needs.

[0143] The example samples are used as part of the auxiliary optimization data, and optimization hint words are generated in the above-mentioned manner. The optimization hint words are input into the large language model to optimize the preliminary SQL statements, so as to obtain multiple candidate SQL statements output by the large language model.

[0144] S150: Determine a target SQL statement from the plurality of candidate SQL statements using the execution data of each candidate SQL statement.

[0145] In order to ensure the accuracy of the target SQL statement, the obtained candidate SQL statement needs to be verified to ensure that the candidate SQL statement 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 mode, when determining the target SQL statement from the candidate SQL statements, each candidate SQL statement may be executed to obtain the execution data corresponding to the candidate SQL statement; if the execution data does not contain error information, the candidate SQL statement is determined as an intermediate SQL statement; if the execution data contains error information, the candidate SQL statement is repaired 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; and the target SQL statement is determined from the intermediate SQL statements according to preset rules.

[0147] For each candidate SQL statement, the candidate SQL statement can be executed and corresponding execution data can be obtained, and the execution data can include execution time, execution duration and result. The result can include execution success or execution error, and if the execution error occurs, the corresponding error message 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 the execution of the candidate SQL statement is abnormal. The error information can be used to try to repair the candidate SQL statement using the semantic parsing capability of the large language model.

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

[0150] Among them, the repair prompt word is a repair template pre-set, and the repair prompt word can be obtained by filling the corresponding data into the repair template. The repair template provided in the embodiment of the present invention is as follows:

[0151] “### Role: You are a Python pseudocode interpreter. 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.

[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 problematic SQL statements based on user questions and problematic SQL, combined with the provided terminal error information and schema information.

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

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

[0156] bug_sql:str is the problematic SQL statement, which needs to be fixed in syntax.

[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 to the task. Only the answers that can be accurately executed by the SQL terminal are output.

[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 repaired candidate SQL statement still contains error information, the error information can be used to continue repairing 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 above content, it can be known that the intermediate SQL statement can be the original candidate SQL statement or the repaired candidate SQL statement. A repair mark can be added to distinguish the repaired candidate SQL statement.

[0169] As an implementation method, one can directly select any one of the intermediate SQL statements as the target SQL statement. As another implementation method, in order to ensure the best execution effect of the target SQL statement, the number of occurrences of each intermediate SQL statement can be counted, and the intermediate SQL statement with the most occurrences can be used as the target SQL statement; if the number of occurrences is the same, the intermediate SQL statement with the shortest execution time can be used as the target SQL statement; if the execution time is the same, a random selection from the intermediate SQLs without a repair mark is preferentially selected as the target SQL statement.

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

[0171] The method provided by the embodiment of the present invention can mine a first supplementary field related to the query text from a field dictionary pre-constructed based on data in a scenario database in combination with the interpretation of the field, and mine a second supplementary field related to the query text using the data specifically stored in the scenario database. The first supplementary field and the second supplementary field can be used in subsequent optimization of preliminary SQL statements to enhance the model's ability to understand and reason about the query text, so as to mine fields that have an implicit relationship with the query text, supplement or correct the SQL statement, and thereby improve the accuracy of the generated target SQL statement.

[0172] In order to better implement the above method, an embodiment of the present invention further provides a SQL statement generation device, which can be integrated in an electronic device, and the electronic device can be a terminal, a server, etc. Among them, the terminal can be a mobile phone, a tablet computer, a smart Bluetooth device, a laptop, a personal computer, etc.; the server can be a single server or a server cluster composed of multiple servers.

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

[0174] For example, Figure 5 As shown, the SQL statement generating 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] An extraction module 210, for extracting preliminary field information from a preliminary SQL statement corresponding to the query text;

[0176] A first determination module 220, configured to determine a first supplementary field from the field dictionary according to a semantic match 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;

[0177] A second determination module 230, 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] An optimization module 240, configured to fuse 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;

[0179] The target module 250 is used 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 determining module 220 is specifically configured to:

[0181] Obtaining a standardized query obtained by standardizing the query text;

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

[0183] Calculating a second degree of match between the standardized query and a preset interpretation corresponding to the preset field;

[0184] 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.

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

[0186] Performing word segmentation processing on the standardized query corresponding to the query text to obtain multiple query words;

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

[0188] 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;

[0189] Performing hash mapping processing on each of the query words using the specified hash function to obtain a query index;

[0190] According to each of the query indexes, semantic query processing is performed in the hash table to obtain a second supplementary field.

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

[0192] 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;

[0193] 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;

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

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

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

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

[0198] Based on the optimization prompt words, the large language model is guided to optimize the preliminary SQL statement to obtain multiple optimized candidate SQL statements.

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

[0200] 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;

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

[0202] The designated data, the preliminary field information, the first supplementary field, and the second supplementary field are used 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, which is specifically used to:

[0204] Extracting grammatical structure information from the preliminary SQL statement to obtain first structure information;

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

[0206] Obtaining an SQL log corresponding to a scenario database, wherein the SQL log includes a plurality of historical query pairs, wherein the historical query pairs include historical queries and corresponding historical SQL statements;

[0207] Calculating the similarity between the first structural information, the second structural information and each historical query pair respectively, to obtain a first similarity and a second similarity of the historical query pair;

[0208] An example sample is determined from the plurality of historical query pairs using the first similarity and the second similarity of each historical query pair.

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

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

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

[0212] Constructing extraction prompt words based on the historical database information and the log fragments;

[0213] 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.

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

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

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

[0217] If the execution data contains error information, repair the candidate SQL statement 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, the repaired candidate SQL statement is used as the intermediate SQL statement;

[0219] A target SQL statement is determined from the intermediate SQL statements according to preset rules.

[0220] In specific implementation, the above modules can be implemented as independent entities, or can be arbitrarily combined and implemented as the same or several entities. The specific implementation of the above modules can be found in the previous method embodiments, which will not be repeated here.

[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 scene 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 scene dictionary and the scene 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.

[0222] The embodiment of the present invention further provides an electronic device, which may be a terminal, a server, or the like. The terminal may be a mobile phone, a tablet computer, a smart Bluetooth device, a notebook computer, a personal computer, or the like; the server may be a single server or a server cluster composed of multiple servers, or the like.

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

[0224] In this embodiment, the electronic device of this embodiment is a server as an example for detailed description, for example, Figure 6 As shown, it shows a schematic diagram of the structure of an electronic device involved in an embodiment of the present invention, specifically:

[0225] The electronic device may include components such as 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, and a communication module 350. Those skilled in the art will appreciate that Figure 6 The electronic device structure shown in the figure does not constitute a limitation on the electronic device, and may include more or fewer components than shown in the figure, or combine certain components, or arrange the components differently.

[0226] The processor 310 is the control center of the electronic device. It uses various interfaces and lines to connect various parts of the entire electronic device. It executes various functions of the electronic device and processes data by running or executing software programs and / or modules stored in the memory 320, and calling data stored in the memory 320. 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, wherein the application processor mainly processes the operating system, user interface, and application programs, and the modem processor mainly processes wireless communications. It is understandable that the above-mentioned modem processor may not be integrated into the processor 310.

[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, wherein the program storage area may store an operating system, an application required for at least one function (such as a sound playback function, an image playback function, etc.), etc.; the data storage area may store data created according to the use of the electronic device, etc. In addition, the memory 320 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other 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 also includes a power supply 330 for supplying power to various components. In some embodiments, the power supply 330 can be logically connected to the processor 310 through a power management system, so as to manage charging, discharging, and power consumption through the power management system. The power supply 330 can also include any components such as one or more DC or AC power supplies, recharging systems, power failure detection circuits, power converters or inverters, and power status indicators.

[0229] The electronic device may further include an input module 340, which may 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 may perform short-range wireless transmission through the wireless module of the communication module 350, thereby providing the user with wireless broadband Internet access. For example, the communication module 350 may be used to help the user send and receive emails, browse web pages, and access streaming media.

[0231] Although not shown, the electronic device may further include a display unit, etc., which will not be described in detail herein. 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] The specific implementation of the above operations can be found in the previous embodiments, which will not be described in detail here.

[0233] As can be seen from the 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 scene 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 scene dictionary and the scene 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] A person of ordinary skill in the art will appreciate that all or part of the steps in the various methods of the above embodiments may be completed by instructions, or by controlling related hardware through instructions. The instructions may be stored in a computer-readable storage medium and loaded and executed by a processor.

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

[0236] The storage medium may include: a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk, etc.

[0237] According to one aspect of the present invention, a computer program product or computer program is provided, the computer program product or computer program including a computer program / instruction, the computer program / instruction being stored in a computer-readable storage medium. A processor of an electronic device reads the computer program / instruction from the computer-readable storage medium, and the processor executes the computer program / instruction, so that the electronic device executes the method provided in various optional implementations of the SQL statement generation provided in the above embodiments.

[0238] Since the instructions stored in the storage medium can execute the steps in any SQL statement generation method provided in the embodiments of the present invention, the beneficial effects that can be achieved by any SQL statement generation method provided in the embodiments of the present invention can be achieved. Please refer to the previous embodiments for details and will not be repeated here.

[0239] The above is a detailed introduction to a method and device for generating SQL statements provided in an embodiment of the present invention. Specific examples are used herein to illustrate the principles and implementation methods of the present invention. The description of the above embodiments is only used to help understand the method and 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 may be changes in the specific implementation method and application scope. In summary, the content of this specification should not be understood as limiting the present invention.

Claims

1. A method for generating a SQL statement, characterized in that: The method comprises: Extract preliminary field information from the preliminary SQL statement corresponding to the query text; 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 a preset field and a preset interpretation in a scenario database; Determining a second supplementary field from the scenario database based on semantic similarity between the query text and scenario data in the scenario database; 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; The target SQL statement is determined from the plurality of candidate SQL statements using the execution data of each candidate SQL statement.

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 The 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 includes: Fusion the preliminary field information, the first supplementary field, and the second supplementary field to generate auxiliary optimization data; Filling the auxiliary optimization data, the query text, the preliminary SQL statement and the database information of the target database into the optimization template to obtain optimization prompt words; Based on the optimization prompt words, the large language model is guided to optimize the preliminary SQL statement to obtain multiple optimized candidate SQL statements.

6. The method according to claim 5, characterized in that The fusing the preliminary field information, the first supplementary field, and the second supplementary field to generate auxiliary optimization data includes: 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 of the designated fields, acquiring designated data corresponding to the designated field according to the database name, the table name, and the field name; The designated data, the preliminary field information, the first supplementary field, and the second supplementary field are used as auxiliary optimization data.

7. The method according to claim 6, characterized in that The auxiliary optimization data also includes example samples, and the method further includes: Extracting grammatical structure information from the preliminary SQL statement to obtain first structure information; Performing masking processing on each word segment in the query text with a specified probability to obtain second structure information; Obtaining an SQL log corresponding to a scenario database, wherein the SQL log includes a plurality of historical query pairs, wherein the historical query pairs include historical queries and corresponding historical SQL statements; Calculating the similarity between the first structural information, the second structural information and each historical query pair respectively, to obtain a first similarity and a second similarity of the historical query pair; An example sample is determined from the plurality of historical query pairs using the first similarity and the second similarity of each historical query pair.

8. The method according to claim 7, 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.

9. 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.

10. A device for generating SQL statements, the device being used to implement the method according to any one of claims 1 to 9, characterized in that: The device comprises: An extraction module, used to extract preliminary field information from a preliminary SQL statement corresponding to the query text; A first determination module, configured 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; 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, configured to fuse 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; 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