分組、子查詢、WITH、自定排序、串接 (DML 五)

※分組(GROUP BY)

分組以emp這張table最好講，就是以部門為單位，看每個部門的最高、最低、平均、加總…等

※彙總函數(Aggregate Functions)

SELECT
    count(sal),
    max(sal),
    min(sal),
    sum(sal),
    round(avg(sal)),
    median(sal)
FROM emp;

※彙總函數就是將同一個欄位的所有資料加起來，然後做最高、最低、平均、加總…等，上面的round不是彙總函數，只是因為小數位太長了，還可以配合DISTINCT使用(median除外)

※如上的範例，彙總函數可以不用分組

※median是取中間數，如有15列，會取第7列；如有16列，會取第8和第9列再除2

※還有很多彙總函數，看官網(最下面)

※分組

SELECT
    -- deptno,
    count(sal),
    max(sal),
    min(sal),
    sum(sal),
    round(avg(sal)),
    median(sal)
FROM emp
-- WHERE sal > 500
GROUP BY deptno
-- HAVING deptno > 10
-- ORDER BY deptno DESC;

※分組就是 GROUP BY，注意 WHERE、GROUP BY、ORDER BY 的順序

※HAVING 是針對 GROUP BY的過濾條件，就像WHERE是針對表的過濾一樣

※註解的部分都可以用，但要注意要顯示的欄位和排序(ORDER BY)，只能顯示GROUP BY的欄位

因為彙總函數是好幾個欄位加起來的，如果打其他的欄位，它不知道要怎麼顯示和排序
而GROUP BY的欄位比較例外，因為以它分組，那它的值一定都一樣

但是HAVING、ORDER BY 如果配合彙總函數就可以使用其他欄位，如下：
HAVING SUM(sal) > 500 --本來只能配合GROUP BY的欄位使用
ORDER BY SUM(mgr) DESC--本來只能配合GROUP BY的欄位使用
因為這時所有欄位合在一起，當然只剩一個值，所以就都OK了

※GROUP BY還可以很多欄位，而且也可以配合join使用

※彙總函數裡的彙總函數

SELECT
    max(round(avg(sal)))
FROM emp
-- WHERE sal > 500
GROUP BY deptno
ORDER BY sal DESC;

※彙總函數裡放彙總函數，叫嵌套，但要使用嵌套，只能顯示一個欄位，不能再打其他的欄位和彙總函數，而且一定要GROUP BY(沒有嵌套可以不用)

※ORDER BY這時又可以使用其他欄位了(分組不行)

※rollup、cube 的使用

SELECT d.deptno, sum(sal), ROUND(avg(sal), 2), min(sal), max(sal), count(sal)
from emp e join dept d on(e.deptno = d.deptno)
group by rollup(d.deptno)
order by d.deptno;
    
SELECT d.deptno, sum(sal), ROUND(avg(sal), 2), min(sal), max(sal), count(sal)
from emp e join dept d on(e.deptno = d.deptno)
group by cube(d.deptno)
order by d.deptno;

※結果：

※兩支SQL就只差在 group by 的不同而已

※會增加一行，配合彙總函數而有所不同

※尤於group by只有一個欄位，所以此時 rollup、cube 沒什麼不同

※rollup、cube 的差異

SELECT d.deptno, job, sum(sal), ROUND(avg(sal), 2), min(sal), max(sal), count(sal)
from emp e join dept d on(e.deptno = d.deptno)
group by rollup(d.deptno, job)
order by d.deptno;
    
SELECT d.deptno, job, sum(sal), ROUND(avg(sal), 2), min(sal), max(sal), count(sal)
from emp e join dept d on(e.deptno = d.deptno)
group by cube(d.deptno, job)
order by d.deptno;

※結果：

※此時會發現 cube 還會多出綠框的部分
因為group多一個job，所以 job 也會做進一步的分析，但要注意此時的 job 並沒有按照部門分類

※圖少打了一個字，是cube，不是cub

※Rollup、Cube 的意思

