SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 30.03.2018 11:48:26

sergey.pte
Участник
Зарегистрирован: 11.12.2015
Сообщений: 4

Значение с максимальной датой

Здравствуйте! Прошу помощи в написании запроса, своей фантазии не хватает, гугл тоже на правильную мысль не натолкнул.
Есть следующая таблица:
CREATE TABLE `test` (
`ID` INT NOT NULL AUTO_INCREMENT,
`IdNameBlock` INT NULL,
`Param` TINYTEXT NULL,
`Value` FLOAT NULL,
`DateDB` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Для примера заполним ее вот такими данными:

[syntax=mysql]INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (1, 'T1', 55.2, '2018-03-28 19:34:20');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (1, 'T2', 55.4, '2018-03-28 19:34:20');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (1, 'T3', 55.5, '2018-03-28 19:34:21');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (2, 'T1', 17.4, '2018-03-28 19:34:22');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (2, 'T2', 17.5, '2018-03-28 19:34:22');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (2, 'T3', 17.6, '2018-03-28 19:34:23');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (3, 'T1', 38.7, '2018-03-28 19:34:24');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (3, 'T2', 38.9, '2018-03-28 19:34:25');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (3, 'T3', 38.9, '2018-03-28 19:34:25');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (1, 'T1', 57.3, '2018-03-28 21:50:38');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (1, 'T2', 57.4, '2018-03-28 21:50:39');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (1, 'T3', 57.5, '2018-03-28 21:50:39');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (2, 'T1', 16.1, '2018-03-28 21:50:39');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (2, 'T2', 16.2, '2018-03-28 21:50:40');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (2, 'T3', 16.4, '2018-03-28 21:50:40');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (3, 'T1', 48.7, '2018-03-28 21:50:41');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (3, 'T2', 48.6, '2018-03-28 21:50:41');
INSERT INTO `boiler`.`test` (`IdNameBlock`, `Param`, `Value`, `DateDB`) VALUES (3, 'T3', 48.9, '2018-03-28 21:50:42');
[/syntax]

Задача получить значение Value от каждой записи IdNameBlock с разными Param (T1,T2,T3) и максимальной датой.
То есть результатом выборки должно быть:
ID|IdNameBlock|Param|Value|    DateDB
10|        1     | T1     |57.3  |2018-03-28 21:50:38
11|        1     | T2     |57.4  |2018-03-28 21:50:39
12|        1     | T3     |57.5  |2018-03-28 21:50:39
13|        2     | T1     |16.1  |2018-03-28 21:50:39
14|        2     | T2     |16.2  |2018-03-28 21:50:40
15|        2     | T3     |16.4  |2018-03-28 21:50:40
16|        3     | T1     |48.7  |2018-03-28 21:50:41
17|        3     | T2     |48.6  |2018-03-28 21:50:41
18|        3     | T3     |48.9  |2018-03-28 21:50:42

Сделал следующий запрос:
SELECT s1.ID,s1.IdNameBlock, s1.Param, s1.Value, s1.DateDB
FROM test s1
JOIN (
SELECT IdNameBlock, MAX(DateDB) AS dateDB
FROM test
GROUP BY IdNameBlock
) AS s2 ON s1.IdNameBlock = s2.IdNameBlock AND s1.DateDB = s2.dateDB
ORDER BY s1.IdNameBlock ASC


И получаю такую выборку:
ID|IdNameBlock|Param|Value|    DateDB
11|      1              | T2  |57.4 |2018-03-28 21:50:39
12|     1              | T3  |57.5 |2018-03-28 21:50:39
14|     2              | T2  |16.2 |2018-03-28 21:50:40
15|     2              | T3  |16.4 |2018-03-28 21:50:40
18|     3              | T3  |48.9 |2018-03-28 21:50:42

Часть данных отрезается, так как есть записи у которых дата не соответствует MAX (меньше на одну - две секунды).
Подскажите пожалуйста, как получить желаемый результат?

Неактивен

 

#2 30.03.2018 11:55:44

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Значение с максимальной датой

SELECT s1.ID,s1.IdNameBlock, s1.Param, s1.Value, s1.DateDB
FROM test s1
JOIN (
SELECT IdNameBlock, Param, MAX(DateDB) AS dateDB
FROM test
WHERE Param IN ('T1','T2','T3')
GROUP BY IdNameBlock, Param
) AS s2 ON s1.IdNameBlock = s2.IdNameBlock AND s1.DateDB = s2.dateDB AND s1.Param= s2.Param
ORDER BY s1.IdNameBlock ASC

Так?

Неактивен

 

#3 30.03.2018 12:04:36

sergey.pte
Участник
Зарегистрирован: 11.12.2015
Сообщений: 4

Re: Значение с максимальной датой

Блин, все оказывается элементарно)
Да, то что нужно! Огромное Вам спасибо!)

Неактивен

 

#4 31.03.2018 16:23:57

sergey.pte
Участник
Зарегистрирован: 11.12.2015
Сообщений: 4

Re: Значение с максимальной датой

К сожалению радость была не долгой, с появлением 44 тис. записей в таблице, запрос начал выполняться около 2 секунд.
Боюсь, что с ростом таблицы данная ситуация будет усугубляться.
Уважаемые гуру, подскажите как можно оптимизировать выше упомянутый запрос?
EXPLAIN выдает следующие результаты:
https://image.ibb.co/ijkhZn/explain.jpg

На сколько я понимаю, самой тяжелой частью запроса является поиск MAX? Запрос пробегает по всей таблице сравнивая даты.
Возможно ли как то прикрутить ORDER BY ID DESC и например LIMIT в 100 записей и уже выбирать MAX среди этих 100 записей?

Неактивен

 

#5 31.03.2018 18:43:40

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Значение с максимальной датой

1. замените TINYTEXT на varchar(N), где N минимально необходимое
2. создайте составной индекс на (IdNameBlock, Param, DateDB)
3.


SELECT s1.ID,s2.IdNameBlock, s2.Param, s1.Value, s2.DateDB
FROM test s1
JOIN (
SELECT IdNameBlock, Param, MAX(DateDB) AS dateDB
FROM test
WHERE Param IN ('T1','T2','T3')
GROUP BY IdNameBlock, Param
) AS s2 ON s1.IdNameBlock = s2.IdNameBlock AND s1.DateDB = s2.dateDB AND s1.Param= s2.Param
ORDER BY s2.IdNameBlock ASC

Неактивен

 

#6 31.03.2018 19:11:42

sergey.pte
Участник
Зарегистрирован: 11.12.2015
Сообщений: 4

Re: Значение с максимальной датой

Супер!!! Теперь вместо 2,4 сек стало 0,062 сек!
Впервые слышу про составной индекс - отправляюсь читать!
Спасибо Вам большое еще раз.

Неактивен

 

#7 31.03.2018 19:39:17

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Значение с максимальной датой

sergey.pte написал:

Впервые слышу про составной индекс - отправляюсь читать!

начните с FAQ №5

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson