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

#开始我想使用and year(s.sale_month)=2023 and month(s.sale_month)<7作为条件筛选的，但是函数的返回结果一直是NULL,添加了str_to_date(s.sale_month, "%Y-%m")之后还是不行，也不知道为什么，就直接看讨论里面的用between...and了

with window_tb as (
    select
        p.product_id,
        quantity,
        row_number() over(partition by product_name order by quantity desc) as max_ranking,
        row_number() over(partition by product_name order by quantity) as min_ranking
    from
        products_underline as p
        left join sales_underline as s on p.product_id=s.product_id and sale_month between "2024-01" and "2024-06"
)

select
    product_id,
    product_name,
    (
        select sum(quantity) 
        from sales_underline as s
        where s.product_id=p.product_id and sale_month between "2024-01" and "2024-06"
    ) as total_sales,
    (
        select quantity
        from window_tb
        where window_tb.product_id=p.product_id and window_tb.max_ranking=1
    ) as max_monthly_sales,
    (
        select quantity
        from window_tb
        where window_tb.product_id=p.product_id and window_tb.min_ranking=1
    ) as min_monthly_sales,
    (
        select convert(avg(quantity), signed) 
        from sales_underline as s
        where s.product_id=p.product_id and sale_month between "2024-01" and "2024-06"
    ) as avg_monthly_sales
from products_underline as p
order by product_id