Generation method and generation device of database query language

Abstract

The invention discloses a database query language generation method and device. The generation method comprises the steps of obtaining an entity set, an intention type and an intention classification probability in question information according to the question information input by a user; based on the entity set, the intention type and the intention classification probability, target tables are screened out from candidate tables of a preset metadata graph, and foreign keys between the target tables are obtained according to foreign key activeness weights; and obtaining a database query statement corresponding to the question information based on the target table, the foreign key and a preset constraint condition. According to the generation method, the candidate table can be subjected to intention type and intention classification probability filtering and then matched with the entity set, and the precision of selecting the target table in the candidate table is improved. According to the generation method, the foreign keys are obtained according to the foreign key activeness weights, the foreign keys with high activeness represent that the relevance between the two target tables is high, and the probability of establishing the JOIN path is high.

Description

Technical Field

[0001] This application relates to the field of software, and more specifically, to a method and apparatus for generating a database query language. Background Technology

[0002] As databases grow in size, users expect to generate database queries directly using natural language to lower the barrier to entry. Current technologies generally employ vector retrieval, selecting the target table and constructing the database query by calculating the similarity between the query information and candidate tables. However, in environments with hundreds of tables, the sheer number of candidate tables and the complex relationships between them result in low accuracy of JOIN paths and high error rates in generated database queries during cross-domain or multi-hop scenarios. Summary of the Invention

[0003] This application provides a method and apparatus for generating a database query language.

[0004] Firstly, this application provides a method for generating a database query language. The method includes: obtaining an entity set, intent type, and intent classification probability from user-inputted question information; selecting target tables from a preset metadata graph candidate table based on the entity set, intent type, and intent classification probability, and obtaining foreign keys between the target tables based on foreign key activity weights; and obtaining a database query statement corresponding to the question information based on the target tables, the foreign keys, and preset constraints.

[0005] In some implementations, the candidate tables have a preset domain, which includes multiple sub-domains. The step of selecting target tables from the candidate tables in the preset metadata graph and obtaining foreign keys between the target tables based on foreign key activity weights includes: obtaining semantic relevance based on the semantic matching degree between the entity set and the description information of the candidate tables; obtaining intent relevance based on the intent classification probability and a preset domain weight coefficient, where the domain weight coefficient represents the weight of the sub-domain corresponding to the candidate table relative to the intent type; obtaining performance relevance based on the historical query time and cache hit rate of the candidate tables; and obtaining candidate tables that meet preset requirements as target tables based on the semantic relevance, the performance relevance, and the intent relevance.

[0006] In some implementations, obtaining candidate tables that meet preset requirements as target tables based on the semantic relevance, performance relevance, and intent relevance includes: obtaining a score for each candidate table based on the semantic relevance, performance relevance, and intent relevance; sorting the candidate tables based on the scores; if the number of candidate tables is less than or equal to a preset parameter, retaining the top three candidate tables as the target table; and if the number of candidate tables is greater than the preset parameter, retaining the top min(5,√N) candidate tables as the target table. N represents the number of candidate tables.

[0007] In some implementations, determining whether the entity set and / or intent type belongs to a cross-domain query includes: determining whether the entities in the entity set belong to different domains; obtaining the current user's permission identifier and determining whether the permission identifier meets preset conditions; and determining that the entity belongs to a cross-domain query if the entity spans different subdomains under the domain and the permission identifier meets preset conditions.

[0008] In some implementations, obtaining the candidate table as the target table based on the semantic relevance, the performance relevance, and the intent relevance, which meets the preset requirements, further includes: determining whether the entity set and / or intent type belongs to a cross-topic domain query; and if it belongs to a cross-topic domain query, selecting the target foreign key from the candidate foreign keys in the target table based on the foreign key activity weight, wherein the foreign key activity weight is the ratio of the number of times the candidate foreign key is used in historical queries to the total number of historical queries.

[0009] In some implementations, obtaining the database query statement corresponding to the problem information based on the target table, the foreign key, and preset constraints includes: obtaining an initial query statement based on the constraints; determining whether the initial query statement meets the output conditions; if the initial query statement meets the output conditions, outputting the initial query statement as the database query statement; and if the initial query statement does not meet the output conditions, adjusting the initial query statement based on an abstract syntax tree until it meets the output conditions, and outputting the adjusted initial query statement as the database query statement.

[0010] In some implementations, obtaining an initial query statement based on the target table and the target foreign key includes: the constraints include a syntax constraint channel; obtaining a preset rule based on the syntax constraint channel, the preset rule being used to limit the syntax structure of the initial query statement; the constraints include a semantic guidance channel; obtaining a query template corresponding to the intent type based on the semantic guidance channel; and obtaining the initial query statement based on the preset rule, the query template, the target table, and the target foreign key.

[0011] In some implementations, adjusting the initial query statement based on the abstract syntax tree includes: parsing the initial query statement based on the abstract syntax tree to obtain syntax nodes; classifying errors in the initial query statement based on the syntax nodes to obtain error types; and adjusting the initial query statement based on the error types to obtain an adjusted query statement that meets the output conditions.

[0012] In some implementations, adjusting the initial query statement based on the error type to obtain an adjusted query statement that meets the output conditions includes: if the error type is a missing join condition, completing the missing join condition based on the target foreign key; if the error type is an incorrect position of the aggregate function, moving the aggregate function from the query condition area of ​​the initial query statement to the grouping statistics area of ​​the initial query statement; and if the error type is an ambiguous field name, adding an alias prefix of the target table to the ambiguous fields in the target table.

