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

select
    product_id,
    product_name,
    round(sum(sales),0) as total_sales,
    round(max(sales),0) as max_monthly_sales,
    round(min(sales),0) as min_monthly_sales,
    round(avg(sales),0) as avg_monthly_sales
from 
    (select 
        p.product_id,
        p.product_name,
        s.sale_month,
        sum(s.quantity) as sales
    from
        products_underline p 
        join sales_underline s on p.product_id=s.product_id
    where
        sale_month between '2024-01' and '2024-06'
    group by
        p.product_id,
        p.product_name,
        s.sale_month) sub
group by
    product_id,
    product_name
order by
    product_id asc

1. 看到题目首先拆解问题，确定按照哪几个字段进行分组，分组是输出一行（直接聚合）还是给组内数据打标签输出全部（窗口函数）
2. 销量和销售额不一样，看清楚！销售额=销量*价格