聚合函數，聽起來高深莫測，但是其實很簡單，只要懂國小數學就懂聚合函數，我們從字面上的意義來看，聚合函數就是把多筆資料聚合成一筆資料，例如把多筆的資料聚合成一筆總筆數的資料(COUNT)，把多筆的金額然後聚合成一筆總金額(SUM)、把多筆的金額聚合選出一筆最大的金額(MAX)等等。

有哪些聚合函數?

• COUNT : 總筆數。
• MIN : 最小值。
• MAX : 最大值。
• SUM : 加總值。
• AVG : 平均值。

怎麼用?

告訴聚合函數要用哪一個欄位來聚合，例如你想知道有幾筆的訂單，如果這個資料表的設計是每一筆資料就是一筆訂單的話，那麼你隨意放哪個欄位都可以算出有幾筆的訂單，但是如果是想知道訂單金額最高的是哪一筆，那麼就要跟聚合函數說你要找的其實是訂單金額，這樣才會找出這一筆的金額。

SELECT COUNT(*) FROM orders;

SELECT MAX(訂單金額) FROM orders;

聚合函數的快樂夥伴-群組(GROUP BY)

只有聚合函數，能夠做的事情還有限，它還需要另一個好搭檔GROUP BY，以SUM聚合函數為例，如果對整個資料表使用SUM，那麼就會回傳所有客戶加起來的總金額，但是我們希望的是計算出每個客戶的總金額，加上 GROUP BY 讓資料庫知道 SUM 聚合函數的範圍。

SELECT 客戶, SUM(金額) 
FROM orders
GROUP BY 客戶;

聚合函數的快樂夥伴-四捨五入(ROUND)

計算平均值的聚合函數會有除不盡的問題，我們可以使用快樂夥伴(?)四捨五入來讓資料取出我們想要的值，例如我們希望計算的結果只取到小數點第二位。

SELECT ROUND(42.4382,2) -- 輸出結果為42.44

實戰一個小範例

這是一個訂飲料的小範例，包含顧客資料表、訂單資料表、訂單項目資料表、付款方式資料表以及品項資料表。

創建訂飲料系統的資料表

資料表會使用主鍵與外鍵進行關聯，不過這個範例主要只是要示範聚合函數所使用，並不會對於之前Day 18 所提的外鍵限制進行太多的探討。

CREATE TABLE customers(
	id SERIAL PRIMARY KEY,
	name VARCHAR(300) NOT NULL,
	phone VARCHAR(300) NOT NULL
);

CREATE TABLE products(
	id SERIAL PRIMARY KEY,
	name VARCHAR(300) NOT NULL,
	price INT
);

CREATE TABLE payments(
	id SERIAL PRIMARY KEY,
	name VARCHAR(300) NOT NULL
);

CREATE TABLE orders(
	id SERIAL PRIMARY KEY,
	customer_id INT REFERENCES customers(id),
	payment_id INT REFERENCES payments(id),
	note VARCHAR(300)
);

CREATE TABLE order_items(
	id SERIAL PRIMARY KEY,
	order_id INT REFERENCES orders(id),
	product_id INT REFERENCES products(id),
	quantity INT
);

創建訂飲料系統的資料

寫入orders有一筆資料是插入NULL，因為這個欄位預設是可以不寫入的，不過因為插入資料的時候有指定要寫note欄位，所以要寫入NULL。

INSERT INTO customers (name,phone)
VALUES 
('陳冬瓜','0999099099'),
('蘇芋頭','0988777666'),
('張香腸','0977789789'),
('廖五花','0966566566');

INSERT INTO products (name,price)
VALUES 
('紅茶', 35),
('蓮藕茶', 40),
('枸杞茶', 40),
('苦茶', 50);

INSERT INTO payments (name)
VALUES ('現金'),('行動支付');

INSERT INTO orders (customer_id,payment_id,note)
VALUES
	(1,1,'不要吸管'),
	(2,2,NULL),
	(3,1,'不同的飲料分開放'),
	(4,2,'我有故事老闆有苦茶嗎?');

INSERT INTO order_items (order_id,product_id,quantity)
VALUES
	(1,1,3),
	(1,2,2),
	(2,3,1),
	(3,1,3),
	(3,2,3),
	(3,3,4),
	(4,4,6);

創建訂飲料系統的VIEW-1

為了簡化聚合函數範例的簡潔度，建立一個VIEW來封裝SQL的複雜度。

CREATE VIEW order_detail
AS SELECT order_id AS 訂單編號, 
c.name AS 訂購人,
c.phone AS 電話,
pa.name AS 付款方式,
p.name AS 飲料名稱,
p.price AS 飲料金額,
oi.quantity AS 飲料數量,
p.price * oi.quantity AS 小計
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON oi.product_id = p.id
JOIN customers AS c ON customer_id = c.id
JOIN payments AS pa ON payment_id = pa.id;

創建訂飲料系統的VIEW-2

這個範例要計算平均值是有一點傷腦筋，因為架構上必需使用尚未學習到的子查詢或視窗函數，不過因為這個系列尚未提及，所以我選擇使用在Day 14所提過的VIEW來當作替代方案，本文結尾也會提供視窗函數的使用方法。

CREATE VIEW order_total
AS SELECT 訂單編號, SUM(小計) AS 總計
FROM order_detail
GROUP BY 訂單編號
ORDER BY 訂單編號

使用聚合函數

COUNT

我們想知道，總共有幾筆的訂單。

SELECT COUNT(*) FROM orders;

MIN

SELECT MIN(price) AS min_price
FROM products;

MAX

SELECT MAX(price) AS max_price
FROM products;

SUM

SELECT 訂單編號,SUM(小計) FROM order_detail
GROUP BY 訂單編號
ORDER BY 訂單編號;

AVG()

SELECT SUM(總計) AS 總收入, ROUND(AVG(總計),2) AS 客單平均額
FROM order_total;

聚合的過濾(HAVING)

Day 12 學過使用where來對資料做過濾，聚合過的資料則必需用having來進行過濾，例如老闆只想看到超過新台幣100元的訂單資料，由於訂單總金額的資料是從SUM(小計)所產生的，因此就需要使用HAVING來進行過濾啦~

SELECT 訂單編號, SUM(小計) AS 總計
FROM order_detail
GROUP BY 訂單編號
HAVING SUM(小計) > 100
ORDER BY 訂單編號;

Window Function

我們有時候會很想看原始資料跟統計值放在同一個表格內，不過統計值通常是透過函數所產生出來的，那麼我們目前所學到的內容是無法跟原始資料一起顯示的，別氣餒，我們即將使用一個超級好用的視窗函數來辦到這件事情，這個主題建議延伸閱讀這篇精彩的文章 十分鐘內快速上手與使用 Window function｜SQL 教學

• OVER 代表為視窗函數
• PARTITION BY 依據什麼去分割

基礎應用1

資料表最後加上一個欄位，放整個資料表的小計值總和。

SELECT *, SUM(小計) OVER()
FROM order_detail

基礎應用2

資料表最後加上一個欄位，放以訂單編號進行分割的小計值總和。

SELECT *, SUM(小計) OVER(PARTITION BY 訂單編號)
FROM order_detail

基礎應用3

資料表最後加上一個欄位，放以訂單編號進行分割並且依小計值累計小計值總和。

SELECT *, SUM(小計) OVER(PARTITION BY 訂單編號 ORDER BY 小計) AS 累計
FROM order_detail