Multidimensional data query method based on embedded engine and dynamic aggregation

Abstract

This application relates to a multidimensional data query method based on an embedded engine and dynamic aggregation. It integrates factual data and their corresponding dimensional description data from different databases into a single table containing dimension and measure fields by constructing a wide data table. Based on this, different data processing methods are selected according to the parsing results of the user's multidimensional query request. For cases involving only basic aggregation tasks, the corresponding processing is performed directly in the target database containing the wide data table. For cases involving complex computational tasks, basic aggregation processing is first performed in the target database to obtain basic aggregated data. Then, the embedded database engine reads the aggregation file corresponding to the basic aggregated data and performs the processing operations corresponding to the complex computational tasks. This reduces intermediate steps in the query process, improves query efficiency, and ensures the standardization and consistency of the data used in complex calculations, thereby improving query accuracy.

Description

Technical Field

[0001] This application relates to the field of data query technology, and in particular to a multidimensional data query method based on an embedded engine and dynamic aggregation. Background Technology

[0002] With the popularization of big data technology and the deepening of enterprise digital transformation, multidimensional data query has become a core requirement for enterprise business analysis and decision support. In practical applications, users typically need to perform basic statistical, complex calculation, and multi-source data association queries based on a combination of multiple dimensions (such as time, region, and product category) and multiple metrics (such as sales revenue, order volume, and profit margin) to obtain comprehensive data analysis results.

[0003] However, existing multidimensional data query solutions reveal several significant shortcomings when handling basic aggregation and complex calculation tasks. On one hand, when users simultaneously request both basic aggregation (e.g., summing sales figures for a region over a specific time period) and complex calculations (e.g., calculating the month-on-month growth rate of sales figures for that region over that time period), the traditional approach is to execute two separate queries. This step-by-step query method not only complicates the query process but also consumes considerable time, significantly reducing overall query efficiency and failing to meet the needs of real-time enterprise decision-making. On the other hand, when processing queries for complex calculation tasks, the data may originate from multiple different types of databases, resulting in a lack of uniformity and standardization. Differences in data formats, precision, and definitions across different databases make it difficult to guarantee data consistency and accuracy during complex calculations, leading to low query accuracy for complex calculation tasks and an inability to provide reliable data support for enterprises.

[0004] Therefore, existing multidimensional data query technologies suffer from low efficiency and low query accuracy, and a new technical solution is urgently needed to solve these problems in order to meet the actual needs of enterprises in multidimensional data query. Summary of the Invention

[0005] Based on this, the purpose of this application is to provide a multi-dimensional data query method based on an embedded engine and dynamic aggregation, which optimizes the query process, improves query efficiency, and ensures that the data used in complex calculations is standardized and consistent, thereby improving query accuracy.

[0006] The multidimensional data query method based on embedded engine and dynamic aggregation described in this application includes the following steps:

[0007] Obtain a multidimensional query request input by the user; parse the multidimensional query request to obtain a parsing result; the parsing result includes at least dimension fields and measure fields;

[0008] Based on the dimension field and the measure field, obtain at least one matching wide data table; wherein, the wide data table is a single table including dimension field and measure field, obtained based on fact data from at least one database and corresponding dimension description data of the fact data;

[0009] Based on the parsing results, the following data processing steps are performed to generate the results:

[0010] When the parsing result only includes basic aggregation tasks, the processing corresponding to the basic aggregation tasks is performed on the data wide table in the target database where the data wide table is located to obtain basic aggregation data; the basic aggregation data is then used as the data processing result.

[0011] When the parsing result includes complex computation tasks, basic aggregation processing is performed on the wide data table in the target database to obtain basic aggregate data; at least one aggregate file is obtained based on the basic aggregate data; each aggregate file is read through an embedded database engine, and the processing operation corresponding to the complex computation task is performed on the basic aggregate data in each aggregate file to obtain complex computation data; the complex computation data is used as the data processing result; wherein, when the parsing result only includes complex computation tasks, the basic aggregation processing follows a preset rule; when the parsing result includes both basic aggregation tasks and complex computation tasks, the basic aggregation processing follows the basic aggregation task.

[0012] When the parsing result does not include basic aggregation tasks and complex calculation tasks, a preset summary process is performed on the data wide table in the target database to obtain summary data and use the summary data as the data processing result;

[0013] Based on the data processing results, the multidimensional query results corresponding to the multidimensional query request are obtained.

[0014] This application addresses the problem of inconsistent and standardized data in existing multidimensional data query technologies due to the diverse data sources. It constructs a wide data table—deeply integrating factual data from at least one database with its corresponding dimensional description data to form a unified single table containing dimension fields and measure fields. This solves the data inconsistency problem at the data source, providing a standardized and unified data foundation for subsequent complex computational tasks. This ensures accurate data correspondence during complex calculations, avoiding computational errors caused by differences in data format and precision, thereby significantly improving the query accuracy of complex computational tasks.

[0015] In terms of query efficiency, the system flexibly and efficiently executes data processing operations based on the parsing results of the user's multidimensional query request. When the parsing results only contain basic aggregation tasks, the corresponding processing is performed directly in the target database where the wide data table resides, reducing data transmission and intermediate steps, and quickly obtaining basic aggregated data as the query result. When the parsing results contain complex calculation tasks, basic aggregation processing is first performed in the target database to obtain basic aggregated data, and then the aggregation file corresponding to the basic aggregated data is read through the embedded database engine to perform complex calculations. This integrated processing approach avoids the cumbersome process and time consumption caused by executing independent queries separately in traditional methods, organically combining basic aggregation with complex calculation tasks, reducing repeated queries and data processing steps, and significantly improving overall query efficiency. Moreover, regardless of whether the parsing results only contain complex calculation tasks or contain both basic aggregation tasks and complex calculation tasks, the basic aggregation processing rules can be reasonably planned according to the actual situation, further optimizing the query process. When the parsing results contain neither basic aggregation tasks nor complex calculation tasks, preset summary processing is performed, which also efficiently meets the diverse query needs of users.

[0016] Finally, the embodiments of this application possess strong adaptability and scalability. The wide data table construction method allows for easy integration of data from different types of databases, adapting to diverse enterprise data storage environments. Simultaneously, the design of flexibly adjusting data processing methods according to different query requests enables the solution to handle various complex business scenarios. Whether it's simple data aggregation or complex analysis involving multiple dimensions and metrics, it can provide accurate and efficient query results, providing solid data support for enterprise business analysis and decision support, and powerfully promoting the enterprise's digital transformation process.

[0017] To better understand and implement this application, the following detailed description is provided in conjunction with the accompanying drawings. Attached Figure Description

[0018] Figure 1 This is a flowchart illustrating the multidimensional data query method based on an embedded engine and dynamic aggregation according to an embodiment of this application.

[0019] Figure 2 This is a schematic diagram illustrating the steps of performing the classification and summary task and the multi-fact table association statistics task in the embodiments of this application. Detailed Implementation

[0020] To make the objectives, technical solutions, and advantages of this application clearer, the embodiments of this application will be described in further detail below with reference to the accompanying drawings. Wherein, when the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements.

[0021] It should be understood that the embodiments described below do not represent all embodiments consistent with this application. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this application as detailed in the appended claims. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without inventive effort are within the scope of protection of this application.

[0022] The terminology used in this application is for the purpose of describing particular embodiments only and is not intended to be limiting of the application. The singular forms “a,” “the,” and “the” used in this application are also intended to include the plural forms unless the context clearly indicates otherwise. Furthermore, in the description of this application, unless otherwise stated, “a plurality” means two or more. It should also be understood that the term “and / or” as used herein refers to and includes any or all possible combinations of one or more associated listed items, for example, A and / or B, which can represent: A alone, A and B together, and B alone; the character “ / ” generally indicates that the preceding and following objects are in an “or” relationship.

