Using no aggregate function to get the top 5 for each customer

Question

I would like to get the top 5 quantity for every customers from this table. I am able to get it through rank and row number, but I think there is a way using no aggregate function to achieve this.

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');

Solution using 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

Solution 1:

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

Solution 2:

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

Solution 3:

I kind of having idea that we can create the rank column by let the table's quantity compares with itself but no idea how to do it, below is code i tried but failed

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

FWIW, `rank` isn't an aggregate function, it's a window function. Regardless, if the query with `rank` gets you the right result, why would you not want to use it? — Mureinik, Feb 17 '23 at 14:46
If it works why change it? Unless performance is super bad, but even then you should be able to fix that with indexes. I think what you have is best approach. — Brad, Feb 17 '23 at 14:46

score 0 · Answer 1 · answered Feb 17 '23 at 16:35

I'm not quite clear about the reason why you don't want to use aggregate/window functions. But if you are using MySQL version 8.0, there is a way that could achieve your result without using these functions. We sort the data by customer_code, quantity DESC and using a SQL variable to calculate order rank per each customer. Then with that ranked data, you could apply filter on order rank to retrieve the result that you want to get.

SELECT 
   customer_code, product_name, quantity, order_rank   
FROM 
  (SELECT
    customer_code, product_name, quantity,
    @rank := IF(@current_customer = customer_code, @rank + 1, 1) AS order_rank,
    @current_customer := customer_code 
  FROM
    pd_orders
  WHERE
    MONTH(ordered_date) = 1  
  ORDER BY customer_code, quantity DESC) ranked_data
WHERE order_rank <= 5;

You could see demo here.

Using no aggregate function to get the top 5 for each customer

1 Answers1