SQL (Structured Query Language) statement generation method and device, computer equipment and readable storage medium

Abstract

The invention relates to an SQL statement generation method and device, computer equipment, a computer readable storage medium and a computer program product. The method comprises the steps of obtaining a to-be-queried statement, and determining a historical query statement matched with the to-be-queried statement; based on an SQL statement corresponding to the historical query statement, generating first prompt information for the to-be-queried statement; performing entity identification on the to-be-queried statement to obtain a to-be-queried entity in the to-be-queried statement; aligning a to-be-queried entity in the to-be-queried statement with a preset entity in a preset data table to obtain description information of the preset entity aligned with the to-be-queried entity in the preset data table; generating second prompt information for the to-be-queried statement based on the description information; and generating an SQL statement corresponding to the to-be-queried statement according to the first prompt information and the second prompt information. By adopting the method, the requirement of manually compiling the SQL statement by a user can be reduced, and the error rate is reduced. And the accuracy and efficiency of conversion from the natural language to the SQL statement are improved.

Description

Technical Field

[0001] The present application relates to the field of computer technology, and in particular to a method, apparatus, computer device, computer-readable storage medium and computer program product for generating SQL statements. Background Art

[0002] Natural language expressions are often imprecise and have multiple possibilities for interpretation, while SQL statements require very clear grammatical structures to ensure the accuracy of queries. Therefore, the significance of converting natural language into SQL statements is to be able to obtain the required information from the database more efficiently and ensure that the query results are accurate. It can also greatly improve the user experience and support a wider range of business intelligence applications.

[0003] However, the current way to convert natural language into SQL statements is usually to define abstract SQL templates and information extraction rules to convert the input natural language into SQL statements that meet the templates. This conversion method requires pre-definition of templates for each type of SQL, which requires a large number of templates, and each template can only cover a limited number of SQL statements, and there is cross-coverage, which greatly affects the efficiency and accuracy of converting natural language into SQL statements. Summary of the invention

[0004] Based on this, it is necessary to provide a SQL statement generation method, apparatus, computer device, computer-readable storage medium and computer program product that can improve the efficiency and accuracy of converting natural language into SQL statements in order to address the above technical problems.

[0005] In a first aspect, the present application provides a method for generating a SQL statement, comprising:

[0006] Obtain a query statement to be queried, and determine a historical query statement that matches the query statement to be queried;

[0007] Based on the SQL statement corresponding to the historical query statement, generate first prompt information for the statement to be queried;

[0008] Performing entity recognition on the query statement to obtain the query entity in the query statement;

[0009] Aligning the entity to be queried in the query statement with the preset entity in the preset data table, and obtaining description information of the preset entity aligned with the entity to be queried in the preset data table;

[0010] generating second prompt information for the query statement based on the description information;

[0011] Generate an SQL statement corresponding to the query statement according to the first prompt information and the second prompt information.

[0012] In one embodiment, determining the historical query statement matching the query statement includes:

[0013] Acquire a historical answer record, wherein the historical answer record includes a plurality of candidate historical query statements; for each of the plurality of candidate historical query statements, determine the similarity between the candidate historical query statement and the statement to be queried; based on the determined similarity between each candidate historical query statement and the statement to be queried, select a candidate historical query statement among the plurality of candidate historical query statements that has the greatest similarity to the statement to be queried as a historical query statement that matches the statement to be queried.

[0014] In one embodiment, the generating first prompt information for the query statement based on the SQL statement corresponding to the historical query statement includes:

[0015] In the historical answer records, search for an SQL statement corresponding to a candidate historical query statement with the greatest similarity to the statement to be queried; and use the SQL statement corresponding to the candidate historical query statement with the greatest similarity to the statement to be queried as the first prompt information of the statement to be queried.

[0016] In one embodiment, performing entity recognition on the query statement to obtain the query entity in the query statement includes:

[0017] Obtain a preset entity type, wherein the preset entity type includes at least one of a preset business type and a preset field type; classify the statement to be queried according to the preset business type and the preset field type to obtain a plurality of classified entity results; extract the statement to be queried according to the plurality of classified entity results to obtain an entity to be queried in the statement to be queried.

[0018] In one embodiment, aligning the entity to be queried in the query statement with the preset entity in the preset data table to obtain description information of the preset entity aligned with the entity to be queried in the preset data table includes:

[0019] The to-be-queried entity in the to-be-queried statement and the preset entity in the preset data table are matched for similarity to obtain a similarity value; a similarity threshold is obtained, and the to-be-queried entity and the preset entity whose similarity value is greater than the similarity threshold are regarded as a similar entity pair; and description information of the preset entity corresponding to the to-be-queried entity in the similar entity pair in the preset data table is obtained.

[0020] In one embodiment, generating a SQL statement corresponding to the query statement according to the first prompt information and the second prompt information includes:

[0021] According to the entity to be queried in the statement to be queried and the first prompt information, determine the replacement entity in the entity to be queried and the entity to be replaced in the first prompt information; replace the entity to be replaced in the first prompt information with the replacement entity in the entity to be queried, and obtain an initial SQL statement; according to the entity to be queried in the similar entity pair recorded in the second prompt information, the preset entity and the description information of the preset entity in the preset data table, replace the entity to be queried in the similar entity pair appearing in the initial SQL statement with the preset entity, and obtain a SQL statement corresponding to the statement to be queried.

