 # 1. 分組 ## 1.1. 把相似的行數據聚集在一起 # 2. SQL分組的定義 ## 2.1. 數學上的“群” ...
1. 分組
1.1. 把相似的行數據聚集在一起
2. SQL分組的定義
2.1. 數學上的“群”（group）定義為 (G, •,e)，其中G是一個集合，• 表示G的二進位運算，而e則是G中的成員
2.2. 一個SQL 分組須滿足的兩個定理
2.2.1. 對於G的每一個成員e，e具有唯一性，並且存在一個或者多個e的實例
2.2.1.1. 分組不為空
2.2.1.1.1. 一個分組至少要擁有一個成員（行
2.2.1.1.2. 無法從一個空表中生成任何分組
2.2.1.2. 分組具有唯一性
2.2.1.2.1. 如果查詢語句使用了GROUP BY子句，那麼通常而言SELECT列表裡就不再需要使用DISTINCT關鍵字了
2.2.2. 對於G的每一個成員e，聚合函數COUNT的返回值大於0
2.2.2.1. COUNT永遠大於0
2.2.2.1.1. 無法從一個空表裡生成分組，因此一個分組至少會含有1行數據
2.2.2.1.2. 既然至少有1行數據，那麼COUNT查詢的結果自然至少等於1
2.2.2.1.3. 同時使用COUNT和GROUP BY的狀況
2.2.2.1.4. 如果不要GROUP BY子句，針對一個空表執行COUNT查詢當然會得到0
2.2.2.1.4.1. 只使用COUNT的狀況
2.3. SQL 分組的概念依存於SQL 查詢，沒有SQL 查詢就不會有SQL 分組
2.4. SQL 分組在技術上指的就是由行數據構成的結果集
3. PARTITION BY子句
3.1. 針對行數據進行分區（partition）或者分組（group），並根據其結果執行聚合運算
3.2. “動態的GROUP BY”
3.2.1. 在最終的結果集中允許出現多種由PARTITION BY生成的分區
3.3. 在同一個SELECT語句里我們可以按照不同的列進行分區，而且不同的視窗函數調用之間互不影響
4. Null的影響
4.1. 所有的Null歸入同一個分區或者分組
4.2. COUNT(column)會忽略Null
4.3. 如果希望把NULL值一併計入，則應該使用COUNT(*)
5. 使用視窗函數的代碼顯得短小精悍
5.1. sql
select deptno,
job,
count(*) over (partition by deptno) as emp_cnt,
count(job) over (partition by deptno,job) as job_cnt,
count(*) over () as total
from emp
DEPTNO JOB EMP_CNT JOB_CNT TOTAL
------ --------- ---------- ---------- ----------
10 CLERK 3 1 14
10 MANAGER 3 1 14
10 PRESIDENT 3 1 14
20 ANALYST 5 2 14
20 ANALYST 5 2 14
20 CLERK 5 2 14
20 CLERK 5 2 14
20 MANAGER 5 1 14
30 CLERK 6 1 14
30 MANAGER 6 1 14
30 SALESMAN 6 4 14
30 SALESMAN 6 4 14
30 SALESMAN 6 4 14
30 SALESMAN 6 4 14
5.1.1. 視窗函數的出現使得許多通常被認為單純使用標準SQL 難以解決的問題變得較為容易了
6. 使用多個自連接和標量子查詢
6.1. sql
select a.deptno, a.job,
(select count(*) from emp b
where b.deptno = a.deptno) as emp_cnt,
(select count(*) from emp b
where b.deptno = a.deptno and b.job = a.job) as job_cnt,
(select count(*) from emp) as total
from emp a
order by 1,2
DEPTNO JOB EMP_CNT JOB_CNT TOTAL
------ --------- ---------- ---------- ----------
10 CLERK 3 1 14
10 MANAGER 3 1 14
10 PRESIDENT 3 1 14
20 ANALYST 5 2 14
20 ANALYST 5 2 14
20 CLERK 5 2 14
20 CLERK 5 2 14
20 MANAGER 5 1 14
30 CLERK 6 1 14
30 MANAGER 6 1 14
30 SALESMAN 6 4 14
30 SALESMAN 6 4 14
30 SALESMAN 6 4 14
30 SALESMAN 6 4 14
7. 視窗函數DENSE_RANK OVER
7.1. sql
select max(case grp when 1 then rpad(ename,6) ||
' ('|| sal ||')' end) top_3,
max(case grp when 2 then rpad(ename,6) ||
' ('|| sal ||')' end) next_3,
max(case grp when 3 then rpad(ename,6) ||
' ('|| sal ||')' end) rest
from (
select ename,
sal,
rnk,
case when rnk ＜= 3 then 1
when rnk ＜= 6 then 2
else 3
end grp,
row_number()over (
partition by case when rnk ＜= 3 then 1
when rnk ＜= 6 then 2
else 3
end
order by sal desc, ename
) grp_rnk
from (
select ename,
sal,
dense_rank()over(order by sal desc) rnk
from emp
) x
) y
group by grp_rnk
TOP_3 NEXT_3 REST
--------------- --------------- -------------
KING (5000) BLAKE (2850) TURNER (1500)
FORD (3000) CLARK (2450) MILLER (1300)
SCOTT (3000) ALLEN (1600) MARTIN (1250)
JONES (2975) WARD (1250)
ADAMS (1100)
JAMES (950)
SMITH (800)
7.2. 視窗函數最為引人註目的功能之一就是，只需訪問一次原始數據就可以完成很多複雜的任務
7.3. 不需要自連接或臨時表，只要準備好必要的基礎數據集，剩下的工作交給視窗函數處理就行了
8. 為兩次變換後的結果集增加列標題
8.1. sql
select * from it_research
DEPTNO ENAME
------ --------------------
100 HOPKINS
100 JONES
100 TONEY
200 MORALES
200 P.WHITAKER
200 MARCIANO
200 ROBINSON
300 LACY
300 WRIGHT
300 J.TAYLOR
select * from it_apps
DEPTNO ENAME
------ -----------------
400 CORRALES
400 MAYWEATHER
400 CASTILLO
400 MARQUEZ
400 MOSLEY
500 GATTI
500 CALZAGHE
600 LAMOTTA
600 HAGLER
600 HEARNS
600 FRAZIER
700 GUINN
700 JUDAH
700 MARGARITO
8.2. sql
RESEARCH APPS
-------------------- ---------------
100 400
JONES MAYWEATHER
TONEY CASTILLO
HOPKINS MARQUEZ
200 MOSLEY
P.WHITAKER CORRALES
MARCIANO 500
ROBINSON CALZAGHE
MORALES GATTI
300 600
WRIGHT HAGLER
J.TAYLOR HEARNS
LACY FRAZIER
LAMOTTA
700
JUDAH
MARGARITO
GUINN
8.3. sql
select max(decode(flag2,0,it_dept)) research,
max(decode(flag2,1,it_dept)) apps
from (
select sum(flag1)over(partition by flag2
order by flag1,rownum) flag,
it_dept, flag2
from (
select 1 flag1, 0 flag2,
decode(rn,1,to_char(deptno),' '||ename) it_dept
from (
select x.*, y.id,
row_number()over(partition by x.deptno order by y.id) rn
from (
select deptno,
ename,
count(*)over(partition by deptno) cnt
from it_research
) x,
(select level id from dual connect by level ＜= 2) y
)
where rn ＜= cnt+1
union all
select 1 flag1, 1 flag2,
decode(rn,1,to_char(deptno),' '||ename) it_dept
from (
select x.*, y.id,
row_number()over(partition by x.deptno order by y.id) rn
from (
select deptno,
ename,
count(*)over(partition by deptno) cnt
from it_apps
) x,
(select level id from dual connect by level ＜= 2) y
)
where rn ＜= cnt+1
) tmp1
) tmp2
group by flag
