歡迎來到 [SQL 入門與面試實戰系列] 第七章！
從這章開始，我們將探討如何在實戰和面試時，高效地拆解並解決 SQL 相關的問題。這章節主要會探討如何識別和運用不同類型的商業資料表，以及計算關鍵業務指標。
[SQL 入門與面試實戰系列] 7_常用商業指標.ipynb
- 商業資料表的類型與解題步驟
- 常用商業指標與計算方式
- 商業資料表的類型與解題步驟
面對 SQL 解題時，最重要的第一步是需要清楚了解問題在問什麼。通常問題可以按照人、事、物、條件進行分類和拆解。假設我們面試的是一間電商，題目是計算 2023 年註冊的顧客，在 2023 年購買 3C 產品的消費金額。我們先將此問題，按照人、事、物、條件進行拆解：
# 人：2023 年註冊顧客
# 事：2023 年購買 3C 產品的訂單
# 物：3C 類別產品
# 條件：2023 年註冊 (人)、2023 年消費 (事)、購買 3C 產品 (物)
理解問題的組成後，下一步就是找到人、事、物相對應的資料表，來獲取相關資訊。而實務中資料表也可以分成人、事、物三個類別：
1.人：顧客資料表
顧客資料表 (例如 chinook.db 的 `customers` 表) 常用的欄位包括：顧客 ID、年齡、性別、註冊時間、最近登入時間。面試時公司提供的顧客資料表，是絕對不會有個人信息如姓名、聯繫方式的 (在多數大公司中，這些個人信息資訊也是受到加密保護的)。
顧客資料表的欄位，通常會做為 GROUP BY 欄位或 WHERE 條件設置，例如將不同年齡層的顧客分組計算人數、找出在 2023 年註冊的顧客等。
2.事：訂單資料表、訂單明細資料表
紀錄顧客的消費紀錄。多數公司會有訂單表跟訂單明細表兩種表，前者紀錄的是訂單的總體信息，每一行資料代表一筆訂單 (例如 `invoices` 表)；而後者記錄的是訂單中的每項商品及其購買數量金額，每一行資料代表一項售出商品 (例如 `invoice_items` 表)。解題時一定要分清楚兩者的差別。
訂單表主要欄位包括：訂單 ID、訂單時間、訂單總金額、訂單狀態 (完成或取消退貨等)、購買顧客 ID。
訂單明細表的主要欄位則包括：訂單 ID、購買商品 ID、購買商品數量、購買商品金額。
訂單相關的資料基本上會是解題的主體，畢竟商業解題主要就是在分析顧客的消費行為以及銷售表現 (很多面試甚至只會提供訂單明細表作為考題)。欄位除了會作為分組與條件設置，也經常用來計算相關指標，包括計算訂單數、顧客數、銷售數、GMV 等。
此外，如果問題涉及購買哪些產品，我們就要很直覺的將訂單表與訂單明細表進行連接 (以訂單 ID 作為連接欄位)。例如找出購買特定產品的訂單。
補充：根據產業或情境，紀錄的事可能略有不同。例如銀行可能是紀錄轉帳行為、社群網站紀錄的會是用戶瀏覽行為等。但基本概念是一樣的，欄位會包含事件 ID、發生的時間等。
3.物：商品資料表
商品資料表 (例如 `items` 表) 記錄每一項商品資訊。常見欄位包含：商品 ID、商品類別、商品成本、商品售價。
其中商品類別欄位，會有相對應的資料表 (例如 `albums` 表)。比如商品的品類欄位，就會有對應的商品品類資料表，紀錄商品品類有哪些。如果問題是找出某個產品類別的銷售資料，就需要透過商品表的品類欄位與品類對照表串接進行篩選 (以品類 ID 作為連接欄位)。
這三種類型的表，我們需要非常直覺地知道如何進行連接。例如訂單資料表與顧客資料表，可以透過顧客 ID 欄位進行串接；而訂單資料明細表與商品資料表可以透過商品 ID 欄位串接。回到一開始的問題，我們知道需要串接以下這些表來獲取資訊：
# 人：2023 年註冊顧客 -> 顧客資料表
# 事：2023 年購買 3C 產品的訂單 -> 訂單資料表、訂單明細資料表
# 物：3C 類別產品 -> 商品資料表
在串接好需要的資料表後，下一步就是針對條件部分進行設置了。我們需要篩選的條件包括：23 年註冊 (人)、2023 年消費 (事)、購買 3C 產品 (物)。因此我們在 WHERE 子句中，分別針對不同條件，選擇相應的欄位進行設置。
# 人：顧客資料表 -> 註冊時間欄位 = 2023 年
# 事：訂單資料表 -> 訂單時間欄位 = 2023 年
# 物：商品資料表 -> 產品類別欄位 = 3C 類別產品
透過以上步驟，就能夠篩選出我們所需要的資料！最後我們再根據題目所需，進行相對應的計算或呈現方式。例如題目需要計算每一位顧客的總銷售額，我們透過 GROUP BY 顧客 ID 欄位，再搭配 SUM 計算得出。如此我們便能完成解題！
隨著解題經驗越來越多，這些步驟都會變成直覺反應，很快知道需要連接哪些表、進行篩選等。大家在解題時，可以試著將 SQL 語法的架構寫在白紙上，甚至直接在腦中構建。讓我們再跑一次解題的步驟：
- 了解問題，並且掌握問題的要件：先閱讀題目，理解背後的業務邏輯與要回答的問題。將問題拆解為人、事、物、條件，定義解題範圍。
- 找出相對應的資料表並進行串接：根據問題的人、事、物，找出對應的資料表。並且將表進行連接 (也需要確定連接的方式是 LEFT JOIN 或 INNER JOIN)。
- 篩選出符合題目條件的資料：利用 WHERE 子句篩選符合題目條件的資料。條件例如時間範圍、特定類別的產品、消費者的特定行為等。根據需要我們可能會用到 GROUP BY、HAVING 進一步篩選資料集。
- 根據題目需求調整呈現方式：決定最終數據的呈現形式，例如運用聚合函數 (如 SUM、AVG 等)、CASE WHEN、或是 ORDER BY 排序。
- 常用商業指標與計算方式
接下來，我們來探討幾個常見的商業指標。雖然不同產業有各自關注的特定指標，但普遍性的核心指標大家一定要非常熟悉，知道這些指標的計算方式，以及要用到哪些資料表。以下我們一樣透過老朋友 chinook.db 的資料表練習。
1.銷售額/GMV
實戰中最常計算的就是銷售額了 (公司最重要的就是錢！)。銷售額在電商會稱作 GMV (Gross Merchandise Value)，其實就是總訂單金額。計算銷售額使用訂單資料表，透過 SUM 加總訂單金額欄位：
SELECT SUM(Total) AS GMV FROM invoices
通常計算 GMV 會搭配不同的維度去進行分析或比較。例如按顧客維度，計算每位顧客的消費金額，以制定顧客分群：
SELECT
CustomerId,
SUM(Total) AS CustomerTotal
FROM invoices
GROUP BY CustomerId
按產品維度，分析各個產品或產品類別的銷售表現：
SELECT
TrackId,
SUM(Total) AS ItemTotal
FROM invoices
INNER JOIN invoice_items ON invoices.InvoiceId = invoice_items.InvoiceId
GROUP BY TrackId
按訂單的時間維度，計算年/月度的總 GMV，觀察銷售趨勢：
SELECT
strftime('%Y-%m', InvoiceDate) AS ExtractedYM,
SUM(Total) AS GMV
FROM invoices
GROUP BY ExtractedYM
上述的維度也會相互組合，獲得更深入的洞見。比如分析不同月份各品項的銷售額：
SELECT
strftime('%Y-%m', InvoiceDate) AS ExtractedYM,
invoice_items.TrackId,
SUM(invoices.Total) AS GMV
FROM invoices
INNER JOIN invoice_items ON invoices.InvoiceId = invoice_items.InvoiceId
GROUP BY ExtractedYM, invoice_items.TrackId
2.訂單數
總共有幾筆訂單售出。計算訂單數一樣使用訂單資料表，透過 COUNT 加上 DISTINCT 計算不重複的訂單編號：
SELECT COUNT(DISTINCT InvoiceId) AS OrderCnt FROM invoices
訂單數經常按時間維度分析，計算年/月度的訂單數了解銷售趨勢：
SELECT
strftime('%Y-%m', InvoiceDate) AS ExtractedYM,
COUNT(DISTINCT InvoiceId) AS OrderCnt
FROM invoices
GROUP BY ExtractedYM
按顧客維度，計算每位顧客的購買訂單數，了解顧客的購買頻率：
SELECT
CustomerId,
COUNT(DISTINCT InvoiceId) AS OrderCnt
FROM invoices
GROUP BY CustomerId
實務上比較不會按產品維度看訂單數。因為訂單數反映的是交易發生的次數，而一個訂單中可能包含多種產品，GROUP BY 產品欄位計算訂單數會有重疊的狀況。當我們關注於產品維度時，銷售量會是更有意義的指標。
3.銷售量
銷售量指的是產品的售出數量。計算銷售量會使用訂單明細資料表 (記錄了每筆訂單中各個產品的購買數量)，透過 SUM 加總商品銷售數量欄位：
SELECT SUM(Quantity) AS SalesQnt FROM invoice_items
銷售量基本上會搭配產品維度 (直接看全公司的銷售量沒有意義，除非公司只有賣一項或一種商品)。我們會觀察各個產品或品類的銷售量，規劃庫存管理與銷售策略：
SELECT
tracks.TrackId,
tracks.Name AS TrackName,
SUM(Quantity) AS SalesQnt
FROM invoice_items
INNER JOIN tracks ON invoice_items.TrackId = tracks.TrackId
GROUP BY tracks.TrackId, TrackName
我們也會結合時間維度和產品維度進行銷售量分析，觀察不同時間段產品/品類的銷售量，了解顧客對產品需求的變化趨勢：
SELECT
strftime('%Y-%m', InvoiceDate) AS ExtractedYM,
tracks.TrackId,
tracks.Name AS TrackName,
SUM(Quantity) AS SalesQnt
FROM invoices
INNER JOIN invoice_items ON invoices.InvoiceId = invoice_items.InvoiceId
INNER JOIN tracks ON invoice_items.TrackId = tracks.TrackId
GROUP BY ExtractedYM, tracks.TrackId, TrackName
ORDER BY ExtractedYM, tracks.TrackId, TrackName
4.顧客數
通常指的是有購買行為的顧客數。我們使用訂單資料表的顧客 ID 欄位，透過 COUNT 加上 DISTINCT 來計算不重複顧客數：
SELECT COUNT(DISTINCT CustomerId) AS CustomerCnt FROM invoices
我們會按時間維度觀察顧客數，例如觀察年/月度的顧客數，了解顧客是否成長與留存狀況：
SELECT
strftime('%Y-%m', InvoiceDate) AS ExtractedYM,
COUNT(DISTINCT CustomerId) AS CustomerCnt
FROM invoices
GROUP BY ExtractedYM
也會按產品維度觀察購買人數，了解哪些產品/品類能夠吸引更多顧客：
SELECT
TrackId,
COUNT(DISTINCT CustomerId) AS CustomerCnt
FROM invoices
INNER JOIN invoice_items ON invoices.InvoiceId = invoice_items.InvoiceId
GROUP BY TrackId
5. 平均訂單金額/AOV
平均訂單金額是電商經常看的指標，又稱作 AOV (Average Order Value)。計算方式是將總銷售額除以總訂單數：
SELECT
SUM(Total) AS GMV,
COUNT(DISTINCT InvoiceId) AS OrderCnt,
SUM(Total) / COUNT(DISTINCT InvoiceId) AS AOV
FROM invoices
平均訂單金額經常作為衡量行銷活動成效的指標。例如 AOV 為 900 元，企業可以實施加價購或滿千元折扣等策略，來刺激提升 AOV 和整體銷售額。
有時我們會按顧客維度分析 AOV，了解每個顧客的購買習慣與能力，以進行顧客分群，制定差異化的行銷策略：
SELECT
CustomerId,
SUM(Total) / COUNT(DISTINCT InvoiceId) AS AOV
FROM invoices
GROUP BY CustomerId
以上這幾個指標是面試與實戰中最常見的指標，大家一定要非常熟悉如何計算！
本章節的重點回顧：
- 資料表類型與解題步驟：先拆解問題的人、事、物、條件元素，找出對應的資料表進行連接與篩選，最後根據需求調整數據呈現的方式。
- 常用商業指標：GMV、訂單數、銷售量、顧客數、AOV。
相信前六章有好好實作的朋友，這些概念都不會覺得很陌生。實際上，大多數商業問題的本質，都是計算這些商業指標，只是條件疊加以及數據呈現方式的複雜程度不同而已。因此大家一定要多練習，精通這些指標的計算方式。
接下來，讓我們踏入實際面試題目的解析，看看如何將所學應用於真實世界的挑戰中！
