oracle 高效函数,Oracle分组函数之高效的ROLLUP

Oracle/" target=_blank>Oracle分组函数之高效的ROLLUP

㈠ 初始化实验坏境

www.2cto.com

[sql]

hr@ORCL> create table rollup_test as

2      select e.department_id,j.job_title,e.first_name,e.salary

3        from employees e,jobs j

4       where e.job_id=j.job_id;

Table created.

hr@ORCL> select * from rollup_test;

DEPARTMENT_ID JOB_TITLE                           FIRST_NAME               SALARY

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

50 Shipping Clerk                      Donald                     2600

50 Shipping Clerk                      Douglas                    2600

10 Administration Assistant            Jennifer                   4400

20 Marketing Manager                   Michael                   13000

20 Marketing Representative            Pat                        6000

40 Human Resources Representative      Susan                      6500

70 Public Relations Representative     Hermann                   10000

110 Accounting Manager                  Shelley                   12000

110 Public Accountant                   William                    8300

90 President                           Steven                    24000

90 Administration Vice President       Neena                     17000

90 Administration Vice President       Lex                       17000

60 Programmer                          Alexander                  9000

60 Programmer                          Bruce                      6000

60 Programmer                          David                      4800

60 Programmer                          Valli                      4800

60 Programmer                          Diana                      4200

100 Finance Manager                     Nancy                     12000

100 Accountant                          Daniel                     9000

100 Accountant                          John                       8200

100 Accountant                          Ismael                     7700

100 Accountant                          Jose Manuel                7800

100 Accountant                          Luis                       6900

30 Purchasing Manager                  Den                       11000

30 Purchasing Clerk                    Alexander                  3100

30 Purchasing Clerk                    Shelli                     2900

30 Purchasing Clerk                    Sigal                      2800

30 Purchasing Clerk                    Guy                        2600

30 Purchasing Clerk                    Karen                      2500

50 Stock Manager                       Matthew                    8000

...............................................

...............................................

...............................................

www.2cto.com

㈡ 先看一下普通分组的效果：对DEPARTMENT_ID进行普通的GROUP BY操作---按照小组进行分组

[sql]

hr@ORCL> select department_id,sum(salary) from rollup_test group by department_id;

DEPARTMENT_ID SUM(SALARY)

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

100       51600

30       24900

7000

20       19000

70       10000

90       58000

110       20300

50      156400

40        6500

80      304500

10        4400

60       28800

12 rows selected.

㈢ 对DEPARTMENT_ID进行普通的ROLLUP操作---按照小组进行分组，同时求总计

[sql]

hr@ORCL> select department_id,sum(salary) from rollup_test group by rollup(department_id);

DEPARTMENT_ID SUM(SALARY)

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

10        4400

20       19000

30       24900

40        6500

50      156400

60       28800

70       10000

80      304500

90       58000

100       51600

110       20300

7000

691400

13 rows selected.

Elapsed: 00:00:00.06

Execution Plan

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

Plan hash value: 3210238927

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

| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |             |   107 |  2782 |     4  (25)| 00:00:01 |

|   1 |  SORT GROUP BY ROLLUP|             |   107 |  2782 |     4  (25)| 00:00:01 |