[0022] In a second aspect, the present application also provides a SQL statement generating device, comprising:

[0023] A determination module, used to obtain a query statement and determine a historical query statement that matches the query statement;

[0024] A first generating module, used for generating first prompt information for the query statement based on the SQL statement corresponding to the historical query statement;

[0025] An identification module, used to perform entity identification on the query statement to obtain the query entity in the query statement;

[0026] An alignment module, used to align the entity to be queried in the query statement with the preset entity in the preset data table, and obtain description information of the preset entity aligned with the entity to be queried in the preset data table;

[0027] A second generating module, used to generate second prompt information for the query statement based on the description information;

[0028] The third generating module is used to generate an SQL statement corresponding to the query statement according to the first prompt information and the second prompt information.

[0029] In a third aspect, the present application further provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and when the processor executes the computer program, the following steps are implemented:

[0030] Obtain a query statement to be queried, and determine a historical query statement that matches the query statement to be queried;

[0031] Based on the SQL statement corresponding to the historical query statement, generate first prompt information for the statement to be queried;

[0032] Performing entity recognition on the query statement to obtain the query entity in the query statement;

[0033] Aligning the entity to be queried in the query statement with the preset entity in the preset data table, and obtaining description information of the preset entity aligned with the entity to be queried in the preset data table;

[0034] generating second prompt information for the query statement based on the description information;

[0035] Generate an SQL statement corresponding to the query statement according to the first prompt information and the second prompt information.

[0036] In a fourth aspect, the present application further provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, the following steps are implemented:

[0037] Obtain a query statement to be queried, and determine a historical query statement that matches the query statement to be queried;

[0038] Based on the SQL statement corresponding to the historical query statement, generate first prompt information for the statement to be queried;

[0039] Performing entity recognition on the query statement to obtain the query entity in the query statement;

[0040] Aligning the entity to be queried in the query statement with the preset entity in the preset data table, and obtaining description information of the preset entity aligned with the entity to be queried in the preset data table;

[0041] generating second prompt information for the query statement based on the description information;

[0042] Generate an SQL statement corresponding to the query statement according to the first prompt information and the second prompt information.

[0043] In a fifth aspect, the present application further provides a computer program product, including a computer program, which implements the following steps when executed by a processor:

[0044] Obtain a query statement to be queried, and determine a historical query statement that matches the query statement to be queried;

[0045] Based on the SQL statement corresponding to the historical query statement, generate first prompt information for the statement to be queried;

[0046] Performing entity recognition on the query statement to obtain the query entity in the query statement;

[0047] Aligning the entity to be queried in the query statement with the preset entity in the preset data table, and obtaining description information of the preset entity aligned with the entity to be queried in the preset data table;

[0048] generating second prompt information for the query statement based on the description information;

[0049] Generate an SQL statement corresponding to the query statement according to the first prompt information and the second prompt information.

[0050] The above-mentioned SQL statement generation method, device, computer equipment, computer-readable storage medium and computer program product obtain the query statement to be queried and determine the historical query statement that matches the query statement to be queried; if there are similar historical query records, these records can be directly used to quickly build SQL statements to avoid re-analyzing the entire query. Based on the SQL statement corresponding to the historical query statement, a first prompt information for the query statement to be queried is generated; using the historical SQL statement as a reference can reduce the parsing burden of new queries. Entity recognition is performed on the query statement to be queried to obtain the query entity in the query statement to be queried; different entities may correspond to different tables or fields, and this step can make the system more flexible in handling different types of queries. The query entity in the query statement to be queried is aligned with the preset entity in the preset data table to obtain the description information of the preset entity aligned with the query entity in the preset data table; through entity alignment, it is ensured that the query operation is only for related data tables and fields. It is clear which entities are associated with which parts of the database, which can reduce errors caused by misunderstandings. Second prompt information for the query statement to be queried is generated based on the description information; the prompt information can help users understand how their queries are understood and what adjustments may need to be made. According to the first prompt information and the second prompt information, an SQL statement corresponding to the query statement is generated. By automating the generation process, the need for users to manually write SQL statements is reduced, and the error rate is reduced. The accuracy and efficiency of the conversion from natural language to SQL statements are improved, and the user experience is also improved. In addition, this conversion mechanism can be widely used in various business scenarios, especially in data query systems that require frequent interaction with non-technical users, which enhances the applicability of statements. BRIEF DESCRIPTION OF THE DRAWINGS

[0051] In order to more clearly illustrate the technical solutions in the embodiments of the present application or related technologies, the drawings required for use in the embodiments of the present application or related technical descriptions will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without paying creative work.

[0052] FIG1 is an application environment diagram of a method for generating SQL statements in one embodiment;

[0053] FIG2 is a schematic diagram of a flow chart of a method for generating a SQL statement in one embodiment;

[0054] FIG3 is a schematic diagram of feedback of a method for generating SQL statements in one embodiment;

[0055] FIG4 is a schematic diagram of a flow chart of a method for generating a SQL statement in another embodiment;

[0056] FIG5 is a block diagram of a SQL statement generating device according to an embodiment;

[0057] FIG. 6 is a diagram showing the internal structure of a computer device in one embodiment. DETAILED DESCRIPTION

