GROUPING SETS

該關(guān)鍵字可以實(shí)現(xiàn)同一數(shù)據(jù)集的多重group by操作。事實(shí)上GROUPING SETS是多個(gè)GROUP BY進(jìn)行UNION ALL操作的簡(jiǎn)單表達(dá)，它僅僅使用一個(gè)stage完成這些操作。GROUPING SETS的子句中如果包含()數(shù)據(jù)集，則表示整體聚合。

示例：

select name, work_space[0] as main_place, count(employee_id) as emp_id_cnt
from employee
group by name, work_space[0]
GROUPING SETS((name,work_space[0]), name, ());

// 上面語(yǔ)句與下面語(yǔ)句等效

select name, work_space[0] as main_place, count(employee_id) as emp_id_cnt
from employee
group by name, work_space[0]
UNION ALL
select name, work_space[0] as main_place, count(employee_id) as emp_id_cnt
from employee
group by name
UNION ALL
select name, work_space[0] as main_place, count(employee_id) as emp_id_cnt
from employee;

ROLLUP

擴(kuò)展了GROUTING SETS。

select a, b, c from table group by a, b, c WITH ROLLUP;
// 等價(jià)于下面語(yǔ)句
select a, b, c from table group by a, b, c
GROUPING SETS((a,b,c),(a,b),(a),());

CUBE

擴(kuò)展了GROUTING SETS，對(duì)各種條件進(jìn)行聚合。

select a, b, c from table group by a, b, c WITH ROLLUP;
// 等價(jià)于下面語(yǔ)句
select a, b, c from table group by a, b, c
GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),());

聚合條件 HAVING

having用于在組內(nèi)進(jìn)行過(guò)濾。

select cid,max(price) mx from orders group by cid having mx  > 1000;
//等價(jià)于下面的子查詢語(yǔ)句
select t.cid, t.mx from (
        select cid, max(price) mx from orders group by cid
    ) t
where t.mx > 1000;