Oracle analytic function-ROLLUP

Oracle的GROUP BY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。如果是Group by ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作。
如果是GROUP BY CUBE(A, B, C),则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作。

普通的group by分组

SQL> select department_id, sum(salary)
  2    from employees
  3   where department_id <= 40
  4   group by department_id;

DEPARTMENT_ID SUM(SALARY)
------------- -----------
           10        4400
           20       19000
           30       24200
           40        6500

对department_id进行rollup操作。

SQL> select department_id, sum(salary)
  2    from employees
  3   where department_id <= 40
  4   group by rollup(department_id);

DEPARTMENT_ID SUM(SALARY)
------------- -----------
           10        4400
           20       19000
           30       24200
           40        6500
                    54100
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  batcf7mbazs84, child number 0
-------------------------------------
select department_id, sum(salary)   from employees  where department_id
<= 40  group by rollup(department_id)

Plan hash value: 2954037253

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |        |       |     2 (100)|       |       |          |
|   1 |  SORT GROUP BY NOSORT ROLLUP |                   |      1 |     7 |     2   (0)| 73728 | 73728 |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     10 |    70 |     2   (0)|       |       |          |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     10 |       |     1   (0)|       |       |          |
-------------------------------------------------------------------------------------------------------------------

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

   3 - access("DEPARTMENT_ID"<=40)

上述语句转换成传统的group by语句如下

SQL> select department_id, sum(salary)
  2    from employees
  3   where department_id <= 40 group by department_id
  4   union all
  5   select null,sum(salary)
  6   from employees
  7   where department_id <= 40;

DEPARTMENT_ID SUM(SALARY)
------------- -----------
           10        4400
           20       19000
           30       24200
           40        6500
                    54100

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0k79kgu07aqd8, child number 0
-------------------------------------
select department_id, sum(salary)   from employees  where department_id
<= 40 group by department_id  union all  select null,sum(salary)  from
employees  where department_id <= 40

Plan hash value: 815785445

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | E-Rows |E-Bytes| Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |        |       |     4 (100)|
|   1 |  UNION-ALL                    |                   |        |       |            |
|   2 |   SORT GROUP BY NOSORT        |                   |      1 |     7 |     2   (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     10 |    70 |     2   (0)|
|*  4 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     10 |       |     1   (0)|
|   5 |   SORT AGGREGATE              |                   |      1 |     7 |            |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     10 |    70 |     2   (0)|
|*  7 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     10 |       |     1   (0)|
-----------------------------------------------------------------------------------------

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

   4 - access("DEPARTMENT_ID"<=40)
   7 - access("DEPARTMENT_ID"<=40)

接下来对department_id,employee_id进行rollup。

SQL> select department_id,employee_id, sum(salary)
  2    from employees
  3   where department_id <= 40
  4   group by rollup(department_id,employee_id);
DEPARTMENT_ID EMPLOYEE_ID SUM(SALARY)
------------- ----------- -----------
           10         200        4400           
           10                    4400           
           20         201       13000           --AB
           20         202        6000
           20                   19000           -A
           30         114       11000
           30         115        2600
           30         116        2900
           30         117        2600
           30         118        2600
           30         119        2500
           30                   24200
           40         203        6500
           40                    6500
                                54100          --FULL

已选择15行。

对department_id,employee_id, job_id进行rollup。

SQL> select department_id,employee_id, job_id,sum(salary)
  2    from employees
  3   where department_id <= 40
  4   group by rollup(department_id,employee_id,job_id);

DEPARTMENT_ID EMPLOYEE_ID JOB_ID               SUM(SALARY)
------------- ----------- -------------------- -----------
           10         200 AD_ASST                     4400        --ABC
           10         200                             4400        --AB
           10                                         4400        -A
           20         201 MK_MAN                     13000
           20         201                            13000
           20         202 MK_REP                      6000
           20         202                             6000
           20                                        19000
           30         114 PU_MAN                     11000
           30         114                            11000
           30         115 PU_CLERK                    2600
           30         115                             2600
           30         116 PU_CLERK                    2900
           30         116                             2900
           30         117 PU_CLERK                    2600
           30         117                             2600
           30         118 PU_CLERK                    2600
           30         118                             2600
           30         119 PU_CLERK                    2500
           30         119                             2500
           30                                        24200
           40         203 HR_REP                      6500
           40         203                             6500
           40                                         6500
                                                     54100      --ALL

已选择25行。

通过rollup和cube会产生superaggregate 行,这些行的特点是列值为NULL。为了区分superaggregate行,可以使用GROUPING函数来进行区分,GROUPING函数返回的数据类型为NUMBER型。
作为GROUPING函数中的表达式必须是GROUP BY从句中的一项,如果该表达式值对应的行中所有的列值为空,则GROUPING函数返回1,否则返回0。

SQL> select department_id,
  2         employee_id,
  3         job_id,
  4         sum(salary),
  5         grouping(department_id),
  6         grouping(employee_id),
  7         grouping(job_id)
  8    from employees
  9   where department_id <= 40
 10   group by rollup(department_id, employee_id, job_id);
DEPARTMENT_ID EMPLOYEE_ID JOB_ID               SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(EMPLOYEE_ID) GROUPING(JOB_ID)
------------- ----------- -------------------- ----------- ----------------------- --------------------- ----------------
           10         200 AD_ASST                     4400                       0                     0                0
           10         200   NULL                      4400                       0                     0                1
           10         NULL  NULL                      4400                       0                     1                1
           20         201 MK_MAN                     13000                       0                     0                0
           20         201                            13000                       0                     0                1
           20         202 MK_REP                      6000                       0                     0                0
           20         202                             6000                       0                     0                1
           20                                        19000                       0                     1                1
           30         114 PU_MAN                     11000                       0                     0                0
           30         114                            11000                       0                     0                1
           30         115 PU_CLERK                    2600                       0                     0                0
           30         115                             2600                       0                     0                1
           30         116 PU_CLERK                    2900                       0                     0                0
           30         116                             2900                       0                     0                1
           30         117 PU_CLERK                    2600                       0                     0                0
           30         117                             2600                       0                     0                1
           30         118 PU_CLERK                    2600                       0                     0                0
           30         118                             2600                       0                     0                1
           30         119 PU_CLERK                    2500                       0                     0                0
           30         119                             2500                       0                     0                1
           30                                        24200                       0                     1                1
           40         203 HR_REP                      6500                       0                     0                0
           40         203                             6500                       0                     0                1
           40                                         6500                       0                     1                1
           NULL       NULL  NULL                      54100                      1                     1                1
已选择25行。
此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

One Response to Oracle analytic function-ROLLUP

  1. Pingback引用通告: Oracle analytic function-CUBE | LEO Notes

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s