如何用PARTITION BY简化查询，获取客户最高交易数对应的shop_id？

原查询语句 #

WITH base AS (
  SELECT 
    shop_id, 
    customer_id, 
    sum(no_trans) AS total 
  FROM 
    tx.table 
  WHERE region = 'USA' 
  GROUP BY 1, 2
), 
cte2 AS (
  SELECT 
    shop_id, 
    customer_id, 
    total, 
    ROW_NUMBER() OVER (
      PARTITION BY customer_id 
      ORDER BY total DESC
    ) AS rank FROM base
), 
max_shop AS (SELECT * FROM cte2 WHERE rank = 1)

现有CTE输出示例 #

shop_id | customer_id|total |rank
1234    | 100        |6789  |1
345     | 100        |365   |2
673     | 100        |10    |3

需求：简化上述查询，仅返回rank=1的行，寻求更高效简洁的实现方式。

简化方案1：合并CTE层级 #

将多个CTE合并为一个，减少冗余层级，结构更紧凑：

WITH customer_totals AS (
  SELECT 
    shop_id,
    customer_id,
    SUM(no_trans) AS total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY SUM(no_trans) DESC) AS rank
  FROM tx.table
  WHERE region = 'USA'
  GROUP BY shop_id, customer_id
)
SELECT shop_id, customer_id, total
FROM customer_totals
WHERE rank = 1;

简化方案2：使用子查询替代CTE #

省略CTE定义，直接用子查询完成排名与过滤：

SELECT shop_id, customer_id, total
FROM (
  SELECT 
    shop_id,
    customer_id,
    SUM(no_trans) AS total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY SUM(no_trans) DESC) AS rank
  FROM tx.table
  WHERE region = 'USA'
  GROUP BY shop_id, customer_id
) AS ranked_totals
WHERE rank = 1;

补充说明 #

• 若存在多个店铺对应同一客户的最高交易数（即total值相同），ROW_NUMBER()会随机选取其中一条；如果需要保留所有并列最高的记录，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()。
• 两种简化写法的执行效率与原查询基本一致，但结构更简洁，避免了不必要的CTE拆分。

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