A metadata-driven natural language data exploration dialogue generation method

Abstract

The application discloses a metadata-driven natural language data exploration dialogue generation method, and relates to the technical field of data processing.The method comprises the following steps: analyzing user input, positioning a target field and a table to which the target field belongs, searching for an incremental JOIN path based on an ER relationship diagram if the table is not in a current query involved table set, identifying a one-to-many associated edge in the path as an amplification edge, determining an acceptance mode according to the number of amplification edges, obtaining amplification edge positioning information and determining a correction flag of each aggregation expression when the acceptance mode is determined as a pre-aggregation acceptance, constructing an inflation factor based on the amplification edge information, and performing weighted correction on the aggregation expression with the correction flag being true and being an accumulation sensitive type, assembling a SQL query plan, executing the SQL and verifying the result accuracy, and returning simultaneously while updating a query state object.The application automatically identifies and corrects data inflation problems in multiple rounds of data exploration, and improves the efficiency of interactive data exploration.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and more specifically, to a method for generating dialogues for natural language data exploration based on metadata-driven approaches. Background Technology

[0002] As enterprise data volumes continue to grow, the need for business personnel to interact with data analytics systems via natural language is becoming increasingly urgent. Natural language data exploration allows users to refine queries step-by-step in a conversational manner, such as calculating sales by region and then adding further details by product category. During this process, the system needs to dynamically interpret user intent and associate new fields, such as product category, with the current query. However, real-world data warehouses typically employ star or snowflake schemas, with various relationships between fact tables and dimension tables, especially one-to-many relationships. When a new dimension is introduced through a one-to-many relationship, each record in the fact table may be expanded into multiple rows on the dimension side. This can lead to row inflation in intermediate query results. For cumulative aggregate functions like SUM and COUNT, this inflation directly causes the contribution of the same fact record to be repeatedly accumulated, multiplying the final aggregation result and causing serious data errors. Existing natural language query systems often only focus on SQL generation and execution, lacking a deep understanding of the semantics of the data model, and are unable to identify and correct data inflation problems introduced by JOIN paths. Users have to manually check the query logic or rely on experience to adjust it, which is not only inefficient but also makes it difficult to guarantee the accuracy of the results. In addition, the inflation risk accumulated in multiple rounds of dialogue further increases the complexity of correction.

[0003] To address the above problems, this invention proposes a solution. Summary of the Invention

[0004] To overcome the aforementioned deficiencies of the prior art, embodiments of the present invention provide a metadata-driven natural language data exploration dialogue generation method to address the problems raised in the background art.

[0005] To achieve the above objectives, the present invention provides the following technical solution:

[0006] A metadata-driven natural language data exploration dialogue generation method includes the following steps: parsing the user's query change intent in the current round of natural language input, locating the target table to which the target field belongs, if the target table does not belong to the set of tables involved in the current query state object, obtaining a set of candidate paths based on ER relationship graph search, evaluating the cost by combining the path length and the penalty value of the cardinality mark of each associated edge, and selecting the path with the minimum total cost as the final incremental JOIN path;

[0007] Traverse the associated edges of the final incremental JOIN path to read the cardinality marker, identify one-to-many associated edges as amplification edges and count their number, and determine the acceptance method based on the number of amplification edges; if it is a pre-aggregation acceptance, read the left table name, the primary key field of the left table and the right table name of the amplification edge to obtain the amplification edge positioning information, and determine the correction flag of each aggregation expression based on the positional relationship of the table to which the aggregated field belongs relative to the amplification edge.

[0008] Based on the magnified edge positioning information, the right table is used as the data source and counted by grouping by the associated fields. The count results are associated with the fact records of the left table to obtain the inflation factor. The reciprocal of the inflation factor after replacing null values ​​is defined as the row contribution weight. For the aggregate expression with the correction flag being true and the inflation response type being cumulatively sensitive, the single row contribution value is extracted, multiplied by the row contribution weight, and then summed and aggregated to obtain the corrected aggregate expression, which is then assembled into an SQL query plan.

[0009] Translate the SQL query plan into an executable SQL statement, execute it, obtain the result set, verify the accuracy of the result set, return it, and update the query status object.

[0010] In a preferred embodiment, parsing the user's query change intent in the current round of natural language input includes: the query change intent includes, but is not limited to, adding grouping dimensions, replacing grouping dimensions, adding filtering conditions, adding metrics, and replacing metrics; using a rule template matching method, the user input is matched with a preset set of keyword templates that cover the above-mentioned change scenarios, and if a match is found, the target field business name is extracted from the template slot and the change type is determined.

[0011] In a preferred embodiment, when the user input does not match any template in the keyword template set, the user input is compared with the business aliases of all fields in the field registry using cosine similarity calculation to obtain the highest semantic similarity value; when the highest semantic similarity value exceeds the preset intent recognition confidence threshold, the corresponding field is determined as the target field business name; when the highest semantic similarity value does not exceed the intent recognition confidence threshold, a clarification request is returned to terminate the process.

[0012] In a preferred embodiment, a candidate path set is obtained based on the ER graph search. Specifically, a breadth-first search with depth restrictions is adopted, with each table in the already involved table set as the starting point and the target table as the ending point. The search depth is limited by a preset maximum number of path edges. During the search, each candidate edge is checked to see if it forms a loop. If so, it is skipped. A candidate path set is collected, and the number of collected paths is controlled by a preset upper limit for candidate paths.

[0013] In a preferred embodiment, each path in the candidate path set determines the final incremental JOIN path through the following cost evaluation steps: assigning an edge cardinality penalty value to each associated edge in the path according to its cardinality label, wherein the penalty value for one-to-one and many-to-one pairs is zero, and the penalty value for one-to-many pairs is a preset one-to-many pair penalty value; the total path cost is equal to the normalized edge number obtained by multiplying the preset path length weight coefficient by the number of edges in the path and dividing by the maximum number of edges in the path, plus the sum of the penalty values ​​of all edges in the path multiplied by the preset cardinality penalty weight coefficient; and taking the path with the minimum total path cost as the final incremental JOIN path.

[0014] In a preferred embodiment, the acceptance method based on the number of amplified edges includes: when the number of amplified edges is equal to zero, it is determined to be directly accepted, and the incremental JOIN condition and target field are directly appended to the query status object to enter the dialog generation step; when the number of amplified edges is greater than zero and does not exceed the preset upper limit of the number of amplified edges, it is determined to be pre-aggregated and accepted; when the number of amplified edges exceeds the upper limit of the number of amplified edges, it is determined to be rejected and an alternative is suggested.

[0015] In a preferred embodiment, when a rejection is determined and an alternative is suggested, the target dimension node is used as the starting point to backtrack upwards along the hierarchical tree in the dimension-level registry. The backtracking depth is controlled by a preset dimension backtracking search depth. The table corresponding to the upper-level dimension is checked level by level to see if it can be reached from the set of tables involved through a path with an amplified edge count not exceeding the upper limit of the amplified edge count. If found, the corresponding upper-level dimension is returned as an alternative suggestion; otherwise, a rejection prompt is returned.

[0016] In a preferred embodiment, determining the correction flag for each aggregation expression includes: splitting the final incremental JOIN path into a left half and a right half, with the left table of the amplified edge as the boundary; the left half includes the left table and other associated tables in the already involved table set; the right half includes tables from the right table to the target table; the correction flag is marked as true when the table to which the aggregated field belongs belongs to the left half, and marked as false when it belongs to the right half.

[0017] In a preferred embodiment, before rewriting the aggregation expression whose correction flag is true, the pre-defined inflation response type flag in the metadata is read. The inflation response type includes accumulation-sensitive and inflation-immune types. The condition for accumulation-sensitive is that after copying any record in the aggregation input set into N positive integers and executing the aggregation function, the result is equal to N times the result before copying. The condition for inflation-immune is that the aggregation result remains unchanged after the copying operation. For aggregation expressions with inflation response type of inflation-immune, the correction flag is corrected to false to keep the original expression unchanged.

[0018] In a preferred embodiment, after performing accuracy verification on the result set and returning the result, the query status object is updated simultaneously. This includes: constructing and executing the original reference query without introducing incremental JOIN conditions to obtain the reference total value; summing the results of the executable SQL statement with the corrected aggregated columns to obtain the verification total value; calculating the deviation ratio as the absolute value of the difference between the verification total value and the reference total value divided by the absolute value of the reference total value; verification passes when the deviation ratio does not exceed a preset result deviation tolerance threshold; otherwise, verification fails and an accuracy warning is issued. Updating the query status object also includes recording correction metadata, which includes the amplification edge identifier, the expansion factor, and the subquery results. The modified aggregate expression is constructed. If the user removes the changes in the current round in a subsequent dialogue round, the modification is rolled back based on the modified meta-information. The query state object stores both the original aggregate expression and the modified aggregate expression. The original expression is used for the rollback operation, and the modified expression is used for the execution of this query. If the user continues to add new changes, the number of amplified edges recorded in the query state object is summed with the number of amplified edges in the new path, and then compared with the upper limit of the number of amplified edges. If the sum exceeds the upper limit of the number of amplified edges, it is judged as a rejection, and an alternative dimension is searched upwards in the dimension level registry. If it does not exceed the upper limit, the pre-aggregation acceptance process continues to be executed according to this method.

[0019] The technical effects and advantages of this invention, a metadata-driven natural language data exploration dialogue generation method, are as follows: Firstly, by parsing user input and utilizing metadata to accurately locate target fields and their corresponding tables, combined with intent recognition technology, the invention automatically determines whether a new table needs to be introduced, reducing manual intervention and improving the naturalness and convenience of the interaction. Secondly, when cross-table joins are required, a path search with cardinality penalty is performed based on the ER graph. Taking into account path length and the inflation risk brought by one-to-many edges, the optimal incremental JOIN path is selected, assessing the possibility of data inflation from the source while ensuring query performance. Thirdly, by identifying and counting one-to-many edges in the path, the acceptance method is intelligently determined: for paths without inflation, acceptance is performed directly; for paths with a finite number of amplifying edges, a pre-aggregation acceptance strategy is adopted, which involves constructing an inflation factor and... By using row contribution weights to perform weighted correction on accumulation-sensitive aggregation functions, this invention accurately eliminates aggregation bias caused by duplicate records. For cases with multiple amplified edges exceeding the correction capacity, it proactively rejects them and provides semantically similar alternative dimensions, avoiding the generation of erroneous queries. Before returning results, this invention performs accuracy verification, comparing the deviation ratio of the total aggregation value before and after correction to promptly identify and warn of potential systematic errors, further ensuring data reliability. The explanatory text generated during the correction process helps users understand the logic of the data adjustment, enhancing interpretability and trustworthiness. The updated query status object records correction metadata, supporting rollback and cumulative risk control in subsequent dialogue rounds, effectively preventing the accumulation of inflationary risks in multiple exploration rounds. This invention improves the accuracy, robustness, and user experience of natural language data exploration. Attached Figure Description

[0020] Figure 1 This is a flowchart illustrating a method for generating dialogues based on metadata-driven natural language data exploration according to the present invention.

[0021] Figure 2 This is a schematic diagram of the ER relationship graph and the final incremental JOIN path search in the metadata-driven natural language data exploration dialogue generation method of the present invention. Detailed Implementation

[0022] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0023] Example: This invention discloses a metadata-driven natural language data exploration dialogue generation method, including the following steps:

[0024] Step 1: Analyze the user's query change intent in this round of natural language input, locate the target table to which the target field belongs, and when the target table does not belong to the set of tables involved in the current query state object, obtain a set of candidate paths based on the ER relationship graph, and evaluate the cost based on the path length and the penalty value assigned by the cardinality label of each associated edge in the path, and select the path with the minimum total cost as the final incremental JOIN path.

[0025] This step receives the user's natural language input for the current round and, combined with the query state object QueryState maintained by the system from the previous round, completes intent parsing, target field location, and incremental JOIN path search. The current query state object QueryState contains the following fields: the set of tables involved. JOIN condition list Field list List of aggregate expressions Grouping field list and filter list The system simultaneously maintains four global metadata structures: FieldRegistry, TablePrimaryKeyRegistry, DimensionHierarchyRegistry, and AggregateFunctionAttributeRegistry, as well as an ER graph (ERGraph); among which:

[0026] Each record in the FieldRegistry contains a business alias and the name of the table to which it belongs. Physical field name and field type The field type is a dimension field;

[0027] Table primary key registry entry TablePKR: Each record contains the table name. Primary key field name Primary key type Such as integer and string types;

[0028] Dimensional Hierarchical Registry (DH): A tree structure where each record contains dimension nodes. Parent node Corresponding table name Dimension field names Hierarchical levels, such as level 1 being the root node and level 2 being child nodes;

[0029] Aggregate function attributes registry AFPR: Each record contains the function name. Expansion response type Does it support compound computing? That is, yes or no;

[0030] ERGraph is defined as a directed graph, where the set of nodes V represents all data tables, and the set of edges... Each associated edge It is a quintuple, where i is the identifier of the associated edge, including the name of the left table. Right table name Left table related fields right table related fields And the associated cardinality marker, Card, with the cardinality marker value being... ;

[0031] Furthermore, the system identifies the user's query change intent in the current input round. In multi-round data exploration dialogues, each round of user input can be understood as a semantic change operation on the query state of the previous round. This invention uniformly defines the semantic changes that the user may trigger as query change intents, including but not limited to the following: adding grouping dimensions, replacing grouping dimensions, adding filter conditions, adding metrics, and replacing metrics. The common feature of the above types of changes is that the target field pointed to by the user may not belong to the set of tables already involved in the current query. Therefore, it is necessary to search for incremental JOIN paths on the ER relationship diagram to associate new tables; thus, the core purpose of this step is not to distinguish the specific change operation type, but to extract the target field pointed to by the user and determine whether the field triggers an incremental JOIN requirement.

[0032] Intent recognition adopts a method that combines rule template matching and semantic similarity calculation. Specifically: the system presets a keyword template set covering the above various change scenarios, matches the user input with the template set, and if a match is found, extracts the service name of the target field from the template slot and determines the change type ; if no template is matched, cosine similarity calculation is performed between the user input and the service aliases of all fields in the field registry FieldRegistry to obtain semantic similarity; the specific implementation of cosine similarity calculation is as follows: both the user input text and the field service aliases are accurately segmented using the Chinese word segmentation tool Jieba, and stop words such as "de" (of), "an" (by / according to), "kankan" (look / view) are filtered; based on the pre-trained Word2Vec word vector model with a dimension of 300, the word segmentation result is converted into word vectors, and the average of the word vectors is used to obtain the overall text vector; the similarity value between the user input vector and the field alias vector is calculated according to the cosine similarity formula, and 6 decimal places are retained;

[0033] the highest semantic similarity value is taken ; if the highest semantic similarity value exceeds the intent recognition confidence threshold the corresponding field is determined as the service name of the target field , otherwise a clarification request is returned and the process is terminated; wherein the design logic of the intent recognition confidence threshold is as follows: the threshold needs to strike a balance between false recognition rate and missed recognition rate. An overly low threshold will cause irrelevant fields to be incorrectly matched, while an overly high threshold will cause legitimate requests to be rejected. Through statistical analysis on the similarity distribution of correctly matched and incorrectly matched samples in historical conversation logs, a value near the intersection of the two types of distributions is taken as the initial setting, and the initial value of the intent recognition confidence threshold is set to 0.75;

[0034] It should be noted that the keyword template set includes but is not limited to: adding grouping dimensions, for example, "view by...", "group by..."; adding filtering conditions, for example, "filter...", "only view..."; adding measurement indicators, for example, "count / statistics...", "add..."; replacing dimensions, for example, "replace...with...", after matching, the content in the slot is extracted as the target field;

[0035] after the service name of the target field is determined a matching record is retrieved in the field registry FieldRegistry to obtain the target table name and the physical field name ; if multiple records are retrieved, the first record is taken in descending order of field usage frequency. The field usage frequency is counted according to the number of system query calls in the last 30 days, and the statistical result is automatically updated every day at midnight;

[0036] further, the target table name is determined Does it belong to the set of tables already involved? If the target table name This already belongs to the set of tables involved. This indicates that the table to which the target field belongs has already been joined in the current query, and there is no need for incremental JOIN. The query can proceed directly based on the change type. After modifying the QueryState object according to the corresponding change type, proceed to the dialog generation step; if the target table name Not part of the already involved table set Perform a depth-limited breadth-first search on the ER graph (ERGraph) to cover the set of tables already involved. Each table in the table is a starting point and a target table name. Search for associated paths to the endpoint; the search depth is determined by the maximum number of path edges. Constraints: Maximum number of path edges The initial value is set to 6, which is determined based on empirical statistics of the longest reasonable association link between tables in the ER relationship diagram; the maximum number of candidate paths collected is determined by the upper limit of candidate paths. Control, initial value set to 5; during the search, check each candidate edge for forming a cycle, if so, skip it; if the candidate path set PC is empty after the search ends, return an unreachable message;

[0037] Cost evaluation is performed on each path in the candidate path set PC. First, the cost of each associated edge in the path is evaluated. Penalty values ​​are assigned based on their cardinality: one-to-one penalty value The initial value is 0.0, and the penalty value for multiple sides is... The initial value is 0.0, and the one-to-many penalty value is... The initial value is 1.0. The design logic for the three penalty values ​​is as follows: one-to-one and many-to-one associations do not cause row expansion, so there is no penalty; one-to-many associations are the only source of row expansion, so a unit penalty is assigned; therefore, the edge cardinality penalty value is... The calculation is expressed by the formula: ;