[0023] It should be understood that although the terms first, second, third, etc., may be used in this application to describe various information, this information should not be limited to these terms, and these terms are only used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence, nor should they be construed as indicating or implying relative importance. Those skilled in the art can understand the specific meaning of the above terms in this application according to the specific circumstances. Depending on the context, the word "if" as used in this application can be interpreted as "when," "when," or "in response to determination."

[0024] The embodiments of this application aim to solve two key problems existing in the multidimensional data query technology: first, to improve the efficiency of multidimensional data query and avoid the complexity and time consumption caused by executing independent queries when processing basic aggregation and complex calculation tasks; second, to improve the accuracy of query and solve the problem that the lack of uniformity and standardization of data due to the diversity of data sources affects the accuracy of query in complex calculation tasks.

[0025] To address this, this application's embodiments construct a wide data table, integrating factual data and their corresponding dimensional description data from different databases to form a single table containing dimension fields and measure fields. Based on this, different data processing methods are flexibly selected according to the parsing results of the user's multidimensional query request. For cases involving only basic aggregation tasks, the corresponding processing is directly performed in the target database where the wide data table resides. For cases involving complex computational tasks, basic aggregation processing is first performed in the target database to obtain basic aggregated data, and then the aggregation file corresponding to the basic aggregated data is read through the embedded database engine to perform the processing operations corresponding to the complex computational tasks. This integrated processing method reduces intermediate steps in the query process, improves query efficiency, and utilizes the unified data format of the wide data table to ensure the standardization and consistency of the data used in complex calculations, thereby improving query accuracy.

[0026] Please refer to Figure 1 The multidimensional data query method based on embedded engine and dynamic aggregation described in this application includes the following steps:

[0027] S101: Obtain the multidimensional query request input by the user; parse the multidimensional query request to obtain the parsing result; the parsing result includes at least a dimension field and a metric field;

[0028] S102: Based on the dimension field and the measure field, obtain at least one matching wide data table; wherein, the wide data table is a single table including dimension field and measure field, obtained based on fact data from at least one database and dimension description data corresponding to the fact data;

[0029] S103: Based on the parsing results, perform the following data processing result generation steps:

[0030] S104: When the parsing result only includes the basic aggregation task, in the target database where the data wide table is located, perform the processing corresponding to the basic aggregation task on the data wide table to obtain the basic aggregation data; use the basic aggregation data as the data processing result.

[0031] S105: When the parsing result includes a complex computation task, perform basic aggregation processing on the data wide table in the target database to obtain basic aggregation data; obtain at least one aggregation file based on the basic aggregation data; read each aggregation file through an embedded database engine, perform the processing operation corresponding to the complex computation task on the basic aggregation data in each aggregation file to obtain complex computation data; use the complex computation data as the data processing result; wherein, when the parsing result only includes a complex computation task, the basic aggregation processing follows a preset rule; when the parsing result includes both a basic aggregation task and a complex computation task, the basic aggregation processing follows the basic aggregation task.

[0032] S106: When the parsing result does not include basic aggregation tasks and complex calculation tasks, a preset summary processing is performed on the data wide table in the target database to obtain summary data and use the summary data as the data processing result;

[0033] S107: Based on the data processing results, obtain the multidimensional query results corresponding to the multidimensional query request.

[0034] This application addresses the problem of inconsistent and standardized data in existing multidimensional data query technologies due to the diverse data sources. It constructs a wide data table—deeply integrating factual data from at least one database with its corresponding dimensional description data to form a unified single table containing dimension fields and measure fields. This solves the data inconsistency problem at the data source, providing a standardized and unified data foundation for subsequent complex computational tasks. This ensures accurate data correspondence during complex calculations, avoiding computational errors caused by differences in data format and precision, thereby significantly improving the query accuracy of complex computational tasks.

[0035] In terms of query efficiency, the system flexibly and efficiently executes data processing operations based on the parsing results of the user's multidimensional query request. When the parsing results only contain basic aggregation tasks, the corresponding processing is performed directly in the target database where the wide data table resides, reducing data transmission and intermediate steps, and quickly obtaining basic aggregated data as the query result. When the parsing results contain complex calculation tasks, basic aggregation processing is first performed in the target database to obtain basic aggregated data, and then the aggregation file corresponding to the basic aggregated data is read through the embedded database engine to perform complex calculations. This integrated processing approach avoids the cumbersome process and time consumption caused by executing independent queries separately in traditional methods, organically combining basic aggregation with complex calculation tasks, reducing repeated queries and data processing steps, and significantly improving overall query efficiency. Moreover, regardless of whether the parsing results only contain complex calculation tasks or contain both basic aggregation tasks and complex calculation tasks, the basic aggregation processing rules can be reasonably planned according to the actual situation, further optimizing the query process. When the parsing results contain neither basic aggregation tasks nor complex calculation tasks, preset summary processing is performed, which also efficiently meets the diverse query needs of users.

[0036] Finally, the embodiments of this application possess strong adaptability and scalability. The wide data table construction method allows for easy integration of data from different types of databases, adapting to diverse enterprise data storage environments. Simultaneously, the design of flexibly adjusting data processing methods according to different query requests enables the solution to handle various complex business scenarios. Whether it's simple data aggregation or complex analysis involving multiple dimensions and metrics, it can provide accurate and efficient query results, providing solid data support for enterprise business analysis and decision support, and powerfully promoting the enterprise's digital transformation process.

[0037] The multidimensional data query method based on embedded engine and dynamic aggregation described in this application uses a computer as the execution subject. The following describes each step.

[0038] For step S101, obtain the multidimensional query request input by the user; parse the multidimensional query request to obtain the parsing result; the parsing result includes at least dimension fields and metric fields.

[0039] Multidimensional query requests are user requests to query and analyze data based on multiple dimensions (such as time, region, product category, etc.). These requests typically include the dimension fields and measure fields of the data the user wishes to retrieve.

[0040] Dimension fields describe different attributes or categories of data characteristics, such as time dimension, region dimension, etc., and are used to group or filter data.

[0041] Measure fields are used to represent the specific numerical performance of data in various dimensions. They are usually numerical data that needs to be calculated or summarized, such as sales revenue and quantity.

[0042] In this step, the user inputs a multidimensional query request through the interface or API. This request describes the information the user wants to obtain from the data, including the dimensions of interest (such as time, region) and the metrics to be calculated (such as sales revenue, profit). The query request is parsed to extract the dimension fields and metric fields, yielding the parsed results.

[0043] For step S102, at least one matching wide data table is obtained based on the dimension field and the measure field; wherein, the wide data table is a single table including dimension field and measure field obtained based on fact data from at least one database and the dimension description data corresponding to the fact data.

[0044] Among them, the wide data table is a single-table structure that includes dimension fields and measure fields. It is obtained by integrating fact data and corresponding dimension description data from at least one database, providing a unified and standardized data foundation for complex computing tasks.

[0045] This step searches for matching wide data tables from a pre-built wide data table library based on the dimension and measure fields in the parsing results. These wide data tables are obtained by integrating fact data and dimensional description data from multiple databases, ensuring data consistency and standardization.

[0046] For step S103, the following data processing result generation steps are performed based on the parsing result.

[0047] This step is the core of the data processing flow. Depending on the type of the parsing result, such as only basic aggregation tasks, including complex calculation tasks, or neither basic aggregation tasks nor complex calculation tasks, different data processing logic is executed.

[0048] For step S104, when the parsing result only includes the basic aggregation task, the processing corresponding to the basic aggregation task is performed on the data wide table in the target database where the data wide table is located to obtain the basic aggregation data; the basic aggregation data is used as the data processing result.

