A method and apparatus for cross-database multidimensional data hierarchical aggregation

Abstract

This invention discloses a method and apparatus for cross-database multi-dimensional data hierarchical aggregation. The method includes: S01, initializing parameters and defining query parameters; S02, generating an SQL query list, constructing SQL query statements for statistical data, and constructing SQL query statements for total statistical records; S03, executing SQL queries in parallel, establishing a link with the database based on the data source information in the query parameters, and starting an independent thread for each statement in the SQL query statement set constructed in the previous step to execute the query one-to-one with the data source; S04, data weaving, merging the multiple data sets generated in the previous step, placing records with the same number of statistical dimensions adjacently, and arranging them according to the dimension hierarchy from high to low, forming the same effect as a rollup query. This invention, a method and apparatus for cross-database multi-dimensional data hierarchical aggregation, can achieve cross-database platform differences, efficiently execute advanced aggregation operations, and ensure accurate pagination queries.

Description

Technical Field

[0001] This invention relates to the field of data processing, and in particular to a method and apparatus for hierarchical aggregation of multidimensional data across databases. Background Technology

[0002] In contemporary data-intensive applications, advanced data analytics capabilities have become crucial for supporting business decisions and gaining insights into market trends. Among these, multi-dimensional subtotals and statistical analyses require complex data aggregation operations, such as using advanced aggregation functions like ROLLUP and CUBE, which are essential for generating multi-dimensional summary data. However, these capabilities have exposed a series of technical and implementation challenges in practical applications, particularly when it comes to consistency across database platforms, performance efficiency, and the accuracy of paginated queries.

[0003] First, cross-platform differences and compatibility challenges

[0004] While the SQL standard provides some specification guidance for advanced aggregate functions, significant differences exist between different database management systems (DBMS) at the implementation level. For example, mainstream databases such as Oracle, SQL Server, MySQL, and PostgreSQL each have their own strengths and weaknesses in the specific syntax, functional details, and performance optimization strategies for supporting ROLLUP and CUBE. This inconsistency imposes an additional burden on developers, requiring them to write and debug specific query statements for different databases, reducing code portability and maintainability.

[0005] Second, performance challenges and resource consumption.

[0006] While full-dimensional aggregation features like CUBE can provide detailed summary views, their computational cost is high. As the number of dimensions involved in aggregation increases, the resulting set expands exponentially, consuming significant amounts of memory and CPU resources and potentially leading to substantial increases in query response time, impacting system real-time performance and user experience. This performance bottleneck is particularly severe in big data applications, becoming a major constraint on analytical efficiency.

[0007] Third, the complexity of paginated queries

[0008] Traditional data aggregation query designs often overlook the impact of aggregation results on pagination logic when implementing pagination functionality. Directly paginating result sets containing summary rows not only leads to inaccurate record counts but can also cause logical inconsistencies in page content, affecting users' ability to browse and understand the data. Especially when displaying detailed data and summary statistics simultaneously, precisely controlling pagination boundaries to ensure the integrity and continuity of data on each page becomes a significant technical challenge. Summary of the Invention

[0009] To address the problems existing in the prior art, this invention provides a method and apparatus for cross-database multi-dimensional data hierarchical aggregation, which can not only overcome differences in database platforms, but also efficiently perform advanced aggregation operations, while ensuring accurate pagination queries.

[0010] To achieve the above objectives, the present invention adopts the following technical solution:

[0011] In one embodiment of the present invention, a method for cross-database multidimensional data hierarchical aggregation is proposed, the method comprising:

[0012] S01. Initialize parameters and define query parameters, including: data source information, query table name, statistical dimension information, statistical indicator information, filtering information, and pagination information;

[0013] Furthermore, the query parameters defined in S01 specifically include:

[0014] Data source information, source: database connection information;

[0015] Query table name, where table is the data source table name;

[0016] Statistical dimension information, dims: defines an array of dimensions used for grouping;

[0017] Statistical indicator information, metrics: Defines a two-dimensional array of aggregated indicator information, which requires specifying the aggregation function, corresponding statistical column name, and alias;

[0018] Filter information: filter is the condition for filtering user data, i.e., user-defined input;

[0019] Pagination information, page: includes the current page and the number of records displayed per page.

[0020] S02. Generate a list of SQL queries, construct SQL query statements for statistical data, and construct SQL query statements for total statistical records.

[0021] Further, S02 includes:

[0022] S021. Construct the SQL query statement for statistical data. In each loop, for the given dimension array dims, decrease the length of the dimension array from the end to the beginning, from the highest dimension combination to the lowest dimension, and gradually reduce the length of the dimension array to generate groupings of different granularities until there are no grouping dimensions, that is, from the most complete combination to the smallest subset, which is the dimensionality reduction process.

[0023] The dims refers to statistical dimension information.

[0024] Further, S021 includes:

[0025] S0211. Create a basic SQL structure, including a fixed structure: SELECT ${dims}, ${metrics} FROM ${table} group by ${dims}, where ${dims} represents the decreasing statistical dimension, ${metrics} represents the statistical metric in the parameter, and ${table} represents the query table;

[0026] S0212. Generate SQL queries for different combinations of dimensions, dynamically add a counter column _dim_cnt to record the number of dimensions in the current query. _dim_cnt is added to the query as a column, and its value is equal to the number of dimensions used in the current query.

[0027] S0213. If filter information is set in the query parameters, the query conditions need to be concatenated.

[0028] S0214. If pagination information (page) is set in the query parameters, pagination restrictions need to be added.

