Database multi-level grouping method, device and system and medium

Abstract

The invention provides a database multi-level grouping method, device and system and a medium, and the method comprises the steps: carrying out the analysis processing of GROUP BY clauses, and generating a multi-level grouping list and a grouping item summary list; traversing the grouping item summary list, adding all grouping items to an output item of a GROUP BY calculation pre-process according to a preset sequence, and processing the output item of the GROUP BY calculation pre-process to obtain a GROUP BY output item; traversing the multi-level grouping list, calculating a current grouping GROUP BY result set of each group in combination with the grouping item summarizing list and the output item of the GROUP BY, and summarizing the GROUP BY result set to obtain the GROUP BY result set; calculating a result set according to the GROUP BY result set and the calculation expression; and executing subsequent processing to obtain a final result set. According to the method, the multi-level grouping list and the grouping item summary list are generated by analyzing the GROUP BY clauses, each group is calculated, null grouping items are processed, the expression is calculated according to the result set, the sorted result set is finally output, multi-dimensional data analysis is achieved, and the analysis efficiency is improved.

Description

Technical Field

[0001] The present invention relates to the field of data monitoring technology, and in particular to a database multi-level grouping method, device, system and medium. Background Art

[0002] In the era of the continuous popularization and development of the Internet and mobile Internet devices, the amount of data has experienced explosive growth. The rapidly growing amount of information has brought tremendous pressure and challenges to data statistics and analysis. However, the traditional single grouping (GROUP BY) function can no longer meet the needs of multidimensional data analysis. In order to facilitate the analysis of multidimensional data, a GROUP BY multi-level grouping calculation method is proposed. In the database, the GROUP BY syntax can group data according to the specified content, which is a commonly used tool in statistical analysis. Compared with a single grouping, GROUP BY multi-level grouping supports the calculation of query results under multiple different grouping methods, and can output a result set that contains both totals and sub-totals, which provides convenience for complex statistical operations. However, compared with a single grouping, the implementation of GROUP BY multi-level grouping is also more complicated. Summary of the invention

[0003] In view of the above-mentioned deficiencies in the prior art, the purpose of the present invention is to provide a database multi-level grouping method, which aims to solve the traditional single grouping limitation, support the multi-level grouping function in the GROUP BY clause, realize multi-dimensional data analysis, and improve the efficiency and flexibility of multi-dimensional data analysis.

[0004] In order to achieve the above object, the present invention adopts the following technical solutions:

[0005] A first aspect of the present invention provides a database multi-level grouping method, comprising the following steps:

[0006] Parse the GROUP BY clause to generate a multi-level grouping list and a grouping item summary list;

[0007] Traversing the grouping item summary list, adding all the grouping items to the output items of the GROUP BY calculation front process in a preset order, processing the output items of the GROUP BY calculation front process, and obtaining the GROUP BY output items;

[0008] Traverse the multi-level grouping list, combine the grouping item summary list and the output items of GROUP BY to calculate the current group GROUP BY result set of each group, summarize the current group GROUP BY result set, and obtain the GROUP BY result set;

[0009] According to the GROUP BY result set, the expression in the query is calculated to obtain an expression calculation result set;

[0010] Subsequent processing is performed on the expression calculation result set to obtain a final result set.

[0011] In one embodiment, the parsing of the GROUP BY clause to generate a multi-level grouping list and a grouping item summary list includes:

[0012] Parsing the GROUP BY clause according to grammatical rules and parsing design rules to obtain groups and grouping items;

[0013] The group is added to the multi-level group list and the group items are added to the group item summary list in a preset order.

[0014] In one embodiment, the parsing of the GROUP BY clause to generate a multi-level grouping list and a grouping item summary list also includes:

[0015] After parsing the GROUP BY clause, check whether there is an empty group in the multi-level group list, wherein the empty group is a group with zero group items;

[0016] When there is an empty group in the multi-level group list, a NULL group item is added to the empty group, and the NULL group item is added to the group item summary list.

[0017] In one embodiment, traversing the grouping item summary list, adding all grouping items to the output items of the GROUP BY calculation front process in a preset order, and processing the output items of the GROUP BY calculation front process to obtain the GROUP BY output items include:

[0018] Traversing the grouping item summary list, adding all the grouping items in the grouping item summary list to the output items of the GROUP BY calculation front process in a preset order;

[0019] Based on the output items of the GROUP BY calculation front process, aggregate functions are added and parameter expressions of aggregate functions are deleted to obtain GROUP BY output items.

[0020] In one embodiment, traversing the multi-level grouping list, combining the grouping item summary list and the output items of GROUP BY to calculate the current grouping GROUP BY result set of each group, and summarizing the current grouping GROUP BY result set includes:

[0021] Traversing the multi-level group list, and obtaining a current group item list according to each group in the multi-level group list;

[0022] Performing a difference calculation on the current grouping item list and the grouping item summary list to obtain a remaining grouping item list of the current group;

[0023] Based on the result set of the previous process, group processing is performed according to the current grouping item list, and the GROUP BY output item is calculated to obtain the GROUP BY intermediate result set of the current grouping;

