Database query optimization method and device

Abstract

The present invention discloses a method and device for optimizing database query. The method comprises: extracting data of all partitions from a database in parallel; concurrently counting the values ​​of the row label column in each partition; deduplicating the values ​​of the row label column of each partition to determine the number of rows in the result set; assigning the row number in the result set to the value of each row label column; counting all the values ​​of the pivoting column to determine the number of columns in the result set; grouping the partition data according to the partition value of the pivoting column corresponding to the partition dimension; counting the value of the pivoting column to initialize each column of the result set; concurrently executing grouping by all pivot by columns in the group for each group, and calculating the last aggregate function for the columns in the select clause; for each group result, finding the row number corresponding to the row label column value and the column number corresponding to the pivoting column value, and filling the aggregate value into the corresponding position of the result set. The present invention can improve the query efficiency and the utilization rate of computing resources by reducing unnecessary data grouping and serial calculation.

Description

Technical Field

[0001] The present invention relates to the field of database technology application, and in particular to a database query optimization method and device. Background Art

[0002] The calculation process of the prior art when processing Pivot by query is as follows:

[0003] 1. Concurrent calculation for all partitions: Within each data partition, use all pivot by columns to perform groupby operations. Within each group, calculate the last aggregate function for each column that appears in the select clause.

[0004] 2. Merge partition results: Merge the results calculated for each partition.

[0005] 3. Group again: Use all pivot by columns to group the merged results again. In this grouping, the pivoting column (the last column in the pivot by clause) is the first column, and the row label column is the subsequent column.

[0006] 4. Count all values ​​of the row label column: Determine all different values ​​of the row label column in the result set, which determines the number of rows in the result set.

[0007] 5. Count the values ​​of the pivoting column: Determine all the different values ​​of the pivoting column, which determines the number of columns in the result set.

[0008] 6. Perform operations on each column of the result:

[0009] Define the corresponding data area for each column in the aggregation result.

[0010] Use the row label column as the grouping column and calculate the last aggregate function again for each column in the select.

[0011] Determine the row numbers in the result set that correspond to the values ​​of these row label columns.

[0012] Fill the calculated aggregate value into the corresponding position of the result set.

[0013] The problem with the prior art is that when the pivoting column is a partition column, it means that before the Pivot by operation, the data has been grouped and stored in different partitions according to the value of the pivoting column. However, the prior art does not use this feature to optimize query performance, but still executes according to the above steps, which will lead to unnecessary data grouping operations and serial calculations, thereby affecting query efficiency.

[0014] Currently, no effective solution has been proposed to solve the problem of poor performance of Pivot by queries in distributed databases due to the existing technologies. Summary of the invention

[0015] To solve the above technical problems, embodiments of the present invention are intended to provide a database query optimization method and device to at least solve the problem of poor performance of Pivot by queries in distributed databases due to the prior art.

[0016] The technical solution of the present invention is achieved in this way:

[0017] An embodiment of the present invention provides a database query optimization method, comprising: step 1: extracting data of all partitions from a database in parallel; step 2: concurrently counting the values ​​of a row label column in each partition; step 3: deduplicating the values ​​of the row label column of each partition to determine the number of rows in a result set; step 4: assigning a row number in the result set to the value of each row label column; step 5: counting all values ​​of a pivoting column to determine the number of columns in the result set; step 6: grouping partition data according to the partition value of the pivoting column corresponding to the partition dimension; step 7: counting the values ​​of the pivoting column to initialize each column of the result set; step 8: concurrently executing grouping by all pivot by columns within the group for each group, and calculating the last aggregate function for the columns in the select clause; for each grouping result, finding the row number corresponding to the row label column value and the column number corresponding to the pivoting column value, and filling the aggregate value into the corresponding position of the result set.

[0018] Optionally, extracting data of all partitions from the database in parallel includes: when the last column of all pivot by columns is a partition column and only simple columns appear in the select clause, extracting data of all partitions from the database in parallel.

[0019] Optionally, the method further includes: serially executing deduplication of values ​​of the row label column of each partition to determine the number of rows in the result set.

[0020] Optionally, count all values ​​of the pivoting column including the number of columns after the result of the group by query is expanded with the last grouping column as the horizontal axis.

[0021] Optionally, grouping the partitioned data according to the partition value of the pivoting column corresponding to the partition dimension includes: grouping the partitioned data according to the path of the partition where each data is located and the value of each data in the path.

[0022] Optionally, allocate memory space and set initial state for each column of the result set.

[0023] Optionally, the concurrent operation in step eight is implemented through a multi-threaded or distributed computing framework.

[0024] The embodiment of the present invention provides a database query optimization method, comprising: step 1: concurrently executing a group by operation on all database partitions, grouping according to all pivot by columns, and calculating a last aggregate function for each column in a select clause; step 2: merging the group by operation results of each partition; step 3: regrouping the merged results of all pivot by column pairs, wherein the pivoting column is used as the first column and the row label column is used as the subsequent column; step 4: counting all values ​​of the row label column to determine the number of rows in a result set; step 5: counting all values ​​of the pivoting column to determine the number of columns in the result set; step 6: defining a corresponding data area for each column in the merged results of all pivot by column pairs in step 3; step 7: executing in parallel for each column in the result set to obtain data in the corresponding data area; taking the row label column as the grouping column, calculating the last aggregate function for each column in the select clause again to obtain an aggregate value; determining the row number of the row label column value in the result set, and filling the aggregate value into the corresponding position of the result set.

[0025] Optionally, concurrently executing the group by operation on all database partitions includes: concurrently executing the group by operation on all database partitions through a multi-threaded or distributed computing framework.

[0026] Optionally, defining a corresponding data region for each column in the result after merging all pivot by column pairs in step three includes: grouping columns with the same pivoting column values ​​together as a group for each result column.

[0027] An embodiment of the present invention provides a database query optimization device, comprising: an extraction module, used to extract data of all partitions from a database in parallel; a first statistical module, used to concurrently count the values ​​of a row label column in each partition; a deduplication module, used to deduplicate the values ​​of the row label column of each partition and determine the number of rows in a result set; an allocation module, used to allocate a row number in a result set to the value of each row label column; a second statistical module, used to count all values ​​of a pivoting column and determine the number of columns in a result set; a grouping module, used to group partition data according to the partition value of the pivoting column corresponding to the partition dimension; a third statistical module, used to count the value of the pivoting column and initialize each column of the result set; a query module, used to concurrently execute grouping by all pivot by columns within each group, and calculate the last aggregate function for the columns in the select clause; for each grouping result, find the row number corresponding to the row label column value and the column number corresponding to the pivoting column value, and fill the aggregate value into the corresponding position of the result set.

[0028] The embodiment of the present invention provides a database query optimization device, including: a calculation module, which is used to concurrently perform a group by operation on all database partitions, grouping according to all pivot by columns, and calculating the last aggregate function for each column in a select clause; a merging module, which is used to merge the group by operation results of each partition; a grouping module, which is used to regroup the merged results of all pivot by column pairs, wherein the pivoting column is used as the first column and the row label column is used as the subsequent column; a row number determination module, which is used to count all values ​​of the row label column and determine the number of rows in a result set; a column number determination module, which is used to count all values ​​of the pivoting column and determine the number of columns in the result set; a region division module, which is used to define a corresponding data region for each column in the merged result of all pivot by column pairs; a query module, which is used to execute in parallel on each column in the result set to obtain data in the corresponding data region; using the row label column as the grouping column, calculating the last aggregate function for each column in the select clause again to obtain an aggregate value; determining the row number of the row label column value in the result set, and filling the aggregate value into the corresponding position of the result set.

[0029] The embodiment of the present invention provides a database query optimization method and device, which extracts data of all partitions from a database in parallel; concurrently counts the values ​​of a row label column in each partition; deduplicates the values ​​of the row label column of each partition to determine the number of rows in a result set; assigns a row number in the result set to the value of each row label column; counts all values ​​of a pivoting column to determine the number of columns in the result set; groups partition data according to the partition value of the pivoting column corresponding to the partition dimension; counts the value of the pivoting column to initialize each column of the result set; concurrently executes grouping by all pivot by columns within the group for each group, and calculates the last aggregate function for the columns in the select clause; for each group result, searches for the row number corresponding to the row label column value and the column number corresponding to the pivoting column value, and fills the aggregate value into the corresponding position of the result set, thereby reducing unnecessary data grouping and serial calculation, thereby improving the query efficiency and the utilization rate of computing resources. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] The drawings described herein are used to provide a further understanding of the present invention and constitute a part of this application. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation of the present invention. In the drawings:

[0031] Figure 1 A flowchart of a database query optimization method provided by an embodiment of the present invention;

[0032] Figure 2 A flowchart of another database query optimization method provided by an embodiment of the present invention;

[0033] Figure 3 A flowchart of another database query optimization method provided by an embodiment of the present invention;

[0034] Figure 4 A flowchart of another database query optimization method provided by an embodiment of the present invention;

[0035] Figure 5 A schematic diagram of a database query optimization device provided by an embodiment of the present invention;

[0036] Figure 6 A schematic diagram of another database query optimization device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0037] In order to enable those skilled in the art to better understand the scheme of the present invention, the technical scheme in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.

[0038] It should be noted that the terms "first", "second" and the like in the specification, claims and drawings of the present invention are used to distinguish different objects rather than to limit a specific order.

[0039] It should also be noted that the following embodiments of the present invention may be executed separately, or in combination with each other, and the embodiments of the present invention do not impose any specific limitations on this.

[0040] Technical terms involved in the embodiments of this application:

[0041] Partition column: For tables with large data volumes that cannot be stored on one machine, the data can be divided into blocks according to the values ​​of certain columns and stored on different machines. The columns used for block division are called partition columns. Block division can be based on the value of the partition column (value partitioning), the value of the partition column calculated by a hash function (hash partitioning), the range of the partition column value (range partitioning), etc.

[0042] Partition value: By applying the predetermined partitioning rules to the partitioning column, you can calculate which partition each row of data belongs to. The value calculated for each partitioning column is used as the partition number in the corresponding partitioning dimension, called the partition value. For example, if a distributed table has two partitioning columns, the first column is a date type value partition, and the second column is a hash partition, then the partition values ​​of all partitions in the database can be: 2020.01.01 / Key0, 2020.01.01 / Key1, 2020.01.02 / Key0

[0043] Row label column: In the pivot by clause, except for the last column, it is similar to the grouping column in the group by query, and the values ​​will not be repeated in the result.

[0044] Pivoting column: The last column in the pivot by clause. Each unique value in this column will become a column in the result.

[0045] All pivot by columns: All columns in the pivot by clause.

[0046] The embodiment of the present invention provides a database query optimization method. Figure 1 A flowchart of a database query optimization method provided by an embodiment of the present invention; Figure 1 As shown, the database query optimization method provided in the embodiment of the present application includes:

[0047] Step S100, extracting data of all partitions from the database in parallel;

[0048] Optionally, extracting data of all partitions from the database in parallel in step S100 includes: when the last column of all pivotby columns is a partition column and only simple columns appear in the select clause, extracting data of all partitions from the database in parallel.

[0049] Among them, the premise of the optimization process of the database query optimization method provided by the embodiment of the present application is: when the last column of all pivot by columns is a partition column, and only simple columns appear in the select clause.

[0050] Step S102, concurrently counting the values ​​of the row label column in each partition;

[0051] Among them, for the data of all partitions, the values ​​of the row label column are concurrently counted.

[0052] Step S104, deduplicate the values ​​of the row label column of each partition to determine the number of rows in the result set;

[0053] Optionally, the database query optimization method provided in the embodiment of the present application further includes: serially executing deduplication of the values ​​of the row label column of each partition, and determining that the number of rows in the result set is 0.

[0054] The values ​​of the row label column of each partition in step S102 are deduplicated to obtain all the values ​​of the rowlabel column of the final result and the number of rows in the result set.

[0055] Step S106, assigning a row number in the result set to each value of the row label column;

[0056] Here, a value is taken for each row label column obtained in step S104, and a corresponding row number in the result set is assigned.

[0057] Step S108, counting all values ​​of the pivoting column to determine the number of columns in the result set;

[0058] Optionally, count all values ​​of the pivoting column including the number of columns after the result of the group by query is expanded with the last grouping column as the horizontal axis.

[0059] Among them, for all partitioned data, all values ​​of the pivoting column are counted, that is, the equivalent group by query result is the number of columns after the last grouping column is expanded as the horizontal axis.

