oracle中的数据统计

一、使用ROLLUP和CUBE汇总统计

1、测试数据

create table A
(
  GRADE CHAR(5),
  ID    CHAR(5),
  NUM   CHAR(5)
)

insert into a values('a',1,1);

insert into a values('a',2,2);

insert into a values('b',2,4);

insert into a values('b',3,4);

2、查看几个分组结果

SQL> select sum(num) from a; //全部求和

  SUM(NUM)
----------
        11

SQL> select grade,sum(num) from a group by grade;//按grade分组

GRADE   SUM(NUM)
----- ----------
a              3
b              8

SQL> select id,sum(num) from a group by id;;//按id分组

ID      SUM(NUM)
----- ----------
1              1
2              6
3              4

SQL> select id,grade,sum(num) from a group by grade,id;;//按grade,id分组

ID    GRADE   SUM(NUM)
----- ----- ----------
1     a              1
2     a              2
2     b              4
3     b              4

3、使用rollup分组结果

SQL> select grade,id,sum(num) from a group by rollup(id,grade);

GRADE ID      SUM(NUM)
----- ----- ----------
a     1              1
      1              1
a     2              2
b     2              4
      2              6
b     3              4
      3              4
                    11

8 rows selected

SQL> select id,grade,sum(num) from a group by rollup(id,grade);

ID    GRADE   SUM(NUM)
----- ----- ----------
1     a              1
1                    1
2     a              2
2     b              4
2                    6
3     b              4
3                    4
                    11

可以看出调换id和grade位置结果是不一样的，当使用rollup分组时，首先是按第一个参数分组，然后第一个，第二个参数分组 ，如果跟多的话依次类推

4、使用cube分组

SQL> select id,grade,sum(num) from a group by cube(id,grade);

ID    GRADE   SUM(NUM)
----- ----- ----------
                    11
      a              3
      b              8
1                    1
1     a              1
2                    6
2     a              2
2     b              4
3                    4
3     b              4

10 rows selected

SQL> select grade,id,sum(num) from a group by cube(grade,id);

GRADE ID      SUM(NUM)
----- ----- ----------
                    11
      1              1
      2              6
      3              4
a                    3
a     1              1
a     2              2
b                    8
b     2              4
b     3              4

使用cube分组时调换参数的位置并不会影响结果，因为cube会为每个参数单独分组统计，然后按照然后第一个，第二个参数分组 ，第二个参，第三个参数一次分组统计

5、多个参数实验

 (1)修改表

alter table a add  grade1 char(5);

SQL> select * from a;

GRADE ID    NUM   GRADE1
----- ----- ----- ------
a     1     1     a1
a     2     2     a1
b     2     4     b1
b     3     4     b2

（2）使用rollup

SQL> select grade,grade1,id,sum(num) from a group by rollup(grade,grade1,id);

GRADE GRADE ID      SUM(NUM)
----- ----- ----- ----------
a     a1    1              1
a     a1    2              2    按grade，grade1和id分组
a     a1                   3     按grade和grade1分组
a                          3         按grade分组
b     b1    2              4
b     b1                   4
b     b2    3              4
b     b2                   4
b                          8
                          11

（2）使用cube

SQL> select grade,grade1,id,sum(num) from a group by cube(grade,grade1,id);

GRADE GRADE ID      SUM(NUM)
----- ----- ----- ----------
                          11
            1              1
            2              6       按id分组
            3              4
      a1                   3      按grade1分组
      a1    1              1     按grade1和id分组
      a1    2              2
      b1                   4
      b1    2              4
      b2                   4
      b2    3              4
a                          3       按grade分组
a           1              1
a           2              2
a     a1                   3     按grade和grade1分组
a     a1    1              1    按grade，id和grade1分组

a     a1    2              2
b                          8
b           2              4      按grade，id分组
b           3              4
b     b1                   4
b     b1    2              4
b     b2                   4
b     b2    3              4

24 rows selected.

其实cube和rollup的区别就是cube采用排列组和，所有的参数排列组合，儿而rollup只是依次组合