[0024] The GROUP BY result set of the current group is calculated according to the GROUP BY intermediate result set of the current group and the remaining group item list;

[0025] The GROUP BY result sets of all the groups are aggregated to obtain a final GROUP BY result set.

[0026] In one embodiment, the expression in the query is calculated according to the GROUP BY result set to obtain the expression calculation result set, including:

[0027] Identify the aggregate functions and grouping items in the output items of each expression calculation process;

[0028] Extract the aggregate functions and grouping items in the output items of the expression calculation process from the GROUP BY result set;

[0029] The aggregate functions and grouping items in the output items of the expression calculation process are extracted from the GROUP BY result set to replace the aggregate functions and grouping items in the output items of each expression calculation process identified;

[0030] Combined with the GROUP BY result set, the output items are calculated to obtain the expression calculation result set.

[0031] In one embodiment, traversing the grouping item summary list, adding all grouping items to the output items of the GROUP BY calculation front process in a preset order, processing the output items of the GROUP BY calculation front process to obtain the GROUP BY output items, further includes:

[0032] Check whether the query statement contains an ORDER BY clause;

[0033] When the ORDER BY clause is included, the ORDER BY clause is parsed to obtain a sorting item list of the ORDER BY clause, and the grouping items in the sorting item list that do not exist in the grouping item summary list of ORDER BY are added to the end of the sorting item list;

[0034] When the ORDER BY clause is not contained, a new sorting item list is created, and all non-repetitive grouping items in the grouping item summary list are added to the new sorting item list in a preset order.

[0035] A database multi-level grouping device, the device comprising:

[0036] Clause parsing module, used to parse the GROUP BY clause and generate a multi-level grouping list and a grouping item summary list;

[0037] A grouping item traversal module is used to traverse the grouping item summary list, add all grouping items to the output items of the GROUP BY calculation front process in a preset order, process the output items of the GROUP BY calculation front process, and obtain the GROUP BY output items;

[0038] The traversal grouping module is used to traverse the multi-level grouping list, combine the grouping item summary list and the output items of GROUP BY to calculate the current grouping GROUP BY result set of each group, summarize the current grouping GROUP BY result set, and obtain the GROUP BY result set;

[0039] An expression calculation module, used to calculate the expression in the query according to the GROUP BY result set to obtain an expression calculation result set;

[0040] The result set module is used to perform subsequent processing on the expression calculation result set to obtain a final result set.

[0041] A database multi-level grouping system, the system comprising at least one processor; and

[0042] a memory communicatively connected to the at least one processor; wherein,

[0043] The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the database multi-level grouping method.

[0044] A non-volatile computer-readable storage medium stores computer-executable instructions. When the computer-executable instructions are executed by one or more processors, the one or more processors can execute the database multi-level grouping method.

[0045] Beneficial Effects:The embodiments of the present invention disclose a database multi-level grouping method, device, system and medium. Compared with the prior art, the present invention generates a multi-level grouping list and a grouping item summary list by parsing the GROUP BY clause, calculates each grouping, processes empty grouping items, and calculates expressions based on the result set, and finally outputs a sorted result set, thereby realizing multi-dimensional data analysis and improving the efficiency of multi-dimensional data analysis. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] The present invention will be further described below with reference to the accompanying drawings and embodiments, in which:

[0047] Figure 1 A flowchart of a database multi-level grouping method provided by an embodiment of the present invention;

[0048] Figure 2 A schematic diagram of the functional modules of a database multi-level grouping device provided by an embodiment of the present invention;

[0049] Figure 3 A schematic diagram of the hardware structure of a database multi-level grouping system provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0050] In order to make the technical problems, technical solutions and beneficial effects to be solved by the embodiments of the present invention more clearly understood, the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0051] See also Figure 1 , Figure 1 This is a flowchart of an embodiment of the database multi-level grouping method provided by the present invention, and the method specifically includes the following steps:

[0052] S100, parsing the GROUP BY clause to generate a multi-level grouping list and a grouping item summary list;

[0053] S200, traversing the grouping item summary list, adding all grouping items to the output items of the GROUP BY calculation front process in a preset order, processing the output items of the GROUP BY calculation front process, and obtaining GROUP BY output items;

[0054] S300, traverse the multi-level group list, combine the group item summary list and the output item of GROUP BY to calculate the current group GROUP BY result set of each group, summarize the current group GROUP BY result set, and obtain the GROUP BY result set;

[0055] S400, calculating the expression in the query according to the GROUP BY result set to obtain an expression calculation result set;

[0056] S500: Perform subsequent processing on the expression calculation result set to obtain a final result set.

