题解 | 分析每个商品在不同时间段的销售情况

select
    product_id,
    product_name,
    q2_2024_sales_total,
    row_number() over (partition by category order by q2_2024_sales_total desc,product_id asc) as category_rank,
    supplier_name
from  
    (select
        p.product_id,
        p.product_name,
        coalesce(round(sum(o.total_amount),2),0.00) as q2_2024_sales_total,
        p.category,
        s.supplier_name
    from 
        product_info p 
    left join 
        (select product_id,total_amount from order_info where order_date between '2024-04-01' and '2024-06-30') o on p.product_id=o.product_id
    left join 
        supplier_info s on p.product_id=s.product_id
    group by
        p.product_id,p.product_name,p.category,s.supplier_name) sub
    order by
        product_id asc

注意如果在外层使用where过滤时间段，会筛选掉这个时间段内没有购买量的产品，即使用left join也没用；为了保留未被购买的产品，应该在order表格里筛选过后，然后再left join