[0058] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0059] The SQL statement generation method provided in the embodiment of the present application can be applied in the application environment shown in FIG. 1. Among them, the terminal 102 communicates with the server 104 through the network. The data storage system can store the data that the server 104 needs to process. The data storage system can be integrated on the server 104, or it can be placed on the cloud or other network servers. The terminal 102 generates a SQL statement generation request, and sends the SQL statement generation to the server 104, so that the server 104 generates an SQL statement corresponding to the query statement according to the first prompt information and the second prompt information. Among them, the terminal 102 can be, but is not limited to, various personal computers, laptops, smart phones, tablet computers, Internet of Things devices and portable wearable devices. The Internet of Things devices can be smart speakers, smart TVs, smart air conditioners, smart car devices, projection devices, etc. Portable wearable devices can be smart watches, smart bracelets, head-mounted devices, etc. The head-mounted device can be a virtual reality (VR) device, an augmented reality (AR) device, smart glasses, etc. The server 104 can be an independent physical server, or a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides cloud computing services.

[0060] In an exemplary embodiment, as shown in FIG2 , a method for generating a SQL statement is provided, which is described by taking the method applied to the server 104 in FIG1 as an example, and includes the following steps 202 to 212. Among them:

[0061] Step 202: Obtain the query statement to be queried, and determine the historical query statements that match the query statement to be queried.

[0062] The query statement is a natural language query input by the user, that is, a natural language expression proposed by the user when he wants to query the database through SQL. For example, "What is the total revenue in August 2024?" is a query statement.

[0063] Historical query statements refer to natural language queries that have been processed before and successfully converted into SQL statements. These historical query statements and their corresponding SQL statements are recorded so that they can be used as references when processing similar queries later. For example, "What is the total revenue of the Nanshan branch in July 2024?" and its corresponding SQL statement are examples of historical query statements.

[0064] Specifically, the user first enters a natural language query, i.e., the query statement. Then, through the similarity retrieval algorithm, the historical query statements similar to the query statement are searched in the Q&A (historical question and answer library). For example, the user enters a natural language query, such as: "What is the total revenue in August 2024?" This is the so-called "query statement". Search for historical query statements similar to the current query statement in the existing Q&A library.

[0065] Step 204: Generate first prompt information for the query statement based on the SQL statement corresponding to the historical query statement.

[0066] The SQL statement corresponding to the historical query statement refers to the SQL statement corresponding to the query statement similar to the query statement input by the user in the historical Q&A pairs.

[0067] The first prompt information refers to information generated based on the SQL statement corresponding to the historical query statement, which is used to guide the large model to generate new SQL statements.

[0068] Specifically, first search the historical Q&A pairs for the SQL statement corresponding to the historical query statement. Then use the historical query statement and its corresponding SQL statement as the first prompt information. It can be understood that this first prompt information will be used to guide the large model to generate new SQL statements. For example, the SQL statement corresponding to the historical query statement can be: "select SUM(amount) from INCOME_DETAIL where data > '2024-06-30' and data <= '2024-07-31' and store='Nanshan Branch'".

[0069] Step 206: perform entity recognition on the query statement to obtain the query entity in the query statement.

[0070] The query entity refers to the specific object or concept mentioned by the user in the natural language query statement, which needs to be found in the database. In simple terms, the "query entity" is the keyword or key phrase in the information that the user wants to query, which usually refers to specific data or entities.

[0071] Specifically, first, entity information is extracted from the input query statement. For example, entities such as "Nanshan Branch", "", and "July 2024" are identified from the input query statement.

[0072] Then classify the identified entities, such as time type, amount type, landmark type, organization type, etc. For example, classify "Nanshan Branch" as a store name, "" as a brand name, and classify "July 2024" as a date.

[0073] Finally, the result of entity recognition (i.e., the entity to be queried) is formed, and the entity type and entity information are recorded. For example, the following entities to be queried are generated: entity type: store name, entity information: Nanshan branch; entity type: brand name, entity information:; entity type: date, entity information: July 2024.

[0074] Step 208: align the entity to be queried in the query statement with the preset entity in the preset data table, and obtain description information of the preset entity aligned with the entity to be queried in the preset data table.

[0075] The preset entities are entities defined in the preset data table, that is, fields in the data table or specific values ​​related to the fields. These entities are known information and are used to match entities in the natural language query input by the user.

[0076] A preset data table refers to a defined database table structure, including table name, field name, field type, etc. This information can help you understand the structure of the data and thus better generate SQL statements.

[0077] Descriptive information refers to the natural language description added to help better understand the data table structure and field meaning. These descriptive information can help the model identify and match entities more accurately. For example, for the field amount in the preset data table, its description information is "amount field, recording the revenue amount".

[0078] Specifically, first check the structural information of the preset data table, for example: table name: INCOME_DETAIL; field: amount (amount field); field: data (accounting time of income); field: store (store name). Then align the identified entity to be queried with the preset entity in the preset data table. For example, check whether the actual value in the identified entity "Nanshan Branch" is consistent or similar to the actual value in the entity "store" field in the data table. If it is found that there is no exact "Nanshan Branch" in the data table, but there is "Shenzhen Nanshan Branch", the two are considered to be aligned through semantic similarity calculation. Finally, obtain the descriptive information associated with the aligned entity. For example, for the field store, if the aligned entity is "Shenzhen Nanshan Branch", obtain its descriptive information, such as "store name".

[0079] Step 210: Generate second prompt information for the query statement based on the description information.