[0029] S022. Construct a total record SQL query statement for statistics. Take the first SQL statement above that contains grouping of all dimensions, remove the pagination condition, and use it as a subquery to count the number of records. The structure is as follows: select count(*)as total from(${the first SQL statement without pagination condition})t;

[0030] S023. Generate and construct the SQL query statement for the number of statistical dimensions + 1 statistical data and the SQL statement for the total number of records.

[0031] S03. Parallel execution of SQL queries: Establish a connection with the database based on the data source information in the query parameters, and start an independent thread for each statement in the SQL query statement set constructed in the previous step to execute the query in the data source one-to-one.

[0032] This method can effectively utilize multi-core CPU resources to speed up data processing, especially when dealing with large amounts of data.

[0033] Furthermore, in S03, each SQL query will retrieve a data set. The last record is excluded and used as the total number of records for pagination, which is used by the user to calculate pagination. The remaining data set is a statistical data set. These data sets are then woven together and merged into a single set.

[0034] S04. Data weaving. For the multiple data sets generated in the previous step, which are disordered and not intuitive in terms of statistical grouping dimensions, data merging is required to ensure that records with the same number of statistical dimensions are adjacent and arranged from the highest to the lowest dimension level, achieving the same effect as a rollup query.

[0035] Furthermore, the ROLLUP function in SQL is mainly used for data grouping and summarization, which can be interpreted in Chinese as "rolling up" or "hierarchical aggregation". ROLLUP not only performs basic grouping calculations on data, like the GROUP BY operation, but also automatically calculates the summary results of all sub-groupings and the entire data set.

[0036] When using ROLLUP in the GROUP BY clause, ROLLUP will generate all summaries from the finest granularity to the highest level in sequence.

[0037] Furthermore, the S04 includes:

[0038] S041. All query results are merged and appended to a list one by one according to the order of the SQL query statement. This set is the statistical data set.

[0039] S042. Obtain the total number of records for pagination. The last record in the data set is used as the total number of records for pagination total, which is used for users to calculate pagination. This record is excluded from the set and does not participate in subsequent sorting.

[0040] S043. Record the number of records in the first set, that is, the number of records at the finest granularity begin, as the starting position for sorting.

[0041] S044. Traverse the data. The data list is traversed through two nested loops. The outer loop starts from a specific starting position begin and obtains each data item as T. The inner loop then compares adjacent items in the data list pairwise. For each pair of adjacent data items A and B, the dimension values of A, B, and T items are then converted into a string form according to the value of the dimension count (_dim_cnt) of T, obtaining strings a, b, and t. At the same time, the dimension count (_dim_cnt) values cnt_a and cnt_t of A and T items are obtained.

[0042] S045. Sort according to the values obtained in the previous step. Sorting logic: If the dimension values of item A and item T are the same under the dimension count of T (i.e., a equals t), and the dimension value of item B and item T is different (i.e., b does not equal t), and the dimension count of T is less than the dimension count of A (i.e., cnt_t < cnt_a), then the position of item T in the list will be adjusted after item A. If there is no a equal to t, then item T is deleted from the set.

[0043] In one embodiment of the present invention, a cross-database multidimensional data hierarchy aggregation device is also proposed, the device comprising:

[0044] The initialization parameter module defines query parameters, including: data source information, query table name, statistical dimension information, statistical indicator information, filtering information, and pagination information.

[0045] The module generates an SQL query list, constructs SQL query statements for statistical data, and constructs SQL query statements for total statistical records.

[0046] The parallel SQL query execution module establishes a connection with the database based on the data source information in the query parameters. It starts an independent thread for each statement in the SQL query statement set constructed in the previous step and executes the query in the data source one-to-one.

[0047] The data weaving module merges multiple datasets generated by the parallel SQL query execution module, placing records with the same number of statistical dimensions adjacent to each other and arranging them from high to low according to the dimension hierarchy, achieving the same effect as a rollup query.

[0048] In one embodiment of the present invention, a computer device is also proposed, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the aforementioned method for cross-database multidimensional data hierarchical aggregation.

[0049] In one embodiment of the present invention, a computer-readable storage medium is also provided, which stores a computer program that performs a method for summarizing multidimensional data levels across databases.

[0050] Beneficial effects:

[0051] This invention provides a method and apparatus for cross-database multidimensional data hierarchical aggregation. By solving problems such as cross-platform compatibility, performance efficiency, and pagination query accuracy, it provides an efficient, universal, and easy-to-use method and apparatus for multidimensional data analysis. It has significant benefits in improving the analytical capabilities of data-intensive applications, reducing development costs, and optimizing user experience.

[0052] Enhanced cross-platform compatibility: By shielding the underlying SQL query details and adopting a unified query parameter interface, users do not need to worry about differences between different database management systems (DBMS), significantly improving code portability and maintainability. This reduces the workload of rewriting or adjusting query logic due to different database platforms, and improves development efficiency.

[0053] Performance Optimization: This invention employs a method of dynamically generating SQL query statements, enabling the construction of queries layer by layer based on statistical dimensions, thus avoiding the resource consumption issues associated with using full-dimensional aggregation such as CUBE. By executing multiple targeted SQL queries in parallel and utilizing multi-threading technology to accelerate the data retrieval process, it effectively alleviates performance bottlenecks under large data volumes, improving system real-time performance and user experience.

