Блог

Мне нужно получить максимальную цену на самые дешевые продукты на 33%. Моя идея заключается в следующем. Конечно, этот код — всего лишь пример. Мне нужно использовать подзапросы.

 select max((select price from products order by preco limit 33%  ))  as result from products
  

Например

   product_id    price
      1          10
      2          50
      3          100
      4          400
      5          900
      6         8999
  

Мне нужен запрос, который возвращает 50, поскольку 33% строк равны 2, а максимальное значение 2 (33%) строк равно 50.

В MySQL 8 вы бы использовали оконные функции:

 select avg(precio)
from (select p.*, row_number() over (order by precio) as seqnum,
             count(*) over () as cnt
      from products p
     ) p
where seqnum <= 0.33 * cnt;
  

Очевидно, что существует несколько подходов к этому, но вот как я бы это сделал.

1. Просто получите количество в таблице. Это позволит мне выбрать максимальную цену на 33% самых дешевых продуктов. Допустим, он вернул n записи. Треть от этого была бы n/3 . Здесь вы можете округлить в большую или меньшую сторону, но округлять нужно в случае дроби.
2. Тогда мой запрос был бы примерно таким SELECT * FROM products ORDER BY price ASC LIMIT 1 OFFSET n/3 . Это вернуло бы мне единственную запись с минимальными вычислениями и поиском на стороне MySQL.

Для версий MySQL под управлением MySQL 8.0 вы можете использовать пользовательские переменные MySQL для имитации ROW_NUMBER()

Запрос

  SELECT 
    t.product_id
  , t.price
  , (@ROW_NUMBER := @ROW_NUMBER   1) AS ROW_NUMBER
 FROM 
  t
 CROSS JOIN (SELECT @ROW_NUMBER := 0) AS init_user_variable
 ORDER BY 
  t.price ASC
  

Результат

 | product_id | price | ROW_NUMBER |
| ---------- | ----- | ---------- |
| 1          | 10    | 1          |
| 2          | 50    | 2          |
| 3          | 100   | 3          |
| 4          | 400   | 4          |
| 5          | 900   | 5          |
| 6          | 8999  | 6          |
  

Когда мы получим ROW_NUMBER, мы можем использовать это в сочетании с ROW_NUMBER <= CEIL(((SELECT COUNT(*) FROM t) * 0.33));

Который работает следующим образом

(SELECT COUNT(*) FROM t) => Подсчитывает и возвращает 6
(SELECT COUNT(*) FROM t) * 0.33) Вычисляет 33% из 6, что равно 1,98, и возвращает его
CEIL(..) Возвращает наименьшее целое значение, которое больше или равно 1,98, что в данном случае равно 2 ROW_NUMBER <= 2 Таким образом, последним фильтром является этот.

Запрос

 SELECT 
     a.product_id
   , a.price
FROM (
 SELECT 
    t.product_id
  , t.price
  , (@ROW_NUMBER := @ROW_NUMBER   1) AS ROW_NUMBER
 FROM 
  t
 CROSS JOIN (SELECT @ROW_NUMBER := 0) AS init_user_variable
 ORDER BY 
  t.price ASC
) AS a
WHERE  
 ROW_NUMBER <= CEIL(((SELECT COUNT(*) FROM t) * 0.33));
  

Результат

 | product_id | price |
| ---------- | ----- |
| 1          | 10    |
| 2          | 50    |
  

смотрите демо

Получить максимальную цену так же просто, как добавить ORDER BY a.price DESC LIMIT 1

Запрос

 SELECT 
     a.product_id
   , a.price
FROM (
 SELECT 
    t.product_id
  , t.price
  , (@ROW_NUMBER := @ROW_NUMBER   1) AS ROW_NUMBER
 FROM 
  t
 CROSS JOIN (SELECT @ROW_NUMBER := 0) AS init_user_variable
 ORDER BY 
  t.price ASC
) AS a
WHERE  
 ROW_NUMBER <= CEIL(((SELECT COUNT(*) FROM t) * 0.33))
ORDER BY 
  a.price DESC
LIMIT 1;
  

Результат

 | product_id | price |
| ---------- | ----- |
| 2          | 50    |
  

смотрите демо

Если ваша версия поддерживает оконные функции, вы можете использовать NTILE(3) для разделения строк на три группы, упорядоченные по price . Первая группа будет содержать (около) «33%» самых низких цен. Затем вам просто нужно выбрать МАКСИМАЛЬНОЕ значение из этой группы:

 with cte as (
  select price, ntile(3) over (order by price) as ntl
  from products
)
select max(price)
from cte
where ntl = 1
  

ДЕМОНСТРАЦИЯ

До MySQL 8.0 я бы использовал временную таблицу со AUTO_INCREMENT столбцом:

 create temporary table tmp (
  rn int auto_increment primary key,
  price decimal(10,2)
);
insert into tmp(price)
  select price from products order by price;

set @max_rn = (select max(rn) from tmp);

select price
from tmp
where rn <= @max_rn / 3
order by rn desc
limit 1;
  

ДЕМОНСТРАЦИЯ