[0080] Specifically, first, the second prompt information is generated based on the description information after the entity alignment. Specifically, it includes: using the aligned entity to be queried, the preset entity and the description information of the preset entity in the preset data table as the second prompt information of the query statement. The role of the second prompt information is that through the aligned entity to be queried and the preset entity in the second prompt information, the actual entity existing in the database can be determined, and through the description information in the second prompt information, the description information of the entity in the database (such as "store name") can be determined to help correctly construct the SQL query statement. The purpose of doing this is to ensure the accuracy of the SQL statement, even if the vocabulary used by the user is not completely consistent with the data stored in the database, accurate query can be achieved.

[0081] The second prompt information refers to the entity description information obtained after entity alignment, which guides how to use these entity information in SQL statements. In this process, the description information helps determine which field should be used and the value of the field, thereby helping to generate the correct SQL statement. For example, the second prompt information can be: "Use the field store in the SQL statement, and its value is 'Shenzhen Nanshan Branch'".

[0082] Step 212: Generate an SQL statement corresponding to the query statement according to the first prompt information and the second prompt information.

[0083] Specifically, combine the first hint information (structural example) and the second hint information (entity specific information) to generate the final SQL statement, and then query the database. For example, the generated SQL statement is select SUM(amount) from INCOME_DETAIL where data > '2024-06-30' and data <= '2024-07-31' and store='Shenzhen Nanshan Branch'.

[0084] In one example, first assume that the query statement entered by the user is: "What is the total revenue of the Nanshan Branch in August 2024?". Then find a similar query statement in the historical Q&A pairs: "What is the total revenue of the Nanshan Branch in July 2024?" and its corresponding SQL statement: "select SUM((revenue amount))from(revenue amount))from(revenue table) where (recording time)>′2024−06−30′and(recording time)>′2024−06−30′and(recording time)<='2024-07-31' and $(store name) = ‘Nanshan Branch’". Next, identify the entity information: "Nanshan Branch", "" and "August 2024". After entity recognition, perform entity alignment, for example, align "Nanshan Branch" with "Shenzhen Nanshan Branch" in the data table. Finally, generate the final SQL statement: "select SUM(amount) from INCOME_DETAIL where data > '2024-07-31' and data <= '2024-08-31' and store='Shenzhen Nanshan Branch'".

[0085] In one of the embodiments, obtain the historical answer records, where the historical answer records include multiple candidate historical query statements; for one of the multiple candidate historical query statements, determine the similarity between the targeted candidate historical query statement and the query statement to be queried; according to the similarity determined between each candidate historical query statement and the query statement to be queried, use the candidate historical query statement with the greatest similarity to the query statement to be queried among the multiple candidate historical query statements as the historical query statement that matches the query statement to be queried.

[0086] Among them, the historical answer record refers to a set of cases where natural language queries have been successfully converted into SQL statements in the past. These records store the query questions (in natural language form) proposed by users and the SQL statements generated based on these questions. The role of the historical answer record is to provide a reference for similarity for newly received natural language queries and help the system generate accurate SQL statements more quickly.

[0087] Candidate historical query statements refer to query statements that are selected from historical answer records and may be related to the query statement currently entered by the user. These candidate statements are evaluated by similarity to determine which one is closest to the query statement, so as to provide the best reference for the system to generate new SQL statements.

[0088] Specifically, firstly, similar question-answer pair records are obtained from the historical database, for example, "What is the total revenue of the Nanshan branch in July 2024?" Candidate historical query statements related to the current query statement are selected from the historical answer records, for example, "What is the total revenue of the Nanshan branch in July 2024?".

[0089] Then calculate the similarity between the candidate historical query statement and the query statement. For example, calculate the similarity between "What is the total revenue of the Nanshan branch in July 2024?" and "What is the total revenue in August 2024?". After calculating the similarity, assume that the calculation result shows that the similarity between "What is the total revenue of the Nanshan branch in July 2024?" and the query statement is the highest. Therefore, "What is the total revenue of the Nanshan branch in July 2024?" is regarded as the most similar candidate historical query statement.

[0090] Finally, the most similar candidate historical query statements and their corresponding SQL statements are used to construct new SQL statements. For example, based on "What is the total revenue of the Nanshan branch in July 2024?" and its corresponding SQL statement: "select SUM((revenue amount))from(revenue amount))from(revenue table) where (revenue time)>2024.06.30and(revenue time)>2024.06.30and(revenue time)<=2024.07.31 and $(store name) = 'Nanshan branch'", a new SQL statement is generated, for example: "select SUM(amount) from INCOME_DETAIL where data >2024.07.31 and data <= 2024.08.31 and store = 'Nanshan branch'".

[0091] Since a highly similar historical query statement can be found, its SQL conversion result is likely to be correct. This helps reduce debugging time and costs caused by incorrect interpretation of natural language queries. And the historical query statement with the highest similarity can be quickly located, reducing the time required to parse and generate SQL from scratch, thereby speeding up the overall query processing.

[0092] In one of the embodiments, in the historical answer records, the SQL statement corresponding to the candidate historical query statement with the greatest similarity to the query statement is searched; and the SQL statement corresponding to the candidate historical query statement with the greatest similarity to the query statement is used as the first prompt information of the query statement.

[0093] Specifically, first, the historical answer record database or cache is accessed to retrieve a data set containing multiple candidate historical query statements and their corresponding SQL statements. Then, a text similarity algorithm (for example, by cosine similarity calculation) is used to calculate the similarity score between the candidate historical query statement and the query statement. Next, based on the calculated similarity score, the candidate historical query statement with the highest similarity is selected. The SQL statement corresponding to the most similar candidate historical query statement is extracted from the historical answer record. Finally, the extracted SQL statement is used as the first prompt information for the subsequent natural language to SQL conversion process.