[0013] In some implementations, the generation method further includes: obtaining performance metrics after the database query statement is executed, determining whether the user manually corrects the database query statement; determining a reward value based on the execution performance metrics and whether manual correction exists; and updating the foreign key activity weight based on the reward value, a preset learning rate, and the historical usage count of the target foreign key.

[0014] Secondly, this application provides a database query language generation apparatus, the apparatus including a memory and a processor, the memory for storing instructions. The instructions stored in the memory are executed by the processor to implement the generation method described in any of the above embodiments.

[0015] In the database query language generation method and apparatus of this application, the generation method enables candidate tables to be filtered by intent type and intent classification probability before being matched with entity sets, thereby improving the accuracy of selecting target tables from candidate tables. Furthermore, the generation method obtains foreign keys based on foreign key activity weights. Foreign keys with high activity represent a strong relationship between the two target tables, increasing the probability of establishing a JOIN path. Therefore, obtaining foreign keys based on foreign key activity weights and constructing database query statements can improve the accuracy of JOIN paths and reduce the error rate of generated database query statements, thus lowering the barrier to entry for users to use databases.

[0016] Additional aspects and advantages of the invention will be set forth in part in the description which follows, and in part will be obvious from the description, or may be learned by practice of the invention. Attached Figure Description

[0017] The above and / or additional aspects and advantages of the present invention will become apparent and readily understood from the description of the embodiments taken in conjunction with the following drawings, in which: Figure 1 This is a schematic diagram illustrating the principle architecture of the generation method for some embodiments of this application; Figure 2 This is a flowchart illustrating the generation method of some embodiments of this application; Figure 3 This is a flowchart illustrating the generation method of some embodiments of this application; Figure 4 This is a flowchart illustrating the generation method of some embodiments of this application; Figure 5 This is a flowchart illustrating the generation method of some embodiments of this application; Figure 6 This is a flowchart illustrating the generation method of some embodiments of this application; Figure 7 This is a flowchart illustrating the generation method of some embodiments of this application; Figure 8 This is a flowchart illustrating the generation method of some embodiments of this application; Figure 9 This is a flowchart illustrating the generation method of some embodiments of this application; Figure 10 This is a flowchart illustrating the generation method of some embodiments of this application; Figure 11 This is a flowchart illustrating the generation method of some embodiments of this application; Figure 12 This is a schematic diagram of a portion of the structure of the generating apparatus according to certain embodiments of this application.

[0018] Figure label: Generating device 100; memory 10; processor 30. Detailed Implementation

[0019] Embodiments of the present invention are described in detail below. Examples of these embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals denote the same or similar elements or elements having the same or similar functions throughout. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention, and should not be construed as limiting the present invention.

[0020] In the description of this invention, it should be understood that the terms "center," "longitudinal," "lateral," "length," "width," "thickness," "upper," "lower," "front," "rear," "left," "right," "vertical," "horizontal," "top," "bottom," "inner," "outer," "clockwise," "counterclockwise," "axial," "radial," and "circumferential," etc., indicating orientation or positional relationships, are based on the orientation or positional relationships shown in the accompanying drawings and are only for the convenience of describing the invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation, and therefore should not be construed as a limitation of the invention. Furthermore, features defined with "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of this invention, unless otherwise stated, "a plurality of" means two or more. In the description of this invention, it should be noted that, unless otherwise explicitly specified and limited, the terms "installation," "connection," and "linking" should be interpreted broadly. For example, they can refer to a fixed connection, a detachable connection, or an integral connection; they can refer to a mechanical connection or an electrical connection; they can refer to a direct connection or an indirect connection through an intermediate medium; and they can refer to the internal connection of two components. Those skilled in the art can understand the specific meaning of the above terms in this invention based on the specific circumstances.

[0021] Please see Figure 1 and Figure 2 This application provides a method for generating a database query language. The generation method includes: 03: Based on the question information input by the user, obtain the entity set, intent type, and intent classification probability from the question information; 05: Based on entity sets, intent types, and intent classification probabilities, select target tables from the pre-defined candidate tables of the metadata graph, and obtain the foreign keys between target tables according to foreign key activity weights; and 07: Based on the target table, foreign keys, and preset constraints, obtain the database query statement corresponding to the problem information.

[0022] Specifically, in the method of 03, the user-input question information represents a data query request presented in natural language. The entity set represents specific nouns or values ​​contained in the question that are related to database tables, fields, or query constraints. For example, this application takes the question information "iPhone sales revenue in East China" as a reference. The process of obtaining the entity set is as follows: the question information is fed into a pre-built BERT-BiLSTM-CRF model. The BERT-BiLSTM-CRF model generates a context vector through the BERT layer, which is then labeled by BiLSTM sequence and decoded by CRF to output a BIO label sequence. This identifies "East China" as a geographical entity, "iPhone" as a product entity, and "sales revenue" as an indicator entity. Multiple entities constitute the entity set.

[0023] Intent type represents the category of the query operation that the user expects to perform. Categories include, but are not limited to, data filtering, aggregation statistics, or relationship comparison. Intent classification probability represents the confidence level of the intent type identification result. The process of obtaining intent type and intent classification probability is as follows: The same question information is fed into a pre-built TextCNN model. The TextCNN model extracts local semantic features using multi-channel convolution and outputs the intent classification probability through a fully connected layer. For example, if the probability of the "aggregation query" category is the highest, the generation device 100 determines the intent type as aggregation query and outputs the corresponding Softmax probability value as the intent classification probability, which is used for weight calculation when filtering the target table later. The Softmax probability value is a set of "confidence" numbers between 0 and 1, summed to 1, converted from the raw scores of various intents by the last layer of the TextCNN model. The item with the largest value is taken as the intent type, and its corresponding value is the intent classification probability, used to quantify the reliability of the determination result. The acquisition of entity set, intent type, and intent classification probability can be packaged into a semantic understanding layer in the generation method and updated as a whole.

