Database query statement generation method
By combining sparse and dense retrieval methods, this method extracts related information from database structure description information, rewrites and resolves semantic ambiguities, and generates accurate SQL statements. This solves the problem of low accuracy in generating SQL statements in existing technologies and achieves higher accuracy and recall.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-12-26
- Publication Date
- 2026-04-07
AI Technical Summary
Existing natural language query methods suffer from the loss of key structural information during the semantic parsing stage when converting user-input natural language questions into SQL statements, resulting in low accuracy of the generated SQL statements.
By combining sparse and dense retrieval methods, the association information of natural language query statements is determined from the database structure description information. The query statements are rewritten through semantic ambiguity resolution rules, and the target association information is generated by combining the historical dialogue context, and finally an accurate SQL statement is generated.
It improves the accuracy and recall of generated SQL statements, solves the problem of unstable recall when faced with complex database metadata in a single search mode, and the generated SQL statements are more in line with the user's true intent.
Smart Images
Figure CN121807894A_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of natural language processing technology, and in particular to a method for generating database query statements. Background Technology
[0002] Currently, with the development of Business Intelligence (BI) technology, users increasingly want to directly obtain business data from databases using natural language. However, traditional methods for converting natural language queries (NLQ) or queries described in natural language into SQL queries still have significant shortcomings.
[0003] Currently, the main methods for converting user-input natural language questions into SQL statements are keyword matching, template rules, or deep learning models. However, due to the highly ambiguous, context-dependent, and syntactically diverse nature of natural language, existing solutions often lose key structural information during the semantic parsing stage, resulting in low accuracy of the generated SQL statements.
[0004] Therefore, further research is needed on how to improve the accuracy of generating database query statements. Summary of the Invention
[0005] This invention provides a method for generating a database query statement. Based on first association information and second association information, the target association information of the first query statement in the database structure description information is determined, and based on the target association information, a database query statement SQL corresponding to the first query statement is generated, so as to improve the accuracy of generating the database query statement SQL corresponding to the first query statement.
[0006] In a first aspect, embodiments of this application provide a method for generating a database query statement. The method includes: for a first query statement described in natural language, using a sparse retrieval method to determine first association information related to the first query statement in the database from database structure description information; using a dense retrieval method to determine second association information related to the first query statement in the database from the database structure description information; determining target association information of the first query statement in the database based on the first association information and the second association information; and generating a database query statement (SQL) corresponding to the first query statement based on the target association information.
[0007] Using the above method, for the first query statement described in natural language, two different retrieval methods are employed to determine the first and second related information relevant to the first query statement from the database structure description information. Based on the first and second related information, the target related information of the first query statement in the database structure description information is determined. A sparse retrieval method is used to improve the accuracy of keyword matching, while a dense retrieval method is used to improve the generalization of statement matching. Combining the two retrieval methods extracts information from the database structure description information from multiple perspectives. This solves the problem of unstable recall when a single retrieval mode faces complex database metadata. Furthermore, based on the target related information, a database query statement (SQL) corresponding to the first query statement is generated to improve the accuracy of generating the corresponding SQL.
[0008] In one optional implementation, the database structure description information includes any number of the following: table name, table description, field name, field type, field description, and field value examples.
[0009] By adopting the above method, compared with traditional database information, the database structure description information in this application includes extended information, which can improve the accuracy of the matching process between natural language description statements and database structure description information.
[0010] In one optional implementation, the first association information includes at least one first structural element determined from the database structure description information and a first similarity between the first structural element and the first query statement; the second association information includes at least one second structural element determined from the database structure description information and a second similarity between the second structural element and the first query statement. Determining the target association information of the first query statement in the database based on the first association information and the second association information includes: determining the fusion similarity of any structural element based on the first similarity and the second similarity of the structural element; and determining the structural element whose fusion similarity meets the matching requirements as the target association information of the first query statement in the database.
[0011] Using the above method, based on any structural element in the first and second association information, the fusion similarity of the structural elements is determined according to their first and second similarities. Structural elements whose fusion similarity meets the matching requirements are identified as the target association information of the first query statement in the database. This ensures the relevance between the structural elements in the target association information and the first query statement.
[0012] In one optional implementation, determining the fusion similarity of the structural element based on the first similarity and the second similarity of the structural element includes: determining the fusion similarity of the structural element based on the first similarity of the structural element, the first weight corresponding to the sparse retrieval method, the first ranking result of the structural element in the sparse retrieval method, the second similarity of the structural element, the second weight corresponding to the dense retrieval method, and the second ranking result of the structural element in the dense retrieval method.
[0013] In one optional implementation, before determining the first association information related to the first query statement in the database from the database structure description information using a sparse retrieval method, the method further includes: rewriting the first query statement based on the context dependency of historical dialogues and semantic ambiguity resolution rules; the semantic ambiguity resolution rules are used to target one or more of polysemous expressions, vague expressions, and scene expressions.
[0014] By rewriting the first query statement using the above method, the user's complete query intent corresponding to the first query statement can be fully understood.
[0015] In one optional implementation, generating a database query statement corresponding to the first query statement based on the target association information includes: generating a second query statement based on the first query statement, the target association information, and semantic ambiguity resolution rules; and generating a database query statement corresponding to the first query statement based on the second query statement and the target association information.
[0016] Using the above method, after determining the target association information, the generation device generates a second query statement based on the target association information, the first query statement, and semantic ambiguity resolution rules. This second query statement eliminates the natural language ambiguity in the first query statement, successfully resolving issues such as polysemy, vague expressions, and aliases in natural language, making the generated second query statement more consistent with the user's true intent. Based on this, a preliminary SQL statement is generated using the second query statement and the target association information, which is more accurate than generating a preliminary SQL statement directly based on the first query statement and the target association information.
[0017] In one optional implementation, generating a database query statement corresponding to the first query statement based on the second query statement and the target association information includes: matching the second query statement with cases in a historical case library; if there is a first historical case with a similarity greater than a first preset threshold, using the database query statement in the first historical case as the database query statement corresponding to the first query statement; if there is no historical case with a similarity greater than the first preset threshold, determining a second historical case with a similarity greater than a second preset threshold, and generating a database query statement corresponding to the first query statement based on the second historical case, the second query statement, and the target association information.
[0018] Using the above method, when the similarity between the second query statement and a case in the historical case library exceeds a first threshold, the database query statement from the first historical case is directly used as the database query statement corresponding to the first query statement. This ensures the accuracy of the generated database query statement while improving its efficiency. If no first historical case with a similarity greater than the first preset threshold exists, a second historical case with a similarity greater than the second preset threshold is identified. Based on the second historical case, the second query statement, and the target association information, the database query statement corresponding to the first query statement is generated to improve its accuracy.
[0019] In one optional implementation, based on the second historical case, the second query statement, and the target association information, a database query statement corresponding to the first query statement is generated, including: A semantic parsing agent is used to determine key query elements from the second historical case, the second query statement, and the target association information; an SQL generation agent is used to generate preliminary SQL based on the key query elements and the target association information; and an SQL verification agent is used to verify the preliminary SQL, which, if verified, becomes the database query statement corresponding to the first query statement.
[0020] In one optional implementation, the initial SQL is validated by an SQL validation agent, including: The initial SQL is checked in the sandbox environment. If the check passes, the initial SQL is executed and the execution result is returned. The check includes at least one of the following: syntax check, field missing check, and basic logic check. If the execution result indicates that the execution failed, the database query statement corresponding to the first query statement is re-determined based on the reason for the execution failure.
[0021] Using the above method, the SQL verification agent verifies the preliminary SQL statement by performing syntax checks, field missing checks, and basic logic checks. Once the verification is successful, the preliminary SQL statement is identified as the database query statement corresponding to the first query statement and executed. This improves the accuracy of generating the database query statement corresponding to the first query statement and the reliability of the corresponding query results.
[0022] In an optional implementation, the method further includes: outputting one or more of the second query statement, the execution result, and a natural language interpretation of the execution result, and writing the successfully executed first query statement, the second query statement, and the execution result into the historical case library.
[0023] Secondly, embodiments of this application provide a database query statement generation apparatus, the apparatus comprising: The determination module is used to determine, for a first query statement described in natural language, first related information related to the first query statement in the database from the database structure description information using a sparse retrieval method; second related information related to the first query statement in the database from the database structure description information using a dense retrieval method; and third, determine the target related information of the first query statement in the database based on the first related information and the second related information. The generation module is used to generate the database query statement SQL corresponding to the first query statement based on the target association information.
[0024] In one optional implementation, the database structure description information includes any number of the following: table name, table description, field name, field type, field description, and field value examples.
[0025] In one optional implementation, the first association information includes at least one first structural element determined from the database structure description information and a first similarity between the first structural element and the first query statement; the second association information includes at least one second structural element determined from the database structure description information and a second similarity between the second structural element and the first query statement; the determining module is specifically configured to determine the fusion similarity of any structural element based on the first similarity and the second similarity of the structural element; and determine the structural element whose fusion similarity meets the matching requirements as the target association information of the first query statement in the database.
[0026] In an optional implementation, the determining module is further configured to determine the fusion similarity of the structural element based on the first similarity of the structural element, the first weight corresponding to the sparse retrieval method, the first ranking result of the structural element in the sparse retrieval method, the second similarity of the structural element, the second weight corresponding to the dense retrieval method, and the second ranking result of the structural element in the dense retrieval method.
[0027] In one optional implementation, the device further includes a rewriting module, which is used to rewrite the first query statement based on the contextual dependencies of historical dialogues and semantic ambiguity resolution rules; the semantic ambiguity resolution rules are used to target one or more of polysemous expressions, vague expressions, and scene expressions.
[0028] In one optional implementation, the generation module is specifically used to generate a second query statement based on the first query statement, the target association information, and the semantic ambiguity resolution rules; and to generate a database query statement corresponding to the first query statement based on the second query statement and the target association information.
[0029] In one optional implementation, the generation module is used to match the second query statement with cases in the historical case library. If there is a first historical case with a similarity greater than a first preset threshold, the database query statement in the first historical case is used as the database query statement corresponding to the first query statement. If there is no historical case with a similarity greater than the first preset threshold, a second historical case with a similarity greater than a second preset threshold is determined, and a database query statement corresponding to the first query statement is generated based on the second historical case, the second query statement, and the target association information.
[0030] In an optional implementation, the generation module is further configured to determine key query elements from the second historical case, the second query statement, and the target association information using a semantic parsing agent; and to generate preliminary SQL using an SQL generation agent based on the key query elements and the target association information. The device also includes a verification module, which verifies the preliminary SQL using an SQL verification agent; if the verification is successful, the preliminary SQL is used as the database query statement corresponding to the first query statement.
[0031] In one optional implementation, the verification module is specifically used to check the preliminary SQL in a sandbox environment. If the check passes, the preliminary SQL is executed and the execution result is returned. The check includes at least one of the following: syntax check, field missing check, and basic logic check. If the execution result indicates that the execution failed, the database query statement corresponding to the first query statement is re-determined based on the reason for the execution failure.
[0032] In an optional implementation, the device further includes an output module, which is used to output one or more of the second query statement, the execution result, and the natural language interpretation of the execution result, and to write the successfully executed first query statement, the second query statement, and the execution result into the historical case library.
[0033] Thirdly, embodiments of the present invention provide a database query statement generation device, the device comprising: a memory for storing a computer program; and a processor for executing the method described in the first aspect according to the obtained program when executing the computer program stored in the memory.
[0034] Fourthly, embodiments of the present invention provide a computer-readable storage medium storing a computer program, wherein when a computer reads and executes the computer program, the method described in the first aspect is performed.
[0035] Fifthly, embodiments of the present invention provide a computer program product that, when read and executed by a computer, causes the method described in the first aspect to be executed. Attached Figure Description
[0036] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying 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 effort.
[0037] Figure 1 A schematic diagram illustrating a method for generating database query statements according to an embodiment of this application; Figure 2 A flowchart corresponding to a method for generating a database query statement provided in an embodiment of this application; Figure 3 A schematic diagram illustrating another method for generating database query statements provided in an embodiment of this application; Figure 4 A schematic diagram of the structure of the database query statement generation device provided in the embodiments of this application; Figure 5 This is a schematic diagram of the structure of a database query statement generation device provided in an embodiment of this application. Detailed Implementation
[0038] To make the objectives, technical solutions, and advantages of this application clearer, the application will be further described in detail below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.
[0039] Based on the exemplary embodiments shown in this application, all other embodiments obtained by those skilled in the art without inventive effort are within the scope of protection of this application. Furthermore, although the disclosures in this application are presented by way of one or more exemplary examples, it should be understood that each aspect of these disclosures can constitute a complete technical solution on its own.
[0040] Before introducing the database query statement generation method provided in the embodiments of this application, the background technology of the embodiments of this application will be introduced in detail for ease of understanding.
[0041] Currently, with the development of business intelligence technology, users increasingly want to directly obtain business data from databases using natural language. However, traditional methods for converting natural language queries or natural language-described query statements into SQL statements still have significant shortcomings.
[0042] Currently, the main methods involve converting user-input natural language queries into SQL statements using keyword matching, template rules, or deep learning models. Keyword matching and template rule methods rely heavily on the accuracy of keywords and template rules; improving the accuracy of generated SQL statements typically requires developing a large number of rules, resulting in high development and maintenance costs and poor generalization ability. Deep learning-based methods usually employ large language models, directly inputting user-provided natural language queries into these models to generate corresponding SQL statements. However, natural language queries are often highly ambiguous, context-dependent, and syntactically diverse. Directly using large language models to generate SQL statements from natural language queries struggles to handle complex queries, easily leading to "illusions" and failing to generate correct SQL statements.
[0043] Based on this, embodiments of this application provide a method for generating database query statements. According to first association information and second association information, the target association information of the first query statement in the database structure description information is determined, and based on the target association information, the database query statement SQL corresponding to the first query statement is generated to improve the accuracy of generating the database query statement SQL corresponding to the first query statement.
[0044] The following is a brief introduction to the application scenarios to which the technical solutions of the embodiments of this application are applicable. It should be noted that the application scenarios described below are only for illustrating the embodiments of this application and are not intended to limit the scope. In specific implementation, the technical solutions provided by the embodiments of this application can be flexibly applied according to actual needs.
[0045] Figure 1 An exemplary schematic diagram illustrates a scenario illustrating a method for generating database query statements according to an embodiment of this application, such as... Figure 1 As shown, this application scenario includes at least one terminal device 101 and at least one server 102. The terminal device 101 can be a mobile phone, tablet computer, laptop computer, desktop computer, etc., but is not limited to these. The terminal device 101 can have a client program related to the database query statement generation method installed. The client program can be software (such as a browser, instant messaging software, etc.), or a webpage, mini-program, etc. In this embodiment, the user terminal device 101 can use the client program related to the database query statement generation method to upload a first query statement described in natural language and transmit the first query statement to the server 102 for analysis.
[0046] Furthermore, server 102 can be an independent physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, content delivery networks (CDN), and big data and artificial intelligence platforms. In this embodiment, server 102 may be equipped with a database query statement generation device corresponding to the client, used to process the generation requests initiated by the user on the client related to the database query statement generation method on terminal device 101. According to the method provided in this embodiment, server 102 processes the first query statement to generate the corresponding SQL statement, thereby improving the accuracy of the generated SQL statement.
[0047] In some scenarios, terminal device 101 can access the network and communicate with server 102 through cellular mobile communication technology, which may include 5th generation mobile networks (5G) technology.
[0048] In other scenarios, terminal device 101 can access the network and communicate with server 102 via short-range wireless communication, which may include Wireless Fidelity (Wi-Fi) technology.
[0049] It should be noted that, Figure 1 The examples shown are merely illustrative; in reality, the number of terminal devices 101 and servers 102 is not limited and is not specifically limited in this embodiment.
[0050] To further illustrate the technical solutions provided in the embodiments of this application, a detailed description is provided below in conjunction with the accompanying drawings and specific implementation methods. Although the embodiments of this application provide method operation steps as shown in the following embodiments or drawings, the method may include more or fewer operation steps based on conventional or non-inventive methods. In steps where there is no logically necessary causal relationship, the execution order of these steps is not limited to the execution order provided in the embodiments of this application. In actual processing or when the device executes the method, it may be executed in the order shown in the embodiments or drawings, or in combination.
[0051] Figure 2 The flowchart below illustrates a method for generating a database query statement according to an embodiment of this application. This process can be executed by a database query statement generation device (hereinafter referred to as the generation device), such as... Figure 1 The generation device mounted on server 102 shown is used to improve the accuracy of generated SQL statements, such as... Figure 2 As shown, the process includes the following steps: Step 201: For the first query statement described in natural language, the generating device uses a sparse retrieval method to determine the first association information related to the first query statement in the database from the database structure description information; the generating device uses a dense retrieval method to determine the second association information related to the first query statement in the database from the database structure description information.
[0052] For example, the database structure description information includes any of the following: table name, table description, field name, field type, field description, and field value examples.
[0053] The following is an example of database description information from one embodiment of this application: # Table: front_agent_manager_app_intake, a wide table for the front-end client manager application process; records statistical information on each stage of the client manager's application and loan process, including key indicators such as application, approval, disbursement, collateral, and delinquency. [ (loan_amt:DECIMAL, loan amount; its unit is yuan; Examples: []), (loan_succ_ct:BIGINT, successful loan customers; data unit is individual; Examples: []), (apply2_pass_amt:DECIMAL, the amount approved after review; its unit is yuan; Examples: []), (loan_apply_ct:BIGINT, loan application customers; data unit is individuals; Examples: []), (due60_amt:DECIMAL, overdue amount 60+; data unit is yuan; Examples: []), (due30_ct:BIGINT, 30+ customers overdue; data unit is individual; Examples: []), (due7_ct:BIGINT, customers with overdue payments of 7+; data unit is individual; Examples: []), (mort_finish_ct:BIGINT, mortgage completed customers; its data unit is individuals; Examples: []), (city: VARCHAR, cities with Chinese meanings are enumerated as follows: Jinchang City (Jinchang City), Jinhua City (Jinhua City), Changzhi City (Changzhi City), Changchun City (Changchun City), Changsha City (Changsha City), Examples: [Jinchang City, Jinhua City, Changzhi City, Changchun City, Changsha City]). (area:VARCHAR, area; Examples: []), (apply1_succ_ct:BIGINT, client who passed the first review; its data unit is individuals; Examples: []), (province: VARCHAR, province; enumerations with Chinese meanings are as follows: Chongqing (a municipality directly under the central government in Southwest China), Beijing (a municipality directly under the central government in North China), Tianjin (a municipality directly under the central government in North China), Guizhou (a provincial-level administrative region in Southwest China), Anhui (a provincial-level administrative region in East China), Examples: [Chongqing, Beijing, Tianjin, Guizhou, Anhui]) (apply1_ct:BIGINT, first-stage application client; data unit is individual; Examples: []), (ds_date:VARCHAR, date; Examples: []), (agent_manager:VARCHAR, Regional Manager; Examples: []), (apply2_succ_ct:BIGINT, Clients whose reviews have been approved; Data units are in units; Examples: []), (apply2_ct: BIGINT, Reexamination submission customer; its data unit is number; Examples: []), (dealer_name: VARCHAR, Dealer; Its Chinese meaning enumeration is as follows: Great Wall Motor (Great Wall Motor), Changan Automobile (Independent brand automobile company); Examples: [Great Wall Motor, Changan Automobile]), (car_brand: VARCHAR, Brand; Its Chinese meaning enumeration is as follows: Great Wall (Great Wall Motor brand), Changan (Changan Automobile brand); Examples: [Great Wall, Changan]), (agent_name: VARCHAR, Account manager; Examples: []) As can be seen, the database structure description information proposed in this application is more concise and complete than traditional DDL database information. The database description information in this application adds table descriptions, field descriptions, and field value examples. For example, "front_agent_manager_app_intake" is the English name of the table, "front-end customer manager application topic wide table" is the Chinese name of the table, and "records statistical information of each stage of the customer manager's application and loan process, including key indicators such as application, approval, loan disbursement, mortgage and delinquency" is the table description, which roughly describes the function of the table. For example, consider this information in the database description: loan_amt:DECIMAL, loan amount; its data unit is yuan; Examples: []. "loan_amt" is the English field name, "DECIMAL" is the data type, "loan amount" is the Chinese field name, and "its data unit is yuan" is the field description. Here, the field description is the unit corresponding to the field. Existing databases only include query data and do not include the corresponding unit. When a user queries the database, this application can reflect the corresponding unit in the corresponding query results to improve the accuracy of the query results. "Examples: []" are examples of field values in the database. For example, consider the following information in the database description: (dealer_name:VARCHAR, car dealer; Chinese meanings include: Great Wall Motors (Great Wall Motors), Changan Automobile (independent brand automobile company); Examples: [Great Wall Motors, Changan Automobile]) and (car_brand:VARCHAR, brand; Chinese meanings include: Great Wall (Great Wall Motors brand), Changan (Changan Automobile brand); Examples: [Great Wall, Changan]). These two pieces of information correspond to the car dealer field and the brand field, respectively. For the car dealer field, the added field description includes: Chinese meanings include: Great Wall Motors (Great Wall Motors), Changan Automobile (independent brand automobile company). For the brand field, the added field description includes: Chinese meanings include: Great Wall (Great Wall Motors brand), Changan (Changan Automobile brand). It can be seen that existing databases use abbreviations for car dealers and brands, while this application expands the fields in the field description, thus improving the accuracy of matching natural language description statements with database structure description information.
[0054] For the first query statement described in natural language, the generating device uses a sparse retrieval method to determine the first association information related to the first query statement in the database from the database structure description information. The coefficient retrieval method can be either the BM25 algorithm or the TF-IDF algorithm, which is not limited here. The generating device uses a dense retrieval method to determine the second association information related to the first query statement in the database from the database structure description information. The dense retrieval method can be a vector semantic retrieval algorithm, which is not limited here.
[0055] Optionally, before the generating device uses a sparse retrieval method to determine the first association information related to the first query statement from the database structure description information, it further includes: rewriting the first query statement based on the context dependency of historical dialogues and semantic ambiguity resolution rules; the semantic ambiguity resolution rules are used to target one or more of polysemous expressions, vague expressions, and scene expressions. For example, suppose a user's initial query in natural language is "This month?", and the previous dialogue was "Which department had the highest sales in East China last month?" The initial query contains very little semantic information. Directly generating a corresponding SQL statement based on this initial query would fail to understand the user's true intent, resulting in low accuracy. Therefore, the initial query needs to be rewritten using both the initial query and the previous dialogue to obtain the new query, "Which department had the highest sales in East China this month?". However, "this month" in the initial query is a vague expression and needs to be converted to a precise expression, such as December 1st, 2025 to December 31st, 2025. Therefore, based on the contextual dependency of the previous dialogue and semantic ambiguity resolution rules, the initial query is rewritten to obtain the new natural language query, "Which department had the highest sales in East China from December 1st, 2025 to December 31st, 2025?" By rewriting the initial query, the user's complete intent for this query can be fully understood.
[0056] Step 202: The generating device determines the target association information of the first query statement in the database based on the first association information and the second association information.
[0057] For example, the first association information includes at least one first structural element determined from the database structure description information and a first similarity between the first structural element and the first query statement; the second association information includes at least one second structural element determined from the database structure description information and a second similarity between the second structural element and the first query statement. The generation device determines the target association information of the first query statement in the database based on the first and second association information, including: determining a fusion similarity of any structural element based on its first and second similarity; and determining the structural element whose fusion similarity meets the matching requirements as the target association information of the first query statement in the database.
[0058] Optionally, the generating device determines the fusion similarity of the structural elements based on the first similarity and the second similarity of the structural elements, including: determining the fusion similarity of the structural elements based on the first similarity of the structural elements, the first weight corresponding to the sparse retrieval method, the first ranking result of the structural elements in the sparse retrieval method, the second similarity of the structural elements, the second weight corresponding to the dense retrieval method, and the second ranking result of the structural elements in the dense retrieval method.
[0059] For example, the first related information Second related information ,in, This represents the first similarity value of the first structural element (i.e., structural element q) in the database structure description information. This refers to the second similarity value of the second structural element (structural element p) in the database structure description information. The first similarity value in the first association information and the second similarity value in the second association information are sorted according to similarity, either from smallest to largest or from largest to smallest. It should be noted that at least one first structural element identified in the first association information may be the same as or different from at least one second structural element identified in the second association information; the number of structural elements may be the same or different, and this is not limited here. This indicates that the first structural element in the first associated information and the second structural element in the second associated information are the same structural element in the database information. Here, the structural elements identified in the first and second associated information are structural element 1, structural element 3, structural element 4, structural element 5, and structural element 10. For any of these structural elements, taking structural element 1 as an example, the fusion similarity of structural element 1 is obtained by multiplying the first similarity value corresponding to structural element 1 by the first weight corresponding to the sparse retrieval method, plus the second similarity value corresponding to structural element 1 multiplied by the second weight corresponding to the dense retrieval method. Structural element 5 only exists in the second associated information; therefore, the fusion similarity of structural element 5 is 0 plus the second similarity value corresponding to structural element 5 multiplied by the second weight corresponding to the dense retrieval method. Thus, the fusion similarity corresponding to structural elements 1, 3, 4, 5, and 10 is obtained in the same way. Structural elements with a fusion similarity greater than a preset threshold are determined as the target associated information of the first query statement in the database.
[0060] Step 203: The generating device generates the database query statement SQL corresponding to the first query statement based on the target association information.
[0061] For example, the generation device generates a second query statement based on a first query statement, target association information, and semantic ambiguity resolution rules; and generates a database query statement corresponding to the first query statement based on the second query statement and target association information. Before generating the second query statement based on the first query statement, target association information, and semantic ambiguity resolution rules, the generation device uses an intent analysis agent to determine the intent type of the first query statement. The intent types include: SQL query intent type and unstructured question-and-answer intent. For SQL query intent types, a corresponding SQL statement needs to be generated and data queried. Unstructured question-and-answer intent types are purely casual conversation or knowledge-based questions and answers, and do not require database querying, i.e., no corresponding SQL statement needs to be generated. If the generation device determines through the intent analysis agent that the intent type of the first query statement is an unstructured question-and-answer intent, it directly returns the result of the first query statement. If the generation device determines through the intent analysis agent that the intent type of the first query statement is an SQL query intent type, it generates a second query statement based on the first query statement, target association information, and semantic ambiguity resolution rules; and generates a database query statement corresponding to the first query statement based on the second query statement and target association information.
[0062] Optionally, generating a database query statement corresponding to the first query statement based on the second query statement and the target association information includes: matching the second query statement with cases in the historical case library; if there is a first historical case with a similarity greater than a first preset threshold, using the database query statement in the first historical case as the database query statement corresponding to the first query statement; if there is no historical case with a similarity greater than the first preset threshold, determining a second historical case with a similarity greater than the second preset threshold, and generating a database query statement corresponding to the first query statement based on the second historical case, the second query statement, and the target association information.
[0063] Further, the generation device generates a database query statement corresponding to the first query statement based on the second historical case, the second query statement, and the target association information. This includes: determining key query elements from the second historical case, the second query statement, and the target association information using a semantic parsing agent; generating preliminary SQL using an SQL generation agent based on the key query elements and the target association information; and validating the preliminary SQL using an SQL verification agent. Once verified, the preliminary SQL is used as the database query statement corresponding to the first query statement. The key query elements include: query intent (such as filtering, sorting, or aggregation), involved database tables and fields, filtering conditions, and any number of enumerated value mappings. The SQL validation agent validates the initial SQL statement. This includes checking the initial SQL statement in a sandbox environment. If the check passes, the initial SQL statement is executed, and the execution result is returned. The initial SQL statement is checked for at least one of the following: syntax checking, field missing checking, and basic logic checking. If the check fails, the SQL generation agent reports an error. This error is used as input to the SQL generation agent, which generates a new SQL statement based on the key query elements, target association information, and the reported error. The generated new SQL statement is then validated until it passes. The validated SQL statement is executed. If the execution result indicates failure, the database query statement corresponding to the first query statement is redefined based on the reason for the failure. If the execution is successful, one or more of the following are output: a second query statement, the execution result, and a natural language explanation of the execution result. The successfully executed first query statement, second query statement, and execution result are written to the historical case database. The execution result also includes a suitable chart generated based on the execution result. Chart types include bar charts, line charts, and pie charts. For example, if the first query is "Which department has the highest sales in East China this month?", the corresponding natural language interpretation of the output is "The department with the highest sales in East China this month is the Technology Department, with sales of XXX yuan".
[0064] Using the above methods, such as Figure 3As shown, the generation device, for a first query statement described in natural language, uses two different retrieval methods to determine the first and second related information from the database structure description information. Based on the first and second related information, it determines the target related information of the first query statement in the database structure description information. A sparse retrieval method is used to improve the accuracy of keyword matching, while a dense retrieval method improves the generalization of statement matching. Combining these two retrieval methods extracts information from the database structure description information from multiple perspectives. This solves the problem of unstable recall when a single retrieval mode faces complex database metadata. After determining the target related information, the generation device generates a second query statement based on the target related information, the first query statement, and semantic ambiguity resolution rules. This second query statement eliminates the natural language ambiguity in the first query statement, successfully resolving issues such as polysemy, vague expressions, and aliases in natural language, making the generated second query statement more consistent with the user's true intent. The generation device generates a preliminary SQL statement based on the second query statement and the target related information, which is more accurate than generating a preliminary SQL statement directly based on the first query statement and the target related information. Furthermore, this application uses an SQL verification agent to verify the preliminary SQL statement by performing syntax checks, field missing checks, and basic logic checks. Once the verification is successful, the preliminary SQL statement is identified as the database query statement corresponding to the first query statement and executed. This improves the accuracy of generating the database query statement corresponding to the first query statement and the reliability of the corresponding query results.
[0065] Based on the same technical concept, embodiments of this application provide a database query statement generation device 4000. Figure 4 This is a schematic diagram of the structure of the database query statement generation device provided in the embodiments of this application, as shown below. Figure 4 As shown, the device 4000 includes: The determining module 401 is used to determine, for a first query statement described in natural language, first related information related to the first query statement in the database from the database structure description information using a sparse retrieval method; second related information related to the first query statement in the database from the database structure description information using a dense retrieval method; and third, determine the target related information of the first query statement in the database based on the first related information and the second related information. The generation module 402 is used to generate a database query statement (SQL) corresponding to the first query statement based on the target association information.
[0066] In one optional implementation, the database structure description information includes any number of the following: table name, table description, field name, field type, field description, and field value examples.
[0067] In one optional implementation, the first association information includes at least one first structural element determined from the database structure description information and a first similarity between the first structural element and the first query statement; the second association information includes at least one second structural element determined from the database structure description information and a second similarity between the second structural element and the first query statement; the determining module 401 is specifically configured to determine the fusion similarity of any structural element based on the first similarity and the second similarity of the structural element; and determine the structural element whose fusion similarity meets the matching requirements as the target association information of the first query statement in the database.
[0068] In an optional implementation, the determining module 401 is further configured to determine the fusion similarity of the structural element based on the first similarity of the structural element, the first weight corresponding to the sparse retrieval method, the first ranking result of the structural element in the sparse retrieval method, the second similarity of the structural element, the second weight corresponding to the dense retrieval method, and the second ranking result of the structural element in the dense retrieval method.
[0069] In an optional implementation, the device further includes a rewriting module 403, which is used to rewrite the first query statement based on the contextual dependencies of historical dialogues and semantic ambiguity resolution rules; the semantic ambiguity resolution rules are used to target one or more of polysemous expressions, vague expressions, and scene expressions.
[0070] In one optional implementation, the generation module 402 is specifically used to generate a second query statement based on the first query statement, the target association information, and the semantic ambiguity resolution rules; and to generate a database query statement corresponding to the first query statement based on the second query statement and the target association information.
[0071] In one optional implementation, the generation module 402 is used to match the second query statement with cases in the historical case library. If there is a first historical case with a similarity greater than a first preset threshold, the database query statement in the first historical case is used as the database query statement corresponding to the first query statement. If there is no historical case with a similarity greater than the first preset threshold, a second historical case with a similarity greater than a second preset threshold is determined, and a database query statement corresponding to the first query statement is generated based on the second historical case, the second query statement, and the target association information.
[0072] In an optional implementation, the generation module 402 is further configured to determine key query elements from the second historical case, the second query statement, and the target association information using a semantic parsing agent; and to generate preliminary SQL using an SQL generation agent based on the key query elements and the target association information. The device also includes a verification module 404, which verifies the preliminary SQL using an SQL verification agent. If the verification is successful, the preliminary SQL is used as the database query statement corresponding to the first query statement.
[0073] In one optional implementation, the verification module 404 is specifically used to check the preliminary SQL in a sandbox environment. If the check passes, the preliminary SQL is executed and the execution result is returned. The check includes at least one of the following: syntax check, field missing check, and basic logic check. If the execution result indicates that the execution failed, the database query statement corresponding to the first query statement is re-determined based on the reason for the execution failure.
[0074] In an optional embodiment, the device further includes an output module 405, which is used to output one or more of the second query statement, the execution result, and the natural language interpretation of the execution result, and to write the successfully executed first query statement, the second query statement, and the execution result into the historical case library.
[0075] Based on the same technological concept Figure 5 A schematic diagram of the structure of a database query statement generation device 5000 provided in this application embodiment is shown below. Figure 5 As shown, the device 5000 includes at least one processor 501 and a memory 502 connected to the at least one processor 501. In this embodiment, the specific connection medium between the processor 501 and the memory 502 is not limited. Figure 5 Taking the connection between processor 501 and memory 502 via a bus as an example, the bus can be divided into address bus, data bus, control bus, etc. In this embodiment of the invention, memory 502 stores instructions that can be executed by at least one processor 501. By executing the instructions stored in memory 502, at least one processor 501 can implement the steps of the above-described method for generating database query statements.
[0076] The processor 501 is the control center of the computer device, capable of connecting various parts of the computer device via various interfaces and lines. It performs resource configuration by running or executing instructions stored in the memory 502 and accessing data stored in the memory 502. Optionally, the processor 501 may include one or more processing units. The processor 501 may integrate an application processor and a modem processor. The application processor primarily handles the operating system, user interface, and applications, while the modem processor primarily handles wireless communication. It is understood that the modem processor may not be integrated into the processor 501. In some embodiments, the processor 501 and the memory 502 may be implemented on the same chip; in other embodiments, they may be implemented on separate chips.
[0077] Processor 501 can be a general-purpose processor, such as a central processing unit (CPU), digital signal processor, application-specific integrated circuit (ASIC), field-programmable gate array (FPGA), or other programmable logic device, discrete gate or transistor logic device, or discrete hardware component, capable of implementing or executing the methods, steps, and logic block diagrams disclosed in the embodiments of this application. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the methods disclosed in the embodiments of this application can be directly manifested as being executed by a hardware processor, or executed by a combination of hardware and software modules within the processor.
[0078] Memory 502, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer-executable programs, and modules. Memory 502 may include at least one type of storage medium, such as flash memory, hard disk, multimedia card, card-type memory, random access memory (RAM), static random access memory (SRAM), programmable read-only memory (PROM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), magnetic storage, magnetic disk, optical disk, etc. Memory 502 can be any other medium capable of carrying or storing desired program code in the form of instructions or data structures that can be accessed by a computer, but is not limited thereto. In the embodiments of this application, memory 502 can also be a circuit or any other device capable of implementing storage functions for storing program instructions and / or data.
[0079] Based on the same inventive concept, embodiments of this application provide a computer-readable storage medium. The computer program product includes computer program code, which, when executed on a computer, causes the computer to execute any of the database query statement generation methods discussed above. Since the principle by which the above-described computer-readable storage medium solves the problem is similar to the database query statement generation method, the implementation of the above-described computer-readable storage medium can be found in the implementation of the method; repeated details will not be elaborated further.
[0080] Based on the same inventive concept, this application also provides a computer program product, which includes computer program code. When the computer program code is run on a computer, it causes the computer to execute any of the database query statement generation methods discussed above. Since the principle by which the above computer program product solves the problem is similar to the database query statement generation method, the implementation of the above computer program product can refer to the implementation of the method, and repeated details will not be repeated.
[0081] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0082] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to this application. It should be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.
[0083] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.
[0084] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.
[0085] Obviously, those skilled in the art can make various modifications and variations to this application without departing from the spirit and scope of this application. Therefore, if such modifications and variations fall within the scope of the claims of this application and their equivalents, this application also intends to include such modifications and variations.
Claims
1. A method for generating database query statements, characterized in that, The method includes: For a first query statement described in natural language, a sparse retrieval method is used to determine the first association information related to the first query statement in the database from the database structure description information; a dense retrieval method is used to determine the second association information related to the first query statement in the database from the database structure description information; based on the first association information and the second association information, the target association information of the first query statement in the database is determined. Based on the target association information, a database query statement (SQL) corresponding to the first query statement is generated.
2. The method according to claim 1, characterized in that, The database structure description information includes any of the following: table name, table description, field name, field type, field description, and field value examples.
3. The method according to claim 1, characterized in that, The first association information includes at least one first structural element determined from the database structure description information and a first similarity between the first structural element and the first query statement; The second association information includes at least one second structural element determined from the database structure description information and a second similarity between the second structural element and the first query statement; Based on the first association information and the second association information, the target association information of the first query statement in the database is determined, including: Based on any structural element, the fusion similarity of the structural element is determined according to the first similarity and the second similarity of the structural element. The structural elements whose similarity meets the matching requirements are determined as the target association information of the first query statement in the database.
4. The method according to claim 3, characterized in that, Determining the fusion similarity of the structural elements based on the first similarity and the second similarity of the structural elements includes: The fusion similarity of the structural element is determined based on the first similarity of the structural element, the first weight corresponding to the sparse retrieval method, the first ranking result of the structural element in the sparse retrieval method, the second similarity of the structural element, the second weight corresponding to the dense retrieval method, and the second ranking result of the structural element in the dense retrieval method.
5. The method according to any one of claims 1-4, characterized in that, Before determining the first association information related to the first query statement in the database from the database structure description information using a sparse retrieval method, the method further includes: Based on the contextual dependencies of historical dialogues and semantic ambiguity resolution rules, the first query statement is rewritten; the semantic ambiguity resolution rules are used to target one or more of polysemous expressions, vague expressions, and scene expressions.
6. The method according to any one of claims 1-4, characterized in that, Based on the target association information, a database query statement corresponding to the first query statement is generated, including: Based on the first query statement, the target association information, and the semantic ambiguity resolution rules, a second query statement is generated; Based on the second query statement and the target association information, a database query statement corresponding to the first query statement is generated.
7. The method according to claim 6, characterized in that, The step of generating a database query statement corresponding to the first query statement based on the second query statement and the target association information includes: The second query statement is matched with cases in the historical case library. If there is a first historical case with a similarity greater than a first preset threshold, the database query statement in the first historical case is used as the database query statement corresponding to the first query statement. If there is no historical case with a similarity greater than the first preset threshold, a second historical case with a similarity greater than a second preset threshold is determined. Based on the second historical case, the second query statement, and the target association information, the database query statement corresponding to the first query statement is generated.
8. The method according to claim 7, characterized in that, Based on the second historical case, the second query statement, and the target association information, a database query statement corresponding to the first query statement is generated, including: The key query element is determined from the second historical case, the second query statement, and the target association information by using a semantic parsing intelligent agent; An intelligent agent is generated using SQL, which generates preliminary SQL based on the key query elements and the target association information. The SQL verification agent verifies the initial SQL, and if the verification is successful, it becomes the database query statement corresponding to the first query statement.
9. The method according to claim 8, characterized in that, The initial SQL is validated by an SQL validation agent, including: The initial SQL is checked in the sandbox environment. If the check passes, the initial SQL is executed and the execution result is returned. The check includes at least one of the following: syntax check, field missing check, and basic logic check. If the execution result indicates that the execution failed, the database query statement corresponding to the first query statement is re-determined based on the reason for the execution failure.
10. The method according to claim 9, characterized in that, Also includes: Output one or more of the second query statement, the execution result, and the natural language interpretation of the execution result, and write the successfully executed first query statement, the second query statement, and the execution result into the historical case library.