PostgreSQL查询第三高销量商品ID的问题排查与解决

我有一张transaction表，想要获取按总销量排名第三的商品ID。注意同一商品有多笔交易，每笔交易对应不同的QTY，需按所有交易的QTY总和进行排名。我尝试用rank()函数编写查询，但返回的排名结果异常，不确定问题所在。

我的查询语句 #

select distinct t.product_id,
  sum(t.qty) over (partition by t.product_id) qty, 
  rank() over(partition by t.product_id order by t.qty desc) rnk
from transaction t
order by rnk

表结构 #

CREATE TABLE IF NOT EXISTS "transaction"
  (DATE_ID             BIGINT NOT NULL,
   STORE_ID            INT NOT NULL,
   TRANSACTION_TYPE_ID CHAR(1) NOT NULL,
   PRODUCT_ID          INT NOT NULL,
   QTY                 INT NOT NULL);

测试数据 #

INSERT INTO "transaction"
  (DATE_ID, STORE_ID, TRANSACTION_TYPE_ID, PRODUCT_ID, QTY)
VALUES
  (1, 1, 'A', 1, 2),
  (1, 1, 'B', 1, 1),
  (1, 2, 'A', 4, 1),
  (1, 6, 'A', 3, 1),
  (1, 1, 'A', 1, 1),
  (2, 1, 'B', 1, 1),
  (2, 1, 'A', 1, 1),
  (2, 2, 'A', 2, 5),
  (3, 2, 'A', 2, 7),
  (3, 3, 'A', 2, 1),
  (3, 3, 'B', 1, 15),
  (3, 3, 'A', 1, 1),
  (4, 4, 'A', 1, 1),
  (4, 4, 'A', 1, 5),
  (4, 4, 'A', 1, 11),
  (4, 5, 'A', 3, 2),
  (4, 6, 'A', 3, 1),
  (4, 6, 'A', 3, 1),
  (4, 6, 'B', 2, 1),
  (5, 2, 'A', 2, 2),
  (5, 2, 'B', 1, 1),
  (5, 2, 'A', 2, 1),
  (5, 2, 'A', 4, 1),
  (5, 2, 'A', 5, 1),
  (6, 2, 'B', 4, 1),
  (6, 2, 'A', 6, 1),
  (6, 3, 'A', 3, 5),
  (7, 3, 'A', 2, 7),
  (7, 4, 'A', 2, 1),
  (7, 4, 'B', 2, 15),
  (7, 4, 'A', 2, 1),
  (7, 5, 'A', 2, 1),
  (7, 5, 'A', 2, 5),
  (7, 5, 'A', 2, 11),
  (7, 6, 'A', 2, 2),
  (7, 1, 'A', 2, 1),
  (8, 1, 'A', 2, 1),
  (8, 1, 'B', 2, 1),
  (8, 3, 'A', 3, 2),
  (9, 3, 'B', 3, 1),
  (9, 3, 'A', 3, 1),
  (9, 3, 'A', 3, 1),
  (9, 3, 'A', 3, 1),
  (10, 3, 'B', 3, 1),
  (10, 3, 'A', 3, 1),
  (10, 4, 'A', 4, 5),
  (10, 4, 'A', 4, 7),
  (10, 5, 'A', 5, 1),
  (10, 5, 'B', 5, 15),
  (10, 5, 'A', 5, 1),
  (10, 6, 'A', 6, 1),
  (10, 6, 'A', 6, 5),
  (10, 6, 'A', 6, 11),
  (10, 1, 'A', 1, 2),
  (10, 2, 'A', 2, 1),
  (11, 2, 'A', 2, 1),
  (11, 2, 'B', 2, 1),
  (11, 3, 'A', 5, 2),
  (11, 3, 'B', 5, 1),
  (11, 3, 'A', 5, 1),
  (12, 3, 'A', 5, 1),
  (12, 3, 'A', 5, 1);

你的查询存在两个核心问题：

1. rank() over(partition by t.product_id order by t.qty desc) 是按单个商品的每笔交易QTY降序排名，而非按商品的总销量全局排名。partition by product_id会把每个商品的记录单独分组，导致每个商品内部的记录都有自己的排名，无法得到全局的销量排名。
2. 使用distinct去重是冗余且错误的思路：窗口函数sum(t.qty) over (partition by t.product_id)会给每个商品的每条记录都返回相同的总销量，此时用distinct虽然能得到唯一的商品-总销量组合，但排名逻辑已经完全错误。

要实现需求，需分两步：

1. 先计算每个商品的总销量（聚合所有交易的QTY）；
2. 对总销量进行全局排名，再筛选出排名第三的商品。

方法1：CTE（公共表表达式）实现 #

WITH product_sales AS (
    SELECT 
        product_id,
        SUM(qty) AS total_qty
    FROM "transaction"
    GROUP BY product_id
),
ranked_sales AS (
    SELECT 
        product_id,
        total_qty,
        RANK() OVER(ORDER BY total_qty DESC) AS sales_rank
    FROM product_sales
)
SELECT product_id
FROM ranked_sales
WHERE sales_rank = 3;

方法2：嵌套子查询实现 #

SELECT product_id
FROM (
    SELECT 
        product_id,
        SUM(qty) AS total_qty,
        RANK() OVER(ORDER BY SUM(qty) DESC) AS sales_rank
    FROM "transaction"
    GROUP BY product_id
) AS ranked
WHERE sales_rank = 3;

结果说明 #

按测试数据计算各商品总销量后，product_id=3和product_id=6的总销量相同，因此rank()会给两者都标记为排名3，最终查询会返回这两个商品ID。如果需要严格取第3名（跳过并列情况），可以将RANK()替换为ROW_NUMBER()，但需注意此时若有并列，只会返回其中一个（排序规则由数据库决定）。

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