[0054] Improved pagination accuracy: This feature resolves the issues of logical inconsistencies and inaccurate record counts in traditional pagination queries when dealing with aggregated data. By constructing a dedicated SQL query to count the total number of records, the accurate execution of pagination logic is ensured, guaranteeing the integrity and consistency of data on each page and enhancing the user experience of browsing data analysis results.

[0055] Improved efficiency and convenience in data analysis: Data weaving algorithms automatically integrate summarized data from different dimensions, allowing users to obtain all summary information, from detailed to general, with a single query, greatly simplifying report creation and complex data analysis processes. This mechanism reduces the complexity of data analysis, enabling non-professional users to quickly generate the required reports, improving the speed and quality of decision support. Attached Figure Description

[0056] Figure 1 This is a schematic diagram of the cross-database multi-dimensional data hierarchical aggregation method of the present invention;

[0057] Figure 2 This is a schematic diagram of the architecture of the cross-database multi-dimensional data hierarchical aggregation method of the present invention;

[0058] Figure 3 This is a schematic diagram of the structure of the cross-database multi-dimensional data hierarchical aggregation device of the present invention;

[0059] Figure 4 This is a schematic diagram of a computer device structure according to an embodiment of the present invention. Detailed Implementation

[0060] The principles and spirit of the present invention will now be described with reference to several exemplary embodiments. It should be understood that these embodiments are provided merely to enable those skilled in the art to better understand and implement the present invention, and are not intended to limit the scope of the present invention in any way. Rather, these embodiments are provided to make this disclosure more thorough and complete, and to fully convey the scope of this disclosure to those skilled in the art.

[0061] Those skilled in the art will recognize that embodiments of the present invention can be implemented as a system, apparatus, device, method, or computer program product. Therefore, this disclosure can be specifically implemented in the following forms: entirely hardware, entirely software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.

[0062] According to an embodiment of the present invention, a method and apparatus for cross-database multi-dimensional data hierarchical aggregation is proposed, which selects a gray-scale release service, configures a gray-scale release task, and integrates multiple gray-scale release strategies to improve the controllability and compatibility of gray-scale release.

[0063] The principles and spirit of the present invention will be explained in detail below with reference to several representative embodiments.

[0064] like Figure 1 , Figure 2 As shown, the present invention relates to a method for hierarchical aggregation of multidimensional data across databases, the method comprising:

[0065] S01. Initialize parameters and define query parameters, including: data source information, query table name, statistical dimension information, statistical indicator information, filtering information, and pagination information;

[0066] The query parameters defined in S01 specifically include:

[0067] Data source information, source: database connection information;

[0068] Query table name, where table is the data source table name;

[0069] Statistical dimension information, dims: defines an array of dimensions used for grouping;

[0070] Statistical indicator information, metrics: Defines a two-dimensional array of aggregated indicator information, which requires specifying the aggregation function, corresponding statistical column name, and alias;

[0071] Filter information: filter is the condition for filtering user data, i.e., user-defined input;

[0072] Pagination information, page: includes the current page and the number of records displayed per page.

[0073] S02. Generate a list of SQL queries, construct SQL query statements for statistical data, and construct SQL query statements for total statistical records.

[0074] The S02 includes:

[0075] S021. Construct the SQL query statement for statistical data. In each loop, for the given dimension array dims, decrease the length of the dimension array from the end to the beginning, from the highest dimension combination to the lowest dimension, and gradually reduce the length of the dimension array to generate groupings of different granularities until there are no grouping dimensions, that is, from the most complete combination to the smallest subset, which is the dimensionality reduction process.

[0076] The dims refers to statistical dimension information.

[0077] S021 includes:

[0078] S0211. Create a basic SQL structure, including a fixed structure: SELECT ${dims}, ${metrics} FROM ${table} group by ${dims}, where ${dims} represents the decreasing statistical dimension, ${metrics} represents the statistical metric in the parameter, and ${table} represents the query table;

[0079] S0212. Generate SQL queries for different combinations of dimensions, dynamically add a counter column _dim_cnt to record the number of dimensions in the current query. _dim_cnt is added to the query as a column, and its value is equal to the number of dimensions used in the current query.

[0080] S0213. If filter information is set in the query parameters, the query conditions need to be concatenated.

[0081] S0214. If pagination information (page) is set in the query parameters, pagination restrictions need to be added.

[0082] S022. Construct a total record SQL query statement for statistics. Take the first SQL statement above that contains grouping of all dimensions, remove the pagination condition, and use it as a subquery to count the number of records. The structure is as follows: select count(*)as total from(${the first SQL statement without pagination condition})t;

[0083] S023. Generate and construct the SQL query statement for the number of statistical dimensions + 1 statistical data and the SQL statement for the total number of records.

[0084] S03. Parallel execution of SQL queries: Establish a connection with the database based on the data source information in the query parameters, and start an independent thread for each statement in the SQL query statement set constructed in the previous step to execute the query in the data source one-to-one.

[0085] This method can effectively utilize multi-core CPU resources to speed up data processing, especially when dealing with large amounts of data.

[0086] In S03, each SQL query will retrieve a data set. The last record is excluded and used as the total number of records for pagination, which is used by the user to calculate pagination. The remaining data set is a statistical data set. These data sets are then woven together and merged into a single set.

[0087] S04. Data weaving: The multiple data sets generated in the previous step are unordered in terms of statistical grouping dimensions, which is not intuitive enough. Data merging is required to ensure that records with the same number of statistical dimensions are adjacent and arranged from high to low according to the dimension hierarchy, forming the same effect as a rollup query.