[0049] Basic aggregation tasks refer to computational tasks that perform simple summaries on metric fields, typically grouped based on dimension fields. Basic aggregation tasks include one or more of the following: summation, counting, average, maximum, and minimum value calculations.

[0050] In this step, if the parsed results only contain basic aggregation tasks, such as summation and averaging, these tasks are executed directly in the target database containing the wide data table to obtain the basic aggregated data, which is then used as the data processing result. This approach reduces data transmission and intermediate steps, improving query efficiency.

[0051] For step S105, when the parsing result includes a complex computation task, basic aggregation processing is performed on the data wide table in the target database to obtain basic aggregate data; at least one aggregate file is obtained based on the basic aggregate data; each aggregate file is read through an embedded database engine, and the processing operation corresponding to the complex computation task is performed on the basic aggregate data in each aggregate file to obtain complex computation data; the complex computation data is used as the data processing result; wherein, when the parsing result only includes a complex computation task, the basic aggregation processing follows a preset rule; when the parsing result includes both a basic aggregation task and a complex computation task, the basic aggregation processing follows the basic aggregation task.

[0052] Complex calculation tasks involve multiple steps, multiple dimensions, or multiple metric fields, such as year-on-year comparison, month-on-month comparison, and percentage calculation. These tasks usually require further processing based on the basic aggregated data.

[0053] An embedded database engine is a lightweight database management system directly integrated into an application, such as DuckDB or SQLite. It performs data storage, querying, and computation in memory without requiring separate deployment. In this solution, it is used to read aggregate files and perform complex computational tasks. Its core features include: 1. Zero-configuration deployment: No server installation or network connection configuration is required; it is directly embedded in the application code. 2. In-memory computation: Data is loaded into memory for post-processing, avoiding disk I / O bottlenecks and supporting millisecond-level response times. 3. SQL compatibility: Supports standard SQL syntax, reducing development and learning costs. 4. Cross-platform support: Can run on Windows, Linux, macOS, and other systems, and is compatible with various programming languages ​​(such as Python and Java). In this embodiment, DuckDB is the preferred embedded database engine. DuckDB is a relational database engine primarily designed for embedded OLAP, offering zero-configuration and easy deployment without requiring a separate server, while balancing in-memory computation and disk persistence. It adopts columnar storage and multi-core parallel computing, which has outstanding performance in complex analysis of GB-level data. It is compatible with complete SQL and various data types, deeply adapted to data ecosystems such as Python, can directly read DataFrames, and supports ACID transactions, balancing analysis efficiency and data reliability.

[0054] Aggregate files are intermediate result files after basic aggregation processing, typically containing the following characteristics: 1. They employ columnar storage formats (such as Parquet) or binary encoding (such as Avro) to reduce storage space. 2. They pre-aggregate metric fields (such as sales revenue, quantity) according to specified dimensions (such as region, time) using SUM / COUNT / AVG. 3. They retain only the fields needed for analysis, removing redundant information from the original tables. Embedded database engines directly read pre-generated aggregation files (such as CSV, Parquet) and parse them into structured data tables in memory. Embedded database engines can perform FULL OUTER JOIN on multiple aggregation files, retaining records from all tables and filling in NULL values ​​when no matches are found. Complex calculations (such as ratios, rankings, window functions) are then performed on the joined data to generate the final statistical results.

[0055] In this embodiment, the original fact table (such as tens of millions of sales records) is pre-aggregated into an aggregation file of tens of thousands of records, reducing the amount of data required for subsequent correlation calculations. The aggregation file can be sharded and stored on different nodes, and the embedded engine reads and merges the results in parallel. Furthermore, the data can be persisted, saved to disk as intermediate results to avoid redundant calculations and support breakpoint continuation.

[0056] In this embodiment, if the parsed results contain complex computational tasks, basic aggregation processing is first performed on the wide table in the target database to obtain basic aggregated data. Then, aggregate files are generated based on this basic aggregated data. The embedded database engine reads these aggregate files and performs the processing operations corresponding to the complex computational tasks to obtain the complex computational data. This processing method organically combines basic aggregation with complex computational tasks, improving overall query efficiency.

[0057] In one embodiment, the complex computation task includes a derivative computation task; the derivative computation task includes a time-related computation task and a ranking computation task; the complex computation data includes time-related computation data and ranking computation data.

[0058] Among them, derivative computing tasks refer to computing tasks that derive new indicators or data based on basic aggregated data through specific rules or algorithms. These tasks typically involve in-depth analysis of dimensions such as time and ranking.

[0059] Time-related computation tasks are a type of derivative computation task, which mainly handles calculations related to the time dimension, such as year-on-year comparisons, month-on-month comparisons, and growth rates.

[0060] Ranking calculation tasks are another type of derivative calculation task, which mainly sort data and determine its ranking based on the values ​​of one or more metric fields.

[0061] When the complex computing task includes time-dependent computing tasks, step S105, which involves reading each of the aggregate files through an embedded database engine and performing the processing operations corresponding to the complex computing task on the basic aggregate data in each aggregate file to obtain the complex computing data, includes:

[0062] Step S10511: Determine the time dimension field and derived calculation rules corresponding to the time-related calculation task; the derived calculation rules include basic time fast calculation rules and complex time calculation rules.

[0063] Among them, the fast time calculation rule is a simple and fast calculation rule for the time dimension, such as calculating the sum or average value within a certain time period.

[0064] Complex time calculation rules involve more complex time dimension calculations, such as comparisons across time periods (year-on-year, month-on-month), time series analysis, etc.

[0065] This step first identifies the time dimension fields (such as year, month, day, etc.) involved in the time-related calculation tasks and determines the required derived calculation rules. These rules may be basic (such as calculating the total sales of a certain month) or complex (such as calculating the year-on-year growth rate).

[0066] Step S10512: Extract the basic aggregate data corresponding to the time dimension field from each of the aggregate files, and perform corresponding processing according to the derived calculation rules to obtain time-related calculation data.

[0067] The system extracts basic aggregated data related to time-dimensional fields from the aggregated file and performs calculations according to defined derived calculation rules. For example, if the calculation rule is to calculate the total sales for a month, the sales metric field of all relevant records within that month is summed. For complex time-related calculations, such as year-on-year growth rate, data from the same period last year is first obtained and then compared and calculated. The final result is time-related calculated data.

[0068] When the complex computation task includes a ranking computation task, step S105, which involves reading each of the aggregate files using an embedded database engine and performing the processing operations corresponding to the complex computation task on the basic aggregate data in each aggregate file to obtain the complex computation data, includes:

[0069] Step S10513: Determine the sorting field and sorting direction corresponding to the ranking calculation task; partition the basic aggregate data in each aggregate file according to the dimension field, and sort the metric field data in each partition according to the sorting field and the sorting direction to obtain the ranking calculation data.

[0070] First, identify the sorting fields (such as sales revenue, profit, etc.) and sorting direction (ascending or descending) involved in the ranking calculation task. Then, partition the basic aggregated data in the aggregate file according to the dimension fields (such as region, product category, etc.) that may be specified in the user query. Within each partition, sort the metric field data according to the sorting field and sorting direction to determine the ranking of each record within that partition. The final ranking calculation data is then obtained.

[0071] This embodiment enriches the functionality and depth of multidimensional data queries by introducing derivative computational tasks, particularly time-related and ranking computational tasks. Time-related computational tasks enable users to easily obtain in-depth analysis results based on the time dimension, such as year-on-year and month-on-month comparisons, helping users better understand data trends over time. Ranking computational tasks, by sorting data according to specific metric fields, provide users with information on the relative position of data within a specific dimension, helping them identify leading or lagging projects.

