Programs Knowledge

參考: http://tomkuo139.blogspot.tw/2011/01/oracle-plsql-group-by-rollup-group-by.html

觀念: http://technet.microsoft.com/zh-tw/library/bb522495(v=sql.105).aspx

在 Oracle PL/SQL 中,
對於資料分析使用上,
最常用的莫過於 Group By 用法,
現在介紹 Group By 的另一層用法: Group By Rollup 與 Group By Cube,
範例, 如下 :

建立 Temp Table
   -------------------------- */
create table tomkuo1 (
  user_name varchar2(10)
, dept_no   varchar2(10)
, job_no    varchar2(10)
, salary    number
);

新增資料
   -------------------------- */
insert into tomkuo1 values( 'aaa', 'dept_1', 'job_1', 10000 );
insert into tomkuo1 values( 'bbb', 'dept_1', 'job_2', 20000 );
insert into tomkuo1 values( 'ccc', 'dept_1', 'job_2', 30000 );
insert into tomkuo1 values( 'ddd', 'dept_2', 'job_1', 10000 );
insert into tomkuo1 values( 'eee', 'dept_2', 'job_1', 20000 );
insert into tomkuo1 values( 'fff', 'dept_2', 'job_2', 30000 );
insert into tomkuo1 values( 'ggg', 'dept_2', 'job_2', 40000 );
insert into tomkuo1 values( 'hhh', 'dept_3', 'job_1', 10000 );
insert into tomkuo1 values( 'iii', 'dept_3', 'job_2', 20000 );
insert into tomkuo1 values( 'jjj', 'dept_3', 'job_2', 30000 );
insert into tomkuo1 values( 'kkk', 'dept_3', 'job_3', 40000 );
insert into tomkuo1 values( 'lll', 'dept_3', 'job_3', 50000 );
commit;

一般的 Group By 查詢
   -------------------------- */
select dept_no
     , job_no
     , sum(salary) salary
  from tomkuo1
group by dept_no, job_no;

-- 結果
dept_1   job_1    salary
-------- -------- --------
dept_1   job_1       10000
dept_1   job_2       50000
dept_2   job_1       30000
dept_2   job_2       70000
dept_3   job_1       10000
dept_3   job_2       50000
dept_3   job_3       90000

   Group By Rollup 查詢
   -------------------------- */
select dept_no
     , job_no
     , sum(salary)       salary
     , grouping(dept_no) is_dept_total  -- (可有可無) 判斷是否為dept_no 欄位的彙總
     , grouping(job_no)  is_job_total   -- (可有可無) 判斷是否為job_no  欄位的彙總
  from tomkuo1
group by rollup(dept_no, job_no);

-- 結果
dept_1   job_1    salary   is_dept_total is_job_total
-------- -------- -------- ------------- ------------
dept_1   job_1       10000             0            0
dept_1   job_2       50000             0            0
dept_1               60000             0            1
dept_2   job_1       30000             0            0
dept_2   job_2       70000             0            0
dept_2              100000             0            1
dept_3   job_1       10000             0            0
dept_3   job_2       50000             0            0
dept_3   job_3       90000             0            0
dept_3              150000             0            1
                    310000             1            1

   Group By Cube 查詢
   -------------------------- */
select dept_no
     , job_no
     , sum(salary)       salary
     , grouping(dept_no) is_dept_total  -- (可有可無) 判斷是否為dept_no 欄位的彙總
     , grouping(job_no)  is_job_total   -- (可有可無) 判斷是否為job_no  欄位的彙總
  from tomkuo1
group by cube(dept_no, job_no);

-- 結果
dept_1   job_1    salary   is_dept_total is_job_total
-------- -------- -------- ------------- ------------
                    310000             1            1
         job_1       50000             1            0
         job_2      170000             1            0
         job_3       90000             1            0
dept_1               60000             0            1
dept_1   job_1       10000             0            0
dept_1   job_2       50000             0            0
dept_2              100000             0            1
dept_2   job_1       30000             0            0
dept_2   job_2       70000             0            0
dept_3              150000             0            1
dept_3   job_1       10000             0            0
dept_3   job_2       50000             0            0
dept_3   job_3       90000             0            0