The usage of GROUPING SETS, ROLLUP, CUBE in SAP HANA

[former_member185192]

     We have a Chinese version(http://scn.sap.com/community/chinese/hana/blog/2014/06/16/sap-hana%E4%B8%ADgrouping-sets-rollup-cube...) of this document.

    In the SELECT query sentences, you can use GROUP BY sentence to group the table using the content of one or more columns and use aggregation function on groups. And here you can use GROUPING SETS to generate results of multiple groups of data in a single statement. The result is equivalent to UNION ALL the result of each group.

     Here will introduce the usage of GROUPING SETS, ROLLUP and CUBE.

Syntax：

SELECT <attributes> [, grouping_id(<attributes>) ]   [, text_filter(dimensional_attribute) ]

FROM …

GROUP BY GROUPING SETS [BEST n] [LIMIT n] [OFFSET n]

[WITH SUBTOTAL] [WITH BALANCE] [WITH TOTAL]

[TEXT_FILTER <filterspec> [FILL UP [SORT MATCHES TO TOP]]]

[STRUCTURED RESULT [WITH OVERVIEW] [PREFIX ‘value’]]

( <group>, …, <group> )

HAVING <having-filter>;

<group> := <attribute> |( <attribute>, …, <attribute> ) |(( <attribute>, …, <attribute> ) ORDER BY <attribute> [ASC] [DESC])

GROUPING SETS

     We can use GROUPING SETS to point out the columns combination used to group, which is called grouping sets.

For example, GROUPING SETS(A, B, (C,D)) represents the UNION ALL of the result  of grouping by A, by B and by C,D respectively.

     ROLLUP and CUBE are the special forms of GROUPING SETS.

ROLLUP

For example

     ROLLUP(A,B,C) is equivalent to GROUPING SETS((A,B,C),(A,B),A,()).

     That is to say, the number of grouping sets in ROLLUP is n+1

CUBE

For example:

     CUBE (A,B,C) is equivalent to GROUPING SETS((A,B,C),(A,B),(A,C),(B,C),A,B,C,()).

     Then, the number of grouping sets in CUBE is 1+C(n,1)+C(n,2)+…+C(n,n-1)+1=2ⁿ.

Examples：

     Here we design a table name SALES.

createcolumntable sales ( id int primarykey, customer varchar(5), year int, product varchar(5), sales int );

insert into sales values(1, 'C1', 2009, 'P1', 100);

insert into sales values(2, 'C1', 2009, 'P2', 200);

insert into sales values(3, 'C1', 2010, 'P1', 50);

insert into sales values(4, 'C1', 2010, 'P2', 150);

insert into sales values(5, 'C2', 2009, 'P1', 200);

insert into sales values(6, 'C2', 2009, 'P2', 300);

insert into sales values(7, 'C2', 2010, 'P1', 100);

insert into sales values(8, 'C2', 2010, 'P2', 150);

select * from sales;

GROUPING SETS

select customer, year, product, sum(sales) from sales

groupbyGROUPING SETS ( customer, year,product );

is equivalent to

select customer, NULL, NULL, sum(sales) from sales groupby customer

union all

select NULL, year, NULL, sum(sales) from sales group by year

union all

selectNULL, NULL, product, sum(sales) from sales groupby product;

ROLLUP

select customer, year, sum(sales) from sales

groupby ROLLUP(customer, year);

is equivalent to

select customer, year, sum(sales) from sales groupbygrouping sets ( (customer, year), (customer) )

union all

selectNULL, NULL, sum(sales) from sales;

CUBE

select customer, year, sum(sales) from sales

groupby CUBE(customer, year);

is equivalent to

select customer, year, sum(sales) from sales

group by grouping sets ( (customer, year), (customer), (year) )

union all

selectNULL, NULL, sum(sales) from sales;

grouping_id()/grouping()

     grouping_id() returns a integer for each grouping set to distinguish them.

     grouping() also returns a integer, and its parameter must be the name of one column. It presents if this column is used to group, returning 0 or 1.

select customer, year, sum(sales),grouping_id(customer, year),grouping(customer),grouping(year) from sales

groupby ROLLUP(customer, year);

select customer, year, sum(sales),grouping_id(customer, year),grouping(customer),grouping(year) from sales

groupby CUBE(customer, year);

Notice: in the example of CUBE, there are two columns, CUSTMOER and YEAR in grouping_id(). Then we can assume there is a binary number, the low present the column YEAR and the high present the column CUSTOMER. If in one grouping set this column is used to group, this column is set 1, if not set 0. Then return a integer as the result.

BEST

BEST n：n can be positive number, negative number or 0. Returns only the top-n grouping sets sorted in descending order of the number of rows aggregated in each grouping set. Negative number means sorting in ascending order and 0 means not using the BEST keyword.

select customer, year, product, sum(sales)

from sales groupby cube BEST 3 ( customer, year, product );

LIMIT OFFSET

LIMIT n [OFFSET m]：limit the returned grouped records and offset number for each grouping set.

select customer, year, product, sum(sales)

from sales

groupbygrouping sets LIMIT 2 OFFSET 1 ( (customer, year), (product) );

WITH SUBTOTAL/WITH BALANCE/WITH TOTAL

This can be used with the keyword LIMIT OFFSET, adding an additional subtotal in each grouping set. Using WITH SUBTOTAL, a subtotal of the part of LIMIT OFFSET will be returned. And the last will be returned when using WITH BALANCE. WITH TOTAL means to return the aggregated total value for each grouping set.

select customer, year, product, sum(sales)

from sales groupbygrouping sets LIMIT 2 WITH SUBTOTAL WITH BALANCE WITH TOTAL( (customer, year), (product) );

TEXT_FILTER

TEXT_FILTER <filterspec> means to filter the content on the grouping columns. Here we can use  wildcard characters, such as *, ?, and logical operators, such as OR, AND, NOT, AND NOT.

select customer, year, product, sum(sales), text_filter(customer), text_filter(product)

from sales groupbygrouping sets TEXT_FILTER '*2' ( (customer, year), (product) );

FILL UP

FILL UP can be used with TEXT_FILTER to return all the rows in each grouping set. And we can use text_filter() function to displays matching values.

select customer, year, product, sum(sales), text_filter(customer), text_filter(product)

from sales groupbygrouping sets TEXT_FILTER '*2' FILL UP ( (customer, year), (product) );

SORT MATCHES TO TOP

This is used with FILL UP to order the matching values to the top of each grouping set.

select customer, year, product, sum(sales), text_filter(customer), text_filter(product)

from sales groupbygrouping sets TEXT_FILTER '*2' FILL UP SORT MATCHES TO TOP ( (customer, year), (product));

STRUCTURED RESULT [WITH OVERVIEW] [PREFIX ‘value’]

     Return results as temporary tables. Using WITH OVERVIEW, an overview temporary table will also be returned. Using PREFIX can specify the prefix for the tables. The prefix must start with “#”, which means a temporary table. And the default value is “#GN”. #GN0 is the overview table, and #GNn is the result of each grouping table.

select customer, product, sum(sales)

from sales group by grouping sets structured result WITH OVERVIEW ( product, customer); 

select * from "#GN0";

select * from "#GN1";

select * from"#GN2";

MULTIPLE RESULTSETS

MULTIPLE RESULTSETS can set results to be returned in multiple result sets.

select customer, year, product, sum(sales)

from sales groupbygrouping sets MULTIPLE RESULTSETS ( (customer, year), (product) );