SQL85 統計每個產品的銷售情況

好復雜，俺不中了。。

問題描述

本查詢旨在分析2023年各產品的銷售情況，包括：

1. 每個產品的總銷售額、單價、總銷量和月均銷售額
2. 每個產品銷量最高的月份及其銷量
3. 每個產品購買量最高的客戶年齡段

解題思路

1. 基礎數據準備 (base_data)

首先從訂單、客戶和產品表中提取2023年的銷售數據，并計算：

• 訂單月份 (month(order_date))
• 訂單總金額 (quantity * unit_price)
• 客戶年齡段分組 (customer_age_group)

WITH base_data AS (SELECTproducts.product_id,MONTH(order_date) AS month,unit_price,quantity,quantity * unit_price AS total_amount,customer_age,CASEWHEN customer_age BETWEEN 1 AND 10 THEN '1-10'WHEN customer_age BETWEEN 11 AND 20 THEN '11-20'WHEN customer_age BETWEEN 21 AND 30 THEN '21-30'WHEN customer_age BETWEEN 31 AND 40 THEN '31-40'WHEN customer_age BETWEEN 41 AND 50 THEN '41-50'WHEN customer_age BETWEEN 51 AND 60 THEN '51-60'ELSE '61+'END AS customer_age_groupFROMordersJOIN customers USING (customer_id)JOIN products USING (product_id)WHEREYEAR(order_date) = 2023
)

2. 計算產品月最高銷量 (max_monthly_quantity_data)

找出每個產品在所有月份中的最高銷量：

• 按產品和月份分組計算總銷量
• 使用窗口函數 max(sum(quantity)) 找出每個產品的月最高銷量

max_monthly_quantity_data AS (SELECT DISTINCTproduct_id,max_monthly_quantityFROM(SELECTproduct_id,MAX(SUM(quantity)) OVER (PARTITION BY product_id) AS max_monthly_quantityFROMbase_dataGROUP BYproduct_id,monthORDER BYproduct_id ASC,month ASC) t1
)

3. 識別主要客戶群體 (grouped_data)

找出每個產品購買量最高的客戶年齡段：

• 按產品和客戶年齡段分組計算總銷量
• 使用 ROW_NUMBER() 按銷量降序排序（銷量相同時按年齡段升序）
• 篩選排名第一的記錄

grouped_data AS (SELECTproduct_id,customer_age_groupFROM(SELECTproduct_id,customer_age_group,total_quantity,ROW_NUMBER() OVER (PARTITION BY product_idORDER BY total_quantity DESC, customer_age_group ASC) AS sales_rankFROM(SELECTproduct_id,customer_age_group,SUM(quantity) AS total_quantityFROMbase_dataGROUP BYproduct_id,customer_age_group) t2ORDER BYproduct_id,sales_rank) t3WHEREsales_rank = 1
)

4. 計算基本銷售統計 (basic_stat)

計算每個產品的核心銷售指標：

• 總銷售額 (sum(total_amount))
• 單價 (unit_price)
• 總銷量 (sum(quantity))
• 月均銷售額 (sum(total_amount)/12)

basic_stat AS (SELECTproduct_id,ROUND(SUM(total_amount), 2) AS total_sales,ROUND(unit_price, 2) AS unit_price,SUM(quantity) AS total_quantity,ROUND(SUM(total_amount) / 12, 2) AS avg_monthly_salesFROMbase_dataGROUP BYproduct_id,unit_price
)

5. 合并最終結果

將三個中間結果合并，并按總銷售額降序、產品ID升序、客戶年齡段升序排序：

SELECT*
FROMbasic_statJOIN max_monthly_quantity_data USING (product_id)JOIN grouped_data USING (product_id)
ORDER BYtotal_sales DESC,product_id ASC,customer_age_group ASC

技術亮點

1. 多維度分析：同時考慮了時間維度（月份）和客戶維度（年齡段）
2. 窗口函數應用：使用 MAX() OVER() 和 ROW_NUMBER() 高效計算極值和排名
3. 數據完整性：確保所有計算都基于2023年的銷售數據
4. 精確分組：正確處理了產品和月份、產品和客戶年齡段的交叉分組

結果解讀

最終結果將展示：

• 按總銷售額排序的所有產品
• 每個產品的核心銷售指標
• 該產品銷量最高的月份對應的銷量
• 該產品最主要的客戶年齡段