Меню
Что нового?

Выбрать записи c максимальной ценой для минимального размера

sei

Новичок
Всем привет!
На MySql не получается написать запрос тч корректно работал.
Задача заключается среди всех товаров выбрать товары с минимальным размером, и максимальной ценой для этого размера.

Есть 2е таблицы:

PHP: 
//Товары:
CREATE TABLE IF NOT EXISTS `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `a` (`id`, `title`) VALUES
(1, 'Товар1'),
(2, 'Товар2'),
(3, 'Товар3'),
(4, 'Товар4');

// Параметры товаров
CREATE TABLE IF NOT EXISTS `b` (
  `a_id` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  `size` int(11) NOT NULL,
  `price` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `b` (`a_id`, `size`, `count`, `price`) VALUES
(1, 0, 1, 8),
(1, 0, 1, 10),
(1, 1, 2, 7),
(1, 1, 2, 15),
(2, 1, 2, 17);
В ORACLE запрос делается с помощью оконных функций:
PHP: 
select *
from (
    select t1.id,
    t1.title,
    t2.size_,
    t2.price,
    min(t2.size_) over(partition by t1.id) nMinSize,
    max(t2.price) over(partition by t2.size_) nmaxPrice,
    row_number() over(partition by t1.id order by 1) nrow
    from a t1, b t2
    where t1.id = t2.test_id) t
 where t.nrow = 1 and nmaxPrice > 10 and nmaxPrice <= 20;
В MySQL такой запрос на ум приходят только с использованием переменных.
Подскажите пожалуйста если знаете.
 

sei

Новичок
Решение задачи:
PHP: 
SELECT a.id, a.title, b1.size AS 'MinSize', MAX(b1.price) AS 'MaxPrice'
FROM a 
JOIN b AS b1 ON a.id = b1.a_id
WHERE b1.size = (
    SELECT MIN(size) FROM b AS b2 WHERE b2.a_id = b1.a_id
)
GROUP BY a.id, a.title, b1.size;
 

sei

Новичок
Если у товара нет параметров то товары отображены не будут.
Немного переделал запрос тч если в товаре нет параметров, выведем NULL: 'MinSize' и 'MaxPrice'
PHP: 
SELECT a.id, a.title, b1.size AS 'MinSize', b1.price AS 'MaxPrice'
FROM a
LEFT JOIN b AS b1 ON b1.id = (
  SELECT b2.id
  FROM b AS b2 
  WHERE a.id=b2.a_id AND b2.price = (
    SELECT MAX(price) FROM b AS b3 WHERE b3.a_id = b2.a_id AND b3.size = (
      SELECT MIN(size) FROM b AS b4 WHERE b4.a_id = b3.a_id
    )
  ) LIMIT 1
);
 
WMix

WMix

герр M:)ller
Партнер клуба
PHP: 
select id, title, count, min(size) as size, price
from a
left join (
  select a_id, count, size, max(price) as price
  from b
  group by a_id, size
) mp on a_id=id
group by a.id
но если завтра на прилавках появятся маленькие, дорогие товары, помните это пришло ис феликий германий...
 

sei

Новичок
Спасибо за решение задачи.

Ваш запрос мне больше понравился, тк он менее громоздкий и проще для понимания, проще добавлять фильтры, но по времени выполнения он более чем в 2 раза медленнее. Протестировал на небольшой локальной БД. Товаров 2696, параметров 1671.
Запустил на выполнение по 100 раз подряд:
PHP: 
select id, title, count, min(size) as size, price
from a
left join (
  select a_id, count, size, max(price) as price
  from b
  group by a_id, size
) mp on a_id=id
group by a.id
Время выполнения: 0.460608959198

PHP: 
SELECT a.id, a.title, b1.size AS 'MinSize', b1.price AS 'MaxPrice'
FROM a
LEFT JOIN b AS b1 ON b1.id = (
  SELECT b2.id
  FROM b AS b2 
  WHERE a.id=b2.a_id AND b2.price = (
    SELECT MAX(price) FROM b AS b3 WHERE b3.a_id = b2.a_id AND b3.size = (
      SELECT MIN(size) FROM b AS b4 WHERE b4.a_id = b3.a_id
    )
  ) LIMIT 1
);
Время выполнения: 0.22012090683

На сервере эти цифры могут быть другими.
 
WMix

WMix

герр M:)ller
Партнер клуба
твой запрос у меня вообще не исполняется, а если поправить выдает не то что просишь и в дампах 3 ошибки.... сравнивать не могу, но поверю что 2х group by медленно... выбирай быстрый!
PHP: 
 select id, title, 
    (select min(size) from b where b.a_id=id) as size,
    (select max(price) from b where b.a_id=id) as price
from a
 

sei

Новичок
WMix написал(а):
PHP: 
select id, title, 
	(select min(size) from b where b.a_id=id) as size,
	(select max(price) from b where b.a_id=id) as price
from a
Нажмите для раскрытия...
Этот запрос не по задаче работает. Тк он выберет все товары с минимальный размером и максимальной ценой любого размера.
А надо:
sei написал(а):
Задача заключается среди всех товаров выбрать товары с минимальным размером, и максимальной ценой для этого размера.
Нажмите для раскрытия...

WMix написал(а):
твой запрос у меня вообще не исполняется, а если поправить выдает не то что просишь и в дампах 3 ошибки.... сравнивать не могу, но поверю что 2х group by медленно... выбирай быстрый!
Нажмите для раскрытия...
Прошу прощения я допустил ошибку. В таблице 'b' не указал id int(11) AUTO_INCREMENT и точка с запятой стояла после INSERT INTO `b` ... (1, 1, 2, 15);(2, 1, 2, 17); - Сейчас исправил.
 
WMix

WMix

герр M:)ller
Партнер клуба
PHP: 
CREATE TABLE IF NOT EXISTS `b` (
  `a_id` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  `size` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  KEY `size` (`size`),
  KEY `a_id` (`a_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `b`
  ADD CONSTRAINT `b_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`);
