使用CTE与窗口函数查询Top5客户总订单值的SQL问题排查

需求说明 #

需要编写包含CTE（公共表表达式）和窗口函数的SQL查询，获取所有订单中总订单值最高的前5位客户及其对应总订单值，预期输出如下：

customerName    totalOrderValue
Euro+ Shopping Channel  820689.54
Mini Gifts Distributors Ltd.    591827.34
Australian Collectors, Co.  180585.07
Muscle Machine Inc  177913.95
La Rochelle Gifts   158573.12

现有代码及问题 #

执行以下SQL代码后，输出完全不符合预期：

with customerCTE as(
select c.customerName, od.quantityOrdered,
sum(od.quantityOrdered*od.priceEach) over(partition by c.customerName) as totalOrderValue
from customers c inner join orders o on c.customerNumber = o.customerNumber
inner join orderdetails od on o.orderNumber = od.orderNumber)
select * from customerCTE
order by customerName desc limit 5;

实际输出 #

+-----------------------------+-----------------+-----------------+
| customerName                | quantityOrdered | totalOrderValue |
+-----------------------------+-----------------+-----------------+
| West Coast Collectables Co. |              46 |        43748.72 |
| West Coast Collectables Co. |              49 |        43748.72 |
| West Coast Collectables Co. |              33 |        43748.72 |
| West Coast Collectables Co. |              39 |        43748.72 |
| West Coast Collectables Co. |              46 |        43748.72 |
+-----------------------------+------------

问题根源分析 #

• 窗口函数误用：sum() over(partition by...)会为每一条订单明细行计算对应客户的总订单值，导致同一客户的每笔订单明细都重复输出总订单值，没有实现客户维度的聚合。
• 排序逻辑错误：现有代码按customerName降序排序，而非按totalOrderValue降序，因此取到的是名称字母排序靠后的客户，而非订单值最高的客户。
• 未去重：没有对同一客户的重复行做去重处理，输出结果充满冗余数据。

修正后的SQL代码 #

WITH customerCTE AS (
    SELECT 
        c.customerName,
        SUM(od.quantityOrdered * od.priceEach) AS totalOrderValue,
        -- 用窗口函数按总订单值降序给客户排名
        RANK() OVER(ORDER BY SUM(od.quantityOrdered * od.priceEach) DESC) AS orderRank
    FROM customers c
    INNER JOIN orders o ON c.customerNumber = o.customerNumber
    INNER JOIN orderdetails od ON o.orderNumber = od.orderNumber
    -- 先按客户维度聚合计算总订单值
    GROUP BY c.customerName
)
SELECT customerName, totalOrderValue
FROM customerCTE
-- 筛选排名前5的客户
WHERE orderRank <= 5
ORDER BY totalOrderValue DESC;

修正说明 #

1. 先聚合再排名：通过GROUP BY c.customerName先计算每个客户的总订单值，避免生成重复行。
2. 正确排序逻辑：使用RANK()窗口函数按总订单值降序排名，确保能精准筛选出订单值最高的客户。
3. 匹配预期输出格式：仅保留customerName和totalOrderValue两个字段，符合需求中的输出要求，同时通过orderRank <=5获取Top5客户。

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