Cross-database multi-dimensional data hierarchical summarization method and device
Through the cross-database multi-dimensional data hierarchical summary method, cross-platform differences, low performance efficiency and paging query accuracy are solved, efficient and general data analysis solutions are realized, and the analysis capabilities and user experience of data-intensive applications are improved.
Patent Information
- Application Number
- CN202411018989.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-29
- Publication Date
- 2024-11-26
- Estimated Expiration
- 2044-07-29
AI Technical Summary
The existing technology has cross-platform differences, low performance efficiency and paging query accuracy when implementing multi-dimensional data hierarchical summary on cross-database platforms, resulting in poor development complexity and user experience.
The multidimensional data level summary method across databases is adopted, query parameters are defined by initializing parameters, SQL query list is generated, SQL query is executed in parallel, and data weaving is carried out to ensure the accuracy and performance optimization of paging queries.
It achieves cross-platform compatibility, improves performance efficiency and paging query accuracy, simplifies data analysis processes, reduces development costs and improves user experience.
Smart Images

Figure CN119025538A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of data processing, and in particular to a method and device for summarizing multidimensional data levels across databases. Background Art
[0002] In contemporary data-intensive applications, advanced data analysis functions have become the key to supporting business decisions and gaining insights into market trends. Among them, multi-dimensional subtotals and statistics of data require complex data aggregation operations, such as the use of advanced aggregation functions such as ROLLUP and CUBE, which are essential for generating multi-dimensional summary data. However, these functions expose a series of technical and implementation challenges in actual applications, especially when it comes to consistency across database platforms, performance efficiency, and accuracy of paging queries.
[0003] First, cross-platform differences and compatibility dilemmas
[0004] Although the SQL standard provides certain normative guidance for advanced aggregate functions, there are significant differences between different database management systems (DBMS) in terms of specific implementation. For example, mainstream databases such as Oracle, SQL Server, MySQL, and PostgreSQL have their own strengths in supporting the specific syntax, functional details, and performance optimization strategies of ROLLUP and CUBE. This inconsistency brings additional burdens to developers, who need to write and debug specific query statements for different databases, reducing the portability and maintainability of the code.
[0005] Second, performance challenges and resource consumption
[0006] Although full-dimensional aggregation functions such as CUBE can provide detailed summary views, their computational cost is high. As the number of dimensions involved in the aggregation increases, the resulting result set expands exponentially, which not only consumes a large amount of memory and CPU resources, but may also cause a significant increase in query response time, affecting the real-time performance of the system and user experience. In big data application scenarios, this performance bottleneck is particularly serious and becomes a bottleneck that restricts analysis efficiency.
[0007] Third, the complexity of paging query
[0008] When implementing the paging function, traditional data aggregation query design often ignores the impact of the aggregation results on the paging logic. Directly paging the result set containing summary rows will not only lead to inaccurate record counts, but may also cause logical confusion in the page content, affecting users' normal browsing and understanding of the data. Especially when it is necessary to display detailed data and summary statistics at the same time, how to accurately control the paging boundaries and ensure the integrity and coherence of each page of data has become a major technical challenge. Summary of the invention
[0009] In order to solve the problems existing in the prior art, the present invention provides a cross-database multidimensional data hierarchical aggregation method and device, which can not only achieve bridging the differences in database platforms, but also efficiently perform advanced aggregation operations, while ensuring accurate paging queries.
[0010] To achieve the above object, the present invention adopts the following technical solutions:
[0011] In one embodiment of the present invention, a method for summarizing multidimensional data levels across databases 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 paging information;
[0013] Furthermore, the query parameters defined in S01 specifically include:
[0014] Data source information, source: database connection information;
[0015] Query table name, table: data source table name;
[0016] Statistical dimension information, dims: defines the dimension array used for grouping;
[0017] Statistical indicator information, metrics: defines a two-dimensional array of indicator aggregation information, which requires the specified aggregation function, corresponding statistical column name, and alias;
[0018] Filter information, filter: is the condition for user data filtering, that is, user-defined input;
[0019] Paging information, page: includes the current page and the number of records displayed per page.
[0020] S02, generate an SQL query list, construct an SQL query statement for statistical data, and construct an SQL query statement for total statistical records;
[0021] Further, the S02 includes:
[0022] S021. Construct SQL query statements for statistical data. In each loop, for a given dimension array dims, loop from the back to the front in descending order, from the highest dimension combination to the lowest dimension, gradually reducing the length of the dimension array to generate groups of different granularities until there is no grouping dimension, that is, from the most complete combination to the smallest subset, which is dimensionality reduction processing;
[0023] The dims is statistical dimension information.
[0024] Further, the S021 includes:
[0025] S0211. Create a basic SQL structure, including a fixed structure: SELECT${dims},${metrics}FROM${table}group by${dims}, where ${dims} identifies the decreasing statistical dimension, ${metrics} identifies the statistical indicator in the parameter, and ${table} identifies the query table;
[0026] S0212. Generate SQL queries for different dimension combinations, dynamically add a count column _dim_cnt to record the number of dimensions in the current query, and _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. The filter information filter is set in the query parameters, and the query conditions need to be spliced;
[0028] S0214. The paging information page is set in the query parameters, and paging restriction conditions need to be added.
[0029] S022. Construct a SQL query statement for total records for statistics. Remove the paging condition from the first SQL statement containing all dimension groups in the above SQL statements as a subquery to count the number of records. The structure is as follows: select count(*)as total from(${the first SQL statement without the paging condition})t.
[0030] S023. Generate and construct the number of statistical dimensions + 1 SQL query statement for statistical data and one SQL statement for statistical total number of records.
[0031] S03, executing SQL queries in parallel, establishing a link with the database according to the data source information in the query parameters, opening an independent thread for each statement in the SQL query statement set constructed in the previous step, and executing queries one-to-one in the data source;
[0032] This method can effectively utilize multi-core CPU resources and speed up data processing, especially when processing large amounts of data.
[0033] Furthermore, each SQL query statement in S03 will query a data set, and the last record is excluded as the total number of paging records total, which is used for user paging calculation; the remaining data sets are statistical data sets, and these data sets are woven and merged into one 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] Further, 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 sequentially generate all summaries from the finest granularity to the highest level.
[0037] Further, the S04 includes:
[0038] S041. All query results are successively merged and appended to a list according to the order of the SQL query statement, and this set is the statistical data set;
[0039] S042. Obtain the total number of records for paging. The last record in the data set is taken as the total number of records for paging total, which is used for users to calculate paging, and 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, then the dimension values of A, B, and T items are 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. The sorting logic: If the dimension values of item A and item T are the same under the dimension count of T (i.e., a is equal to t), and the dimension value of item B and item T is different (i.e., b is not equal to 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 device for summarizing multidimensional data levels across databases is also provided, the device comprising:
[0044] Initialize the parameter module and define query parameters, including: data source information, query table name, statistical dimension information, statistical indicator information, filtering information, and paging information;
[0045] Generate SQL query list module, build SQL query statement for statistical data, and build SQL query statement for total statistical records;
[0046] The parallel execution SQL query module establishes a link with the database based on the data source information in the query parameters, opens an independent thread for each statement in the SQL query statement set built in the previous step, and executes the query one-to-one in the data source;
[0047] The data weaving module merges multiple data sets generated by the parallel execution of the SQL query module. Records with the same number of statistical dimensions are adjacent and arranged from high to low according to the dimension level, forming the same effect as the 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, the aforementioned cross-database multidimensional data hierarchical aggregation method is implemented.
[0049] In one embodiment of the present invention, a computer-readable storage medium is further provided, wherein the computer-readable storage medium stores a computer program for executing the method for hierarchical aggregation of multidimensional data across databases.
[0050] Beneficial effects:
[0051] The present invention provides a cross-database multidimensional data hierarchical aggregation method and device, which solves the problems of cross-platform compatibility, performance efficiency, and paging query accuracy, and provides an efficient, universal and easy-to-use method and device for multidimensional data analysis, which has a significant gain effect in improving the analysis capabilities of data-intensive applications, reducing development costs, optimizing user experience, etc.
[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 the differences between different database management systems (DBMS), which significantly improves the portability and maintainability of the code. This reduces the workload of rewriting or adjusting query logic due to different database platforms and improves development efficiency.
[0053] Performance optimization: This invention adopts a method of dynamically generating SQL query statements, which can build queries layer by layer according to statistical dimensions, avoiding the resource consumption problem caused by using full-dimensional aggregation such as CUBE. By executing multiple targeted SQL queries in parallel and using multi-threading technology to accelerate the data retrieval process, it effectively alleviates the performance bottleneck under large data volumes and improves the system's real-time performance and user experience.
[0054] Improved accuracy of paging query: Solved the problems of logical confusion and inaccurate record count in traditional paging query when facing aggregated data. By specially constructing SQL query statements to count the total number of records, the accurate execution of paging logic is ensured, the integrity and coherence of each page of data is guaranteed, and the user's browsing experience of data analysis results is improved.
[0055] Improved data analysis efficiency and convenience: Data weaving algorithms can automatically integrate summary data from different dimensions, allowing users to obtain all summary information from detailed to general with just one query, greatly simplifying report creation and complex data analysis processes. This mechanism reduces the complexity of data analysis, allowing non-professional users to quickly generate the required reports, and improving the speed and quality of decision support. BRIEF DESCRIPTION OF THE DRAWINGS
[0056] Figure 1 It is a schematic diagram of the process of the cross-database multidimensional data hierarchical aggregation method of the present invention;
[0057] Figure 2 It is a schematic diagram of the architecture of the cross-database multidimensional data hierarchical aggregation method of the present invention;
[0058] Figure 3 It is a schematic diagram of the structure of the device for summarizing multidimensional data levels across databases of the present invention;
[0059] Figure 4 It is a schematic diagram of the structure of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION
[0060] The principles and spirit of the present invention will be described below with reference to several exemplary embodiments. It should be understood that these embodiments are provided only 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. On the contrary, these embodiments are provided to make the present disclosure more thorough and complete, and to fully convey the scope of the present disclosure to those skilled in the art.
[0061] Those skilled in the art will appreciate that the embodiments of the present invention may be implemented as a system, device, apparatus, method or computer program product. Therefore, the present disclosure may be specifically implemented in the following forms, namely: complete hardware, complete software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.
[0062] According to the implementation mode of the present invention, a method and device for summarizing multidimensional data levels across databases are proposed, services for grayscale release are selected, and tasks for grayscale release are configured, thereby integrating multiple grayscale release strategies and improving the controllability and compatibility of grayscale release.
[0063] The principle and spirit of the present invention are explained in detail below with reference to several representative embodiments of the present invention.
[0064] like Figure 1 , Figure 2 As shown, the present invention relates to a method for summarizing multidimensional data levels 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 paging information;
[0066] The query parameters defined in S01 specifically include:
[0067] Data source information, source: database connection information;
[0068] Query table name, table: data source table name;
[0069] Statistical dimension information, dims: defines the dimension array used for grouping;
[0070] Statistical indicator information, metrics: defines a two-dimensional array of indicator aggregation information, which requires the specified aggregation function, corresponding statistical column name, and alias;
[0071] Filter information, filter: is the condition for user data filtering, that is, user-defined input;
[0072] Paging information, page: includes the current page and the number of records displayed per page.
[0073] S02, generate an SQL query list, construct an SQL query statement for statistical data, and construct an SQL query statement for total statistical records;
[0074] The S02 includes:
[0075] S021. Construct SQL query statements for statistical data. In each loop, for a given dimension array dims, loop from the back to the front in descending order, from the highest dimension combination to the lowest dimension, gradually reducing the length of the dimension array to generate groups of different granularities until there is no grouping dimension, that is, from the most complete combination to the smallest subset, which is dimensionality reduction processing;
[0076] The dims is statistical dimension information.
[0077] The S021 includes:
[0078] S0211. Create a basic SQL structure, including a fixed structure: SELECT${dims},${metrics}FROM${table}group by${dims}, where ${dims} identifies the decreasing statistical dimension, ${metrics} identifies the statistical indicator in the parameter, and ${table} identifies the query table;
[0079] S0212. Generate SQL queries for different dimension combinations, dynamically add a count column _dim_cnt to record the number of dimensions in the current query, and _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. The filter information filter is set in the query parameters, and the query conditions need to be spliced;
[0081] S0214. The paging information page is set in the query parameters, and paging restriction conditions need to be added.
[0082] S022. Construct a SQL query statement for total records for statistics. Remove the paging condition from the first SQL statement containing all dimension groups in the above SQL statements as a subquery to count the number of records. The structure is as follows: select count(*)as total from(${the first SQL statement without the paging condition})t.
[0083] S023. Generate and construct the number of statistical dimensions + 1 SQL query statement for statistical data and one SQL statement for statistical total number of records.
[0084] S03, executing SQL queries in parallel, establishing a link with the database according to the data source information in the query parameters, opening an independent thread for each statement in the SQL query statement set constructed in the previous step, and executing queries one-to-one in the data source;
[0085] This method can effectively utilize multi-core CPU resources and speed up data processing, especially when processing large amounts of data.
[0086] Each SQL query statement in S03 will query a data set, and the last record is excluded as the total number of paging records, which is used for users to calculate paging; the remaining data sets are statistical data sets, and these data sets are woven and merged into one set.
[0087] S04. Data weaving. The multiple data sets generated in the previous step are disordered in the statistical grouping dimension and 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 level, thus achieving the same effect as the rollup query.
[0088] The ROLLUP function is mainly used for data grouping and aggregation in SQL, which can be interpreted as "rolling aggregation" or "hierarchical aggregation" in Chinese. ROLLUP not only performs basic grouping calculations on data, like the GROUP BY operation, but also automatically calculates the summary results of all sub-groups and the entire data set.
[0089] When you use ROLLUP in a GROUP BY clause, ROLLUP generates all summaries in order, from the most granular to the highest level.
[0090] The S04 includes:
[0091] S041. All query results are merged and appended into a list one by one according to the order of SQL query statements. This list is a statistical data set.
[0092] S042. Get the total number of paging records. Get the last record from the data set as the total number of paging records, which is used for user paging calculation. Exclude the record from the collection and do not participate in subsequent sorting.
[0093] S043, record the number of records in the first set, that is, the number of records of the finest granularity data, begin, as the starting position of sorting;
[0094] S044. Traverse the data, traverse the data list through two nested loops, the outer loop starts from a specific starting position begin, obtains each data item as T, and the inner loop compares the adjacent items in the data list one by one. For each pair of adjacent data items A and B, the dimension values of A, B, and T items are converted into a string form according to the value of T's dimension count (_dim_cnt), and the strings a, b, and t are obtained. At the same time, the dimension count (_dim_cnt) values of A and T items cnt_a and cnt_t are obtained;
[0095] 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 T - dimension count (i.e., a equals t), and the dimension values of item B and item T are 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 to 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 - mentioned 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] For a clearer explanation of the above - mentioned cross - database multi - dimensional data hierarchical aggregation method, specific embodiments will be described below. 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 specific definition of query parameters in S01 includes:
[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 for grouping. For example, when counting commodity sales information, the statistical parameters are defined according to three dimensions: category, brand, and goods: ["category", "brand", "goods"];
[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, when counting the total sales amount and the average sales amount: "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, 10 records per page: [1,10].
[0106] S02, generate an SQL query list, construct an SQL query statement for statistical data, and construct an SQL query statement for total statistical records;
[0107] The S02 includes:
[0108] S021. Construct SQL query statements for statistical data. In each loop, for a given dimension array dims, loop from the back to the front in descending order, from the highest dimension combination to the lowest dimension, gradually reducing the length of the dimension array to generate groups of different granularities until there is no grouping dimension, that is, from the most complete combination to the smallest subset, which is dimensionality reduction processing;
[0109] The dims is statistical dimension information.
[0110] The S021 includes:
[0111] S0211. Create a basic SQL structure, including a fixed structure: SELECT${dims},${metrics}FROM${table}group by${dims}, where ${dims} identifies the decreasing statistical dimension, ${metrics} identifies the statistical indicator in the parameter, and ${table} identifies the query table;
[0112] S0212. Generate SQL queries for different dimension combinations, dynamically add a count column _dim_cnt to record the number of dimensions in the current query. This 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 two dimensions, "category" and "brand", the value of _dim_cnt is 2; if it is grouped only by "category", its value is 1. It is used for subsequent data weaving.
[0114] S0213. The filter information filter is set in the query parameters, and the query conditions need to be spliced, such as wherecategory in ('mobile phone', 'computer');
[0115] S0214. The paging information page is set in the query parameters. You need to add paging restriction conditions, such as limit 0,10 in MySQL.
[0116] S022. Construct a SQL query statement for total records for statistics. Remove the paging condition from the first SQL statement containing all dimension groups in the above SQL statements as a subquery to count the number of records. The structure is as follows: select count(*)as total from(${the first SQL statement without the paging condition})t.
[0117] S023. Generate and construct the number of statistical dimensions + 1 SQL query statement for statistical data and one SQL statement for statistical 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 0as_dim_cnt,sum(amount)as amount_sum from orders wherecategory 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, executing SQL queries in parallel, establishing a link with the database according to the data source information in the query parameters, opening an independent thread for each statement in the SQL query statement set constructed in the previous step, and executing queries one-to-one in the data source;
[0125] This method can effectively utilize multi-core CPU resources and speed up data processing, especially when processing large amounts of data.
[0126] Each SQL query statement in S03 will query a data set, and the last record is excluded as the total number of paging records, which is used for users to calculate paging; the remaining data sets are statistical data sets, and these data sets are woven and merged into one set.
[0127] S04. Data weaving. The multiple data sets generated in the previous step are disordered in the statistical grouping dimension and 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 level, thus achieving the same effect as the rollup query.
[0128] The ROLLUP function is mainly used for data grouping and aggregation in SQL, which can be interpreted as "rolling aggregation" or "hierarchical aggregation" in Chinese. ROLLUP not only performs basic grouping calculations on data, like the GROUP BY operation, but also automatically calculates the summary results of all sub-groups and the entire data set.
[0129] When you use ROLLUP in a GROUP BY clause, ROLLUP generates all summaries in order, from the most granular to the highest level.
[0130] The S04 includes:
[0131] S041. All query results are merged and appended into a list one by one according to the order of SQL query statements. This list is a statistical data set.
[0132] The collection data example is 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 paging. Obtain the last record from the dataset as the total number of records for paging total, which is used for the user to calculate paging. 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 at 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 dimensionality values of A, B, and T items will be converted into a string form according to the dimensionality count (_dim_cnt) value of T, obtaining strings a, b, and t. At the same time, obtain the dimensionality count (_dim_cnt) values cnt_a and cnt_t of A and T items;
[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 dimensionality values of item A and item T are the same under the dimensionality count of T (i.e., a is equal to t), and the dimensionality value of item B and item T is different (i.e., b is not equal to t), and the dimensionality count of T is less than the dimensionality 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 delete item T from the set.
[0156] This adjustment is done to ensure that records with the same dimension count are arranged next to each other while maintaining the order of the dimensions from high to low. This process continues until the entire data list has been traversed and adjusted according to the rules. In the end, the output result list will show a clear dimension hierarchy, and for each dimension count value, all related records will be clustered together and follow the natural hierarchy of the dimension.
[0157] For example, the sorted data is:
[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 be used to obtain the total record data of the original statistical data for calculating paging. At the same time, the original data is reorganized so that analysts can more easily understand and interpret the summary data under different dimensions. In particular, when making reports or conducting data analysis, the summary of each dimensional level can be intuitively seen.
[0172] Based on the same inventive concept, the present invention also proposes a device for summarizing multidimensional data levels across databases. The implementation of the device can refer to the implementation of the above method, and the repeated parts will not be repeated. The term "module" used below can be 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, the implementation of hardware, or a combination of software and hardware, is also possible and conceivable.
[0173] Figure 3 Schematic diagram of the structure of the cross-database multi-dimensional data hierarchical aggregation device of the present invention. Figure 3 As shown, the device comprises:
[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 paging information;
[0175] Generate SQL query list module 120, construct SQL query statement for statistical data, and construct SQL query statement for total statistical records;
[0176] The parallel execution SQL query module 130 establishes a link with the database according to the data source information in the query parameters, opens 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 by one;
[0177] The data weaving module 140 merges the multiple data sets generated by the parallel execution SQL query module. Records with the same number of statistical dimensions are adjacent and arranged from high to low according to the dimension level, forming the same effect as the rollup query.
[0178] It should be noted that although several modules of the cross-database multidimensional data hierarchical aggregation device are mentioned in the above detailed description, this division is only exemplary and not mandatory. In fact, according to an embodiment of the present invention, the features and functions of two or more modules described above can be concretized in one module. Conversely, the features and functions of one module described above can be further divided into multiple modules for concretization.
[0179] Based on the above invention concept, 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, the aforementioned cross-database multidimensional data hierarchical aggregation method is implemented.
[0180] Based on the aforementioned inventive concept, the present invention further proposes a computer-readable storage medium, which stores a computer program for executing the aforementioned cross-database multidimensional data hierarchical aggregation method.
[0181] The present invention provides a cross-database multidimensional data hierarchical aggregation method and device, which solves the problems of cross-platform compatibility, performance efficiency, and paging query accuracy, and provides an efficient, universal and easy-to-use method and device for multidimensional data analysis, which has a significant gain effect in improving the analysis capabilities of data-intensive applications, reducing development costs, optimizing user experience, etc.
[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 the differences between different database management systems (DBMS), which significantly improves the portability and maintainability of the code. This reduces the workload of rewriting or adjusting query logic due to different database platforms and improves development efficiency.
[0183] Performance optimization: This invention adopts a method of dynamically generating SQL query statements, which can build queries layer by layer according to statistical dimensions, avoiding the resource consumption problem caused by using full-dimensional aggregation such as CUBE. By executing multiple targeted SQL queries in parallel and using multi-threading technology to accelerate the data retrieval process, it effectively alleviates the performance bottleneck under large data volumes and improves the system's real-time performance and user experience.
[0184] Improved accuracy of paging query: Solved the problems of logical confusion and inaccurate record count in traditional paging query when facing aggregated data. By specially constructing SQL query statements to count the total number of records, the accurate execution of paging logic is ensured, the integrity and coherence of each page of data is guaranteed, and the user's browsing experience of data analysis results is improved.
[0185] Improved data analysis efficiency and convenience: Data weaving algorithms can automatically integrate summary data from different dimensions, allowing users to obtain all summary information from detailed to general with just one query, greatly simplifying report creation and complex data analysis processes. This mechanism reduces the complexity of data analysis, allowing non-professional users to quickly generate the required reports, and improving the speed and quality of decision support.
[0186] Although the spirit and principle of the present invention have been described with reference to several specific embodiments, it should be understood that the present invention is not limited to the disclosed specific embodiments, and the division of various aspects does not mean that the features in these aspects cannot be combined to benefit, and such division is only for the convenience of expression. The present invention is intended to cover various modifications and equivalent arrangements contained in the spirit and scope of the attached claims.
[0187] Various implementations 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 chips (SOCs), complex programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various implementations can include: being implemented in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.
[0188] The program code for implementing the method of the present disclosure may be written in any combination of one or more programming languages. These program codes may be provided to a processor or controller of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that the program code, when executed by the processor or controller, enables the functions / operations specified in the flow chart and / or block diagram to be implemented. The program code may be executed entirely on the machine, partially on the machine, partially on the machine and partially on a remote machine as a stand-alone software package, or entirely on a remote machine or server.
[0189] In the context of the present disclosure, a machine-readable medium may be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, device, or equipment. A machine-readable medium may be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium may include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, device, or equipment, or any suitable combination of the foregoing. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, 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 (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user can provide input to the computer. Other types of devices can also be used to provide interaction with the user; for example, the 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 acoustic input, voice input, or tactile input).
[0191] The systems and techniques described herein may be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer with a graphical user interface or a web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such back-end components, middleware components, or front-end components. The components of the system may be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), and the Internet.
[0192] A computer system may include a client and a server. The client and the server are generally remote from each other and usually interact through a communication network. The relationship of client and server is generated by computer programs running on respective computers and having a client-server relationship with each other. The server may be a cloud server, a server of a distributed system, or a server combined with a blockchain.
[0193] It should be understood that the various forms of processes shown above can be used to reorder, add or delete steps. For example, the steps recorded in this disclosure can be executed in parallel, sequentially or in different orders, as long as the desired results of the technical solutions disclosed in this disclosure can be achieved, and this document does not limit this.
[0194] The above specific implementations do not constitute a limitation on the protection scope of the present disclosure. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present disclosure shall be included in the protection scope of the present disclosure.
[0195] Regarding the limitation of the protection scope of the present invention, those skilled in the art should understand that, based on the technical solution of the present invention, various modifications or variations that can be made by those skilled in the art without creative work are still within the protection scope of the present invention.
Claims
1. A method for summarizing multidimensional data levels 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 paging information; S02, generate an SQL query list, construct an SQL query statement for statistical data, and construct an SQL query statement for total statistical records; S03, executing SQL queries in parallel, establishing a link with the database according to the data source information in the query parameters, opening an independent thread for each statement in the SQL query statement set constructed in the previous step, and executing queries one-to-one in the data source; S04. Data weaving: Merge multiple data sets generated in the previous step. Records with the same number of statistical dimensions are adjacent and arranged from high to low according to the dimension level, forming the same effect as rollup query.
2. The cross-database multidimensional data hierarchical aggregation method 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, table: data source table name; Statistical dimension information, dims: defines the dimension array used for grouping; Statistical indicator information, metrics: defines a two-dimensional array of indicator aggregation information, which requires the specified aggregation function, corresponding statistical column name, and alias; Filter information, filter: is the condition for user data filtering, that is, user-defined input; Paging information, page: includes the current page and the number of records displayed per page.
3. The cross-database multidimensional data hierarchical aggregation method according to claim 1, characterized in that: The S02 includes: S021. Construct an SQL query statement for statistical data. In each loop, for a given dimension array dims, loop from the back to the front in descending order, from the highest dimension combination to the lowest dimension, and gradually reduce the length of the dimension array to generate groups of different granularities until there is no grouping dimension, that is, from the most complete combination to the smallest subset, which is a dimensionality reduction process, and the dims is statistical dimension information; S022. Construct a SQL query statement for total records for statistics. Remove the paging condition from the first SQL statement containing all dimension groups in the above SQL statements as a subquery to count the number of records. The structure is as follows: select count(*)as total from(${the first SQL statement without the paging condition})t. S023. Generate and construct the number of statistical dimensions + 1 SQL query statement for statistical data and one SQL statement for statistical total number of records.
4. The cross-database multidimensional data hierarchical aggregation method according to claim 3, characterized in that: The S021 includes: S0211. Create a basic SQL structure, including a fixed structure: SELECT${dims},${metrics}FROM${table}group by${dims}, where ${dims} identifies the decreasing statistical dimension, ${metrics} identifies the statistical indicator in the parameter, and ${table} identifies the query table; S0212. Generate SQL queries for different dimension combinations, dynamically add a count column _dim_cnt to record the number of dimensions in the current query, and _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. The filter information filter is set in the query parameters, and the query conditions need to be spliced; In S0214, the paging information page is set in the query parameters, and paging limit conditions need to be added.
5. The cross-database multidimensional data hierarchical aggregation method according to claim 1, characterized in that: Each SQL query statement in S03 corresponds to a data set retrieved. The last record is excluded to be used as the total number of paging records total for users to calculate paging; the remaining data sets are statistical data sets, and these data sets are woven together and merged into one set.
6. The cross-database multidimensional data hierarchical aggregation method according to claim 1, characterized in that: In S04, the ROLLUP function in SQL is mainly used for data grouping and summarization, and its Chinese explanation is "rolling up" or "hierarchical aggregation"; ROLLUP not only performs basic grouping calculations on data but also automatically calculates the summary results of all sub-groupings and the entire data set.
7. The cross-database multidimensional data hierarchical aggregation method according to claim 1, characterized in that: S04 includes: S041: All query results are successively merged and appended to a list in the order of the SQL query statements, and this set is the statistical data set; S042: Obtain the total number of paging records. The last record is retrieved from the data set as the total number of paging records total for users to calculate paging, and this record is excluded from the set and does not participate in subsequent sorting; 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; S044: Traverse the data. The data list is traversed through two nested loops. The outer loop starts from a specific starting position begin and retrieves 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 dimensional values of A, B, and T items are then converted into a string form according to the dimensional count (_dim_cnt) value of T to obtain strings a, b, and t. At the same time, obtain the dimensional count (_dim_cnt) values cnt_a and cnt_t of A and T items; S045: Sort according to the values obtained in the previous step. The sorting logic is as follows: If the dimensional values of A item and T item are the same under the T dimensional count (i.e., a is equal to t), and the dimensional value of B item is different from that of T item (i.e., b is not equal to t), and the dimensional count of T is less than that of A (i.e., cnt_t < cnt_a), then the position of T item in the list will be adjusted after A item. If there is no a equal to t, then T item is deleted from the set.
8. A device for summarizing multidimensional data levels across databases, characterized in that: The device includes: An initialization parameter module that defines query parameters, including: data source information, query table name, statistical dimension information, statistical index information, filtering information, paging information; A module for generating SQL query lists that constructs SQL query statements for statistical data and constructs a SQL query statement for the total records of statistics; A module for parallel execution of SQL queries that establishes a connection with the database for the data source information in the query parameters, and starts an independent thread for each statement in the set of SQL query statements constructed in the previous step to execute queries in the data source one by one; A data weaving module that merges multiple data sets generated by the parallel execution of SQL query module. Records with the same number of statistical dimensions are adjacent and are arranged from high to low according to the dimension hierarchy, forming the same effect as a rollup query.
9. 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, the method according to any one of claims 1 to 7 is implemented.
10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program for executing the method according to any one of claims 1 to 7.
Citation Information
Patent Citations
Data query method, device and equipment and computer readable storage medium
CN112269792A
Data aggregation in hierarchical structure for query execution
CN118043798A
Evaluation of grouping sets by reduction to group-by clause, with or without a rollup operator, using temporary tables
US6775681B1