[0072] Leveraging the technological advantages of embedded database engines, such as columnar storage accelerating aggregation and scanning speeds, vectorized execution improving CPU efficiency, and star schema optimization enhancing multi-table join query performance, this embodiment demonstrates higher efficiency and response speed when handling derivative computational tasks. It can flexibly handle various complex time-based calculations and ranking requirements, providing users with accurate and timely multi-dimensional data analysis results. This not only enhances the user experience but also provides more comprehensive and in-depth data support for enterprise business analysis and decision support.

[0073] In one embodiment, the complex computation task includes a list-based computation task; the complex computation data includes list-based computation data.

[0074] List-based calculation tasks refer to tasks that perform specific calculations, such as uniqueness counting and median calculation, on a specific metric field in the basic aggregated data. These tasks aim to extract statistically significant list-based indicators from the data. Uniqueness counting involves counting the number of unique values ​​in the target metric field, often used to analyze data diversity or uniqueness. Median calculation involves sorting the data in the target metric field by size and finding the value in the middle position, reflecting the central trend of the data, and is particularly suitable for asymmetrically distributed data.

[0075] When the complex computation task includes a list-type computation task, step S105, which involves reading each of the aggregate files using an embedded database engine and performing the processing operations corresponding to the complex computation task on the basic aggregate data in each of the aggregate files to obtain the complex computation data, includes:

[0076] Step S10521: Determine the calculation type and target metric field corresponding to the list-type calculation task; the calculation type includes one or more of unique count calculation or median calculation.

[0077] First, identify the type of calculation involved in the list-based calculation task, such as unique count calculation or median calculation, and the target metric field, such as user ID or sales revenue. This step is fundamental to subsequent processing, ensuring an accurate understanding of the user's needs.

[0078] Step S10522: Extract the basic aggregate data corresponding to the target metric field from each of the aggregate files, and perform data deduplication or sorting preprocessing on the basic aggregate data; wherein, unique count calculation performs deduplication preprocessing, and median calculation performs sorting preprocessing.

[0079] Based on the calculation type and target metric field determined in the previous step, the corresponding basic aggregate data is extracted from the aggregate file. For unique count calculations, the extracted data is deduplicated to eliminate the influence of duplicate values ​​on the counting results; for median calculations, the data is sorted to determine the position of the median later.

[0080] Step S10523: If the calculation type is unique counting calculation, count the number of records of the deduplicated data after preprocessing to obtain unique counting calculation data; if the calculation type is median calculation, determine the median position based on the number of records of the sorted basic aggregated data, and extract the value corresponding to the median position as median calculation data.

[0081] After preprocessing, the corresponding calculation is performed according to the calculation type. For uniqueness counting, the number of records after deduplication is counted to obtain uniqueness counting data. For median calculation, the position of the median is determined based on the number of records in the sorted data (if the data volume is odd, the median is the middle value; if the data volume is even, the median is the average of the two middle values), and the value corresponding to that position is extracted as the median calculation data.

[0082] Step S10524: Based on the unique count calculation data and / or median calculation data, obtain list-type calculation data.

[0083] Finally, based on the unique count data and / or median data obtained in the previous step, the final list-style calculated data is generated. This data can be directly displayed to the user or used as the basis for subsequent analysis or decision-making.

[0084] This embodiment further expands the functional scope of multidimensional data queries by introducing list-based computation tasks. Uniqueness counting and median calculation, as two important statistical indicators, help users gain a deeper understanding of the data's distribution characteristics and central trends. Combining the technical advantages of an embedded database engine, such as columnar storage, vectorized execution, and star schema optimization, this embodiment demonstrates high efficiency and accuracy in handling list-based computation tasks. It can quickly respond to user query requests, providing accurate and reliable list-based computation data, and offering users a more comprehensive and in-depth data analysis perspective.

[0085] In one embodiment, the derivative computing task includes a composite computing task and a percentage calculation task; the complex computing data includes composite computing data and percentage calculation data.

[0086] Among them, composite computing tasks refer to complex computing tasks composed of multiple sub-computing tasks, which have dependencies on each other and need to be executed in a specific order.

[0087] A percentage calculation task refers to calculating the proportion of one metric field (numerator) in another metric field (denominator). It is often used to analyze the relationship between a part and the whole. The numerator metric field is the metric field that acts as the dividend in the percentage calculation, representing the value of the part. The denominator metric field is the metric field that acts as the divisor in the percentage calculation, representing the value of the whole.

[0088] When the complex computing task includes a composite computing task, step S105, which involves reading each of the aggregate files through an embedded database engine and performing the processing operations corresponding to the complex computing task on the basic aggregate data in each aggregate file to obtain the complex computing data, includes:

[0089] Step S10531: The composite computing task is parsed into multiple sub-computing tasks, and the execution order of each sub-computing task is determined. The sub-computing tasks are executed sequentially on the basic aggregate data in each of the aggregate files according to the execution order, and the output result of the previous sub-computing task is used as the input data of the next sub-computing task until the composite computing data is obtained.

[0090] First, the complex computation task is analyzed and broken down into multiple sub-computation tasks. The execution order of these sub-computation tasks is determined based on their dependencies. Then, these sub-computation tasks are executed sequentially according to the determined order, with the output of the previous sub-computation task used as the input data for the next sub-computation task at each execution. In this way, the entire complex computation task is completed step by step, ultimately yielding the complex computation data.

[0091] When the complex computation task includes a percentage calculation task, step S105, which involves reading each of the aggregate files through an embedded database engine and performing the processing operations corresponding to the complex computation task on the basic aggregate data in each of the aggregate files to obtain the complex computation data, includes:

[0092] Step S10532: Determine the numerator and denominator metric fields of the percentage calculation task; extract the total value corresponding to the numerator metric field and the total value corresponding to the denominator metric field from the basic aggregation data in each aggregation file.

[0093] First, identify the numerator and denominator metric fields involved in the percentage calculation task. Then, extract the sum values ​​corresponding to these two metric fields from the basic aggregate data in the aggregate file. These sum values ​​will be used for subsequent percentage calculations.

[0094] Step S10533: If the total value of the denominator measurement field is zero, determine zero as the percentage calculation data; if the total value of the denominator measurement field is not zero, obtain the percentage calculation data based on the percentage of the total value of the numerator to the total value of the denominator.

[0095] After obtaining the sum of the numerator and denominator, first check if the sum of the denominator's measure field is zero. If it is zero, to avoid the error of dividing by zero, directly determine zero as the percentage calculation data. If the sum of the denominator's measure field is not zero, calculate the percentage of the sum of the numerator to the sum of the denominator, and use this percentage as the percentage calculation data.

[0096] This embodiment further enhances the flexibility and practicality of multidimensional data queries by introducing composite calculation tasks and percentage calculation tasks. Composite calculation tasks allow users to combine multiple simple calculation tasks into a complex one, thereby meeting more complex analytical needs. Percentage calculation tasks provide an intuitive way to analyze the relationship between parts and the whole, helping users better understand the distribution and proportion of data. Combining the technical advantages of an embedded database engine, such as efficient query processing capabilities and flexible data processing methods, this embodiment demonstrates excellent performance and accuracy in handling composite and percentage calculation tasks, quickly responding to user query requests and providing accurate and reliable composite and percentage calculation data. These results not only provide users with a deeper perspective on data analysis but also support more complex decision-making processes.

[0097] In one embodiment, the derivative computing task further includes a computing metric task, and the complex computing data includes computing metric data.

[0098] Among them, computational metric tasks refer to tasks that generate new metric data based on existing metric data through specific formulas or logical calculations. These new metric data can reflect deeper characteristics or relationships in the data.

[0099] When the complex computation task includes a computational metric task, step S105, which involves reading each of the aggregate files using an embedded database engine and performing the processing operations corresponding to the complex computation task on the basic aggregate data in each aggregate file to obtain the complex computation data, includes:

