Рекурсивный запрос для создания ребер пути?
У меня есть таблица paths:
CREATE TABLE paths (
id_travel INT,
point INT,
visited INT
);
Примеры строк:
id_travel | point | visited
-----------+-------+---------
10 | 35 | 0
10 | 16 | 1
10 | 93 | 2
5 | 15 | 0
5 | 26 | 1
5 | 193 | 2
5 | 31 | 3
И еще одна таблица distances:
CREATE TABLE distances (
id_port1 INT,
id_port2 INT,
distance INT CHECK (distance > 0),
PRIMARY KEY (id_port1, id_port2)
);
Мне нужно сделать вид:
id_travel | point1 | point2 | distance
-----------+--------+--------+---------
10 | 35 | 16 | 1568
10 | 16 | 93 | 987
5 | 15 | 26 | 251
5 | 26 | 193 | 87
5 | 193 | 31 | 356
Я не знаю, как сделать dist_trips рекурсивным запросом здесь:
CREATE VIEW dist_view AS
WITH RECURSIVE dist_trips (id_travel, point1, point2) AS
(SELECT ????)
SELECT dt.id_travel, dt.point1, dt.point2, d.distance
FROM dist_trips dt
NATURAL JOIN distances d;
dist_trips — это рекурсивный запрос, который должен возвращать три столбца: id_travel, point1 и point2 из таблицы paths.
Ответы 1
Вам не нужна рекурсия. Могут быть простыми соединениями:
SELECT id_travel, p1.point AS point1, p2.point AS point2, d.distance
FROM paths p1
JOIN paths p2 USING (id_travel)
LEFT JOIN distances d ON d.id_port1 = p1.point
AND d.id_port2 = p2.point
WHERE p2.visited = p1.visited + 1
ORDER BY id_travel, p1.visited;
дб <> рабочий пример здесь
Кажется, что ваши пути имеют восходящие числа без пробелов. Просто присоедините каждый пункт к следующему.
Я добавил LEFT JOIN, чтобы сохранить все ребра каждого пути в результате, даже если в таблице distances не должно быть соответствующей записи. Наверное, предусмотрительно.
Ваш NATURAL JOIN никуда не делся. Как правило, NATURAL редко используется и легко ломается. Инструкция предупреждает:
USINGis reasonably safe from column changes in the joined relations since only the listed columns are combined.NATURALis considerably more risky since any schema changes to either relation that cause a new matching column name to be present will cause the join to combine that new column as well.