SQL中的grouping sets 子句

缘起：

• 在分析库表数据时需求是：分析基于日期dt下某个推荐位下各实验位分别的曝光uv及基于日期dt下某个推荐位下的曝光uv。实现中使用到了grouping sets子句，这里对该子句进行总结、整理以备忘。

实例解析

• grouping sets是group by 子句允许指定多个选项，其核心功能是增强group by 的功能。使用**grouping sets相当于多个group by 的sql查询结果再union。**下面对比说明：

实例一：查询每个部门的每类工作的平均工资

• 使用group by的语句

select department_id,job_id,avg(salary)
from hr.employees
group by department_id,job_id;

• 使用grouping sets子句的语句

department_id,job_id,avg(salary)
from hr.employees
group by grouping sets ((department_id,job_id));

运行结果是一样的，如下：

实例二：在实例一查询每个部门每类工作平均工资的基础上，还要查询出每个部门的平均工资。

• 不使用grouping sets子句（注意union时要添加null保证所有字段对齐）

select department_id,job_id,avg(salary)
from hr.employees
group by (department_id,job_id)
union
select department_id,null,avg(salary)
from hr.employees
group by department_id;

• 使用grouping sets子句

select department_id,job_id,avg(salary)
from hr.employees
group by grouping sets ((department_id,job_id),department_id);

以上两种写法的运行结果也是一摸一样的，如下：

实例三：
再查询每个部门每类工作平均工资与查询出每个部门的平均工资 的基础上，还要查询整个企业中雇员的平均工资

• 不使用grouping sets

select department_id,job_id,avg(salary)
from hr.employees
group by (department_id,job_id)
union
select department_id,null,avg(salary)
from hr.employees
group by department_id
union 
select null,null,avg(salary)
from hr.employees;

• 使用grouping sets

select department_id,job_id,avg(salary)
from hr.employees
group by grouping sets ((department_id,job_id),department_id,());

注意上面 grouping sets sql 中的() 表示做一个整体的聚集。
以上两种方式的查询结果一样，如下：