[0100] Step S10541: Identify at least one reference metric that the computational metric task depends on; extract the basic metric data corresponding to each reference metric from each of the aggregate files, perform preprocessing on the basic metric data, and obtain preprocessed reference metric data; the preprocessing includes data cleaning and format standardization.

[0101] In this context, reference metrics are metrics that are directly referenced in the metric computation task and form the basis for calculating new metrics. When a reference metric itself is calculated from other metrics, these referenced metrics are called secondary reference metrics.

[0102] Data cleaning involves processing basic metric data to remove erroneous, abnormal, or redundant data, ensuring data quality. Format standardization involves converting basic metric data into a unified format or standard for subsequent calculations and processing.

[0103] First, the reference metrics that the computational metric task relies on are identified. Then, the underlying metric data corresponding to these reference metrics is extracted from the aggregate file. Next, this underlying metric data is preprocessed, including data cleaning (such as removing erroneous, outlier, or redundant data) and format standardization (converting the data to a uniform format or standard). The preprocessed reference metric data will be used for subsequent computational metric tasks.

[0104] Step S10542: When the reference metric does not depend on the secondary reference metric, based on the preprocessed reference metric data and the basic metric data in each of the aggregate files, the operation corresponding to the calculation metric task is performed according to the preset calculation metric formula to obtain the calculation metric result.

[0105] Among them, the preset calculation metric formula is a predefined mathematical formula or logical rule used to calculate new metric data.

[0106] If the reference metric does not depend on any secondary reference metrics (i.e., the reference metric is extracted directly from the base metric data), then the metric calculation task is performed directly based on the preprocessed reference metric data and other base metric data in the aggregation file, according to the preset metric calculation formula. After the calculation is completed, the calculated metric result is obtained.

[0107] Step S10543: When the reference metric depends on the secondary reference metric, extract the basic metric data corresponding to each secondary reference metric from each of the aggregate files and perform preprocessing on the basic metric data to obtain preprocessed secondary reference metric data; based on the preprocessed reference metric data, the secondary reference metric data and the basic metric data in the aggregate files, perform the operation corresponding to the calculation metric task according to the preset calculation metric formula to obtain the calculation metric result.

[0108] If the reference metric depends on secondary reference metrics (i.e., the reference metric is calculated from other metric data), then the underlying metric data corresponding to these secondary reference metrics needs to be extracted from the aggregate file first, and preprocessed (including data cleaning and format standardization). After preprocessing, based on the preprocessed reference metric data, secondary reference metric data, and other underlying metric data in the aggregate file, the metric calculation task is performed according to the preset metric calculation formula. After the calculation is completed, the calculated metric result is obtained.

[0109] This embodiment expands the functionality of multidimensional data queries by introducing computational metric tasks, enabling users to generate new metric data based on existing data, thereby allowing for deeper analysis of data characteristics and relationships. Leveraging the technological advantages of an embedded database engine, such as efficient query processing capabilities and flexible data processing methods, this embodiment demonstrates excellent performance and accuracy in handling computational metric tasks. It can quickly respond to user query requests and provide accurate and reliable computational metric results. These results not only provide users with richer data analysis perspectives but also support more complex decision-making processes. Furthermore, this embodiment ensures the data quality upon which the computational metric tasks depend through preprocessing steps such as data cleaning and format standardization, thereby improving the accuracy and reliability of the calculation results.

[0110] In one embodiment, when the parsing result further includes filtering conditions, before step S105, which involves performing basic aggregation processing on the data wide table in the target database to obtain basic aggregated data, the following steps are included:

[0111] S10501, Obtain the dimension filtering condition and the metric filtering condition from the filtering conditions; wherein, the dimension filtering condition is used to filter the dimension fields in the data wide table, and the metric filtering condition is used to filter the metric fields in the data wide table.

[0112] The filtering criteria can be restrictive conditions specified in the user's query to narrow the data range and ensure that the query results only include data that meets specific rules. They can also be preset data query permissions to limit the data query range for different users. Dimensional filtering criteria are filtering rules set based on dimension fields (such as time, region, product category, etc.), such as "sales revenue in East China in Q3 2025". Metric filtering criteria are thresholds or ranges set based on metric fields (such as sales revenue, order volume, profit margin, etc.), such as "sales revenue ≥ 1 million yuan".

[0113] This step first analyzes the filtering conditions in the user's query, separating the dimension filtering conditions (such as time range and region range) and the metric filtering conditions (such as sales threshold). For example, if a user queries "ranking of products with sales exceeding 5 million yuan in East China from January to September 2025", the dimension filtering condition "time = January to September 2025, region = East China" and the metric filtering condition "sales ≥ 5 million yuan" will be extracted.

[0114] S10502, Select a subset of data that meets the dimension constraints from the wide data table according to the dimension filtering conditions.

[0115] The data subset is a local data set that meets the dimensional constraints after being filtered by dimensional filtering conditions, and serves as the basis for subsequent aggregation operations.

[0116] This step performs an initial screening of the wide data table based on dimensional filtering conditions. For example, it retains only records with "January-September 2025" and "Region = East China", forming a data subset. This step significantly improves the efficiency of subsequent aggregation operations by reducing the amount of data processed.

[0117] S10503, Perform a preset basic aggregation process on the data subset to obtain a preliminary aggregation result.

[0118] The preliminary aggregation result is an intermediate result obtained after performing basic aggregation processing (such as summation, average, etc.) on a subset of data, and has not yet applied metric filtering conditions.

[0119] This step performs basic aggregation operations (such as summing sales by product category) on the filtered subset of data, generating preliminary aggregation results. For example, it summarizes the total sales of each product by product category for the same time period and region.

[0120] S10504, Based on the metric filtering conditions, select data that meets the metric constraints from the preliminary aggregation results to generate basic aggregation data.

[0121] The initial aggregation results are filtered using metrics, such as retaining only product records with "sales revenue ≥ 5 million yuan," ultimately forming basic aggregated data that meets all filtering criteria. This data will serve as the input for subsequent complex calculations (such as ranking calculations).

[0122] This embodiment achieves a dual improvement in the accuracy and efficiency of multidimensional data queries by introducing a hierarchical processing mechanism for filtering conditions. Dimensional filtering conditions complete the selection of data subsets at the wide table stage, significantly reducing the data scale of subsequent processing. Combined with the columnar storage and vectorized execution characteristics of the embedded database engine, aggregation operations can be accelerated. Metric filtering conditions are applied after the initial aggregation, ensuring that the final result conforms to business logic (such as filtering high-sales products) while avoiding the waste of ineffective computing resources.

[0123] Taking time-related calculation tasks as an example, after limiting the time range through dimensional filtering conditions, only data within a specific time period needs to be processed. By combining basic time-based rapid calculation rules (such as monthly summation) and complex time-based calculation rules (such as month-on-month growth rate), cross-time period comparative analysis can be completed in milliseconds. In ranking calculation tasks, the synergistic effect of dimensional filtering conditions (such as regional partitioning) and metric filtering conditions (such as sales thresholds) ensures that the ranking results reflect both local market characteristics and focus on high-value products, thereby improving the effectiveness of decision support.

[0124] This embodiment addresses the inefficiency caused by full-data processing in traditional solutions through a hierarchical "filter-then-aggregate" processing logic. Furthermore, data cleaning and format standardization preprocessing steps ensure the accuracy of the calculation results. Combined with the star schema optimization technology of the embedded database engine, the performance of multi-table join queries is significantly improved, enabling sub-second response times for multidimensional data analysis while maintaining accuracy, thus meeting the real-time decision-making needs of enterprises.

[0125] In one embodiment, when the parsing result further includes a list query scenario identifier, before step S105, which involves performing basic aggregation processing on the data wide table in the target database to obtain basic aggregated data, the following steps are included:

[0126] S10505, if any data wide table contains all fields of another data wide table, integrate the data of the data wide table and the other data wide table by combining all operations, and fill null values ​​for fields not included in the other data wide table to obtain a combined data wide table.

