Блог

Главная - Вопросы по программированию - SQL — Эффективное сравнение дат для определенных идентификаторов

SQL — Эффективное сравнение дат для определенных идентификаторов

#sql #database #postgresql

Вопрос:

У меня есть две базы данных, которые выглядят так:

Таблица 1

ID время 1
1 21.3.2000, 16:00
1 21.3.2000, 17:00
… …
2 21.3.2000, 16:00
… …

Таблица 2

ID время 2
1 21.3.2000, 16:30
1 21.3.2000, 18:00
… …
2 21.3.2000, 15:30
… …

где для каждой ID у меня есть несколько дат time1 и time2 .
Для каждой time1 я хочу самую близкую time2 , которая также не превышает time1 .
Я пробовал это, но даже построил картезианское произведение так:

 SELECT h."ID", h."time", m."time"
FROM Table1 as h, Table2 as m
WHERE h."ID" = m."ID"

загружается в течение очень долгого времени из-за огромного объема данных, есть ли какой-либо способ повысить скорость и получить тот же результат?

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

1. Позволяет ли ваше приложение ограничить временной диапазон? Можете ли вы сказать «Для каждого time1 я хочу, чтобы он был ближе time2 всего в течение двух часов time1 » или какой-то такой предел?

2. Это может сработать, не идеально, но если я буду щедр с временными рамками, все должно быть в порядке, если у вас есть пример кода на примете, не могли бы вы поделиться им со мной, пожалуйста?

3. вы правы, время 2 должно быть в таблице 2, моя плохая

4. Если время 1, скажем 21.3.2000, 16:00 , вы хотите учитывать только время, которое превышает его, или вы бы также рассмотрели меньшее время, например 21.3.2000, 15:45 , vs 21.3.2000, 16:30 ?

5. Я хотел бы сопоставить ближайший из них со временем 1, но не больше, чем время 1, я должен определенно добавить это к вопросу!

Ответ №1:

Вы упомянули, что время 2 может быть больше или равно времени 1… это упрощает логику. Я бы попробовал боковое соединение:

 SELECT *
FROM table1
LEFT JOIN LATERAL (
    SELECT *
    FROM table2
    WHERE table2.ID = table1.ID AND table2.time2 >= table1.time1
    ORDER BY table2.time2
    LIMIT 1
) AS table2_new ON true

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

1. Я нашел точную идею в этой статье social.msdn.microsoft.com/Forums/sqlserver/en-US /… пять минут назад, и мне было интересно, почему это так быстро, большое спасибо за ответ, не могли бы вы кратко объяснить, почему это намного быстрее, чем выполнение картезианского продукта, а затем фильтрация? Это только потому, что мы сократили длину второго стола или в этом есть что-то еще?

2. Честно говоря, это то, что я использую в SQL Server для таких запросов (там это называется TOP 1 и ВНЕШНЕЕ ПРИМЕНЕНИЕ). Они используются вместо простых соединений, потому что логика проста. Производительность, по неизвестным мне причинам, очень хорошая. Но очевидно, что боковые соединения не создают поперечного произведения m x n.

3. Я до сих пор слышал, что думать о производительности в SQL не так важно из-за великого компилятора, поэтому я думаю, что это в основном перекрестный продукт.

4. Большое спасибо за ответ, я подробнее рассмотрю различные виды соединений, есть ли какие-то конкретные, которые вы считаете полезными, как это, новичок здесь, как вы можете видеть ^^

5. Просто сосредоточьтесь на ВНУТРЕННЕМ и ВНЕШНЕМ. БОКОВЫЕ используются только в нескольких случаях, таких как этот.

Метки: