A method and system for dynamic SQL optimization based on blood dependence

Abstract

The application discloses a kind of based on kinship dependence dynamic SQL optimization method, it is related to database query optimization technical field.The method includes: all indexes in database are analyzed, and are divided into atomic index and composite index;According to the mathematical relationship between index, construct kinship dependence tree, wherein each node indicates an index, and each directed edge indicates the dependent relationship;Receive the index set to be queried, recursively find all dependent target atomic indexes based on graph structure;The target atomic index is grouped according to data source, and the corresponding SQL query statement is generated;The final query index result is calculated after executing the SQL statement and obtaining atomic index value.The application eliminates repeated calculation between cross indexes by uniformly managing index dependent structure, improves query performance, reduces resource consumption, and improves index development and maintenance efficiency.

Description

Technical Field

[0001] This invention relates to the field of database management, and specifically to a dynamic SQL optimization method and system based on lineage dependency. Background Technology

[0002] In modern enterprise-level data platforms, business decisions increasingly rely on indicator systems. As data scale and business complexity increase, enterprises typically need to maintain tens of thousands of indicator items. These indicators are derived from basic indicators (such as DAU, order count, and sales revenue) to form numerous composite indicators, supporting multi-dimensional and multi-scenario business analysis and operational monitoring. Due to the complex computational dependencies between indicators, a change in a single indicator can trigger a chain reaction of changes in a batch of downstream indicators. This "indicator explosion" phenomenon places higher demands on system computing performance and indicator management.

[0003] Currently, common SQL optimization methods are mainly based on the single query paradigm, that is, each query statement is independently rewritten, indexed, and the execution plan is optimized. However, with the continuous development of metrics, this optimization method has gradually revealed two shortcomings: (1) In the existing technology, different business departments often independently develop and maintain the same basic metrics (such as DAU, GMV, etc.). This leads to multiple SQL queries repeatedly calculating the same basic metrics, resulting in a large waste of computing resources and exacerbating the system load. (2) Current SQL optimizers usually use static execution plans and fail to automatically decompose the common atomic metric set based on the multiple composite metrics involved in the query, making it difficult to compress and reuse the query path when multiple metrics are queried at the same time. In addition, it is extremely difficult to reuse the program without destroying its encapsulation characteristics.

[0004] Therefore, how to effectively maintain the data system in a unified manner and save resources when querying data is an urgent problem to be solved. Summary of the Invention

[0005] This disclosure provides a dynamic SQL optimization method and system based on lineage dependency.

[0006] Firstly, this disclosure provides a dynamic SQL optimization method based on lineage dependency, including: Analyze all indicators in the database to obtain a set of atomic indicators and a set of composite indicators. The atomic indicators are the smallest indicator units in the database that can be directly obtained without calculation. Construct a bloodline dependency tree based on the mathematical relationships between the indicators; Receive all query metrics; Find all target atomic indicators needed to solve all the query indicators in the lineage dependency tree, record the mathematical relationship between all the target atomic indicators and all the query indicators, and generate the corresponding SQL query statement; Execute the SQL query statement to obtain the values ​​of all the target atomic indicators, and calculate the final result of all the query indicators.

[0007] Optionally, the mathematical relationships between the indicators, constructing a lineage dependency tree, further include: The calculation formula for each composite indicator is analyzed, and the atomic indicators or other composite indicators that it depends on are extracted. In the bloodline dependency tree, only one node is created for each index, and a directed edge is created for each pair of dependencies.

[0008] Optionally, the step of finding all target atomic indicators needed to solve all the query indicators in the lineage dependency tree, recording the mathematical relationship between all the target atomic indicators and all the query indicators, and generating the corresponding SQL query statement further includes: For each metric to be queried, based on the dependency relationship in the lineage dependency tree, recursively search all the nodes it depends on until the target atomic metric node that no longer depends on other nodes is found. All the target atomic index nodes are grouped according to their data source; Each set of target atomic indicators is merged to generate a corresponding data source SQL query statement.

[0009] Optionally, the step of analyzing all indicators in the database to obtain a set of atomic indicators and a set of composite indicators further includes: Extract the relevant fields and expressions from the database; Based on the calculation structure in the indicator expression, determine whether the indicator depends on other indicators. If not, classify it as an atomic indicator; if so, classify it as a composite indicator. Perform expression parsing on all composite indicators to extract the names, fields, and function calculation methods of other indicators they reference.

[0010] Optionally, the lineage dependency tree is constructed based on steps S610 to S630: Step S610: Define the main database interface GetSqlData to call all the requirements of SQL association; wherein, the main database interface includes the sub-database interface getSqlData for executing SQL statements, while other parts of the main database interface are used to select different sub-database interfaces and build the lineage dependency tree; Step S620: Determine the key type of extraction based on the preset i-th index; In step S630, the key type is used as the input parameter of the database interface, and then the database interface is overloaded; then it is determined whether the indicator has ended. If not, i = i + 1 and return to step 620.

[0011] Secondly, this disclosure provides a lineage-dependent dynamic SQL optimization system, including: The indicator management unit is used to analyze all indicators in the database to obtain a set of atomic indicators and a set of composite indicators. The atomic indicators are the smallest indicator units in the database that can be directly obtained without calculation. Graph construction unit, used to construct a lineage dependency tree based on the mathematical relationships between various indicators; The instruction receiving unit is used to receive all query indicators; The query statement generation unit is used to find all the target atomic indicators needed to solve all the query indicators in the lineage dependency tree, record the mathematical relationship between all the target atomic indicators and all the query indicators, and generate the corresponding SQL query statement. The result generation unit is used to execute the SQL query statement, obtain the values ​​of all the target atomic indicators, and calculate the final result of all the query indicators.

[0012] Thirdly, this disclosure provides an electronic device, including: a processor, and a memory communicatively connected to the processor; The memory stores the instructions that the computer executes; The processor executes computer-executable instructions stored in memory to implement the methods of this disclosure.

[0013] Fourthly, this disclosure provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the methods of this disclosure.

[0014] The beneficial effects of this disclosure are that, compared with the prior art, this disclosure has the following advantages: 1) This invention constructs a unified indicator lineage graph to identify and merge the common dependencies among multiple composite indicators, extracts the required atomic indicator data only once, and performs a merge query at the data source level, avoiding the need for each indicator to initiate repeated SQL requests independently in the traditional mode, thus significantly reducing the database load.

[0015] 2) This invention employs a modular indicator management mechanism, representing atomic and composite indicators as a structured graph model. Developers only need to focus on the combination relationships between indicators to quickly assemble new indicators. This facilitates later change tracking and dependency impact analysis, significantly improving the system's scalability and maintainability.

[0016] 3) This invention creatively uses the new C++11 feature variadic templates, combined with the method of typetraits, to achieve index reuse without destroying the encapsulation characteristics of C++. Attached Figure Description

[0017] The accompanying drawings, which are incorporated in and form a part of this specification, illustrate embodiments consistent with this disclosure and, together with the description, serve to explain the principles of this disclosure.

[0018] Figure 1 A schematic diagram illustrating a dynamic SQL optimization method based on lineage dependency provided in an embodiment of this disclosure; Figure 2 This is a schematic diagram of a bloodline dependency tree construction method provided in an embodiment of the present disclosure; Figure 3 This is a schematic diagram of an SQL query statement generation method provided in an embodiment of this disclosure.

[0019] Figure 4 This is a schematic diagram of a lineage-dependent dynamic SQL optimization system provided in an embodiment of this disclosure.

[0020] Figure 5 This is a simplified bloodline dependency tree diagram provided for an embodiment of the present disclosure.

[0021] The accompanying drawings have illustrated specific embodiments of this disclosure, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concepts of this disclosure to those skilled in the art through reference to particular embodiments. Detailed Implementation

[0022] The present disclosure will be further described below with reference to the accompanying drawings. The following embodiments are only used to illustrate the technical solutions of the present disclosure more clearly, and should not be used to limit the scope of protection of the present disclosure.

[0023] Figure 1 This diagram illustrates a dynamic SQL optimization method based on lineage dependency, provided as an embodiment of this disclosure. See also... Figure 1 The following is a detailed discussion of each step in conjunction with this embodiment.

[0024] S100. Analyze all indicators in the database to obtain a set of atomic indicators and a set of composite indicators. The atomic indicators are the smallest indicator units in the database that can be directly obtained without calculation.

[0025] The goal of step S100 is to perform a structured analysis of all business metrics in the database to clarify the role of each metric in the subsequent graph structure construction, specifically distinguishing which metrics are directly executable data extraction units (i.e., atomic metrics) and which metrics are calculated by combining other metrics (i.e., composite metrics). This step provides the most basic lineage unit for the entire dynamic SQL optimization process.

[0026] Specifically, this step includes the following processing logic: First, the system reads all indicator configuration items from the database indicator management module. Each indicator typically corresponds to a calculation expression, which may consist of fields, functions, filtering conditions, or other indicators. The system parses these expressions, identifying the fields, function structures, and references to other indicators.

[0027] During the analysis, the system uses the following criteria to classify the indicators: (1) If the calculation expression of an indicator only contains direct aggregation operations on the basic field (such as COUNT, SUM, AVG) and does not contain nested references to other indicators, then the indicator is determined to be an atomic indicator. This type of indicator is the smallest unit of calculation and can be directly obtained from database SQL queries without relying on other indicators or intermediate calculation processes.

[0028] (2) If an indicator's expression references other indicators, or if its structure is a combination of multiple basic operation results (such as addition, subtraction, multiplication, division, or nested functions), it is considered a composite indicator. The value of such indicators depends on the prior calculation of one or more other indicators, and therefore cannot be directly pushed down to the database for execution.

[0029] For example, on a certain e-commerce platform, the daily active users (DAU) are expressed as "COUNT(DISTINCT user_id)" and are based on the user behavior log table, without relying on other metrics, making them a typical atomic metric. Meanwhile, the average order value (ARPU) is calculated as "paid sales ÷ number of paying users." Both components are defined as atomic metrics separately in the system; therefore, ARPU itself should be identified as a composite metric.

[0030] The system will sequentially perform the above analysis and classification on all indicators, ultimately outputting two sets: an atomic indicator set and a composite indicator set. The atomic indicator set contains all the smallest directly executable query units; the composite indicator set contains all indicator structures that require subsequent composite calculations. This classification structure not only provides node and dependency information for graph structure construction but also lays the foundation for query plan optimization and reuse.

[0031] By implementing step S100, the chaotic state of "managing all indicators flatly" in traditional business can be effectively broken, and the indicator system can be clearly divided into two levels: basic units and combinational logic, providing structured support for dynamic SQL optimization and lineage tracing.

[0032] In one embodiment, step S100 analyzes all indicators in the database to obtain a set of atomic indicators and a set of composite indicators, and further includes: extracting fields and expressions related to the indicators from the database; determining whether the indicator depends on other indicators based on the calculation structure in the indicator expression, if not, classifying it as an atomic indicator, and if so, classifying it as a composite indicator; parsing the expressions of all composite indicators to extract the names, fields, and function calculation methods of other indicators they reference.

[0033] First, the system scans all registered indicator definitions in the database, extracting the field names, calculation expressions, filtering conditions, and the data source associated with each indicator. For example, an indicator might be defined as: The metric D = SUM(revenue) / COUNT(DISTINCT user_id) will be used as the parsing input by the system.

[0034] Next, the system will determine whether the metric depends on other metrics based on the calculation structure of the expression. If the expression only involves field operations or database function calculations and does not reference other defined metrics (e.g., directly referencing the fields revenue or user_id), it will be classified as an atomic metric. Such metrics can be directly obtained at the data level through SQL aggregate functions without further decomposition.

[0035] Conversely, if an indicator's expression references other indicators (e.g., indicator F = indicator D × 100, where D depends on other indicators), then that indicator is classified as a composite indicator. The system will mark it as an intermediate node in the dependency chain and further parse its expression.

[0036] For all expressions determined to be composite indicators, the system will continue to recursively expand downwards, extracting the names of other referenced indicators, the underlying fields involved, and their function calculation methods. For example, if indicator A is defined as A = B + C, and B = SUM(clicks) and C = COUNT(impressions), the system will ultimately determine that: A is a composite indicator; B and C are atomic indicators; B uses the `clicks` field, and C uses the `impressions` field.

[0037] Ultimately, the system groups all metrics that no longer depend on other metrics (i.e., atomic metrics) into the atomic metric set, and the remaining metrics with dependency structures into the composite metric set, providing the basic structural support for subsequent graph construction and query optimization. This process ensures that the definition of each metric is structurally decomposed and provides clear dependency inputs for graph model construction.

[0038] S200. Construct a bloodline dependency tree based on the mathematical relationships between the indicators.

[0039] In this embodiment, the system further constructs a dependency graph between indicators based on the set of atomic and composite indicators obtained in step S100. This graph uses a Directed Acyclic Graph (DAG) structure to represent the references, combinations, and dependencies between all indicators. The graph construction process includes key steps such as node creation, dependency extraction, edge addition, and structural constraints. The aim is to clearly express the derivation paths between indicators structurally to support subsequent SQL generation and optimization.

[0040] First, for both atomic and composite indicators, the system uniformly assigns a unique node to each indicator. Atomic indicators, as the lowest-level nodes in the graph, are data units that can be directly extracted from the database without relying on other indicators. Composite indicators, on the other hand, are built upon references to other indicators, acting as upper-level nodes that depend on several lower-level nodes. During graph construction, regardless of how many other indicators reference a particular indicator, the system only creates one node for it, preventing redundant structures in the graph.

[0041] Secondly, for each composite indicator, the system parses its calculation formula or definition expression and identifies the names of other indicators referenced within it. Whenever a dependency is identified, the system adds a directed edge to the graph from the referenced indicator node to the current indicator node. This directed edge represents the "dependency" relationship, pointing from the base data to the derived data, thus ensuring a bottom-up data flow.

[0042] To avoid creating closed loops, the system performs a loop closure check on the graph structure before adding each edge. If a circular dependency is detected (e.g., a composite metric indirectly or directly references itself), the system will stop the graph construction process and mark the definition as a configuration error. This ensures the directed acyclic property of the graph, guaranteeing that subsequent dependency resolution and execution order are unique and traceable.

[0043] Furthermore, the system automatically identifies and handles situations where multiple composite indicators have a "common dependency" on the same atomic or intermediate indicator. For example, if "indicator C = A + B" and "indicator D = A / B", where A and B are both atomic indicators, then the graph contains only one node A and one node B, pointing to the two composite indicator nodes C and D respectively. A and B will not be repeatedly referenced in the graph, thus achieving deduplication and compression of the graph structure. This mechanism not only improves graph construction efficiency but also lays the structural foundation for multiple indicators to share subqueries in the future.

[0044] S300: Receives all query metrics.

[0045] In this embodiment, the system enters the query execution phase, first performing the operation of receiving the metrics to be queried. Query metrics are business query requests initiated by users or upper-layer applications, typically provided to the system in the form of a set of metric names or business metric expressions. For example, the business analysis module of an e-commerce platform might initiate a query request to calculate business metrics such as "monthly active users (MAU)," "conversion rate (CTR)," and "average GMV per user" for a certain region.

[0046] The system supports receiving query metrics requests through various methods, such as configuration files, visual interfaces, REST APIs, and SQL analyzers. In practical applications, query metrics might be a set of key indicators from a user-configured dashboard template, or a set of parameters from a real-time data analysis task. Upon receiving the metric set, the system will perform a unified metric parsing process.

[0047] During the parsing process, the system first determines whether each queried indicator exists in the current indicator registry. If it is a composite indicator, its corresponding calculation definition is loaded from the indicator dictionary; if it is an atomic indicator, it is directly marked as a data source input item. The system ensures that all input indicators are registered indicator names and will issue prompts or interrupt execution for missing or incorrect indicator names.

[0048] The core objective of this step is to standardize and structure the query requirements of users or upper-level systems into a set of metrics that can be used for graph search and SQL generation. Each metric in this set must be able to find its node representation in the graph structure to ensure that subsequent steps can accurately match its dependency paths and computational logic. Simultaneously, to support multi-metric merging and optimization, the system transmits the query metric set as a whole at this stage, rather than processing them one by one.

[0049] S400. Find all target atomic indicators needed to solve all the query indicators in the lineage dependency tree, record the mathematical relationship between all the target atomic indicators and all the query indicators, and generate the corresponding SQL query statement.

[0050] After receiving and standardizing the set of query metrics, the system enters the lineage tracing and SQL generation stage. The core objective of this stage is to determine the set of atomic metrics that each query metric ultimately depends on, i.e., the smallest input unit of the computation link, based on the constructed lineage dependency tree (DAG), and construct an SQL query plan accordingly to avoid redundant calculations.

[0051] Specifically, the system first performs a graph backtracking operation on each queried metric. Backtracking refers to starting from a composite metric node and recursively searching upwards along the established directed edges in the graph for its dependent nodes, until all nodes that no longer depend on any other metrics are found. These endpoint nodes are the atomic metrics, representing the lowest-level data items required for the calculation of that metric.

[0052] For example, if a query metric CTR is defined as Click / PV, where Click is composed of Click_APP + Click_PC, and PV is an atomic metric; and Click_APP and Click_PC are also atomic metrics, then the system traverses the graph CTR → Click → Click_APP / Click_PC, records the dependency edges, and finally determines that the set of atomic metrics that CTR depends on is {Click_APP, Click_PC, PV}.

[0053] When a query contains multiple metrics, the system performs similar dependency analysis on all metrics and merges all dependency paths. During this process, if two or more metrics depend on the same atomic metric, that metric is calculated only once. For example, if both CTR and ROI depend on PV and Cost, the system will identify the shared set of atomic metrics {PV, Cost} and generate only one query in SQL to extract these two metrics.

[0054] After dependency path resolution is complete, the system groups atomic metrics according to their respective data sources. For a set of atomic metrics from the same data source, the system constructs an aggregation SQL statement to extract their corresponding fields. The generated SQL statement typically includes field filtering, filtering conditions, aggregation methods (such as SUM, COUNT, AVG), etc., and is stored in a structured format in the scheduling task.

[0055] Simultaneously, the system records the specific positional relationship and calculation method of each atomic indicator within the query indicators. For example, in the formula CTR = Click / PV, it records that Click is composed of Click_APP + Click_PC, and details which base table Click_APP, Click_PC, and PV originate from, which field they reside in, and what functions are used for processing them. Recording this mathematical relationship facilitates the subsequent reconstruction of atomic indicator values ​​into composite indicators.

[0056] After dependency path resolution is complete, the system groups atomic metrics according to their respective data sources. For a set of atomic metrics from the same data source, the system constructs an aggregation SQL statement to extract their corresponding fields. The generated SQL statement typically includes field filtering, filtering conditions, aggregation methods (such as SUM, COUNT, AVG), etc., and is stored in a structured format in the scheduling task.

[0057] Simultaneously, the system records the specific positional relationship and calculation method of each atomic indicator within the query indicators. For example, in the formula CTR = Click / PV, it records that Click is composed of Click_APP + Click_PC, and details which base table Click_APP, Click_PC, and PV originate from, which field they reside in, and what functions are used for processing them. Recording this mathematical relationship facilitates the subsequent reconstruction of atomic indicator values ​​into composite indicators.

[0058] S500. Execute the SQL query statement to obtain the values ​​of all the target atomic indicators, and calculate the final result of all the query indicators.

[0059] In this step, the system extracts and executes the target atomic metrics in a unified manner based on the SQL query statement generated in the previous step, ensuring that data retrieval is completed with the smallest computing unit, and then calculates the final query metric results layer by layer.

[0060] First, the system aggregates all target atomic metrics by data source, grouping atomic metrics belonging to the same data table or data domain into a single SQL statement for querying, thereby improving query efficiency and reducing resource consumption. For example, when PV, UV, and Cost all originate from the data table fact_daily_stats, the system automatically merges them to generate the following SQL query: SELECT SUM(pv) AS pv, COUNT(DISTINCT user_id) AS uv, SUM(cost) AScost FROM fact_daily_stats WHERE date = '2025-07-01' The execution of this SQL statement will return the required atomic metric values: PV, UV, and Cost. Similarly, all other atomic metrics are organized into a minimum number of SQL statement executions using the same strategy.

[0061] Secondly, the system passes the execution results to the metric calculation module. This module, based on the mathematical relationships parsed from the aforementioned dependency tree, calculates the composite metric sequentially according to the dependency chain built upwards from the atomic metric. Specifically: if the user requests a CTR (Click-Through Rate), it is defined as CTR = Click / PV; if Click is composed of Click_APP and Click_PC, the system first executes Click = Click_APP + Click_PC; then it executes CTR = Click / PV to obtain the final metric result.

[0062] Throughout the calculation process, the system supports automatic invocation of built-in functions (addition, division, averaging, weighted multiplication) and checks whether the input values ​​are valid. If there are missing values ​​or division by zero, the system will handle them according to the preset fault tolerance strategy (such as default value substitution and error prompts).

[0063] If certain atomic metrics have been frequently queried historically, the system also supports a caching and reuse mechanism. This mechanism reuses existing calculation results within a certain time window, avoiding redundant queries. For scenarios involving parallel calculation of multiple sets of query metrics, the system will automatically split the tasks and execute them concurrently, accelerating the overall return time.

[0064] Figure 2 This diagram illustrates a method for constructing a lineage dependency tree according to an embodiment of this disclosure. Now, regarding... Figure 2 Let's elaborate on that.

[0065] S210. Analyze the calculation formula of each composite index and extract the atomic index or other composite indexes that it depends on.

[0066] In step S210, the system first reads all defined composite indicators from the indicator management module and parses their calculation formulas one by one. Each composite indicator is usually defined in the form of a mathematical expression, for example: Indicator A = Indicator B + Indicator C; Indicator D = Indicator A ÷ Indicator E; Indicator F = Indicator D × 100.

[0067] The system uses an expression parser to parse these formulas into an operator syntax tree and classifies and identifies each factor involved in the calculation. If the factor itself is an atomic index (i.e., the smallest unit that can be directly extracted from a database field without further calculation), it is marked as a "leaf node"; if the factor is still a composite index, its calculation formula is recursively parsed to continue tracing its lower-level dependencies.

[0068] Taking "Indicator D = Indicator A ÷ Indicator E" as an example, the analysis process reveals that "Indicator A" itself depends on "Indicator B" and "Indicator C". Ultimately, this step will output a complete set of indicator dependency paths. For example, for Indicator D, there should be the following three paths: "Indicator D → Indicator A → Indicator B" and "Indicator D → Indicator A → Indicator C", as well as "Indicator D → Indicator E".

[0069] S220. In the bloodline dependency tree, only one node is established for each index, and a directed edge is established for each pair of dependencies.

[0070] In step S220, the system formally constructs a lineage dependency tree (DAG) to support subsequent optimization logic based on the dependencies between indicators extracted in step S210. The core objective of this step is to ensure that each indicator in the graph appears only once, and that all dependencies are uniquely represented by directed edges, thereby constructing an indicator structure graph that is traceable and free of redundancy.

[0071] First, the system iterates through all atomic and composite metrics, processing their dependencies one by one. For each metric (whether atomic or composite), the system checks if it already exists in the graph structure—if it doesn't, a new graph node is created; if it already exists, the existing node is reused. This prevents the creation of multiple nodes with the same metric in the graph, ensuring node uniqueness.

[0072] Next, for each metric's dependency relationship (i.e., other metrics referenced during the calculation of this metric), the system establishes a directed edge for each pair of "current metric → dependent metric". The direction of this edge clearly indicates "who depends on whom", that is, from the user to the user.

[0073] For example, B, C, and E are atomic indicators that can be directly queried from the database. A, D, and F are composite indicators, depending on the following: A = B + C; D = A ÷ E; F = D × 100.

[0074] Create one node for each metric (A, B, C, D, E, F). Construct directed edges based on dependencies: see [link to relevant documentation] Figure 5 This is a simple diagram of a bloodline dependency tree.

[0075] Figure 3 This is a schematic diagram illustrating a method for generating SQL query statements according to an embodiment of this disclosure. Now, in conjunction with... Figure 3 The specific embodiments of this application are further described below.

[0076] S410. For each metric to be queried, recursively search all the nodes it depends on according to the dependency relationship in the lineage dependency tree until the target atomic metric node that no longer depends on other nodes is found.

[0077] In step S410, the system receives one or more metrics queried by users. The system locates the node for each queried metric in the previously constructed dependency tree. Then, based on the established dependencies in the graph, the system traverses from top to bottom, level by level, to find all nodes that the metric depends on. The system continues searching until it encounters endpoint metrics, or atomic metrics, that no longer depend on any other nodes.

[0078] For example, if the query metric is F, and the graph shows that it depends on D, D depends on A and E, and A depends on B and C, then the system will recursively expand the dependencies and ultimately determine that the atomic metrics that F depends on are B, C, and E. Every dependency path is fully traced, ensuring that even if the dependency chain of a query metric is very deep, its underlying basic data source can be correctly reconstructed.

[0079] When processing multiple query metrics, the system performs the above operations independently for each metric and removes duplicate atomic metrics across all paths to ensure that the final result is a unique and complete set of target atomic metrics. This set will serve as the core foundation for generating subsequent SQL query statements.

[0080] S420. Group all the target atomic index nodes according to their data sources.

[0081] In step S420, the system has obtained all target atomic indicator nodes through the recursive search in the previous step. Since these atomic indicators may come from different data tables, views, or external data sources, the system needs to further group them according to their data source.

[0082] Each atomic metric contains its own data source identifier in its metadata, such as the table name or view name. For example, metrics B and C originate from data source T_USER, while metric E originates from data source T_ORDER. Based on this, the system groups B and C together, and E into a separate group, thus forming a grouping structure based on the data source dimension.

[0083] This grouping method ensures that subsequent generated SQL queries have the smallest data access granularity, meaning that a single SQL query only queries multiple atomic metrics within the same data source, avoiding the performance burden of cross-table or cross-database joins. Furthermore, data source grouping is a fundamental step in implementing SQL merging and result caching for reuse, which helps improve query efficiency and resource reuse.

[0084] S430. Merge each group of target atomic indicators to generate a corresponding data source SQL query statement.

[0085] In step S410, the system receives a query request submitted by the user, such as a request to query the final indicator F. Based on the dependency tree constructed earlier, the system can identify the calculation formula for F as F = D × 100, where D is equal to A ÷ E, and A is composed of B + C. Therefore, the system recursively searches for all upstream nodes that F depends on until it reaches the lowest-level atomic indicator nodes that have no further dependencies, namely B, C, and E. At this point, the system records all these atomic indicator nodes (B, C, E) and their complete dependency paths with the final queried indicator F.

[0086] Next, in step S420, the system groups the three atomic indicators according to the data sources they depend on. For example, suppose atomic indicators B and C both come from the data table T_USER, while E comes from the data table T_ORDER. Then the system categorizes these atomic indicators according to their source, dividing them into two groups: the first group is {B, C} belonging to the data source T_USER, and the second group is {E} belonging to the data source T_ORDER.

[0087] Subsequently, in step S430, the system merges and generates a corresponding data source SQL query statement for each group of atomic indicators. For example, for atomic indicators B and C in T_USER, the system merges their expressions to generate the following SQL: SELECT COUNT(DISTINCT user_id) AS indicator_B, COUNT(DISTINCT registered_user_id) AS indicator_C FROM T_USER WHERE event_date = '2025-07-06'; The atomic index E in T_ORDER is generated separately by the system: SELECT SUM(order_amount) AS indicator_E FROM T_ORDER WHERE order_status = 'paid'; Through the above methods, the system integrates and compresses cross-metric dependency paths, avoiding the repeated extraction of atomic metrics, thereby achieving the technical effects of improved execution efficiency and reduced resource overhead. The output of this step is a set of multiple aggregated and optimized SQL query statements.

[0088] The following example, using automated test cases, illustrates how to construct a dependency tree in practice to achieve metric reuse without violating C++'s encapsulation features. Note that this construction differs from the one discussed earlier; the former emphasized the logical level, while this focuses on the implementation level. Therefore, it must adhere to object-oriented encapsulation principles.

[0089] Application Scenario: The hardware device is a positioning system, the application scenario is a closed environment, and the software methods involved are automated testing methods derived from the positioning system, including: crowd gathering alarm, fence alarm, and unsupervised alarm. Each person A in the closed environment needs to wear a non-removable wristband as a positioning tag to track their status. As the names suggest, a crowd gathering alarm prevents specific individuals A from gathering; a fence alarm prohibits unauthorized personnel from entering the fenced area; and an unsupervised alarm occurs when a specific person A is not supervised by person B (who may also be wearing a wristband) for a certain period of time. The database (e.g., MySQL) includes at least the following four tables: tagid; hispos; rail; task; hisalarm. As the names suggest, tagid stores tag information (e.g., the wristband belongs to person A or person B); hispos stores real-time location information; rail retrieves fence information (fence vertex information); and task represents alarm task information, which includes at least the following fields: alarm_type; tags; switch; info. Here, `alarm_type` corresponds to the alarm type; `tags` represents the corresponding tags, usually set to empty to indicate that the alarm applies to all tags; `switch` indicates whether the alarm is enabled; `info` represents fence information, which can be empty, but in special cases, such as fence alarms, the corresponding fence number needs to be provided. `hisalarm` is a historical alarm record table, including the fields: `tagid`; `alarm_type`; and `timestamp`. Understandably, Person A and Person B refer to personnel types.

[0090] When the main code version of the location system is iterated, automated testing methods are needed to verify its correctness. It should be noted that each test case, for example, using a crowd gathering alarm, can essentially be summarized into steps A1 through A4. It should be understood that in automated testing, every detail needs to be tested individually, meaning that controlled variables must be used. Therefore, even if the logic of a crowd gathering alarm is very simple, its corresponding test cases can still include dozens.

[0091] Code snippet 1: gather_1000_map_1 #The map and task are not associated, so no alarm is triggered.

[0092] #Map Name ${map} Set Variable antu #Gathering Alert Name ${groupname} Set Variable juzhong1 #Group tag ID and position ${grouppos} Set Variable {tagid=100;posx=15;posy=16;posz=1.7},{tagid=102;posx=16;posy=15;posz=1.7},{tagid=104;posx=16 ;posy=16;posz=1.7},{tagid=502;posx=16;posy=16;posz=1.7},{tagid=503;posx=15;posy=15;posz=1.7} #List of tag IDs to be detected @{tagidlist} Create List 100 102 104 502 503 #Expected alarm result 1: Alarm 0: No alarm, the order is the same as the ID order in the list above. @{balarmlist} Create List 0 0 0 0 0 #Personnel Limit ${maxp} Set Variable 2 #The duration of the simulated data needs to be greater than the duration of the alarm issued by the server. ${emulatetime} Set Variable 10 gather_crowd_alarm2_test ls_2_5_1 ${map} ${groupname} ${grouppos} 100:00:00 23:59:59 5 3 ${maxp} ${emulatetime} ${tagidlist} ${balarmlist} Code snippet 2: gather_523_tag_1 #When the number of participants reaches the alarm threshold and the time exceeds the no-alarm period, authorized accounts will trigger an alarm, while accounts without gathering privileges will not trigger an alarm. #Map Name ${map} Set Variable luer #Gathering Alert Name ${groupname} Set Variable juzhong1 #Group tag ID and position ${grouppos} Set Variable {tagid=100;posx=15;posy=16;posz=1.7},{tagid=102;posx=16;posy=15;posz=1.7},{tagid=104;posx=16 ;posy=16;posz=1.7},{tagid=502;posx=16;posy=16;posz=1.7},{tagid=503;posx=15;posy=15;posz=1.7} #List of tag IDs to be detected @{tagidlist} Create List 100 102 104 502 503 #Expected alarm result 1: Alarm 0: No alarm, the order is the same as the ID order in the list above. @{balarmlist} Create List 1 1 1 0 0 #Personnel Limit ${maxp} Set Variable 2 #The duration of the simulated data needs to be greater than the duration of the alarm issued by the server. ${emulatetime} Set Variable 10 gather_crowd_alarm2_test ${map} ${groupname} ${grouppos} 1 00:00:0023:59:59 5 3 ${maxp} ${emulatetime} ${tagidlist} ${balarmlist} Code snippet 3: gather_519_time_1 #When the number of users reaches the alarm threshold and the time reaches the alarm-free period, but the current time period is not within the valid time period, no alarm will be generated. #Group tag ID and position ${grouppos} Set Variable {tagid=100;posx=15;posy=15;posz=1.7},{tagid=102;posx=15;posy=15;posz=1.7},{tagid=104;posx=16;posy=16;posz=1.7} #List of tag IDs to be detected @{tagidlist} Create List 100 102 104 #Expected alarm result 1: Alarm 0: No alarm, the order is the same as the ID order in the list above. @{balarmlist} Create List 0 0 0 #Personnel Limit ${maxp} Set Variable 2 #The duration of the simulated data needs to be greater than the duration of the alarm issued by the server. ${emulatetime} Set Variable 10 gather_crowd_alarm_time_test liuqiu juzhong1 ${grouppos} 1 5 3 ${maxp} ${emulatetime} ${tagidlist} ${balarmlist} Code snippet 4: gather_512_switch_1 #When the number of users reaches the alarm threshold and the alarm-free period is reached, the switch will turn off and no alarm will be generated. #Group tag ID and position ${grouppos} Set Variable {tagid=100;posx=15;posy=15;posz=1.7},{tagid=102;posx=15;posy=15;posz=1.7},{tagid=104;posx=16;posy=16;posz=1.7} #List of tag IDs to be detected @{tagidlist} Create List 100 102 104 #Expected alarm result 1: Alarm 0: No alarm, the order is the same as the ID order in the list above. @{balarmlist} Create List 0 0 0 #switch ${switch} Set Variable 0 #Personnel Limit ${maxp} Set Variable 2 #The duration of the simulated data needs to be greater than the duration of the alarm issued by the server. ${emulatetime} Set Variable 10 gather_crowd_alarm2_test liuqiu juzhong1 ${grouppos} ${switch} 00:00:00 23:59:59 5 3 ${maxp} ${emulatetime} ${tagidlist} ${balarmlist} The code snippets 1, 2, 3, and 4 above show four test cases for crowd gathering alarms. It is not difficult to see that their steps can all be summarized as steps A1 to A3.

[0093] Step A1: Open the historical alarm record table and filter out the relevant sample data and corresponding calculation results.

[0094] Step A2 involves re-examining the logic of the relevant sample data and providing the prediction results. The specific verification process is as follows: based on the timestamp, find the corresponding location data in hispos2025 and the corresponding tag data in the tagid table; locate the interface of the corresponding alarm function in the main program and execute it.

[0095] Step A3: Compare the calculation results with the prediction results to determine whether they match.

[0096] It should be noted that the relevant sample data should be obtained from the database to reflect the completeness of the test. However, in code segments 1-4, for convenience, a direct display format is used (e.g., {grouppos} corresponds to the relevant sample data, while {balarmlist} corresponds to the calculation results).

[0097] This invention creatively addresses the aforementioned problems by combining the novel C++11 feature variadic templates with the method of type traits.

[0098] Step S610: Define the main database interface GetSqlData to invoke all required SQL relationships. The main database interface includes the sub-database interface getSqlData for executing SQL statements, while other parts of the main database interface are used to select different sub-database interfaces and construct the lineage dependency tree.

[0099] More directly, the input parameter for GetSqlData is the SQL statement itself. In this case, step S610 perfectly conforms to the encapsulation characteristic.

[0100] For example, if grouppos needs to get location data from hispos, it can be written as: grouppos=GetSqlData(“select * from hispos where timestamp>current_time”, const T&...Args).

[0101] The GetSqlData interface can take the following form: template<typename RT, typename... T> RT GetSqlData(string, const T&... Args) { … / / Logical subject } In the following steps, all improvements are performed within GetSqlData. This means that test case creators do not need to concern themselves with the implementation details of GetSqlData, thus addressing the problem that this application urgently needs to solve.

[0102] Step S620: Determine the key type of extraction based on the preset i-th index.

[0103] Understandably, i is initially set to 1.

[0104] For simplicity, in the first embodiment, we assume the metric is "table consistency". We also assume there are 5 tasks, and the first subtask of each task is: find table X1, find table X1, find table X1, find table X2, and find table X2. Then, determining the extracted keywords based on the preset metric can be done as shown in the following code: typedef typename Sql::first_demand value_type Here, `Sql` is a class that encapsulates the `GetSqlData` interface, and `first_demand` represents the first metric. `value_type` represents the key data type and can be set arbitrarily. Since operations on the same table can be merged, `value_type` can be directly set to `x1` or `x2` based on the enumeration, mapping to X1 and X2 respectively. It should be noted that the significance of the enumeration is to represent `x1` and `x2` as different data types. For convenience, the `map` function will be used below as the interface for mapping from X1 to `x1` based on the enumeration.

[0105] In step S630, the key type is used as the input parameter of the database interface, and then the database interface is overloaded; then it is determined whether the indicator has ended. If not, i = i + 1 and return to step 620.

[0106] Understandably, the main database interface is recursively called based on type traits (i.e., rs = GetSqlData(sql2, args);). And... Args actually represents the second metric, the third metric, and so on.

[0107] In the first embodiment, the lineage dependency tree is currently divided into two branches, X1 and X2. Therefore, the interface that actually performs the overload is as follows: template<typename RT, typename... T> RT GetSqlData(string sql1, const T&... Args) { x = map(sql1); rs1 = getSqlData(string sql1, x); / / Logical body sql2 = traits(... Args); / / Extract the sql2 associated with the second subtask from the remaining input. rs = GetSqlData(sql2, args); / / Recursive call return rs1+rs; } template<typename RT> RT getSqlData(string, X1); template<typename RT> RT getSqlData(string, X2); It should be noted that the input parameters x1 and x2 themselves have no practical meaning. Instead, they are used to assign different child nodes to different interfaces through type traits. This completes the construction of the lineage dependency tree from an implementation perspective.

[0108] Furthermore, assuming that the second subtask of each of the five tasks is: look up table X3, look up table X3, look up table X4, look up table X4, and look up table X4, the extracted keywords can still be determined based on pre-defined metrics, as shown in the code below: typedef typename Sql::second_demand value_type Here, `Sql` is a class that encapsulates the `GetSqlData` interface, `second_demand` represents the second metric, and `value_type` represents the key type. `value_type` can be directly set to `x3` or `x4` based on the enumeration, which maps to X3 and X4 respectively. It should be noted that the significance of the enumeration is to represent `x3` and `x4` as different data types.

[0109] Figure 4 This diagram illustrates a dynamic SQL optimization system based on lineage dependency, provided as an embodiment of this disclosure. Now, in conjunction with... Figure 4 The specific embodiments of this application are further described below.

[0110] The indicator management unit is used to analyze all indicators in the database to obtain a set of atomic indicators and a set of composite indicators. The atomic indicators are the smallest indicator units in the database that can be directly obtained without calculation.

[0111] The goal of the indicator management unit is to perform structured analysis on all business indicators in the database to clarify the role of each indicator in the subsequent graph structure construction, especially to distinguish which indicators are directly executable data extraction units (i.e., atomic indicators) and which indicators are calculated by combining other indicators (i.e., composite indicators). This step provides the most basic lineage unit for the entire dynamic SQL optimization process.

[0112] Specifically, this step includes the following processing logic: First, the system reads all indicator configuration items from the database indicator management module. Each indicator typically corresponds to a calculation expression, which may consist of fields, functions, filtering conditions, or other indicators. The system parses these expressions, identifying the fields, function structures, and references to other indicators.

[0113] During the analysis, the system uses the following criteria to classify the indicators: (1) If the calculation expression of an indicator only contains direct aggregation operations on the basic field (such as COUNT, SUM, AVG) and does not contain nested references to other indicators, then the indicator is determined to be an atomic indicator. This type of indicator is the smallest unit of calculation and can be directly obtained from database SQL queries without relying on other indicators or intermediate calculation processes.

[0114] (2) If an indicator's expression references other indicators, or if its structure is a combination of multiple basic operation results (such as addition, subtraction, multiplication, division, or nested functions), it is considered a composite indicator. The value of such indicators depends on the prior calculation of one or more other indicators, and therefore cannot be directly pushed down to the database for execution.

[0115] For example, on a certain e-commerce platform, the daily active users (DAU) are expressed as "COUNT(DISTINCT user_id)" and are based on the user behavior log table, without relying on other metrics, making them a typical atomic metric. Meanwhile, the average order value (ARPU) is calculated as "paid sales ÷ number of paying users." Both components are defined as atomic metrics separately in the system; therefore, ARPU itself should be identified as a composite metric.

[0116] The system will sequentially perform the above analysis and classification on all indicators, ultimately outputting two sets: an atomic indicator set and a composite indicator set. The atomic indicator set contains all the smallest directly executable query units; the composite indicator set contains all indicator structures that require subsequent composite calculations. This classification structure not only provides node and dependency information for graph structure construction but also lays the foundation for query plan optimization and reuse.

[0117] In one embodiment, the indicator management unit analyzes all indicators in the database to obtain a set of atomic indicators and a set of composite indicators. The analysis also includes: extracting fields and expressions related to the indicators from the database; determining whether the indicator depends on other indicators based on the calculation structure in the indicator expression; classifying the indicator as an atomic indicator if it does not depend on other indicators, and classifying it as a composite indicator if it does depend on other indicators; and parsing the expressions of all composite indicators to extract the names, fields, and function calculation methods of the other indicators they reference.

[0118] First, the system scans all registered indicator definitions in the database, extracting the field names, calculation expressions, filtering conditions, and the data source associated with each indicator. For example, an indicator might be defined as: The metric D = SUM(revenue) / COUNT(DISTINCT user_id) will be used as the parsing input by the system.

[0119] Next, the system will determine whether the metric depends on other metrics based on the calculation structure of the expression. If the expression only involves field operations or database function calculations and does not reference other defined metrics (e.g., directly referencing the fields revenue or user_id), it will be classified as an atomic metric. Such metrics can be directly obtained at the data level through SQL aggregate functions without further decomposition.

[0120] Conversely, if an indicator's expression references other indicators (e.g., indicator F = indicator D × 100, where D depends on other indicators), then that indicator is classified as a composite indicator. The system will mark it as an intermediate node in the dependency chain and further parse its expression.

