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

 WITH temp AS(   
    SELECT DISTINCT
        a.product_id,
        a.product_name,
        a.category,
        round(coalesce(sum(b.total_amount) OVER (PARTITION BY a.product_id),0),2) AS q2_2024_sales_total,
        c.supplier_name
    FROM
        product_info AS a
        LEFT JOIN order_info AS b ON a.product_id = b.product_id
        AND b.order_date BETWEEN '2024-04-01' AND '2024-06-30'
        INNER JOIN supplier_info AS c ON a.product_id = c.product_id
 )

SELECT 
    product_id,
    product_name,
    q2_2024_sales_total,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY q2_2024_sales_total DESC) AS category_rank,
    supplier_name
FROM temp
ORDER BY product_id ASC

使用LEFT JOIN 保证即使在第二季度没有订单也存在该product_id,注意在判定日期的时候一定要把日期条件加在LEFT JOIN 里面,因为MySQL执行顺序是先FROM和JOIN判断数据来源再WHERE选择出符合条件的行,如果将日期条件加在WHERE当中那么第二季度没有订单的产品还是会被筛选出去