题解 | 统计每个产品的销售情况

-- 步骤1：过滤2023年订单，关联客户+产品，生成年龄区间，计算每月销量（用于单月最高销量）
WITH order_2023 AS (
    SELECT 
        o.order_id,
        o.product_id,
        o.customer_id,
        o.quantity,
        o.order_date,
        -- 提取订单月份（用于计算单月销量）
        DATE_FORMAT(o.order_date, '%Y-%m') AS order_month,
        c.customer_age,
        p.unit_price,
        -- 生成年龄区间（严格按题目要求：61+，不是60+！）
        IF(
            c.customer_age > 60,
            '61+',
            CONCAT(((c.customer_age-1) DIV 10)*10 + 1, '-', ((c.customer_age-1) DIV 10 + 1)*10)
        ) AS age_range
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN products p ON o.product_id = p.product_id
    -- 核心过滤：只保留2023年的订单
    WHERE o.order_date >= '2023-01-01' AND o.order_date <= '2023-12-31'
),
-- 步骤2：计算每个产品的单月销量（用于求单月最高销量）
product_monthly_quantity AS (
    SELECT 
        product_id,
        order_month,
        SUM(quantity) AS monthly_quantity
    FROM order_2023
    GROUP BY product_id, order_month
),
-- 步骤3：提取每个产品的单月最高销量
product_max_monthly AS (
    SELECT 
        product_id,
        MAX(monthly_quantity) AS max_monthly_quantity
    FROM product_monthly_quantity
    GROUP BY product_id
),
-- 步骤4：按产品+年龄区间统计购买量总和，排序取销量最高的区间（销量相同取更小区间）
product_age_quantity AS (
    SELECT 
        product_id,
        age_range,
        SUM(quantity) AS age_total_quantity,
        -- 排序规则：销量降序 → 区间升序（小区间优先）
        ROW_NUMBER() OVER(
            PARTITION BY product_id 
            ORDER BY SUM(quantity) DESC, age_range ASC
        ) AS rn
    FROM order_2023
    GROUP BY product_id, age_range
),
-- 步骤5：提取每个产品的主要年龄区间（rn=1）
product_main_age AS (
    SELECT 
        product_id,
        age_range AS customer_age_group
    FROM product_age_quantity
    WHERE rn = 1
),
-- 步骤6：计算每个产品的总销量、总销售额等基础指标
product_base_stat AS (
    SELECT 
        product_id,
        SUM(quantity) AS total_quantity,
        -- 总销售额=总销量×单价（单价唯一，用MAX取）
        ROUND(SUM(quantity) * MAX(unit_price), 2) AS total_sales,
        ROUND(MAX(unit_price), 2) AS unit_price,
        -- 月均销售额=总销售额/12
        ROUND((SUM(quantity) * MAX(unit_price)) / 12, 2) AS avg_monthly_sales
    FROM order_2023
    GROUP BY product_id
)
-- 最终聚合：关联所有指标，按题目要求排序
SELECT 
    pbs.product_id,
    pbs.total_sales,
    pbs.unit_price,
    pbs.total_quantity,
    pbs.avg_monthly_sales,
    pmm.max_monthly_quantity,
    pma.customer_age_group
FROM product_base_stat pbs
JOIN product_max_monthly pmm ON pbs.product_id = pmm.product_id
JOIN product_main_age pma ON pbs.product_id = pma.product_id
-- 严格按题目排序：总销售额降序 → 产品ID升序
ORDER BY 
    pbs.total_sales DESC,
    pbs.product_id ASC;

存一下ai写的sql