Oracle analytic function-CUBE

前一篇文章中对group by后跟rollup进行介绍,本文对group by后跟cube来介绍。
如果是GROUP BY CUBE(A, B, C),则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作。

SQL> select department_id, employee_id, job_id, sum(salary)
  2      from employees
  3     where department_id = 20
  4      group by cube(department_id, employee_id, job_id)
  5     order by 1;

DEPARTMENT_ID EMPLOYEE_ID JOB_ID               SUM(SALARY)
------------- ----------- -------------------- -----------
           20         201 MK_MAN                     13000
           20         201                            13000
           20         202 MK_REP                      6000
           20         202                             6000
           20             MK_MAN                     13000
           20             MK_REP                      6000
           20                                        19000
                      201 MK_MAN                     13000
                      201                            13000
                      202 MK_REP                      6000
                      202                             6000
                          MK_MAN                     13000
                          MK_REP                      6000
                                                     19000

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

DEPARTMENT_ID EMPLOYEE_ID JOB_ID               SUM(SALARY)
------------- ----------- -------------------- -----------
           10         200 AD_ASST                     4400     -ABC
           10         200                             4400     -AB
           10             AD_ASST                     4400     -AC
           10                                         4400     -A
           20         201 MK_MAN                     13000
           20         201                            13000
           20         202 MK_REP                      6000
           20         202                             6000
           20             MK_MAN                     13000
           20             MK_REP                      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             PU_CLERK                   13200
           30             PU_MAN                     11000
           30                                        24200
           40         203 HR_REP                      6500
           40         203                             6500
           40             HR_REP                      6500
           40                                         6500
                      114 PU_MAN                     11000
                      114                            11000
                      115 PU_CLERK                    2600
                      115                             2600
                      116 PU_CLERK                    2900
                      116                             2900
                      117 PU_CLERK                    2600
                      117                             2600
                      118 PU_CLERK                    2600
                      118                             2600
                      119 PU_CLERK                    2500
                      119                             2500
                      200 AD_ASST                     4400   -BC
                      200                             4400   -B
                      201 MK_MAN                     13000
                      201                            13000
                      202 MK_REP                      6000
                      202                             6000
                      203 HR_REP                      6500
                      203                             6500
                          AD_ASST                     4400  -C
                          HR_REP                      6500
                          MK_MAN                     13000
                          MK_REP                      6000
                          PU_CLERK                   13200
                          PU_MAN                     11000
                                                     54100  -()
已选择57行。

ROLLUP中使用的GROUPING在CUBE中也可以采用。

SQL> select department_id,
  2         employee_id,
  3         job_id,
  4         grouping(department_id),
  5         grouping(employee_id),
  6         grouping(job_id),
  7         sum(salary)
  8    from employees
  9   where department_id <= 40
 10   group by cube(department_id, employee_id, job_id)
 11   order by 1;
DEPARTMENT_ID EMPLOYEE_ID JOB_ID               GROUPING(DEPARTMENT_ID) GROUPING(EMPLOYEE_ID) GROUPING(JOB_ID) SUM(SALARY)
------------- ----------- -------------------- ----------------------- --------------------- ---------------- -----------
           10         200 AD_ASST                                    0                     0                0        4400
           10         200                                            0                     0                1        4400
           10             AD_ASST                                    0                     1                0        4400
           10                                                        0                     1                1        4400
           20         201 MK_MAN                                     0                     0                0       13000
           20         201                                            0                     0                1       13000
           20         202 MK_REP                                     0                     0                0        6000
           20         202                                            0                     0                1        6000
           20             MK_MAN                                     0                     1                0       13000
           20             MK_REP                                     0                     1                0        6000
           20                                                        0                     1                1       19000
           30         114 PU_MAN                                     0                     0                0       11000
           30         114                                            0                     0                1       11000
           30         115 PU_CLERK                                   0                     0                0        2600
           30         115                                            0                     0                1        2600
           30         116 PU_CLERK                                   0                     0                0        2900
           30         116                                            0                     0                1        2900
           30         117 PU_CLERK                                   0                     0                0        2600
           30         117                                            0                     0                1        2600
           30         118 PU_CLERK                                   0                     0                0        2600
           30         118                                            0                     0                1        2600
           30         119 PU_CLERK                                   0                     0                0        2500
           30         119                                            0                     0                1        2500
           30             PU_CLERK                                   0                     1                0       13200
           30             PU_MAN                                     0                     1                0       11000
           30                                                        0                     1                1       24200
           40         203 HR_REP                                     0                     0                0        6500
           40         203                                            0                     0                1        6500
           40             HR_REP                                     0                     1                0        6500
           40                                                        0                     1                1        6500
                      114 PU_MAN                                     1                     0                0       11000
                      114                                            1                     0                1       11000
                      115 PU_CLERK                                   1                     0                0        2600
                      115                                            1                     0                1        2600
                      116 PU_CLERK                                   1                     0                0        2900
                      116                                            1                     0                1        2900
                      117 PU_CLERK                                   1                     0                0        2600
                      117                                            1                     0                1        2600
                      118 PU_CLERK                                   1                     0                0        2600
                      118                                            1                     0                1        2600
                      119 PU_CLERK                                   1                     0                0        2500
                      119                                            1                     0                1        2500
                      200 AD_ASST                                    1                     0                0        4400
                      200                                            1                     0                1        4400
                      201 MK_MAN                                     1                     0                0       13000
                      201                                            1                     0                1       13000
                      202 MK_REP                                     1                     0                0        6000
                      202                                            1                     0                1        6000
                      203 HR_REP                                     1                     0                0        6500
                      203                                            1                     0                1        6500
                          AD_ASST                                    1                     1                0        4400
                          HR_REP                                     1                     1                0        6500
                          MK_MAN                                     1                     1                0       13000
                          MK_REP                                     1                     1                0        6000
                          PU_CLERK                                   1                     1                0       13200
                          PU_MAN                                     1                     1                0       11000
                                                                     1                     1                1       54100
已选择57行。

CUBE在ROLLUP的基础上进一步从各种维度上给出了更详细的统计展现。

此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

发表评论

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