[0127] Among them, the federated wide table is a standardized data structure that integrates data from multiple wide tables and fills in missing fields to form a unified data view, supporting cross-table join queries.

[0128] Null value filling is the operation of assigning NULL or default values ​​to missing fields to ensure the consistency of the data structure after union and avoid calculation errors caused by missing fields.

[0129] In this step, when two wide tables have a field inclusion relationship (e.g., wide table A contains all fields from wide table B), a UNION ALL operation is performed to merge the data, and missing fields from wide table B are filled with NULL values. For example, if wide table A contains "Product ID, Sales Amount, Region," and wide table B only contains "Product ID, Sales Amount," then the "Region" field in the merged wide table B will be filled with NULL values. This operation ensures data structure alignment, providing a unified data foundation for subsequent cross-table queries.

[0130] S10506 If any data wide table does not contain all the fields of another data wide table, the data of the data wide table and the other data wide table are integrated by combining all operations, and the fields not contained in the data of the data wide table and the other data wide table are filled with null values ​​to obtain a combined data wide table.

[0131] When two wide tables have no inclusion relationship between their fields, executing the UNION ALL operation fills the missing fields on both tables with NULL values. For example, if wide table C contains "Product ID, Sales Amount" and wide table D contains "Product ID, Order Quantity", after merging, the "Order Quantity" field in wide table C and the "Sales Amount" field in wide table D will both be filled with NULL values. This operation ensures that the merged wide tables contain all original fields, supporting multi-dimensional list query needs.

[0132] S10507, Perform a preset basic aggregation process on the combined data wide table to obtain basic aggregated data.

[0133] Perform basic aggregation operations on the joined wide table to generate basic aggregated data suitable for list query scenarios. For example, summarize "Sales Revenue" and "Order Quantity" by "Product ID" to form an aggregated result containing detailed records. This data retains the original detailed characteristics while meeting users' query needs for detailed data.

[0134] This embodiment achieves a dual improvement in the flexibility and completeness of multidimensional data queries through a data wide table federation mechanism triggered by list query scenario identifiers. The construction of the federated data wide table solves the data inconsistency problem in multi-table join queries in traditional solutions. Null value padding ensures a consistent field structure, making cross-table query results comparable and analyzable. Combined with the columnar storage and vectorized execution characteristics of the embedded database engine, the aggregation operation efficiency of the federated data wide table is significantly improved, supporting sub-second response times for large-scale detailed data queries.

[0135] Taking complex calculation tasks as an example, the federated wide table provides a complete data foundation for multi-step calculations. For instance, when calculating the "product sales revenue percentage," the "sales revenue" and "total sales revenue" fields can be directly extracted from the federated wide table, avoiding the performance overhead caused by repeated join queries. In ranking calculation tasks, the federated wide table supports partitioning and sorting by any dimension field, ensuring that the ranking results reflect the true market characteristics.

[0136] This embodiment addresses the issues of scattered and inconsistent data formats in traditional list queries through a "join then aggregate" processing logic, while ensuring data integrity through null value filling. Combined with the star schema optimization technology of the embedded database engine, the performance of multi-table join queries is significantly improved, enabling detailed data analysis to achieve efficient response while maintaining accuracy, meeting enterprises' real-time needs for detailed data insights. The overall solution, through dynamic integration and standardized processing of wide data tables, achieves full-scenario coverage from detailed queries to complex aggregations, providing a solid data foundation for enterprise business analysis.

[0137] For step S106, when the parsing result does not include basic aggregation tasks and complex calculation tasks, a preset summary processing is performed on the data wide table in the target database to obtain summary data and use the summary data as the data processing result.

[0138] If the parsing result contains neither basic aggregation tasks nor complex calculation tasks, then a preset summary process, such as simple data statistics or display, is performed on the data wide table to obtain summary data, which is then used as the data processing result. In one embodiment, performing the preset summary process on the data wide table includes performing basic field filtering, simple grouping and integration, and field deduplication on the data wide table in the target database.

[0139] For step S107, based on the data processing results, the multidimensional query result corresponding to the multidimensional query request is obtained.

[0140] Based on the data processing results obtained in the previous step, construct and return the multidimensional query results corresponding to the multidimensional query request. These results can be tables, charts, or other forms of data display to help users better understand and analyze the data.

[0141] In one embodiment, when the parsing result further includes a display configuration task, step S107, which involves obtaining the multidimensional query result corresponding to the multidimensional query request based on the data processing result, includes:

[0142] Step S1071: If the display configuration task indicates that empty members are not displayed, filter the empty member data in the data processing result; based on the filtered data processing result, obtain the multidimensional query result corresponding to the multidimensional query request.

[0143] Among them, the display configuration task is the result display rule specified in the user query, which controls whether to include dimension members with no actual data (such as empty regions, products with zero sales) in the output results, such as "hide product categories with no sales records".

[0144] Empty member data refers to records in the aggregation results that have dimension combinations but no actual measurement values, such as the "region-quarter" combination record when a region has no sales in a certain quarter.

[0145] This step first checks if the display configuration task requires hiding empty members. If "Do not display regions with no sales records" is set, all dimension combination records with NULL or zero values ​​in the measure field will be automatically filtered out. For example, in the "2025 Sales Revenue by Region" query, if there is no sales data for the East China region in Q3, that record will be removed. This operation reduces interference from invalid information and improves the readability of the results.

[0146] Step S1072: If the display configuration task indicates that an empty member is displayed, the multidimensional query result corresponding to the multidimensional query request is obtained according to the data processing result.

[0147] When users require full-dimensional coverage (such as audit scenarios requiring the display of data from all regions), all empty member records are retained. For example, in "National Quarterly Sales Analysis," even if a region has no sales in a certain quarter, the "Region-Quarter" combination is still displayed with a sales amount of 0. This mode ensures data integrity and supports full-dimensional analysis needs.

[0148] This embodiment achieves visualized and precise control of multi-dimensional query results through a dynamic adaptation mechanism for display configuration tasks, significantly improving user experience and decision support value. In the "Do not display empty members" mode, null value records with no actual business significance are automatically removed, allowing the results to focus on high-value data points.

[0149] This embodiment solves the problem of rigid result display in traditional solutions by decoupling the "result generation - display configuration" process. Combined with the dynamic query optimization capabilities of the embedded database engine, empty member filtering is completed in sub-seconds, supporting real-time interactive analysis. The overall solution achieves end-to-end optimization from data computation to result presentation through flexible display configuration adaptation, making multidimensional data analysis both accurate and efficient, as well as intuitive and easy to understand, fully meeting the diverse needs of complex enterprise-level query scenarios.

[0150] In one embodiment, when the parsing result further includes a sorting task, step S107, which involves obtaining the multidimensional query result corresponding to the multidimensional query request based on the data processing result, includes:

[0151] Step S1073: Sort the data processing results according to the sorting field and sorting direction specified by the sorting task to obtain the multidimensional query result corresponding to the multidimensional query request.

[0152] The sorting task is the result sorting rule specified in the user query, which includes the sorting field (such as sales amount, date) and sorting direction (ascending / descending), such as "sort by sales amount in descending order" or "display by product name in ascending order".

[0153] This embodiment first analyzes the key parameters in the sorting task: the sorting field and the sorting direction. For example, when a user queries "Top 10 Sales Revenue of Each Product in 2025", the data processing results will be sorted in descending order by the "Sales Revenue" field. The sorting operation is implemented based on the vectorized execution engine of the embedded database engine, avoiding full table scans through columnar storage index optimization technology. For numeric fields (such as sales revenue), the numerical values ​​are directly compared; for text fields (such as product names), they are sorted in lexicographical order; for date fields, they are arranged in chronological order. If there are multiple sorting fields, such as "first sorted in ascending order by region, then in descending order by sales revenue", a multi-level sorting strategy is adopted, applying sorting rules at each level in sequence to ensure that the results meet the requirements of composite sorting. For example, in "National Sales Analysis", the data is first sorted alphabetically by province, and within the same province, it is displayed in descending order of sales revenue.