[0094] Since users do not need to understand the specific syntax of SQL, they only need to provide natural language queries, which can be intelligently converted into SQL, thus simplifying the user's operation process. And since the history records contain different types of queries and corresponding SQL, this helps the model better understand and adapt to various query scenarios.

[0095] In one of the embodiments, a preset entity type is obtained, and the preset entity type includes at least one of a preset business type and a preset field type; the query statement to be queried is classified according to the preset business type and the preset field type to obtain a plurality of classified entity results; the query statement to be queried is extracted according to the plurality of classified entity results to obtain a to-be-queried entity in the to-be-queried statement.

[0096] The preset entity types refer to a list of predefined entity types that can help the system identify and classify key information in natural language queries. For example, entity types may include time type, amount type, landmark type, organization type, etc.

[0097] Preset business types refer to entity types defined based on business scenarios, such as proper nouns, city names, landmark buildings, etc. These are categories defined based on actual business needs to help the system better understand the entity information in the query.

[0098] Preset field types refer to the types of fields in a database table, such as time fields, amount fields, text fields, etc. These field types help the system determine which database field the entity information should be mapped to.

[0099] Classified entity results refer to the results of classifying the entity information in the input natural language query according to the preset entity type or business type after entity recognition. For example, the entity recognition results for the query "What is the total revenue of the Nanshan branch in July 2024?" may include: entity type: store name, entity information: Nanshan branch; entity type: brand name, entity information:; entity type: date, entity information: July 2024.

[0100] Specifically, first obtain a list of entity types that have been defined in the system, including but not limited to: entity types divided by field type, such as time type, amount type, landmark type, organization type, etc. Entity types divided by business meaning, such as proper nouns, city names, landmark buildings, etc.

[0101] Then, when the user submits a natural language query, the system analyzes the query statement according to the preset business type and field type. This step involves entity recognition, which is to identify key entity information in the query statement, such as time, location, brand name, etc. During the recognition process, the system will use the big model to extract entity information, and will also refer to the entity list in the custom configuration module to improve the accuracy of entity recognition.

[0102] Next, the classified entity result refers to the result obtained by classifying the entities in the query statement according to the preset entity type after entity recognition. For example, for the query "What is the total revenue of the Nanshan branch in July 2024?", the system may get the following classified entity results: Entity type: store name, entity information: Nanshan branch; Entity type: brand name, entity information:; Entity type: date, entity information: July 2024. After obtaining the classified entity results, the entity information to be queried in the query statement will be further extracted. This process may also involve entity alignment, that is, matching the extracted entity information with the entity information in the database to ensure that they can be accurately mapped to the fields or values ​​in the data table.

[0103] Finally, a clear list of entities to be queried will be generated based on the results of the classified entities and the entity alignment, which will serve as the basis for subsequent SQL generation. For example, the entities to be queried can be: Entity: Nanshan Branch (store name); Entity: (brand name); Entity: July 2024 (date).

[0104] Since the entity information in the query statement can be more accurately identified based on these preset entity types, it helps to improve the accuracy and efficiency of entity recognition. The extracted entities to be queried can be directly used to generate SQL statements, reducing the possibility of errors and helping to generate more accurate and expected SQL queries.

[0105] And by classifying the entity information in the query statement according to the preset business type and field type, the query intent can be better understood, because the classified entity results can ensure that each entity is correctly parsed and used, and can more effectively extract key information from natural language queries, which helps to reduce errors in subsequent SQL generation.

[0106] In one of the embodiments, a similarity match is performed between an entity to be queried in a query statement and a preset entity in a preset data table to obtain a similarity value; a similarity threshold is obtained, and the entity to be queried and the preset entity whose similarity value is greater than the similarity threshold are regarded as a similar entity pair; and descriptive information of the preset entity corresponding to the entity to be queried in the similar entity pair in the preset data table is obtained.

[0107] Among them, a similar entity pair refers to a pair of entities obtained by similarity matching between the query entity in the query statement and the preset entity in the preset data table. This usually means that the two are semantically close enough to be considered as the same or interchangeable entities.

[0108] The similarity value is a value used to measure the similarity between the query entity and the preset entity. The higher the similarity value, the more similar the two entities are.

[0109] The similarity threshold is a pre-set value used to determine whether the similarity value of two entities is high enough to determine whether they constitute a similar entity pair. Only when the similarity value is higher than this threshold will the two entities be considered as a similar entity pair.

[0110] Specifically, first use a semantic similarity algorithm (such as cosine similarity calculation) to compare the entity to be queried with the preset entity to obtain a series of similarity values, each value corresponding to the similarity between the entity to be queried and the preset entity. Then set a reasonable similarity threshold according to the application scenario and requirements to filter out truly similar entity pairs. Compare the calculated similarity value with the set similarity threshold, and then filter out all entity pairs whose similarity values ​​are greater than the threshold (i.e., similar entity pairs). Finally, for the filtered similar entity pairs, search the preset data table for the description information of the preset entity corresponding to the entity to be queried, and obtain the description information of the preset entity containing the similar entity pair in the preset data table.

