ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как найти ближайшую запись?
17 сообщений из 17, страница 1 из 1
Как найти ближайшую запись?
    #39172448
antonv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antonv
Гость
Есть таблица с полем 'ut' типа int, в нем время в формате unixtime.
Есть заданное пользователем значение unixtime, нужно найти в таблице запись, имеющую значение поля 'ut' наиболее близкое к заданному значению или равное ему.

Пример:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ut` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `test` (`id`, `ut`) VALUES
(1, 1455603393),
(2, 1455604393),
(3, 1455605393),
(4, 1455606393),
(5, 1455607393),
(6, 1455608393),
(7, 1455609393);


Пользовательское значение: 1455606390
Должно найтись: запись с id=4 и ut=1455606393

Дополнительные условия:
Записей много - несколько миллионов
Поле ut вполне может быть 0
Дополнительные индексы делать нельзя

Возможный вариант - все аналогично, только поле 'ut' имеет тип varchar.

Вторая, более сложная задача.

Две похожие таблицы:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
CREATE TABLE IF NOT EXISTS `test1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ut` int(10) NOT NULL,
  `p1` varchar(20) NOT NULL,
  `p2` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `test2` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ut` int(10) NOT NULL,
  `p2` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;



Сами данные аналогичны первой задаче.
В первой миллионы записей, во второй - тысячи.

Нужно "раскидать" записи из таблицы test2 по таблице test1, т.е. сделать что-то вроде:
"UPDATE test1 SET test1.p2 = test2.p2 WHERE test1.ut примерно равно test2.ut"

Это вообще возможно?
...
Рейтинг: 0 / 0
16.02.2016, 16:40
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39172513
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Задача одна и та же.
Подход к решению:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT * 
FROM table 
WHERE ut >= :значение
ORDER BY ut ASC LIMIT 1
UNION
SELECT * 
FROM table 
WHERE ut <= :значение
ORDER BY ut DESC LIMIT 1


Из двух записей, надеюсь, одну выбрать сумеешь?
...
Рейтинг: 0 / 0
16.02.2016, 17:43
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39172526
antonv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antonv
Гость
AkinaИз двух записей, надеюсь, одну выбрать сумеешь?
Нет, не сумею, к сожалению. Какая из них будет ближайшая к нужному значению?

И как-таки подобным образом решить второй вопрос?
...
Рейтинг: 0 / 0
16.02.2016, 18:00
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39172528
antonv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antonv
Гость
Да и собственно это фактически два отдельных запроса получилось. Это я и на том же пхп могу сделать, не прибегая к union...
...
Рейтинг: 0 / 0
16.02.2016, 18:02
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39172535
SharuPoNemnogu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SharuPoNemnogu
Участник
Код: sql
1.
select * from table order by abs(ut - unixtime) asc limit 1
...
Рейтинг: 0 / 0
16.02.2016, 18:11
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39172537
antonv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antonv
Гость
SharuPoNemnogu
Код: sql
1.
select * from table order by abs(ut - unixtime) asc limit 1


Супер! Работает как надо. Никогда бы не догадался. Спасибо!

Еще бы вторую задачу решить...
...
Рейтинг: 0 / 0
16.02.2016, 18:15
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39172571
antonv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antonv
Гость
AkinaДа одна это задача, одна! только в первом варианте заданное время - константа, а во втором - значения поля записи второй таблицы.
Еще кстати, момент -
ваш запрос с двумя селектами и union, который при этом не решает задачу, а лишь сужает выборку до двух значений - выполняется 1,32 секунды, а второй предложенный запрос отрабатывает при тех же условиях за 0,67.
...
Рейтинг: 0 / 0
16.02.2016, 19:08
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39172586
antonv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antonv
Гость
Код: sql
1.
update test1 as t1 set p2=(select p2 from test2 order by abs(ut - t1.ut) asc limit 1) WHERE p2 like ''


Как-то так получается?
Вроде бы даже результат устраивает. Но это же селект для каждой записи, которую нужно апдейтить... Наверное это неправильно? Можно что-то улучшить?
...
Рейтинг: 0 / 0
16.02.2016, 19:41
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39172661
antonv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antonv
Гость
antonv
Код: sql
1.
update test1 as t1 set p2=(select p2 from test2 order by abs(ut - t1.ut) asc limit 1) WHERE p2 like ''