[0154] This embodiment significantly improves the readability of multidimensional query results and the efficiency of business analysis through a dynamic sorting mechanism. The sorting task presents high-value data intuitively, helping users quickly locate key business indicators. Combined with columnar storage and index optimization of the embedded database engine, the performance of sorting operations is significantly improved compared to traditional databases, supporting real-time interactive analysis. Through end-to-end optimization of "sorting task parsing - field type adaptation - distributed execution," the problems of low sorting efficiency and rigid null value handling in traditional solutions are solved. The overall solution supports both simple single-field sorting and complex composite sorting requirements, making multidimensional data analysis results more aligned with business decision-making logic and comprehensively enhancing enterprise data insight capabilities.

[0155] In this embodiment of the application, if the parsing result includes both a display configuration task and a sorting task, the processing corresponding to the display configuration task is executed first, and then the processing corresponding to the sorting task is executed.

[0156] Please refer to Figure 2 In one embodiment, step S103, which involves generating a data processing result based on the parsing result, further includes:

[0157] Step S301: When the parsing result includes a classification and summary task, a preset basic aggregation process is performed on each of the data wide tables in the target database to obtain each basic aggregated data.

[0158] First, perform basic aggregations (such as SUM and COUNT) on the matched wide table of data to generate preliminary aggregation results grouped by dimension fields. For example, summarize sales revenue by "Region-Product Category" to form basic aggregated data.

[0159] Step S302: Perform the processing corresponding to the subtotal task on each of the basic aggregated data to obtain subtotal data; use the subtotal data as the data processing result; wherein, the subtotal processing includes converting the total column into a string column and then performing subtotal, total and multi-dimensional combination summary operations.

[0160] Hierarchical summarization is performed on basic aggregated data: First, the total column (such as sales revenue) is converted to a string type to be compatible with non-numeric summaries. Then, subtotals (such as the sum of each product category within the region), totals (national sales revenue), and multi-dimensional combined summaries (such as the subdivided data of "East China + Electronic Products") are calculated in sequence. This operation is implemented through window functions and GROUPING SETS of the embedded database engine, supporting dynamic summarization of arbitrary dimension combinations.

[0161] Step S303: When the parsing result includes a multi-fact table association statistics task, a preset basic aggregation process is performed on each of the data wide tables in the target database to obtain each basic aggregation data, and at least one aggregation file is obtained based on each of the basic aggregation data.

[0162] Perform basic aggregations on the wide tables such as the sales fact table and the inventory fact table separately, generating independent aggregation files. For example, aggregate sales revenue in the sales table by "product ID", and aggregate inventory quantity in the inventory table by "product ID".

[0163] Step S304: Read each of the aggregate files through the embedded database engine, perform a full join operation on each of the aggregate files to obtain fully joined associated data; perform the processing corresponding to the multi-fact table association statistics task on the fully joined associated data to obtain multi-fact table association statistics; and use the multi-fact table association statistics as the data processing result.

[0164] After reading the aggregation file, the embedded database engine performs a full join operation to ensure that all product records are retained in the sales and inventory tables, filling in NULL values ​​if no match is found. Subsequently, it performs correlation statistics (such as calculating the "sales revenue / inventory" ratio) to generate comprehensive statistical results containing multi-source data. The full join is implemented using a hash join algorithm, maintaining millisecond-level response times even with millions of data points.

[0165] This embodiment achieves a breakthrough in both the depth and breadth of multidimensional data analysis through an extended mechanism of categorization and summarization combined with multi-fact table correlation statistics. The categorization and summarization task, through string conversion and dynamic grouping sets, supports flexible summarization of arbitrary dimension combinations. Combined with the vectorized execution engine of the embedded database engine, the efficiency of subtotal / total calculations is significantly improved, meeting the real-time generation requirements of complex reports. The multi-fact table correlation statistics task, through full-connection operations and collaboration with the embedded engine, solves the data loss and performance bottleneck problems of multi-table correlations in traditional solutions. For example, in the "product lifecycle analysis" scenario, sales, inventory, and cost fact tables can be correlated simultaneously to calculate the comprehensive indicator of "sales revenue - inventory turnover rate - profit margin," providing data support for product optimization. The full-connection null value filling mechanism ensures the integrity of the correlated data, avoiding analytical biases caused by missing data.

[0166] This embodiment achieves full-scenario coverage, from simple single-table aggregation to complex cross-table joins, through a three-stage processing approach of "basic aggregation - joins - comprehensive statistics." Combined with the columnar storage and star schema optimization of the embedded database engine, multi-table join query performance is improved by over 50%, supporting sub-second response times for large-scale data analysis needs. The overall solution ensures the accuracy of data calculations while enhancing analytical flexibility through dynamic aggregation and join mechanisms, enabling enterprises to quickly respond to complex business needs and achieve data-driven, precise decision-making.

[0167] The above embodiments are merely illustrative of several implementation methods of this application, and their descriptions are relatively specific and detailed, but they should not be construed as limiting the scope of the invention patent. It should be noted that those skilled in the art can make several modifications and improvements without departing from the concept of this application, and this application also intends to include these modifications and variations.

Claims

1. A method for multi-dimensional data query based on embedded engine and dynamic aggregation, characterized in that, The method comprises the following steps: acquiring a multi-dimensional query request input by a user; and parsing the multi-dimensional query request to obtain a parsing result; the parsing result at least comprises a dimension field and a metric field; acquiring at least one matched data wide table according to the dimension field and the metric field; wherein the data wide table is obtained according to fact data of at least one database and dimension description data corresponding to the fact data, and comprises a single table of the dimension field and the metric field; based on the parsing result, performing the following data processing result generation step: when the parsing result only comprises a basic aggregation task, performing processing corresponding to the basic aggregation task on the data wide table in a target database where the data wide table is located, to obtain basic aggregation data; and taking the basic aggregation data as a data processing result; when the parsing result comprises a complex calculation task, performing basic aggregation processing on the data wide table in the target database to obtain basic aggregation data; acquiring at least one aggregation file according to the basic aggregation data; reading each aggregation file through an embedded database engine, and performing processing operation corresponding to the complex calculation task on the basic aggregation data in each aggregation file to obtain complex calculation data; and taking the complex calculation data as a data processing result; wherein when the parsing result only comprises a complex calculation task, the basic aggregation processing is performed according to a preset rule; and when the parsing result comprises a basic aggregation task and a complex calculation task, the basic aggregation processing is performed according to the basic aggregation task; when the parsing result does not comprise a basic aggregation task and a complex calculation task, performing a preset summary processing on the data wide table in the target database to obtain summary data and taking the summary data as a data processing result; acquiring a multi-dimensional query result corresponding to the multi-dimensional query request according to the data processing result.

2. The multi-dimensional data query method based on an embedded engine and dynamic aggregation according to claim 1, wherein the complex calculation task comprises a derivative calculation task; the derivative calculation task comprises a time-related calculation task and a ranking calculation task; and the complex calculation data comprises time-related calculation data and ranking calculation data; when the complex calculation task comprises a time-related calculation task, the step of reading each aggregation file through an embedded database engine, and performing processing operation corresponding to the complex calculation task on the basic aggregation data in each aggregation file to obtain complex calculation data comprises: determining a time dimension field corresponding to the time-related calculation task and a derivative calculation rule; the derivative calculation rule comprises a basic time fast calculation rule and a complex time calculation rule; extracting basic aggregation data corresponding to the time dimension field from each aggregation file, and performing corresponding processing according to the derivative calculation rule to obtain time-related calculation data. When the complex computing task comprises a ranking computing task, the step of reading each of the aggregated files by the embedded database engine, performing a processing operation corresponding to the complex computing task on the basic aggregated data in each of the aggregated files, and obtaining complex computing data comprises: determining a sorting field and a sorting direction corresponding to the ranking computing task; partitioning the basic aggregated data in each of the aggregated files according to the dimension field, and sorting the metric field data in each partition according to the sorting field and the sorting direction, to obtain ranking computing data.

