如何优化SQL查询获取各客户类别最高消费总额？

要实现每个客户类别仅返回消费总额最高的客户记录，可以借助窗口函数在现有客户总消费查询的基础上进行筛选，具体方案如下：

优化后的SQL语句 #

WITH CustomerTotal AS (
    SELECT  c.CustomerID,
            c.CustomerName,
            cat.CustomerCategoryName,
            SUM(p.Quantity*p.UnitPrice) AS TotalAmount
    FROM Purchases AS p
    JOIN Customers AS c ON c.CustomerID = p.CustomerID
    JOIN Categories AS cat ON c.CustomerCategoryID = cat.CustomerCategoryID
    GROUP BY c.CustomerID, c.CustomerName, cat.CustomerCategoryName
)
SELECT CustomerID, CustomerName, CustomerCategoryName, TotalAmount
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY CustomerCategoryName ORDER BY TotalAmount DESC) AS rn
    FROM CustomerTotal
) AS ranked
WHERE rn = 1
ORDER BY CustomerCategoryName;

逻辑说明 #

1. CTE CustomerTotal：复用你原有的查询逻辑，计算每个客户的总消费额，并关联对应的客户类别信息。
2. 窗口函数排序：在外层查询中，使用ROW_NUMBER()函数按CustomerCategoryName分区（即每个类别单独分组），再在每个分组内按TotalAmount降序排列，生成唯一行号rn。
3. 筛选最高记录：通过WHERE rn = 1筛选出每个类别中行号为1的记录，也就是该类别消费总额最高的客户。
4. 排序输出：最后按客户类别名称排序，让结果更规整。

特殊情况处理 #

如果同一类别中有多个客户消费总额并列最高，且需要返回所有并列记录，只需将ROW_NUMBER()替换为RANK()或DENSE_RANK()即可：

• RANK()：并列最高的客户会获得相同行号，后续行号会跳过重复计数
• DENSE_RANK()：并列最高的客户获得相同行号，后续行号连续计数

替换后的窗口函数部分示例：

RANK() OVER (PARTITION BY CustomerCategoryName ORDER BY TotalAmount DESC) AS rn

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