[0088] The ROLLUP function in SQL is mainly used for data grouping and summarizing, which can be interpreted as "rolling summary" or "hierarchical aggregation". ROLLUP not only performs basic grouping calculations on data, similar to the GROUP BY operation, but can also automatically calculate the summary results of all subgroups and the entire dataset.

[0089] When using ROLLUP in the GROUP BY clause, ROLLUP generates all summaries sequentially from the finest granularity to the highest level.

[0090] S04 includes:

[0091] S041. All query results are merged and appended to a list one by one according to the order of the SQL query statements. This set is a statistical data set.

[0092] S042. Obtain the total number of records for each page. Take the last record from the dataset as the total number of records for each page, which is used by the user to calculate the pagination. Exclude this record from the set and do not participate in subsequent sorting.

[0093] S043. Record the number of records in the first set, i.e., the number of records in the finest granular data begin, as the starting position for sorting;

[0094] S044. Traverse the data: Use two nested loops to traverse the data list. The outer loop starts from a specific beginning position (begin) and retrieves each data item as T. The inner loop compares adjacent items in the data list one by one. For each pair of adjacent data items A and B, the dimension values ​​of items A, B, and T are converted into strings a, b, and t based on the dimension count (_dim_cnt) of T. At the same time, the dimension count (_dim_cnt) values ​​cnt_a and cnt_t of items A and T are retrieved.

[0095] S045. Sort according to the value obtained in the previous step. Sorting logic: If the dimension values of item A and item T are the same under the T - dimension count (i.e., a equals t), and the dimension value of item B and item T is different (i.e., b does not equal t), and at the same time the dimension count of T is less than the dimension count of A (i.e., cnt_t < cnt_a), then the position of item T in the list will be adjusted after item A. If there is no a equal to t, then item T will be deleted from the set.

[0096] It should be noted that although the operations of the method of the present invention are described in a specific order in the above embodiments and accompanying drawings, this does not require or imply that these operations must be performed in that specific order, or that all the operations shown must be performed to achieve the desired result. Additionally or alternatively, some steps may be omitted, multiple steps may be combined into one step for execution, and / or one step may be decomposed into multiple steps for execution.

[0097] In order to provide a clearer explanation of the above - mentioned cross - database multi - dimensional data hierarchical aggregation method, the following will be described in conjunction with specific embodiments. However, it should be noted that this embodiment is only for better explaining the present invention and does not constitute an improper limitation of the present invention.

[0098] S01. Initialize parameters and define query parameters, including: data source information, query table name, statistical dimension information, statistical index information, filtering information, paging information;

[0099] The query parameters defined in S01 specifically include:

[0100] Data source information, source: Database connection information, such as database IP, port, username, password, etc.;

[0101] Query table name, table: Data source table name, such as "orders";

[0102] Statistical dimension information, dims: Defines an array of dimensions used for grouping. For example, when statistically analyzing product sales information, statistical parameters are defined according to three dimensions: category, brand, and product: ["category", "brand", "goods"]; <a

[0103] Statistical index information, metrics: Defines a two - dimensional array of index aggregation information, which requires specifying the aggregation function, corresponding statistical column name, and alias. For example, for statistical total sales and statistical average sales: "sum(amount) as amount_sum, avg(amount) as amount_avg";

[0104] Filter information: filter: is the condition for filtering user data, that is, user-defined input, such as only counting categories "mobile phone" and "computer": "category in('mobile phone','computer')";

[0105] Pagination information, page: includes the current page and the number of records displayed per page, such as the first page, with 10 records per page: [1, 10].

[0106] S02. Generate a list of SQL queries, construct SQL query statements for statistical data, and construct SQL query statements for total statistical records.

[0107] The S02 includes:

[0108] S021. Construct the SQL query statement for statistical data. In each loop, for the given dimension array dims, decrease the length of the dimension array from the end to the beginning, from the highest dimension combination to the lowest dimension, and gradually reduce the length of the dimension array to generate groupings of different granularities until there are no grouping dimensions, that is, from the most complete combination to the smallest subset, which is the dimensionality reduction process.

[0109] The dims refers to statistical dimension information.

[0110] S021 includes:

[0111] S0211. Create a basic SQL structure, including a fixed structure: SELECT ${dims}, ${metrics} FROM ${table} group by ${dims}, where ${dims} represents the decreasing statistical dimension, ${metrics} represents the statistical metric in the parameter, and ${table} represents the query table;

[0112] S0212. Generate SQL queries for different dimension combinations, dynamically add a counter column _dim_cnt to record the number of dimensions in the current query. Its purpose is to record the number of dimensions in each grouping result. _dim_cnt is added to the query as a column, and its value is equal to the number of dimensions used in the current query.

[0113] For example, if the query is grouped by both "category" and "brand", the value of _dim_cnt is 2; if it is grouped only by "category", the value is 1. This is used for subsequent data weaving.

[0114] S0213. If filter information is set in the query parameters, the query conditions need to be concatenated, such as wherecategory in('mobile phone','computer').

[0115] S0214. If pagination information (page) is set in the query parameters, pagination restrictions need to be added, such as `LIMIT 0,10` in MySQL.

[0116] S022. Construct a total record SQL query statement for statistics. Take the first SQL statement above that contains grouping of all dimensions, remove the pagination condition, and use it as a subquery to count the number of records. The structure is as follows: select count(*)as total from(${the first SQL statement without pagination condition})t;

[0117] S023. Generate and construct the SQL query statement for the number of statistical dimensions + 1 statistical data and the SQL statement for the total number of records.