[0060] Step S110, grouping the partition data according to the partition value of the pivoting column corresponding to the partition dimension;

[0061] Optionally, grouping the partitioned data according to the partition value of the pivoting column corresponding to the partition dimension includes: grouping the partitioned data according to the path of the partition where each data is located and the value of each data in the path.

[0062] Step S112, counting the values ​​of the pivoting column and initializing each column of the result set;

[0063] Optionally, allocate memory space and set initial state for each column of the result set.

[0064] Step S114, concurrently execute grouping by all pivot by columns within each group, and calculate the last aggregate function for the columns in the select clause; for each group result, find the row number corresponding to the row label column value and the column number corresponding to the pivoting column value, and fill the aggregate value into the corresponding position of the result set.

[0065] Optionally, the concurrent operation in step S114 is implemented through a multi-threaded or distributed computing framework.

[0066] In summary, in combination with step S100 to step S114, as Figure 2 As shown, Figure 2 A flow chart of another database query optimization method provided in an embodiment of the present invention; the database query optimization method provided in the embodiment of the present application counts all values ​​of the rowlabel column, changing from single thread to multi-thread; regroups the merged results by all pivot by columns, and changes to grouping according to the partition values ​​of the partition dimension corresponding to the pivoting column, thereby reducing one unnecessary full data grouping; calculates the results of each column, changing from single thread to multi-thread. The database query optimization method provided in the embodiment of the present application can better utilize computing resources, reduce unnecessary calculations, and thus improve performance.

[0067] The embodiment of the present invention provides a database query optimization method, which extracts data of all partitions from a database in parallel; concurrently counts the values ​​of a row label column in each partition; deduplicates the values ​​of the row label column of each partition to determine the number of rows in a result set; assigns a row number in the result set to the value of each row label column; counts all values ​​of a pivoting column to determine the number of columns in the result set; groups the partition data according to the partition value of the pivoting column corresponding to the partition dimension; counts the value of the pivoting column to initialize each column of the result set; concurrently executes grouping by all pivot by columns within the group for each group, and calculates the last aggregate function for the columns in the select clause; for each group result, searches for the row number corresponding to the row label column value and the column number corresponding to the pivoting column value, and fills the aggregate value into the corresponding position of the result set, thereby reducing unnecessary data grouping and serial calculation, thereby improving the query efficiency and the utilization rate of computing resources.

[0068] The embodiment of the present invention provides a database query optimization method. Figure 3 A flowchart of another database query optimization method provided by an embodiment of the present invention; Figure 3 As shown, the database query optimization method provided in the embodiment of the present application includes:

[0069] Step S300, concurrently executing the group by operation on all database partitions, grouping according to all pivot by columns, and calculating the last aggregate function for each column in the select clause;

[0070] Optionally, concurrently executing the group by operation on all database partitions includes: concurrently executing the group by operation on all database partitions through a multi-threaded or distributed computing framework.

[0071] Step S301, merging the group by operation results of each partition;

[0072] Step S302, regrouping the merged results of all pivot by column pairs, wherein the pivoting column is used as the first column and the row label column is used as the subsequent column;

[0073] Step S303, counting all values ​​of the row label column to determine the number of rows in the result set;

[0074] Step S304, counting all values ​​of the pivoting column to determine the number of columns in the result set;

[0075] Step S305, defining a corresponding data region for each column in the result of merging all pivot by column pairs in step S302;

[0076] Optionally, defining a corresponding data region for each column in the result after merging all pivot by column pairs in step S302 includes: grouping together columns with the same values ​​of the pivoting columns as a group for each column of the result.

[0077] Step S306, execute in parallel for each column in the result set to obtain the data in the corresponding data area; use the row label column as the grouping column, calculate the last aggregation function again for each column in the select clause to obtain the aggregate value; determine the row number of the row label column value in the result set, and fill the aggregate value into the corresponding position of the result set.

[0078] In summary, in combination with step S300 to step S306, as Figure 4 As shown, Figure 4 A flowchart of another database query optimization method provided in an embodiment of the present invention; The database query optimization method provided in an embodiment of the present application is Figure 1 and Figure 2 As an alternative to the database query optimization method shown, a parallel approach is adopted when calculating by column in step S110. The database query optimization method provided in the embodiment of the present application can better utilize computing resources, reduce unnecessary calculations, and thus improve performance.

