SQL窗口函数vs GROUP BY：各品类最大折扣商品正确查询

需求说明：
某百货商店拥有CUSTOMER、PRODUCT、PURCHASE三张表，分别存储顾客、商品、购买记录数据。需要查询每个品类中折扣力度最大的商品，要求按品类升序输出品类（category）、商品ID（product_id）、该商品的折扣（discount）。若同一品类中有多个商品折扣相同且为最大值，需输出商品ID最小的商品。

正确实现语句 #

使用窗口函数row_number()可以精准实现需求：

with ranked_discount as (
    select p.category,
         p.product_id,
         p.discount,
         row_number() over (partition by category ORDER BY discount DESC, product_id ASC) as rn_disc
     FROM Product as p
) select category, product_id, discount from ranked_discount where rn_disc = 1

逻辑说明：通过partition by category按品类分组，在每组内先按折扣降序排序，折扣相同时按商品ID升序排序，row_number()会给每组内符合要求的商品标记为1，最后筛选出标记为1的记录即可。

错误实现语句 #

以下语句无法满足需求：

select category, product_id, MAX(discount)
FROM Product
GROUP BY category, product_id
ORDER BY category ASC

错误原因 #

该语句的GROUP BY category, product_id是将每个商品作为单独分组，MAX(discount)实际返回的就是每个商品自身的折扣，完全没有实现“每个品类取最大折扣商品”的逻辑。即便忽略这一点，当同一品类存在多个折扣相同的最大值商品时，该语句会把所有这些商品都返回，无法筛选出其中商品ID最小的那个，不符合需求要求。

内容的提问来源于stack exchange，提问作者Jwan622