postgresql递归查询，以获得排序的边

idv4meu8 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(357)

我有一张表，有大量的边，它们之间有某种联系（从\u段到\u段）。每条边被分成几个不同的小扇区（从\u米到\u米）。每个较小的扇区都有一个特定的方向。
见下表：

DROP TABLE IF EXISTS network;
CREATE TABLE network (
  id integer PRIMARY KEY,
  from_segment text,
  to_segment text, 
  from_meter int,
  to_meter int,
  direction text
);

INSERT INTO network (id, from_segment, to_segment, from_meter, to_meter, direction) VALUES
  (1,'A', 'B', 0, 5,'X'),
  (2,'A', 'B', 0, 5,'Y'),
  (3,'A', 'B', 5,10,'X'),
  (4,'A', 'B', 5,10,'Y'),
  (5,'A', 'B',10,15,'X'),
  (6,'A', 'B',10,15,'Y'),
  (7,'A', 'B',15,20,'X'),
  (8,'A', 'B',15,20,'Y'),

  (9,'B', 'C', 0, 5,'X'),
  (10,'B', 'C', 0, 5,'Y'),
  (11,'B', 'C', 5,10,'X'),
  (12,'B', 'C', 5,10,'Y'),
  (13,'B', 'C',10,15,'X'),
  (14,'B', 'C',10,15,'Y'),
  (15,'B', 'C',15,20,'X'),
  (16,'B', 'C',15,20,'Y'),

  (17,'C', 'D', 0, 5,'X'),
  (18,'C', 'D', 0, 5,'Y'),
  (19,'C', 'D', 5,10,'X'),
  (20,'C', 'D', 5,10,'Y'),
  (21,'C', 'D',10,15,'X'),
  (22,'C', 'D',10,15,'Y'),

  (23,'E', 'F', 0, 5,'X'),
  (24,'E', 'F', 0, 5,'Y'),
  (25,'E', 'F', 5,10,'X'),
  (26,'E', 'F', 5,10,'Y'),

  (27,'K', 'L', 0, 5,'X'),
  (28,'K', 'L', 0, 5,'Y'),
  (29,'K', 'L', 5,10,'X'),
  (30,'K', 'L', 5,10,'Y'),
  (31,'K', 'L',10,15,'X'),
  (32,'K', 'L',10,15,'Y'),

  (33,'L', 'M', 0, 5,'X'),
  (34,'L', 'M', 0, 5,'Y');

我想做的是将边缘（也适用于每个较小的扇区和方向）划分为特定的等级/顺序：

from_segment| to_segment | result_1 | result_2
     A            B           1         1 
     B            C           1         2
     C            D           1         3
     E            F           2         1
     K            L           3         1
     L            M           3         2

我尝试用几种不同的方法来完成查询，例如递归函数和分区方法，但每次都失败了。我想解决办法介于两者之间，但我不知道如何解决这个问题。
谢谢你的帮助！

sql postgresql window-functions recursive-query graph-algorithm

来源：https://stackoverflow.com/questions/62272142/postgresql-recursive-query-in-order-to-get-ranked-edges

1条答案

按热度按时间

sd2nnvve1#

你想走路吗 (from_segment, to_segmgent) 元组，同时对路径和每个路径上的节点进行编号。
此递归查询应执行所需的操作：

with recursive
    data as (
        select distinct from_segment, to_segment from network
    ),
    rcte as (
        select 
            from_segment, 
            to_segment, 
            row_number() over(order by from_segment) result_1,
            1 result_2
        from data d
        where not exists (
            select 1 from data d1 where d1.to_segment = d.from_segment
        )
        union all
        select  
            d.from_segment,
            d.to_segment,
            r.result_1,
            r.result_2 + 1
        from rcte r
        inner join data d on d.from_segment = r.to_segment
    )
select * from rcte order by result_1, result_2

第一个cte列出不同的元组；然后递归cte从每条路径的开始处开始，将它们与 row_number() ，然后遍历每条路径，沿途递增一个计数器。
db小提琴演示：

from_segment | to_segment | result_1 | result_2
:----------- | :--------- | -------: | -------:
A            | B          |        1 |        1
B            | C          |        1 |        2
C            | D          |        1 |        3
E            | F          |        2 |        1
K            | L          |        3 |        1
L            | M          |        3 |        2

赞(0）回复(0）举报 2021-08-09

我来回答

postgresql递归查询，以获得排序的边

1条答案

相关问题

热门标签

最新问答