[0079] The embodiment of the present invention provides a database query optimization method, which performs group by operations on all database partitions concurrently, performs grouping according to all pivot by columns, and calculates the last aggregate function for each column in a select clause; merges the group by operation results of each partition; regroups the merged results of all pivot by column pairs, wherein the pivoting column is used as the first column and the row label column is used as the subsequent column; counts all values ​​of the row label column to determine the number of rows in a result set; counts all values ​​of the pivoting column to determine the number of columns in the result set; defines a corresponding data area for each column in the merged results of all pivot by column pairs; executes in parallel for each column in the result set to obtain data in the corresponding data area; uses the row label column as a grouping column, and calculates the last aggregate function for each column in the select clause again to obtain an aggregate value; determines the row number of the row label column value in the result set, and fills the aggregate value into the corresponding position of the result set, thereby reducing unnecessary data grouping and serial calculation, thereby improving the query efficiency and the utilization rate of computing resources.

[0080] The embodiment of the present invention provides a database query optimization device. Figure 5 A schematic diagram of a database query optimization device provided by an embodiment of the present invention; Figure 5 As shown, the database query optimization device provided by the embodiment of the present application includes: an extraction module 50, which is used to extract data of all partitions from the database in parallel; a first statistical module 51, which is used to concurrently count the values ​​of the row label column in each partition; a deduplication module 52, which is used to deduplicate the values ​​of the row label column of each partition and determine the number of rows in the result set; an allocation module 53, which is used to allocate the row number in the result set to the value of each row label column; a second statistical module 54, which is used to count all values ​​of the pivoting column and determine the number of columns in the result set; a grouping module 55, which is used to group the partition data according to the partition value of the pivoting column; a third statistical module 56, which is used to count the values ​​of the pivoting column and initialize each column of the result set; a query module 57, which is used to concurrently execute grouping by all pivotby columns within each group, and calculate the last aggregate function for the columns in the select clause; for each grouping result, find the row number corresponding to the row label column value and the column number corresponding to the pivoting column value, and fill the aggregate value into the corresponding position of the result set.

[0081] The embodiment of the present invention provides a database query optimization device, which extracts data of all partitions from a database in parallel; concurrently counts the values ​​of a row label column in each partition; deduplicates the values ​​of the row label column of each partition to determine the number of rows in a result set; assigns a row number in the result set to the value of each row label column; counts all values ​​of a pivoting column to determine the number of columns in the result set; step six: grouping partition data according to the partition value of the pivoting column; counts the value of the pivoting column to initialize each column of the result set; concurrently executes grouping by all pivot by columns within the group for each group, and calculates the last aggregate function for the columns in the select clause; for each group result, searches for the row number corresponding to the rowlabel column value and the column number corresponding to the pivoting column value, and fills the aggregate value into the corresponding position of the result set, thereby reducing unnecessary data grouping and serial calculations, thereby improving the query efficiency and the utilization of computing resources.

[0082] The embodiment of the present invention provides a database query optimization device. Figure 6 A schematic diagram of another database query optimization device provided by an embodiment of the present invention; Figure 6As shown, the database query optimization device provided by the embodiment of the present application includes: a calculation module 60, which is used to concurrently perform group by operations on all database partitions, group according to all pivot by columns, and calculate the last aggregation function for each column in the select clause; a merging module 61, which is used to merge the group by operation results of each partition; a grouping module 62, which is used to regroup the merged results of all pivot by columns, wherein the pivoting column is used as the first column and the row label column is used as the subsequent column; a row number determination module 63, which is used to count all values ​​of the row label column and determine the number of rows in the result set; a column number determination module 64, which is used to count all values ​​of the pivoting column and determine the number of columns in the result set; an area division module 65, which is used to perform group by operations on all pivot by columns. In the result after the by column pair is merged, a corresponding data area is defined for each column; a query module 66 is used to execute in parallel for each column in the result set to obtain the data in the corresponding data area; the rowlabel column is used as the grouping column, and the last aggregation function is calculated again for each column in the select clause to obtain the aggregate value; the row number of the rowlabel column value in the result set is determined, and the aggregate value is filled into the corresponding position of the result set.