[0111] In one example, the entity to be queried is: "Nanshan Branch"; the preset entity is: "Shenzhen Nanshan Branch"; similarity value: the calculated similarity value may be 0.95; the similarity threshold is 0.8. The similarity value of 0.95 is greater than the similarity threshold of 0.8, so "Nanshan Branch" and "Shenzhen Nanshan Branch" constitute a similar entity pair. Similar entity pairs: "Nanshan Branch" and "Shenzhen Nanshan Branch"; description information: find the information of "Shenzhen Nanshan Branch" from the preset data table, such as store name, location, etc.

[0112] Because the entity alignment process can identify and match the entities in the user input with the entities already in the database, even if the entity names are slightly different, a match can be found, thereby improving the accuracy and flexibility of the query. And when the entity name entered by the user is not exactly the same as the entity name stored in the database, the closest match can be found by calculating the semantic similarity, thus avoiding query failures caused by name differences.

[0113] In one of the embodiments, based on the entity to be queried in the statement to be queried and the first prompt information, the replacement entity in the entity to be queried and the entity to be replaced in the first prompt information are determined; the entity to be replaced in the first prompt information is replaced with the replacement entity in the entity to be queried, to obtain an initial SQL statement; based on the entity to be queried in the similar entity pair recorded in the second prompt information, the preset entity, and the description information of the preset entity in the preset data table, the entity to be queried in the similar entity pair appearing in the initial SQL statement is replaced with the preset entity, to obtain an SQL statement corresponding to the statement to be queried.

[0114] Specifically, after obtaining the natural language query statement input by the user, the entities in the query statement (such as time, place, person, etc.) will be identified first, and then which parts need to be replaced will be determined based on the most similar historical query and its SQL (i.e., the first prompt information) found previously. It can be understood that the so-called "replacement entities in the query entity" here refer to those entities that need to be concretized in the user's new query, while the "entities to be replaced in the first prompt information" refer to those parts in the historical query that need to be replaced.

[0115] Then, the entities to be replaced in the historical query statements found will be replaced with the specific entity information in the current query. For example, if there is "Nanshan Branch" in the historical query, and "Shenzhen Nanshan Branch" is mentioned in the current query, then "Nanshan Branch" will be replaced with "Shenzhen Nanshan Branch", thus generating a preliminary SQL statement.

[0116] Finally, further refine and improve the initial SQL statement. Based on the second piece of hint information (i.e., the information obtained from entity alignment), it will be checked again whether there are any entities in the SQL statement that need further adjustment. For example, if it is found that the entity name entered by the user is not exactly the same as the entity name in the database (such as "Nanshan Branch" and "Shenzhen Nanshan Branch"), then the final adjustment will be made according to the exact description of the entity in the database to ensure the accuracy of the SQL statement.

[0117] Due to entity alignment and the replacement of descriptive information, accurate queries can be achieved even if the vocabulary used by the user is not exactly the same as the data stored in the database. And reusing existing query templates can reduce the server load because the process of constructing queries is faster and less error-prone, which helps save computing resources.

[0118] In one embodiment, referring to Figure 3, the feedback enhancement mechanism continuously improves the performance of the large model by collecting and accumulating feedback data during the user's usage process. This can not only enable the large model to better understand and adapt to the user's query habits, but also improve the accuracy of generating SQL statements, mainly including:

[0119] Collect feedback through the user's like and dislike behaviors. Liking means that the user is satisfied with the generated SQL statement, and disliking is the opposite. These feedbacks are used as positive or negative signals to adjust and optimize the large model. For example, assume the user asks: "What is the total revenue of Nanshan Branch in July 2024?" And the system successfully generates the correct SQL statement: select SUM(amount) from INCOME_DETAIL where data > '2024-06-30' and data <= '2024-07-31' and store='Shenzhen Nanshan Branch'. The user is satisfied with this result and gives positive feedback. This will generate two aspects of positive feedback:

[0120] The first aspect is to generate a new Q&A case for future similar queries. For example, Q: What is the total revenue of Nanshan Branch in July 2024? A: select SUM(amount) from INCOME_DETAIL where data > '2024-06-30' and data <= '2024-07-31' and store='Shenzhen Nanshan Branch'.

[0121] The second aspect is to generate multiple Q&A cases and record the identified entity information. For example, Q: What is the total revenue of the Nanshan branch in July 2024? A: Entity type: store name, entity information: Nanshan branch; Q: What is the total revenue of the Nanshan branch in July 2024? A: Entity type: brand name, entity information:; Q: What is the total revenue of the Nanshan branch in July 2024? A: Entity type: date, entity information: July 2024.

[0122] In an exemplary embodiment, as shown in FIG4 , steps 402 to 412 are included. Among them:

[0123] Step 402, obtaining a query statement and a historical answer record, wherein the historical answer record includes a plurality of candidate historical query statements; for each of the plurality of candidate historical query statements, determining the similarity between the candidate historical query statement and the query statement; based on the determined similarity between each candidate historical query statement and the query statement, taking the candidate historical query statement with the greatest similarity to the query statement among the plurality of candidate historical query statements as the historical query statement matching the query statement;

[0124] Step 404, searching the historical answer records for an SQL statement corresponding to the candidate historical query statement with the greatest similarity to the query statement; using the SQL statement corresponding to the candidate historical query statement with the greatest similarity to the query statement as the first prompt information of the query statement;

