Поиск в MySQL с использованием JOIN или коррелированного подзапроса с TEMPORARY TABLE

Question

Поиск в MySQL с использованием JOIN или коррелированного подзапроса с TEMPORARY TABLE

0

У меня есть две первичные таблицы, providers and депо. У каждого провайдера есть обязательное первое место. Дополнительные места предоставляются путем добавления дополнительного "депо". "etc" представляет большое количество дополнительных варов, связанных с учетной записью поставщика. Дополнительная информация о таблицах и фильтры опущены здесь для упрощения.

providers
+--------+------------+-----+
| id     | location   | etc |
+--------+------------+-----+
| 1      | POINT(1,1) | ... |
| 2      | POINT(1,2) | ... |
| 3      | POINT(1,3) | ... |
+--------+------------+-----+

depots
+---------+------------+------------+
| depotId | providerId | location   |
+---------+------------+------------+
| 1       | 1          | POINT(2,1) |
| 2       | 1          | POINT(2,2) |
| 3       | 1          | POINT(2,3) |
| 4       | 2          | POINT(2,4) |
| 5       | 2          | POINT(2,5) |
+---------+------------+------------+

У поставщика может быть ноль или несколько дополнительных складов. Эти "местоположения" используются для вычисления расстояний входящего "задания" от каждого провайдера. Традиционно я использую UNION, чтобы присоединиться к таблице providers и depots чтобы сформировать единую таблицу, которую я буду называть provDeps.

SELECT id, location, 0 AS depotId FROM providers UNION SELECT p.id, d.location d.id AS depotId FROM провайдеры p, депо d

Предположим, что это представление, для которого сейчас эффективность и индексирование. Надеюсь, это уменьшит визуальную сложность запроса.

provDeps
+--------+------------+---------+-----+
| id     | location   | depotId | etc |
+--------+------------+---------+-----+
| 1      | POINT(1,1) | 0       | ... |
| 1      | POINT(2,1) | 1       | ... |
| 1      | POINT(2,2) | 2       | ... |
| 1      | POINT(2,3) | 3       | ... |
| 2      | POINT(1,2) | 0       | ... |
| 2      | POINT(2,4) | 4       | ... |
| 2      | POINT(2,5) | 5       | ... |
| 3      | POINT(1,3) | 0       | ... |
+--------+------------+---------+-----+

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

SELECT loc.*, degToMeter(st_distance(jobLocation, location)) AS distanceToJob FROM provDeps;

+--------+------------+---------+---------------+-----+
| id     | location   | depotId | distanceToJob | etc |
+--------+------------+---------+---------------+-----+
| 1      | POINT(1,1) | 0       | 8234          | ... |
| 1      | POINT(2,1) | 1       | 7334          | ... |
| 1      | POINT(2,2) | 2       | 6434          | ... |
| 1      | POINT(2,3) | 3       | 5534          | ... |
| 2      | POINT(1,2) | 0       | 4634          | ... |
| 2      | POINT(2,4) | 4       | 3734          | ... |
| 2      | POINT(2,5) | 5       | 2834          | ... |
| 3      | POINT(1,3) | 0       | 1934          | ... |
+--------+------------+---------+---------------+-----+

Теперь мне нужно сократить этот список, чтобы сохранить только ближайшее хранилище, сгруппированное по идентификатору поставщика. Результаты будут включать в себя всех поставщиков, но только для одного депо для каждого - либо основного места, депо "0", либо идентификатора ближайшего склада. Это желаемый результат: -

+--------+------------+---------+---------------+-----+
| id     | location   | depotId | distanceToJob | etc |
+--------+------------+---------+---------------+-----+
| 1      | POINT(2,3) | 3       | 5534          | ... |
| 2      | POINT(2,5) | 5       | 2834          | ... |
| 3      | POINT(1,3) | 0       | 1934          | ... |
+--------+------------+---------+---------------+-----+

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

DROP TEMPORARY TABLE IF EXISTS locTemp;
CREATE TEMPORARY TABLE locTemp AS
    SELECT depots.*, st_distance(jobLocation, location) AS distanceToJob
    FROM provDeps

Затем я попытался использовать коррелированный подзапрос, но это приводит к ошибкам в попытке получить временную таблицу дважды в одном поиске:

SELECT * FROM locTemp
WHERE distanceToJob = (SELECT MIN(distanceToJob) FROM locTemp AS lt WHERE lt.id = locTemp.id);

Это приводит к ошибке "Не удается открыть таблицу:" locTemp ". Я также попытался выполнить соединение, но затем я получаю ошибки группировки или невозможность получить доступ к самой временной таблице из подзапроса:

SELECT * FROM
(
    SELECT id, depotId, MIN(distanceToJob) as minDist
    FROM locTemp GROUP BY id
) AS res
INNER JOIN locTemp AS lt on lt.id = res.id and lt.minDist = res.distanceToJob;

Любые указатели, или лучшие решения, будут очень благодарны! :)

NickSaintJohn 13 дек. 2017, в 05:57

Источник

Теги:

mysql

database

temp-tables

inner-join

1 ответ

Ещё вопросы

Tim Biegeleisen · Accepted Answer · 2017-12-13T01-52-00.000Z

Я не думаю, что вам обязательно нужна временная таблица или посмотреть здесь. Ваш окончательный запрос полностью отслеживается здесь, который я изменил ниже. Единственные проблемы, которые я вижу, это то, что вы выбираете неагрегатный столбец с GROUP BY и, возможно, существует проблема с использованием временной таблицы.

SELECT
    t1.id, t1.location, t1.depotId,
    degToMeter(st_distance(t1.jobLocation, t1.location)) AS distanceToJob
FROM provDeps t1
INNER JOIN
(
    SELECT
        id,
        MIN(degToMeter(st_distance(t1.jobLocation, t1.location))) AS minDistanceToJob
    FROM provDeps
    GROUP BY id
) t2
    ON t1.id = t2.id AND
       degToMeter(st_distance(t1.jobLocation, t1.location)) = t2.minDistanceToJob;

Это, кажется, делает трюк ... Потрясающая работа, большое спасибо! Не только делает вещи быстрее, но и намного легче читать :)