[0118] like:

[0119] select 3as_dim_cnt,category,brand,goods,sum(amount)as amount_sum fromorders where category in('mobile phone','computer')group by category,brand,goods limit 0,10

[0120] select 2as_dim_cnt,category,brand,sum(amount)as amount_sum fromorders where category in('mobile phone','computer')group by category,brand limit 0,10

[0121] select 1as_dim_cnt,category,sum(amount)as amount_sum from orderswhere category in('mobile phone','computer')group by category limit 0,10

[0122] SELECT 0 AS _dim_cnt, SUM(amount) AS amount_sum FROM orders WHERE category IN ('Mobile', 'Computer') LIMIT 0, 10

[0123] select count(*)as total from(select 3as_dim_cnt,category,brand,goods,sum(amount)as amount_sum from orders where category in('mobile phone','computer')group bycategory,brand,goods)t

[0124] S03. Parallel execution of SQL queries: Establish a connection with the database based on the data source information in the query parameters, and start an independent thread for each statement in the SQL query statement set constructed in the previous step to execute the query in the data source one-to-one.

[0125] This method can effectively utilize multi-core CPU resources to speed up data processing, especially when dealing with large amounts of data.

[0126] In S03, each SQL query will retrieve a data set. The last record is excluded and used as the total number of records for pagination, which is used by the user to calculate pagination. The remaining data set is a statistical data set. These data sets are then woven together and merged into a single set.

[0127] S04. Data weaving: The multiple data sets generated in the previous step are unordered in terms of statistical grouping dimensions, which is not intuitive enough. Data merging is required to ensure that records with the same number of statistical dimensions are adjacent and arranged from high to low according to the dimension hierarchy, forming the same effect as a rollup query.

[0128] The ROLLUP function in SQL is mainly used for data grouping and summarizing, which can be interpreted as "rolling summary" or "hierarchical aggregation". ROLLUP not only performs basic grouping calculations on data, similar to the GROUP BY operation, but can also automatically calculate the summary results of all subgroups and the entire dataset.

[0129] When using ROLLUP in the GROUP BY clause, ROLLUP generates all summaries sequentially from the finest granularity to the highest level.

[0130] S04 includes:

[0131] S041. All query results are merged and appended to a list one by one according to the order of the SQL query statements. This set is a statistical data set.

[0132] Examples of aggregated data are as follows:

[0133] {_dim_cnt = 3, category = mobile phone, brand = Huawei, goods = P70, amount_sum = 7999.0}

[0134] {_dim_cnt = 3, category = mobile phone, brand = Xiaomi, goods = Xiaomi 13, amount_sum = 2999.0}

[0135] {_dim_cnt = 3, category = mobile phone, brand = Xiaomi, goods = Xiaomi 14, amount_sum = 3999.0}

[0136] {_dim_cnt = 3, category = computer, brand = ASUS, goods = M8000, amount_sum = 8700.0}

[0137] {_dim_cnt = 3, category = computer, brand = ASUS, goods = ROG, amount_sum = 6700.0}

[0138] {_dim_cnt = 3, category = computer, brand = Lenovo, goods = ThinkPad, amount_sum = 5888.0}

[0139] {_dim_cnt = 2, category = mobile phone, brand = Huawei, amount_sum = 7999.0}

[0140] {_dim_cnt = 2, category = mobile phone, brand = Xiaomi, amount_sum = 6998.0}

[0141] {_dim_cnt = 2, category = computer, brand = ASUS, amount_sum = 15400.0}

[0142] {_dim_cnt = 2, category = computer, brand = Lenovo, amount_sum = 5888.0}

[0143] {_dim_cnt = 1, category = mobile phone, amount_sum = 14997.0}

[0144] {_dim_cnt = 1, category = computer, amount_sum = 21288.0}

[0145] {_dim_cnt = 0, amount_sum = 36285.0}

[0146] {total = 32355}

[0147] S042. Obtain the total number of records for pagination, and obtain the last record from the dataset as the total number of records for pagination total, which is used for the user to calculate pagination. Exclude this record from the set and do not participate in subsequent sorting;

[0148] S043. Record the number of records in the first set, that is, the number of records with the finest granularity begin, as the starting position for sorting;

[0149] S044. Traverse the data. Traverse the data list through two nested loops. The outer loop starts from a specific starting position begin and obtains each data item as T. The inner loop then compares adjacent items in the data list pairwise. For each pair of adjacent data items A and B, then the dimensional values of items A, B, and T will be converted into a string form according to the dimensional count (_dim_cnt) value of T, obtaining strings a, b, and t. At the same time, obtain the dimensional count (_dim_cnt) values cnt_a and cnt_t of items A and T;

[0150] For example:

[0151] T = {_dim_cnt = 2, category = mobile phone, brand = Huawei, amount_sum = 7999.0}

[0152] A = {_dim_cnt = 3, category = mobile phone, brand = Huawei, goods = P70, amount_sum = 7999.0}

[0153] B = {_dim_cnt = 3, category = mobile phone, brand = Xiaomi, goods = Xiaomi 13, amount_sum = 2999.0}

[0154] Then a = "mobile phone, Huawei", b = "mobile phone, Xiaomi", t = "mobile phone, Huawei", cnt_a = 3, cnt_t = 2

