题解 | 电商平台想要了解不同商品在不同月份的销售趋势

-- 逻辑拆解：时间条件2024年上半年，需要聚合的字段：按商品分组的总销量+月均销量+单月最高+单月最低
WITH monthly_total_max AS(
    SELECT 
        t1.product_id,
        t2.quantity,
        DENSE_RANK() OVER(partition by t1.product_id order by t2.quantity DESC) rk1          
    FROM products_underline t1
    LEFT JOIN sales_underline t2
    ON t1.product_id = t2.product_id
    WHERE t2.sale_month BETWEEN '2024-01' AND '2024-06'
),
monthly_total_min AS(
    SELECT 
        t1.product_id,
        t2.quantity,
        DENSE_RANK() OVER(partition by t1.product_id order by t2.quantity asc) rk2          
    FROM products_underline t1
    LEFT JOIN sales_underline t2
    ON t1.product_id = t2.product_id
    WHERE t2.sale_month BETWEEN '2024-01' AND '2024-06'
),
total AS(
    SELECT 
        t1.product_id,
        t1.product_name,
        SUM(t2.quantity) total_sales,
        ROUND(AVG(t2.quantity),0) avg_monthly_sales
    FROM products_underline t1
    LEFT JOIN sales_underline t2
    ON t1.product_id = t2.product_id
    WHERE t2.sale_month BETWEEN '2024-01' AND '2024-06'
    GROUP BY t1.product_id
)


SELECT 
    t1.product_id,
    t1.product_name,
    t1.total_sales,
    t2.quantity max_monthly_sales,
    t3.quantity min_monthly_sales,
    t1.avg_monthly_sales
FROM total t1
LEFT JOIN monthly_total_max t2
ON t1.product_id = t2.product_id
LEFT JOIN monthly_total_min t3
ON t1.product_id = t3.product_id
WHERE t2.rk1 = 1
AND t3.rk2 = 1
ORDER BY t1.product_id