rollup是捲起來的意思，這裡指的是從右到左捲起來，為n+1
cube是立方的意思，這裡指的是2的x次方
假設rollup(c1, c2)=>2+1，所以要union all 3次
也就是c1, c2 union all c1 union all null

假設cube(c1, c2)=>2的2次方，所以要union all 4次
也就是c1 union all c2 union all c1, c2 union all null

※rollup 和 union all

SELECT d.deptno, job, sum(sal)
from emp e join dept d on(e.deptno = d.deptno)
group by rollup(d.deptno, job)
order by d.deptno;
    
Select d.deptno, job, sum(sal) from emp e join dept d on(e.deptno = d.deptno) group by d.deptno, job
    union all
Select d.deptno, null,sum(sal) from emp e join dept d on(e.deptno = d.deptno) group by d.deptno
    union all
Select null, null,sum(sal) from emp e join dept d on(e.deptno = d.deptno);

※這兩條SQL是一樣的，只是union all 排序不好排

※cube 和 union all

SELECT d.deptno, job, sum(sal)
from emp e join dept d on(e.deptno = d.deptno)
group by cube(d.deptno, job)
order by d.deptno;
    
Select d.deptno, null, sum(sal) from emp e join dept d on(e.deptno = d.deptno) group by d.deptno
    union all
Select null, job, sum(sal) from emp e join dept d on(e.deptno = d.deptno) group by job
    union all
Select d.deptno, job, sum(sal) from emp e join dept d on(e.deptno = d.deptno) group by d.deptno, job
    union all
Select null, null,sum(sal) from emp e join dept d on(e.deptno = d.deptno)
order by deptno;

※這兩條SQL也是一樣的，只是union all 排序不好排

※GROUPING SETS

Group by 可以說是Grouping sets 的其中之一
grouping sets 裡面可以接收好多欄位，而 group by 等同 grouping sets 的一個參數的方法
只是一個參數而且有多個欄位時，要用圓括號包起來

多個參數表示第一個參數做完後，會在接上第二個參數的group by，
然後再接第三個參數，一直接到最後一個參數
參數裡也可以加上小計的功能，就是 rollup、cube

一個SQL只能有一個GROUP BY ，但GROUPING SETS可以多個
所以還可以用group by xxx, grouping sets(...), grouping sets(...)

SELECT d.deptno, job, sum(sal)
from emp e join dept d on(e.deptno = d.deptno)
group by d.deptno, job
order by d.deptno;
    
SELECT d.deptno, job, sum(sal)
from emp e join dept d on(e.deptno = d.deptno)
group by grouping sets((d.deptno, job))
order by d.deptno;
    
SELECT d.deptno, job, sum(sal)
from emp e join dept d on(e.deptno = d.deptno)
group by grouping sets(d.deptno, job)
order by d.deptno;

※第一和第二個SQL完全一樣

※結果：

※上圖是第一和第二個SQL的結果

※第三個SQL會以第一個參數的欄位進行加總，然後再用第二個參數的欄位進行加總

※grouping sets(c1, c2, c3) 會對c1, c2, c3 group by，等同 group by c1, c2, c3

※grouping sets((c1, c2), c3) 會對(c1, c2) 做 group by，做完再加上 c3 的 group by

※GROUPING

rollup、cube因為是小計，所以沒有彙總的部分會變成空白，使用這個可以寫一些資訊，但也因為這樣，所以使用Grouping時，一定要有 rollup 或 cube

SELECT 
    d.deptno, 
    GROUPING(d.deptno) xxx, 
    decode(GROUPING(d.deptno), 1, '小計', d.deptno) deptno,
    sum(sal)
from emp e join dept d on(e.deptno = d.deptno)
group by cube(d.deptno)
order by d.deptno;

※結果：

※可以發現grouping回傳的是1和0，1表示有空白

※最左邊的deptno到第三列的deptno最下面有變化了

※使用 Grouping + cube 有兩個欄位時