[0155] S045. Sort according to the values obtained in the previous step. Sorting logic: If the dimensional values of item A and item T are the same under the dimensional count of T (that is, a is equal to t), and the dimensional value of item B and item T is different (that is, b is not equal to t), and the dimensional count of T is less than the dimensional count of A (that is, cnt_t < cnt_a), then the position of item T in the list will be adjusted to after item A. If there is no a equal to t, then delete item T from the set.

[0156] Such adjustments are made to ensure that records with the same dimension count are arranged adjacent to each other while maintaining the order of dimensions from high to low. This process continues until the entire data list has been traversed and adjusted according to the rules. Finally, the output result list will show a clear dimension hierarchy where, for each dimension count value, all related records are grouped together and follow the natural hierarchy of the dimensions.

[0157] Sorted data, for example:

[0158] {_dim_cnt = 3, category = mobile phone, brand = Huawei, goods = P70, amount_sum = 7999.0}

[0159] {_dim_cnt = 2, category = mobile phone, brand = Huawei, amount_sum = 7999.0}

[0160] {_dim_cnt = 3, category = mobile phone, brand = Xiaomi, goods = Xiaomi 13, amount_sum = 2999.0}

[0161] {_dim_cnt = 3, category = mobile phone, brand = Xiaomi, goods = Xiaomi 14, amount_sum = 3999.0}

[0162] {_dim_cnt = 2, category = mobile phone, brand = Xiaomi, amount_sum = 6998.0}

[0163] {_dim_cnt = 1, category = mobile phone, amount_sum = 14997.0}

[0164] {_dim_cnt = 3, category = computer, brand = ASUS, goods = M8000, amount_sum = 8700.0}

[0165] {_dim_cnt = 3, category = computer, brand = ASUS, goods = ROG, amount_sum = 6700.0}

[0166] {_dim_cnt = 2, category = computer, brand = ASUS, amount_sum = 15400.0}

[0167] {_dim_cnt = 3, category = computer, brand = Lenovo, goods = ThinkPad, amount_sum = 5888.0}

[0168] {_dim_cnt=2,category=Computer,brand=Lenovo,amount_sum=5888.0}

[0169] {_dim_cnt=1,category=computer,amount_sum=21288.0}

[0170] {_dim_cnt=0,amount_sum=36285.0}

[0171] This method can obtain the total record data of the original statistical data for pagination calculation. At the same time, the original data is reorganized, making it easier for analysts to understand and interpret the summary data under different dimensions. Especially when creating reports or conducting data analysis, it can intuitively see the summary situation at each dimension level.

[0172] Based on the same inventive concept, this invention also proposes a cross-database multi-dimensional data hierarchy aggregation device. The implementation of this device can refer to the implementation of the method described above, and repeated details will not be repeated. The term "module" used below can refer to a combination of software and / or hardware that implements a predetermined function. Although the device described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.

[0173] Figure 3 This is a schematic diagram of the cross-database multi-dimensional data hierarchy aggregation device of the present invention. (See diagram below.) Figure 3 As shown, the device includes:

[0174] Initialize parameter module 110 to define query parameters, including: data source information, query table name, statistical dimension information, statistical indicator information, filtering information, and pagination information;

[0175] The SQL query list generation module 120 constructs SQL query statements for statistical data and SQL query statements for total statistical records.

[0176] The parallel execution SQL query module 130 establishes a connection with the database based on the data source information in the query parameters, and starts an independent thread for each statement in the SQL query statement set constructed in the previous step, executing the query one-to-one with the data source.

[0177] The data weaving module 140 merges multiple data sets generated by the parallel SQL query execution module, placing records with the same number of statistical dimensions adjacent to each other and arranging them from high to low according to the dimension hierarchy, forming the same effect as the rollup query.

[0178] It should be noted that although several modules of the cross-database multidimensional data hierarchy aggregation device are mentioned in the detailed description above, this division is merely exemplary and not mandatory. In fact, according to embodiments of the present invention, the features and functions of two or more modules described above can be embodied in one module. Conversely, the features and functions of one module described above can be further divided and embodied by multiple modules.

[0179] Based on the aforementioned inventive concept, such as Figure 4 As shown, the present invention also proposes a computer device 200, including a memory 210, a processor 220, and a computer program 230 stored in the memory 210 and executable on the processor 220. When the processor 220 executes the computer program 230, it implements the aforementioned cross-database multidimensional data hierarchical aggregation method.

[0180] Based on the aforementioned inventive concept, the present invention also proposes a computer-readable storage medium storing a computer program that executes the aforementioned cross-database multidimensional data hierarchical aggregation method.

[0181] This invention provides a method and apparatus for cross-database multidimensional data hierarchical aggregation. By solving problems such as cross-platform compatibility, performance efficiency, and pagination query accuracy, it provides an efficient, universal, and easy-to-use method and apparatus for multidimensional data analysis. It has significant benefits in improving the analytical capabilities of data-intensive applications, reducing development costs, and optimizing user experience.

[0182] Enhanced cross-platform compatibility: By shielding the underlying SQL query details and adopting a unified query parameter interface, users do not need to worry about differences between different database management systems (DBMS), significantly improving code portability and maintainability. This reduces the workload of rewriting or adjusting query logic due to different database platforms, and improves development efficiency.

[0183] Performance Optimization: This invention employs a method of dynamically generating SQL query statements, enabling the construction of queries layer by layer based on statistical dimensions, thus avoiding the resource consumption issues associated with using full-dimensional aggregation such as CUBE. By executing multiple targeted SQL queries in parallel and utilizing multi-threading technology to accelerate the data retrieval process, it effectively alleviates performance bottlenecks under large data volumes, improving system real-time performance and user experience.