[0083] The embodiment of the present invention provides a database query optimization device, which performs group by operations on all database partitions concurrently, groups all pivot by columns, and calculates the last aggregate function for each column in a select clause; merges the group by operation results of each partition; regroups the merged results of all pivot by column pairs, wherein the pivoting column is used as the first column and the row label column is used as the subsequent column; counts all values ​​of the row label column to determine the number of rows in a result set; counts all values ​​of the pivoting column to determine the number of columns in the result set; defines a corresponding data area for each column in the merged results of all pivot by column pairs; executes in parallel for each column in the result set to obtain data in the corresponding data area; uses the row label column as a grouping column, and calculates the last aggregate function for each column in the select clause again to obtain an aggregate value; determines the row number of the row label column value in the result set, and fills the aggregate value into the corresponding position of the result set, thereby reducing unnecessary data grouping and serial calculation, thereby improving the query efficiency and the utilization rate of computing resources.

[0084] The above are only preferred embodiments of the present invention and are not intended to limit the protection scope of the present invention.

Claims

1. A database query optimization method, characterized in that: include: Step 1: extracting data of all partitions from the database in parallel; wherein the extracting data of all partitions from the database in parallel includes: when the last column of all pivot by columns is a partition column and only simple columns appear in the select clause, extracting data of all partitions from the database in parallel; Step 2: Concurrently count the values ​​of the row label column in each partition; Step 3: De-duplicate the values ​​of the row label column of each partition to determine the number of rows in the result set; Step 4: Assign a row number in the result set to each value in the row label column; Step 5: Count all the values ​​of the pivoting column to determine the number of columns in the result set; wherein, counting all the values ​​of the pivoting column includes: the number of columns after the result of the group by query is expanded using the last grouping column as the horizontal axis; Step 6: grouping the partition data according to the partition value of the partition dimension corresponding to the pivoting column; wherein grouping the partition data according to the partition value of the partition dimension corresponding to the pivoting column includes: grouping the partition data according to the path of the partition where each data is located and the value of each data in the path; Step 7: Count the values ​​of the pivoting column and initialize each column of the result set; Step 8: For each group, execute the grouping by all pivot by columns concurrently within the group, and calculate the last aggregate function for the columns in the select clause; for each group result, find the row number corresponding to the row label column value and the column number corresponding to the pivoting column value, and fill the aggregate value into the corresponding position of the result set.

2. The database query optimization method according to claim 1, characterized in that: The method further comprises: The values ​​of the row label column of each partition are deduplicated in serial execution to determine the number of rows in the result set.

3. The database query optimization method according to claim 1, characterized in that: Each column of the result set is allocated memory space and set to an initial state.

4. The database query optimization method according to claim 1, characterized in that: The concurrent operation in step eight is implemented through a multi-threaded or distributed computing framework.

5. A database query optimization device, characterized in that: include: An extraction module is used to extract data of all partitions from the database in parallel; wherein the extracting data of all partitions from the database in parallel comprises: when the last column of all pivot by columns is a partition column and only simple columns appear in the select clause, extracting data of all partitions from the database in parallel; The first statistics module is used to concurrently count the values ​​of the row label column in each partition; A deduplication module is used to deduplicate the values ​​of the row label column of each partition and determine the number of rows in the result set; An allocation module, used to allocate a row number in the result set to the value of each row label column; The second statistical module is used to count all values ​​of the pivoting column and determine the number of columns in the result set; wherein, the counting of all values ​​of the pivoting column includes: the number of columns after the result of the group by query is expanded using the last grouping column as the horizontal axis; A grouping module, configured to group the partition data according to the partition value of the partition dimension corresponding to the pivoting column; wherein the grouping of the partition data according to the partition value of the partition dimension corresponding to the pivoting column comprises: grouping the partition data according to the path of the partition where each piece of data is located and the value of each piece of data in the path; A third statistical module is used to count the values ​​of the pivoting column and initialize each column of the result set; The query module is used to concurrently execute grouping by all pivot by columns within each group and calculate the last aggregate function for the columns in the select clause; for each group result, find the row number corresponding to the row label column value and the column number corresponding to the pivoting column value, and fill the aggregate value into the corresponding position of the result set.

Images