[0057] In this embodiment, the GROUP BY clause is parsed and processed, and multi-level grouping is constructed in the GROUP BY clause by using GROUPINGSETS, ROLLUP, and CUBE to generate a multi-level grouping list and a grouping item summary list. Each grouping item in the grouping item summary list is traversed in a preset order (usually the order in which it appears in the GROUP BY clause). All grouping items are added to the output items of the GROUP BY calculation front process for subsequent GROUP BY group calculation. After this processing, the front process output items include all fields required for calculation, parameter expressions of aggregate functions, and all grouping items of the GROUP BY clause. On the basis of the front process output items (i.e., GROUP BY input items), aggregate functions are added and the parameter expressions of aggregate functions are removed to obtain the output items of GROUP BY, including all fields required for calculation, aggregate functions, and all grouping items of the GROUP BY clause.

[0058] Next, traverse the multi-level grouping list, calculate the current group GROUP BY result set for each group, and then summarize the calculated result sets to obtain the final GROUP BY result set. Specifically, for each group, first perform group calculation according to the current grouping item list to obtain the intermediate result set of the current group (the group calculation here is the same as the conventional GROUP BY single group calculation process, while grouping according to the specified grouping item, while calculating the aggregate function for each group); the remaining grouping items are grouping items that exist in the grouping item summary list but not in the current grouping item list. After the intermediate result set is calculated, the remaining grouping items in the intermediate result set are set to NULL to obtain the GROUP BY result set of the current group; when the current group GORUP BY result sets of each group in the multi-level grouping list are calculated, they are summarized to obtain the final GROUP BY result set.

[0059] Next, the values ​​of each expression in the GROUP BY result set are calculated to obtain the expression calculation result set. The output items of the expression calculation are all the fields required for the calculation and all the expressions required for the subsequent calculation. That is, the expression calculation result set contains the values ​​of all the fields required for the calculation and the values ​​of all the expressions required for the subsequent calculation. The expression calculation result set is processed later, including HAVING clause filtering, ORDER BY sorting, and LIMIT limiting the number of results, to obtain the final result set. The final result set is the result after all processing, including filtering, sorting, and limiting the number of data.

[0060] The above steps ensure that the SQL query can correctly handle complex grouping and aggregation operations and generate a result set that meets user needs. From parsing the GROUP BY clause to finally generating a sorted and filtered result set, each step ensures the correctness of the data and the integrity of the query.

[0061] In one embodiment, the parsing of the GROUP BY clause to generate a multi-level grouping list and a grouping item summary list includes:

[0062] Parsing the GROUP BY clause according to grammatical rules and parsing design rules to obtain groups and grouping items;

[0063] The group is added to the multi-level group list and the group items are added to the group item summary list in a preset order.

[0064] In this embodiment, the GROUP BY clause is parsed, including the GROUPING SETS, ROLLUP, and CUBE parts. According to the GROUPING SETS, ROLLUP, and CUBE syntax in the GROUP BY clause, the parser will split and combine these groups, generate all possible group combinations, and obtain a multi-level group list and a group item summary list. Among them, the multi-level group list is composed of one or more groups, describing all the groups specified in the GROUP BY clause; each group is composed of one or more group items, and the group item is the field or expression based on the group; the group item summary list contains all the group items that have appeared in the GROUP BY clause. During the parsing of the GROUP BY clause, the parser will preliminarily construct a multi-level group list and a group item summary list according to the grammatical rules and parsing design, through splitting, combination processing, and Cartesian product processing of the same-level groups. After the processes of GROUPING SETS, ROLLUP, CUBE splitting, combination processing, and Cartesian product calculation of the groups, all groups are obtained and added to the multi-level group list. All grouping items in the GROUP BY clause are added to the grouping item summary list in order without duplication. These lists provide the basis for subsequent grouping and aggregation calculations.

[0065] Specifically, GROUPING SETS allows you to specify multiple grouping sets, each containing different fields or expressions. ROLLUP is used to generate summary rows for all intermediate levels from the lowest level of grouping to the highest level of summary. CUBE is used to generate all possible grouping combinations, including single field grouping, field combination grouping, and summary of all fields. After expanding ROLLUP and CUBE, the parser will perform Cartesian product calculations on the groups to generate all possible grouping combinations, ensuring that all groups and grouping items can be processed correctly during query processing.

[0066] In a specific embodiment, the following statement is taken as an example to illustrate the above calculation process: select id, col1, col2, col3, mod(id), col2+2, abs(col2), abs(col2+2), 2+sum(col2+2), sum(col3+3) from t1group by grouping sets(rollup(id)), rollup(col1+1, col2+2). According to the grammatical rules and analytical design, after splitting, combination processing, same-layer grouping Cartesian product processing and other processes, the preliminary multi-level grouping list is as follows: {{id, col1+1, col2+2}, {id, col1+1}, {id}, {col1+1, col2+2}, {col1+1}, {}}. The preliminary grouping item summary list is as follows: {id, col1+1, col2+2}. The order of the grouping items in the grouping item summary list is consistent with the order in which they appear in the GROUP BY clause.

[0067] In one embodiment, the parsing of the GROUP BY clause to generate a multi-level grouping list and a grouping item summary list further includes:

[0068] After parsing the GROUP BY clause, check whether there is an empty group in the multi-level group list, wherein the empty group is a group with zero group items;

[0069] When there is an empty group in the multi-level group list, a NULL group item is added to the empty group, and the NULL group item is added to the group item summary list.

[0070] In the present embodiment, during the GROUP BY clause parsing, for the special grouping item "()", a new grouping is added according to the normal process, but it is not added as a grouping item to the newly added grouping, nor is it added to the grouping item summary list. After the GROUP BY clause parsing is completed, it is necessary to check each grouping in the multi-level grouping list to check whether the number of grouping items of the grouping is zero. When the number of grouping items of one or more groups in the multi-level grouping list is 0, it is necessary to add a new grouping item NULL in the groupings where all grouping items are zero, i.e., the empty grouping. Then NULL is added as a grouping item to the end of the grouping item summary list without duplication. In the final multi-level grouping list and the grouping item summary list, each grouping is composed of one or more grouping items.

[0071] In a specific embodiment, after the GROUP BY syntax parsing is completed, for groups with 0 grouping items, a NULL value is added to the corresponding group as a grouping item, and then the NULL value is added to the end of the group summary list to obtain the final multi-level group list and group item summary list: {{id,col1+1,col2+2},{id,col1+1},{id},{col1+1,col2+2},{col1+1},{NULL}},{id,col1+1,col2+2,NULL}.

[0072] The above steps ensure that in the SQL query, even if some groups do not have clear grouping items, the grouping operation can be performed correctly, and in the final grouping result, each group has at least one grouping item. This avoids errors when performing grouping operations and ensures the consistency and correctness of query results.

[0073] In one embodiment, traversing the grouping item summary list, adding all grouping items to the output items of the GROUP BY calculation front process in a preset order, and processing the output items of the GROUP BY calculation front process to obtain the GROUP BY output items include:

[0074] Traversing the grouping item summary list, adding all the grouping items in the grouping item summary list to the output items of the GROUP BY calculation front process in a preset order;

[0075] Based on the output items of the GROUP BY calculation front process, aggregate functions are added and parameter expressions of aggregate functions are deleted to obtain GROUP BY output items.

[0076] In this embodiment, the grouping item summary list is traversed, and all grouping items are added to the output items of the GROUP BY calculation front process in order without duplication, for subsequent GROUP BY group calculation. Specifically, the output items of the GROUP BY calculation front process include all fields required for statement processing (such as id, col1, col2, col3), all parameter expressions of each aggregate function (such as col2+2, col3+3, col1+1) and all grouping items of the GROUP BY clause, and the output items of the front process are finally obtained: {id, col1, col2, col3, col2+2, col3+3, col1+1, NULL}.

[0077] Adjust the GROUP BY output items based on the output items of the GROUP BY calculation front process, add all aggregate functions to the output items of the GROUP BY calculation front process, and remove all parameter expressions of each aggregate function (because the use of these parameters has been completed in the calculation of aggregate functions during grouping), and obtain the GROUP BY output items. Therefore, at this time, the GROUP BY output items include all fields required for statement processing, all aggregate functions, and all grouping items of the GROUP BY clause.

[0078] In a specific embodiment, a GROUP BY front-end output item is set, and the grouping items in the group summary list are added to the output items of the GROUP BY calculation front-end process, so that the output items of the GROUP BY calculation front-end process are {id, col1, col2, col3, col2+2, col3+3, col1+1, NULL}, and aggregation functions are added to the output items of the GROUP BY calculation front-end process and the parameter expressions of the aggregation functions are deleted, so that the GROUP BY output items are {id, col1, col2, col3, col2+2, col1+1, sum(col2+2), sum(col3+3), NULL}.

[0079] In one embodiment, the traversal of the multi-level grouping list, combining the grouping item summary list and the output items of GROUP BY to calculate the current grouping GROUP BY result set of each group, and aggregating the current grouping GROUP BY result set to obtain the GROUP BY result set includes:

[0080] Traversing the multi-level group list, and obtaining a current group item list according to each group in the multi-level group list;

[0081] Performing a difference calculation on the current grouping item list and the grouping item summary list to obtain a remaining grouping item list of the current group;

[0082] Based on the result set of the previous process, group processing is performed according to the current grouping item list, and the GROUP BY output item is calculated to obtain the GROUP BY intermediate result set of the current grouping;

[0083] The GROUP BY result set of the current group is calculated according to the GROUP BY intermediate result set of the current group and the remaining group item list;

[0084] The GROUP BY result sets of all the groups are aggregated to obtain a final GROUP BY result set.

[0085] In this embodiment, the multi-level grouping list is traversed, the current grouping item list is obtained from each grouping, and the grouping item summary list and the current grouping item list are used to perform a difference set operation to obtain the remaining grouping item list. Among them, the remaining grouping item list includes grouping items in the summary list but not in the current grouping list. During the GROUP BY operation of the current grouping, the result set of the previous process is first sorted according to the current grouping item list. After sorting, the current grouping item list is used to perform conventional grouping processing, and various output items such as aggregate functions are calculated to obtain the GROUP BY intermediate result set of the current grouping.