[0184] Improved pagination accuracy: This feature resolves the issues of logical inconsistencies and inaccurate record counts in traditional pagination queries when dealing with aggregated data. By constructing a dedicated SQL query to count the total number of records, the accurate execution of pagination logic is ensured, guaranteeing the integrity and consistency of data on each page and enhancing the user experience of browsing data analysis results.

[0185] Improved efficiency and convenience in data analysis: Data weaving algorithms automatically integrate summarized data from different dimensions, allowing users to obtain all summary information, from detailed to general, with a single query, greatly simplifying report creation and complex data analysis processes. This mechanism reduces the complexity of data analysis, enabling non-professional users to quickly generate the required reports, improving the speed and quality of decision support.

[0186] While the spirit and principles of the invention have been described with reference to several specific embodiments, it should be understood that the invention is not limited to the disclosed specific embodiments, and the division of aspects does not imply that features in these aspects cannot be combined for benefit; such division is merely for ease of description. The invention is intended to cover various modifications and equivalent arrangements included within the spirit and scope of the appended claims.

[0187] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), complex programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0188] The program code used to implement the methods of this disclosure may be written in any combination of one or more programming languages. This program code may be provided to a processor or controller of a general-purpose computer, special-purpose computer, or other programmable data processing apparatus, such that when executed by the processor or controller, the program code causes the functions / operations specified in the flowcharts and / or block diagrams to be implemented. The program code may be executed entirely on a machine, partially on a machine, as a standalone software package partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0189] In the context of this disclosure, a machine-readable medium can be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, apparatus, or device. A machine-readable medium can be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium can be, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0190] To provide interaction with a user, the systems and techniques described herein can be implemented on a computer having: a display device for displaying information to the user (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor); and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the computer. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0191] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as a data server), or computing systems that include middleware components (e.g., an application server), or computing systems that include frontend components (e.g., a user computer with a graphical user interface or web browser through which a user can interact with embodiments of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., a communication network). Examples of communication networks include local area networks (LANs), wide area networks (WANs), and the Internet.

[0192] Computer systems can include clients and servers. Clients and servers are generally located far apart and typically interact via communication networks. Client-server relationships are created by computer programs running on the respective computers and having a client-server relationship with each other. Servers can be cloud servers, servers in distributed systems, or servers incorporating blockchain technology.

[0193] It should be understood that the various forms of processes shown above can be used to rearrange, add, or delete steps. For example, the steps described in this disclosure can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution disclosed in this disclosure can be achieved, and this is not limited herein.

[0194] The specific embodiments described above do not constitute a limitation on the scope of protection of this disclosure. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this disclosure should be included within the scope of protection of this disclosure.

[0195] Regarding the limitation of the scope of protection of this invention, those skilled in the art should understand that various modifications or variations that can be made by those skilled in the art without creative effort based on the technical solution of this invention are still within the scope of protection of this invention.

Claims

1. A method for hierarchical aggregation of multidimensional data across databases, characterized in that, The method includes: S01. Initialize parameters and define query parameters, including: data source information, query table name, statistical dimension information, statistical indicator information, filtering information, and pagination information; S02. Generate a list of SQL queries, construct SQL query statements for statistical data, and construct SQL query statements for total statistical records. S02 includes: S021. Construct the SQL query statement for statistical data. In each loop, for a given dimension array dims, decrease the length of the dimension array from back to front, from the highest dimension combination to the lowest dimension, and gradually reduce the length of the dimension array to generate groupings of different granularities until there are no grouping dimensions, that is, from the most complete combination to the smallest subset, which is dimensionality reduction processing. The dims is statistical dimension information. S021 includes: S0211. Create a basic SQL structure, including a fixed structure: SELECT ${dims} , ${metrics} FROM ${table} group by ${dims}, where ${dims} represents the decreasing statistical dimension, ${metrics} represents the statistical metric in the parameter, and ${table} represents the query table; S0212. Generate SQL queries for different combinations of dimensions, dynamically add a counter column _dim_cnt to record the number of dimensions in the current query. _dim_cnt is added to the query as a column, and its value is equal to the number of dimensions used in the current query. S0213. If filter information is set in the query parameters, the query conditions need to be concatenated. S0214. If pagination information (page) is set in the query parameters, pagination restrictions need to be added. S022. Construct a total record SQL query statement for statistics. Take the first SQL statement above that contains grouping of all dimensions, remove the pagination condition, and use it as a subquery to count the number of records. The structure is as follows: select count(*) as total from (${the first SQL statement without pagination condition}) t; S023. Generate and construct an SQL query statement for the number of statistical dimensions + 1 statistical data statement and an SQL statement for the total number of records; S03. Parallel execution of SQL queries: Establish a connection with the database based on the data source information in the query parameters, and start an independent thread for each statement in the SQL query statement set constructed in the previous step to execute the query in the data source one-to-one. S04. Data weaving: Merge multiple datasets generated in the previous step, making records with the same number of statistical dimensions adjacent and arranging them from high to low according to the dimension hierarchy, forming the same effect as a rollup query. S04 includes: S041. All query results are merged and appended to a list one by one according to the order of the SQL query statements. This list is a collection of statistical data. S042. Obtain the total number of records for each page. Take the last record from the dataset as the total number of records for each page, which is used by the user to calculate the pagination. Exclude this record from the set and do not participate in subsequent sorting. S043. Record the number of records in the first set, i.e., the number of records in the finest granular data begin, as the starting position for sorting; S044. Traverse the data. The data list is traversed through two nested loops. The outer loop starts from a specific starting position begin and obtains each data item as T. The inner loop compares adjacent items in the data list one by one. For each pair of adjacent data items A and B, the dimension values ​​of items A, B, and T are converted into a string form based on the dimension count _dim_cnt of T, resulting in strings a, b, and t. At the same time, the dimension count _dim_cnt values ​​of items A and T, cnt_a and cnt_t, are obtained. S045. Sort the values ​​obtained in the previous step. The sorting logic is as follows: If item A and item T have the same dimension value under the dimension count of T (i.e., a equals t), and item B and item T have different dimension values ​​(i.e., b does not equal t), and the dimension count of T is less than the dimension count of A (i.e., cnt_t < cnt_a), then item T will be moved to the position after item A in the list. If there is no a equals t, then item T will be deleted from the set.

