题解 | 目标月份的品类销售简报

SELECT
    category,
    orders_cnt,
    buyers_cnt,
    items_qty,
    revenue,
    -- ✅ 处理除以0：如果orders_cnt=0，返回0
    IFNULL(ROUND(revenue/orders_cnt,2),0) avg_order_value,
    -- ✅ 正确排名：revenue降序优先，orders_cnt次之，category最后
    RANK() OVER(ORDER BY revenue DESC, orders_cnt DESC, category) rank_by_revenue
FROM( 
    -- 子查询：计算基础指标
    SELECT
        category,
        -- ✅ 去重统计订单数
        COUNT(DISTINCT o.order_id) orders_cnt,
        -- ✅ 去重统计买家数
        COUNT(DISTINCT o.buyer_id) buyers_cnt,
        -- ✅ 总销售件数
        SUM(i.qty) items_qty,
        -- ✅ 总销售额 = 单价×数量
        SUM(p.price * i.qty) revenue
    FROM product p
    -- ✅ 先关联商品和订单明细
    LEFT JOIN order_items i USING(product_id)
    -- ✅ 再关联订单主表
    LEFT JOIN orders o USING(order_id)
    -- ✅ 关键：只保留8月的订单
    WHERE LEFT(order_date,7) = '2024-08'
    GROUP BY category
) tmp
-- ✅ 外层排序和子查询分组、窗口函数排序一致
ORDER BY 
    revenue DESC, orders_cnt DESC, category