[0121] For all expressions determined to be composite indicators, the system will continue to recursively expand downwards, extracting the names of other referenced indicators, the underlying fields involved, and their function calculation methods. For example, if indicator A is defined as A = B + C, and B = SUM(clicks) and C = COUNT(impressions), the system will ultimately determine that: A is a composite indicator; B and C are atomic indicators; B uses the `clicks` field, and C uses the `impressions` field.

[0122] Ultimately, the system groups all metrics that no longer depend on other metrics (i.e., atomic metrics) into the atomic metric set, and the remaining metrics with dependency structures into the composite metric set, providing the basic structural support for subsequent graph construction and query optimization. This process ensures that the definition of each metric is structurally decomposed and provides clear dependency inputs for graph model construction.

[0123] Graph construction unit, used to construct a lineage dependency tree based on the mathematical relationships between various indicators.

[0124] In this embodiment, the graph construction unit is used to further construct a dependency graph between the obtained atomic and composite indicator sets. This graph adopts a Directed Acyclic Graph (DAG) structure to represent the references, combinations, and dependencies between all indicators. The graph construction process includes key steps such as node creation, dependency extraction, edge addition, and structural constraints, aiming to clearly express the derivation paths between each indicator structurally to support subsequent SQL generation and optimization.

[0125] First, for both atomic and composite indicators, the system uniformly assigns a unique node to each indicator. Atomic indicators, as the lowest-level nodes in the graph, are data units that can be directly extracted from the database without relying on other indicators. Composite indicators, on the other hand, are built upon references to other indicators, acting as upper-level nodes that depend on several lower-level nodes. During graph construction, regardless of how many other indicators reference a particular indicator, the system only creates one node for it, preventing redundant structures in the graph.

[0126] Secondly, for each composite indicator, the system parses its calculation formula or definition expression and identifies the names of other indicators referenced within it. Whenever a dependency is identified, the system adds a directed edge to the graph from the referenced indicator node to the current indicator node. This directed edge represents the "dependency" relationship, pointing from the base data to the derived data, thus ensuring a bottom-up data flow.

[0127] To avoid creating closed loops, the system performs a loop closure check on the graph structure before adding each edge. If a circular dependency is detected (e.g., a composite metric indirectly or directly references itself), the system will stop the graph construction process and mark the definition as a configuration error. This ensures the directed acyclic property of the graph, guaranteeing that subsequent dependency resolution and execution order are unique and traceable.

[0128] Furthermore, the system automatically identifies and handles situations where multiple composite indicators have a "common dependency" on the same atomic or intermediate indicator. For example, if "indicator C = A + B" and "indicator D = A / B", where A and B are both atomic indicators, then the graph contains only one node A and one node B, pointing to the two composite indicator nodes C and D respectively. A and B will not be repeatedly referenced in the graph, thus achieving deduplication and compression of the graph structure. This mechanism not only improves graph construction efficiency but also lays the structural foundation for multiple indicators to share subqueries in the future.

[0129] The instruction receiving unit is used to receive all query indicators.

[0130] In this embodiment, the system enters the query execution phase, first performing the operation of receiving the metrics to be queried. Query metrics are business query requests initiated by users or upper-layer applications, typically provided to the system in the form of a set of metric names or business metric expressions. For example, the business analysis module of an e-commerce platform might initiate a query request to calculate business metrics such as "monthly active users (MAU)," "conversion rate (CTR)," and "average GMV per user" for a certain region.

[0131] The system supports receiving query metrics requests through various methods, such as configuration files, visual interfaces, REST APIs, and SQL analyzers. In practical applications, query metrics might be a set of key indicators from a user-configured dashboard template, or a set of parameters from a real-time data analysis task. Upon receiving the metric set, the system will perform a unified metric parsing process.

[0132] During the parsing process, the system first determines whether each queried indicator exists in the current indicator registry. If it is a composite indicator, its corresponding calculation definition is loaded from the indicator dictionary; if it is an atomic indicator, it is directly marked as a data source input item. The system ensures that all input indicators are registered indicator names and will issue prompts or interrupt execution for missing or incorrect indicator names.

[0133] The core objective of this step is to standardize and structure the query requirements of users or upper-level systems into a set of metrics that can be used for graph search and SQL generation. Each metric in this set must be able to find its node representation in the graph structure to ensure that subsequent steps can accurately match its dependency paths and computational logic. Simultaneously, to support multi-metric merging and optimization, the system transmits the query metric set as a whole at this stage, rather than processing them one by one.

[0134] The query statement generation unit is used to find all target atomic indicators needed to solve all the query indicators in the lineage dependency tree, record the mathematical relationship between all the target atomic indicators and all the query indicators, and generate the corresponding SQL query statement.

[0135] After receiving and standardizing the set of query metrics, the system enters the lineage tracing and SQL generation stage. The core objective of this stage is to determine the set of atomic metrics that each query metric ultimately depends on, i.e., the smallest input unit of the computation link, based on the constructed lineage dependency tree (DAG), and construct an SQL query plan accordingly to avoid redundant calculations.

[0136] Specifically, the system first performs a graph backtracking operation on each queried metric. Backtracking refers to starting from a composite metric node and recursively searching upwards along the established directed edges in the graph for its dependent nodes, until all nodes that no longer depend on any other metrics are found. These endpoint nodes are the atomic metrics, representing the lowest-level data items required for the calculation of that metric.

[0137] For example, if a query metric CTR is defined as Click / PV, where Click is composed of Click_APP + Click_PC, and PV is an atomic metric; and Click_APP and Click_PC are also atomic metrics, then the system traverses the graph CTR → Click → Click_APP / Click_PC, records the dependency edges, and finally determines that the set of atomic metrics that CTR depends on is {Click_APP, Click_PC, PV}.

[0138] When a query contains multiple metrics, the system performs similar dependency analysis on all metrics and merges all dependency paths. During this process, if two or more metrics depend on the same atomic metric, that metric is calculated only once. For example, if both CTR and ROI depend on PV and Cost, the system will identify the shared set of atomic metrics {PV, Cost} and generate only one query in SQL to extract these two metrics.

[0139] After dependency path resolution is complete, the system groups atomic metrics according to their respective data sources. For a set of atomic metrics from the same data source, the system constructs an aggregation SQL statement to extract their corresponding fields. The generated SQL statement typically includes field filtering, filtering conditions, aggregation methods (such as SUM, COUNT, AVG), etc., and is stored in a structured format in the scheduling task.

[0140] Simultaneously, the system records the specific positional relationship and calculation method of each atomic indicator within the query indicators. For example, in the formula CTR = Click / PV, it records that Click is composed of Click_APP + Click_PC, and details which base table Click_APP, Click_PC, and PV originate from, which field they reside in, and what functions are used for processing them. Recording this mathematical relationship facilitates the subsequent reconstruction of atomic indicator values ​​into composite indicators.

[0141] After dependency path resolution is complete, the system groups atomic metrics according to their respective data sources. For a set of atomic metrics from the same data source, the system constructs an aggregation SQL statement to extract their corresponding fields. The generated SQL statement typically includes field filtering, filtering conditions, aggregation methods (such as SUM, COUNT, AVG), etc., and is stored in a structured format in the scheduling task.

