无需聚合函数获取每个客户的Top5下单数量技术问询

我需要从pd_orders表中获取每个客户下单数量最多的前5条记录，目前已通过RANK()和ROW_NUMBER()窗口函数实现需求，但想找到不使用聚合函数的实现方式。

表结构DDL #

CREATE TABLE pd_orders (
  `ordered_date` DATETIME,
  `order_code` VARCHAR(15),
  `customer_code` VARCHAR(14),
  `product_name` VARCHAR(10),
  `quantity` INTEGER
);

INSERT INTO pd_orders
  (`ordered_date`, `order_code`, `customer_code`, `product_name`, `quantity`)
VALUES
  ('2023/1/17', '662370230_FP_TW', '1676797_FP_TW', 'product_1', '10'),
  ('2023/1/17', '662370230_FP_TW', '1676797_FP_TW', 'product_2', '10'),
  ('2023/1/17', '662102654_FP_TW', '3794354_FP_TW', 'product_3', '8'),
  ('2023/1/17', '662513860_FP_TW', '3989950_FP_TW', 'product_4', '8'),
  ('2023/1/17', '662070842_FP_TW', '2384070_FP_TW', 'product_5', '5'),
  ('2023/1/17', '662097031_FP_TW', '8080834_FP_TW', 'product_6', '4'),
  ('2023/1/17', '662097031_FP_TW', '8080834_FP_TW', 'product_7', '4'),
  ('2023/1/17', '662025835_FP_TW', '1635359_FP_TW', 'product_8', '6'),
  ('2023/1/17', '662025835_FP_TW', '1635359_FP_TW', 'product_9', '4'),
  ('2023/1/17', '662025835_FP_TW', '1635359_FP_TW', 'product_10', '4'),
  ('2023/1/17', '662025835_FP_TW', '1635359_FP_TW', 'product_11', '4'),
  ('2023/1/17', '662177606_FP_TW', '4400774_FP_TW', 'product_12', '5'),
  ('2023/1/17', '662177606_FP_TW', '4400774_FP_TW', 'product_13', '5'),
  ('2023/1/17', '662177606_FP_TW', '4400774_FP_TW', 'product_14', '5'),
  ('2023/1/17', '662333911_FP_TW', '6798862_FP_TW', 'product_15', '4'),
  ('2023/1/17', '662333911_FP_TW', '6798862_FP_TW', 'product_16', '7'),
  ('2023/1/17', '662376770_FP_TW', '717440_FP_TW', 'product_17', '4'),
  ('2023/1/17', '662376770_FP_TW', '717440_FP_TW', 'product_18', '4'),
  ('2023/1/17', '662260058_FP_TW', '10822485_FP_TW', 'product_19', '4'),
  ('2023/1/17', '662260058_FP_TW', '10822485_FP_TW', 'product_20', '6'),
  ('2023/1/17', '662260058_FP_TW', '10822485_FP_TW', 'product_21', '5'),
  ('2023/1/17', '662201603_FP_TW', '2653694_FP_TW', 'product_22', '6');

已实现的窗口函数方案 #

方案1：使用RANK() #

SELECT
  customer_code,
  product_name,
  quantity,
  RANK() OVER (ORDER BY quantity DESC, product_name) AS product_rank
FROM
  pd_orders
WHERE
  MONTH(ordered_date) = 1

方案2：使用ROW_NUMBER() #

SELECT
  customer_code,
  product_name,
  quantity,
  ROW_NUMBER() OVER (ORDER BY quantity DESC, product_name) AS product_rank
FROM
  pd_orders
WHERE
  MONTH(ordered_date) = 1

尝试失败的自连接方案（含聚合函数） #

select p1.customer_code,
p1.quantity, 
count(p2.quantity) Sales_Rank

from pd_orders p1, pd_orders p2
where p2.quantity <= p1.quantity

group by p1.customer_code, p1.quantity
order by p1.quantity desc

不使用聚合函数的实现方案 #

方案：利用NOT EXISTS + LIMIT判断排名 #

完全不依赖聚合函数，通过判断当前记录是否存在至少5条同客户的更高优先级记录，筛选出前5条：

SELECT
    p1.customer_code,
    p1.product_name,
    p1.quantity
FROM
    pd_orders p1
WHERE
    MONTH(p1.ordered_date) = 1
    AND NOT EXISTS (
        SELECT 1
        FROM pd_orders p2
        WHERE p2.customer_code = p1.customer_code
              AND (p2.quantity > p1.quantity
                   OR (p2.quantity = p1.quantity AND p2.product_name < p1.product_name))
        LIMIT 5, 1 -- 跳过前5条更高优先级记录，若仍有结果则当前记录不在前5
    )
ORDER BY
    p1.customer_code, p1.quantity DESC, p1.product_name;

逻辑说明 #

1. 自连接限定同一客户，通过quantity降序、product_name升序定义排名优先级；
2. LIMIT 5,1表示跳过前5条更高优先级的记录，如果还能查到结果，说明当前记录的排名在第6及以后，需排除；
3. NOT EXISTS确保只保留排名在前5的记录。

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