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

/*没有输出是因为时间处理不当，答案错误是因为销量全部计算为销售额！
这里时间是字符串类型，不能直接用函数取年份和月份！！！*/

/*处理一下总表*/
with details as (
    select p.product_id
    ,product_name
    ,substring(sale_month,6,2) as month
    ,quantity
    from products_underline p
    join sales_underline s on p.product_id = s.product_id
    where substring(sale_month,1,4) = 2024 and substring(sale_month,6,2) between "01" and "06"
    and sale_month is not null
),

/*总销售量和月平均销售量,粒度是产品*/
totalsales as(
    select product_id
    ,product_name
    /*total_sales是总销量不是总销售额！！avg也是平均销量不是平均销售额！！！看清目标字段含义！！*/
    ,sum(quantity) as  total_sales
    ,round(avg(quantity),0) as avg_monthly_sales
    from details d
    group by product_id
    ,product_name
),

/*月最大及最小销售量，是数量！不是总销售额！！！子查询是月销售量,粒度是产品+月份*/
monthly_sales as (
    select product_id
    ,max(monthly_sales) as max_monthly_sales
    ,min(monthly_sales) as min_monthly_sales
    from (
        select product_id
        ,month
        ,sum(quantity) as monthly_sales
        from details
        group by product_id
        ,month
    ) t
    group by product_id
)

/*主查询*/
select t1.product_id
,product_name
,total_sales
,max_monthly_sales
,min_monthly_sales
,avg_monthly_sales
from totalsales t1
join monthly_sales t2 on t1.product_id = t2.product_id
group by t1.product_id
,product_name
order by product_id