PHP: 
select id, title, 
	( select min(size) from b where a.id = b.a_id ) as size,
	( select max(price) from b where a.id = b.a_id and b.size = ( 
		select min(size) from b b1 where a.id = b1.a_id 
	)) as price
from a
я поправил... позже понял ошибку....
Код: 
mysql> select id, title, 
    ->   (select min(size) from b where a.id = b.a_id) as size,
    ->   (select max(price) from b where a.id = b.a_id and b.size = ( 
    ->     select min(size) from b b1 where a.id = b1.a_id 
    ->   )) as price
    -> from a;
+----+-------------+------+-------+
| id | title       | size | price |
+----+-------------+------+-------+
|  1 | Товар1      |    0 |    10 |
|  2 | Товар2      |    1 |    17 |
|  3 | Товар3      | NULL |  NULL |
|  4 | Товар4      | NULL |  NULL |
+----+-------------+------+-------+
4 rows in set (0.00 sec)

mysql> explain select id, title, 
    ->   (select min(size) from b where a.id = b.a_id) as size,
    ->   (select max(price) from b where a.id = b.a_id and b.size = ( 
    ->     select min(size) from b b1 where a.id = b1.a_id 
    ->   )) as price
    -> from a;
+----+--------------------+-------+------+---------------+------+---------+-----------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+-----------+------+-------------+
|  1 | PRIMARY            | a     | ALL  | NULL          | NULL | NULL    | NULL      |    4 |             |
|  3 | DEPENDENT SUBQUERY | b     | ref  | size,a_id     | size | 4       | func      |    1 | Using where |
|  4 | DEPENDENT SUBQUERY | b1    | ref  | a_id          | a_id | 4       | test.a.id |    1 |             |
|  2 | DEPENDENT SUBQUERY | b     | ref  | a_id          | a_id | 4       | test.a.id |    1 |             |
+----+--------------------+-------+------+---------------+------+---------+-----------+------+-------------+
4 rows in set (0.00 sec)
sei написал(а):
...Прошу прощения я допустил ошибку....
Нажмите для раскрытия...
PHP: 
INSERT INTO `a` (`id`, `active`, `title`) VALUES (1, 'Товар1')
... и тут на 3 поля 2 значения
 

sei

Новичок
WMix написал(а):
PHP: 
INSERT INTO `a` (`id`, `active`, `title`) VALUES (1, 'Товар1')
... и тут на 3 поля 2 значения
Нажмите для раскрытия...
Извините. Не все подчистил.

PHP: 
select id, title, 
	( select min(size) from b where a.id = b.a_id ) as size,
	( select max(price) from b where a.id = b.a_id and b.size = ( 
		select min(size) from b b1 where a.id = b1.a_id 
	)) as price
from a
Да этот запрос лучше и работает так же как и 1 запрос.

Сделал запрос с переменной. Избавился от еще одного под запроса, а по времени в 3+ раза дольше чем этот:
PHP: 
select id, title, @min_size := ( select min(size) from b where a.id = b.a_id ) as size,
    ( select max(price) from b where a.id = b.a_id and b.size = @min_size) as price
from a
Время выполнения:
1) 0.0669798851013
2) 0.072741985321
3) 0.068372964859
4) 0.269473075867

Думаю смысл упрощать дальше нет.
Большое спасибо за активное участие обсуждения!
 
  • Like
Реакции: WMix
Войдите или зарегистрируйтесь для ответа.
Сверху