[0024] The preset metadata graph is a database metadata organized in a graph structure. The metadata graph is generated during the generation method's computation; each time, it only needs to be loaded and invoked. The metadata graph includes candidate tables as table nodes, field nodes, and foreign key relationships connecting different tables. Candidate tables are the set of all tables in the metadata graph that may be related to the problem information. Target tables are the set of data tables ultimately determined after filtering to construct the query statements. Foreign key activity weight represents the ratio of the number of times a particular foreign key is actually used in a join to the total number of queries, quantifying the priority of that foreign key. For example, foreign key activity weight = historical JOIN usage counts / total number of queries. Compared to a static ER diagram, adding foreign key activity weight allows the metadata graph to reflect user query preferences. When filtering target tables, the generation device 100 first calculates the relevance of candidate tables in the metadata graph using entity sets, intent types, and intent classification probabilities, selecting the target tables most relevant to the requirements. Then, it reads the foreign key edges between target tables and selects the required foreign keys based on the foreign key activity weight, thereby determining the connection relationships between the target tables. Predefined constraints characterize the syntax, semantics, and performance rules used to guide query statement generation. Database query statements characterize query code (e.g., SQL statements) that precisely correspond to the question information and can be directly executed in the database, thus enabling automated conversion from natural language to database query language.

[0025] In the database query language generation method of this application, the generation method first filters candidate tables based on intent type and intent classification probability, and then matches them with the entity set, improving the accuracy of selecting target tables from the candidate tables. Furthermore, the generation method obtains foreign keys based on foreign key activity weights. Foreign keys with high activity represent a strong relationship between the two target tables, increasing the probability of establishing a JOIN path. Therefore, obtaining foreign keys based on foreign key activity weights and constructing database query statements can improve the accuracy of JOIN paths and reduce the error rate of generated database query statements, thereby lowering the barrier to entry for users using databases.

[0026] Please see Figure 1 and Figure 3 In some implementations, the candidate tables have a preset domain, which includes multiple sub-domains. 05: Selecting target tables from the candidate tables of the preset metadata graph and obtaining foreign keys between target tables based on foreign key activity weights includes: 051: Obtain semantic relevance based on the semantic matching degree between the entity set and the candidate table's description information. S semantic ; 052: Obtaining Intent Relevance Based on Intent Classification Probability and Preset Domain Weight Coefficients S intentThe domain weight coefficient represents the weight of the subdomain corresponding to the candidate table relative to the intent type; 053: Obtain performance relevance based on the historical query time of the candidate tables and the cache hit rate of the candidate tables. S perf ;and 055: Based on semantic relevance S semantic Intent relevance S intent and performance correlation S perf The candidate table that meets the preset requirements is obtained as the target table.

[0027] Specifically, candidate tables have a predefined domain, which includes multiple sub-domains. The domain is the top-level classification of the business affiliation of candidate tables, used to categorize tables in the database by business segment. Each domain is further subdivided into several sub-domains, which are classifications within the same business segment. For example, taking the manufacturing domain as an example, the manufacturing domain includes four sub-domains: production management, equipment maintenance, supply chain, and quality inspection. It is understandable that a candidate table can belong to multiple domains, and for a candidate table within a given domain, the candidate table can correspond to one or more sub-domains.

