优化SQL查询：避免子查询重复，高效统计客户消费总额

需求说明 #

需要统计两类数据：消费最高客户的消费总额，以及其余所有客户的消费总额。

原写法的问题 #

原代码通过CTE计算客户消费总额后，多次重复查询该CTE获取最大值、筛选数据，存在重复计算和代码冗余的问题，既影响性能也降低了可读性。

优化方案：利用窗口函数简化逻辑 #

通过窗口函数MAX() OVER()可以一次获取全局最高消费额，无需多次子查询，同时用CASE语句分组聚合，代码更简洁，性能更优：

WITH customer_spending AS (
  SELECT 
    c.cust_name,
    SUM(oi.quantity * oi.item_price) AS total_spent,
    MAX(SUM(oi.quantity * oi.item_price)) OVER() AS max_total_spent
  FROM Customers c
  JOIN Orders o ON c.cust_id = o.cust_id
  JOIN OrderItems oi ON oi.order_num = o.order_num
  JOIN Products p ON p.prod_id = oi.prod_id
  GROUP BY c.cust_name
)
SELECT
  CASE 
    WHEN total_spent = max_total_spent THEN cust_name
    ELSE 'Others'
  END AS cust_name,
  SUM(total_spent) AS total_spent
FROM customer_spending
GROUP BY 
  CASE 
    WHEN total_spent = max_total_spent THEN cust_name
    ELSE 'Others'
  END;

优化点说明 #

1. 减少重复计算：仅通过一次分组聚合计算客户消费总额，同时用窗口函数获取全局最高值，避免多次查询同一数据集。
2. 代码更简洁：通过CASE语句直接分组，无需UNION拼接两个结果集。
3. 性能提升：窗口函数的计算在分组聚合时一并完成，减少了SQL引擎的执行步骤，配合关联字段（cust_id、order_num、prod_id）的索引，能大幅提升查询效率。

额外提示 #

原CTE中已经按cust_name分组得到每个客户的唯一消费总额，后续查询中无需再用MAX(tst.total_spent)，这属于冗余操作，可以直接使用total_spent字段。

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