SELECT 
    d.deptno,
    job,
    GROUPING(d.deptno) d,
    GROUPING(job) j, 
    decode(GROUPING(d.deptno), 1, '工作分類小計') xxx,
    decode(GROUPING(job), 1, '部門分類小計') ooo,
    decode(GROUPING(d.deptno), 
        0, decode(GROUPING(job), 1, '部門分類小計', d.deptno),
        1, decode(GROUPING(job), 1, '總小計', '工作分類小計')
    ) zzz,
    sum(sal)
from emp e join dept d on(e.deptno = d.deptno)
group by cube(d.deptno, job)
order by d.deptno;

※結果：

※GROUPING()裡只能放一個欄位

※D對應XXX；J對應OOO
但比較奇怪的是我的J寫的是GROUPING(job)，OOO也是這麼寫，但如上圖，算的是部門的加總；D也是一樣，所以我才將J寫成部門分類小計；而D寫成工作分類小計

※ZZZ是兩個合併的寫法，因為兩個都是1，由D和J可以知道

※GROUPING_ID

SELECT 
    d.deptno,
    job,
    GROUPING_ID(d.deptno) AS dno,
    GROUPING_ID(job) AS job,
    GROUPING_ID(d.deptno, job) AS dno_and_job,
    GROUPING_ID(job, d.deptno) AS job_and_dno,
    sum(sal)
from emp e join dept d on(e.deptno = d.deptno)
group by cube(d.deptno, job)
order by d.deptno;

※結果：

※GROUPING_ID()裡面可放多個欄位

※數字怎麼來的和二進位有關，如下：

※如果是空的還是0，不是0才會做上圖的次方問題，而任何數的零次方都是1，然後全加起來，順序是有差的，所以dno_and_job 和 job_and_dno有點不太一樣

※GROUP_ID()

無參數
一筆紀錄的結果如有重覆會一直累加，從0開始

※

SELECT d.deptno, sum(sal), group_id()
from emp e join dept d on(e.deptno = d.deptno)
group by grouping sets(d.deptno, d.deptno)
-- having group_id() = 0
order by d.deptno;

※結果：

※加上group_id() = 0 可將重覆行去除

※子查詢

也就是查詢裡還有查詢
顯示的結果有一個欄位，但有好筆記錄，簡稱單欄多行

※SELECT 裡的子查詢

SELECT hiredate, (SELECT loc FROM dept WHERE deptno = 20) FROM emp;
    
SELECT 
    empno,
    ename,
    (
        SELECT grade FROM salgrade 
        WHERE e.sal BETWEEN losal AND hisal
    ) grade
FROM emp e
ORDER BY grade;

※在欄位裡的子查詢，結果只能是單欄單行

※FROM 裡的子查詢

SELECT * FROM (
    SELECT * FROM emp
);
    
SELECT * FROM dept d,(
    SELECT * FROM emp
) e
WHERE d.deptno = e.deptno;
------------------------------
------------------------------
SELECT * FROM dept d, emp e
WHERE d.deptno = e.deptno;
    
SELECT * FROM dept d /*INNER*/ JOIN emp e
ON(d.deptno = e.deptno);
    
SELECT * FROM dept d /*INNER*/ JOIN emp e
USING(deptno);
    
SELECT * FROM dept NATURAL JOIN emp;

※第二個SELECT，也可以是虛線下的四個SELECT任何一組，不懂join的可看第四篇

※這應該是子查詢裡最難理解的了，實際上在一般select的結果都是多欄多行(所以FROM裡的子查詢可以是單/多欄單/多行)，而放在裡面的子查詢也是多欄多行，所以可以放在裡面，但一般不會像我的範例這樣用，因為已經有表的名稱了，就直接用就好了，大部分都是裡面的子查詢沒有表名稱或者因為效能問題才會使用，例如子查詢是join出來的，所以沒有表名稱，只好寫在裡面，欄位的名稱就是select的名稱，如果要自己取就用別名(as)，可以說是個匿名table，如下：

