Как объединить выборки с расстояниями и минимальным расстоянием в SQL

Что проверяют

Умеете ли вы решать задачу «лучшая строка в группе»: не просто найти минимальное расстояние, а вернуть вместе с ним сам объект. Следом спрашивают, что делать при равных расстояниях и как запрос поведёт себя на большой таблице.

Как отвечать

Сначала уточните постановку: есть таблица пар (from_id, to_id, distance) или две выборки, которые нужно склеить и для каждого объекта найти ближайший.

Склейка: UNION ALL просто объединяет строки, UNION дополнительно убирает дубли и стоит дороже. Для поиска минимума дубли не мешают, берите UNION ALL.

Варианты найти минимум вместе со строкой:

• GROUP BY с MIN(distance): даёт только значение. Добавить to_id в тот же SELECT нельзя: PostgreSQL выдаст ошибку, а MySQL с выключенным ONLY_FULL_GROUP_BY вернёт значение из произвольной строки;
• оконная функция: ROW_NUMBER() OVER (PARTITION BY from_id ORDER BY distance) в CTE и фильтр rn = 1. Работает во всех основных СУБД;
• DISTINCT ON (from_id) с ORDER BY from_id, distance: короткий вариант для PostgreSQL;
• LATERAL с ORDER BY distance LIMIT 1: для каждого объекта одна строка по индексу на (from_id, distance), выгодно, когда групп немного, а строк в них много.

Если расстояние считается из координат, в PostgreSQL берут PostGIS: ST_Distance на типе geography даёт метры, а оператор <-> с GiST-индексом находит ближайших соседей через ORDER BY ... LIMIT k без полного перебора.

Со стороны Java это один нативный запрос через JDBC или @Query(nativeQuery = true), а не выгрузка всех пар в память и поиск минимума в стриме.

Где спотыкаются

Не думают о равенстве. ROW_NUMBER выберет одну из равных строк произвольно, а если нужны все ближайшие, берут RANK().

Соединяют таблицу с подзапросом по значению MIN(distance) и получают дубли, когда минимум совпадает у нескольких строк.

Считают евклидово расстояние по широте и долготе в градусах. На широте Москвы градус долготы почти вдвое короче градуса широты, и ближайшая точка определяется неверно.