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

select 
    p.product_id,
    p.product_name,
    sum(s.quantity) as total_sales,
    max(s.quantity) as max_monthly_sales,
    min(s.quantity) as min_monthly_sales,
    round(avg(s.quantity),0) as avg_monthly_sales
from products_underline as p
left join sales_underline as s
on p.product_id = s.product_id
WHERE s.sale_month between'2024-01' AND '2024-06'
group by p.product_id
ORDER BY p.product_id;

我在想要是这个题问的是 月销售量和最大/小的月销售量，如果问的是月销售额呢？那就得用CTE写了，先计算出每个月的销售额

WITH monthly_sales AS ( -- 第一步：先算出每个商品、每个月的【月销售额】 SELECT product_id, sale_month, SUM(quantity * sale_price) AS monthly_amount -- 月销售额 FROM sales_underline WHERE sale_month BETWEEN '2024-01' AND '2024-06' GROUP BY product_id, sale_month -- 关键：按 商品+月 聚合 ) SELECT p.product_id, p.product_name, SUM(ms.monthly_amount) AS total_sales_amount, -- 总销售额 MAX(ms.monthly_amount) AS max_monthly_amount, -- 最大月销售额 ✅ MIN(ms.monthly_amount) AS min_monthly_amount, -- 最小月销售额 ✅ ROUND(AVG(ms.monthly_amount), 0) AS avg_monthly_amount -- 平均月销售额 ✅ FROM products_underline AS p LEFT JOIN monthly_sales ms ON p.product_id = ms.product_id GROUP BY p.product_id, p.product_name ORDER BY p.product_id;