2. The method for cross-database multidimensional data hierarchical aggregation according to claim 1, characterized in that, The query parameters defined in S01 specifically include: Data source information, source: database connection information; Query table name, where table is the data source table name; Statistical dimension information, dims: defines an array of dimensions used for grouping; Statistical indicator information, metrics: Defines a two-dimensional array of aggregated indicator information, which requires specifying the aggregation function, corresponding statistical column name, and alias; Filter information: filter is the condition for filtering user data, i.e., user-defined input; Pagination information, page: includes the current page and the number of records displayed per page.

3. The method for cross-database multidimensional data hierarchical aggregation according to claim 1, characterized in that, In step S03, each SQL query statement corresponds to a data set. The last record is excluded and used as the total number of records for pagination, which is used by the user to calculate pagination. The remaining data set is a statistical data set. These data sets are then woven together and merged into a single set.

4. The method for cross-database multidimensional data hierarchical aggregation according to claim 1, characterized in that, The ROLLUP function in S04 is mainly used for data grouping and summarizing in SQL. It is also known as "rolling summary" or "hierarchical aggregation". ROLLUP not only performs basic grouping calculations on the data, but also automatically calculates the summary results of all subgroups and the entire dataset.

5. A cross-database multi-dimensional data hierarchical aggregation device, characterized in that, The device includes: The initialization parameter module defines query parameters, including: data source information, query table name, statistical dimension information, statistical indicator information, filtering information, and pagination information. The module generates an SQL query list, constructs SQL query statements for statistical data, and constructs SQL query statements for total statistical records. The module for generating the SQL query list includes: The SQL query statement for constructing statistical data is used to decrease the length of the dimension array dim from back to front in each loop, from the highest dimension combination to the lowest dimension, so as to generate groupings of different granularities until there are no grouping dimensions, that is, from the most complete combination to the smallest subset. This is the dimensionality reduction process. The dim represents the statistical dimension information. Create a basic SQL structure, including a fixed structure: SELECT ${dims} , ${metrics} FROM ${table}group by ${dims}, where ${dims} represents the decreasing statistical dimension, ${metrics} represents the statistical metric in the parameter, and ${table} represents the query table; Generate SQL queries for different combinations of dimensions, dynamically add a counter column _dim_cnt to record the number of dimensions in the current query. _dim_cnt is added as a column in the query, and its value is equal to the number of dimensions used in the current query. The query parameters contain filter information, which needs to be concatenated with the query conditions. The query parameters include pagination information (page), so pagination restrictions need to be added. Construct a total record SQL query statement for statistics. Take the first SQL statement above that contains grouping of all dimensions, remove the pagination condition, and use it as a subquery to count the number of records. The structure is as follows: select count(*) as total from (${the first SQL statement without pagination condition}) t; Generate an SQL query statement that constructs the number of statistical dimensions + 1 statistical data statement and an SQL statement that counts the total number of records; The parallel SQL query execution module establishes a connection with the database based on the data source information in the query parameters. It starts an independent thread for each statement in the SQL query statement set constructed in the previous step and executes the query in the data source one-to-one. The data weaving module merges multiple datasets generated by the parallel SQL query execution module, placing records with the same number of statistical dimensions adjacent to each other and arranging them from high to low according to the dimension hierarchy, achieving the same effect as a rollup query. The data weaving module includes: All query results are merged and appended to a list in the order of the SQL query statements. This list is a collection of statistical data. Get the total number of records for each page. Take the last record from the dataset as the total number of records for each page. Use this record to calculate the pagination. Exclude this record from the set and do not participate in subsequent sorting. Record the number of records in the first set, i.e., the number of records at the finest granularity, begin, as the starting position for sorting; The data is traversed using two nested loops. The outer loop starts from a specific beginning position and retrieves each data item as T. The inner loop compares adjacent items in the data list one by one. For each pair of adjacent data items A and B, the dimension values ​​of items A, B, and T are converted into strings a, b, and t based on the dimension count _dim_cnt of T. At the same time, the dimension count _dim_cnt values ​​of items A and T are retrieved as cnt_a and cnt_t. Sort the values ​​obtained in the previous step. The sorting logic is as follows: If item A and item T have the same dimension value under the dimension count of T (i.e., a equals t), and item B and item T have different dimension values ​​(i.e., b does not equal t), and the dimension count of T is less than the dimension count of A (i.e., cnt_t < cnt_a), then item T will be moved to the position after item A in the list. If there is no a equals t, then item T will be deleted from the set.

6. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method according to any one of claims 1-4.

7. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that performs the method according to any one of claims 1-4.

Images