SQL 解鎖 - 購物籃分析

購物籃分析（Basket Analysis）是一種常見的資料探勘技術，可以幫助企業了解哪些產品經常被消費者一起購買，從而優化產品組合和促銷策略。

表1 - 訂單號-SKU的組合

首先，我們將此匯入資料庫，建立一個名為 orders 的資料表，包含以下欄位：

• order_id：訂單編號
• sku：產品代號

產品組合

我們可以使用自我連接（self join）來找出每個訂單中所有可能的產品配對。

SELECT 
    a.sku AS product_A, 
    b.sku AS product_B
FROM 
    orders a
JOIN 
    orders b
ON 
    a.order_id = b.order_id 
    AND a.sku < b.sku;

解析

• 自我連接：將 orders 資料表與自身連接，條件是同一個 order_id。
• 避免重複配對：通過 a.sku < b.sku 條件，避免了相同產品的配對和重複的組合。
• 要注意這裡不能用distinct，因為我們要在下一個步驟使用group by算出頻率。

組合頻率

根據上一個步驟的結果，統計每個產品組合出現次數，找出最常一起購買的產品配對。

WITH product_combinations AS (
    SELECT 
        a.sku AS product_A, 
        b.sku AS product_B
    FROM 
        orders a
    JOIN 
        orders b
    ON 
        a.order_id = b.order_id 
        AND a.sku < b.sku
)
SELECT 
    product_A, 
    product_B, 
    COUNT(*) AS frequency
FROM 
    product_combinations
GROUP BY 
    product_A, 
    product_B
ORDER BY 
    frequency DESC;

解析

• CTE（Common Table Expression）：生成 CTE，方便後續的查詢。
• GROUP BY：以產品組合進行分組，統計每個組合的出現次數。
• ORDER BY：以 frequency (頻率)進行降序排列，頻率最高的組合會排在最前面。
• 從下表可以知道，A003和A004最常被一起下單，共5次。

*上述的資料表和欄位我們皆以英文來撰寫以符合程式碼的特性，若覺得閱讀較為困難我們也有附上一個中文的版本供您參考和練習。

WITH 產品組合 AS (
    SELECT 
        a.產品代號 AS 產品_A, 
        b.產品代號 AS 產品_B
    FROM 
        訂單 a
    JOIN 
        訂單 b ON a.訂單號 = b.訂單號 
    WHERE 
        a.產品代號 < b.產品代號
)
SELECT 
    產品_A, 
    產品_B, 
    COUNT(*) AS 頻率
FROM 
    產品組合
GROUP BY 
    產品_A, 
    產品_B
ORDER BY 
    頻率 DESC;

結論

通過上述步驟，使用 SQL 進行了購物籃分析，找出了最常一起購買的產品組合以:

• 優化產品組合：根據消費者行為調整產品陳列和組合。
• 制定營銷策略：針對高頻組合推出促銷活動，提升銷售額。
• 提升客戶滿意度：提供更貼合客戶需求的產品建議。

遵循著我們上述的思考脈絡，希望能對您思考這類問題有所助益，也謝謝您撥冗完食。這些內容是根據我過去在外商的經驗，非常歡迎業界先進留言與我交流，我也會不定期補充我的觀點，也歡迎Follow我的Threads，持續都會有這類問題討論和生產力提升的點子喔!