PostgreSQL 的 GROUPING SETS，CUBE，ROLLUP

这三个都是 GROUP BY 的子句，用于简化 GROUP BY 与 UNION ALL 的配合使用。

GROUPING SETS

用 GROPING SETS 可以产生与 UNION ALL ，GROUP BY 联合。
对于要联合的查询，如果字段的个数不同，在用 UNION时，需要把 没用的字段设为 NULL，给对应起来。

SELECT
brand,
segment,
SUM (quantity)
FROM
sales
GROUP BY
    brand,
    segment
 
UNION ALL
 
SELECT
    brand,
    NULL,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand
 
UNION ALL
 
SELECT
    NULL,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    segment
 
UNION ALL
 
SELECT
    NULL,
    NULL,
    SUM (quantity)
FROM
    sales;

这样 写的语句长，且需要遍历多次表，效率低。

To make it more efficient, PostgreSQL provides the GROUPING SETS which is the subclause of the GROUP BY clause.

The GROUPING SETS allows you to define multiple grouping sets in the same query. The general syntax of the GROUPING SETS is as follows:

SELECT
    c1,
    c2,
    aggregate_function(c3)
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (c1, c2),
        (c1),
        (c2),
        ()
);

Grouping function

The GROUPING function accepts a name of a column and returns bit 0 if the column is the member of the current grouping set and 1 otherwise. See the following example:

SELECT
   GROUPING(brand) grouping_brand,
   GROUPING(segment) grouping_segement,
   brand,
   segment,
   SUM (quantity)
FROM
   sales
GROUP BY
   GROUPING SETS (
      (brand, segment),
      (brand),
      (segment),
      ()
   )
ORDER BY
   brand,
   segment;

CUBE

The query generates all possible grouping sets based on the dimension columns specified in CUBE. The CUBE subclause is a short way to define multiple grouping sets so the following are equivalent:

简写形式，自动把CUBE中的几个字段 排列组合起来。
排出所有可能的组合形式。

SELECT
    c1,
    c2,
    c3,
    aggregate (c4)
FROM
    table_name
GROUP BY
    CUBE (c1, c2, c3);

比较：
CUBE(c1,c2,c3) 
 
GROUPING SETS (
    (c1,c2,c3), 
    (c1,c2),
    (c1,c3),
    (c2,c3),
    (c1),
    (c2),
    (c3), 
    ()
 ) 

下面这样，就会返会 组合中含有c1的结果。不含有c1的就不返回了。

SELECT
    c1,
    c2,
    c3,
    aggregate (c4)
FROM
    table_name
GROUP BY
    c1,
    CUBE (c2, c3);

ROLLUP

The PostgreSQL ROLLUP is a subclause of the GROUP BY clause that offers a shorthand for defining multiple grouping sets.
Different from the CUBE subclause, ROLLUP does not generate all possible grouping sets based on the specified columns. It just makes a subset of those.

用来分组的字段，根据先后顺序，有了等级
越靠前越等级高
就是 以等级的高低来构建分组了。

The ROLLUP assumes a hierarchy among the input columns and generates all grouping sets that make sense considering the hierarchy. This is the reason why ROLLUP is often used to generate the subtotals and the grand total for reports.

the ROLLUP(c1,c2,c3) generates only four grouping sets, assuming the hierarchy c1 > c2 > c3 as follows:

(c1, c2, c3)
(c1, c2)
(c1)
()

A common use of ROLLUP is to calculate the aggregations of data by year, month, and date, considering the hierarchy year > month > date

The following statement finds the number of rental per day, month, and year by using the ROLLUP:

SELECT
    EXTRACT (YEAR FROM rental_date) y,
    EXTRACT (MONTH FROM rental_date) M,
    EXTRACT (DAY FROM rental_date) d,
    COUNT (rental_id)
FROM
    rental
GROUP BY
    ROLLUP (
        EXTRACT (YEAR FROM rental_date),
        EXTRACT (MONTH FROM rental_date),
        EXTRACT (DAY FROM rental_date)
    );

以上内容可参考：
http://www.postgresqltutorial.com/postgresql-rollup/