[0086] After the calculation of the GROUP BY intermediate result set of the current group is completed, each record of the intermediate result set is traversed, and the remaining group item values ​​in each record are replaced with NULL values ​​to obtain the GROUP BY result set of the current group. After the calculation of the GROUP BY result sets of all groups is completed, all GROUP BY result sets are spliced ​​to obtain the GROUP BY final result set. The number of record rows in the GROUP BY final result set is the sum of the number of rows in the GROUP BY result sets of all groups.

[0087] Furthermore, for NULL grouping items, if the grouping item list of the current group contains only one NULL grouping item, the remaining grouping item list of the current group will contain all grouping items in the GROUP BY clause except "()". Based on the result set of the previous process, using NULL values ​​for grouping calculation will obtain a row of summary records. After replacing the remaining grouping items of the current group in this row with NULL values, the result obtained satisfies the semantics of "GROUP BY()" in multi-level grouping; for NULL values ​​in the remaining grouping item list, the same treatment is adopted as for the remaining grouping items, which will not affect the normal calculation of the current group GROUP BY result set.

[0088] In a specific embodiment, each group in the multi-level group list is first traversed to calculate the result set of each group. Taking the first group {id, col1+1, col2+2} as an example, its current group item list is {id, col1+1, col2+2}, and the remaining group item list is {NULL}. Based on the result set of the GROUP BY pre-process, grouping and aggregate function calculations are performed according to the elements in the current group item list to obtain the GROUP BY intermediate result set of the current group. The GROUP BY intermediate result set of the current group is modified according to the remaining group item list, and the values ​​of the remaining group items in each row of records are modified to NULL values ​​to obtain the GROUP BY result set of the current group. Here, there is only one NULL value in the remaining group item list, and there is no need to modify it to NULL again. The GROUP BY intermediate result set of the current group is the GROUP BY result set of the current group. Perform the same processing as described in the above steps on the group {id,col1+1},{id},{col1+1,col2+2},{col1+1},{NULL} to obtain the GROUP BY result set of all groups. Merge the GROUP BY result sets of all groups to obtain the final GROUP BY result set.

[0089] In one embodiment, the expression in the query is calculated according to the GROUP BY result set to obtain the expression calculation result set, including:

[0090] Identify the aggregate functions and grouping items in the output items of each expression calculation process;

[0091] Extract the aggregate functions and grouping items in the output items of the expression calculation process from the GROUP BY result set;

[0092] The aggregate functions and grouping items in the output items of the expression calculation process are extracted from the GROUP BY result set to replace the aggregate functions and grouping items in the output items of each expression calculation process identified;

[0093] Combined with the GROUP BY result set, the output items are calculated to obtain the expression calculation result set.

[0094] In this embodiment, after the GROUP BY operation is completed, the next step of expression calculation is performed according to the normal processing flow. The aggregate functions and grouping items in the output items of each expression calculation process are identified, and the values ​​of the above aggregate functions and grouping items are extracted from the GROUP BY result set. The aggregate functions and grouping items in the output items of each expression calculation process are replaced with the extracted values, and the output items are calculated in combination with the values ​​of each field in the GROUP BY result set to obtain the expression calculation result set. The expression result set includes all query items of the statement and all fields or expressions used in subsequent HAVING filtering, ORDER BY sorting and other processes.

[0095] Specifically, based on the GROUP BY result set, the output items of the expression calculation process are calculated. The specific output items are as follows: {id, col1, col2, col3, mod(id), col2+2, abs(col2), abs(col2+2), 2+sum(col2+2), sum(col3+3), col1+1, NULL}. Given that the grouping item summary list is {id, col1+1, col2+2, NULL}, when calculating the output items of the expression calculation process, for the output items col1+1, col2+2, the parameter col2+2 in the expression abs(col2+2), and the parameters sum(col2+2) and sum(col3+3) in the expression 2+sum(col2+2), the values ​​of the corresponding expressions are directly taken from the GROUP BY result set for calculation. The remaining field values ​​required for the calculation can be directly read from the GROUP BY result set. After the calculation is completed, the result set of the expression calculation step is obtained.

[0096] In one embodiment, traversing the grouping item summary list, adding all grouping items to the output items of the GROUP BY calculation front process in a preset order, processing the output items of the GROUP BY calculation front process to obtain the GROUP BY output items, further includes:

[0097] Check whether the query statement contains an ORDER BY clause;

[0098] When the ORDER BY clause is included, the ORDER BY clause is parsed to obtain a sorting item list of the ORDER BY clause, and the grouping items in the sorting item list that do not exist in the grouping item summary list of ORDER BY are added to the end of the sorting item list;

[0099] When the ORDER BY clause is not contained, a new sorting item list is created, and all non-repetitive grouping items in the grouping item summary list are added to the new sorting item list in a preset order.

