Oracle GROUPING函數的使用

GROUPING函數可以接受一列，返回0或者1。如果列值為空白，那麼GROUPING()返回1；如果列值非空，那麼返回0。GROUPING只能在使用ROLLUP或CUBE的查詢中使用。當需要在返回空值的地方顯示某個值時，GROUPING()就非常有用。

1、在ROLLUP中對單列使用GROUPING()

SQL> select division_id,sum(salary)
  2  from employees2
  3  group by rollup(division_id)
  4  order by division_id;

DIV SUM(SALARY)
--- -----------
BUS     1610000
OPE     1320000
SAL     4936000
SUP     1015000
        8881000

加上GROUPING來看看

SQL> select grouping(division_id),division_id,sum(salary)
  2  from employees2
  3  group by rollup(division_id)
  4  order by division_id;

GROUPING(DIVISION_ID) DIV SUM(SALARY)
--------------------- --- -----------
                    0 BUS     1610000
                    0 OPE     1320000
                    0 SAL     4936000
                    0 SUP     1015000
                    1         8881000
可以看到，為空白的地方返回1，非空的地方返回0。

2、使用CASE轉換GROUPING()的傳回值

可能你會覺得前面的0和1太枯燥了，代表不了任何意義，說白了就是不夠人性化，呵呵。這個時候我們可以使用CASE來轉換為一些有意義的值。

SQL> select
  2  case grouping(division_id)
  3  when 1 then 'all divisions'
  4  else division_id
  5  end as div,
  6  sum(salary)
  7  from employees2
  8  group by rollup(division_id)
  9  order by division_id;

DIV           SUM(SALARY)
------------- -----------
BUS               1610000
OPE               1320000
SAL               4936000
SUP               1015000
all divisions     8881000

3、使用CASE和GROUPING()轉換多個列的值

SQL> select
  2  case grouping(division_id)
  3  when 1 then 'all divisions'
  4  else division_id
  5  end as div,
  6  case grouping(job_id)
  7  when 1 then 'all jobs'
  8  else job_id
  9  end as job,
 10  sum(salary)
 11  from employees2
 12  group by rollup(division_id,job_id)
 13  order by division_id,job_id;

DIV           JOB      SUM(SALARY)
------------- -------- -----------
BUS           MGR           530000
BUS           PRE           800000
BUS           WOR           280000
BUS           all jobs     1610000
OPE           ENG           245000
OPE           MGR           805000
OPE           WOR           270000
OPE           all jobs     1320000
SAL           MGR          4446000
SAL           WOR           490000
SAL           all jobs     4936000

DIV           JOB      SUM(SALARY)
------------- -------- -----------
SUP           MGR           465000
SUP           TEC           115000
SUP           WOR           435000
SUP           all jobs     1015000
all divisions all jobs     8881000

16 rows selected.

4、CUBE與GROUPING()結合使用

SQL> select
  2  case grouping(division_id)
  3  when 1 then 'all divisions'
  4  else division_id
  5  end as div,
  6  case grouping(job_id)
  7  when 1 then 'all jobs'
  8  else job_id
  9  end as job,
 10  sum(salary)
 11  from employees2
 12  group by cube(division_id,job_id)
 13  order by division_id,job_id;

DIV           JOB      SUM(SALARY)
------------- -------- -----------
BUS           MGR           530000
BUS           PRE           800000
BUS           WOR           280000
BUS           all jobs     1610000
OPE           ENG           245000
OPE           MGR           805000
OPE           WOR           270000
OPE           all jobs     1320000
SAL           MGR          4446000
SAL           WOR           490000
SAL           all jobs     4936000

DIV           JOB      SUM(SALARY)
------------- -------- -----------
SUP           MGR           465000
SUP           TEC           115000
SUP           WOR           435000
SUP           all jobs     1015000
all divisions ENG           245000
all divisions MGR          6246000
all divisions PRE           800000
all divisions TEC           115000
all divisions WOR          1475000
all divisions all jobs     8881000

21 rows selected.

5、使用GROUPING SETS子句

使用GROUPING SETS子句可以只返回小計記錄。

SQL> select division_id,job_id,sum(salary)
  2  from employees2
  3  group by grouping sets(division_id,job_id)
  4  order by division_id,job_id;

DIV JOB SUM(SALARY)
--- --- -----------
BUS         1610000
OPE         1320000
SAL         4936000
SUP         1015000
    ENG      245000
    MGR     6246000
    PRE      800000
    TEC      115000
    WOR     1475000

9 rows selected

當你與COUNT和SUM這類總計函數一起使用GROUP BY語句時，你一般得不到多級總數。GROUP BY中每個唯一的列組合產生一個總數，但這些總數不會“累加”到更高一級的總數中。
    要實現這一點，你可以用GROUP BY ROLLUP或GROUP BY CUBE替代GROUP BY，不過它們會產生所有可能的總數，而你可能不需要全部總數。對GROUP BY CUBE而言，將會產生2^n組總數，這裡的n是GROUP BY中列的數目。
查看下面的查詢，它使用了SH樣本模式：
SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)
FROM sales
WHERE cust_id < 3
GROUP BY CUBE (prod_id, cust_id, channel_id)
這將產生8組總數：
所有行的總和
每個通道，包括所有產品和顧客
每個顧客，包括所有產品和通道
每項產品，包括所有顧客和通道
每個通道/顧客組合，包括所有產品
每個通道/產品組合，包括所有顧客
每個產品/顧客組合，包括所有通道
每個產品、顧客和通道組合
可能的組合非常多。GROUP BY CUBE中每增加一列，產生的總數就會翻一番。
   可以用GROUP BY GROUPING SETS來代替GROUP BY CUBE。你可以應用來指定你感興趣的總數組合。因為它不必計算它不需要集合（也不會產生太多結果），所以對SQL引擎來說更為高效。
其格式為：
GROUP BY GROUPING SETS ((list), (list) ... )
這裡（list）是圓括弧中的一個列序列，這個組合產生一個總數。要增加一個總和，必須增加一個(NUlL)分組集。
例如，如果只要產生每項產品（包括所有顧客和通道）和每個顧客/通道組合（包括所有產品）的總數，可以輸入：
SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)
FROM sales
WHERE cust_id < 3
GROUP BY GROUPING SETS (
(prod_id), (cust_id, channel_id)
);
這種方法將這個資料集產生的總數數量從180個減少到37個，並協助你著重回答你希望解答的問題。