[0125] Step 406, obtaining a preset entity type, the preset entity type including at least one of a preset business type and a preset field type; classifying the query statement to be queried according to the preset business type and the preset field type to obtain a plurality of classified entity results; extracting the query statement to be queried according to the plurality of classified entity results to obtain a to-be-queried entity in the to-be-queried statement;

[0126] Step 408, performing similarity matching between the entity to be queried in the query statement and the preset entity in the preset data table to obtain a similarity value; obtaining a similarity threshold, and taking the entity to be queried and the preset entity whose similarity value is greater than the similarity threshold as a similar entity pair; obtaining description information of the preset entity corresponding to the entity to be queried in the similar entity pair in the preset data table;

[0127] Step 410, generating second prompt information for the query statement based on the description information;

[0128] Step 412, determining a replacement entity in the entity to be queried and an entity to be replaced in the first prompt information according to the entity to be queried in the query statement and the first prompt information;

[0129] Replace the entity to be replaced in the first prompt information with the replacement entity in the entity to be queried, to obtain an initial SQL statement;

[0130] According to the to-be-queried entity, the preset entity and the description information of the preset entity in the preset data table in the similar entity pair recorded in the second prompt information, the to-be-queried entity in the similar entity pair appearing in the initial SQL statement is replaced with the preset entity to obtain the SQL statement corresponding to the to-be-queried statement.

[0131] It should be understood that, although the steps in the flowcharts involved in the above embodiments are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence according to the order indicated by the arrows. Unless there is a clear explanation in this article, the execution of these steps is not strictly limited in order, and these steps can be executed in other orders. Moreover, at least a part of the steps in the flowcharts involved in the above embodiments may include multiple steps or multiple stages, and these steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these steps or stages is not necessarily carried out in sequence, but can be executed in turn or alternately with other steps or at least a part of the steps or stages in other steps.

[0132] Based on the same inventive concept, the embodiment of the present application also provides a SQL statement generation device for implementing the SQL statement generation method involved above. The implementation solution provided by the device to solve the problem is similar to the implementation solution recorded in the above method, so the specific limitations in the one or more SQL statement generation device embodiments provided below can refer to the limitations of the SQL statement generation method above, and will not be repeated here.

[0133] In an exemplary embodiment, as shown in FIG5 , a SQL statement generating device 500 is provided, comprising: a determining module 502, a first generating module 504, an identifying module 506, an aligning module 508, a second generating module 510, and a third generating module 512, wherein:

[0134] A determination module 502 is used to obtain a query statement and determine a historical query statement that matches the query statement;

[0135] A first generating module 504, configured to generate first prompt information for a query statement based on an SQL statement corresponding to a historical query statement;

[0136] The recognition module 506 is used to perform entity recognition on the query statement to obtain the query entity in the query statement;

[0137] An alignment module 508, configured to align the entity to be queried in the query statement with the preset entity in the preset data table, and obtain description information of the preset entity aligned with the entity to be queried in the preset data table;

[0138] A second generating module 510, configured to generate second prompt information for the query statement based on the description information;

[0139] The third generating module 512 is used to generate an SQL statement corresponding to the query statement according to the first prompt information and the second prompt information.

[0140] In one embodiment, the determination module 502 is used to obtain a historical answer record, which includes a plurality of candidate historical query statements; for each of the plurality of candidate historical query statements, the similarity between the candidate historical query statement and the statement to be queried is determined; based on the determined similarity between each candidate historical query statement and the statement to be queried, the candidate historical query statement with the greatest similarity to the statement to be queried among the plurality of candidate historical query statements is used as the historical query statement that matches the statement to be queried.

[0141] In one embodiment, the first generation module 504 is used to search for an SQL statement corresponding to a candidate historical query statement with the greatest similarity to the statement to be queried in the historical answer records; and use the SQL statement corresponding to the candidate historical query statement with the greatest similarity to the statement to be queried as the first prompt information of the statement to be queried.

[0142] In one embodiment, the identification module 506 is used to obtain a preset entity type, which includes at least one of a preset business type and a preset field type; classify the query statement according to the preset business type and the preset field type to obtain multiple classified entity results; extract the query statement according to the multiple classified entity results to obtain the entity to be queried in the query statement.

[0143] In one embodiment, the alignment module 508 is used to perform similarity matching between the entity to be queried in the query statement and the preset entity in the preset data table to obtain a similarity value; obtain a similarity threshold, and regard the entity to be queried and the preset entity whose similarity value is greater than the similarity threshold as a similar entity pair; obtain the description information of the preset entity corresponding to the entity to be queried in the similar entity pair in the preset data table.

[0144] In one embodiment, the third generation module 512 is used to determine the replacement entity in the entity to be queried and the entity to be replaced in the first prompt information based on the entity to be queried in the statement to be queried and the first prompt information; replace the entity to be replaced in the first prompt information with the replacement entity in the entity to be queried to obtain an initial SQL statement; replace the entity to be queried in the similar entity pair appearing in the initial SQL statement with the preset entity based on the entity to be queried in the similar entity pair recorded in the second prompt information, the preset entity, and the description information of the preset entity in the preset data table, to obtain an SQL statement corresponding to the statement to be queried.

[0145] Each module in the above-mentioned SQL statement generation device can be implemented in whole or in part by software, hardware and a combination thereof. Each module can be embedded in or independent of a processor in a computer device in the form of hardware, or can be stored in a memory in a computer device in the form of software, so that the processor can call and execute operations corresponding to each module above.

