Oracle分组函数之ROLLUP的大旨用法,用法实例详细解释

rollup函数

Group by的语法
Select [filed1,fild2,]聚合函数(filed),
[Grouping(filed),]
[Grouping_id(filed1,filed2,…)]
From tablename
Where condition
[Group by {rollup|cube}(filed,filed2)]
[having condition]
[order by filed1]
生龙活虎、基本用法:

group by 用来在原有数据上创办聚合来将数据转发为使得的音讯。

本博客简要介绍一下oracle分组函数之rollup的用法,rollup函数常用于分组总计,也是归属oracle分析函数的后生可畏种

(1)我们经过多少个例证来商量groupby的基本用法

着力的group by 列出个个部门的称谓,工作者业总会数

景况策动

始建测验表
SQL> create table sales(
2 empid number, –雇员ID
3 depid number, – -部门ID
4 area varchar(20), –区域
5 salenum number); –销售额

表已创建。

SQL> insert into sales values(1,1,’china’,10);
SQL> insert into sales values(2,1,’china’,10);
SQL> insert into sales values(3,1,’china’,10);
SQL> insert into sales values(3,1,’china’,10);
SQL> insert into sales values(3,1,’china’,10);
SQL> insert into sales values(1,1,’china’,10);
SQL> insert into sales values(2,1,’china’,10);
SQL> insert into sales values(4,2,’china’,10);
SQL> insert into sales values(4,2,’china’,10);
SQL> insert into sales values(5,3,’us’,10);
SQL> insert into sales values(5,3,’us’,10);
急需1,按机关总结划发售售额 (简单用法卡塔尔(قطر‎
SQL> select depid,sum(salenum) from sales group by depid;
DEPID SUM(SALENUM)


1        70
2        20
3        20
必要2,按机关总结划发卖售额,何况只展现贩卖总额小于30的机关及出售额(使用having子句)
SQL> select depid,sum(salenum) totalnum from sales
group by depid
having sum(salenum) <30;
DEPID SUM(SALENUM)


2        20
3        20
申明:须要2亟需采纳having字名,并且在子句中不可能使用小名,必得选拔在select语句中书写的样式

select d.dname, count(empno) empcount
  from scott.dept d
  left outer join scott.emp e
    on d.deptno = e.deptno
 group by d.dname
 order by d.dname;
create table dept as select * from scott.dept;create table emp as select * from scott.emp;

(2)Where 和having的区别

select列表中的每一列必需带有在group by子句中。若无则会形成错误。如:

业务场景:求各部门的薪金总额及其具有单位的薪水总数

Wheret和having子句都用于筛选数据,不过where是针对原数据开展筛选,而having子句只是针对汇中国人民解放军总后勤部的结果实行筛选,所以在需要二的例证中,想要对发卖总额进行过滤只可以动用having子句

SQL> select d.dname, d.loc, count(empno) empcount
  2    from scott.emp e
  3     join scott.dept d
  4      on d.deptno = e.deptno
  5   group by d.dname;
select d.dname, d.loc, count(empno) empcount
                *
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式

此地可以用union来做,先按单位计算薪水之和,然后在总括全部机构的工钱之和

(3)使用order by 排序

尽管满含group
by子句的select语句输出看上去是按顺类别出的,你不能够仰望group
by每一次都排好序地再次回到数据,假若出口结果必需依据一定的顺序排列,则必得选择order
by子句。

select a.dname, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by a.dnameunion allselect null, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno;

SQL> select depid,sum(salenum) from sales group by depid;
DEPID SUM(SALENUM)


1       70
2       20
3       20
留意观望需要1的事例,depid是已经遵照在depid升序排列的,这是因为oracle在做聚合总括的时候会率先对字段举办排序,所以最后的结果是比照升序举行排列的,倘使order
by后随着多少个字段,默许排序是先对第一个字段升序排序,然后再排第4个字段,由此及彼,所以倘若在行使中仅仅须要长序排序能够不要加order
by 参数,究竟那会耳濡目染属性

--没有排序的group by 
select deptno,count(*)
from emp
group by deptno;

--复杂的sql
set serveroutput off;
--复杂的sql
select /* lst7-4 */
distinct dname,
         decode(d.deptno,
                1,
                (select count(*) from emp where deptno = 1),
                2,
                (select count(*) from emp where deptno = 2),
                3,
                (select count(*) from emp where deptno = 3),
                (select count(*) from emp where deptno not in (1, 2, 3))) dept_count
  from (select distinct deptno from emp) d
  join dept d2
    on d.deptno = d.deptno;

@E:bjc2016studypln lst7-4

地点是用union来做,然后用rollup来做,语法更简约,而且质量越来越好

二、扩张用法:

地点的写法,会使SQL语句特别目眩神摇难以精通况且难以保证。group
by子句十分的大的简化必得写的sql语句以外,还免去了数据库不要求的IO。

select a.dname, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by rollup(a.dname);

扩大用法使用下边的表实行尝试商量
SQL> create table testgroup(
2 a varchar(5),
3 b varchar(5),
4 c varchar(5),
5 n number);
建完测量试验表,然后插入两条测量检验数据
SQL> insert into testgroup values(‘a1′,’b1′,’c1’,10);
SQL> insert into testgroup values(‘a1′,’b1′,’c1’,20);
咱俩应用基本的group by 能够获得以下结果
SQL> select a,b,c,sum(n) total from testgroup group by a,b,c;
A        B      C     TOTAL

set serveroutput off;
--复杂的sql
select /* lst7-5 */
distinct dname,
         count(empno) empcount
  from  emp e
  join dept d
    on d.deptno = d.deptno
    group by d.dname
    order by d.dname;

@E:bjc2016studypln lst7-5

政工场景:基于下边包车型大巴总括,再加须求,现在要会见每个机关岗位对应的薪水之和


group by 优点:

select a.dname, b.job, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by a.dname, b.jobunion all//各部门的工资之和select a.dname, null, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by a.dnameunion all//所有部门工资之和select null, null, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno;

a1      b1     c1     30
(1)使用rollup操作符

  • 使sql语句更具备可读性
  • 挥洒起来比使用过多相关子查询更简约
  • 调整和减弱了双重访谈同三个数据块的次数,进而获取越来越好的性质。

用rollup达成,语法更简短

Rollup意思有”卷起,汇总”的情致,他得以在驱动在其括号中的字段,按从右到左的逐个分别group后展示,相似我们用七个group
by 语句,然后union
all起来,大家把针对地点的测量检验表,使用rollup操作符,看看效果
SQL> select a,b,c,sum(n) total from testgroup group by
rollup(a,b,c);
Result:

A B C TOTAL
a1 b1 c1 30
a1 b1 30
a1 30
30

从地点结果能够看来,
除了对(a1,b1,c1卡塔尔(قطر‎进行了汇总外,又从右向左分别对子句中的”a,b,c”字段举行了汇总,比如(a1,b1卡塔尔国,(a1State of Qatar(卡塔尔国

在分组之后,还在数量集上应用了having子句。另一面,在获得数据行之后,进行分组在此以前,应用了where子句。having
子句中得以接受运算,函数及子查询。

select a.dname, b.job, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by rollup(a.dname, b.job);

(2)使用cube操作符

SQL> --having子句
SQL> select /* lst7-6 */
  2   d.dname, trunc(e.hiredate, 'yyyy') hiredate, count(empno) empcount
  3    from emp e
  4    join dept d
  5      on e.deptno = e.deptno
  6   group by d.dname, trunc(e.hiredate, 'yyyy')
  7  having count(empno) <= 5 and trunc(e.hiredate, 'yyyy') between (select min(hiredate)
  8                                                                    from scott.emp) and (select max(hiredate)
  9                                                                                           from scott.emp)
 10   order by d.dname;

未选定行

SQL> @E:bjc2016studypln lst7-6
原值    8:        WHERE UPPER(SQL_TEXT) LIKE '%&1%'
新值    8:        WHERE UPPER(SQL_TEXT) LIKE '%lst7-6%'

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0wcfknkztdxqt, child number 0
-------------------------------------
select /* lst7-6 */  d.dname, trunc(e.hiredate, 'yyyy') hiredate,
count(empno) empcount   from emp e   join dept d     on e.deptno =
e.deptno  group by d.dname, trunc(e.hiredate, 'yyyy') having
count(empno) <= 5 and trunc(e.hiredate, 'yyyy') between (select
min(hiredate)
        from scott.emp) and (select max(hiredate)
                                                                   from
scott.emp)  order by d.dname


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 239717969

---------------------------------------------------------------------------
| Id  | Operation              | Name | E-Rows |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |        |       |       |          |
|*  1 |  FILTER                |      |        |       |       |          |
|   2 |   SORT GROUP BY        |      |      1 |  2048 |  2048 | 2048  (0)|
|   3 |    MERGE JOIN CARTESIAN|      |     40 |       |       |          |
|   4 |     TABLE ACCESS FULL  | DEPT |      4 |       |       |          |
|   5 |     BUFFER SORT        |      |     10 |  2048 |  2048 | 2048  (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   6 |      TABLE ACCESS FULL | EMP  |     10 |       |       |          |
|   7 |   SORT AGGREGATE       |      |      1 |       |       |          |
|   8 |    TABLE ACCESS FULL   | EMP  |     10 |       |       |          |
|   9 |   SORT AGGREGATE       |      |      1 |       |       |          |
|  10 |    TABLE ACCESS FULL   | EMP  |     10 |       |       |          |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((COUNT(*)<=5 AND TRUNC(INTERNAL_FUNCTION("E"."HIREDATE"),'

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
              fmyyyy')>= AND TRUNC(INTERNAL_FUNCTION("E"."HIREDATE"),'fmyyyy')<=))


Note
-----
   - Warning: basic plan statistics not available. These are only collected when
:

       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system leve

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
l



已选择41行。

SQL>

生机勃勃经再加个时间总计的,能够用上边sql:

Cube意思是立方,使用该操作符能够对操作符内的字段,进行遍历组合汇总,举个例子cube(a,b,c卡塔尔国,那么就能够产生8种组成结果,分别如下”a-b-c”,”a-b”,”a”,”a-c”,”
b-c”,”b”,”c”,”空”,看上边包车型客车事例
SQL> select a,b,c,sum(n) total from testgroup group by
cube(a,b,c);

group by的cube扩展

当与group
by子句一同行使时,将会使得对每少年老成行都要思量满含在cube的参数中的全体望的要素构成。这一个运算将会生成比表中实际上存在的行数越多的数据行。

-- hr.emplyees表的cube运算
select last_name, first_name
  from hr.employees
 group by first_name, last_name;

set autotrace off;
set autotrace on statistics;
with emps as
 (select /* lst-7 */
   last_name, first_name
    from hr.employees
   group by cube(last_name, first_name))
select rownum, last_name, first_name from emps;

对于每意气风发对last_name,first_name,cube将会按顺序为各类成分更替为null值。cube生成的数据行在Oracle文书档案中称之为一流聚合行,能够在运算列中参加null值来分辨。

SQL> set autotrace off;
SQL> --预测cube返回行数
SQL> with counts as
  2   (select count(distinct first_name) first_name_count,
  3           count(distinct last_name) last_name_count,
  4           count(distinct(first_name || last_name)) full_name_count
  5      from hr.employees)
  6  select first_name_count,
  7         last_name_count,
  8         full_name_count,
  9         first_name_count + last_name_count + full_name_count + 1 total_count
 10    from counts;

FIRST_NAME_COUNT LAST_NAME_COUNT FULL_NAME_COUNT TOTAL_COUNT
---------------- --------------- --------------- -----------
              91             102             107         301

下边用SQL语句模拟cube,可以见到cube为大家节省了广马来亚力。

--用union all生成cube数据行
with emps as (
 select last_name,first_name from hr.employees
),
mycube as (
       select last_name,first_name from emps
       union all
       select last_name,null first_name from emps
       union all
       select null last_name,first_name from emps
       union all
       select null last_name,null first_name from emps
)
select /*+ gather_plan_statistics */ * 
from mycube group by last_name,first_name;
select to_char(b.hiredate, 'yyyy') hiredate, a.dname, b.job, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by rollup(to_char(b.hiredate, 'yyyy'), a.dname, b.job);

        Result:

cube实际应用

sales_history情势中隐含一九九八~2000年的行销数量。
下边包车型地铁SQL体现二〇〇二年的享有出售数量。并想要查看各样付加物种类的行销售市场合汇总,包括基于10年消费者年龄段,收入水平的联谊;根据收入水平而不构思年龄的汇聚;以致按年龄而不思忖收入水平的成团。

--销售数据的union all查询
with tsales as
 (select /* lst7-10 */
   s.quantity_sold,
   s.amount_sold,
   to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
   to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
   nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
   p.prod_name,
   p.prod_desc,
   p.prod_category,
   (pf.unit_cost * s.quantity_sold) total_cost,
   s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
    from sh.sales s
    join sh.customers c
      on c.cust_id = s.cust_id
    join sh.products p
      on p.prod_id = s.prod_id
    join sh.times t
      on t.time_id = s.time_id
    join sh.costs pf
      on pf.channel_id = s.channel_id
     and pf.prod_id = s.prod_id
     and pf.promo_id = s.promo_id
     and pf.time_id = s.time_id
   where (t.fiscal_year = 2001)),
gb as
 (select --Q1 - 所有分类通过收入和年龄范围
   'Q1' query_tag,
   prod_category,
   cust_income_level,
   age_range,
   sum(profit) profit
    from tsales
   group by prod_category, cust_income_level, age_range
  union all
  select --Q2 - 所有分类通过年龄范围
   'Q2' query_tag,
   prod_category,
   'ALL INCOME' cust_income_level,
   age_range,
   sum(profit) profit
    from tsales
   group by prod_category, 'ALL INCOME', age_range
  union all
  select --Q3 - 所有分类通过收入
   'Q3' query_tag,
   prod_category,
   cust_income_level,
   'ALL AGE' age_range,
   sum(profit) profit
    from tsales
   group by prod_category, cust_income_level, 'ALL AGE'
  union all
  select --Q4 - 所有分类
   'Q4' query_tag,
   prod_category,
   'ALL INCOME' cust_income_level,
   'ALL AGE' age_range,
   sum(profit) profit
    from tsales
   group by prod_category, 'ALL INCOME', 'ALL AGE'
  )
select * from gb order by prod_category, profit;

【语法】NVL (expr1, expr2)
【功能】若expr1为NULL,返回expr2;expr1不为NULL,返回expr1。
用心两者的连串要黄金年代律
nvl(c.cust_income_level, ‘A: below 30,000’) cust_income_level
若c.cust_income_level为null,则返回’A: below 30,000′
mod(x,y)
【功能】返回x除以y的余数
【参数】x,y,数字型表明式
【返回】数字
【示例】
select mod(23,8),mod(24,8) from dual;
返回:7,0
to_char(mod(c.cust_year_of_birth, 10) * 10) || ‘_’ ||
to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range
地方是求年龄段,若是56,则求出的限量为50_60

--用cube代替union all
with tsales as
 (select /* lst7-11 */
   s.quantity_sold,
   s.amount_sold,
   to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
   to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
   nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
   p.prod_name,
   p.prod_desc,
   p.prod_category,
   (pf.unit_cost * s.quantity_sold) total_cost,
   s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
    from sh.sales s
    join sh.customers c
      on c.cust_id = s.cust_id
    join sh.products p
      on p.prod_id = s.prod_id
    join sh.times t
      on t.time_id = s.time_id
    join sh.costs pf
      on pf.channel_id = s.channel_id
     and pf.prod_id = s.prod_id
     and pf.promo_id = s.promo_id
     and pf.time_id = s.time_id
   where (t.fiscal_year = 2001))
select 'Q' || decode(cust_income_level,
                     null,
                     decode(age_range, null, 4, 3),
                     decode(age_range, null, 2, 1)) query_tag,
       prod_category,
       cust_income_level,
       age_range,
       sum(profit) profit
  from tsales
 group by prod_category, cube(cust_income_level, age_range)
 order by prod_category, profit;

decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)
【效率】根据标准重返相应值
【参数】c1, c2, …,cn,字符型/数值型/日期型,必需类型相像或null
注:值1……n 不可能为条件表明式,这种情景只可以用case when then end消除
·含义解释:  
  decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)  
  该函数的意义如下:  
  IF 条件=值1 THEN
  RETURN(翻译值1)
  ELSIF 条件=值2 THEN
  RETURN(翻译值2)
  ……
  ELSIF 条件=值n THEN
  RETURN(翻译值n)  
  ELSE
  RETURN(缺省值)
  END IF
  
或:
  when case 条件=值1 THEN
  RETURN(翻译值1)
  ElseCase 条件=值2 THEN
  RETURN(翻译值2)
  ……
  ElseCase 条件=值n THEN
  RETURN(翻译值n)  
  ELSE

RETURN(缺省值)
  END
‘Q’ || decode(cust_income_level, null,decode(age_range, null, 4,
3),decode(age_range, null, 2, 1)) query_tag
是回到查询分类标志cust_income_level为null返返回decode(age_range,
null, 4, 3卡塔尔国不然再次来到decode(age_range, null, 2, 1)
cust_income_level==null and age_range==null,query_tag=4
cust_income_level==null and age_range!=null,query_tag=3
cust_income_level!=null and age_range==null,query_tag=2
cust_income_level!=null and age_range!=null,query_tag=1

cube函数

A B C TOTAL
30
c1 30
b1 30
b1 c1 30
a1 30
a1 c1 30
a1 b1 30
a1 b1 c1 30

(3),使用grouping(filed)函数

动用grouping 函数必得先清楚rollup
和cube操作符,那么grouping函数有怎么样用啊?在普通使用中,大家通过rollup只怕cube对集中举行了汇总,汇中国人民解放军总后勤部的结果往往要传递到利用程序端,在应用程序端大家亟必要有叁个基于来决断某行数据是还是不是遵守rollup或cube实行聚集,grouping函数可以用来发出这一个依靠,他得以收到叁个参数,决断该参数是或不是为null,是则赶回1,不然重临0,小编样可感到此来剖断该是不是按某列举办聚焦执会侦察总结局计的,当然在实验应用中,0和1看起来不那么直观,大家能够利用decode只怕case函数进行转议,让翻看结果看起来越来越直观,请看以下例子
SQL> select grouping(a) ca,grouping(b) cb,grouping(c) cc,
a,b,c,sum(n) from testgroup group by rollup(a,b,c);
Result:

CA CB CC A B C SUM(N)
0 0 0 a1 b1 c1 30
0 0 1 a1 b1 30
0 1 1 a1 30
1 1 1 30

(4)使用grouping_id(filed1,file2,…)函数

利用grouping函数不时候认为不是那么灵活的,他一定要收取贰个字段,而grouping_id(卡塔尔函数则足以收到多少个字段,GROUPING_ID(卡塔尔国函数能够选用一列或多列,再次来到按GROUPING位向量举行测算的十进制值。下边大家经过叁个事例来商讨grouping_id函数是怎么样遵照grouping函数的位向量进行总括的。
SQL> select grouping(a) ca,grouping(b) cb,grouping_id(a,b) caandb,
a,b,sum(n) from testgroup group by rollup(a,b);
Result:

CA CB CAANDB A B SUM(N)
0 0 0 a1 b1 30
0 1 1 a1 30
1 1 3 30

位向量总结方法:如上例,
先是行,CA=0,CB=0,那么位向量正是“00”,换算成十进制是0
其次行,CA=0,CB=1,那么位向量正是‘01’,换算成十进制是1
其三行,CA=1,CB=1,那么位向量就是‘11’,换算成十进制是3
请在意上例中grouping_id的总结值跟括号内的字段顺序有关,上例中书写顺序是grouping_id(a,b卡塔尔(قطر‎,a字段在日前,假若换下顺序grouping_id(b,a卡塔尔(قطر‎,计算结果是区别等的,看下例
SQL> select grouping(a) ca,grouping(b) cb,grouping_id(b,a) caandb,
a,b,sum(n) from testgroup group by rollup(a,b);
Result:

CA CB CAANDB A B SUM(N)
0 0 0 a1 b1 30
0 1 2 a1 30
1 1 3 30

看看第二行浅灰字体,grouping_id中的字段顺序发生了变化,位向量值也不意气风发致了
(4-1) grouping_id()函数的用途
上边讲了grouping_id的用法,但在常常职业中,大家什么样行使该函数呢?其实只要精晓了她的规律及用法,要怎么用,就看大家是或不是足以灵活应用了,上边介绍生机勃勃种普及的用法:
即使大家要对某testgroup举办分组总括,並且过滤掉不蕴涵小计或累积的行,这个时候grouping_id就有发挥专长了,大家能够行使grouping_id的值结合having子句,通过判定grouping_id是不是大于0来过滤掉不需求的行。
SQL> select grouping(a) ca,grouping(b) cb,grouping_id(a,b) caandb,
a,b,sum(n) from testgroup group by rollup(a,b) having
grouping_id(a,b)>0;
Result:

CA CB CAANDB A B SUM(N)
0 1 1 a1 30
1 1 3 30

用grouping(卡塔尔函数清除空值

上面的SQL有个难点,纵然总行数与从前运用union
all运算符所获得的相平等,一些多少行中的cust_income_level和age_range具备空值,并且有后生可畏行的这两列都为空值。当cube的参数中富含生成列的享有希望构成时,每一列都有会产生n-1个空值,n为列表中的数目。在查询的例子中有五个例,由此对此各类唯生龙活虎的age_range值都会在cust_income_level列上发生空值。对于age_range列来说也适用相像的平整。倘诺这两列中的数据在少数行上原来就有空值,那几个空值就恐怕出问题。怎么样识别数据中原来的空值和cube扩张所插入的值吗?在oracle
8i中引进了grouping(卡塔尔函数,能够用来甄别那么些超聚合行。被用来作为grouping(卡塔尔国函数参数的表明式必得与出新在group
by子句中的表明式相匹配。举个例子
decode(grouping(age_range),1,'ALL AGE',age_range) age_range
age_range检测age_range是还是不是有风流罗曼蒂克行由cube爆发的空值,或然是还是不是其在数据库中自己正是空值。假设当前进是由cube生成的超聚合行则再次回到值为1,对于任何具有情状再次来到值都为0。
当与case(卡塔尔国表明式或decode(State of Qatar函数组适当时候,超聚合行中的空值能够用八个告知中央银卓有成效的值替换。这种景色下,decode(卡塔尔看上去是更好的选料,因为它更便利并且grouping(卡塔尔国函数唯有二种可能的再次回到值。

--grouping()函数
--无grouping
with tsales as
 (select /* lst7-11 */
   s.quantity_sold,
   s.amount_sold,
   to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
   to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
   nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
   p.prod_name,
   p.prod_desc,
   p.prod_category,
   (pf.unit_cost * s.quantity_sold) total_cost,
   s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
    from sh.sales s
    join sh.customers c
      on c.cust_id = s.cust_id
    join sh.products p
      on p.prod_id = s.prod_id
    join sh.times t
      on t.time_id = s.time_id
    join sh.costs pf
      on pf.channel_id = s.channel_id
     and pf.prod_id = s.prod_id
     and pf.promo_id = s.promo_id
     and pf.time_id = s.time_id
   where (t.fiscal_year = 2001))
select 'Q' || decode(cust_income_level,
                     null,
                     decode(age_range, null, 4, 3),
                     decode(age_range, null, 2, 1)) query_tag,
       prod_category,
       cust_income_level,
       age_range,
       sum(profit) profit
  from tsales
 group by prod_category, cube(cust_income_level, age_range)
 order by prod_category, profit;

--有grouping
--case和decode都可以工作,我更喜欢用decode
with tsales as
 (select /* lst7-12 */
   s.quantity_sold,
   s.amount_sold,
   to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
   to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
   nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
   p.prod_name,
   p.prod_desc,
   p.prod_category,
   (pf.unit_cost * s.quantity_sold) total_cost,
   s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
    from sh.sales s
    join sh.customers c
      on c.cust_id = s.cust_id
    join sh.products p
      on p.prod_id = s.prod_id
    join sh.times t
      on t.time_id = s.time_id
    join sh.costs pf
      on pf.channel_id = s.channel_id
     and pf.prod_id = s.prod_id
     and pf.promo_id = s.promo_id
     and pf.time_id = s.time_id
   where (t.fiscal_year = 2001))
select 'Q' || decode(cust_income_level,
                     null,
                     decode(age_range, null, 4, 3),
                     decode(age_range, null, 2, 1)) query_tag,
       prod_category,
       case grouping(cust_income_level)
         when 1 then
          'ALL INCOME'
         else
          cust_income_level
       end cust_income_level,
       decode(grouping(age_range), 1, 'ALL AGE', age_range) age_range,
       sum(profit) profit
  from tsales
 group by prod_category, cube(cust_income_level, age_range)
 order by prod_category, profit;
select a.dname, b.job, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by cube(a.dname, b.job);

用grouping(卡塔尔(قطر‎扩大报告

另意气风发种接纳grouping(卡塔尔国的法子是投身having子句中,用来支配在输出中显示哪个层级的汇合。
动用grouping(卡塔尔国函数可以被浓缩为对cube扩大中的各行或有所行开展滚动小计。

--在having子句中进行grouping()
with tsales as
 (select /* lst7-13 */
   s.quantity_sold,
   s.amount_sold,
   to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
   to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
   nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
   p.prod_name,
   p.prod_desc,
   p.prod_category,
   (pf.unit_cost * s.quantity_sold) total_cost,
   s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
    from sh.sales s
    join sh.customers c
      on c.cust_id = s.cust_id
    join sh.products p
      on p.prod_id = s.prod_id
    join sh.times t
      on t.time_id = s.time_id
    join sh.costs pf
      on pf.channel_id = s.channel_id
     and pf.prod_id = s.prod_id
     and pf.promo_id = s.promo_id
     and pf.time_id = s.time_id
   where (t.fiscal_year = 2001))
select 'Q' || decode(cust_income_level,
                     null,
                     decode(age_range, null, 4, 3),
                     decode(age_range, null, 2, 1)) query_tag,
       prod_category,
       case grouping(cust_income_level)
         when 1 then
          'ALL INCOME'
         else
          cust_income_level
       end cust_income_level,
       decode(grouping(age_range), 1, 'ALL AGE', age_range) age_range,
       sum(profit) profit
  from tsales
 group by prod_category, cube(cust_income_level, age_range)
 --having grouping(cust_income_level)=1
 --having grouping(age_range)=1
 having grouping(cust_income_level)=1 and grouping(age_range)=1
 order by prod_category, profit;

上边sql的数据足以见见将grouping(卡塔尔应用到cust_income_level列对全部age_range值跨各样收入等级次序成立聚合。对age_range列举行如此的操作会博得雷同的效应,对全体cust_income_level值举办联谊而不酌量age_range的值。将cube扩大中的全部列作为grouping(State of Qatar函数的参数将会形成聚合被减弱为大器晚成行雷同sum(profit卡塔尔和group
by
prod_category所达成的效劳。但是,使用cube扩展轻易改善having子句就足以成立几份差别的告知。

cube

用grouping_id(State of Qatar扩大报告

grouping_id(卡塔尔函数绝对grouping(卡塔尔函数来讲是相对较新的,在oracle
9i中引入,与grouping(卡塔尔(قطر‎函数在某种程度上是近乎的。差别的是grouping(卡塔尔总计一个表明式并再次回到0或1,而grouping_id(卡塔尔(قطر‎总计一个表明式,明确其参数中的哪生机勃勃行(借使部分话)用来扭转超聚合行,然后成立三个位矢量,并将该值作为整形值重回。

--group_id()位矢量
with rowgen as (
     select 1 bit_1,0 bit_0
     from dual
), cubed as (
 select 
 grouping_id(bit_1,bit_0) gid,
 to_char(grouping(bit_1)) bv_1,
 to_char(grouping(bit_0)) bv_0,
 decode(grouping(bit_1),1,'GRP BIT 1') gb_1,
 decode(grouping(bit_0),1,'GRP BIT 0') gb_0
 from rowgen
 group by cube(bit_1,bit_0)
)
select gid,bv_1 || bv_0 bit_vector,
gb_1,
gb_0
from cubed
order by gid;

图片 1

group_id(卡塔尔国位矢量运维结果

大家己经理解怎样行使grouping(State of Qatar通过having子句来决定输出,但寻思数据库效能时,单独的grouping_id(State of Qatar调用能够用来顶替全体分歧的having
grouping(卡塔尔子句。grouping(卡塔尔函数的效劳仅仅用来辨别数据行,因为它仅能重临0或1。由于grouping_id(State of Qatar函数重回八个基于位矢量的数值,它能够轻松被用来進展各个分裂的可比而不用修改sql语句。
为啥要关怀不改动SQL语句就能够匡正相比较呢?如上边基于发售历史的例子中,客户可能会被交付4个出口选项,猖獗二个或四个可能会被入选。客户的挑肥拣瘦能够用来作为利用having
grouping_id(卡塔尔函数的二个单身的sql语句,并不是基于having
grouping(卡塔尔的例外组全的多个sql语句的输入,由此必要数据库剖判sql语句的次数也比就少之甚少。同一时候那也会使得需求实施的sql语句更加少,使用更加小的IO,以至越来越少的内部存款和储蓄器。
正如运用cube来防止通过union
all将多少个sql语句结合起来一样,grouping_id(卡塔尔能够幸免在运用中运用八个sql语句。

--显示所有收入层次和年龄段的聚合
variable N_ALL_DATA number 
--显示所有年龄段的聚合
variable N_AGE_RANGE number 
--显示所有收入层次的聚合
variable N_INCOME_LEVEL number 
--只给出汇总
variable N_SUMMAY number 
begin
 :N_ALL_DATA      := 0;      -- 1 生效
 :N_AGE_RANGE     := 2;      -- 2 生效
 :N_INCOME_LEVEL  := 0;      -- 3 生效
 :N_SUMMAY        := 4;      -- 4 生效
end;
/
with tsales as
 (select /* lst7-15 */
   s.quantity_sold,
   s.amount_sold,
   to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
   to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
   nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
   p.prod_name,
   p.prod_desc,
   p.prod_category,
   (pf.unit_cost * s.quantity_sold) total_cost,
   s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
    from sh.sales s
    join sh.customers c
      on c.cust_id = s.cust_id
    join sh.products p
      on p.prod_id = s.prod_id
    join sh.times t
      on t.time_id = s.time_id
    join sh.costs pf
      on pf.channel_id = s.channel_id
     and pf.prod_id = s.prod_id
     and pf.promo_id = s.promo_id
     and pf.time_id = s.time_id
   where (t.fiscal_year = 2001))
select 'Q' || to_char(grouping_id(cust_income_level,age_range)+1) query_tag,
prod_category,
decode(grouping(cust_income_level),1,'ALL INCOME',cust_income_level) cust_income_level,
decode(grouping(age_range),1,'ALL AGE',age_range) age_range, 
sum(profit) profit
from tsales
group by prod_category,cube(cust_income_level,age_range)
having grouping_id(cust_income_level,age_range)+1 in(:N_ALL_DATA,:N_AGE_RANGE,:N_INCOME_LEVEL,:N_SUMMAY)
order by prod_category,profit;

利用grouping函数也能够兑现同的结果,但要求在having子句中开展局地测量试验。示例发卖历史数据查询在cube参数中只含有两列。在having子句中生龙活虎共必要进行4次测量检验,因为grouping子句将会回到1只怕0,每一列有八个也许的值。进而供给4次测量试验。倘诺3列,则必要8次,所需的测量检验次数将会是2的n次方,个中n为cube中参数列或表明式的个数。

用grouping()代替grouping_id(卡塔尔国的having子句的事例

--显示所有收入层次和年龄段的聚合
variable N_ALL_DATA number 
--显示所有年龄段的聚合
variable N_AGE_RANGE number 
--显示所有收入层次的聚合
variable N_INCOME_LEVEL number 
--只给出汇总
variable N_SUMMAY number 
begin
 :N_ALL_DATA      := 0;      -- 1 生效
 :N_AGE_RANGE     := 2;      -- 2 生效
 :N_INCOME_LEVEL  := 0;      -- 3 生效
 :N_SUMMAY        := 4;      -- 4 生效
end;
/
with tsales as
 (select /* lst7-16 */
   s.quantity_sold,
   s.amount_sold,
   to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
   to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
   nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
   p.prod_name,
   p.prod_desc,
   p.prod_category,
   (pf.unit_cost * s.quantity_sold) total_cost,
   s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
    from sh.sales s
    join sh.customers c
      on c.cust_id = s.cust_id
    join sh.products p
      on p.prod_id = s.prod_id
    join sh.times t
      on t.time_id = s.time_id
    join sh.costs pf
      on pf.channel_id = s.channel_id
     and pf.prod_id = s.prod_id
     and pf.promo_id = s.promo_id
     and pf.time_id = s.time_id
   where (t.fiscal_year = 2001))
select 'Q' || to_char(grouping_id(cust_income_level,age_range)+1) query_tag,
prod_category,
decode(grouping(cust_income_level),1,'ALL INCOME',cust_income_level) cust_income_level,
decode(grouping(age_range),1,'ALL AGE',age_range) age_range, 
sum(profit) profit
from tsales
group by prod_category,cube(cust_income_level,age_range)
having 
 (bin_to_num(grouping(cust_income_level),grouping(age_range))+1 = :N_ALL_DATA)
 or (bin_to_num(grouping(cust_income_level),grouping(age_range))+1 = :N_AGE_RANGE)
 or (bin_to_num(grouping(cust_income_level),grouping(age_range))+1 = :N_INCOME_LEVEL)
 or (bin_to_num(grouping(cust_income_level),grouping(age_range))+1 = :N_SUMMAY)
order by prod_category,profit;

1. 利用grouping能够判别该行是数据库中自然的行,依旧有计算发生的行
grouping值为0时验证那几个值是数据库中本来的值,为1表达是计算的结果(也能够说该列为空时是1,不为空时是0State of Qatar
2. GROUPING_ID(卡塔尔国函数可以接收一列或多列,重回GROUPING位向量的十进制值。GROUPING位向量的乘除办法是将根据顺序对每一列调用GROUPING函数的结果组合起来,所以说01和10的值差异的
3. group_id的行使 当group
by子句中重复使用几个列时,通过group_id来去除重复值

函数是维度越来越细的计算,语法和rollup相仿

grouping sets与rollup()

group by的grouping sets(State of Qatar扩张在oracle
9i中最初进场,后边的例证中的整个group by…having子句能够用group by
grouping sets(State of Qatar替换。

with tsales as
 (select /* lst7-17 */
   s.quantity_sold,
   s.amount_sold,
   to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
   to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
   nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
   p.prod_name,
   p.prod_desc,
   p.prod_category,
   (pf.unit_cost * s.quantity_sold) total_cost,
   s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
    from sh.sales s
    join sh.customers c
      on c.cust_id = s.cust_id
    join sh.products p
      on p.prod_id = s.prod_id
    join sh.times t
      on t.time_id = s.time_id
    join sh.costs pf
      on pf.channel_id = s.channel_id
     and pf.prod_id = s.prod_id
     and pf.promo_id = s.promo_id
     and pf.time_id = s.time_id
   where (t.fiscal_year = 2001))
select 'Q' || to_char(grouping_id(cust_income_level,age_range)+1) query_tag,
prod_category,
decode(grouping(cust_income_level),1,'ALL INCOME',cust_income_level) cust_income_level,
decode(grouping(age_range),1,'ALL AGE',age_range) age_range, 
sum(profit) profit
from tsales
group by prod_category,grouping sets(
rollup(prod_category), --产品分类小计
(cust_income_level),--产品分类和收入层次
(age_range), --产品分类和年龄范围
(cust_income_level,age_range) --产品分类,年龄范围和收入层次
)
--having group_id() < 1
order by prod_category,profit;

group by cube having grouping_id(卡塔尔(قطر‎与group by grouping
sets叁个根本的界别是,前面二个能将变量设定为不易的值来方便修正出口,而后人的出口无法改改,除非更改或动态生成sql语句。修正sql语句意味着须要保证更加多的代码何况占用更加多的数据库能源。最棒尽量幸免使用动态生成sql语句,因为它会开支的数据库财富更加的多,並且在产出难点时麻烦检查和修理。
少数时候grouping_sets(卡塔尔国增添会招致出口中现身重复。重复是由rollup(prod_category卡塔尔(قطر‎发生的。能够因此去掉rollup(卡塔尔国然后再次运维获得证实,重复的就要一扫而光。但是,种种产物连串的共计也不设有了。消除的秘技便是利用group_id(卡塔尔函数标识重复的行,并将其插入到having子句中。
在上头的sql元帅--having group_id() < 1 改成 having group_id() < 1
如此那般,输出结果就像是预期的那样不带有重复的行了。有意思的是借使将rollup(prod_category)改成null,去掉having子句,同期仍为能够获取预期的出口。代码如下:

with tsales as
 (select /* lst7-17-1 */
   s.quantity_sold,
   s.amount_sold,
   to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
   to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
   nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
   p.prod_name,
   p.prod_desc,
   p.prod_category,
   (pf.unit_cost * s.quantity_sold) total_cost,
   s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
    from sh.sales s
    join sh.customers c
      on c.cust_id = s.cust_id
    join sh.products p
      on p.prod_id = s.prod_id
    join sh.times t
      on t.time_id = s.time_id
    join sh.costs pf
      on pf.channel_id = s.channel_id
     and pf.prod_id = s.prod_id
     and pf.promo_id = s.promo_id
     and pf.time_id = s.time_id
   where (t.fiscal_year = 2001))
select 'Q' || to_char(grouping_id(cust_income_level,age_range)+1) query_tag,
prod_category,
decode(grouping(cust_income_level),1,'ALL INCOME',cust_income_level) cust_income_level,
decode(grouping(age_range),1,'ALL AGE',age_range) age_range, 
sum(profit) profit
from tsales
group by prod_category,grouping sets(
null,
--rollup(prod_category), --产品分类小计
(cust_income_level),--产品分类和收入层次
(age_range), --产品分类和年龄范围
(cust_income_level,age_range) --产品分类,年龄范围和收入层次
)
--having group_id() < 1
order by prod_category,profit

group by的rollup(卡塔尔扩张也得以单独用来计量不然将会要求由union
all结合起来的五个查询完毕的小计。
比如:成立显示器所著名字以Sul初阶的顾客各自的进货总额报告,况兼须要对各类消费者分别按年,产物分类举行小计,还要有全部花费的总括。那类别型的职责能够选取rollup(卡塔尔完毕。

--rollup()小计
with mysales as (
    select c.cust_last_name || ',' || c.cust_first_name cust_name,
    p.prod_category,
    to_char(trunc(time_id,'YYYY'),'YYYY') sale_year,
    p.prod_name,
    s.amount_sold
    from sh.sales s
    join sh.products p on p.prod_id=s.prod_id
    join sh.customers c on c.cust_id=s.cust_id
    where c.cust_last_name like 'Sul%'
)
select 
    decode(grouping(m.cust_name),1,'GRAND TOTAL',m.cust_name) cust_name,
    decode(grouping(m.sale_year),1,'TOTAL BY YEAR',m.sale_year) sale_year,
    decode(grouping(m.prod_category),1,'TOTAL BY CATEGORY',m.prod_category) prod_category,
    sum(m.amount_sold) amount_sold
from mysales m
group by rollup(m.cust_name,m.prod_category,m.sale_year)
order by grouping(m.cust_name), 1,2,3;

注意decode(State of Qatar和grouping(State of Qatar函数再二回被用来代表小计行。使用grouping(m.cust_name卡塔尔将总共呈现在告知的终极。由于这一个值>0的当世无双情形正是当总括有所消费者一同时,这几个计算值只会冒出在告诉的末梢。

假设有n个维度,那么rollup会有n个聚合,cube会有2n个聚合

group by的局限性

  • LOB列,嵌套表或数组不能够用做group by表明式的大器晚成有些

SQL> with lobtest as (
  2   select to_clob(d.dname) dname
  3   from scott.emp e
  4   join scott.dept d on d.deptno=e.deptno
  5  )
  6  select l.dname
  7  from lobtest l
  8  group by l.dname;
group by l.dname
         *
第 8 行出现错误:
ORA-00932: 数据类型不一致: 应为 -, 但却获得 CLOB
  • 不许利用标量子查询表达式

SQL> select d.dname,count(empno) empcount
  2  from scott.emp e
  3  join scott.dept d on d.deptno=e.deptno
  4  group by (select dname from scott.dept d2 where d2.dname = d.dname )
  5  order by d.dname;
group by (select dname from scott.dept d2 where d2.dname = d.dname )
          *
第 4 行出现错误:
ORA-22818: 这里不允许出现子查询表达式
  • 借使group by子句引用任何对象类型的列则查询不可能并行化

create type dept_location_type as object
(
       street_address varchar2(40),
       postal_code varchar2(10),
       city varchar2(30),
       state_province varchar2(10),
       country_id char(2),
       order member function match (e dept_location_type) return integer
);
/
create or replace type body  dept_location_type
as order member function match (e dept_location_type) return integer
is 
   begin
         if city <e.city then
                 return -1;
         elsif city > e.city then
                 return 1;
         else
                 return 0;        
         end if;
   end;
end;
/      

create table deptobj
as 
select d.deptno,d.dname
from scott.dept d;     

alter table  deptobj add (dept_location dept_location_type);   

select * from deptobj;

update deptobj set dept_location=dept_location_type('1234 fenmenao st','453076','ShenZhen','GuangDong','GD') where deptno=1;
update deptobj set dept_location=dept_location_type('345 Leshan st','123456','LeShan','SiCuan','SC') where deptno=2;
update deptobj set dept_location=dept_location_type('345 ChongQing st','123456','ChongQing','ChongQing','CQ') where deptno=3;
update deptobj set dept_location=dept_location_type('345 ChangChun st','123456','ChangChun','GuiYang','GY') where deptno=4;
--对象列的并行group by
select /*+ gather_plan_statictics parallel(e 2)*/
d.dept_location,count(e.ename) ecount
from scott.emp e,deptobj d
where e.deptno=d.deptno
group by d.dept_location
order by d.dept_location;

实行结果

图片 2

对象列的竞相group by

dept_location类型体中的成员函数相配用来实行城股票总市值的可比,然后使用group
by将雇员按城市分组。最后三个列出的局限性在最终时代的本子是足以干活的。

rollup统计列

总结

Oracle以group
by子句扩张的款式为SQL开辟者提供了一些极佳的工具,帮忙大家不但能够收缩代码量,而且能抓实数据库成效。大比比较多的表征也要与另外分裂的效劳进行理并了结合。

图片 3

group by总结

rollup(a,b卡塔尔 总括列蕴涵:(a,bState of Qatar、(a卡塔尔、(卡塔尔

rollup(a,b,cState of Qatar 总结列包括:(a,b,c卡塔尔、(a,b卡塔尔(قطر‎、(a卡塔尔国、(卡塔尔国

….

cube统计列

cube(a,b卡塔尔国 总计列包罗:(a,b卡塔尔(قطر‎、(aState of Qatar、(b卡塔尔(قطر‎、(卡塔尔国

cube(a,b,c卡塔尔国 总括列富含:(a,b,c卡塔尔、(a,bState of Qatar、(a,c卡塔尔国、(b,c卡塔尔(قطر‎、(aState of Qatar、(b卡塔尔、(c卡塔尔(قطر‎、(卡塔尔(قطر‎

….

总结

上述便是那篇小说的全体内容了,希望本文的故事情节对大家的就学恐怕办事有所一定的参谋学习价值,感谢我们对剧本之家的帮衬。

发表评论

电子邮件地址不会被公开。 必填项已用*标注