Блог

Главная - Вопросы по программированию - MySQL -> Как получить одну строку, предшествующую определенной строке

MySQL -> Как получить одну строку, предшествующую определенной строке

#mysql #sql

Вопрос:

время ценность
00:00 100
00:01 101
00:02 102
01:03 103
02:04 104
03:05 105
03:10 106

Скажем, у меня есть таблица mysql с указанной выше структурой, индексированной по столбцу времени.

Как я могу получить первую строку непосредственно перед определенной меткой времени. Например, я должен быть в состоянии прочитать строку [02:04 : 104], если я хочу получить метку времени до 02:30.

Кроме того, поскольку время не увеличивается последовательно, я не могу получить, просто уменьшившись на одну минуту.

Один из известных мне способов-упорядочить строки по убыванию и ограничить на 1, но, похоже, это выполняет сканирование диапазона индексов, которое будет дорогостоящим по мере увеличения числа строк. Есть ли какой-либо способ получить значение в виде операции с постоянным временем?

Комментарии:

1. Пожалуйста, предоставьте достаточно кода, чтобы другие могли лучше понять или воспроизвести проблему.

Ответ №1:

Вы можете использовать LIMIT запрос:

 SELECT time, value
FROM yourTable
WHERE time < '02:30'
ORDER BY time DESC
LIMIT 1;

Для приведенного выше запроса может быть полезен следующий индекс:

 CREATE INDEX idx ON yourTable (time, value);

Это должно позволить MySQL быстро найти интересующую запись в логарифмическом времени и вернуть ее. Индекс также охватывает value столбец, так что это можно назвать индексом покрытия.

Комментарии:

1. Да, но проблема в том, что, похоже, он выполняет сканирование всех строк до 02:30, а затем возвращает первую строку после спуска. Это будет довольно интенсивно, если в таблице много строк.

2. @VikrantPradhan Такого АФАЙКА не избежать. Проверьте мой обновленный ответ на наличие опции индекса.

Ответ №2:

Я не думаю, что вы сможете сделать это за постоянное время. Я считаю , что вы должны создать предыдущий момент времени с помощью LAG , добавить индекс, а затем отфильтровать с WHERE помощью оператора. Из любопытства и поскольку у вас есть индекс time , выполняется ли это быстрее, чем LIMIT запрос?

 WITH cte AS (
   SELECT time,
      value,
      LAG(time) OVER(ORDER by time) AS prev_time
   FROM input_table
   -- Try to add a WHERE statement here to limit the number of rows selected (since you have an index on time). That would make things faster of course.
)
SELECT *
FROM cte
WHERE '02:30' BETWEEN prev_time and time
Метки: ,