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

WITH
    -- 1. 产品基础统计
    product_total AS (
        SELECT
            p.product_id,
            p.unit_price,
            ROUND(SUM(p.unit_price * o.quantity),2) AS total_sales,
            SUM(o.quantity) AS total_quantity,
            ROUND(SUM(p.unit_price * o.quantity) / 12,2) AS avg_monthly_sales
        FROM products AS p
        LEFT JOIN orders AS o 
            ON p.product_id = o.product_id
            AND YEAR(o.order_date) = 2023
        GROUP BY p.product_id, p.unit_price
    ),
    -- 2. 单月最高销量
    product_month_max AS (
        SELECT
            product_id,
            MAX(month_qty) AS max_month_quantity
        FROM (
            SELECT
                product_id,
                SUM(quantity) AS month_qty
            FROM orders
            WHERE YEAR(order_date) = 2023
            GROUP BY product_id, DATE_FORMAT(order_date, '%Y-%m')
        ) t
        GROUP BY product_id
    ),
    -- 3. 客户年龄段
    product_customer_age AS (
        SELECT
            product_id,
            customer_age_group
        FROM (
            SELECT
                o.product_id,
                CASE
                    WHEN c.customer_age BETWEEN 1 AND 10 THEN '1-10'
                    WHEN c.customer_age BETWEEN 11 AND 20 THEN '11-20'
                    WHEN c.customer_age BETWEEN 21 AND 30 THEN '21-30'
                    WHEN c.customer_age BETWEEN 31 AND 40 THEN '31-40'
                    WHEN c.customer_age BETWEEN 41 AND 50 THEN '41-50'
                    WHEN c.customer_age BETWEEN 51 AND 60 THEN '51-60'
                    WHEN c.customer_age >= 61 THEN '61+'
                END AS customer_age_group,
                ROW_NUMBER() OVER (
                    PARTITION BY o.product_id
                    ORDER BY SUM(o.quantity) DESC, c.customer_age ASC
                ) AS rn
            FROM orders AS o
            JOIN customers AS c ON o.customer_id = c.customer_id
            WHERE YEAR(o.order_date) = 2023
            GROUP BY o.product_id, c.customer_id, customer_age_group
        ) AS t
        WHERE rn = 1
    )

-- 最终查询
SELECT
    pt.product_id,
    pt.total_sales,
    pt.unit_price,
    pt.total_quantity,
    pt.avg_monthly_sales,
    IFNULL(pm.max_month_quantity, 0) AS max_monthly_quantity, 
    pca.customer_age_group
FROM product_total AS pt
LEFT JOIN product_month_max AS pm ON pt.product_id = pm.product_id
LEFT JOIN product_customer_age pca ON pt.product_id = pca.product_id
WHERE pt.total_quantity IS NOT NULL
ORDER BY pt.total_sales DESC, pt.product_id;