[0146] In an exemplary embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be shown in FIG6. The computer device includes a processor, a memory, an input / output interface (Input / Output, referred to as I / O) and a communication interface. The processor, the memory and the input / output interface are connected via a system bus, and the communication interface is connected to the system bus via the input / output interface. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store data related to the generation of SQL statements. The input / output interface of the computer device is used to exchange information between the processor and an external device. The communication interface of the computer device is used to communicate with an external terminal via a network connection. When the computer program is executed by the processor, a method for generating SQL statements is implemented.

[0147] Those skilled in the art will understand that the structure shown in FIG. 6 is merely a block diagram of a partial structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than shown in the figure, or combine certain components, or have a different arrangement of components.

[0148] In one embodiment, a computer device is further provided, including a memory and a processor, wherein a computer program is stored in the memory, and the processor implements the steps in the above method embodiments when executing the computer program.

[0149] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps in the above-mentioned method embodiments are implemented.

[0150] In one embodiment, a computer program product is provided, including a computer program, which implements the steps in the above method embodiments when executed by a processor.

[0151] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with relevant regulations.

[0152] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to the memory, database or other medium used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. As an illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM). The database involved in each embodiment provided in this application may include at least one of a relational database and a non-relational database. Non-relational databases may include distributed databases based on blockchains, etc., but are not limited to this. The processor involved in each embodiment provided in this application may be a general-purpose processor, a central processing unit, a graphics processor, a digital signal processor, a programmable logic device, a data processing logic device based on quantum computing, an artificial intelligence (AI) processor, etc., but are not limited to this.

[0153] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.

[0154] The above-described embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the present application. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the attached claims.

Claims

1. A method for generating a SQL statement, characterized in that: The method comprises: Obtain a query statement to be queried, and determine a historical query statement that matches the query statement to be queried; Based on the SQL statement corresponding to the historical query statement, generate first prompt information for the statement to be queried; Performing entity recognition on the query statement to obtain the query entity in the query statement; Aligning the entity to be queried in the query statement with the preset entity in the preset data table, and obtaining description information of the preset entity aligned with the entity to be queried in the preset data table; generating second prompt information for the query statement based on the description information; Generate an SQL statement corresponding to the query statement according to the first prompt information and the second prompt information.

2. The method according to claim 1, characterized in that The determining of the historical query statement matching the query statement to be queried includes: Obtaining a historical answer record, wherein the historical answer record includes a plurality of candidate historical query statements; For each of the plurality of candidate historical query statements, determining a similarity between the candidate historical query statement and the query statement; According to the determined similarity between each candidate historical query statement and the statement to be queried, the candidate historical query statement with the greatest similarity to the statement to be queried among multiple candidate historical query statements is used as the historical query statement matching the statement to be queried.

3. The method according to claim 2, characterized in that The generating first prompt information for the query statement based on the SQL statement corresponding to the historical query statement includes: In the historical answer records, searching for an SQL statement corresponding to a candidate historical query statement having the greatest similarity to the query statement; The SQL statement corresponding to the candidate historical query statement having the greatest similarity to the statement to be queried is used as the first prompt information of the statement to be queried.

4. The method according to claim 1, characterized in that: The performing entity recognition on the query statement to obtain the query entity in the query statement includes: Obtaining a preset entity type, where the preset entity type includes at least one of a preset business type and a preset field type; Classifying the query statement according to the preset business type and the preset field type to obtain multiple classification entity results; The query statement is extracted according to the multiple classification entity results to obtain the query entity in the query statement.

5. The method according to claim 1, characterized in that The step of aligning the entity to be queried in the query statement with the preset entity in the preset data table to obtain description information of the preset entity aligned with the entity to be queried in the preset data table includes: Performing similarity matching between the entity to be queried in the query statement and the preset entity in the preset data table to obtain a similarity value; Obtaining a similarity threshold, and taking the to-be-queried entity and the preset entity whose similarity value is greater than the similarity threshold as a similar entity pair; Obtain description information in the preset data table of a preset entity corresponding to the entity to be queried in the similar entity pair.

6. The method according to claim 5, characterized in that The step of generating a SQL statement corresponding to the query statement according to the first prompt information and the second prompt information includes: Determine, according to the entity to be queried in the query statement and the first prompt information, a replacement entity in the entity to be queried and an entity to be replaced in the first prompt information; Replace the entity to be replaced in the first prompt information with the replacement entity in the entity to be queried, to obtain an initial SQL statement; According to the entity to be queried, the preset entity in the similar entity pair recorded in the second prompt information, and the description information of the preset entity in the preset data table, the entity to be queried in the similar entity pair appearing in the initial SQL statement is replaced with the preset entity to obtain the SQL statement corresponding to the statement to be queried.

7. A SQL statement generating device, characterized in that: The device comprises: A determination module, used to obtain a query statement and determine a historical query statement that matches the query statement; A first generating module, used for generating first prompt information for the query statement based on the SQL statement corresponding to the historical query statement; An identification module, used to perform entity identification on the query statement to obtain the query entity in the query statement; An alignment module, used to align the entity to be queried in the query statement with the preset entity in the preset data table, and obtain description information of the preset entity aligned with the entity to be queried in the preset data table; A second generating module, used to generate second prompt information for the query statement based on the description information; The third generating module is used to generate an SQL statement corresponding to the query statement according to the first prompt information and the second prompt information.

8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 6 are implemented.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

Images