The 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 ROLLUP option.

Prerequisites

The PolarDB cluster version is PolarDB for MySQL 8.0 and the revision version is 8.0.1.1.0 or later.

Syntax

The 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;

The 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.

The ROLLUP option 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.
  • The ROLLUP option 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.1
  • 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.2