[0028] Obtain semantic relevance S semantic Semantic relevance characterizes the literal association between keywords in the user's query and the content of the candidate table, measuring how well the candidate table matches the user's query literally. Example: Semantic relevance. S semantic satisfy: S semantic = Word2Vec ( entity (Description information) × Terminology matching degree Word2Vec refers to a static word vector mapping method. This method first trains each word in a pre-defined domain dictionary into a fixed-length numerical vector, making semantically similar words closer together in the vector space. "Entity" refers to the entities identified in the O3 method. "Descriptive information" refers to the natural language text pre-defined for each candidate table in the metadata graph. This text typically includes the table name, business meaning, core fields, and their functions, used to determine whether the candidate table is relevant to the problem information through text similarity calculation. "Term matching degree" characterizes the degree of matching between entities in the domain dictionary and candidate tables.

[0029] During calculation, entities are mapped to word vectors, and the descriptive information of the candidate table is mapped to text vectors. Cosine similarity is calculated between word vectors and text vectors, outputting a similarity score in the range of 0–1. The term matching score further calculates the direct hit ratio between entities in the domain dictionary and field names in the candidate table (e.g., 1 for a hit, 0.5 for a partial hit, and 0 for no hit), used to weight and adjust the similarity score. The two scores are then multiplied to obtain the semantic relevance. S semantic The larger the value, the more semantically relevant the candidate list is to the user's input question information.

[0030] Intent relevance S intent Domain weight coefficients characterize the degree of fit between the candidate table's corresponding subdomain and the current intent type. Intent relevance. S intent satisfy: S intent = Intent classification probability × domain weight coefficient Domain weight coefficients are parameters used to distinguish the importance of multiple subdomains within a given domain, representing the relevance of the subdomain corresponding to a candidate table to the intent type. Even within the same domain, the association priority of tables in different subdomains varies significantly, and the domain weight coefficient assigns different weights to subdomains based on this association priority. A larger weight value indicates a higher association priority for the subdomain under the intent type.

[0031] For example, if the generation device 100 identifies a candidate table for a user intent type of "device-production related intent" and involving both the "equipment operation and maintenance subdomain" and the "production management subdomain," the generation device 100 will assign different domain weight coefficients to these two subdomains. Finally, by multiplying the intent classification probability (e.g., 0.95) by the average of the domain weight coefficients, the relevance of the candidate table to the current intent type is determined. S intent .

[0032] More specifically, taking the manufacturing sector as an example, when a user asks "What is the relationship between the failure rate of stamping equipment and the daily production task?", the intent classification probability is 0.95. The failure rate of stamping equipment corresponds to the equipment operation and maintenance subdomain, with a domain weight coefficient of 0.7, while the production task corresponds to the production management subdomain, with a domain weight coefficient of 0.9. S intent =Intent Classification Probability × Domain Weight Coefficient = 0.95 (Device-Production Related Intent Probability) × (0.7 + 0.9) / 2 = 0.76. A higher result indicates a better match between the candidate table and the user's intent in terms of business, thus securing a higher position in the subsequent score ranking.

[0033] Performance Relevance S perf This characterizes the table's performance in database queries, helping to avoid filtering candidate tables that are inefficient or slow. For example, performance relevance... S perf satisfy: S perf =1 / (Average query time + ε) + 0.5 × cache hit rate The average query time (or historical query time) represents the average time spent querying the candidate table in the past; the shorter the time, the higher the query efficiency. The cache hit rate represents the frequency with which data from the candidate table is cached; a higher hit rate indicates that the query does not require access to the underlying storage, resulting in better performance. The cache hit rate ranges from [0,1], and a coefficient of 0.5 is used to adjust the cache hit rate's relevance to performance. S perf The contribution weight is determined by the formula. The constant ε=0.01 is introduced to prevent division by zero errors when the average query time is extremely short (approaching zero), ensuring the stability of the calculation. This allows for the acquisition of semantic relevance. S semantic Intent relevance S intent and performance correlation S perf It can provide a basis for selecting targets for defenders.

[0034] Please see Figure 1 and Figure 4 In some implementations, 055: based on semantic relevance S semantic Intent relevance S intent and performance correlation S perf Obtain candidate tables that meet preset requirements as the target table, including: 0551: Based on the candidate table and semantic relevance S semantic Intent relevance S intent and performance correlation S perf Get the score for each candidate table S core Based on scores S core Sort the candidate table; 0553: Number of candidate tables N If the values ​​are less than or equal to the preset parameters, the top three candidate tables in the sorted list are retained as the target table; and 0555: Number of candidate tables N If the value is greater than the preset parameter, the candidate table with the first min(5,√N) positions is retained as the target table.

[0035] Specifically, in the method of 0551, semantic relevance is used as the basis. S semantic Intent relevance S intent and performance correlation S perf Scoring based on these three metrics S core ,Score S core satisfy: S core = α × S semantic + β × S intent + γ × S perf middle, α , β and γ The three factors represent semantic relevance. S semantic Intent relevance S intent and performance correlation S perf The weights of the three indicators. α , β and γ The three factors are configurable parameters that can be adjusted based on actual business scenarios or query characteristics. To ensure the standardization of the calculation, α , β and γ The sum is 1, that is α + β + γ =1. For example, the default value for the weights of the three factors can be set to: α = 0.6, β = 0.3, γ =0.1. The score was calculated. S core All candidate tables can be quantitatively compared and ranked, providing a basis for subsequent selection of target tables, such as scores. S core The higher the value, the better the relevance, business fit, and query performance of the candidate table to the user query, and the higher its priority in being selected as the target table.

[0036] In method 0553, the preset parameter is "total number of candidate tables". N "The set threshold is used to switch the pruning strategy; for example, the preset parameter in this application is pre-configured to 50, when..." N The "keep the top 3" rule is triggered when the value is ≤50. N When the value is greater than 50, the "retain min(5,√N)" rule is triggered, thereby controlling the size and computation time of subsequent JOINs while ensuring the recall of related tables.

[0037] When the total number of candidate tables N When the score is less than or equal to 50, the pruning strategy is to retain branches based on their scores. S core The top 3 candidate tables after sorting are used as the target table to quickly identify the target table in scenarios with small table sizes and to avoid subsequent JOIN expansion. When the total number of candidate tables... N For numbers greater than 50, the pruning strategy uses a square root scaling strategy to retain the desired number. K The candidate table is used as the target table, where, K = min(5, √ N ),quantity K With the total number of candidate tables N Increase and grow, ensuring both the breadth of recall under large datasets and the quantity K The upper limit is controlled to within 5 tables, enabling dynamic pruning. Actual testing shows that the pruning strategy of this application reduces the average routing time from over 1200ms in the traditional static scheme to less than 240ms, maintaining sub-second response even in scenarios with hundreds of tables, thereby improving the generation speed of query commands.

[0038] Please see Figure 1 and Figure 5 In some implementations, 055: Based on semantic relevance, performance relevance, and intent relevance, obtaining candidate tables that meet preset requirements as target tables further includes: 0557: Determine whether the entity set and / or intent type belong to a cross-topic domain query; and 0559: In the case of cross-topic domain queries, the target foreign key is selected from the candidate foreign keys among the target tables based on the foreign key activity weight. The foreign key activity weight is the ratio of the number of times the candidate foreign key is used in historical queries to the total number of historical queries.

[0039] Upon determining that a query is cross-subject domain, the subject domain gateway is activated to add the target foreign key (i.e., the cross-subject foreign key). Candidate foreign keys represent all possible join relationships between two or more target tables, while the target foreign key is the one ultimately selected and used to generate the query statement. Foreign key activity weight represents the ratio of the number of times a candidate foreign key has been used in historical queries to the total number of historical queries, used for foreign key reliability and user preference. In the case of a cross-subject domain query, the generation device 100 prioritizes selecting the target foreign key based on the foreign key activity weight to prevent cross-domain Cartesian product problems caused by missing join conditions, thereby ensuring the accuracy and efficiency of the generated database query statement in cross-domain scenarios.

[0040] Load metadata graph, score S core Dynamic pruning and activation of topic domain gateways, along with the addition of cross-topic foreign keys, can be packaged into a graph routing layer in the generation method and updated as a whole.

[0041] Please see Figure 1 and Figure 6 In some implementations, 0557: Determining whether the entity set and / or intent type belongs to a cross-topic domain query includes: 05571: Determine whether entities in an entity set belong to different domains; 05573: Obtain the current user's permission identifier and determine whether the permission identifier meets preset conditions; and 05575: If an entity spans different subdomains under its own domain and the permission identifier meets the preset conditions, it is determined to be a cross-domain query.

[0042] Specifically, scan all entities in the entity set. If at least two entities in the entity set come from different subdomains under the same domain, for example, one entity belongs to the "equipment operation and maintenance subdomain" and the other entity belongs to the "production management subdomain", they are considered to "belong to different domains". Then continue to determine whether it is a cross-domain query. Otherwise, directly proceed to the above-mentioned process of obtaining the target table based on preset parameters.

[0043] After determining that the domains belong to different subdomains, the current user's permission identifier is obtained. This identifier is the user's role's authorization token in the subject domain gateway. The authorization token is compared with preset conditions, which consist of a role-domain whitelist. The condition is considered "satisfied" only if the role carried by the permission identifier has data access rights to all relevant domains and their subdomains. Otherwise, the cross-domain process is terminated and a permission error is returned. For example, a role with the character "Finance Specialist" can access the Supply Chain subdomain. If the domains belong to different subdomains and the permission identifier meets all preset conditions, it is determined to be a cross-subdomain query. Determining a cross-subdomain query is used to activate the cross-domain gateway.

[0044] Please see Figure 1 and Figure 7 In some implementations, based on the target table, foreign keys, and preset constraints, a database query statement corresponding to the problem information is obtained, including: 071: Obtain the initial query statement based on constraints; 073: Determine if the initial query statement meets the output conditions; 075: If the initial query statement meets the output conditions, output the initial query statement as the database query statement; and 077: If the initial query statement does not meet the output conditions, adjust the initial query statement based on the abstract syntax tree until it meets the output conditions, and output the adjusted initial query statement as the database query statement.

[0045] Specifically, the initial query statement is the first version of the database query statement constructed based on the target table and foreign keys, combined with preset constraints (e.g., syntax and semantic requirements). This construction can be based on a preset large model. The output conditions characterize the final criteria for whether the query statement can be successfully executed by the database and return valid results; for example, SQL syntax correctness, semantic completeness, and unambiguity. If the initial query statement meets the output conditions, it is directly output as the database query statement.

[0046] If the initial query statement does not meet the output conditions, the generation device 100 will adjust the initial query statement based on an Abstract Syntax Tree (AST) until it meets the output conditions, and then output the adjusted initial query statement as the database query statement. An Abstract Syntax Tree is a data structure that decomposes the component structure of the initial query statement into a tree-like representation, which helps the generation device 100 locate and specifically correct errors in the initial query statement. Through adjustments based on the Abstract Syntax Tree, potential syntax errors, missing joins, or field ambiguities in the initial query statement can be resolved, ensuring that the final output database query statement is executable and accurate.

[0047] Please see Figure 1 and Figure 8 In some implementations, 071: Based on the target table and the target foreign key, obtain the initial query statement, including: 0711: Constraints include syntax constraint channels, which are used to obtain preset rules. These preset rules are used to limit the syntax structure of the initial query statement. 0713: Constraints include semantic guidance channels, which are used to obtain query templates corresponding to intent types; and 0715: Based on preset rules and query templates, as well as the target table and target foreign keys, obtain the initial query statement.

[0048] Specifically, syntax constraint channels represent the hard syntax rules that must be followed in SQL, used to limit the syntactic structure of the initial query statement. Hard syntax rules include, but are not limited to: prohibiting the use of aggregate functions (such as SUM or AVG) in the WHERE clause; requiring JOIN operations to explicitly declare the ON condition clause; and requiring the use of table aliases to qualify fields (e.g., using table name.field instead of just field). Hard syntax rules ensure that the generated initial query statement conforms to basic SQL syntax, avoiding excessive subsequent modifications and improving the speed of generating the final database query statement.

[0049] Semantic guidance channels represent templates or examples based on domain knowledge and historical success stories. By matching the user-identified intent type (e.g., "aggregate query") with pre-stored query templates corresponding to the intent type (i.e., few-shot examples), domain-adaptive semantic structure guidance can be provided for the generation of the initial query statement, thereby improving the accuracy of the initial query statement. For example, the query template can be a few-shot example, which represents a set of pre-constructed, structured question-answer pair examples to guide the generation device 100 in mimicking the structure and style of these examples when faced with new question information. Specifically, each example includes a natural language question, a corresponding database structure description (target table, fields, relations), and a correct, compliant database query statement (i.e., the query template).

[0050] For example, if the problem information is "Find the average thickness deviation of the last 10 processes processed by stamping machine A", An example of few-shot is: Device table (device_id, name) Processing record table (record_id, device_id, thickness, timestamp) Quality Inspection Report (record_id, deviation) Output example: SELECT AVG(qc.deviation) AS avg_deviationFROM device_table_dev JOIN the processing record table rec on dev.device_id = rec.device_id JOIN the quality control sheet qc ON rec.record_id = qc.record_id WHERE dev.name = 'Punching Machine A' AND rec.timestamp>DATE_SUB(NOW(), INTERVAL30 DAY) GROUP BY dev.device_id LIMIT 10.

[0051] Thus, by using a dual-channel approach (syntax constraint channel and semantic guidance channel) to obtain and preset rules and query templates, as well as the target table and target foreign key, the initial query statement can be obtained, thereby improving the speed of generating the initial query statement.

[0052] Please see Figure 1 and Figure 9 In some implementations, the initial query statement is adjusted based on the abstract syntax tree, including: 0771: Parse the initial query statement based on the abstract syntax tree to obtain syntax nodes; 0773: Based on syntax nodes, classify errors in the initial query statement to obtain the error type; 0775: Based on the error type, adjust the initial query statement to obtain an adjusted query statement that meets the output conditions.

[0053] Specifically, a syntax node represents the various components of the initial query statement decomposed from the abstract syntax tree. These components include, but are not limited to, the positions of the SELECT clause, JOIN conditions, and aggregate functions, as well as the positions and relationships of each component in the abstract syntax tree.

[0054] The generation device 100 detects structural or semantic defects that do not conform to preset rules by traversing the syntax nodes in the abstract syntax tree, and classifies the error types, such as "missing join condition," "incorrect aggregate function position," or "ambiguous field name." This achieves error detection and classification. If the generation device 100 finds no errors after traversal, it can generate the final database query statement.

[0055] If a syntax node contains an error, the corresponding syntax node in the initial query statement is repaired using the Abstract Syntax Tree (AST) based on the error type, until the initial query statement meets the output conditions. This allows for targeted repair of the corresponding syntax nodes in the initial query statement based on different error types, improving repair efficiency.

[0056] Please see Figure 1 and Figure 10 In some implementations, the initial query statement is adjusted based on the error type to obtain an adjusted query statement that meets the output conditions, including: 07551: In the case of a missing join condition, complete the missing join condition based on the target foreign key; 07553: In cases where the error type is "incorrect aggregate function position," the aggregate function will be moved from the query condition area of ​​the initial query statement to the grouping statistics area of ​​the initial query statement; and 07555: In cases where the error type is ambiguous field name, add an alias prefix of the target table to the ambiguous field in the target table.

[0057] Specifically, a missing join condition is equivalent to a "missing JOIN condition". When the error type is missing join condition, the generation device 100 checks whether the target tables are missing the necessary JOIN ON conditions. The generation device 100 queries the pre-defined metadata graph for the target foreign keys that have been identified between the target tables and explicitly injects them as ON conditions into the initial query statement, thereby establishing the correct inter-table relationship path.

[0058] When the error type is an incorrect aggregate function placement, such as an aggregate function (e.g., SUM(), AVG()) being incorrectly placed in the WHERE clause and not conforming to the preset rules of the syntax constraint channel, the generation device 100 will move the aggregate function from the query condition area of ​​the initial query statement to the grouping statistics area of ​​the initial query statement. For example, the aggregate function and its associated filtering logic can be moved to the HAVING clause or corrected to the SELECT list to conform to SQL syntax specifications.

[0059] When the error type is field name ambiguity—that is, the same field name exists in multiple target tables, causing the database to be unable to determine which target table's field is being referenced—the generation device 100 adds an alias prefix to the ambiguous field in the target table. For example, it corrects the `name` field to the table alias `name`, thereby eliminating ambiguity and ensuring that the query statement can be correctly parsed and executed in the database.

[0060] Thus, by differentiating error types and performing targeted repairs, this generation method can improve the quality of generated database query statements and their execution success rate. Dual-channel hints and initial query statement generation, along with AST repair via abstract syntax tree (AST), can be packaged into a dynamic path generation layer for unified processing.

[0061] Please see Figure 1 and Figure 11 In some implementations, the generation method further includes: 091: Obtain performance metrics after the database query statement is executed to determine whether the user has manually corrected the database query statement; 093: Determine the reward value based on performance indicators and whether manual corrections are available; 095: Update the foreign key activity weight based on the reward value, preset learning rate, and the historical usage count of the target foreign key.

[0062] After each database query statement is generated, the generation device 100 updates the foreign key activity weights to improve the accuracy of subsequent database query statement generation.

[0063] Specifically, performance metrics after database query execution include, but are not limited to, the execution time (e.g., milliseconds) and memory usage, used to quantify the actual efficiency of the query. Simultaneously, the generation device 100 determines whether the user has manually corrected the database query statement through methods such as monitoring interface interaction. Reward value reward This characterizes the direction and strength of the impact of the query's quality and efficiency on the adjustment of the target foreign key weights. Reward value. reward The determination follows these rules: if manual correction is possible and execution efficiency is high (e.g., execution time less than 240ms), then the reward value is... reward =+1; If manual correction exists and execution efficiency is low (e.g., execution time > 240ms), then the reward value is increased. reward =-0.5; if no manual correction is available, then the reward value is... reward =+0.1. The update formula for foreign key activity weight is: Wnew = Wold + η·(reward- Wold)·λ in, Wnew The updated foreign key activity weights are represented. Wold Weights representing the current foreign key activity; preset learning rate η This represents the step size of the weight adjustment, controlling the magnitude of each learning iteration, for example... η = 0.7; Reward value reward It represents the direction and magnitude of the update; λ The reciprocal of the historical usage count of the target foreign key, i.e. λ=1The historical usage count of the target foreign key is used to normalize the reward value to avoid negative feedback from a single query (i.e., the reward value). reward Too low ） Excessive influence on frequently used foreign keys. Thus, the generation device 100 achieves dynamic adaptation of foreign key activity weights, enabling the preset metadata graph to continuously reflect real user query preferences and performance.

[0064] The following is an embodiment of this application. When the user inputs the following question: "Statistically count the number of failures of each piece of equipment on line A of the stamping workshop in the past month, and list the corresponding equipment models and production tasks"; The identified entities include: location entities (stamping workshop, A line), time entities (last month), object entities (equipment, number of failures, equipment model, production task volume), and indicator entities (number of failures (statistical), production task volume (numerical)).

[0065] The identified intent categories include operation type and domain intent. Operation types are: multi-table join queries + aggregation statistics + conditional filtering. Domain intents are: equipment operation and maintenance + production management. Equipment operation and maintenance and production management are different subdomains.

[0066] There are five candidate tables: workshop table, equipment table, production line table, production task table, and fault record table. The foreign key activity weights are as follows: workshop_id=0.7 (workshop table and equipment table), device_id=0.9 (equipment table and fault record table), line_id=0.8 (equipment table and production line table), and device_id=0.6 (production line table and production task table).

[0067] The subdomain weights include: Equipment Operation and Maintenance Domain Weight = 0.7; Production Management Domain Weight = 0.9.

[0068] Based on score S core The formula calculates the scores for the five tables as follows: Score for the Equipment Table S core =0.85; Score of the fault record sheet S core =0.82; Score of production task sheet S core =0.78; Workshop table score S core =0.65; Score of the production line table S core =0.61 Total number of candidate tables N for 3， Less than 50, therefore, retain the score.S core The top three candidate tables are used as the target tables, which include: equipment table, fault record table, and production task table.

[0069] Equipment operation and maintenance and production management are different subdomains, which is determined to be a cross-subject domain query. Therefore, device 100 is activated to open the subject domain gateway between the equipment operation and maintenance and production management domains, and the target foreign key is injected. JOIN device_table Fault_log_table ON device_table.device_id = fault_log_table.device_id JOIN device_table and ON production_task_table.device_id = production_task_table.device_id Then, the initial query statement is generated: SELECT device_model, COUNT(fault_id), SUM(production_volume) FROM Device table, Fault record table.

[0070] Based on the abstract syntax tree, the initial query statement is determined to be inconsistent with the output conditions. The adjustments based on the error type are as follows: if a missing JOIN condition is detected, the ON device_id association is automatically injected; if an ambiguous name is detected in the workshop_name field, it is rewritten as workshop_table.name; if an aggregate function is detected in WHERE, the aggregate function is moved to HAVING.

[0071] The corrected database query statement is then output. After outputting the database query statement, the foreign key activity weight is updated, specifically including: the user was detected to have manually corrected the query to "added a join condition for the production line table.line_id"; the response time decreased from 2100ms to 1300ms. This indicates that a manual correction was performed with high execution efficiency, thus increasing the reward value. reward = +1.0, calculate Wnew (Production line table.line_id) = 0.6 + 0.7×(1.0 - 0.6)×(1 / 15) ≈ 0.618.

[0072] Please see Figure 1 and Figure 12 This application provides a database query language generation apparatus 100, including a memory 10 and a processor 30. When the instructions stored in the memory 10 are executed by the processor 30, the steps of the method in any of the above embodiments are implemented.

[0073] For example, if the instructions stored in memory 10 are executed by processor 30, the following fetching method is implemented: 03: Based on the question information input by the user, obtain the entity set, intent type, and intent classification probability from the question information; 05: Based on entity sets, intent types, and intent classification probabilities, select target tables from the pre-defined candidate tables of the metadata graph, and obtain the foreign keys between target tables according to foreign key activity weights; and 07: Based on the target table, foreign keys, and preset constraints, obtain the database query statement corresponding to the problem information.

[0074] For example, when the instructions stored in memory 10 are executed by processor 30, the following retrieval method is implemented: 051: Obtain semantic relevance (Ssemantic) based on the semantic matching degree between the entity set and the candidate table's description information; 052: The intent relevance Sintent is obtained based on the intent classification probability and the preset domain weight coefficient. The domain weight coefficient represents the weight of the sub-domain corresponding to the candidate table relative to the intent type. 053: Based on the historical query time of the candidate tables and the cache hit rate of the candidate tables, obtain the performance correlation score (Sperf); and 055: Based on semantic relevance, performance relevance, and intent relevance, obtain candidate tables that meet preset requirements as target tables.

[0075] For example, when the instructions stored in memory 10 are executed by processor 30, the fetching methods in 0551, 0553, 0555, 0557, 0559, 05571, 05573, 05575, 071, 073, 075, 077, 0711, 0713, 0715, 0771, 0773, 0775, 07551, 07553, 07555, 091, 093 or 095 can also be implemented.

[0076] The database query language generation device 100 includes at least the beneficial effects of the database query language generation method, which will not be elaborated here.

[0077] In the description of this specification, the references to terms such as "one embodiment," "some embodiments," "illustrative embodiment," "example," "specific example," or "some examples," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of the present invention. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples.

[0078] Although embodiments of the invention have been shown and described, those skilled in the art will understand that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the claims and their equivalents.

Claims

1. A method for generating a database query language, characterized in that, include: Based on the question information input by the user, obtain the entity set, intent type, and intent classification probability from the question information; Based on the entity set, the intent type, and the intent classification probability, target tables are selected from the candidate table of the preset metadata graph, and foreign keys between the target tables are obtained according to the foreign key activity weight; and Based on the target table, the foreign key, and the preset constraints, obtain the database query statement corresponding to the problem information.

2. The generation method according to claim 1, characterized in that, The candidate tables have a preset domain, which includes multiple sub-domains. The process of selecting target tables from the candidate tables in the preset metadata graph and obtaining the foreign keys between the target tables based on foreign key activity weights includes: Semantic relevance is obtained based on the semantic matching degree between the entity set and the description information of the candidate table; The intent relevance is obtained based on the intent classification probability and the preset domain weight coefficient, wherein the domain weight coefficient represents the weight of the subdomain corresponding to the candidate table relative to the intent type; Based on the historical query time of the candidate tables and the cache hit rate of the candidate tables, a performance relevance is obtained; and Based on the semantic relevance, the performance relevance, and the intent relevance, the candidate table that meets the preset requirements is obtained as the target table.

3. The generation method according to claim 2, characterized in that, The step of obtaining the candidate table as the target table based on the semantic relevance, the performance relevance, and the intent relevance includes: Based on the candidate table, a score is obtained for each candidate table according to the semantic relevance, performance relevance, and intent relevance, and the candidate tables are ranked based on the scores; If the number of candidate tables is less than or equal to a preset parameter, the top three candidate tables in the ranking are retained as the target table; and If the number of candidate tables is greater than a preset parameter, the candidate tables ranked min(5,√N)th in the sorting order are retained as the target table, where N represents the number of candidate tables.

4. The generation method according to claim 3, characterized in that, The step of obtaining the candidate table as the target table based on the semantic relevance, the performance relevance, and the intent relevance further includes: Determine whether the entity set and / or intent type belong to a cross-topic domain query; and In the case of cross-topic domain queries, the target foreign key is selected from the candidate foreign keys in the target table based on the foreign key activity weight, where the foreign key activity weight is the ratio of the number of times the candidate foreign key is used in historical queries to the total number of historical queries.

5. The generation method according to claim 4, characterized in that, The step of determining whether the entity set and / or intent type belongs to a cross-topic domain query includes: Determine whether the entities in the entity set belong to different domains; Obtain the current user's permission identifier, and determine whether the permission identifier meets preset conditions; and If the entity spans different subdomains under the domain to which it belongs, and the permission identifier meets the preset conditions, it is determined to be a cross-topic domain query.

6. The generation method according to claim 1, characterized in that, The step of obtaining the database query statement corresponding to the problem information based on the target table, the foreign key, and preset constraints includes: Based on the constraints, obtain the initial query statement; Determine whether the initial query statement meets the output conditions; If the initial query statement meets the output conditions, the initial query statement is output as the database query statement; and If the initial query statement does not meet the output conditions, the initial query statement is adjusted based on the abstract syntax tree until it meets the output conditions, and the adjusted initial query statement is output as the database query statement.

7. The generation method according to claim 6, characterized in that, The step of obtaining the initial query statement based on the target table and the target foreign key includes: The constraints include a syntax constraint channel, and preset rules are obtained based on the syntax constraint channel. The preset rules are used to limit the syntax structure of the initial query statement. The constraints include a semantic guidance channel, based on which a query template corresponding to the intent type is obtained; and Based on the preset rules and the query template, as well as the target table and the target foreign key, the initial query statement is obtained.

8. The generation method according to claim 7, characterized in that, The adjustment of the initial query statement based on the abstract syntax tree includes: The initial query statement is parsed based on the abstract syntax tree to obtain syntax nodes; Based on the syntax nodes, errors in the initial query statement are classified to obtain the error types; Based on the error type, the initial query statement is adjusted to obtain an adjusted query statement that meets the output conditions.

9. The generation method according to claim 8, characterized in that, The step of adjusting the initial query statement based on the error type to obtain an adjusted query statement that meets the output conditions includes: In the case where the error type is missing join conditions, the missing join conditions are filled in based on the target foreign key. In the case where the error type is an aggregate function position error, the aggregate function is moved from the query condition area of ​​the initial query statement to the grouping statistics area of ​​the initial query statement; and In the case where the error type is ambiguous field name, add an alias prefix of the target table to the ambiguous field in the target table.

10. The generation method according to claim 1, characterized in that, The generation method further includes: Obtain the performance metrics after the database query statement is executed, and determine whether the user has manually corrected the database query statement; The reward value is determined based on the performance metrics and whether manual corrections are possible. The foreign key activity weight is updated based on the reward value, the preset learning rate, and the historical usage count of the target foreign key.

11. A database query language generation apparatus, comprising a memory and a processor, wherein the memory is used to store instructions, characterized in that, The instructions stored in the memory are executed by the processor to implement the generation method according to any one of claims 1-10.

Images