[0100] In this embodiment, it is checked whether the query statement contains an ORDER BY clause. If it contains an ORDER BY clause, the ORDER BY clause is parsed to obtain the sorting item list of the ORDER BY clause, and each grouping item in the grouping item summary list is compared with the items in the ORDER BY sorting item list to find out the grouping items that are not in the ORDER BY sorting item list. These grouping items that are not in the ORDER BY sorting item list are added to the end of the ORDER BY sorting item list in the order in which they are in the grouping item summary list without duplication. If there is no ORDER BY clause, a new sorting item list needs to be created, and the grouping items are added to the new sorting item list in the order in which they are in the grouping item summary list without duplication. This is done to ensure that when there is no special sorting requirement, the output result set can meet the order of the summarized records first and the summarized rows later. It is ensured that the final sorting item list contains all the grouping items in the grouping item summary list, as well as any other sorting items specified by the user.

[0101] Specifically, in SQL queries, the ORDER BY clause is used to specify the sorting rules for result sets. If the query contains an ORDER BY clause, the sorting item list is directly specified by this clause. Without an ORDER BY clause, the database usually does not return an unordered result set, but determines the sorting order based on certain rules. In the absence of an ORDER BY clause, the database builds a default sorting item list based on the grouping items in the GROUP BY clause and sorts the result set according to this list. This ensures that even if there is no explicit sorting requirement, the output result set has a certain order, usually with the summarized records first and the summary rows last. This default sorting order helps to maintain consistency and predictability of the results.

[0102] The present invention also provides a functional module diagram of a database multi-level grouping device, such as Figure 2 As shown, the clause parsing module 11, the traversal grouping item module 12, the traversal grouping module 13, the expression calculation module 14 and the result set module 15 are connected in sequence. The module referred to in the present invention refers to a series of computer program instruction segments that can complete specific functions, which is more suitable for the database multi-level grouping method execution process than the program. For the specific implementation of each module, please refer to the corresponding method embodiment above, which will not be repeated here.

[0103] A database multi-level grouping device, the device comprising:

[0104] A clause parsing module 11 is used to parse the GROUP BY clause and generate a multi-level grouping list and a grouping item summary list;

[0105] A grouping item traversal module 12 is used to traverse the grouping item summary list, add all grouping items to the output items of the GROUP BY calculation front process in a preset order, and process the output items of the GROUP BY calculation front process to obtain GROUP BY output items;

[0106] The traversal grouping module 13 is used to traverse the multi-level grouping list, calculate the current grouping GROUP BY result set of each group by combining the grouping item summary list and the output items of GROUP BY, and summarize the current grouping GROUP BY result set to obtain the GROUP BY result set;

[0107] An expression calculation module 14, used to calculate the expression in the query according to the GROUP BY result set to obtain an expression calculation result set;

[0108] The result set module 15 is used to perform subsequent processing on the expression calculation result set to obtain a final result set.

[0109] In one embodiment, the clause parsing module 11 includes:

[0110] A clause parsing unit, used for parsing the GROUP BY clause according to grammatical rules and parsing design rules to obtain groups and grouping items;

[0111] The grouping unit is used to add the group to the multi-level grouping list and add the group items to the group item summary list in a preset order.

[0112] In one embodiment, the clause parsing module 11 further includes:

[0113] An empty grouping unit, used to check whether there is an empty group in the multi-level grouping list after parsing the GROUP BY clause, wherein the empty group is a group with zero grouping items;

[0114] The grouping item adding unit is used to add a NULL grouping item to the empty group when there is an empty group in the multi-level grouping list, and add the NULL grouping item to the grouping item summary list.

[0115] In one embodiment, the traversal group item module 12 includes:

[0116] A grouping item traversal unit, used for traversing the grouping item summary list, and adding all the grouping items in the grouping item summary list to the output items of the GROUP BY calculation front process in a preset order;

[0117] The output item processing unit is used to add aggregate functions and delete parameter expressions of aggregate functions based on the output items of the GROUP BY calculation front-end process to obtain GROUP BY output items.

[0118] In one embodiment, the traversal grouping module 13 includes:

[0119] A grouping traversal unit, used for traversing the multi-level grouping list, and obtaining a current grouping item list according to each group in the multi-level grouping list;

[0120] A difference set calculation unit, used for performing a difference set calculation on the current grouping item list and the grouping item summary list to obtain a remaining grouping item list of the current group;

[0121] An intermediate result set unit, configured to perform grouping processing according to the current grouping item list based on the result set of the previous process, and to calculate the GROUP BY output items to obtain the GROUP BY intermediate result set of the current grouping;

[0122] A result set unit, used for calculating the GROUP BY result set of the current group according to the GROUP BY intermediate result set of the current group and the remaining group item list;

[0123] The result set concatenation unit is used to aggregate the GROUP BY result sets of all the groups to obtain a final GROUP BY result set.

[0124] In one embodiment, the expression calculation module 14 includes:

[0125] An expression recognition unit, used to recognize the aggregation functions and grouping items in the output items of each expression calculation process;

[0126] The expression value extraction unit is used to extract the aggregate functions and grouping items in the output items of the expression calculation process from the GROUP BY result set;

[0127] An expression replacement unit is used to extract the aggregate functions and grouping items in the output items of the expression calculation process from the GROUP BY result set to replace the aggregate functions and grouping items in the output items of each identified expression calculation process;