|   2 |   TABLE ACCESS FULL  | ROLLUP_TEST |   107 |  2782 |     3   (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement

Statistics

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

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

648  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

13  rows processed

㈣ 使用Group By语句翻译一下上面的SQL语句如下(union all一个统计所有数据的行)

[sql]

hr@ORCL> select department_id,sum(salary) from rollup_test group by department_id

2      union all

3      select null, sum(salary) from rollup_test

4      order by 1;

DEPARTMENT_ID SUM(SALARY)

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

10        4400

20       19000

30       24900

40        6500

50      156400

60       28800

70       10000

80      304500

90       58000

100       51600

110       20300

7000

691400

13 rows selected.

Elapsed: 00:00:00.02

Execution Plan

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

Plan hash value: 1519347417

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

| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |             |   108 |  2795 |     8  (25)| 00:00:01 |

|   1 |  SORT ORDER BY       |             |   108 |  2795 |     7  (58)| 00:00:01 |

|   2 |   UNION-ALL          |             |       |       |            |          |

|   3 |    HASH GROUP BY     |             |   107 |  2782 |     4  (25)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| ROLLUP_TEST |   107 |  2782 |     3   (0)| 00:00:01 |

|   5 |    SORT AGGREGATE    |             |     1 |    13 |            |          |

|   6 |     TABLE ACCESS FULL| ROLLUP_TEST |   107 |  1391 |     3   (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement

Statistics

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

0  recursive calls

0  db block gets

6  consistent gets

0  physical reads

0  redo size

648  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

13  rows processed

㈤ 再看一个ROLLUP两列的情况

[sql]

hr@ORCL> select department_id,job_title,sum(salary) from rollup_test group by rollup(department_id,job_title);

DEPARTMENT_ID JOB_TITLE                           SUM(SALARY)

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

Sales Representative                       7000

7000

10 Administration Assistant                   4400

10                                            4400

20 Marketing Manager                         13000

20 Marketing Representative                   6000

20                                           19000

30 Purchasing Clerk                          13900

30 Purchasing Manager                        11000

30                                           24900

40 Human Resources Representative             6500

40                                            6500

50 Stock Clerk                               55700

50 Stock Manager                             36400

50 Shipping Clerk                            64300

50                                          156400

60 Programmer                                28800

60                                           28800

70 Public Relations Representative           10000

70                                           10000

80 Sales Manager                             61000

80 Sales Representative                     243500

80                                          304500

90 President                                 24000

90 Administration Vice President             34000

90                                           58000

100 Accountant                                39600

100 Finance Manager                           12000

100                                           51600

110 Public Accountant                          8300

110 Accounting Manager                        12000

110                                           20300

691400

33 rows selected.

Elapsed: 00:00:00.02

Execution Plan

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

Plan hash value: 3210238927

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

| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |             |   107 |  4815 |     4  (25)| 00:00:01 |

|   1 |  SORT GROUP BY ROLLUP|             |   107 |  4815 |     4  (25)| 00:00:01 |

|   2 |   TABLE ACCESS FULL  | ROLLUP_TEST |   107 |  4815 |     3   (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement

Statistics

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

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

1511  bytes sent via SQL*Net to client

407  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

33  rows processed

㈥ 上面的SQL语句该如何使用Group By进行翻译呢？

[sql]

hr@ORCL> select department_id,job_title,sum(salary) from rollup_test group by department_id,job_title

2      union all

3      select department_id,null,sum(salary) from rollup_test group by department_id

4      union all

5      select null,null,sum(salary) from rollup_test

6      order by 1,2;

DEPARTMENT_ID JOB_TITLE                           SUM(SALARY)

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

10 Administration Assistant                   4400

10                                            4400

20 Marketing Manager                         13000

20 Marketing Representative                   6000

20                                           19000

30 Purchasing Clerk                          13900

30 Purchasing Manager                        11000

30                                           24900

40 Human Resources Representative             6500

40                                            6500

50 Shipping Clerk                            64300

50 Stock Clerk                               55700

50 Stock Manager                             36400

50                                          156400

60 Programmer                                28800

60                                           28800

70 Public Relations Representative           10000

70                                           10000

80 Sales Manager                             61000

80 Sales Representative                     243500

80                                          304500

90 Administration Vice President             34000

90 President                                 24000

90                                           58000

100 Accountant                                39600

100 Finance Manager                           12000

100                                           51600

110 Accounting Manager                        12000

110 Public Accountant                          8300

110                                           20300

Sales Representative                       7000

691400

7000

33 rows selected.

Elapsed: 00:00:00.02

Execution Plan

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

Plan hash value: 2979879831

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

| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |             |   215 |  7610 |    12  (25)| 00:00:01 |

|   1 |  SORT ORDER BY       |             |   215 |  7610 |    11  (73)| 00:00:01 |

|   2 |   UNION-ALL          |             |       |       |            |          |

|   3 |    HASH GROUP BY     |             |   107 |  4815 |     4  (25)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| ROLLUP_TEST |   107 |  4815 |     3   (0)| 00:00:01 |

|   5 |    HASH GROUP BY     |             |   107 |  2782 |     4  (25)| 00:00:01 |

|   6 |     TABLE ACCESS FULL| ROLLUP_TEST |   107 |  2782 |     3   (0)| 00:00:01 |

|   7 |    SORT AGGREGATE    |             |     1 |    13 |            |          |

|   8 |     TABLE ACCESS FULL| ROLLUP_TEST |   107 |  1391 |     3   (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement

Statistics

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

0  recursive calls

0  db block gets

9  consistent gets

0  physical reads

0  redo size

1513  bytes sent via SQL*Net to client

407  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

33  rows processed

㈦ 补充一步，体验一下GROUPING函数的效果

[sql]

SQL> select department_id,job_title,grouping(department_id),grouping(job_title),sum(salary)

2  from rollup_test group by rollup(department_id,job_title);

DEPARTMENT_ID JOB_TITLE                           GROUPING(DEPARTMENT_ID) GROUPING(JOB_TITLE) SUM(SALARY)

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

Sales Representative                                      0                   0        7000

0                   1        7000

10 Administration Assistant                                  0                   0        4400

10                                                           0                   1        4400

20 Marketing Manager                                         0                   0       13000

20 Marketing Representative                                  0                   0        6000

20                                                           0                   1       19000

30 Purchasing Clerk                                          0                   0       13900

30 Purchasing Manager                                        0                   0       11000

30                                                           0                   1       24900

40 Human Resources Representative                            0                   0        6500

40                                                           0                   1        6500

50 Stock Clerk                                               0                   0       55700

50 Stock Manager                                             0                   0       36400

50 Shipping Clerk                                            0                   0       64300

50                                                           0                   1      156400

60 Programmer                                                0                   0       28800

60                                                           0                   1       28800

70 Public Relations Representative                           0                   0       10000

70                                                           0                   1       10000

80 Sales Manager                                             0                   0       61000

80 Sales Representative                                      0                   0      243500

80                                                           0                   1      304500

90 President                                                 0                   0       24000

90 Administration Vice President                             0                   0       34000

90                                                           0                   1       58000

100 Accountant                                                0                   0       39600

100 Finance Manager                                           0                   0       12000

100                                                           0                   1       51600

110 Public Accountant                                         0                   0        8300

110 Accounting Manager                                        0                   0       12000

110                                                           0                   1       20300

1                   1      691400

33 rows selected    www.2cto.com

看出来什么效果了么？

如果显示“1”表示GROUPING函数对应的列(例如JOB_TITLE字段)是由于ROLLUP函数所产生的空值对应的信息

即对此列进行汇总计算后的结果

如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动

GROUPING函数可以接受一列，返回0或者1

如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0

GROUPING只能在使用ROLLUP或CUBE的查询中使用

当需要在返回空值的地方显示某个值时，GROUPING()就非常有用

㈧ 小结

ROLLUP在数据统计和报表生成过程中带来极大的便利，而且效率比起来Group By + Union组合方法效率高得多

这也体现了Oracle在SQL统计分析上人性化、自动化、高效率的特点.