Database query statement generation method and device, equipment and medium
By using the collaborative correction mechanism of the correction module in the criteria layer to replace calculation expressions, complete detached fields, and correct join rules, the problem of high-cost sample data was solved, the quality of SQL statement generation was improved, and the cost was reduced.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2026-01-14
- Publication Date
- 2026-04-03
AI Technical Summary
In existing technologies, obtaining sample data of natural language statements and SQL statements is costly, making it difficult for artificial intelligence models to learn the mapping relationship and resulting in low-quality SQL statements.
Through three correction modules in the criteria layer: the first correction module replaces the calculated expression with a preset function, the second correction module identifies and completes the table information of detached fields, and the third correction module corrects clauses that violate join rules, a collaborative correction mechanism is formed to ensure the logical integrity and join compliance of query statements.
It improves the quality of generated database query statements and reduces the dependence on large-scale sample datasets, thereby reducing generation costs.
Smart Images
Figure CN121786057A_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of artificial intelligence technology, and in particular to a method, apparatus, device, and medium for generating database query statements. Background Technology
[0002] With the rapid development of artificial intelligence (AI) technology, the technology of automatically generating Structured Query Language (SQL) statements corresponding to natural language statements based on AI models has been widely applied in fields such as database interaction and data analysis. The training method for AI models typically involves training an end-to-end AI model based on sample data of <natural language statements and SQL statements>, ultimately obtaining a generative model capable of directly generating SQL statements from natural language statements.
[0003] However, the cost of obtaining sample data for <natural language statements and SQL statements> is high, which makes it difficult to train end-to-end artificial intelligence models with a sufficiently large sample dataset that covers a wide range of scenarios. As a result, the artificial intelligence model has difficulty learning the mapping relationship between natural language statements and SQL statements, which may lead to lower quality SQL statements generated in practical applications. Summary of the Invention
[0004] To address the aforementioned issues, this application provides a method, apparatus, device, and medium for generating database query statements, which can improve the quality of generated database query statements at low cost.
[0005] The embodiments of this application disclose the following technical solutions:
[0006] Firstly, this application discloses a method for generating database query statements, the method comprising:
[0007] Obtain the original database query statement;
[0008] The first correction module of the criteria layer replaces the calculation expression corresponding to the target business indicator in the original database query statement with a preset function corresponding to the target business indicator to obtain the first database query statement.
[0009] The second correction module of the criterion layer identifies the detached fields in the first database query statement and completes the table information corresponding to the detached fields in the first database query statement to obtain the second database query statement.
[0010] The third correction module of the criterion layer modifies the clauses in the second database query statement that violate the preset join rules to obtain a standard database query statement.
[0011] Optionally, the first database query statement includes a FROM clause and / or a JOIN clause; identifying detached fields in the first database query statement includes:
[0012] Determine the set of fields in the first database query statement;
[0013] If, in the set of fields, there exists a target field that is not qualified by a table name or alias, and the FROM clause and / or JOIN clause includes zero or more table information corresponding to the target field, then the target field is determined to be a detached field.
[0014] Optionally, completing the table information corresponding to the detached field in the first database query statement includes:
[0015] From the database, find the target table whose primary key or foreign key is the aforementioned free field;
[0016] Add the table information of the target table to the JOIN clause.
[0017] Optionally, the original database query statement is generated based on a natural language statement; the step of searching the target table in the database where the primary key or foreign key is the detached field includes:
[0018] Based on the semantic information of the natural language statements, a subset of tables is determined from the database;
[0019] From the subset of tables, find the target table whose primary key or foreign key is the free field.
[0020] Optionally, the preset connection rule is: when the FROM clause and / or JOIN clause includes multiple table information, each table information needs to be connected to at least one other table information through the primary key.
[0021] Secondly, this application discloses a database query statement generation device, the device comprising: a statement acquisition module, a function replacement module, an information completion module, and a clause adjustment module;
[0022] The statement acquisition module is used to acquire the original database query statement;
[0023] The function replacement module is used to replace the calculation expression corresponding to the target business indicator in the original database query statement with a preset function corresponding to the target business indicator through the first correction module of the criterion layer, so as to obtain the first database query statement;
[0024] The information completion module is used to identify the detached field in the first database query statement through the second correction module of the criterion layer, and complete the table information corresponding to the detached field in the first database query statement to obtain the second database query statement;
[0025] The clause adjustment module is used to modify the clauses in the second database query statement that violate the preset connection rules through the third correction module of the criterion layer, so as to obtain a standard database query statement.
[0026] Optionally, the first database query statement includes a FROM clause and / or a JOIN clause;
[0027] The information completion module is specifically used to: determine the set of fields in the first database query statement; if there is a target field in the set of fields that is not limited by a table name or alias, and the FROM clause and / or JOIN clause includes 0 or more table information corresponding to the target field, then the target field is determined to be a detached field.
[0028] Optionally, the information completion module is specifically used to: find the target table whose primary key or foreign key is the free field from the database; and add the table information of the target table to the JOIN clause.
[0029] Optionally, the original database query statement is generated based on a natural language statement; the information completion module is specifically used to: determine a subset of tables from the database based on the semantic information of the natural language statement; and search for a target table from the subset of tables whose primary key or foreign key is the detached field.
[0030] Optionally, the preset connection rule is: when the FROM clause and / or JOIN clause includes multiple table information, each table information needs to be connected to at least one other table information through the primary key.
[0031] Thirdly, this application discloses a database query statement generation device, the device comprising: a memory and a processor;
[0032] The memory is used to store programs;
[0033] The processor is configured to execute the program to implement the various steps of the database query statement generation method as described in the first aspect.
[0034] Fourthly, this application discloses a computer-readable medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the database query statement generation method as described in the first aspect.
[0035] Compared with the prior art, this application has the following beneficial effects:
[0036] This application provides a method, apparatus, device, and medium for generating database query statements. The method includes: obtaining an original database query statement; replacing the calculation expression corresponding to the target business indicator in the original database query statement with a preset function corresponding to the target business indicator through a first correction module of the criterion layer to obtain a first database query statement; identifying detached fields in the first database query statement through a second correction module of the criterion layer and completing the table information corresponding to the detached fields in the first database query statement to obtain a second database query statement; and modifying clauses in the second database query statement that violate preset join rules through a third correction module of the criterion layer to obtain a standard database query statement. Therefore, the three correction modules at the criteria layer form a collaborative correction mechanism: the first correction module effectively avoids query result deviations caused by expression differences by replacing the calculated expressions in the original database query statement with standardized preset functions determined based on business indicators, thus improving the generation quality of the database query statement; the second correction module automatically identifies detached fields and completes the corresponding table information; and the third correction module corrects non-compliant clauses based on preset join rules. Together, these two modules ensure the logical integrity and join compliance of the database query statement, avoiding the problem of not obtaining query results due to incomplete logic or non-compliant joins, thereby improving the generation quality of the database query statement. Furthermore, this application does not require training the artificial intelligence model using a large-scale, comprehensive sample dataset, thus significantly reducing the cost of improving the generation quality of the database query statement. Attached Figure Description
[0037] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0038] Figure 1 A flowchart illustrating a method for generating a database query statement as provided in an embodiment of this application;
[0039] Figure 2 This is a schematic diagram illustrating the generation of a database query statement, provided as an embodiment of this application.
[0040] Figure 3 A schematic diagram of a database query statement generation device provided in an embodiment of this application;
[0041] Figure 4This is a schematic diagram of a computer-readable medium provided in an embodiment of this application. Detailed Implementation
[0042] As described earlier, the typical training method for artificial intelligence models is to train an end-to-end AI model based on sample data of natural language statements and SQL statements, ultimately obtaining a generative model capable of directly generating SQL statements from natural language statements. However, acquiring sample data of natural language statements and SQL statements is costly, making it difficult to train an end-to-end AI model using a sufficiently large sample dataset that comprehensively covers various scenarios. Consequently, the AI model struggles to learn the mapping relationship between natural language statements and SQL statements, potentially resulting in lower quality SQL statements generated in practical applications.
[0043] Through research, the inventors have proposed a method, apparatus, device, and medium for generating database query statements. Three correction modules at the criteria layer form a collaborative correction mechanism: the first correction module replaces the calculated expressions in the original database query statement with standardized preset functions determined based on business indicators, effectively avoiding query result deviations caused by expression differences and improving the quality of generated database query statements; the second correction module automatically identifies detached fields and completes the corresponding table information; and the third correction module corrects non-compliant clauses based on preset join rules. Together, these two modules ensure the logical integrity and join compliance of the database query statement, avoiding the problem of not obtaining query results due to incomplete logic or non-compliant joins, thereby improving the quality of generated database query statements. Furthermore, this application does not require training an artificial intelligence model using a large-scale, comprehensive sample dataset, thus significantly reducing the cost of improving the quality of generated database query statements.
[0044] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of the present application.
[0045] See Figure 1 This figure is a flowchart illustrating a method for generating database query statements according to an embodiment of this application. See also... Figure 2 This figure is a schematic diagram illustrating the generation of a database query statement according to an embodiment of this application. Combined with... Figure 1 and Figure 2 The method includes:
[0046] S101: Obtain the original database query statement.
[0047] The original database query statement refers to a database query statement that may have defects in terms of business logic standardization, clear table field ownership, or correct table join relationships.
[0048] For example, the original database query statement can be a database query statement generated by inputting a natural language statement into an artificial intelligence model (such as a large language model), or it can be a database query statement manually written by business personnel or data analysts. This application does not limit this.
[0049] S102: The first correction module of the criteria layer replaces the calculation expression corresponding to the target business indicator in the original database query statement with a preset function corresponding to the target business indicator to obtain the first database query statement.
[0050] Target business metrics refer to the metrics that need to be calculated in the original database query statement, such as revenue, gross profit, number of users, actual order amount received, payment conversion rate, monthly average order value, repurchase rate, etc.
[0051] The calculation expression corresponding to the target business metric refers to the code snippet in the original database query statement used to calculate the target business metric, such as SUM(amount) or COUNT(DISTINCT user_id).
[0052] The preset function corresponding to the target business metric refers to a standardized function that is predefined based on the target business scenario and uniquely corresponds to the target business metric.
[0053] Specifically, the first correction module at the criteria layer determines whether a calculation expression corresponding to the target business indicator exists in the original database query statement. If so, a preset function corresponding to the target business indicator is retrieved from the preset function library, and the calculation expression in the original database query statement corresponding to the target business indicator is replaced with the preset function, thus obtaining the first database query statement. At this point, the calculation of the target business indicator in the database query statement has been transformed from a non-standard original expression into a standard preset function.
[0054] For example, in an order analysis scenario, "revenue" is not equal to the order amount SUM(amount) indicated by the original database query, but rather the difference between the order amount and the refund amount, SUM(order_amount) - SUM(refund_amount). Therefore, the first correction module in the criteria layer replaces the calculation expression SUM(amount) corresponding to the target business indicator in the original database query with the preset function CALC_REVENUE(order_amount, refund_amount) from the preset function library, thus obtaining the first database query. This preset function internally encapsulates the correct logic for SUM(order_amount) - SUM(refund_amount).
[0055] For example, a preset function library stores multiple preset functions in a structured manner, as shown below:
[0056] { "revenue": { "description": "Total revenue (order amount - refund amount)", "formula":"SUM(order_amount) - SUM(refund_amount)", "sql_function": "CALC_REVENUE(order_amount, refund_amount)", "business_rules": [ "Must include a relation to the refund table", "Calculation results must be aggregated by date range" ]}, "active_users": { "description": "Number of active users (users with transactions in the last 30 days)", "formula": "COUNT(DISTINCT user_id)", "sql_function":"CALC_ACTIVE_USERS(user_id, transaction_date)", "business_rules": [ "Must join user and transaction tables", "Time range must be limited to the last 30 days" ]}}
[0057] It should be noted that this application supports adding, modifying, and deleting preset functions in the preset function library through a visual interface, without modifying the underlying code of the first correction module, thus adapting to the rapid iteration of business rules.
[0058] It should also be noted that if the original database query statement contains a calculation expression corresponding to the target business indicator, but the preset function library does not contain a standard function corresponding to the target business indicator, either of the following two processing methods can be executed: First, retain the calculation expression in the original database query statement, but mark the original database query statement; Second, immediately trigger an alarm indication so that a standard function corresponding to the target business indicator can be manually configured temporarily before replacement.
[0059] Therefore, step S102 can solve the problem of inconsistency between the calculation method of business indicators in the original database query statement and the definition of enterprise standards. Its core lies in replacing the non-standard calculation expression in the original database query statement with a standardized preset function that corresponds to the target business indicator.
[0060] S103: Through the second correction module of the criterion layer, identify the detached fields in the first database query statement, and complete the table information corresponding to the detached fields in the first database query statement to obtain the second database query statement.
[0061] It is understandable that if the original database query statement does not include a calculation expression corresponding to the target business indicator, then the original database query statement can be used as the first database query statement to execute step S103.
[0062] A detached field is a field referenced in the first database query statement, but whose table information cannot be explicitly and uniquely determined by the current SQL's FROM and / or JOIN clauses. The presence of detached fields in a database query statement may pose two risks: first, the database may be unable to identify the field's attribution and therefore cannot determine the query results; second, the database may identify the wrong field's attribution and generate incorrect query results.
[0063] In one specific implementation, the second correction module of the criteria layer can determine detached fields using the following method: First, determine the set of fields in the first database query statement. Then, determine whether a target field exists in the field set that simultaneously satisfies the following two conditions: First, the target field is not qualified by a table name or alias (i.e., the target field is not in the form of "table name.field name" or "alias.field name"); second, the FROM clause and / or JOIN clause of the first database query statement includes zero or more table information corresponding to the target field. If the target field satisfies both of the above conditions, then the target field is determined to be a detached field.
[0064] In one example, if the field is `orders.amount`, it is directly determined to be a non-detached field because it is explicitly qualified by the table name. In another example, if the field is `region_name`, not qualified by any table name or alias, and the field exists in both the `regions` table and the `backup_regions` table included in the `FROM` and / or `JOIN` clauses, then `region_name` is determined to be a detached field. In yet another example, if the field is `amount`, not qualified by any table name or alias, and no table in the `FROM` and / or `JOIN` clauses contains the field, then `region_name` is determined to be a detached field.
[0065] After identifying the detached fields in the first database query statement, the second correction module of the criteria layer needs to complete the table information corresponding to the detached fields to obtain the second database query statement.
[0066] In one specific implementation, the target table, where the primary key or foreign key is a detached field, can first be located in the database. Then, the table information of the target table is added to the JOIN clause, thus obtaining a second database query statement. Here, the database stores all table metadata related to the business logic, including table content, table name, primary key, and foreign key information.
[0067] In another specific implementation, a subset of tables can first be determined from the database based on the semantic information of the natural language statement. This subset consists of tables selected from the database that match the query requirements, based on the semantic information of the natural language statement. Then, from this subset, the target table with a detached primary key or foreign key is located, and its table information is added to the JOIN clause, thus obtaining the second database query statement.
[0068] It should be noted that if the first database query statement does not include a JOIN clause, either of the following two processing methods can be executed: First, after adding a JOIN clause, add the target table information to the JOIN clause; second, add the target table information to the FROM clause. This application does not impose any limitations on this.
[0069] Therefore, step S103 ensures that all fields in the second database query statement have a clear table affiliation, avoiding fundamental errors caused by ambiguous references or missing associations, thereby eliminating the execution risk caused by detached fields.
[0070] S104: The third correction module of the criteria layer modifies the clauses in the second database query statement that violate the preset join rules to obtain the standard database query statement.
[0071] It is understandable that if the first database query statement does not include a free field, the first database query statement can be used as the second database query statement to execute step S104; if the second database query statement does not include a clause that violates the preset join rule, the second database query statement can be used as the final standard database query statement.
[0072] Preset join rules are stored in a preset rule base. This preset rule base can be a collection of table join rules covering syntax compliance, business logic, and performance optimization. Its core design goal is to enforce controllability in table joins, fundamentally eliminating unpredictable join behavior.
[0073] In one specific implementation, the preset join rule could be: when the FROM clause and / or JOIN clause include information from multiple tables, each table needs to be joined with at least one other table through its primary key. In another specific implementation, the preset join rule could be: when a JOIN operation does not have an ON condition, automatically convert JOIN to INNERJOIN and add a foreign key-based join condition. Therefore, the preset join rule can guarantee the determinism and efficiency of the join from a business logic perspective, avoiding the risk of unexpected many-to-many relationships and Cartesian products caused by using ordinary field joins.
[0074] For example, the connection rule base can be as follows:
[0075] { "required_primary_key": { "rule_description": "The join must be based on the primary key", "rule_condition": "The JOIN condition must contain the primary key of table A", "rule_action": "Automatically add a primary key join or prompt for modification", "examples": [ "orders.region_id = regions.region_id", "users.user_id = orders.user_id" ]}, "avoid_cross_join": { "rule_description": "Avoid using CROSS JOIN", "rule_condition": "When the JOIN operation does not have an ON condition", "rule_action": "Automatically convert to INNER JOIN and add a reasonable join condition", "examples": [ "SELECT * FROM table1, table2", "Should be converted to: SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id" ]}}
[0076] It should be noted that this application supports adding, modifying, and deleting preset connection rules in the preset rule base through a visual interface, without modifying the underlying code of the third correction module, thus adapting to the rapid iteration of business rules.
[0077] Therefore, the standard database query statement output by step S104 is not only correct in syntax and basic semantics, but also meets the requirements of enterprise-level production environments in terms of the accuracy of business logic, the standardization of data association, and the performance and security of query execution. It can be directly delivered to the database engine for execution and used to support key decisions.
[0078] It should be noted that the criteria layer, as an independent middleware, is decoupled from the front-end artificial intelligence model and the back-end database, and can be flexibly integrated into different systems (such as e-commerce data query systems and financial statement generation systems). It also supports cross-database type adaptation, reducing system integration and maintenance costs.
[0079] It should also be noted that in practical applications, the three correction modules of the aforementioned criterion layer can be processed serially or in parallel. Serial processing refers to execution in a fixed order of "first correction module → second correction module → third correction module," with the output of the preceding module serving as the input of the following module (i.e., steps S102-S104 above). Parallel processing refers to the simultaneous activation of the three correction modules, processing different issues of the original database query statements, and determining the final standard database query statement based on the three database query statements after each correction module outputs its own. This application does not impose any limitations on this aspect.
[0080] It should also be noted that, in practical applications, the aforementioned criterion layer can utilize any one, two, or three correction modules to generate standard database query statements. This application does not impose any limitations on this.
[0081] Next, the technical solution of this application will be explained in conjunction with practical application scenarios:
[0082] Application Scenario 1:
[0083] First, input the natural language query "Query yesterday's total revenue" into the AI model. The AI model then outputs the raw database query statement. The raw database query statement can be as follows:
[0084] SELECT SUM(amount) AS revenue
[0085] FROM orders
[0086] WHERE order_date = '2023-10-27'
[0087] Subsequently, after receiving the original database query statement, the criteria layer performs the following processing:
[0088] The first step involves the first correction module of the criteria layer identifying the calculation expression SUM(amount) corresponding to the target business indicator "amount" from the original database query statement, and then searching for the preset function CALC_REVENUE(order_amount, refund_amount) in the preset function library. Therefore, the calculation expression SUM(amount) is replaced with the preset function CALC_REVENUE(order_amount, refund_amount) to obtain the first database query statement. The first database query statement can be as follows:
[0089] SELECT CALC_REVENUE(orders.amount, refunds.refund_amount) as revenue
[0090] FROM orders
[0091] WHERE order_date = '2023-10-27';
[0092] The second step involves the second correction module at the criteria layer identifying the detached field `refund_amount` from the first database query statement. Subsequently, it determines the table corresponding to `refunds` in the database. Therefore, by adding a JOIN clause to the first database query statement to complete the table information corresponding to the detached field `refund_amount`, the second database query statement is obtained. The second database query statement can be as follows:
[0093] SELECT CALC_REVENUE(orders.amount, refunds.refund_amount) as revenue
[0094] FROM orders
[0095] LEFT JOIN refunds
[0096] WHERE orders.order_date = '2023-10-27';
[0097] Thirdly, the third correction module of the criteria layer detects that the JOIN clause violates the preset join rule that "when the FROM clause and / or JOIN clause includes information from multiple tables, each table information needs to be joined with at least one other table information through the primary key." Therefore, the JOIN clause is modified to obtain the standard database query statement. The standard database query statement can be shown below:
[0098] SELECT CALC_REVENUE(orders.amount, refunds.refund_amount) as revenue
[0099] FROM orders
[0100] LEFT JOIN refunds ON orders.order_id = refunds.order_id
[0101] WHERE orders.order_date = '2023-10-27';
[0102] Therefore, standard database query statements have compliant business logic, complete field attribution, and compliant join rules, and can be executed directly with results that meet business requirements.
[0103] Application Scenario 2 :
[0104] First, input the natural language query "Query the sales revenue and corresponding customer numbers for each region in Q4 2023, grouped by region and quarter" into the AI model. The AI model then outputs the raw database query statement. The raw database query statement can be as follows:
[0105] SELECT region, SUM(amount) AS revenue, COUNT(user_id) AS customers
[0106] FROM orders
[0107] WHERE order_date BETWEEN '2023-10-01' AND '2023-12-31'
[0108] GROUP BY region;
[0109] Subsequently, after receiving the original database query statement, the criteria layer performs the following processing:
[0110] The first step involves the first correction module of the criteria layer identifying the calculation expression SUM(amount) corresponding to the target business indicator "amount" from the original database query statement, and then searching for the preset function CALC_REVENUE(order_amount, refund_amount) in the preset function library. Therefore, the calculation expression SUM(amount) is replaced with the preset function CALC_REVENUE(order_amount, refund_amount) to obtain the first database query statement. The first database query statement can be as follows:
[0111] SELECT region, CALC_REVENUE(amount, refund_amount) AS revenue, COUNT(user_id) AS customers
[0112] FROM orders
[0113] WHERE order_date BETWEEN '2023-10-01' AND '2023-12-31'
[0114] GROUP BY region;
[0115] The second step involves the second correction module at the criteria layer identifying the following detached fields from the first database query: ① `region`: has no table prefix and is not present in the `orders` table; ② `refund_amount`: has no table prefix and is not present in the `orders` table. Subsequently, the database is used to determine the corresponding tables `regions` and `refunds` for the detached fields `region` and `refund_amount`, respectively. Therefore, by adding a `FROM` clause to the first database query to complete the table information corresponding to the detached fields `regions` and `refunds`, the second database query is obtained. The second database query can be shown below:
[0116] SELECT region,
[0117] CALC_REVENUE(orders.amount, refunds.refund_amount) AS revenue,
[0118] COUNT(orders.user_id) AS customers
[0119] FROM orders, refunds, regions
[0120] WHERE orders.order_date BETWEEN '2023-10-01' AND '2023-12-31'
[0121] GROUP BY region;
[0122] The third step, the third correction module of the criteria layer, detected the following: 1. The FROM clause violated the preset join rule that "when the FROM clause and / or JOIN clause include information from multiple tables, each table information needs to be joined with at least one other table information through the primary key"; 2. The region field did not have a clear table affiliation; 3. COUNT(user_id) was not deduplicated, which could easily lead to statistical errors. Therefore, the above issues were modified to obtain the standard database query statement. The standard database query statement can be shown below:
[0123] SELECT regions.region_name AS region,
[0124] CALC_REVENUE(orders.amount, refunds.refund_amount) AS revenue,
[0125] COUNT(DISTINCT orders.user_id) AS customers
[0126] FROM orders
[0127] LEFT JOIN refunds ON orders.order_id = refunds.order_id
[0128] LEFT JOIN regions ON orders.region_id = regions.region_id
[0129] WHERE orders.order_date BETWEEN '2023-10-01' AND '2023-12-31'
[0130] GROUP BY regions.region_name;
[0131] Therefore, standard database query statements have compliant business logic, complete field attribution, and compliant join rules, and can be executed directly with results that meet business requirements.
[0132] In summary, this application discloses a method for generating database query statements. Three correction modules at the criteria layer form a collaborative correction mechanism: the first correction module replaces the calculated expressions in the original database query statement with standardized preset functions determined based on business indicators, effectively avoiding query result deviations caused by expression differences and improving the quality of generated database query statements; the second correction module automatically identifies detached fields and completes the corresponding table information; and the third correction module corrects non-compliant clauses based on preset join rules. Together, these two modules ensure the logical integrity and join compliance of the database query statement, avoiding the problem of not obtaining query results due to incomplete logic or non-compliant joins, thereby improving the quality of generated database query statements. Furthermore, this application does not require training an artificial intelligence model using a large-scale, comprehensive sample dataset, thus significantly reducing the cost of improving the quality of generated database query statements.
[0133] See Figure 3The figure is a schematic diagram of a database query statement generation device provided in an embodiment of this application. The database query statement generation device 300 includes: a statement acquisition module 301, a function replacement module 302, an information completion module 303, and a clause adjustment module 304.
[0134] Statement acquisition module 301 is used to acquire the original database query statement;
[0135] The function replacement module 302 is used to replace the calculation expression corresponding to the target business indicator in the original database query statement with a preset function corresponding to the target business indicator through the first correction module of the criterion layer, so as to obtain the first database query statement;
[0136] The information completion module 303 is used to identify the detached fields in the first database query statement through the second correction module of the criteria layer, and complete the table information corresponding to the detached fields in the first database query statement to obtain the second database query statement;
[0137] Clause adjustment module 304 is used to modify clauses in the second database query statement that violate preset join rules through the third correction module of the criteria layer, so as to obtain a standard database query statement.
[0138] In one specific implementation, the first database query statement includes a FROM clause and / or a JOIN clause;
[0139] The information completion module 303 is specifically used to: determine the set of fields in the first database query statement; if there is a target field in the set of fields that is not qualified by a table name or alias, and the FROM clause and / or JOIN clause includes 0 or more table information corresponding to the target field, then the target field is determined to be a detached field.
[0140] In one specific implementation, the information completion module 303 is used to: find the target table in the database where the primary key or foreign key is a detached field; and add the table information of the target table in the JOIN clause.
[0141] In one specific implementation, the original database query statement is generated based on the natural language statement; the information completion module 303 is specifically used to: determine a subset of tables from the database based on the semantic information of the natural language statement; and from the subset of tables, find the target table whose primary key or foreign key is a detached field.
[0142] In one specific implementation, the default join rule is: when the FROM clause and / or JOIN clause include multiple table information, each table information needs to be joined with at least one other table information through the primary key.
[0143] In summary, this application discloses a database query statement generation device. Three correction modules at the criteria layer form a collaborative correction mechanism: the first correction module replaces the calculated expressions in the original database query statement with standardized preset functions determined based on business indicators, effectively avoiding query result deviations caused by expression differences and improving the generation quality of the database query statement; the second correction module automatically identifies detached fields and completes the corresponding table information; and the third correction module corrects non-compliant clauses based on preset join rules. Together, these two modules ensure the logical integrity and join compliance of the database query statement, avoiding the problem of not obtaining query results due to incomplete logic or non-compliant joins, thereby improving the generation quality of the database query statement. Furthermore, this application does not require training an artificial intelligence model using a large-scale, comprehensive sample dataset, thus significantly reducing the cost of improving the generation quality of the database query statement.
[0144] This application also provides a corresponding database query statement generation device and a computer-readable medium for implementing the database query statement generation method provided in this application.
[0145] The database query statement generation device includes a memory and a processor. The memory is used to store instructions or code, and the processor is used to execute the instructions or code so that the device executes a database query statement generation method according to any embodiment of this application.
[0146] See Figure 4 This figure is a schematic diagram of a computer-readable medium provided in an embodiment of this application. The computer-readable medium 400 stores a computer program 411, which, when executed by a processor, implements the above-described... Figure 1 The steps for generating database query statements.
[0147] It should be noted that, in the context of this application, a machine-readable medium can be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, apparatus, or device. A machine-readable medium can be a machine-readable signal medium or a machine-readable storage medium. Machine-readable media can be, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.
[0148] It should be noted that the machine-readable medium described above in this application can be a computer-readable signal medium or a computer-readable storage medium, or any combination of the two. A computer-readable storage medium can be, for example,—but not limited to—an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of a computer-readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination thereof. In this application, a computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device. In this application, a computer-readable signal medium can include a data signal propagated in baseband or as part of a carrier wave, carrying computer-readable program code. Such propagated data signals can take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. A computer-readable signal medium can be any computer-readable medium other than a computer-readable storage medium, which can send, propagate, or transmit a program for use by or in connection with an instruction execution system, apparatus, or device. The program code contained on the computer-readable medium can be transmitted using any suitable medium, including but not limited to: wires, optical fibers, RF (radio frequency), etc., or any suitable combination thereof.
[0149] The aforementioned computer-readable medium may be included in the aforementioned electronic device; or it may exist independently and not assembled into the electronic device.
[0150] Although the subject matter has been described using language specific to structural features and / or methodological logic, it should be understood that the subject matter defined in the appended claims is not necessarily limited to the specific features or actions described above. Rather, the specific features and actions described above are merely illustrative examples of implementing the claims.
[0151] While several specific implementation details are included in the foregoing discussion, these should not be construed as limiting the scope of this application. Certain features described in the context of individual embodiments may also be implemented in combination in a single embodiment. Conversely, various features described in the context of a single embodiment may also be implemented individually or in any suitable sub-combination in multiple embodiments.
[0152] The above description is merely a preferred embodiment of this application and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of disclosure in this application is not limited to technical solutions formed by specific combinations of the above-described technical features, but should also cover other technical solutions formed by arbitrary combinations of the above-described technical features or their equivalents without departing from the above-described concept. For example, technical solutions formed by substituting the above features with (but not limited to) technical features with similar functions disclosed in this application.
Claims
1. A method for generating database query statements, characterized in that, The method includes: Obtain the original database query statement; The first correction module of the criteria layer replaces the calculation expression corresponding to the target business indicator in the original database query statement with a preset function corresponding to the target business indicator to obtain the first database query statement. The second correction module of the criterion layer identifies the detached fields in the first database query statement and completes the table information corresponding to the detached fields in the first database query statement to obtain the second database query statement. The third correction module of the criterion layer modifies the clauses in the second database query statement that violate the preset connection rules to obtain a standard database query statement.
2. The method according to claim 1, characterized in that, The first database query statement includes a FROM clause and / or a JOIN clause; identifying detached fields in the first database query statement includes: Determine the set of fields in the first database query statement; If, in the set of fields, there exists a target field that is not qualified by a table name or alias, and the FROM clause and / or JOIN clause includes zero or more table information corresponding to the target field, then the target field is determined to be a detached field.
3. The method according to claim 2, characterized in that, The step of completing the table information corresponding to the detached field in the first database query statement includes: From the database, find the target table whose primary key or foreign key is the aforementioned free field; Add the table information of the target table to the JOIN clause.
4. The method according to claim 3, characterized in that, The original database query statement is generated based on natural language statements; the step of searching the database for the target table whose primary key or foreign key is the detached field includes: Based on the semantic information of the natural language statements, a subset of tables is determined from the database; From the subset of tables, find the target table whose primary key or foreign key is the free field.
5. The method according to claim 2, characterized in that, The preset connection rule is as follows: when the FROM clause and / or JOIN clause includes multiple table information, each table information needs to be connected to at least one other table information through the primary key.
6. A device for generating database query statements, characterized in that, The device includes: a statement acquisition module, a function replacement module, an information completion module, and a clause adjustment module; The statement acquisition module is used to acquire the original database query statement; The function replacement module is used to replace the calculation expression corresponding to the target business indicator in the original database query statement with a preset function corresponding to the target business indicator through the first correction module of the criterion layer, so as to obtain the first database query statement; The information completion module is used to identify the detached field in the first database query statement through the second correction module of the criterion layer, and complete the table information corresponding to the detached field in the first database query statement to obtain the second database query statement; The clause adjustment module is used to modify the clauses in the second database query statement that violate the preset connection rules through the third correction module of the criterion layer, so as to obtain a standard database query statement.
7. The apparatus according to claim 6, characterized in that, The first database query statement includes a FROM clause and / or a JOIN clause; The information completion module is specifically used to: determine the set of fields in the first database query statement; if there is a target field in the set of fields that is not limited by a table name or alias, and the FROM clause and / or JOIN clause includes 0 or more table information corresponding to the target field, then the target field is determined to be a detached field.
8. The apparatus according to claim 7, characterized in that, The information completion module is specifically used to: find the target table whose primary key or foreign key is the free field from the database; and add the table information of the target table to the JOIN clause.
9. A device for generating database query statements, characterized in that, The device includes: a memory and a processor; The memory is used to store programs; The processor is configured to execute the program to implement each step of the method for generating a database query statement as described in any one of claims 1 to 5.
10. A computer-readable medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements each step of the method for generating a database query statement as described in any one of claims 1 to 5.