SQL85 统计每个产品的销售情况

好复杂，俺不中了。。

问题描述

本查询旨在分析2023年各产品的销售情况，包括：

每个产品的总销售额、单价、总销量和月均销售额
每个产品销量最高的月份及其销量
每个产品购买量最高的客户年龄段

解题思路

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