旋转-3-优快云博客

本文深入探讨了SQL中分组和汇总数据的各种高级技巧，包括使用多维数据集、分组集、GROUPING和GROUPING_ID函数进行复杂的数据分析。通过实际案例，展示了如何在不同维度下进行数据透视和聚合，以及如何利用CASE语句和窗口函数来增强查询的灵活性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

多维数据集：-它为分组列的所有可能组合生成小计。

分组集：-在CHOOSEN级别生成摘要信息，不包括常规分组操作产生的行。

GROUPING，GROUPING_ID，GROUP_ID：-有助于正确理解使用汇总，多维数据集和分组集生成的解释结果。

select empno,ename,sum(sal),avg(sal) from emp group by cube(ename,empno); 
select decode(grouping(empno),1,'all empno',empno) as no,empno,ename,sum(sal),avg(sal) from emp group by cube(ename,empno);  
select decode(grouping(empno),1,'all empno',empno) as no,empno,ename,sum(sal),avg(sal) from emp group by cube(ename,empno) 
select empno,ename,avg(sal) from emp group by grouping sets(empno,ename)

来自GROUPING（JOB）的值将为1或0，具体取决于SAL的值是归因于GROUP BY还是CUBE。如果结果归因于CUBE，则该值为1，否则为0。

-------------------------------------------------- -

select deptno,
       job,
       case grouping(deptno)||grouping(job)
            when '00' then 'TOTAL BY DEPT AND JOB'
            when '10' then 'TOTAL BY JOB'
            when '01' then 'TOTAL BY DEPT'
            when '11' then 'GRAND TOTAL FOR TABLE'
       end category,
       sum(sal) sal
  from emp
 group by cube(deptno,job)
 order by grouping(job),grouping(deptno)

更多示例查询

==================

样品＃1

===========

select deptno, job, sum(sal) sal,
       grouping(deptno) deptno_subtotals,
       grouping(job) job_subtotals
  from emp
 group by cube(deptno,job)

样品＃2

===========

select ename,
        job,
        case when job = 'CLERK'
             then 1 else 0
        end as is_clerk,
        case when job = 'SALESMAN'
             then 1 else 0
        end as is_sales,
        case when job = 'MANAGER'
             then 1 else 0
        end as is_mgr,
        case when job = 'ANALYST'
            then 1 else 0
        end as is_analyst,
        case when job = 'PRESIDENT'
            then 1 else 0
        end as is_prez
   from emp
  order by 2

样品＃3

===========

select max(case deptno when 10 then ename end) d10,
        max(case deptno when 20 then ename end) d20,
        max(case deptno when 30 then ename end) d30,
        max(case job when 'CLERK' then ename end) clerks,
        max(case job when 'MANAGER' then ename end) mgrs,
        max(case job when 'PRESIDENT' then ename end) prez,
        max(case job when 'ANALYST' then ename end) anals,
        max(case job when 'SALESMAN' then ename end) sales
   from (
 select deptno, job, ename,
        row_number()over(partition by deptno order by empno) rn
   from emp
        ) x
  group by rn

同时在不同的组/分区上执行聚合

================================================== ====

select ename,deptno, count(*)over(partition by deptno) deptno_cnt, job,
count(*)over(partition by job) job_cnt,
count(*)over() total from emp

在值的移动范围内执行聚合

==========================================

select hiredate,
       sal,
       sum(sal)over(order by hiredate
                       range between 90 preceding
                         and current row) spending_pattern
  from emp e

使用小计透视结果集

===========================

select mgr,
       sum(case deptno when 10 then sal else 0 end) dept10,
       sum(case deptno when 20 then sal else 0 end) dept20,
       sum(case deptno when 30 then sal else 0 end) dept30,
       sum(case flag when '11' then sal else null end) total
  from (
select deptno,mgr,sum(sal) sal,
       cast(grouping(deptno) as char(1))||
       cast(grouping(mgr) as char(1)) flag
  from emp
 where mgr is not null
 group by rollup(deptno,mgr)
       ) x
 group by mgr

From: https://bytes.com/topic/oracle/insights/739013-pivoting-3-a