ROLLUP option allows you to execute only a single SQL statement to calculate the subtotal for each group in your SQL query and the grand total for all the groups. Data is divided into different groups based on dimensions. This topic describes how to use the
The PolarDB cluster version is ApsaraDB PolarDB MySQL-compatible edition 8.0 and the revision version is 18.104.22.168.0 or later. For how to confirm the cluster version, see Query the kernel version number.
ROLLUP option is an extension to the
GROUP BY clause. You need only to add
WITH ROLLUP after the columns that are specified in the
GROUP BY clause. The following example is provided:
SELECT year, country, product, SUM(profit) AS profit FROM sales GROUP BY year, country, product WITH ROLLUP;
ROLLUP option calculates an aggregation result for each column that is specified in the GROUP BY clause. The ROLLUP option also calculates high-level subtotals based on the right-to-left order of the specified columns. To be more specific, after a subtotal is calculated each time, the rightmost column is removed from the list of columns for the next calculation. This process continues until a grand total is produced. In the preceding example, the profits are calculated based on the following order of clauses:
GROUP BY year, country, product,
GROUP BY year, country, and
GROUP BY year. Then, a grand total of the profits is calculated for the entire sales table. During the calculation of the grand total, the
GROUP BY clause is ignored.
ROLLUPoption provides the following benefits:
- The ROLLUP option facilitates multi-dimensional statistical analysis and reduces the complexity of SQL queries for multi-dimensional analysis.
- The ROLLUP option improves the efficiency of processing queries.
ROLLUPoption enables the server to perform all the aggregation operations. The client can access data for only once to collect statistics. This reduces the processing loads and the network traffic on the client. If the ROLLUP option is not used, you must run multiple queries to collect the same statistics.
Test the performance of parallel queries that are improved by the ROLLUP option
PolarDB improves the capabilities of parallel queries by using the
ROLLUP option. If this option is used, multiple threads can aggregate data in parallel and produce aggregation results. This improves the efficiency of executing statements.
TPC Benchmark™H (TPC-H) is used for testing. TPC-H provides 22 SQL queries. In the following example, the first SQL query is used. In the following statement, the
ROLLUP option is added after the GROUP BY clause.
select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date_sub('1998-12-01', interval ':1' day) group by l_returnflag, l_linestatus with rollup order by l_returnflag, l_linestatus;
- When parallel queries are disabled, it takes 318.73 seconds to execute the statement.
- After parallel queries are enabled, it takes only 22.30 seconds to execute the statement. The efficiency of executing the statement is improved by more than 14 times.