SQL技巧（4）分組的定義和性質

（Molinaro-SQL cookbook筆記）

給部門10的員工分組，且傳回組中成員數，最高工資和最低工資

select deptno, count(*) as cnt, max(sal) as hi_sal, min(sal) as lo_sal
from emp
where deptno = 10
group by deptno
           

SQL組的定義

把SQL組定義為（G, e），其中G是使用GROUP BY的單個查詢或自包含查詢的結果集，e是G的一個成員，而且必須滿足下列規則：

（1）對與G中的每個e都是獨特（各不相同）的，而且表示e的一個或多個執行個體

（2）對于G中的每個e，聚集函數COUNT會傳回一個大于0的值

注：在上述規則中用“行”替換“e“将非常準确，因為結果集裡的行就代表了組。

性質1：組是非空的

根據組的定義，不能從空表中建立組，建立如下空表

create table fruits (name varchar(10))
           

采用以下三種方法對這個空表建立組

select name
from fruits
group by name
           

select conut(*) as cnt
from fruits
group by name
           

select name, count(*) as cnt
from fruits
group by name
           

結果都是（no rows selected)，從空表中建立SQL的組是不可能的

性質2：組是獨特的

insert into fruits values ('Oranges')
insert into fruits values ('Oranges')
insert into fruits values ('Oranges')
insert into fruits values ('Apple')
insert into fruits values ('Peach')
           

select name 
from fruits
group by name
           

NAME

Apple

Orange

Peach

select name, count(*) as cnt
from fruits
group by name
           

NAME	CNT
Apple	1
Oranges	3
Peach	1

是以在查詢中使用GROUP BY時，SELECT清單中就不必使用DISTINCT

悖論：

由于SQL結果集和表的性質，可能可以傳回一個包含重複組的結果集

例1：

select coalesce(name, 'NULL') as name, count(*) as cnt
from fruits
group by name
union all
select coalesce(name, 'NULL') as name, count(*) as cnt
from fruits
group by name
           

NAME	CNT
Apple	1
Oranges	3
Peach	1
NULL	3
Apple	1
Oranges	3
Peach	1
NULL	3

對于這種情況，每個GROUP BY生成的結果集符合組的定義，隻有合并兩個GROUP BY查詢結果集時，才會建立包含重複組的多集，上述查詢中使用了UNION ALL，它不是一個集合操作，也調用了兩次GROUP BY。

例2：

select x.*
from (
       select coalesce(name, 'NULL') as name, count(*) as cnt
       from fruits
       group by name
     )x,
     (select deptno from dept) y
           

NAME	CNT
Apple	1
Apple	1
Apple	1
Apple	1
Oranges	3
Oranges	3
Oranges	3
Oranges	3
Peach	1
Peach	1
Peach	1
Peach	1
NULL	3
NULL	3
NULL	3
NULL	3

此查詢中使用了笛卡爾積，隻有先實作分組，再執行笛卡爾積才會起作用。其實兩個例子都沒有對SQL組的定義造成任何破壞

性質3：每組的COUNT絕不為0

注：此處指包含GROUP BY的COUNT，而不是使用COUNT本身

悖論:

SQL中NULL是合法的”值“，由于聚集函數會忽略NULL，是以當一個表隻包含一行，而且它的值是NULL，那麼在GROUP BY查詢時COUNT會傳回什麼呢？

insert into fruits values (null)
insert into fruits values (null)
insert into fruits values (null)
           

select coalesce(name, 'NULL') as name, count(name) as cnt
from fruits
group by name
           

NAME	CNT
Apple	1
NULL
Oranges	3
Peach	1

使用COUNT(*)代替COUNT(NAME)可避免NULL組悖論，如果傳遞給聚集函數的列中存在NULL值，将忽略這些值。傳遞*時COUNT對行進行技術，而不對實際列值進行技術。

SELECT和GROUP BY的關系

當使用聚集函數時，對于SELECT清單中的項，如果沒有作為聚集函數的參數，那麼它必須是組的一部分：

select deptno, count(*) as cnt
from emp
group by deptno
           

一定要在GROUP BY子句中列出DEPTNO

由使用者定義的函數，視窗函數和非關聯的标量子查詢傳回的常量、标量值都是此規則的例外

由于SELECT子句是GROUP BY子句之後進行計算的，是以SELECT 清單允許這些結構，而且不必（某些情況下不能）在GROUP BY子句中指定

select 'hello' as msg, 
       1 as num, deptno, 
       (select count(*) from emp) as total, 
       count(*) as cnt
from emp
group by deptno
           

上述查詢中SELECT清單中沒有包含在GROUP BY子句中項不會影響各DEPTNO的CNT值，也不會改變DEPTNO值。

是以，準确的比對規則是： 對于可能會更改組（或更改聚集函數傳回值）的SELECT清單項，一定要包含在GROUP BY子句中那麼什麼會改變組及聚集函數傳回值呢？答案是 SELECT對象表的其他列

如果SELECT清單中隻有聚集函數，那麼可以在GROUP BY子句中列出任意想要的有效列：

select count(*)
from emp
group by deptno
           

COUNT(*)

3

5

6

select count(*)
from emp
group by deptno, job
           

COUNT(*)
1
1
1
2
2
1
1
1
4

注：有可能DEPTNO和JOB的值都不是獨特的，但二者的組合是獨特的