14. 進階 SQL 語法

在撰寫 SQL 的過程中，使用一些較進階的語法可以有效減少 SQL 語句的複雜度，藉此提升執行的效率。

• 使用 with 語句

當 SQL 語法使用到子查詢時，使用 with 語法可以用來提升查詢的效率， with 語法最早由 Oracle 9i 開始，作用是優先處理子查詢語句並將結果產生出 result set ，接下來的 SQL 語句只需要從這個 result set 篩選出需要的資料即可，這種處理方式不僅可以減少擷取資料所需的 I/O ，而且子查詢已經產生 result set ，可以避免子查詢與主查詢的執行計畫進行 merge join ，造成不良的執行計畫進而影響效能。例如一段使用到子查詢的語法如下 :

select country_name,job_title,sum(salary) from  (select department_id,job_title,sum(salary) salary    from employees a,jobs b    where a.job_id=b.job_id   group by department_id,job_title) a1,  (select department_id,country_name    from departments a,locations b,countries c   where a.location_id=b.location_id) b1 where a1.department_id=b1.department_id group by country_name,job_title;

執行後須讀取 504 個 Block :

使用 with 語句改寫後只需讀取 497 個 Block :

with a1 as  (select department_id,job_title,sum(salary) salary    from employees a,jobs b    where a.job_id=b.job_id   group by department_id,job_title), b1 as  (select department_id,country_name    from departments a,locations b,countries c   where a.location_id=b.location_id) select country_name,job_title,sum(salary)   from a1,b1 where a1.department_id=b1.department_id group by country_name,job_title;

使用 with 語句的 I/O 次數較少，效率優於原本的子查詢語法。

• Advance Group By

Group By 語法的使用必須將 select 當中所有的欄位都寫進去才行，否則會報 ORA-00979 的錯誤 :

修正後可以撈取出每個部門 ID 加上這個部門所屬的 JOB_ID 的薪資水平 (salary) :

那現在有個問題就是，如果我們在撈出 (部門ID + JOB_ID) 的薪資水平後，想要再統計這個部門的所有薪資，那就必須再寫另外一個 SQL 只針對部門 ID 做 group by :

然後再花些功夫將這兩張表格合併。

Advance Group By 語法的用途是只使用一句 SQL 就可以將這兩張表格合併成一個，只需要在第一句的 group by 後面加上 rollup 就可以達到這個功能，例如查詢中有 a,b,c 三個欄位，使用 group by rollup(a,b,c) 之後會同時針對三種情況做 group by 並輸出結果，首先對 a+b+c 欄位做 group by ，其次是對 a+b 欄位做 group by ，最後是只對 a 欄位做 group by 。

利用 rollup 語法套入第一句 SQL 語法，就可以同時得到 (部門ID + JOB_ID) 的薪資水平，以及部門ID 加總的薪資水平 :

由於 rollup(a,b,c) 只會針對 a+b+c 、 a+b ，以及 c 做 group by ，若是要針對 a,b,c 的所有組合做 group by ，可以使用 CUBE ，例如 :

SQL> select department_id,job_id,sum(salary) from emp        where department_id in (10,20)       group by cube (department_id,job_id)       order by department_id,job_id;

這個結果就會針對 department_id + job_id 、 department_id ，以及 job_id 所有三種組合做 group by :

由於 CUBE 所做的組合太多，查詢出來的結果過於雜亂不易讀取，一般會再加上 grouping_id 輔助來使用 :

SQL> select department_id,job_id,sum(salary),       grouping_id(department_id,job_id) as grouping_id from emp         where department_id in (10,20)       group by cube (department_id,job_id)       order by department_id,job_id;

加上了 grouping_id 方便用來分辨 group by 的種類 :

有了 grouping_id ，就可以只列出所需要的 group by 結果集，例如只列出各個部門加總的薪資，以及各個 JOB_ID 所有的薪資水平，從上面的結果集只需挑選出 grouping_id 為 1 與 2 的結果即可 :

SQL>  select department_id,job_id,sum(salary),         grouping_id(department_id,job_id) as grouping_id from emp        group by cube (department_id,job_id)        having grouping_id(department_id,job_id) in (1,2)        order by department_id,job_id

Advance Group By 最後一種用法就是直接使用 grouping sets 來指定 group by 的欄位，例如只要針對 (department_id + job_id) 以及 (department_id + manager_id) 兩種組合做 group by :

SQL> select department_id,job_id,manager_id,sum(salary) from emp         where department_id=10       group by grouping sets ((department_id,job_id),(department_id,manager_id))       order by department_id,job_id,manager_id;

比起使用 CUBE 再過濾 grouping_id ， grouping sets 使用起來更為簡單明瞭。

• Analytic function

當 SQL 語句使用到運算函數例如加總 (sum) 、 平均 (avg) …等，同樣的會碰到 group by 欄位的問題，例如我們只想統計出 department_id=30 的所有員工薪資與部門平均薪資 :

SQL> select employee_id,job_id,salary,avg(salary) from employees where department_id=30 group by department_id;

由於沒有 group by 所有欄位，所以會造成 ORA-00979 的錯誤 :

若是將所有欄位進行 group by ，語法正確但是結果集卻不是我們想要的，最後只好將 SQL 修改為子查詢的語法來達到所希望的結果 :

SQL> select e.employee_id,e.job_id,e.salary,b.avg_salary        from employees e,             (select department_id,avg(salary) avg_salary                from employees group by department_id) b       where e.department_id=b.department_id and e.department_id=30;

這個時候就可以使用分析函數 (Analytic function) 來避免掉較為複雜的子查詢語法， Analytic function 最早從 Oracle 9i 開始有，透過 Analytic function 可以讓未參與統計的字段出現在 select 語句當中，對於每一行紀錄都能夠返回獨立的統計值，不會像group by那樣強制對每個字段做合併的操作，使用的方式為在運算函數之後加上 over partition by 的語法就可以單獨對這項欄位進行獨立的運算。

使用 Analytic function 將上述 SQL 修正之後便可以很簡潔的得到所要的結果集 :

SQL> select employee_id,job_id,salary,         avg(salary) over(partition by department_id)        from employees where department_id=30

使用 Analytic function 不僅讓程式簡化了，也避免了使用子查詢對於效能的負面影響。