歡迎來到 [SQL 入門與面試實戰系列] 第二章!!
在第一章大家已經學會了最基本的 SQL 查詢語法,並且透過 WHERE 對數據進行指定條件的篩選。這章將繼續教大家 SQL 最常用的計算公式,以及實戰中超常使用的 GROUP BY。
[SQL 入門與面試實戰系列] 2_GROUP BY 與聚合函數.ipynb
- 聚合函數:各種常用的計算資料公式
- GROUP BY: 對數據進行分組
- 聚合函數:各種常用的計算資料公式
當我們面對大量的資料時,只是排序 (ORDER BY) 或篩選指定條件 (WHERE),還是無法滿足我們分析資料的需求。尤其實際商業案例時,可能需要分析好幾萬筆的資料,這時就需要借用 SQL 的聚合函數了。簡單說聚合函數可以對一欄數據進行計算,然後返回計算出來的結果,最常使用的包括:
- COUNT: 計算欄位中的紀錄數量
- SUM:計算欄位中數值的總和
- AVG:計算欄位中數值的平均值
- MAX:找出欄位中的最大值
- MIN:找出欄位中的最小值
接下來就讓我們使用數據庫 (chinook.db) 當中的 invoices
資料表:
老樣子,在做任何分析之前,一定要先了解使用的資料表包含了哪些欄位、每個欄位代表什麼意思、紀錄的數值是什麼等。先來看一下 invoices
資料表長什麼樣子:
SELECT * FROM invoices
LIMIT 10
invoices
資料表記錄了所有銷售發票信息,我們可以將此資料表的每一列資料視為一筆訂單。它的主要欄位包括:
發票編號 (InvoiceId
):每筆交易訂單的唯一編號 (不同的訂單一定是不同的訂單編號)
客戶編號 (CustomerId
):表示購買該筆訂單的會員編號
購買日期 (InvoiceDate
):記錄發票的創建日期,即發生交易的時間
發票金額 (Total
):該筆交易的總金額
現在我們來看看如何使用前面提到的聚合函數,從 invoices
資料表中提取一些有價值的信息。
- COUNT:計算特定欄位的紀錄數量
我們可以使用 COUNT 來計算invoices
表中總共有多少筆交易。通過計算發票編號 (InvoiceId
欄位) 的數量來實現:
SELECT COUNT(InvoiceId) FROM invoices
可能有朋友想問,既然 COUNT 是計算欄位的項目數量,那麼 COUNT 其他的欄位是否也可以得到一樣的結果?在 invoices
這張練習表確實是,但實務中各欄位的資料可能會有缺失,例如有些訂單是沒有記錄到顧客編號的。不過我們可以確定在訂單資料表中,每筆資料都會有唯一的訂單編號。因此要準確計算訂單數量,最佳做法是 COUNT 訂單編號欄位。
此外,最保險的做法是加上 DISTINCT,確保我們不會重複計算相同的訂單編號:
SELECT COUNT(DISTINCT InvoiceId) FROM invoices
最後,為了讓輸出的欄位名更精簡且易於理解,我們可以為 COUNT(DISTINCT InvoiceId) 起一個更具描述性、讓人一眼就可以理解該欄位的名字。只要在欄位後面使用 AS + “取名”:
SELECT COUNT(DISTINCT InvoiceId) AS TotalTransactions FROM invoices
- SUM:計算欄位中數值的總和
比如我們想知道所有交易的總銷售額,可以通過 SUM 來加總發票金額 (Total
欄位) :
SELECT SUM(Total) AS TotalSales FROM invoices
SUM 能夠對數值型態的欄位進行加總,實務中我們會加總那些具有實際計算意義的數字,例如銷售額、成本、利潤等 (加總像顧客編號或訂單編號這樣的欄位在邏輯上沒有意義)。
- AVG:計算欄位中數值的平均值
如果我們想知道平均每筆交易的金額,就可以使用 AVG 來計算 Total 的平均值:
SELECT AVG(Total) AS AverageSale FROM invoices
與 SUM 一樣,我們基本上只會對那些具有實際計算意義的數字欄位進行 AVG。
- MAX 和 MIN:找出欄位的最大值和最小值
如果我們想找出最大與最小的交易金額,就可以使用 MAX 和 MIN:
SELECT MAX(Total) AS LargestSale, MIN(Total) AS SmallestSale
FROM invoices
- GROUP BY: 對數據進行分組
通過上面學到的聚合函數,我們能夠對資料表的總體概況有一定的了解。但我們經常需要進入到更細的維度,再來計算欄位的數值,GROUP BY 就派上用場了!它讓我們可以根據特定欄位將資料「分組」,然後對每個組別應用上面學到的聚合函數,從而獲得更詳細的資訊。
GROUP BY 的基本語法如下:
SELECT "分組欄位", 聚合函數("計算欄位")
FROM "資料表名"
GROUP BY "分組欄位"
為了讓各位快速感受 GROUP BY 的使用情境,假設我們想知道每位顧客的總消費是多少,我們可以使用 WHERE 一個一個篩選指定的顧客,再透過 SUM 加總發票金額來計算每個顧客的總花費。例如先查看會員編號為 1 的顧客總共消費多少:
SELECT SUM(Total) AS TotalSales
FROM invoices
WHERE CustomerId = 1
但如果我們想要一次性查看每位顧客的總消費,這時候就可以使用 GROUP BY,對顧客編號欄位進行分組,再使用聚合函數 SUM( ) 來加總每位顧客的消費金額:
SELECT CustomerId, SUM(Total) AS CustomerTotalSales
FROM invoices
GROUP BY CustomerId
這個查詢將 invoices
表按照 CustomerId
分組,然後對每個獨特的 CustomerId
,計算該顧客的總銷售額,讓我們能夠一目了然地看到每位顧客的消費情況。
GROUP BY 好用的地方在於與聚合函數的搭配。比如這次我們想要查看每位顧客的訂單平均金額,就可以使用 GROUP BY + AVG( ):
SELECT CustomerId, AVG(Total) AS CustomerTotalSales
FROM invoices
GROUP BY CustomerId
這個查詢則是對每個獨特的 CustomerId
,計算該顧客每筆訂單的平均金額。
對 Excel 熟悉的朋友應該發現,GROUP BY 其實就跟 Excel 的樞紐分析表概念是一樣的!我們選擇某個欄位進行分組,從而讓資料的維度降低,更集中獲得資訊。
再來試另一個例子,假設我們想知道在 invoices
資料表中,每天的訂單總銷售額,我們可以 GROUP BY 訂單日期 (InvoiceDate
欄位):
SELECT InvoiceDate, SUM(Total) AS DayTotalSales
FROM invoices
GROUP BY InvoiceDate
通常,當我們使用 GROUP BY 某個欄位時,會在 SELECT 語句中包含這個欄位。這樣做可以幫助我們清楚地知道查詢結果,是基於哪個分組欄位的聚合計算。
進一步,我們也可以將 GROUP BY 應用於多個欄位,例如,如果我們想了解每位顧客在哪些日期進行了多少消費,可以同時對顧客編號(CustomerId
)和訂單日期(InvoiceDate
)進行分組:
SELECT CustomerId, InvoiceDate, SUM(Total) AS CustomerDaySales
FROM invoices
GROUP BY CustomerId, InvoiceDate
GROUP BY 也常與 ORDER BY 同時使用,來進一步提高查詢結果的可讀性:
SELECT CustomerId, InvoiceDate, SUM(Total) AS CustomerDaySales
FROM invoices
GROUP BY CustomerId, InvoiceDate
ORDER BY CustomerId, InvoiceDate
最後,我們可能希望對 GROUP BY 產生的分組結果,再進一步進行篩選。這時,我們可以使用 HAVING,來對聚合後的數值進行篩選。舉個例子,假設我們想要找出總消費超過 40 元的顧客,我們可以在 GROUP BY 子句後面這樣寫:
SELECT CustomerId, SUM(Total) AS CustomerTotalSales
FROM invoices
GROUP BY CustomerId
HAVING CustomerTotalSales > 40
這個查詢會篩選出那些總消費超過 40 元的顧客。
HAVING 與 WHERE 子句很相似都是設置篩選條件,但大家要記得兩者差別在於 HAVING 是用來篩選「分組後」的結果,而 WHERE 則是在分組之前對「原始數據」進行篩選。
讓我們徹底了解這兩者的差別,假設我們希望使用 invoices
資料表,找出會員編號在 1 到 10 之間,並且總消費超過 40 元的顧客,聰明的你可以來思考一下如何撰寫這段語法:
SELECT CustomerId, Sum(Total) AS CustomerTotalSales
FROM invoices
WHERE CustomerId Between 1 AND 10
GROUP BY CustomerId
HAVING CustomerTotalSales > 40
ORDER BY CustomerId
在這裡,我們首先使用 WHERE 子句,篩選出顧客編號在 1 到 10 之間的原始數據。然後通過 GROUP BY 進行分組,對每位顧客的訂單金額進行總和計算。最後,我們用 HAVING 子句篩選出那些總消費超過 40 元的顧客。
總結一下這個章節所學到的內容:
- 聚合函數: 對欄位資料進行計算和摘要,最常用的包括 COUNT、SUM、AVG、MAX、MIN。
- GROUP BY:選擇指定欄位來將資料「分組」。搭配聚合函數,對每一個分組進行更細致的聚合計算。此外可以使用 HAVING 對分組後的結果進行篩選。
最後補充一下使用 AS 設定別名的一些準則。隨著查詢涉及的欄位與資料表越多,我們經常需要指定一個臨時的名稱,讓我們更容易處理複雜查詢。以下是一些準則:
- 清晰的命名:命名盡量直接反映欄位或資料表的內容或意義。例如當我們計算總銷售額,可以將此別名設定為 “TotalSales”,而不是模糊不清的名稱如 “x1”、”temp1”。
- 避免使用 SQL 關鍵字:比方說不要將欄位或資料表取名為 “SELECT”、”DATE” 等與 SQL 函數相同的名字。
- 別名的撰寫風格:常見的包含駝峰式大小寫 (每個單詞的首字母大寫,其餘字母小寫,單詞之間不使用空格或底線)、小寫加底線 (全部小寫,單詞跟單詞間用底線連結)、全大寫 (單詞間沒有空格或底線、可讀性較差)。
-- 駝峰式大小寫
SELECT SUM(Total) AS TotalSales FROM invoices;
-- 小寫加底線
SELECT SUM(Total) AS total_sales FROM invoices;
-- 全大寫
SELECT SUM(Total) AS TOTALSALES FROM invoices;
GROUP BY 可以說是我們在分析資料中最常用到的功能,所以各位一定要徹底了解它的運作邏輯。恭喜你完成了第二章,準備好就讓我們進入下一章節:JOIN!