Как-то так получается?
Вроде бы даже результат устраивает. Но это же селект для каждой записи, которую нужно апдейтить... Наверное это неправильно? Можно что-то улучшить?
2 000 000 в test1
100 000 в test2
Результат выполнения 10 минут. Это совсем не результат :-(

Мало того, не пойму, как апдейтить два, три и т.д. полей сразу - ведь не так же:

Код: sql
1.
2.
3.
4.
5.
update test1 as t1 
    set 
      p2=(select p2 from test2 order by abs(ut - t1.ut) asc limit 1), 
      p3=(select p3 from test2 order by abs(ut - t1.ut) asc limit 1) 
WHERE p2 like '' or p3 like ''


Эдак будет по 10 минут на каждый столбец... Как оптимизировать?
...
Рейтинг: 0 / 0
16.02.2016, 22:00
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39172711
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
antonvКак оптимизировать?Вернуться к варианту с UNION. Он может использовать индекс, а order by abs(ut - unixtime) - нет.
...
Рейтинг: 0 / 0
16.02.2016, 23:48
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39172719
antonv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antonv
Гость
miksoftantonvКак оптимизировать?Вернуться к варианту с UNION. Он может использовать индекс, а order by abs(ut - unixtime) - нет.
Индекса у используемых полей нет и добавить возможности тоже нет. Я писал об этом в начале.
...
Рейтинг: 0 / 0
17.02.2016, 00:09
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39172746
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
antonvmiksoftпропущено...
Вернуться к варианту с UNION. Он может использовать индекс, а order by abs(ut - unixtime) - нет.
Индекса у используемых полей нет и добавить возможности тоже нет. Я писал об этом в начале.Тогда оставляйте order by abs(ut - unixtime). Это будет хотя бы одно полное сканирование таблицы, а не два.
...
Рейтинг: 0 / 0
17.02.2016, 01:03
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39172787
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
antonvПродемонстрированный вами подход, ни с одной из возможных точек зрения не может служить даже толчком для "подумать", т.к. уводит далеко в сторону от нормальных оптимизированных решений. Подход "тупо в лоб, перебором".
Собственно, все остальное, что вы написали - из той же серии.
У Акины как раз правильный подход... но только для правильных таблиц, с необходимыми индексами. В вашей чудо-таблице их нет, и только поэтому вариант ШПН работает лучше, т.к. делает только 1 фулскан(то самое "тупо в лоб, перебором", о котором вы так пренебрежительно отзываетесь), в то время как по варианту Акины приходится выполнять два.
...
Рейтинг: 0 / 0
17.02.2016, 07:10
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39173288
antonv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antonv
Гость
Кстати, эти "неправильные чудо-таблицы", как вы выразились, генерят некие достаточно старые девайсы, доступа к которым нет и не будет. Да и к самой базе доступ изрядно урезан. А работать с этим надо.
...
Рейтинг: 0 / 0
17.02.2016, 15:41
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39173308
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Размышления предполагались такие:

Есть запрос из двух подзапросов c UNION. Он даёт две записи. У одной время раньше заданного, у другой позже. Надо выбрать ту, которая ближе. Ближе та, у которой меньше разница. Но у одной записи она положительна, у другой отрицательна. Значит, для сравнения надо взять её абсолютное значение. Это делает функция ABS. А если так - то зачем два запроса и две записи, когда можно в одном посчитать все разницы, и выбрать сразу одну запись, у которой это самое ABS(разница) самое маленькое?
И получаем то, что написАл SharuPoNemnogu .
...
Рейтинг: 0 / 0
17.02.2016, 15:55
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39173353
antonv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antonv
Гость
Да, кстати - свою задачу я успешно решил, всем до свидания и спасибо за участие (даже Akina) :-)
...
Рейтинг: 0 / 0
17.02.2016, 16:47
| Ответить | Цитировать | Написать  
Как найти ближайшую запись?
    #39173403
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Модератор: Проредил топик от срача. Надеюсь, не зацепил лишнего.
...
Рейтинг: 0 / 0
17.02.2016, 17:23
| Ответить | Цитировать | Написать  
17 сообщений из 17, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как найти ближайшую запись?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=39172448&tid=1832138]:
 0ms
get settings:
 6ms
get forum list:
 17ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 172ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 36ms
get tp. blocked users:
 1ms
others: 240ms
total:  489ms
созд. / загр.: 489ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]