[0128] The expression calculation unit is used to calculate the output items in combination with the GROUP BY result set to obtain the expression calculation result set.

[0129] In one embodiment, the traversal group item module 12 further includes:

[0130] Clause query unit, used to check whether the query statement contains an ORDER BY clause;

[0131] A clause parsing unit, configured to parse the ORDER BY clause when it contains the ORDER BY clause, obtain a sorting item list of the ORDER BY clause, and add the grouping items in the sorting item list that do not exist in the grouping item summary list of ORDER BY to the end of the sorting item list;

[0132] The list creation unit is used to create a new sorting item list when the ORDER BY clause is not contained, and add all non-repeating grouping items in the grouping item summary list to the new sorting item list in a preset order.

[0133] Another embodiment of the present invention provides a database multi-level grouping system, the system comprising at least one processor; and

[0134] a memory communicatively connected to the at least one processor; wherein,

[0135] The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the database multi-level grouping method.

[0136] One or more processors 110 and memory 120, Figure 3 A processor 110 is used as an example for description. The processor 110 and the memory 120 may be connected via a bus or other means. Figure 3 The example of connecting through bus is taken in the following.

[0137] The processor 110 is used to complete various control logics of the control system 10. It can be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field programmable gate array (FPGA), a single-chip microcomputer, an ARM (Acorn RISC Machine) or other programmable logic device, a discrete gate or transistor logic, a discrete hardware component, or any combination of these components. In addition, the processor 110 can also be any traditional processor, microprocessor or state machine. The processor 110 can also be implemented as a combination of computing devices, for example, a combination of a DSP and a microprocessor, a plurality of microprocessors, one or more microprocessors combined with a DSP and / or any other such configuration.

[0138] The memory 120 is a non-volatile computer-readable storage medium that can be used to store non-volatile software programs, non-volatile computer executable programs and modules, such as program instructions corresponding to the database multi-level grouping method in the embodiment of the present invention. The processor 110 executes various functional applications and data processing of the control system 10 by running the non-volatile software programs, instructions and units stored in the memory 120, that is, the database multi-level grouping method in the above method embodiment is implemented.

[0139] The memory 120 may include a program storage area and a data storage area, wherein the program storage area may store an operating system and an application required by at least one function; the data storage area may store data created according to the use of the control system 10, etc. In addition, the memory 120 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 120 may optionally include a memory remotely arranged relative to the processor 110, and these remote memories may be connected to the control system 10 via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0140] One or more units are stored in the memory 120, and when executed by one or more processors 110, the database multi-level grouping method in any of the above method embodiments is executed, for example, the above described Figure 1 The method comprises steps S100 to S500.

[0141] An embodiment of the present invention provides a non-volatile computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are executed by one or more processors, for example, to execute the above-described Figure 1 The method comprises steps S100 to S500.

[0142] As an example, non-volatile storage media can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) as external cache memory. By way of illustration and not limitation, RAM can be obtained in many forms such as synchronous RAM (SRAM), dynamic RAM, (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), Synchlink DRAM (SLDRAM), and direct Rambus RAM (DRRAM). The disclosed memory components or memories of the operating environment described herein are intended to include one or more of these and / or any other suitable types of memory.

[0143] In summary, the present invention discloses a database multi-level grouping method, device, system and medium, including: parsing the GROUP BY clause to generate a multi-level grouping list and a grouping item summary list; traversing the grouping item summary list, adding all grouping items to the output items of the GROUP BY calculation front process in a preset order, processing the output items of the GROUP BY calculation front process, and obtaining the GROUP BY output items; traversing the multi-level grouping list, combining the grouping item summary list and the GROUP BY output items to calculate the current grouping GROUP BY result set of each group, summarizing the current grouping GROUP BY result set to obtain the GROUP BY result set; calculating the expression in the query according to the GROUP BY result set to obtain the expression calculation result set; performing subsequent processing on the expression calculation result set to obtain the final result set. The present invention generates a multi-level grouping list and a grouping item summary list by parsing the GROUP BY clause, calculates each group, processes empty grouping items, and calculates the expression according to the result set, and finally outputs the sorted result set, thereby realizing multidimensional data analysis and improving the efficiency of multidimensional data analysis.

[0144] Of course, those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing related hardware (such as a processor, a controller, etc.) through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium, and the computer program can include the processes of the above-mentioned method embodiments when executed. The storage medium can be a memory, a disk, a floppy disk, a flash memory, an optical storage device, etc.

[0145] It should be understood that the application of the present invention is not limited to the above examples. For ordinary technicians in this field, improvements or changes can be made based on the above description. All these improvements and changes should fall within the scope of protection of the claims attached to the present invention.

Claims