3. The multi-dimensional data query method based on an embedded engine and dynamic aggregation according to claim 1, characterized in that: the complex computing task comprises a list-type computing task; and the complex computing data comprises list-type computing data. When the complex computing task comprises a list-type computing task, the step of reading each of the aggregated files by the embedded database engine, performing a processing operation corresponding to the complex computing task on the basic aggregated data in each of the aggregated files, and obtaining complex computing data comprises: determining a computing type and a target metric field corresponding to the list-type computing task; the computing type comprises one or more of unique count computing or median computing; extracting the basic aggregated data corresponding to the target metric field from each of the aggregated files, and performing data deduplication or sorting preprocessing on the basic aggregated data; wherein the unique count computing performs deduplication preprocessing, and the median computing performs sorting preprocessing; if the computing type is unique count computing, counting the number of records of the deduplicated data after preprocessing, to obtain unique count computing data; if the computing type is median computing, determining a median position according to the number of records of the sorted basic aggregated data, and extracting a value corresponding to the median position as median computing data; obtaining list-type computing data according to the unique count computing data or the median computing data.

4. The multi-dimensional data query method based on an embedded engine and dynamic aggregation according to claim 2, characterized in that: the derivative computing task comprises a composite computing task and a proportion computing task; and the complex computing data comprises composite computing data and proportion computing data. When the complex computing task comprises a composite computing task, the step of reading each of the aggregated files by the embedded database engine, performing a processing operation corresponding to the complex computing task on the basic aggregated data in each of the aggregated files, and obtaining complex computing data comprises: parsing the composite computing task into a plurality of sub-computing tasks, and determining an execution order of each of the sub-computing tasks; sequentially performing each of the sub-computing tasks on the basic aggregated data in each of the aggregated files in the execution order, taking the output result of a previous sub-computing task as input data of a next sub-computing task, until the composite computing data is obtained. When the complex computing task comprises a proportion computing task, the step of reading each of the aggregation files by the embedded database engine, performing a processing operation corresponding to the complex computing task on the basic aggregation data in each of the aggregation files, and obtaining complex computing data comprises: determining a numerator measurement field and a denominator measurement field of the proportion computing task; extracting a total value corresponding to the numerator measurement field and a total value corresponding to the denominator measurement field from the basic aggregation data in each of the aggregation files; if the total value of the denominator measurement field is zero, determining zero as the proportion computing data; if the total value of the denominator measurement field is not zero, obtaining the proportion computing data according to a percentage of the numerator total value and the denominator total value.

5. The multi-dimensional data query method based on an embedded engine and dynamic aggregation according to claim 2, wherein the derived computing task further comprises a computing measurement task, and the complex computing data comprises computing measurement data; and when the complex computing task comprises the computing measurement task, the step of reading each of the aggregation files by the embedded database engine, performing a processing operation corresponding to the complex computing task on the basic aggregation data in each of the aggregation files, and obtaining complex computing data comprises: identifying at least one reference measurement on which the computing measurement task depends; extracting basic measurement data corresponding to each of the reference measurements from each of the aggregation files, and performing preprocessing on the basic measurement data to obtain preprocessed reference measurement data; the preprocessing comprises data cleaning and format standardization; when the reference measurement does not depend on a secondary reference measurement, performing an operation corresponding to the computing measurement task according to a pre-designed computing measurement formula based on the preprocessed reference measurement data and the basic measurement data in each of the aggregation files to obtain a computing measurement result; when the reference measurement depends on a secondary reference measurement, extracting basic measurement data corresponding to each of the secondary reference measurements from each of the aggregation files and performing preprocessing on the basic measurement data to obtain preprocessed secondary reference measurement data; performing an operation corresponding to the computing measurement task according to a pre-designed computing measurement formula based on the preprocessed reference measurement data, the secondary reference measurement data, and the basic measurement data in the aggregation files to obtain a computing measurement result.

6. The multi-dimensional data query method based on an embedded engine and dynamic aggregation according to claim 1, wherein when the analysis result further comprises a filtering condition, the step of performing basic aggregation processing on the data wide table in the target database to obtain basic aggregation data comprises: obtaining a dimension filtering condition and a measurement filtering condition in the filtering condition; wherein the dimension filtering condition is used for filtering a dimension field in the data wide table, and the measurement filtering condition is used for filtering a measurement field in the data wide table; filtering a data subset meeting a dimension constraint from the data wide table according to the dimension filtering condition; performing a preset basic aggregation processing on the data subset to obtain a preliminary aggregation result; and ​ ​ ​ Filtering data meeting metric constraints from the preliminary aggregation result according to the metric filtering condition to generate basic aggregation data.

7. The multi-dimensional data query method based on embedded engine and dynamic aggregation according to claim 1, wherein, When the analysis result further comprises a list query scene identifier, before the step of performing basic aggregation processing on the data wide table in the target database to obtain basic aggregation data, comprising: If any data wide table contains all fields of another data wide table, integrating the data wide table and another data wide table data through joint all operation, and filling null values for fields not contained in another data wide table to obtain a joint data wide table; If any data wide table does not contain all fields of another data wide table, integrating the data wide table and another data wide table data through joint all operation, and filling null values for fields not contained in the data wide table and another data wide table respectively to obtain a joint data wide table; Performing a preset basic aggregation processing on the joint data wide table to obtain basic aggregation data.

8. The multi-dimensional data query method based on embedded engine and dynamic aggregation according to claim 1, wherein, The data processing result generation step based on the analysis result further comprises: When the analysis result comprises a classification summary task, performing a preset basic aggregation processing on each data wide table in the target database to obtain each basic aggregation data; performing a processing corresponding to the classification summary task on each basic aggregation data to obtain classification summary data; taking the classification summary data as the data processing result; wherein, the classification summary processing comprises converting a subtotal column to a string column, and then performing subtotal, total, and multi-dimensional combined summary operations; When the analysis result comprises a multi-fact table association statistical task, performing a preset basic aggregation processing on each data wide table in the target database to obtain each basic aggregation data, and obtaining at least one aggregation file according to each basic aggregation data; reading each aggregation file through an embedded database engine, performing a full connection operation on each aggregation file to obtain full connection association data; performing a processing corresponding to the multi-fact table association statistical task on the full connection association data to obtain multi-fact table association statistical data; taking the multi-fact table association statistical data as the data processing result.

9. The multi-dimensional data query method based on embedded engine and dynamic aggregation according to claim 1, wherein, When the analysis result further comprises a display configuration task, the step of obtaining a multi-dimensional query result corresponding to the multi-dimensional query request according to the data processing result comprises: If the display configuration task indicates not to display null members, filtering null member data in the data processing result; obtaining a multi-dimensional query result corresponding to the multi-dimensional query request according to the filtered data processing result; If the display configuration task indicates to display null members, obtaining a multi-dimensional query result corresponding to the multi-dimensional query request according to the data processing result. 10.The method of claim 1, wherein the method further comprises: when the parsing result further comprises a sorting task, the obtaining the multi-dimensional query result corresponding to the multi-dimensional query request based on the data processing result comprises: sorting the data processing result according to a sorting field and a sorting direction specified by the sorting task to obtain the multi-dimensional query result corresponding to the multi-dimensional query request.

Images