到目前為止，查詢回傳的列數取決於 where 過濾——過濾作用在 from 子句與其 join 產生的資料集上（outer join 可能產出比參考資料集更多的列，cross join 更是笛卡兒積）。本章來看聚合（aggregate）：一次對多列輸入計算出摘要值，回傳的摘要列數遠少於通過 where 過濾的列數。
聚合（Map/Reduce）：GROUP BY#
GROUP BY 子句把聚合引入 SQL，效果與其他系統的 map/reduce 大致相同：把資料 map 到不同群組，在每個群組內把資料 reduce 成單一值。第一個例子：計算每個年代辦了幾場比賽：
select extract('year'
from
date_trunc('decade', date))
as decade,
count(*)
from races
group by decade
order by decade; decade │ count
════════╪═══════
1950 │ 84
1960 │ 100
1970 │ 144
1980 │ 156
1990 │ 162
2000 │ 174
2010 │ 156
(7 rows)預告：用視窗函式計算年代間差異
各年代之間的差異可以用視窗函式（本章稍後介紹）輕鬆算出，先預覽一下：
with races_per_decade
as (
select extract('year'
from
date_trunc('decade', date))
as decade,
count(*) as nbraces
from races
group by decade
order by decade
)
select decade, nbraces,
case
when lag(nbraces, 1)
over(order by decade) is null
then ''
when nbraces - lag(nbraces, 1)
over(order by decade)
< 0
then format('-%3s',
lag(nbraces, 1)
over(order by decade)
- nbraces)
else format('+%3s',
nbraces
- lag(nbraces, 1)
over(order by decade))
end as evolution
from races_per_decade;lag() over(order by decade) 讓我們看到前一列，進而計算當前列與前一列的差；外面再包一個 CASE 來控制輸出格式：
decade │ nbraces │ evolution
════════╪═════════╪═══════════
1950 │ 84 │
1960 │ 100 │ + 16
1970 │ 144 │ + 44
1980 │ 156 │ + 12
1990 │ 162 │ + 6
2000 │ 174 │ + 12
2010 │ 156 │ - 18
(7 rows)PostgreSQL 除了常見的 sum、count、avg，還有一些更有趣的聚合，例如 bool_and：起始為 true，只有看到的每一列都為 true 才維持 true。用它可以找出「整個生涯參賽卻一場都沒完賽」的車手：
with counts as
(
select driverid, forename, surname,
count(*) as races,
bool_and(position is null) as never_finished
from drivers
join results using(driverid)
join races using(raceid)
group by driverid
)
select driverid, forename, surname, races
from counts
where never_finished
order by races desc;結果多得驚人：202 位車手從未完賽過任何一場，其中 117 位只參加過一場。再進一步依賽季統計「該季參賽卻全季未完賽的車手」：
with counts as
(
select date_trunc('year', date) as year,
count(*) filter(where position is null) as outs,
bool_and(position is null) as never_finished
from drivers
join results using(driverid)
join races using(raceid)
group by date_trunc('year', date), driverid
)
select extract(year from year) as season,
sum(outs) as "#times any driver didn't finish a race"
from counts
where never_finished
group by season
order by sum(outs) desc
limit 5;注意
filter(where …)語法：讓聚合只針對通過過濾的列更新計算。這裡用它計算 position 為 null（車手因故未抵達終點）的賽果數。
結果顯示 1989 是很糟的一季（139 次），其後是 1953、1955、1990、1956。
沒有 GROUP BY 的聚合#
不使用 group by 也能對資料集計算聚合——意思是整個結果集被當成單一的隱含群組：
select count(*)
from races;篩選群組：HAVING#
想知道那些車手沒完賽的原因嗎？來查一下：
\set season 'date ''1978-01-01'''
select status, count(*)
from results
join races using(raceid)
join status using(statusid)
where date >= :season
and date < :season + interval '1 year'
and position is null
group by status
having count(*) >= 10
order by count(*) desc;HAVING 子句的作用是把結果集過濾到只留下符合條件的群組——就像 where 之於個別列。為避免歧義，having 不允許引用 select 的輸出別名。
status │ count
════════════════════╪═══════
Did not qualify │ 55
Accident │ 46
Engine │ 37
Did not prequalify │ 25
Gearbox │ 13
Spun off │ 12
Transmission │ 12
(7 rows)未完賽的主因是根本沒取得參賽資格，其次是事故。
Grouping Sets#
傳統聚合的限制是一次只能用單一群組定義。若想同時對多個群組計算聚合，SQL 提供 grouping sets 功能。F1 的積分同時用於車手冠軍與車隊（constructor）冠軍的計算——能在一個查詢裡對同一批積分算出兩種總和嗎？當然可以：
\set season 'date ''1978-01-01'''
select drivers.surname as driver,
constructors.name as constructor,
sum(points) as points
from results
join races using(raceid)
join drivers using(driverid)
join constructors using(constructorid)
where date >= :season
and date < :season + interval '1 year'
group by grouping sets((drivers.surname),
(constructors.name))
having sum(points) > 20
order by constructors.name is not null,
drivers.surname is not null,
points desc; driver │ constructor │ points
═══════════╪═════════════╪════════
Andretti │ ¤ │ 64
Peterson │ ¤ │ 51
Reutemann │ ¤ │ 48
Lauda │ ¤ │ 44
Depailler │ ¤ │ 34
Watson │ ¤ │ 25
Scheckter │ ¤ │ 24
¤ │ Team Lotus │ 116
¤ │ Brabham │ 69
¤ │ Ferrari │ 65
¤ │ Tyrrell │ 41
¤ │ Wolf │ 24
(12 rows)當聚合是為車隊群組計算時，driver 欄為 null；為車手群組計算時，constructor 欄為 null。
另外兩種 grouping sets 只是語法糖：
rollup：依序對各欄位產生排列組合，主要用於階層式資料。例如查 Prost 與 Senna 的積分，group by rollup(drivers.surname, constructors.name)一趟就能取得每位車手在各車隊的累積積分、每位車手的總分（constructor 為 null 的列，Prost 合計 798.5、Senna 647），最後一列則是所有人的總分（1445.5）。cube：展開到所有排列組合（含部分組合）。除了 rollup 給的內容，還多了「不分車手、只看車隊」的積分——因為 Prost 與 Senna 都曾效力 McLaren、Renault、Williams（甚至有兩季同在 McLaren），例如 McLaren 合計 909.5 分。
cube 查詢與完整結果
select drivers.surname as driver,
constructors.name as constructor,
sum(points) as points
from results
join races using(raceid)
join drivers using(driverid)
join constructors using(constructorid)
where drivers.surname in ('Prost', 'Senna')
group by cube(drivers.surname, constructors.name); driver │ constructor │ points
════════╪═════════════╪════════
Prost │ Ferrari │ 107
Prost │ McLaren │ 458.5
Prost │ Renault │ 134
Prost │ Williams │ 99
Prost │ ¤ │ 798.5
Senna │ HRT │ 0
Senna │ McLaren │ 451
Senna │ Renault │ 2
Senna │ Team Lotus │ 150
Senna │ Toleman │ 13
Senna │ Williams │ 31
Senna │ ¤ │ 647
¤ │ ¤ │ 1445.5
¤ │ Ferrari │ 107
¤ │ HRT │ 0
¤ │ McLaren │ 909.5
¤ │ Renault │ 136
¤ │ Team Lotus │ 150
¤ │ Toleman │ 13
¤ │ Williams │ 130
(20 rows)共同資料表運算式：WITH#
前面看到很多車手因事故（accident）未能完賽，僅次於未取得資格。這讓人好奇 F1 比賽有多危險。先看史上事故最多的五個賽季：
select extract(year from races.date) as season,
count(*)
filter(where status = 'Accident') as accidents
from results
join status using(statusid)
join races using(raceid)
group by season
order by accidents desc
limit 5;前五名依序是 1977（60 次）、1975（54）、1978（48）、1976（48）、1985（36）——最危險的賽季集中在 70 年代末、80 年代初，所以用一個「主控台友善」的直方圖查詢放大這段期間：
with accidents as
(
select extract(year from races.date) as season,
count(*) as participants,
count(*) filter(where status = 'Accident') as accidents
from results
join status using(statusid)
join races using(raceid)
group by season
)
select season,
round(100.0 * accidents / participants, 2) as pct,
repeat(text '■',
ceil(100*accidents/participants)::int
)
as bar
from accidents
where season between 1974 and 1990
order by season;CTE 裡先算出每季所有比賽的總參賽人次（count(*)）與其中發生事故的人次（用前面介紹的 filter 子句）；主查詢再算出事故率百分比，甚至用重複的 Unicode 黑方塊字元畫出水平長條圖：
season │ pct │ bar
════════╪═══════╪════════════════
1974 │ 3.67 │ ■■■
1975 │ 14.88 │ ■■■■■■■■■■■■■■
1976 │ 11.06 │ ■■■■■■■■■■■
1977 │ 12.58 │ ■■■■■■■■■■■■
1978 │ 10.19 │ ■■■■■■■■■■
1979 │ 7.20 │ ■■■■■■■
1980 │ 7.83 │ ■■■■■■■
1981 │ 3.56 │ ■■■
1982 │ 0.86 │
1983 │ 0.00 │
1984 │ 5.58 │ ■■■■■
1985 │ 8.87 │ ■■■■■■■■
1986 │ 6.07 │ ■■■■■■
1987 │ 5.97 │ ■■■■■
1988 │ 0.61 │
1989 │ 0.81 │
1990 │ 1.29 │ ■
(17 rows)Wikipedia 的 F1 賽季列表有一張各季車手冠軍與車隊冠軍（該年積分最高者）的表格。要用 SQL 算出同樣的結果，需要先加總每位車手與每個車隊的積分，再挑出各季最高分者：
with points as
(
select year as season, driverid, constructorid,
sum(points) as points
from results join races using(raceid)
group by grouping sets((year, driverid),
(year, constructorid))
having sum(points) > 0
order by season, points desc
),
tops as
(
select season,
max(points) filter(where driverid is null) as ctops,
max(points) filter(where constructorid is null) as dtops
from points
group by season
order by season, dtops, ctops
),
champs as
(
select tops.season,
champ_driver.driverid,
champ_driver.points,
champ_constructor.constructorid,
champ_constructor.points
from tops
join points as champ_driver
on champ_driver.season = tops.season
and champ_driver.constructorid is null
and champ_driver.points = tops.dtops
join points as champ_constructor
on champ_constructor.season = tops.season
and champ_constructor.driverid is null
and champ_constructor.points = tops.ctops
)
select season,
format('%s %s', drivers.forename, drivers.surname)
as "Driver's Champion",
constructors.name
as "Constructor's champion"
from champs
join drivers using(driverid)
join constructors using(constructorid)
order by season;這已是整頁長的查詢，重點在於 CTE 的串鏈（daisy chain）：
pointsCTE 用 grouping sets 在單次掃描中，同時算出各季每位車手與每個車隊的積分總和。topsCTE 以 points 為來源，算出各季車手與車隊的最高積分。之所以分成兩步，是因為 SQL 不允許聚合套聚合（ERROR: aggregate function calls cannot be nested）——想在同一查詢裡同時有「積分總和」與「總和的最大值」，就得用兩階段管線。champsCTE 用 tops 與 points 把結果限縮到冠軍（積分等於最大值者）；這裡 points 被使用了兩次、各取別名：找車手冠軍時是champ_driver，找車隊冠軍時是champ_constructor。- 最外層查詢把 champs 格式化成接近 Wikipedia 頁面的輸出。
冠軍查詢結果（68 列節錄）
season │ Driver's Champion │ Constructor's champion
════════╪═══════════════════╪════════════════════════
1950 │ Nino Farina │ Alfa Romeo
1951 │ Juan Fangio │ Ferrari
1952 │ Alberto Ascari │ Ferrari
1955 │ Juan Fangio │ Mercedes
1957 │ Juan Fangio │ Maserati
...
1988 │ Alain Prost │ McLaren
1990 │ Ayrton Senna │ McLaren
1992 │ Nigel Mansell │ Williams
1993 │ Alain Prost │ Williams
...
2014 │ Lewis Hamilton │ Mercedes
2015 │ Lewis Hamilton │ Mercedes
2016 │ Nico Rosberg │ MercedesDistinct On#
distinct on 是另一個實用的 PostgreSQL 擴充。文件是這麼說的：SELECT DISTINCT ON (expression [, …]) 只保留每組運算式求值相等的列中的第一列；其運算式的解讀規則與 ORDER BY 相同。注意除非用 ORDER BY 確保想要的列排在最前面，否則「第一列」是不可預測的。
列出 F1 史上所有贏過比賽的車手：
select distinct on (driverid)
forename, surname
from results
join drivers using(driverid)
where position = 1;共 107 位，可用 count(distinct(driverid)) 驗證。SQL 的傳統做法則是對車手做聚合、每位車手一組：
select forename, surname
from results join drivers using(driverid)
where position = 1
group by drivers.driverid;這裡的 group by 沒搭配任何聚合函式——這是合法用法，可用來強制結果集中每組只留唯一一筆。
結果集操作#
SQL 也包含把多個查詢結果集合併為一的集合運算：union、intersect、except。資料模型中的 driverstandings 與 constructorstandings 內容衍生自 results 表，方便查詢較小的資料集。用 union 把多個查詢的結果組起來：
(
select raceid,
'driver' as type,
format('%s %s',
drivers.forename,
drivers.surname)
as name,
driverstandings.points
from driverstandings
join drivers using(driverid)
where raceid = 972
and points > 0
)
union all
(
select raceid,
'constructor' as type,
constructors.name as name,
constructorstandings.points
from constructorstandings
join constructors using(constructorid)
where raceid = 972
and points > 0
)
order by points desc;單一查詢就取得比賽 972 中車手與車隊的積分榜（22 列，Mercedes 136 分居首）。這是 union 的經典用法：在每個分支加上靜態欄位值（type），標明每列結果的來源。
- 各分支加上括號並非必要，但能提升可讀性，也讓 order by 作用的資料集一目瞭然。
- 這裡用
union all是因為查詢的寫法已確保不會產生重複列；若需要去除重複，才用不帶 all 的union。
接著一個稍繞的查詢：列出在比賽 972（2017-04-30 俄羅斯站）沒拿到積分、但在前一場 971（2017-04-16 巴林站）有積分的車手：
(
select driverid,
format('%s %s',
drivers.forename,
drivers.surname)
as name
from results
join drivers using(driverid)
where raceid = 972
and points = 0
)
except
(
select driverid,
format('%s %s',
drivers.forename,
drivers.surname)
as name
from results
join drivers using(driverid)
where raceid = 971
and points = 0
)
;結果是 Romain Grosjean 與 Daniel Ricciardo 兩位。同樣的結構改用 intersect，得到的則是兩場比賽都沒積分的車手。
( select name, location, country from circuits order by position <-> point(2.349014, 48.864716) ) except ( select name, location, country from circuits order by point(lng,lat) <-> point(2.349014, 48.864716) ) ;回傳 0 列，代表索引可靠、
position欄與 lng/lat 資料一致。用 except 就能輕鬆實作迴歸測試！