1. A database multi-level grouping method, characterized in that: The steps include: Parse the GROUP BY clause to generate a multi-level grouping list and a grouping item summary list; Traverse the group item summary list, add all group items to the output items of the GROUP BY calculation front process in a preset order, process the output items of the GROUP BY calculation front process, and obtain the GROUP BY output items; Traverse the multi-level grouping list, combine the grouping item summary list and the output items of GROUP BY to calculate the current group GROUP BY result set of each group, summarize the current group GROUP BY result set, and obtain the GROUP BY result set; According to the GROUP BY result set, the expression in the query is calculated to obtain an expression calculation result set; Subsequent processing is performed on the expression calculation result set to obtain a final result set.

2. The database multi-level grouping method according to claim 1, characterized in that: The GROUP BY clause is parsed to generate a multi-level grouping list and a grouping item summary list, including: Parsing the GROUP BY clause according to grammatical rules and parsing design rules to obtain groups and grouping items; The group is added to the multi-level group list and the group items are added to the group item summary list in a preset order.

3. The database multi-level grouping method according to claim 1 is characterized in that: The parsing of the GROUP BY clause to generate a multi-level grouping list and a grouping item summary list also includes: After parsing the GROUP BY clause, check whether there is an empty group in the multi-level group list, wherein the empty group is a group with zero group items; When there is an empty group in the multi-level group list, a NULL group item is added to the empty group, and the NULL group item is added to the group item summary list.

4. The database multi-level grouping method according to claim 1 is characterized in that: The traversing the grouping item summary list, adding all the grouping items to the output items of the GROUP BY calculation front process in a preset order, and processing the output items of the GROUP BY calculation front process to obtain the GROUP BY output items include: Traversing the grouping item summary list, adding all the grouping items in the grouping item summary list to the output items of the GROUP BY calculation front process in a preset order; Based on the output items of the GROUP BY calculation front process, aggregate functions are added and parameter expressions of aggregate functions are deleted to obtain GROUP BY output items.

5. The database multi-level grouping method according to claim 1 is characterized in that: The multi-level grouping list is traversed, and the current grouping GROUP BY result set of each group is calculated by combining the grouping item summary list and the output items of GROUP BY, and the current grouping GROUP BY result set is summarized to obtain the GROUP BY result set, including: Traversing the multi-level group list, and obtaining a current group item list according to each group in the multi-level group list; Performing a difference calculation on the current grouping item list and the grouping item summary list to obtain a remaining grouping item list of the current group; Based on the result set of the previous process, group processing is performed according to the current grouping item list, and the GROUP BY output items are calculated to obtain the GROUP BY intermediate result set of the current grouping; The GROUP BY result set of the current group is calculated according to the GROUP BY intermediate result set of the current group and the remaining group item list; The GROUP BY result sets of all the groups are aggregated to obtain a final GROUP BY result set.

6. The database multi-level grouping method according to claim 1 is characterized in that: The expression in the query is calculated according to the GROUP BY result set to obtain an expression calculation result set, including: Identify the aggregate functions and grouping items in the output items of each expression calculation process; Extract the aggregate functions and grouping items in the output items of the expression calculation process from the GROUP BY result set; The aggregate functions and grouping items in the output items of the expression calculation process are extracted from the GROUP BY result set to replace the aggregate functions and grouping items in the output items of each identified expression calculation process; Combined with the GROUP BY result set, the output items are calculated to obtain the expression calculation result set.

7. The database multi-level grouping method according to claim 1 is characterized in that: The step of traversing the grouping item summary list, adding all grouping items to the output items of the GROUP BY calculation front process in a preset order, processing the output items of the GROUP BY calculation front process to obtain the GROUP BY output items, further includes: Check whether the query statement contains an ORDER BY clause; When the ORDER BY clause is included, the ORDER BY clause is parsed to obtain a sorting item list of the ORDER BY clause, and the grouping items in the sorting item list that do not exist in the grouping item summary list of ORDER BY are added to the end of the sorting item list; When the ORDER BY clause is not contained, a new sorting item list is created, and all the grouping items in the grouping item summary list without duplication are added to the new sorting item list in a preset order.

8. A database multi-level grouping device, characterized in that: The device comprises: Clause parsing module, used to parse the GROUP BY clause and generate a multi-level grouping list and a grouping item summary list; A grouping item traversal module is used to traverse the grouping item summary list, add all grouping items to the output items of the GROUP BY calculation front process in a preset order, process the output items of the GROUP BY calculation front process, and obtain the GROUP BY output items; The traversal grouping module is used to traverse the multi-level grouping list, combine the grouping item summary list and the output items of GROUP BY to calculate the current grouping GROUP BY result set of each group, summarize the current grouping GROUP BY result set, and obtain the GROUP BY result set; An expression calculation module, used to calculate the expression in the query according to the GROUP BY result set to obtain an expression calculation result set; The result set module is used to perform subsequent processing on the expression calculation result set to obtain a final result set.

9. A database multi-level grouping system, characterized in that: The system includes at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to execute the database multi-stage grouping method according to any one of claims 1 to 7.

10. A non-volatile computer-readable storage medium, characterized in that: The non-volatile computer-readable storage medium stores computer-executable instructions, which, when executed by one or more processors, enable the one or more processors to execute the database multi-level grouping method according to any one of claims 1 to 7.

Images