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

# 3.筛选出rk为1的行，再选择所需数据即可
SELECT 
    p.product_id,
    ROUND(total_quantity*unit_price,2) total_sales,
    ROUND(unit_price,2) unit_price,
    total_quantity,
    ROUND(total_quantity*unit_price/12,2) avg_monthly_sales,
    monthly_quantity max_monthly_quantity,
    CASE 
        WHEN 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 '60+'
    END AS customer_age_group
FROM(
    # 2.使用窗口函数,按产品id分组，对用户购买数量降排，月份销售数量降排，用户年龄升排编号，分组内编号为1的行即为符合要求的行
    SELECT 
        *, 
        ROW_NUMBER() OVER(
            PARTITION BY product_id 
            ORDER BY monthly_quantity DESC, customer_buy_num DESC, customer_age) rk 
    FROM(
        # 1.通过窗口函数，分别统计不同产品的总销售数量，不同产品不同用户的总购买数量，不同产品不同月份月份的总销售数量
        SELECT
            product_id,
            o.customer_id,
            customer_age,
            SUM(quantity) OVER(PARTITION BY product_id) total_quantity, 
            SUM(quantity) OVER(PARTITION BY product_id,MONTH(order_date)) monthly_quantity,
            SUM(quantity) OVER(PARTITION BY product_id,customer_id) customer_buy_num
        FROM
            orders o
        INNER JOIN
            customers c
            ON c.customer_id = o.customer_id
    ) t1
)t2
INNER JOIN
    products p
    ON p.product_id = t2.product_id
WHERE 
    rk = 1
ORDER BY
    total_sales DESC, p.product_id