Выберите Min Datediff из объединенных таблиц
Сейчас я работаю в BigQuery. У меня есть две таблицы:
Table A {
id int
b_id int
date timestamp
}
Table B {
id int
long float
lat float
date timestamp
}
Для одного b_id в A есть несколько записей в B. Я хочу эффективно объединить обе таблицы и использовать только запись в B, где A.b_id = B.id и разница между временными метками минимальна.
Мое текущее решение:
SELECT A.*, B.*, ABS(DATETIME_DIFF(DATETIME(A.date), DATETIME(B.date), SECOND)) as timeDiff
FROM A
INNER JOIN B
ON A.b_id = B.id
WHERE ABS(DATETIME_DIFF(DATETIME(A.date), DATETIME(B.date), SECOND)) =
(
SELECT MIN(ABS(DATETIME_DIFF(DATETIME(B2.date), DATETIME(A2.date), SECOND)))
FROM B as B2
INNER JOIN A as A2
ON B2.driverId = A2.driverId
GROUP BY A2.driverId, B2.rideId
)
Проблема в том, что для одной записи требуется много времени. Любые предложения о том, как улучшить запрос?
Как бы вы проверили в SQL, если бы он выдал только одну строку, и если бы он выдал больше, как бы вы выбрали только первую?
Ответы 1
Вы можете использовать оконные функции:
select *
from (
select a.*, b.*,
row_number() over(
partition by a.b_id
order by abs(datetime_diff(datetime(a.date), datetime(b.date), second))
) rn
from a
inner join b on a.b_id = b.id
) t
where rn = 1
Привет @GMB, кажется, работает отлично. Не могли бы вы вкратце объяснить, как работает запрос?
@Бруно: конечно. Подзапрос ранжирует записи с одинаковыми b_id по разнице во времени. Запись с наименьшей разницей дат получает рейтинг 1, который мы затем можем использовать для фильтрации. Вы можете запустить подзапрос независимо и посмотреть, что он возвращает.
Как насчет добавления условия к соединению, в котором дата B должна быть между датой A и датой A плюс некоторый интервал, а затем использовать только соединения, которые создают одну строку? (Или взять только первый, если много строк объединяются для каждого идентификатора?)