[0142] Simultaneously, the system records the specific positional relationship and calculation method of each atomic indicator within the query indicators. For example, in the formula CTR = Click / PV, it records that Click is composed of Click_APP + Click_PC, and details which base table Click_APP, Click_PC, and PV originate from, which field they reside in, and what functions are used for processing them. Recording this mathematical relationship facilitates the subsequent reconstruction of atomic indicator values ​​into composite indicators.

[0143] The result generation unit is used to execute the SQL query statement, obtain the values ​​of all the target atomic indicators, and calculate the final result of all the query indicators.

[0144] In this step, the system extracts and executes the target atomic metrics in a unified manner based on the SQL query statement generated in the previous step, ensuring that data retrieval is completed with the smallest computing unit, and then calculates the final query metric results layer by layer.

[0145] First, the system aggregates all target atomic metrics by data source, grouping atomic metrics belonging to the same data table or data domain into a single SQL statement for querying, thereby improving query efficiency and reducing resource consumption. For example, when PV, UV, and Cost all originate from the data table fact_daily_stats, the system automatically merges them to generate the following SQL query: SELECT SUM(pv) AS pv, COUNT(DISTINCT user_id) AS uv, SUM(cost) AScost FROM fact_daily_stats WHERE date = '2025-07-01' The execution of this SQL statement will return the required atomic metric values: PV, UV, and Cost. Similarly, all other atomic metrics are organized into a minimum number of SQL statement executions using the same strategy.

[0146] Secondly, the system passes the execution results to the metric calculation module. This module, based on the mathematical relationships parsed from the aforementioned dependency tree, calculates the composite metric sequentially according to the dependency chain built upwards from the atomic metric. Specifically: if the user requests a CTR (Click-Through Rate), it is defined as CTR = Click / PV; if Click is composed of Click_APP and Click_PC, the system first executes Click = Click_APP + Click_PC; then it executes CTR = Click / PV to obtain the final metric result.

[0147] Throughout the calculation process, the system supports automatic invocation of built-in functions (addition, division, averaging, weighted multiplication) and checks whether the input values ​​are valid. If there are missing values ​​or division by zero, the system will handle them according to the preset fault tolerance strategy (such as default value substitution and error prompts).

[0148] If certain atomic metrics have been frequently queried historically, the system also supports a caching and reuse mechanism. This mechanism reuses existing calculation results within a certain time window, avoiding redundant queries. For scenarios involving parallel calculation of multiple sets of query metrics, the system will automatically split the tasks and execute them concurrently, accelerating the overall return time.

[0149] According to embodiments of this disclosure, an electronic device is also provided, which may include a processor, a communications interface, a memory, and a communication bus, wherein the processor, communications interface, and memory communicate with each other via the communication bus. The processor can invoke logical instructions in the memory to execute a configuration software-based software licensing implementation method.

[0150] Furthermore, the logical instructions in the aforementioned memory can be implemented as software functional units and sold or used as independent products, and can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this disclosure, in essence, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this disclosure. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0151] On the other hand, this disclosure also provides a non-transitory computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, is implemented to perform the configuration software-based software licensing implementation method provided by the above methods.

[0152] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0153] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0154] It should be understood that the above embodiments are only used to illustrate the technical solutions of this disclosure, and not to limit them; although this disclosure has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the foregoing embodiments, or make equivalent substitutions for some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this disclosure.

Claims

1. A dynamic SQL optimization method based on lineage dependency, characterized in that, include: Analyze all indicators in the database to obtain a set of atomic indicators and a set of composite indicators. The atomic indicators are the smallest indicator units in the database that can be directly obtained without calculation. Construct a bloodline dependency tree based on the mathematical relationships between the indicators; Receive all query metrics; Find all target atomic indicators needed to solve all the query indicators in the lineage dependency tree, record the mathematical relationship between all the target atomic indicators and all the query indicators, and generate the corresponding SQL query statement; Execute the SQL query statement to obtain the values ​​of all the target atomic indicators, and calculate the final result of all the query indicators; The lineage dependency tree is constructed based on steps S610 to S630: Step S610: Define the main database interface GetSqlData to call all the requirements of SQL association; wherein, the main database interface includes the sub-database interface getSqlData for executing SQL statements, while other parts of the main database interface are used to select different sub-database interfaces and build the lineage dependency tree; Step S620: Determine the key type of extraction based on the preset i-th index; Step S630: Use the key type as the input parameter of the database interface, and then reload the database interface; then determine whether the indicator has ended. If not, let i = i + 1 and return to step 620. Among them, the key type is a data type composed of database table names based on enumeration.

2. The dynamic SQL optimization method based on lineage dependency according to claim 1, characterized in that, The construction of the kinship dependency tree based on the mathematical relationships between the indicators also includes: The calculation formula for each composite indicator is analyzed, and the atomic indicators or other composite indicators that it depends on are extracted. In the bloodline dependency tree, only one node is created for each index, and a directed edge is created for each pair of dependencies.

3. The dynamic SQL optimization method based on lineage dependency according to claim 1, characterized in that, The step of finding all target atomic indicators needed to solve all the query indicators in the lineage dependency tree, recording the mathematical relationships between all the target atomic indicators and all the query indicators, and generating the corresponding SQL query statement also includes: For each metric to be queried, based on the dependency relationship in the lineage dependency tree, recursively search all the nodes it depends on until the target atomic metric node that no longer depends on other nodes is found. All the target atomic index nodes are grouped according to their data source; Each set of target atomic indicators is merged to generate a corresponding data source SQL query statement.

4. The dynamic SQL optimization method based on lineage dependency according to claim 2, characterized in that, The step of analyzing all indicators in the database to obtain the set of atomic indicators and the set of composite indicators also includes: Extract the relevant fields and expressions from the database; Based on the calculation structure in the indicator expression, determine whether the indicator depends on other indicators. If not, classify it as an atomic indicator; if so, classify it as a composite indicator. Perform expression parsing on all composite indicators to extract the names, fields, and function calculation methods of other indicators they reference.

5. A lineage-dependent dynamic SQL optimization system, applied to the method described in any one of claims 1-4, characterized in that, include: The indicator management unit is used to analyze all indicators in the database to obtain a set of atomic indicators and a set of composite indicators. The atomic indicators are the smallest indicator units in the database that can be directly obtained without calculation. Graph construction unit, used to construct a lineage dependency tree based on the mathematical relationships between various indicators; The instruction receiving unit is used to receive all query indicators; The query statement generation unit is used to find all the target atomic indicators needed to solve all the query indicators in the lineage dependency tree, record the mathematical relationship between all the target atomic indicators and all the query indicators, and generate the corresponding SQL query statement. The result generation unit is used to execute the SQL query statement, obtain the values ​​of all the target atomic indicators, and calculate the final result of all the query indicators.

6. An electronic device, comprising: A processor, and a memory communicatively connected to the processor; characterized in that: The memory stores instructions that the computer executes; The processor executes computer execution instructions stored in memory, in accordance with the steps of the method according to any one of claims 1-4.

7. A computer-readable storage medium having a computer program stored thereon, characterized in that, When executed by a processor, the program implements the steps of the method according to any one of claims 1-4.

Images