按不同维度组合并聚合方法

grouping sets，在一个 group by 查询中，根据不同的维度组合进行聚合，等价于将不同维度的 group by 结果集进行 union all

cube，根据 group by 的维度的所有组合进行聚合

rollup，是 cube 的子集，以最左侧的维度为主，从该维度进行层级聚合。

grouping sets

select order_id, departure_date, count(*) as cnt

from order_info

group by order_id, departure_date

grouping sets (order_id,(order_id,departure_date));

---- 等价于以下

group by order_id union all group by order_id,departure_date

 

cube

select order_id, departure_date, count(*) as cnt

from order_info

group by order_id, departure_date

with cube;

---- 等价于以下

select count(*) as cnt from order_info

union all group by order_id

union all group by departure_date

union all group by order_id,departure_date