SQL如何按product_category分组取每组消费最高的3个customerID

你原有SQL的核心问题是LIMIT 3是对全局查询结果做截断，无法实现每个product_category分组内单独取Top 3的逻辑，以下是两种不同场景的解法：

提示：如果你的数据库把transaction识别为关键字，执行报错时可以将表名用反引号`transaction`包裹。

解法1：支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等通用方案） #

这是性能最高、可读性最好的方案，通过窗口排序函数实现分组内排名，你可以根据对并列值的处理需求选择对应的排序函数：

• 用ROW_NUMBER()：同组内总消费相同的客户会被随机分配不同排名，最终只会返回刚好3个客户，并列的会被截断
• 用RANK()：同组内总消费相同的客户排名相同，后续排名会跳空，比如两个并列第2，下一个就是第4，最终返回的客户数可能少于3
• 用DENSE_RANK()：同组内总消费相同的客户排名相同，后续排名不跳空，比如两个并列第3，两个都会被返回，最终返回的客户数可能多于3

WITH customer_category_spend AS (
    SELECT 
        product_category,
        customerID,
        SUM(dollar_spent) AS total_spend
    FROM transaction
    GROUP BY product_category, customerID
),
ranked_spend AS (
    SELECT 
        *,
        -- 这里可以替换成ROW_NUMBER()/RANK()/DENSE_RANK()满足不同并列需求
        DENSE_RANK() OVER (PARTITION BY product_category ORDER BY total_spend DESC) AS spend_rank
    FROM customer_category_spend
)
SELECT product_category, customerID, total_spend
FROM ranked_spend
WHERE spend_rank <= 3;

解法2：不支持窗口函数的旧版本数据库（如MySQL 5.x） #

可以用关联子查询实现分组取Top N，以下示例对应DENSE_RANK的并列值处理逻辑：

SELECT 
    t1.product_category,
    t1.customerID,
    t1.total_spend
FROM (
    SELECT 
        product_category,
        customerID,
        SUM(dollar_spent) AS total_spend
    FROM transaction
    GROUP BY product_category, customerID
) t1
WHERE (
    SELECT COUNT(DISTINCT t2.total_spend) 
    FROM (
        SELECT 
            product_category,
            customerID,
            SUM(dollar_spent) AS total_spend
        FROM transaction
        GROUP BY product_category, customerID
    ) t2
    WHERE t2.product_category = t1.product_category AND t2.total_spend > t1.total_spend
) < 3
ORDER BY t1.product_category, t1.total_spend DESC;

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