SELECT grade,losal, hisal, ed.xxx, ed.dname
FROM salgrade s,(
    SELECT e.ename xxx, d.dname, e.sal s 
    FROM emp e, dept d
    WHERE d.deptno = e.deptno
) ed
WHERE
ed.s BETWEEN losal AND hisal;

※效能問題如下：

SELECT d.deptno, d.dname, count(*)
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY d.deptno,d.dname;
    
SELECT d.deptno, d.dname, e.c
FROM dept d,(
    SELECT deptno,count(*) c
    FROM emp
    GROUP BY deptno
    ) e
WHERE d.deptno = e.deptno;

※EMP表有14條記錄，DEPT表有4條記錄

※第一個SELECT總共要查14*4=56次

※第二個SELECT總共要查dept表的4次*GROUP BY的結果3次+count的14次，所以總共是26次

※有工作經驗的都知道這資料量太少了，給它*100也不過分，
所以第一個SELECT：1400*400=560000
第二個SELECT：400*300+1400=121400
通過結果可以發現資料量越大，效能差的越多

※WHERE裡的子查詢

SELECT * FROM emp
where sal > (
    SELECT max(sal) FROM emp WHERE job = 'SALESMAN'
);
    
SELECT * FROM emp
where (job,sal,deptno,nvl(comm,0)) = (
    SELECT job,sal,deptno,nvl(comm,0) FROM emp where ename = 'FORD'
);

※第一個SELECT的子查詢結果是單欄單行，這應該最多人熟悉的使用方式了

※第二個SELECT的子查詢結果是多欄單行，要注意如果有null，它不會顯示任何結果，所以我加了NVL

※如果SELECT的子查詢結果是單欄多行，看第一篇的最下面有ANY/SOME/ALL

※HAVING裡的子查詢

SELECT
    deptno,
    count(sal),
    max(sal),
    min(sal),
    sum(sal),
    round(avg(sal)),
    median(sal)
FROM emp
WHERE sal > 500
GROUP BY deptno
HAVING max(sal) > (
    SELECT avg(sal) FROM emp
)
ORDER BY deptno DESC;

※WITH

子查詢寫在FROM是沒有名字的，所以叫匿名表，而WITH子句可以有名字，所以是虛擬表，如下使用：

WITH  V AS (
    SELECT * FROM EMP
)
SELECT * FROM  V, DEPT D WHERE V.DEPTNO = D.DEPTNO;

也可以用多張虛擬表格

WITH  V1 AS (
    SELECT * FROM EMP
),
V2 AS (
    SELECT * FROM DEPT
)
SELECT * FROM  V1, V2 WHERE V1.DEPTNO = V2.DEPTNO;

※注意AS一定要打，且「)」後面不能加「;」

※WITH裡面不能再放WITH

※自定排序

select * from (
    select 'a' as n from dual union all
    select 'b' as n from dual union all
    select 'c' as n from dual
)
order by decode(n, 'b', 1, 'a', 2, 'c', 3);
    
with xxx as(
    select 'a' as n from dual union all
    select 'b' as n from dual union all
    select 'c' as n from dual
) select n from xxx
ORDER BY n desc;

※可用子查詢和 WITH 的方式達成

※串接

listagg 可以實現串接的功能
語法如下：

SELECT LISTAGG(xxx, ':')
WITHIN GROUP (ORDER BY xxx) result
FROM (
    SELECT TO_CHAR(DATE '2017-01-01' + (LEVEL-1) , 'YYYY/MM/DD') xxx
    FROM DUAL
    CONNECT BY DATE '2017-01-01' + (LEVEL-1) <= DATE '2017-06-30'
);

※其中LISTAGG 的第二個參數不是必要的，如果不寫就會將全部的字連在一起

※WITHIN GROUP 本身，和後面的 ORDER BY 都是一定要的

※SELECT 只能放彙總函數

※後面還可加 OVER (PARTITION BY ...)，我沒試過，是不是加了之後，就不一定要使用彙總函數了，有待驗證