SQL 資料分析的技術演進與商業價值
在當代企業的數位轉型浪潮中,資料已經成為最寶貴的戰略資產之一。每天都有數以億計的交易記錄、使用者行為資料和業務指標被儲存在各種資料庫系統中。然而,這些原始資料本身並不能直接產生商業價值,只有透過有效的分析和洞察提取,才能轉化為支援決策的關鍵資訊。SQL(Structured Query Language)作為資料庫查詢的標準語言,其核心價值不僅在於資料的儲存和檢索,更在於透過強大的聚合函式和分析能力,將海量資料轉換為有意義的商業洞察。
聚合函式是 SQL 資料分析能力的基石。這類函式能夠將多筆記錄彙總為單一的統計值,從而快速計算出關鍵業務指標。從最基本的計數(COUNT)到複雜的統計計算,聚合函式讓資料分析師和開發者能夠在秒級時間內從百萬筆記錄中提取出營收總額、平均訂單金額、客戶流失率等關鍵指標。這種「多對一」的資料轉換能力,是傳統程式語言需要撰寫複雜迴圈和累加邏輯才能實現的功能,在 SQL 中卻只需要簡潔的聲明式語法就能完成。
分組統計功能則進一步提升了 SQL 的分析深度。透過 GROUP BY 子句,我們可以將資料按照不同的維度進行劃分,針對每個群組分別計算統計指標。這種多維度分析能力讓我們能夠從不同角度觀察資料,例如比較各業務人員的銷售績效、分析各產品類別的毛利率、或是追蹤各地區的成長趨勢。HAVING 子句則提供了對聚合結果進行篩選的機制,讓我們能夠找出表現突出或需要關注的特定群組,這在績效管理和異常偵測中特別重要。
字串處理函式則是 SQL 工具箱中另一個不可或缺的組成部分。在實務的資料庫應用中,文字資料往往佔據了相當大的比例,從客戶姓名、產品描述到地址資訊,都需要適當的字串處理才能保證資料品質和查詢效率。SQL 提供的字串函式不僅能夠進行基本的格式化和轉換,更能夠支援複雜的文字解析和標準化作業,這在資料清理、整合和遷移專案中發揮著關鍵作用。
然而,強大的功能也伴隨著複雜性和效能考量。不當使用聚合函式可能導致查詢效能急劇下降,錯誤的分組邏輯會產生誤導性的分析結果,過度使用字串函式則可能讓索引失效。因此,深入理解這些函式的運作原理、執行順序和效能特性,對於撰寫高效且正確的 SQL 查詢至關重要。特別是在處理大規模資料集時,查詢效能的差異可能是秒級與小時級的區別,這直接影響到業務報表的產出效率和決策的時效性。
本文將系統性地剖析 SQL 聚合函式、分組統計和字串處理的完整技術體系。我們會從基礎的 COUNT、SUM、AVG、MAX、MIN 函式開始,深入探討它們的使用場景和注意事項。接著討論 GROUP BY 的分組機制和 HAVING 的篩選邏輯,理解它們與 WHERE 子句的本質差異。在字串處理部分,我們會涵蓋從基本的大小寫轉換到複雜的文字解析技巧。更重要的是,我們會探討這些功能在企業級應用中的實務模式,包括效能優化策略、常見陷阱的避免,以及如何結合子查詢實現複雜的分析需求。
聚合函式的核心原理與應用場景
聚合函式代表了 SQL 中一種特殊的運算模式,它們接受一組輸入值並產生單一的輸出結果。這種「多對一」的轉換特性,使得聚合函式成為資料統計和分析的基礎工具。理解聚合函式的運作機制,對於正確使用它們並避免常見錯誤至關重要。
在 SQL 的執行模型中,聚合函式會遍歷整個結果集或分組內的所有記錄,進行累積計算後返回最終值。這個過程是透過資料庫引擎的內部演算法最佳化的,通常比在應用層進行相同計算要高效得多。然而,這也意味著聚合函式的執行成本與資料量成正比,在處理大型資料集時需要特別注意效能影響。
聚合函式在商業分析中的應用場景極為廣泛。在財務報表中,SUM 函式用於計算營收總額、成本總計和利潤彙總。在績效管理中,AVG 函式用於計算平均訂單金額、平均客戶貢獻度和平均回應時間。在庫存管理中,COUNT 函式用於統計商品種類、供應商數量和缺貨品項。在品質控制中,MAX 和 MIN 函式用於找出異常值、極端案例和臨界情況。這些應用展示了聚合函式如何將原始資料轉化為可執行的商業洞察。
@startuml
!define PLANTUML_FORMAT svg
!theme _none_
skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100
title SQL 聚合函式處理流程與架構
database "原始資料表\nSales Table" as db {
file "Record 1\nAmount: 1000"
file "Record 2\nAmount: 2000"
file "Record 3\nAmount: 1500"
file "Record 4\nAmount: 3000"
file "Record 5\nAmount: 2500"
}
package "聚合函式處理引擎" {
component "COUNT\n計數處理器" as count
component "SUM\n求和處理器" as sum
component "AVG\n平均值處理器" as avg
component "MAX/MIN\n極值處理器" as extrema
}
package "處理流程" {
rectangle "掃描所有記錄" as scan
rectangle "累積計算" as accumulate
rectangle "產生結果" as result
}
package "輸出結果" {
card "COUNT: 5\n總記錄數" as count_result
card "SUM: 10000\n銷售總額" as sum_result
card "AVG: 2000\n平均金額" as avg_result
card "MAX: 3000\nMIN: 1000" as extrema_result
}
db --> scan
scan --> accumulate
accumulate --> count
accumulate --> sum
accumulate --> avg
accumulate --> extrema
count --> result
sum --> result
avg --> result
extrema --> result
result --> count_result
result --> sum_result
result --> avg_result
result --> extrema_result
note right of count
COUNT 處理邏輯
計算記錄數量
COUNT(*) 包含 NULL
COUNT(column) 排除 NULL
時間複雜度: O(n)
空間複雜度: O(1)
end note
note right of sum
SUM 處理邏輯
累加所有數值
自動跳過 NULL 值
可能產生溢位
適用於財務統計
營收成本計算
end note
note right of avg
AVG 處理邏輯
計算算術平均值
等於 SUM / COUNT
排除 NULL 值
注意精度問題
可能需要 ROUND
end note
note right of extrema
MAX/MIN 處理邏輯
找出最大最小值
支援數值日期字串
忽略 NULL 值
可用於範圍分析
異常值偵測
end note
note bottom
聚合函式關鍵特性
多對一轉換
一組輸入值產生單一輸出
這是聚合函式的本質特徵
NULL 值處理
大多數聚合函式會跳過 NULL
COUNT(*) 是例外會計入 NULL
效能考量
掃描成本與記錄數成正比
適當索引可以加速某些聚合
避免對計算欄位進行聚合
組合使用
可以在同一查詢中使用多個聚合函式
結合 CASE 實現條件聚合
搭配 GROUP BY 進行分組統計
end note
@enduml
COUNT 函式的深度應用與陷阱
COUNT 函式是最基礎也最容易被誤用的聚合函式。表面上看,它只是簡單地計算記錄數,但在實務應用中,COUNT 的兩種形式 COUNT(*) 和 COUNT(column) 有著重要的差異,理解這些差異對於正確分析資料至關重要。
COUNT() 會計算所有的記錄數,無論欄位是否包含 NULL 值。這種形式主要用於了解資料表的規模、特定條件下的記錄總數,或是驗證資料載入的完整性。相對地,COUNT(column) 只會計算指定欄位中非 NULL 的記錄數。這個特性讓 COUNT 成為檢查資料完整性的有力工具。透過比較 COUNT() 和 COUNT(specific_column) 的差異,我們可以快速識別出該欄位的 NULL 值比例,這在資料品質評估中非常重要。
COUNT(DISTINCT column) 則是另一個重要的變體,它計算欄位中不重複值的數量。這個功能在分析唯一性時特別有用,例如計算不同客戶的數量、獨立產品的種類,或是唯一交易日期的天數。在客戶分析中,我們可能需要知道實際購買的客戶數而非交易筆數,這時候 COUNT(DISTINCT customer_id) 就能提供準確的答案。
-- COUNT 函式的完整應用範例
-- 展示 COUNT 在資料分析中的各種使用模式
-- 範例情境:分析一個電子商務平台的訂單資料
-- 假設有 orders 表格包含以下欄位:
-- order_id, customer_id, product_id, order_date, ship_date,
-- amount, status, payment_method, coupon_code
-- 1. 基本計數:了解資料規模
-- COUNT(*) 計算所有記錄,包含任何欄位為 NULL 的記錄
SELECT
COUNT(*) AS total_orders, -- 訂單總數
COUNT(ship_date) AS shipped_orders, -- 已出貨訂單數
COUNT(*) - COUNT(ship_date) AS pending_orders -- 待出貨訂單數
FROM
orders;
-- 註解:透過比較 COUNT(*) 和 COUNT(ship_date) 可以得知有多少訂單尚未出貨
-- 這是利用 COUNT(column) 會排除 NULL 的特性
-- 2. 計算資料完整率
-- 評估各欄位的填寫情況,用於資料品質監控
SELECT
COUNT(*) AS total_records,
-- 計算各欄位的填寫率
CONCAT(
ROUND(COUNT(payment_method) * 100.0 / COUNT(*), 2),
'%'
) AS payment_method_fill_rate,
CONCAT(
ROUND(COUNT(coupon_code) * 100.0 / COUNT(*), 2),
'%'
) AS coupon_usage_rate,
CONCAT(
ROUND(COUNT(ship_date) * 100.0 / COUNT(*), 2),
'%'
) AS shipment_completion_rate
FROM
orders;
-- 註解:填寫率計算公式 = (非 NULL 數量 / 總記錄數) × 100
-- 這種分析可以幫助識別資料收集過程中的問題
-- 3. 不重複值計數:了解資料多樣性
-- COUNT(DISTINCT) 計算唯一值的數量
SELECT
COUNT(DISTINCT customer_id) AS unique_customers, -- 不同客戶數
COUNT(DISTINCT product_id) AS unique_products, -- 不同產品數
COUNT(DISTINCT DATE(order_date)) AS active_days, -- 有訂單的天數
COUNT(*) AS total_transactions, -- 總交易筆數
-- 計算平均每個客戶的訂單數
ROUND(
COUNT(*) * 1.0 / COUNT(DISTINCT customer_id),
2
) AS avg_orders_per_customer
FROM
orders
WHERE
order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR);
-- 註解:透過比較總交易數和不重複客戶數,可以了解客戶的重複購買率
-- 4. 條件計數:使用 CASE 進行分類統計
-- 在單一查詢中計算多個條件下的記錄數
SELECT
COUNT(*) AS total_orders,
-- 使用 CASE WHEN 進行條件計數
-- 只有當條件為真時才計入
COUNT(CASE
WHEN amount >= 10000 THEN 1
END) AS high_value_orders,
COUNT(CASE
WHEN amount >= 5000 AND amount < 10000 THEN 1
END) AS medium_value_orders,
COUNT(CASE
WHEN amount < 5000 THEN 1
END) AS low_value_orders,
-- 計算各類別的佔比
ROUND(
COUNT(CASE WHEN amount >= 10000 THEN 1 END) * 100.0 / COUNT(*),
2
) AS high_value_percentage
FROM
orders;
-- 註解:CASE WHEN 結合 COUNT 是實現條件計數的標準模式
-- 當條件不成立時,CASE 返回 NULL,COUNT 會自動忽略
-- 5. 時間維度的計數分析
-- 分析訂單在時間軸上的分佈
SELECT
-- 今日訂單數
COUNT(CASE
WHEN DATE(order_date) = CURRENT_DATE THEN 1
END) AS today_orders,
-- 本週訂單數
COUNT(CASE
WHEN YEARWEEK(order_date, 1) = YEARWEEK(CURRENT_DATE, 1) THEN 1
END) AS this_week_orders,
-- 本月訂單數
COUNT(CASE
WHEN YEAR(order_date) = YEAR(CURRENT_DATE)
AND MONTH(order_date) = MONTH(CURRENT_DATE) THEN 1
END) AS this_month_orders,
-- 本年訂單數
COUNT(CASE
WHEN YEAR(order_date) = YEAR(CURRENT_DATE) THEN 1
END) AS this_year_orders,
-- 計算各時期訂單佔比
ROUND(
COUNT(CASE WHEN DATE(order_date) = CURRENT_DATE THEN 1 END) * 100.0 /
COUNT(CASE WHEN YEAR(order_date) = YEAR(CURRENT_DATE) THEN 1 END),
2
) AS today_percentage_of_year
FROM
orders;
-- 註解:透過條件計數可以在單一查詢中獲得多時期的統計資料
-- 6. 狀態分佈分析
-- 了解訂單在各種狀態下的分佈
SELECT
COUNT(*) AS total_orders,
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
COUNT(CASE WHEN status = 'processing' THEN 1 END) AS processing_count,
COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_count,
COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered_count,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count,
-- 計算成功完成率
ROUND(
COUNT(CASE WHEN status = 'delivered' THEN 1 END) * 100.0 /
COUNT(*),
2
) AS delivery_success_rate,
-- 計算取消率
ROUND(
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) * 100.0 /
COUNT(*),
2
) AS cancellation_rate
FROM
orders
WHERE
order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH);
-- 註解:這種統計可以幫助監控訂單處理流程的健康狀況
-- 7. 多維度組合計數
-- 結合多個條件進行細緻的分類統計
SELECT
-- 新客戶的訂單數(假設 customer_type 欄位區分新舊客戶)
COUNT(CASE
WHEN customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) = 1
) THEN 1
END) AS new_customer_orders,
-- 回購客戶的訂單數
COUNT(CASE
WHEN customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
) THEN 1
END) AS returning_customer_orders,
-- 使用優惠券的訂單數
COUNT(CASE
WHEN coupon_code IS NOT NULL THEN 1
END) AS orders_with_coupon,
-- 使用信用卡支付的訂單數
COUNT(CASE
WHEN payment_method = 'credit_card' THEN 1
END) AS credit_card_orders
FROM
orders
WHERE
order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH);
-- 註解:複雜的子查詢結合 CASE WHEN 可以實現精細的客戶分類
-- 8. 效能最佳化的計數查詢
-- 當只需要知道是否存在記錄時,使用 EXISTS 比 COUNT 更高效
SELECT
CASE
WHEN EXISTS (
SELECT 1
FROM orders
WHERE customer_id = 12345
AND order_date >= CURRENT_DATE
) THEN '今日有訂單'
ELSE '今日無訂單'
END AS order_status;
-- 註解:EXISTS 在找到第一筆符合的記錄後就會停止搜尋
-- 比 COUNT(*) > 0 更有效率,特別是在大型資料集上
-- 9. 避免 COUNT 的常見陷阱
-- 示範 COUNT 的錯誤使用和正確方式
SELECT
-- 錯誤:這會計算所有記錄,而非有效訂單
-- COUNT(*) AS valid_orders
-- 正確:使用 WHERE 子句或條件計數
COUNT(CASE
WHEN status IN ('shipped', 'delivered') THEN 1
END) AS valid_orders,
-- 錯誤:嘗試對聚合結果進行 WHERE 篩選
-- WHERE COUNT(*) > 10 -- 這會導致語法錯誤
-- 正確:使用 HAVING 子句(需要配合 GROUP BY)
customer_id,
COUNT(*) AS order_count
FROM
orders
GROUP BY
customer_id
HAVING
COUNT(*) > 10;
-- 註解:理解 WHERE 和 HAVING 的差異對於正確使用聚合函式至關重要
COUNT 函式雖然看似簡單,但在實務應用中需要注意許多細節。最常見的錯誤是混淆 COUNT(*) 和 COUNT(column) 的差異,導致對 NULL 值的處理不當。另一個常見問題是在需要使用 HAVING 的地方錯用 WHERE 子句,這會導致語法錯誤。理解這些細節並在適當的場景選擇正確的 COUNT 形式,是撰寫準確查詢的基礎。
SUM AVG 與數值統計的藝術
SUM 和 AVG 函式是財務分析和業績統計中最核心的工具。SUM 計算數值欄位的總和,常用於彙總營收、成本、數量等指標。AVG 計算算術平均值,用於評估平均表現、設定基準值和識別異常情況。這兩個函式的運作都建立在忽略 NULL 值的基礎上,這個特性在某些情況下很有用,但也可能導致意外的結果。
在使用 SUM 函式時,需要特別注意資料型別和數值範圍。當累加大量數值時,可能會遇到溢位(Overflow)問題,特別是使用整數型別儲存金額時。因此,在處理財務資料時,建議使用 DECIMAL 或 NUMERIC 型別以保證精確度。另一個重要考量是幣別和單位的一致性,在彙總不同幣別或單位的資料前,必須先進行適當的轉換。
AVG 函式的計算方式是 SUM(column) / COUNT(column),注意這裡的 COUNT 是排除 NULL 的 COUNT(column) 而非 COUNT(*)。這意味著如果某些記錄的欄位值為 NULL,它們既不會被加總也不會被計入除數。如果希望將 NULL 視為 0 來計算平均值,需要使用 COALESCE 或 IFNULL 函式進行轉換。此外,AVG 返回的通常是浮點數,可能需要使用 ROUND 函式來調整精度,以符合業務報表的需求。
-- SUM 與 AVG 函式的企業級應用範例
-- 展示數值統計在商業分析中的實務模式
-- 範例情境:分析企業的銷售與財務資料
-- 假設有 transactions 表格包含以下欄位:
-- transaction_id, customer_id, product_id, salesperson_id,
-- transaction_date, quantity, unit_price, discount_rate,
-- tax_rate, shipping_fee, total_amount, profit_margin, region
-- 1. 基本的營收與獲利統計
-- 計算整體的財務表現指標
SELECT
-- 計算交易筆數作為基準
COUNT(*) AS transaction_count,
-- 計算營收總額
-- SUM 會自動跳過 NULL 值
SUM(total_amount) AS total_revenue,
-- 計算平均訂單金額
-- 使用 ROUND 保留兩位小數
ROUND(AVG(total_amount), 2) AS average_order_value,
-- 計算總利潤
-- 利潤 = 營收 × 利潤率
SUM(total_amount * profit_margin) AS total_profit,
-- 計算平均利潤率
-- 注意:這是加權平均,考慮了交易金額的影響
ROUND(
SUM(total_amount * profit_margin) / SUM(total_amount) * 100,
2
) AS weighted_avg_profit_margin,
-- 計算總銷售數量
SUM(quantity) AS total_quantity_sold,
-- 計算平均交易數量
ROUND(AVG(quantity), 2) AS avg_quantity_per_order
FROM
transactions
WHERE
-- 只分析當年度的資料
YEAR(transaction_date) = YEAR(CURRENT_DATE);
-- 註解:在財務分析中,加權平均比簡單平均更能反映真實情況
-- 2. 時間序列的營收趨勢分析
-- 比較不同時期的表現並計算成長率
SELECT
-- 今年營收
SUM(CASE
WHEN YEAR(transaction_date) = YEAR(CURRENT_DATE)
THEN total_amount
ELSE 0
END) AS current_year_revenue,
-- 去年同期營收
SUM(CASE
WHEN YEAR(transaction_date) = YEAR(CURRENT_DATE) - 1
THEN total_amount
ELSE 0
END) AS last_year_revenue,
-- 計算年度成長率 (YoY Growth)
-- 使用 NULLIF 避免除以零的錯誤
ROUND(
(SUM(CASE
WHEN YEAR(transaction_date) = YEAR(CURRENT_DATE)
THEN total_amount
ELSE 0
END) -
SUM(CASE
WHEN YEAR(transaction_date) = YEAR(CURRENT_DATE) - 1
THEN total_amount
ELSE 0
END)) /
NULLIF(SUM(CASE
WHEN YEAR(transaction_date) = YEAR(CURRENT_DATE) - 1
THEN total_amount
ELSE 0
END), 0) * 100,
2
) AS yoy_growth_percentage,
-- 本季營收
SUM(CASE
WHEN YEAR(transaction_date) = YEAR(CURRENT_DATE)
AND QUARTER(transaction_date) = QUARTER(CURRENT_DATE)
THEN total_amount
ELSE 0
END) AS current_quarter_revenue,
-- 上季營收
SUM(CASE
WHEN transaction_date >= DATE_SUB(
DATE_FORMAT(CURRENT_DATE, '%Y-%m-01'),
INTERVAL 3 MONTH
)
AND transaction_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
THEN total_amount
ELSE 0
END) AS last_quarter_revenue
FROM
transactions;
-- 註解:NULLIF 函式在分母可能為零時特別重要,避免運算錯誤
-- 3. 多維度的統計分析
-- 結合多個聚合函式進行綜合評估
SELECT
COUNT(*) AS total_transactions,
-- 營收統計
SUM(total_amount) AS total_revenue,
ROUND(AVG(total_amount), 2) AS avg_transaction_value,
MAX(total_amount) AS largest_transaction,
MIN(total_amount) AS smallest_transaction,
-- 計算營收的標準差
-- 標準差衡量資料的離散程度
ROUND(STDDEV(total_amount), 2) AS revenue_std_deviation,
-- 計算變異係數 (CV)
-- CV = 標準差 / 平均值,用於比較不同量級資料的變異程度
ROUND(
STDDEV(total_amount) / AVG(total_amount) * 100,
2
) AS coefficient_of_variation,
-- 折扣統計
ROUND(AVG(discount_rate * 100), 2) AS avg_discount_percentage,
SUM(total_amount * discount_rate) AS total_discount_amount,
-- 運費統計
SUM(shipping_fee) AS total_shipping_revenue,
ROUND(AVG(shipping_fee), 2) AS avg_shipping_fee,
-- 計算運費佔營收的比例
ROUND(
SUM(shipping_fee) / SUM(total_amount) * 100,
2
) AS shipping_to_revenue_ratio
FROM
transactions
WHERE
transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR);
-- 註解:標準差和變異係數是評估資料穩定性的重要指標
-- 4. 處理 NULL 值的不同策略
-- 展示 NULL 值如何影響聚合計算
SELECT
-- 不處理 NULL:SUM 和 AVG 會自動跳過
SUM(profit_margin) AS sum_profit_ignore_null,
ROUND(AVG(profit_margin), 4) AS avg_profit_ignore_null,
-- 將 NULL 視為 0 處理
-- 使用 COALESCE 將 NULL 轉換為 0
SUM(COALESCE(profit_margin, 0)) AS sum_profit_null_as_zero,
ROUND(
AVG(COALESCE(profit_margin, 0)),
4
) AS avg_profit_null_as_zero,
-- 計算非 NULL 值的數量
COUNT(profit_margin) AS non_null_count,
-- 計算 NULL 值的數量
COUNT(*) - COUNT(profit_margin) AS null_count,
-- 計算 NULL 值的比例
ROUND(
(COUNT(*) - COUNT(profit_margin)) * 100.0 / COUNT(*),
2
) AS null_percentage
FROM
transactions;
-- 註解:選擇正確的 NULL 處理策略對於獲得準確的統計結果至關重要
-- 5. 客戶價值分析(Customer Lifetime Value)
-- 計算客戶的總消費和平均消費
SELECT
customer_id,
-- 該客戶的總交易次數
COUNT(*) AS total_purchases,
-- 該客戶的總消費金額(客戶終身價值的簡化版本)
SUM(total_amount) AS lifetime_value,
-- 平均每次購買金額
ROUND(AVG(total_amount), 2) AS avg_purchase_value,
-- 總利潤貢獻
SUM(total_amount * profit_margin) AS total_profit_contribution,
-- 首次購買日期
MIN(transaction_date) AS first_purchase_date,
-- 最近購買日期
MAX(transaction_date) AS last_purchase_date,
-- 客戶活躍天數
DATEDIFF(MAX(transaction_date), MIN(transaction_date)) AS customer_lifespan_days,
-- 計算平均購買間隔
-- 總天數 / (購買次數 - 1)
ROUND(
DATEDIFF(MAX(transaction_date), MIN(transaction_date)) /
NULLIF(COUNT(*) - 1, 0),
1
) AS avg_days_between_purchases
FROM
transactions
GROUP BY
customer_id
-- 只顯示有多次購買記錄的客戶
HAVING
COUNT(*) > 1
ORDER BY
lifetime_value DESC
LIMIT 100;
-- 註解:客戶終身價值分析是精準行銷和客戶關係管理的基礎
-- 6. 產品績效分析
-- 評估產品的銷售表現和獲利能力
SELECT
product_id,
-- 銷售數量統計
SUM(quantity) AS total_quantity_sold,
COUNT(DISTINCT customer_id) AS unique_customers,
COUNT(DISTINCT transaction_date) AS sales_days,
-- 營收統計
SUM(total_amount) AS total_revenue,
ROUND(AVG(total_amount), 2) AS avg_transaction_value,
-- 價格分析
ROUND(AVG(unit_price), 2) AS avg_selling_price,
MAX(unit_price) AS highest_price,
MIN(unit_price) AS lowest_price,
-- 獲利分析
SUM(total_amount * profit_margin) AS total_profit,
ROUND(
AVG(profit_margin * 100),
2
) AS avg_profit_margin_percentage,
-- 計算該產品佔總營收的比例
ROUND(
SUM(total_amount) / (
SELECT SUM(total_amount) FROM transactions
) * 100,
2
) AS revenue_contribution_percentage
FROM
transactions
WHERE
transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
GROUP BY
product_id
-- 只顯示營收超過門檻的產品
HAVING
SUM(total_amount) > 10000
ORDER BY
total_revenue DESC;
-- 註解:產品績效分析幫助識別明星產品和需要改進的品項
-- 7. 地區表現比較
-- 分析不同地區的營收和效率指標
SELECT
region,
-- 基本統計
COUNT(*) AS transaction_count,
COUNT(DISTINCT customer_id) AS unique_customers,
COUNT(DISTINCT salesperson_id) AS active_salespeople,
-- 營收指標
SUM(total_amount) AS regional_revenue,
ROUND(AVG(total_amount), 2) AS avg_transaction_value,
-- 效率指標
-- 人均營收
ROUND(
SUM(total_amount) / COUNT(DISTINCT salesperson_id),
2
) AS revenue_per_salesperson,
-- 客均營收
ROUND(
SUM(total_amount) / COUNT(DISTINCT customer_id),
2
) AS revenue_per_customer,
-- 平均訂單處理時間(假設有 processing_days 欄位)
-- ROUND(AVG(processing_days), 1) AS avg_processing_days,
-- 獲利指標
SUM(total_amount * profit_margin) AS regional_profit,
ROUND(
SUM(total_amount * profit_margin) / SUM(total_amount) * 100,
2
) AS profit_margin_percentage
FROM
transactions
WHERE
transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
GROUP BY
region
ORDER BY
regional_revenue DESC;
-- 註解:地區比較分析幫助識別高表現市場和需要支援的區域
-- 8. 同期比較分析
-- 比較本月與上月的表現
SELECT
'本月' AS period,
SUM(total_amount) AS revenue,
ROUND(AVG(total_amount), 2) AS avg_order_value,
COUNT(*) AS order_count
FROM
transactions
WHERE
YEAR(transaction_date) = YEAR(CURRENT_DATE)
AND MONTH(transaction_date) = MONTH(CURRENT_DATE)
UNION ALL
SELECT
'上月' AS period,
SUM(total_amount) AS revenue,
ROUND(AVG(total_amount), 2) AS avg_order_value,
COUNT(*) AS order_count
FROM
transactions
WHERE
transaction_date >= DATE_FORMAT(
DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH),
'%Y-%m-01'
)
AND transaction_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01');
-- 註解:UNION ALL 結合多個查詢結果,適合進行同期比較
SUM 和 AVG 函式在企業級應用中往往需要結合其他函式和子查詢才能發揮最大價值。理解如何正確處理 NULL 值、如何計算加權平均、如何避免除以零錯誤,以及如何解釋統計結果,這些都是資料分析師必備的技能。在處理大規模資料時,還需要注意這些聚合運算的效能影響,適當使用索引和分區可以顯著提升查詢速度。
GROUP BY 分組統計的系統化應用
GROUP BY 子句將 SQL 從簡單的資料檢索工具提升為強大的分析平台。透過分組功能,我們可以將龐大的資料集按照不同維度切分,針對每個群組分別計算統計指標。這種能力讓我們能夠從多個角度審視資料,發現不同層面的模式和趨勢。
在 SQL 的執行順序中,GROUP BY 位於 WHERE 之後、SELECT 之前。這意味著 WHERE 子句先過濾出要分析的資料,然後 GROUP BY 將這些資料分組,接著對每個群組執行聚合計算,最後 SELECT 選擇要顯示的欄位。理解這個執行順序對於撰寫正確的查詢至關重要。
GROUP BY 子句有一個重要的語法規則:SELECT 子句中所有非聚合函式的欄位,都必須出現在 GROUP BY 子句中。這個規則確保了每個群組只產生一筆結果記錄。如果違反這個規則,資料庫無法判斷應該顯示該欄位的哪個值,因此會產生錯誤。
@startuml
!define DISABLE_LINK
!define PLANTUML_FORMAT svg
!theme _none_
skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100
title SQL 查詢執行順序與 GROUP BY 處理流程
start
:FROM 子句指定資料表;
note right
讀取基礎資料表
執行 JOIN 連接
建立初始結果集
end note
:JOIN 處理;
:WHERE 子句過濾記錄;
note right
在分組前執行
只能使用表格欄位
不能使用聚合函式
end note
:GROUP BY 子句建立群組;
note right
依指定欄位分組
相同值的記錄歸為一組
每組產生一筆結果
end note
:執行聚合計算;
note right
對每個群組分別計算
COUNT SUM AVG 等函式
end note
:HAVING 子句篩選群組;
note right
在分組後執行
可以使用聚合函式
篩選聚合結果
end note
:SELECT 子句選擇欄位;
note right
選擇要顯示的欄位
可以包含聚合結果
非聚合欄位必須在 GROUP BY 中
end note
:DISTINCT 去重;
:ORDER BY 子句排序結果;
note right
對最終結果排序
可以使用欄位別名
可以使用聚合結果
end note
:LIMIT 限制筆數;
stop
note right
WHERE vs HAVING 的關鍵差異
WHERE 在分組前執行
用於過濾原始記錄
不能使用聚合函式
HAVING 在分組後執行
用於過濾聚合結果
可以使用聚合函式
最佳實務:
儘可能使用 WHERE 減少資料量
只在需要篩選聚合結果時使用 HAVING
end note
@enduml
-- GROUP BY 分組統計的企業級應用範例
-- 展示多維度分組分析的實務模式
-- 範例情境:分析企業的銷售資料
-- 假設有 sales 表格包含以下欄位:
-- sale_id, customer_id, product_id, salesperson_id, region_id,
-- sale_date, quantity, unit_price, discount, total_amount,
-- payment_method, order_source
-- 1. 單一維度分組:業務人員績效分析
-- 計算每位業務人員的銷售統計
SELECT
salesperson_id,
-- 交易統計
COUNT(*) AS total_transactions,
COUNT(DISTINCT customer_id) AS unique_customers,
-- 營收統計
SUM(total_amount) AS total_revenue,
ROUND(AVG(total_amount), 2) AS avg_transaction_value,
MAX(total_amount) AS largest_sale,
MIN(total_amount) AS smallest_sale,
-- 產品數量統計
SUM(quantity) AS total_quantity_sold,
ROUND(AVG(quantity), 1) AS avg_quantity_per_order,
-- 時間範圍
MIN(sale_date) AS first_sale_date,
MAX(sale_date) AS last_sale_date,
DATEDIFF(MAX(sale_date), MIN(sale_date)) AS active_days
FROM
sales
WHERE
-- WHERE 在分組前執行,用於過濾原始資料
YEAR(sale_date) = YEAR(CURRENT_DATE)
AND total_amount > 0 -- 排除退貨或無效交易
GROUP BY
salesperson_id
-- HAVING 在分組後執行,用於篩選聚合結果
HAVING
COUNT(*) >= 10 -- 只顯示至少有 10 筆交易的業務人員
ORDER BY
total_revenue DESC;
-- 註解:這個查詢展示了 WHERE 和 HAVING 的正確使用時機
-- 2. 雙維度分組:業務人員與客戶的關係分析
-- 分析每位業務人員對各個客戶的銷售情況
SELECT
salesperson_id,
customer_id,
-- 交易頻率
COUNT(*) AS transaction_count,
-- 營收統計
SUM(total_amount) AS total_revenue,
ROUND(AVG(total_amount), 2) AS avg_order_value,
-- 客戶忠誠度指標
-- 計算該業務人員對該客戶的最後一次交易距今天數
DATEDIFF(CURRENT_DATE, MAX(sale_date)) AS days_since_last_purchase,
-- 計算購買頻率(天/次)
ROUND(
DATEDIFF(MAX(sale_date), MIN(sale_date)) /
NULLIF(COUNT(*) - 1, 0),
1
) AS avg_days_between_purchases
FROM
sales
WHERE
sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
GROUP BY
salesperson_id,
customer_id
-- 多個 HAVING 條件的組合使用
HAVING
COUNT(*) >= 3 -- 至少 3 次交易
AND SUM(total_amount) > 5000 -- 總額超過 5000
AND DATEDIFF(CURRENT_DATE, MAX(sale_date)) <= 90 -- 近 3 個月有交易
ORDER BY
salesperson_id,
total_revenue DESC;
-- 註解:多維度分組可以揭示資料之間的關聯模式
-- 3. 時間維度分組:月度趨勢分析
-- 分析每個月的銷售趨勢
SELECT
-- 使用日期函式提取年月資訊
YEAR(sale_date) AS sale_year,
MONTH(sale_date) AS sale_month,
-- 格式化顯示年月
DATE_FORMAT(sale_date, '%Y-%m') AS year_month,
-- 訂單統計
COUNT(*) AS order_count,
COUNT(DISTINCT customer_id) AS unique_customers,
COUNT(DISTINCT product_id) AS products_sold,
-- 營收統計
SUM(total_amount) AS monthly_revenue,
ROUND(AVG(total_amount), 2) AS avg_order_value,
-- 計算該月營收佔年度總營收的比例
ROUND(
SUM(total_amount) / (
SELECT SUM(total_amount)
FROM sales
WHERE YEAR(sale_date) = YEAR(CURRENT_DATE)
) * 100,
2
) AS revenue_percentage_of_year
FROM
sales
WHERE
YEAR(sale_date) = YEAR(CURRENT_DATE)
GROUP BY
YEAR(sale_date),
MONTH(sale_date),
DATE_FORMAT(sale_date, '%Y-%m')
ORDER BY
sale_year,
sale_month;
-- 註解:時間維度分組對於趨勢分析和季節性研究特別重要
-- 4. 多層次分組:地區、產品類別的綜合分析
-- 分析不同地區各產品類別的表現
SELECT
region_id,
-- 假設有產品類別資訊(可能需要 JOIN products 表格)
-- product_category,
-- 銷售統計
COUNT(*) AS sales_count,
SUM(quantity) AS total_quantity,
-- 營收與利潤
SUM(total_amount) AS revenue,
ROUND(AVG(total_amount), 2) AS avg_transaction,
-- 折扣分析
ROUND(AVG(discount * 100), 2) AS avg_discount_percentage,
SUM(total_amount * discount) AS total_discount_amount,
-- 市場佔有率
-- 該地區該類別佔所有銷售的比例
ROUND(
COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sales),
2
) AS market_share_percentage
FROM
sales
WHERE
sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
GROUP BY
region_id
-- product_category
HAVING
COUNT(*) > 50 -- 至少 50 筆交易才納入分析
ORDER BY
revenue DESC;
-- 註解:多層次分組可以進行細緻的市場區隔分析
-- 5. 使用 WITH ROLLUP 產生小計與總計
-- 展示如何在分組結果中加入彙總資訊
SELECT
COALESCE(region_id, '總計') AS region,
COALESCE(payment_method, '小計') AS payment,
COUNT(*) AS transaction_count,
SUM(total_amount) AS revenue
FROM
sales
WHERE
sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
GROUP BY
region_id,
payment_method WITH ROLLUP
ORDER BY
region_id,
payment_method;
-- 註解:WITH ROLLUP 自動產生小計和總計行
-- COALESCE 將 NULL 值(代表小計/總計)轉換為有意義的標籤
-- 6. 複雜的條件分組:動態分類統計
-- 根據金額範圍進行分類統計
SELECT
-- 使用 CASE 建立動態分組
CASE
WHEN total_amount >= 10000 THEN '高價值訂單(>=10000)'
WHEN total_amount >= 5000 THEN '中價值訂單(5000-9999)'
WHEN total_amount >= 1000 THEN '一般訂單(1000-4999)'
ELSE '小額訂單(<1000)'
END AS order_category,
COUNT(*) AS order_count,
SUM(total_amount) AS category_revenue,
ROUND(AVG(total_amount), 2) AS avg_order_value,
-- 計算該類別佔總訂單數的百分比
ROUND(
COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sales),
2
) AS order_percentage,
-- 計算該類別佔總營收的百分比
ROUND(
SUM(total_amount) * 100.0 / (SELECT SUM(total_amount) FROM sales),
2
) AS revenue_percentage
FROM
sales
WHERE
sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
GROUP BY
-- GROUP BY 子句中也使用相同的 CASE 表達式
CASE
WHEN total_amount >= 10000 THEN '高價值訂單(>=10000)'
WHEN total_amount >= 5000 THEN '中價值訂單(5000-9999)'
WHEN total_amount >= 1000 THEN '一般訂單(1000-4999)'
ELSE '小額訂單(<1000)'
END
ORDER BY
category_revenue DESC;
-- 註解:CASE 表達式可以建立動態的分組標準
-- 7. 星期與小時的時間模式分析
-- 分析不同星期幾和時段的銷售模式
SELECT
-- 提取星期幾(1=週日, 2=週一, ... 7=週六)
DAYOFWEEK(sale_date) AS day_of_week,
-- 轉換為可讀的星期名稱
CASE DAYOFWEEK(sale_date)
WHEN 1 THEN '週日'
WHEN 2 THEN '週一'
WHEN 3 THEN '週二'
WHEN 4 THEN '週三'
WHEN 5 THEN '週四'
WHEN 6 THEN '週五'
WHEN 7 THEN '週六'
END AS day_name,
-- 假設有時間戳記欄位
-- HOUR(sale_timestamp) AS hour_of_day,
COUNT(*) AS order_count,
SUM(total_amount) AS revenue,
ROUND(AVG(total_amount), 2) AS avg_order_value
FROM
sales
WHERE
sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
GROUP BY
DAYOFWEEK(sale_date)
-- HOUR(sale_timestamp)
ORDER BY
DAYOFWEEK(sale_date);
-- HOUR(sale_timestamp);
-- 註解:時間模式分析可以幫助優化人力配置和促銷時機
-- 8. 客戶分群分析(RFM 模型簡化版)
-- 根據最近購買時間、購買頻率、購買金額進行客戶分群
SELECT
customer_id,
-- Recency: 最近一次購買距今天數
DATEDIFF(CURRENT_DATE, MAX(sale_date)) AS recency_days,
-- Frequency: 購買頻率
COUNT(*) AS purchase_frequency,
-- Monetary: 總消費金額
SUM(total_amount) AS monetary_value,
-- 簡化的客戶分級
CASE
WHEN DATEDIFF(CURRENT_DATE, MAX(sale_date)) <= 30
AND COUNT(*) >= 5
AND SUM(total_amount) >= 20000
THEN 'VIP 客戶'
WHEN DATEDIFF(CURRENT_DATE, MAX(sale_date)) <= 90
AND COUNT(*) >= 3
THEN '活躍客戶'
WHEN DATEDIFF(CURRENT_DATE, MAX(sale_date)) <= 180
THEN '一般客戶'
ELSE '流失風險客戶'
END AS customer_segment
FROM
sales
GROUP BY
customer_id
HAVING
COUNT(*) > 0 -- 至少有一次購買
ORDER BY
monetary_value DESC;
-- 註解:RFM 分析是客戶關係管理中的經典模型
GROUP BY 子句的靈活運用是進行多維度資料分析的關鍵。透過單一或多個欄位的組合分組,我們可以從不同角度觀察資料的特性。結合時間函式可以進行趨勢分析,結合條件表達式可以建立動態分類,結合 ROLLUP 可以產生層級化的統計報表。掌握這些技巧,能夠讓資料分析工作更加高效和深入。
HAVING 子句與進階篩選技巧
HAVING 子句是 GROUP BY 查詢中的重要組成部分,它專門用於篩選聚合後的結果。理解 HAVING 與 WHERE 的本質差異,是撰寫正確分組查詢的關鍵。WHERE 子句在分組前執行,用於過濾原始記錄,因此無法使用聚合函式。HAVING 子句在分組後執行,專門用於篩選聚合計算的結果,因此可以使用聚合函式。
這種設計反映了 SQL 的執行順序邏輯。資料庫引擎首先使用 WHERE 子句過濾出符合條件的記錄,減少需要處理的資料量。然後執行 GROUP BY 將資料分組,並計算每個群組的聚合值。最後使用 HAVING 子句根據這些聚合值進一步篩選群組。這種分階段的處理方式不僅邏輯清晰,也有助於查詢優化。
在實務應用中,合理使用 WHERE 和 HAVING 的組合可以顯著提升查詢效能。一般的原則是:能用 WHERE 過濾的條件就不要放在 HAVING 中,因為提早過濾可以減少分組和聚合計算的資料量。只有涉及聚合函式的條件才應該放在 HAVING 子句中。
-- HAVING 子句的企業級應用範例
-- 展示進階篩選技巧與效能優化策略
-- 範例情境:企業的客戶與訂單分析
-- 假設有完整的 orders 表格資料
-- 1. 基本 HAVING 使用:找出高價值客戶
-- 篩選總消費超過特定金額的客戶
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent,
ROUND(AVG(total_amount), 2) AS avg_order_value,
MAX(order_date) AS last_order_date
FROM
orders
WHERE
-- WHERE 在分組前執行,用於過濾原始資料
order_status != 'cancelled' -- 排除已取消的訂單
AND order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
GROUP BY
customer_id
HAVING
-- HAVING 在分組後執行,用於篩選聚合結果
SUM(total_amount) > 50000 -- 總消費超過 50000
AND COUNT(*) >= 10 -- 至少 10 筆訂單
ORDER BY
total_spent DESC
LIMIT 100;
-- 註解:這個查詢同時使用 WHERE 和 HAVING 進行兩階段篩選
-- WHERE 減少需要分組的資料,HAVING 篩選聚合後的結果
-- 2. 複合條件的 HAVING:多重聚合篩選
-- 找出表現優異但近期活動減少的客戶
SELECT
customer_id,
COUNT(*) AS total_orders,
SUM(total_amount) AS lifetime_value,
ROUND(AVG(total_amount), 2) AS avg_order_value,
-- 計算最近 3 個月的訂單數
COUNT(CASE
WHEN order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
THEN 1
END) AS recent_orders,
-- 計算最近一次訂單距今天數
DATEDIFF(CURRENT_DATE, MAX(order_date)) AS days_since_last_order
FROM
orders
WHERE
order_status NOT IN ('cancelled', 'refunded')
GROUP BY
customer_id
HAVING
-- 歷史表現優異
SUM(total_amount) > 30000
AND COUNT(*) >= 15
-- 但近期活動減少
AND COUNT(CASE
WHEN order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
THEN 1
END) < 3
-- 最後一次訂單在 30-90 天之間
AND DATEDIFF(CURRENT_DATE, MAX(order_date)) BETWEEN 30 AND 90
ORDER BY
lifetime_value DESC;
-- 註解:這種查詢可以識別需要進行客戶挽留的高價值客戶
-- 3. 使用子查詢的 HAVING:與整體指標比較
-- 找出平均訂單金額高於整體平均的客戶
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent,
ROUND(AVG(total_amount), 2) AS avg_order_value,
-- 顯示整體平均值作為參考
(SELECT ROUND(AVG(total_amount), 2) FROM orders) AS overall_avg
FROM
orders
WHERE
order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
GROUP BY
customer_id
HAVING
-- 客戶的平均訂單金額高於整體平均
AVG(total_amount) > (
SELECT AVG(total_amount)
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
)
-- 且訂單數量足夠(避免樣本過小)
AND COUNT(*) >= 5
ORDER BY
avg_order_value DESC;
-- 註解:在 HAVING 中使用子查詢可以實現更複雜的比較邏輯
-- 4. 百分位數篩選:找出前 20% 的客戶
-- 使用變數或子查詢計算閾值
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent
FROM
orders
GROUP BY
customer_id
HAVING
SUM(total_amount) >= (
-- 子查詢計算 80 百分位數
SELECT PERCENTILE_CONT(0.80) WITHIN GROUP (ORDER BY total_spent)
FROM (
SELECT SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
) AS customer_totals
)
ORDER BY
total_spent DESC;
-- 註解:百分位數篩選可以動態調整閾值,適應資料分佈的變化
-- 5. 組合多個聚合條件:綜合績效評估
-- 找出在多個維度都表現良好的業務人員
SELECT
salesperson_id,
-- 銷售量指標
COUNT(*) AS total_sales,
SUM(quantity) AS total_quantity_sold,
-- 營收指標
SUM(total_amount) AS total_revenue,
ROUND(AVG(total_amount), 2) AS avg_deal_size,
-- 客戶指標
COUNT(DISTINCT customer_id) AS unique_customers,
-- 效率指標
ROUND(
SUM(total_amount) / COUNT(*),
2
) AS revenue_per_transaction
FROM
orders
WHERE
order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 QUARTER)
AND order_status = 'completed'
GROUP BY
salesperson_id
HAVING
-- 多重條件組合
COUNT(*) >= 50 -- 至少 50 筆交易
AND SUM(total_amount) >= 100000 -- 營收超過 100000
AND COUNT(DISTINCT customer_id) >= 20 -- 至少 20 個不同客戶
AND AVG(total_amount) >= 2000 -- 平均訂單金額超過 2000
ORDER BY
total_revenue DESC;
-- 註解:多維度篩選可以識別全方位表現優異的員工
-- 6. 時間序列篩選:找出成長趨勢明顯的客戶
-- 比較不同時期的表現
SELECT
customer_id,
-- 最近 3 個月的訂單統計
COUNT(CASE
WHEN order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
THEN 1
END) AS recent_orders,
SUM(CASE
WHEN order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
THEN total_amount
ELSE 0
END) AS recent_revenue,
-- 前 3-6 個月的訂單統計
COUNT(CASE
WHEN order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
AND order_date < DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
THEN 1
END) AS previous_orders,
SUM(CASE
WHEN order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
AND order_date < DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
THEN total_amount
ELSE 0
END) AS previous_revenue
FROM
orders
WHERE
order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
GROUP BY
customer_id
HAVING
-- 近期訂單數增加
COUNT(CASE
WHEN order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
THEN 1
END) >
COUNT(CASE
WHEN order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
AND order_date < DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
THEN 1
END)
-- 近期營收增加
AND SUM(CASE
WHEN order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
THEN total_amount
ELSE 0
END) >
SUM(CASE
WHEN order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
AND order_date < DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
THEN total_amount
ELSE 0
END) * 1.2 -- 成長超過 20%
ORDER BY
recent_revenue DESC;
-- 註解:趨勢分析可以識別成長中的客戶,優先分配資源
-- 7. 異常檢測:找出偏離常態的群組
-- 使用標準差識別異常
SELECT
product_category,
COUNT(*) AS order_count,
SUM(total_amount) AS category_revenue,
ROUND(AVG(total_amount), 2) AS avg_order_value,
ROUND(STDDEV(total_amount), 2) AS stddev_order_value,
-- 計算變異係數
ROUND(
STDDEV(total_amount) / AVG(total_amount) * 100,
2
) AS coefficient_of_variation
FROM
orders
WHERE
order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
GROUP BY
product_category
HAVING
COUNT(*) >= 100 -- 樣本數足夠
-- 變異係數異常高(表示訂單金額非常不穩定)
AND STDDEV(total_amount) / AVG(total_amount) > 0.5
ORDER BY
coefficient_of_variation DESC;
-- 註解:異常檢測可以識別需要特別關注的產品類別或市場區隔
-- 8. 效能最佳化:WHERE vs HAVING 的選擇
-- 示範正確的篩選條件放置位置
-- 不佳的做法:所有條件都放在 HAVING
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent
FROM
orders
GROUP BY
customer_id
HAVING
-- 這個條件應該放在 WHERE 中
-- 因為它不涉及聚合函式
MAX(order_date) >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
AND SUM(total_amount) > 10000;
-- 註解:這個查詢會先分組所有客戶,然後才篩選,效率較低
-- 良好的做法:合理分配 WHERE 和 HAVING
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent
FROM
orders
WHERE
-- 提早過濾,減少需要分組的資料量
order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
GROUP BY
customer_id
HAVING
-- 只有聚合條件放在 HAVING
SUM(total_amount) > 10000
ORDER BY
total_spent DESC;
-- 註解:這個查詢先過濾再分組,效率更高
-- 9. 動態閾值:基於統計量的篩選
-- 找出高於平均水準的群組
SELECT
region,
COUNT(*) AS order_count,
SUM(total_amount) AS regional_revenue,
ROUND(AVG(total_amount), 2) AS avg_order_value
FROM
orders
WHERE
order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
GROUP BY
region
HAVING
-- 地區平均訂單金額高於全國平均
AVG(total_amount) > (
SELECT AVG(total_amount)
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
)
-- 且訂單數量達到最低要求
AND COUNT(*) >= (
SELECT AVG(order_count)
FROM (
SELECT COUNT(*) AS order_count
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
GROUP BY region
) AS regional_counts
)
ORDER BY
regional_revenue DESC;
-- 註解:動態閾值會隨資料變化自動調整,比固定閾值更有彈性
HAVING 子句的靈活運用能夠實現複雜的分組篩選邏輯。透過組合多個聚合條件、使用子查詢進行比較、結合條件表達式進行動態分類,我們可以從聚合結果中精確地篩選出符合特定標準的群組。同時,理解 WHERE 和 HAVING 的執行順序差異,合理分配篩選條件,對於查詢效能的優化至關重要。
結語
SQL 的聚合函式、分組統計和字串處理能力,構成了現代資料分析的技術基礎。透過本文的深入探討,我們系統性地學習了從基礎的 COUNT、SUM、AVG、MAX、MIN 聚合函式,到進階的 GROUP BY 多維度分組分析,再到 HAVING 子句的精確篩選技巧。這些技術的組合運用,讓我們能夠從原始資料中提取關鍵指標,發現隱藏的模式,並產生支援決策的商業洞察。
聚合函式的核心價值在於其能夠將大量記錄彙總為有意義的統計值。COUNT 函式讓我們能夠快速了解資料規模和完整性,SUM 和 AVG 提供了營收和績效的量化指標,MAX 和 MIN 則幫助我們識別極值和異常情況。理解這些函式對 NULL 值的處理方式,以及它們在不同場景下的適用性,是正確使用聚合函式的基礎。
GROUP BY 子句將 SQL 從簡單的資料檢索提升為強大的分析工具。透過單一或多個欄位的分組,我們可以從不同維度審視資料,進行業務人員績效比較、產品類別分析、時間趨勢研究和客戶分群。理解 SQL 的執行順序,特別是 WHERE、GROUP BY、HAVING 和 SELECT 之間的關係,對於撰寫正確且高效的查詢至關重要。
HAVING 子句補充了 WHERE 子句在分組查詢中的不足,讓我們能夠根據聚合結果進行篩選。這種能力在識別高價值客戶、評估業務人員表現、監控產品績效等場景中特別重要。透過組合多個聚合條件、使用子查詢進行動態比較,我們可以實現精細的分析需求。同時,合理分配 WHERE 和 HAVING 的篩選條件,能夠顯著提升查詢效能。
在企業級應用中,這些技術往往需要結合使用才能發揮最大價值。子查詢與聚合函式的組合提供了強大的分析能力,例如找出高於平均值的記錄、計算排名和百分位數、進行同期比較分析。視窗函式(Window Functions)則進一步擴展了分析的可能性,讓我們能夠在保留原始記錄的同時計算聚合值。
效能考量是實務應用中不可忽視的重要議題。在大型資料集上不當使用聚合函式可能導致查詢時間急劇增加。提早使用 WHERE 過濾資料、適當建立索引、避免對計算欄位進行聚合,這些都是查詢優化的基本原則。在處理百萬級以上的資料時,可能需要考慮分區、物化視圖或彙總表等進階技術。
掌握這些 SQL 技術不僅能夠提升資料分析能力,更能夠改變我們思考商業問題的方式。當我們能夠熟練地使用聚合函式和分組統計時,許多看似複雜的分析需求都可以透過簡潔的 SQL 查詢來實現。這種能力讓資料分析師和開發者能夠更快速地響應業務需求,從資料中發掘價值,為企業的數位轉型提供堅實的技術支援。