PostgreSQL - 排序困惑的数据(递归？)

Question

我在 PostgreSQL 数据库中有一些无序数据，如下所示:

ID  PATH    START   END
7   A123    B       C
4   B456    D       E
9   A123    A       B
2   B456    A       B
6   B456    B       C
21  A123    C       D
3   B456    C       D
5   B456    E       F

START 和 END 值不能按字母顺序排列，这些只是用来说明问题

我正在努力实现这一目标:

id  path    sequence    start   end
9   A123    1           A       B
7   A123    2           B       C
21  A123    3           C       D
2   B456    1           A       B
6   B456    2           B       C
3   B456    3           C       D
4   B456    4           D       E
5   B456    5           E       F

我正在考虑实现此目的的逻辑以确定每条路径 (A123/B456) 的起始值(表示为 A)。然后确定顺序 AB、BC、CD 等)。这需要对所有路径重复。

我编写了遍历给定路径名的递归查询(请参阅 WHERE path = 'B456')

WITH RECURSIVE ordered(id, path, sequence, "start", "end") AS (
WITH path AS (SELECT id, "path", "start", "end"
FROM unordered
WHERE path = 'B456'),
startofpath AS (SELECT p1.id
FROM unordered p1
LEFT JOIN unordered p2 ON p1.start = p2.end
WHERE p2.start IS NULL)

--find start of path (A)
SELECT path.id, path.path, 1, path.start, path.end
FROM path, startofpath
WHERE path.id = startofpath.id
UNION ALL
--add on next path (B -> C)
SELECT path.id, path.path, ordered.sequence + 1, path.start, path.end FROM 
path
INNER JOIN ordered
ON path.start = ordered."end")
SELECT * FROM ordered

示例数据:

CREATE table unordered (
id   INT PRIMARY KEY,
path TEXT NOT NULL,
"start" TEXT NOT NULL,
"end" TEXT NOT NULL);

INSERT INTO unordered (id, path, "start", "end") VALUES (7,'A123','B','C');
INSERT INTO unordered (id, path, "start", "end") VALUES (4,'B456','D','E');
INSERT INTO unordered (id, path, "start", "end") VALUES (9,'A123','A','B');
INSERT INTO unordered (id, path, "start", "end") VALUES (2,'B456','A','B');
INSERT INTO unordered (id, path, "start", "end") VALUES (6,'B456','B','C');
INSERT INTO unordered (id, path, "start", "end") VALUES (21,'A123','C','D');
INSERT INTO unordered (id, path, "start", "end") VALUES (3,'B456','C','D');
INSERT INTO unordered (id, path, "start", "end") VALUES (5,'B456','E','F');

我遇到的问题是如何遍历所有路径(A123，然后是 B456 等)

是否有人可以协助完成下一步？(如果我的想法完全错误，或者从头开始修改我的查询)

非常感谢!

Answer 1

这是您要找的吗？

WITH RECURSIVE
get_path(id, path, sequence, starting, ending) AS (
    SELECT u.id, u.path, 1, u.starting, u.ending 
    FROM unordered AS u
    WHERE u.starting NOT IN (SELECT u.ending FROM unordered AS u) -- a starting point has no entry in ending column

    UNION

    SELECT u.id, u.path, g.sequence + 1, g.ending, u.ending
    FROM get_path AS g, unordered AS u
    WHERE u.starting = g.ending
)
TABLE get_path ORDER BY path, sequence;

请注意我更改了"start"进入starting和 "end"进入ending .