[0038] After obtaining the penalty value for each edge, the total path cost is defined as the weighted sum of the path length term and the penalty term; the total path cost is expressed by the formula: Where α is the preset path length weight coefficient with an initial value of 0.3, β is the cardinality penalty weight coefficient with an initial value of 0.7, and the design logic of the two weights is: the impact of row number expansion risk on query accuracy is greater than the impact of path length on query performance, so the penalty term is given a higher weight, and k is the number of edges contained in the path;

[0039] Sort all paths in the candidate path set PC in ascending order of total path cost, and select the path with the lowest cost as the final incremental JOIN path. If the Cost is the same, the one with the fewest edges is selected; if the number of edges is also the same, the one with the earliest edge registration order is selected.

[0040] In an exemplary ER relationship diagram, such as Figure 2 As shown, nodes represent different data tables, such as data table A, data table B, data table C, and data table D; the nodes are connected by directed edges with associated cardinality markers (Cards). Some of the data tables constitute the set of tables already involved in the current query state object, while the target table is the data table to which the newly added field belongs in this query belongs. The system searches for candidate paths in the ER graph (ERGraph) starting from the data tables in the set of involved tables and ending at the target table, and determines the final incremental JOIN path through cost evaluation.

[0041] Regardless of the change type, as long as the final incremental JOIN path... The existence of a one-to-many relationship may lead to row number inflation, requiring the risk assessment process in step two.

[0042] Step 2: Read the association cardinality marker for each associated edge in the final incremental JOIN path one by one. Identify the associated edges with a one-to-many association cardinality marker as amplified edges and count the number of amplified edges. Determine the acceptance method based on the number of amplified edges. When it is determined to be a pre-aggregation acceptance, read the left table name, the primary key field of the left table, and the right table name of the amplified edge to obtain the amplified edge positioning information. Determine the correction flag of each aggregation expression based on the positional relationship of the table to which the aggregated field belongs in the aggregation expression is relative to the amplified edge.

[0043] This step receives the final incremental JOIN path output from step one. For each edge in the path, check the cardinality marker one by one, count the number of one-to-many edges, determine the acceptance method of this query change, and when it is determined to be pre-aggregation acceptance, accurately locate the amplified edge that causes the number of rows to expand and its left table primary key field. It is also necessary to access the table primary key registry TablePKR to obtain the primary key field name of each table, and the dimension hierarchy registry DH to obtain the hierarchical relationship between dimensions.

[0044] According to the final incremental JOIN path The edges are arranged in a sequential manner, starting from the first edge. The cardinality of each edge is read one by one, and all edges with cardinality from 1 to N are collected into the AmpEdgeList. The number of amplified edges is... Defined as the total number of edges in the path whose cardinality is 1-N, expressed by the formula: ;in This is an indicator function that takes a value of 1 if the condition is true and 0 otherwise. In SQL, it is implemented using the CASEWHEN statement, i.e., CASEWHEN condition THEN 1 ELSE 0 END.

[0045] Simultaneously calculate the cumulative amplification factor. The cumulative amplification factor is defined as the product of the average number of associated rows (AvgF) of all amplified edges in the amplified edge list (AmpEdgeList). It is used when the amplified edge list (AmpEdgeList) is empty. The value is 1.0; the average number of associated rows (AvgF) is maintained by the metadata through periodic statistical sampling. The sampling involves randomly selecting 1000 records from the left table of each associated edge daily, calculating the average number of associated rows in the right table, and retaining 6 decimal places. The cumulative amplification factor is expressed by the formula:

[0046] Among them, the magnified edges in the magnified edge list AmpEdgeList are... This indicates that j is the sequential number in the AmpEdgeList of the magnified edges, with a value ranging from 1 to... ; It is the final incremental JOIN path The result of renumbering all edges with cardinality labels 1-N according to their order of appearance in the path is as follows: Let j be the j-th edge in the path edge sequence that satisfies the condition that Card equals 1-N;

[0047] The acceptance method is determined based on the number of enlarged edges. With the upper limit of the number of enlarged sides Comparison; enlarge the upper limit of the number of sides The design logic is as follows: Each additional amplification edge significantly increases the nesting level and computational complexity of the expansion factor subqueries. When there is more than one amplification edge, the controllability of correction accuracy and query performance decreases drastically. Therefore, the initial upper limit for the number of amplification edges is set to a single amplification edge. The initial value is set to 1;

[0048] Then the decision function Expressed as a formula: ;

[0049] When the number of sides is increased When the value is 0, it is determined to be accepted directly. The cardinality of all edges in the path is 1-1 or N-1. JOIN does not cause row number inflation and does not need to be corrected. The incremental JOIN condition and target field are directly appended to the query state object QueryState and the dialog generation step is entered.

[0050] When the number of sides is increased Greater than 0 and not exceeding the maximum number of enlarged sides When the condition is determined to be pre-aggregated and accepted, the amplified edges in the AmpEdgeList are considered. Perform precise positioning: Read the magnified edge left table name In a one-to-many relationship, the left table name is retrieved from the TablePKR registry key for the "one" side. The primary key field name is denoted as Read the right table name That is, the side with more information; the three pieces of information are packaged into the magnified edge positioning information Ampl;

[0051] It should be noted that the above-mentioned upper limit for the number of enlarged edges... The initial value can be dynamically adjusted based on the following: if the business scenario involves coarse-grained statistics, such as when the grouping dimension is at the annual, quarterly, or provincial level, increase the upper limit of the number of edges. The initial value is adjusted to 2, and only two consecutive magnified edges are allowed to be related between the fact table and the dimension table; consecutive magnified edges between dimension tables are prohibited. If the business scenario involves fine-grained statistics, such as when the grouping dimension is at the order number, user ID, or product SKU level, the maximum number of magnified edges is maintained. The initial value is 1; if the business scenario involves medium-granularity statistics such as monthly, city, or product category levels, the value will be dynamically adjusted based on the amount of data in the fact table: when the data volume is less than 1 million rows, the upper limit of the number of edges will be increased. The initial value is 2, and the upper limit of the number of edges is increased when the data volume is ≥1 million rows. Initial value equals 1; maximum number of enlarged edges in all scenarios. The maximum number is no more than 2;

[0052] Then the list of aggregate expressions In each aggregation expression, the source table attribution of the aggregated field is determined: the table to which the aggregated field belongs is extracted. To enlarge the left side of the table To define the boundary, the path is split into the left half, including the left table. and the set of tables already involved Other related tables and the right half containing the right table To target table The table;

[0053] If the table to which the aggregate field belongs If it belongs to the left half, the CFlag flag of the expression is marked as True; otherwise, it is marked as False.

[0054] When the number of sides is increased Exceeding the maximum number of enlarged edges If the condition is rejected and an alternative is suggested, the search depth is determined by backtracking upwards along the hierarchy tree from the target dimension node in the dimensional hierarchy registry (DH), with the search depth decreasing from the preset dimension. Control; preset dimension backtracking search depth The design logic is as follows: there is usually a direct many-to-one relationship between adjacent levels in the dimension hierarchy tree. Backtracking two levels is sufficient to cover most feasible alternative dimensions. If the backtracking is too deep, the business semantics of the alternative dimensions will deviate too much from the user's original request. Therefore, the preset dimension backtracking search depth is used. The initial value is set to 2;

[0055] Check each level to see if the table corresponding to the higher-level dimension can be expanded by a number of edges that does not exceed the upper limit. If the path is found, an alternative suggestion is returned; otherwise, a rejection message is returned.

[0056] Step 3: Based on the magnified edge positioning information, group and count the data by the related fields using the right table as the data source. Associate the group count results of the right table with the fact records of the left table through the related fields to obtain the inflation factor corresponding to each fact record in the left table. Define the reciprocal of the inflation factor after replacing null values ​​as the row contribution weight. For the aggregate expression with the correction flag being true and the inflation response type being determined to be cumulatively sensitive, extract its single row contribution value, multiply it by the row contribution weight, and then perform summation and aggregation to obtain the corrected aggregate expression, which is then assembled into an SQL query plan.

[0057] This step receives the amplification edge positioning information Ampl and the correction flags of each aggregation expression from step two, completes two tasks: constructing the accurate expansion factor and rewriting the aggregation expression differentially, and outputs a complete SQL query plan SqlPlan, where the expansion factor is: for each record in the left table of the amplification edge, count the number of rows that the record is associated with in the right table through the amplification edge;

[0058] The right table in the zoomed-in edge positioning information Ampl As the data source, zoom in on the related fields on the right side of the table. As the grouping key, for the right table Perform group counting; the count value of each group is the inflation factor. The counting results are converted to precision by the inflation factor, with precision retaining digits. Controlled floating-point numbers, expansion factor, precision reserved bits. The design logic is as follows: subsequent division operations require sufficient decimal precision to avoid truncation errors. However, excessive precision increases storage and computational overhead. Retaining six decimal places can control the truncation error of a single division to within one millionth. The number of decimal places retained for the expansion factor is also considered. If the initial value is set to 6, the inflation factor can be expressed by the formula: Where r is the right table The records in the table, where g is the grouping key; this structure is represented as a derived table in the final SQL. Embedded in the form of LEFTJOIN with the primary key of the main table. The reason for using LEFT JOIN is: if a certain fact record is in the right table... If there are no related rows, the expansion factor field will be empty. The value will be replaced with 1.0 using the cross-database universal null replacement function IFNULL(column name, 1.0) to avoid division by zero error and preserve the original aggregation contribution of the record. For PostgreSQL database, it is adapted to COALESCE(column name, 1.0) and for Oracle database, it is adapted to NVL(column name, 1.0).

[0059] After obtaining the expansion factor, define the row contribution weight w, which is the proportional coefficient that each row of data should contribute to the aggregation result; when the same original record is copied as the expansion factor due to JOIN... When arranging rows, each row should only contribute the inflation factor of the original value. One-third, making the expansion factor The total contribution of each row is restored to the correct value of the original single row; the row contribution weight is defined as the reciprocal of the inflation factor after null value replacement, and the null value inflation factor is replaced with 1.0 using the IFNULL / COALESCE / NVL function, expressed as follows: ;

[0060] The correction of aggregation expressions requires first determining the response characteristics of each aggregation expression to row number inflation. That is, aggregation functions are classified into two inflation response types based on their mathematical response characteristics: The first type is accumulation-sensitive, determined by the following condition: if copying any record in the aggregation input set to N positive integers and then executing the aggregation function results in a result N times greater than the original result, then the aggregation function is considered accumulation-sensitive. The essential characteristic of this type is that the aggregation result is equal to the row-by-row accumulation of the contribution value; row number inflation directly leads to the repeated accumulation of the same contribution value, thus proportionally amplifying the result. The second type is inflation-immune, determined by the following condition: if the aggregation result remains unchanged after the above copying operation, then the aggregation function is considered inflation-immune. The essential characteristic of this type is that the aggregation operation itself includes a mechanism for deduplication or taking extreme values; repeated records do not affect the final result.

[0061] In the metadata, each registered aggregate function has a pre-defined bloat response type flag ASType. This flag represents the inherent mathematical property of the aggregate function and is set during system initialization. It is retrieved directly from a table at runtime without dynamic calculation. Specifically, the system maintains a registry of aggregate function attributes. For functions like SUM, COUNT, and AVG in standard SQL, they are pre-defined as accumulation-sensitive. For functions containing deduplication or extremum logic, such as COUNT(DISTINCT), MAX, and MIN, they are pre-defined as bloat-immune. For user-defined composite aggregate functions, the system generates their Abstract Syntax Tree (AST) using the ANTLR4 parser. The determination rule is: traverse the AST to obtain the top-level operator; if the top-level operator is summation... If the function is SUM, COUNT, or AVG and does not contain the deduplication keyword DISTINCT, it is classified as an accumulation-sensitive function. If the top-level operator is COUNT(DISTINCT)), MAX, MIN, or VAR, it is classified as an expansion-immune function. If the composite aggregation function is a combination of multiple operators such as (SUM(a)+SUM(b)) / COUNT(c), the expansion response type is determined independently for each sub-operator, and each sub-item inherits the attributes of the corresponding operator and performs weighted corrections respectively. If the composite aggregation function contains a conditional expression such as CASEWHEN, the result type of the conditional expression is parsed first, and then the aggregation operator is determined for the result.

[0062] For aggregate expressions with the correction flag CFlag set to True, read their expansion response type flag ASType; if the expansion response type flag ASType is expansion-immune, then although the expression is on the expansion side, it is not affected by row expansion, and the correction flag CFlag is set to False to keep the original expression unchanged.

[0063] If the inflation response type is marked as cumulatively sensitive (ASType), a uniform weighted correction is performed. The correction method is as follows: extract the single-row contribution value *v* of each row in the original aggregation expression to the aggregation result, multiply *v* by the row contribution weight *w*, and then perform summation aggregation. Here, the single-row contribution value *v* represents the independent contribution of that row of data to the final aggregation result under no inflation conditions. The corrected aggregation expression... Expressed as a formula: Where v is the single-row contribution of the original aggregation expression to the current row; for example, when the original aggregation is to sum the values ​​of field x, v is equal to the current row value of x; when the original aggregation is to count rows, v is equal to a constant 1; for a composite aggregation consisting of the quotients of two summation-sensitive aggregations, for example, when the original aggregation is to sum the values ​​of field x, v is equal to the current row value of x; when the original aggregation is to count rows, the single-row contribution v of COUNT(*) and COUNT (non-nullable field) is a constant 1, and the single-row contribution v of COUNT (nullable field) is... : The value is 1 when the field is not empty and 0 when the field is empty; For composite aggregations consisting of the quotients of two summation-sensitive aggregations, such as mean, percentage, and ratio aggregations, the essence is the summation result divided by the counting result. The system independently rewrites the numerator and denominator according to the above rules before taking the quotient; For SUM(DISTINCTx) type aggregations, the single-row contribution value v is the current row value of x. After correction, duplicates are removed before weighted summation is performed; The correctness of the above unified correction formula is proven as follows: For any original fact record, it is copied as an expansion factor due to JOIN. Each line carries the same single-line contribution value v. After rewriting, the corrected contribution of each line is v divided by 1 / 2. Inflation factor The sum of the modified contributions of each row equals v divided by the inflation factor. Multiply by the expansion factor Equal to v, restoring the record to its correct contribution without inflation;

[0064] Traversing the list of aggregate expressions Each aggregate expression Rewrite execution: Read the correction flag CFlag. If it is False, leave it as is. If it is True, read the inflation response type flag ASType. If it is inflation-immune, correct the correction flag CFlag to False and leave it as is. If it is accumulation-sensitive, extract the single-row contribution value v and generate the rewritten expression SUM, which is the single-row contribution value v divided by the inflation factor after null replacement. After rewriting, assemble the complete SQL query plan (SqlPlan): the FROM clause retains the JOIN condition list starting from the original main table. There is already a JOIN; incremental JOIN follows the final incremental JOIN path. Edges are added sequentially; expansion factor query. Using LEFTJOIN with the main table Related; the SELECT clause uses a set of rewritten expressions. ;GROUPBY in the grouping field list Based on the type of change Decide whether to add the target dimension field WHERE based on change type Decide whether to add additional filtering conditions;

[0065] For example, in the scenario described above, middle There are 3 details for 1001. There are 2 entries for 1002; the expansion factor query result is 1001. Equals 3.000000, 1002 corresponds to 2.000000; row contribution weights w are 1 divided by 3 and 1 divided by 2 respectively; the original aggregation is a pair The summation is performed, with the expansion response type flag ASType set to cumulative sensitivity, and the single-row contribution value v equal to the current row value of amount; the rewritten expression is: After being divided by null replacement After JOIN, there are 5 rows: Category Electronics (2 rows) each contributes 900, multiplied by 1 and divided by 3, which equals 300, resulting in a total of 600; Category Food (1 row) contributes 300; Category Clothing (2 rows) each contributes 600, multiplied by 1 and divided by 2, which equals 300, resulting in a total of 600. The sum of the three categories is 1500, which equals the original total of 1500, thus maintaining numerical conservation. If no correction is made, the sum would be 3900, which is 2.6 times the correct value. This step outputs the query plan (SqlPlan) and the correction summary (CSummary) as input for step four.

[0066] Step 4: Translate the SQL query plan into an executable SQL statement, execute it to obtain the result set, verify the accuracy of the result set execution results, return and update the query status object;

[0067] This step receives the SqlPlan and the modified summary CSummary output from step three, and translates the SqlPlan into executable SQL statements. Submit the database to retrieve the result set, execute the result accuracy verification program, generate a correction process description text, and finally return the result set, description text, and verification status to the front end. Specifically:

[0068] Concatenate the components of the query plan SqlPlan according to standard SQL syntax. The concatenation order is SELECT, FROM, JOIN (row by row in list order), WHERE, GROUPBY;

[0069] Before returning the results, an accuracy verification procedure is performed. The verification principle is as follows: the query change only alters the grouping granularity or filtering range. The correction in step three ensures that the aggregation contribution of each fact record remains unchanged. Therefore, the sum of the aggregated values ​​of each group after correction should be conserved with the total aggregated value of the original query without the new related table. The verification consists of three steps, specifically including: the first step, constructing the original reference query. To obtain the reference total value, perform global aggregation on the aggregated fields that need correction without introducing incremental JOIN. To control computational overhead, if the number of records in the fact table exceeds the upper limit of the number of rows to be sampled for validation... Then, a sampling condition based on primary key hash modulo is added. Specifically, the primary key hash modulo uses the MD5 hash algorithm. After converting the primary key field into a hexadecimal hash value and rounding it, the modulo is taken with the sampling cardinality. The sampling cardinality is equal to the total number of records in the fact table divided by the upper limit of the number of rows to be sampled for verification. Rounding; the hash modulo seed value is a fixed random number of 1024, and the original reference query and the verification query use the same seed value to ensure that the sampling range is completely consistent; the SQL expression for the sampling condition is: MOD(CAST(CONV(SUBSTRING(MD5(pk_column),1,8),16,10)ASUNSIGNED),sample_base)=0; where pk_column is the primary key field representation under the SQL operation, and sample_base is the sampling base; if the primary key is a composite primary key, all primary key fields are concatenated before performing the above hash modulo operation; the upper limit of the number of rows to be verified. The initial value is set to 1000; verify the upper limit of the number of sampled rows. The design logic is as follows: the purpose of the verification is to detect systematic biases caused by duplicate or null primary keys, rather than a full and accurate comparison. Sampling thousands of records is statistically sufficient to expose systematic errors. At the same time, the execution time of the verification query is controlled within an acceptable proportion of the main query, and the upper limit of the number of rows sampled for verification is set. The initial value is set to 1000; the second step is to construct a validation query. Executable SQL statements The result, used as a subquery, is summed again on the corrected aggregated column to obtain the verification total. If sampling conditions were used in the first step, the same conditions are applied in the second step; when the type is changed... When adding filter conditions, validate the original reference query. The same filtering conditions need to be applied simultaneously to ensure that the calculation ranges of the two are consistent;

[0070] The third step is to calculate and determine the deviation ratio δ; the deviation ratio δ is the absolute value of the difference between the corrected aggregate total value and the original reference aggregate total value divided by the absolute value of the original reference aggregate total value; when the reference total value... When the value equals 0, the decision condition degenerates into the total verification value. Is it also equal to 0? If yes, then the deviation ratio δ equals 0; if not, then the deviation ratio δ is positive infinity, and the verification is marked as failed. The deviation ratio is expressed by the formula: ;

[0071] The judgment rule is: if the deviation ratio δ does not exceed the result deviation tolerance threshold. If the verification passes, then the verification fails and an accuracy warning is issued; deviation tolerance threshold. The design logic is as follows: the threshold needs to cover the inherent errors caused by floating-point division truncation and sampling statistical fluctuations, while not being so large as to mask the actual correction failures. For analysis of single-shot truncation error on the order of one part per million and statistical fluctuation on the order of one thousand for thousand-level sampling, one thousand-level is taken as the upper tolerance bound and the deviation tolerance threshold. The initial value is set to 0.001; for example, refer to the total value. Equals 1500, verify the total value. Equal to 1500, δ equal to 0 and not exceeding 0.001, verification passed; can be dynamically adjusted according to specific business scenarios;

[0072] After successful verification, a correction process description text (EText) will be generated. The generation method is template filling, using the system's default template: Your operation introduced... Related fields, this field is accessed through Associated with the current query; because The same stroke of This may result in duplicate entries; to ensure data accuracy, the system has automatically calculated a row contribution weight for each row of data. Weighted adjustments were made; among them, placeholders Retrieve the business alias of the target field from the FieldRegistry. Connect the business names of the various tables in the path with arrows. Describe the relationship between the magnified edges. To enlarge the business entity name in the left table, To correct the alias for the aggregated metrics business; the generated text in this example is: Your operation introduced a product category-related field, which is linked to the current query through the order fact table → order details table → product table → product category table; since one order can be associated with multiple order detail records, the sales amount of the same order will be counted repeatedly; to ensure data accuracy, the system has automatically calculated the row contribution weight for each row of data and made a weighted correction to the sales amount;

[0073] The system last updated QueryState to :Will The new table involved is appended to the set of tables already involved. The incremental JOIN condition is appended to the JOIN condition list. According to the type of change Append the target field to the grouping field list. Or a list of filter conditions or list of aggregate expressions In the corresponding position, replace the original aggregate expression with the modified expression;

[0074] Updated query status object The correction metadata, CorrectionMeta, contains the amplified edge identifier, the expansion factor subquery structure, and the corrected expression. This metadata plays two roles in subsequent rounds: if a user removes a change in this round, the correction is rolled back accordingly; if a user adds new changes, step two sums the already recorded number of amplified edges with the number of amplified edges in the new path, and then modifies this sum with the upper limit of the number of amplified edges. The comparison is performed, and if the value exceeds the limit, the query is rejected to prevent cumulative amplification from exceeding the correction capacity. The final output includes a result set, a correction process description text (EText), a verification status, and an updated query status object. .

[0075] The above formulas are all dimensionless calculations. The formulas are derived from software simulations based on a large amount of collected data to obtain the most recent real-world results. The preset parameters in the formulas are set by those skilled in the art according to the actual situation.

[0076] The above embodiments can be implemented, in whole or in part, by software, hardware, firmware, or any other combination thereof. When implemented using software, the above embodiments can be implemented, in whole or in part, in the form of a computer program product.

[0077] Those skilled in the art will recognize that the modules and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and inventive constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0078] In addition, the functional modules in the various embodiments of this application can be integrated into one processing module, or each module can exist physically separately, or two or more modules can be integrated into one module.

[0079] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in this application should be included within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.

[0080] In conclusion, the above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A metadata-driven natural language data exploration dialogue generation method, characterized in that, The steps include: parsing the user's query change intent in the current round of natural language input, locating the target table to which the target field belongs, and if the target table does not belong to the set of tables involved in the current query state object, obtaining a set of candidate paths based on the ER relationship graph, evaluating the cost by combining the path length and the penalty value of the cardinality mark of each associated edge, and selecting the path with the minimum total cost as the final incremental JOIN path. The process involves iterating through the associated edges of the final incremental JOIN path, reading the cardinality markers, identifying one-to-many associated edges as amplification edges and counting their number, and determining the acceptance method based on the number of amplification edges. If it is a pre-aggregation acceptance, the amplification edge's left table name, left table primary key field, and right table name are read to obtain the amplification edge's positioning information. Based on the positional relationship between the table to which the aggregated field belongs and the amplification edge, the correction flags for each aggregation expression are determined. This includes splitting the final incremental JOIN path into a left half and a right half, with the left table of the amplification edge as the boundary. The left half includes the left table and other associated tables in the already involved table set, while the right half includes tables from the right table to the target table. When the table to which the aggregated field belongs belongs to the left half, the correction flag is set to true; when it belongs to the right half, the correction flag is set to false. Before rewriting an aggregate expression whose correction flag is true, read its pre-defined inflation response type flag in the metadata. Inflation response types include accumulation-sensitive and inflation-immune types. The condition for accumulation-sensitive type is that after copying any record in the aggregate input set into N positive integers, the result of the aggregate expression is equal to N times the result before copying. The condition for inflation-immune type is that the aggregate result remains unchanged after the copying operation. For aggregate expressions with inflation response type of inflation-immune, correct their correction flag to false and keep the original expression unchanged. Based on the magnified edge positioning information, the right table is used as the data source and counted by grouping by the associated fields. The count results are associated with the fact records of the left table to obtain the inflation factor. The reciprocal of the inflation factor after replacing null values ​​is defined as the row contribution weight. For the aggregate expression with the correction flag being true and the inflation response type being cumulatively sensitive, the single row contribution value is extracted, multiplied by the row contribution weight, and then summed and aggregated to obtain the corrected aggregate expression, which is then assembled into an SQL query plan. Translate the SQL query plan into an executable SQL statement, execute it, obtain the result set, verify the accuracy of the result set, return it, and update the query status object.

2. The method for generating dialogues based on metadata-driven natural language data exploration according to claim 1, characterized in that, The query change intent of the user's natural language input in this round includes: query change intent including but not limited to adding grouping dimensions, replacing grouping dimensions, adding filter conditions, adding metrics, and replacing metrics; using rule template matching, the user input is matched with a set of preset keyword templates covering various change scenarios, and if a match is found, the target field business name is extracted from the template slot and the change type is determined.

3. The method for generating dialogues based on metadata-driven natural language data exploration according to claim 2, characterized in that, When a user's input does not match any template in the keyword template set, the user's input is compared with the business aliases of all fields in the field registry using cosine similarity calculation to obtain the highest semantic similarity value; when the highest semantic similarity value exceeds the preset intent recognition confidence threshold, the corresponding field is determined as the target field business name; The process terminates when the highest semantic similarity value does not exceed the intent recognition confidence threshold, and a clarification request is returned.

4. The method for generating dialogues based on metadata-driven natural language data exploration according to claim 1, characterized in that, The candidate path set is obtained by searching the ER graph. Specifically, a breadth-first search with depth restrictions is adopted, starting from each table in the already involved table set and ending at the target table. The search depth is limited by the preset maximum number of path edges. During the search, each candidate edge is checked to see if it forms a cycle. If so, it is skipped. The candidate path set is collected, and the number of collected paths is controlled by the preset upper limit of path candidates.

5. The metadata-driven natural language data exploration dialogue generation method according to claim 4, characterized in that, The final incremental JOIN path is determined for each path in the candidate path set through the following cost evaluation steps: each associated edge in the path is assigned an edge cardinality penalty value according to its cardinality label, where the penalty value for one-to-one and many-to-one pairs is zero, and the penalty value for one-to-many pairs is the preset one-to-many pair penalty value; the total path cost is equal to the normalized number of edges obtained by multiplying the preset path length weight coefficient by the number of edges in the path and dividing by the maximum number of edges in the path, plus the sum of the penalty values ​​of all edges in the path multiplied by the preset cardinality penalty weight coefficient; the path with the minimum total path cost is selected as the final incremental JOIN path.

6. The method for generating dialogues based on metadata-driven natural language data exploration according to claim 1, characterized in that, The acceptance method based on the number of amplified edges includes: when the number of amplified edges is equal to zero, it is judged as direct acceptance, and the incremental JOIN condition and target field are directly appended to the query status object to enter the dialog generation step; when the number of amplified edges is greater than zero but does not exceed the preset amplified edge number limit, it is judged as pre-aggregation acceptance; when the number of amplified edges exceeds the amplified edge number limit, it is judged as rejection and an alternative is suggested.

7. The method for generating dialogues based on metadata-driven natural language data exploration according to claim 6, characterized in that, When a rejection is determined and an alternative is suggested, the target dimension node is used as the starting point to backtrack upwards along the hierarchical tree in the dimension-level registry. The backtracking depth is controlled by the preset dimension backtracking search depth. The system checks whether the table corresponding to the upper-level dimension can be reached from the set of tables involved through a path with an amplified edge count not exceeding the upper limit of the amplified edge count. If found, the corresponding upper-level dimension is returned as an alternative suggestion; otherwise, a rejection message is returned.

8. The method for generating dialogues based on metadata-driven natural language data exploration according to claim 1, characterized in that, After performing accuracy verification on the result set and returning the result, the query status object is updated as follows: The original reference query without incremental JOIN conditions is constructed and executed to obtain the reference total value; the results of the executable SQL statement are summed over the corrected aggregate columns to obtain the verification total value; the deviation ratio is calculated as the absolute value of the difference between the verification total value and the reference total value divided by the absolute value of the reference total value; verification passes when the deviation ratio does not exceed a preset result deviation tolerance threshold; otherwise, verification fails and an accuracy warning is issued. The update of the query status object also includes recording correction metadata, which includes the amplification edge identifier, the expansion factor subquery structure, and the corrected aggregate columns. If the user removes the changes in a subsequent dialogue round, the changes are rolled back based on the modified metadata. The query state object stores both the original and modified aggregate expressions. The original expression is used for rollback operations, and the modified expression is used for the current query execution. If the user continues to add new changes, the sum of the number of amplified edges recorded in the query state object and the number of amplified edges in the new path is compared with the upper limit of the number of amplified edges. If the sum exceeds the upper limit of the number of amplified edges, it is judged as a rejection, and an alternative dimension is searched upwards in the dimension level registry. If it does not exceed the upper limit, the pre-aggregation acceptance process continues to be executed according to this method.

Images