SQL Server: bağlantılı satırların sırası (rotalar)

Rotaları olan bir masa var ve onlara gerçek emirleri vermeliyim.

Veriler aşağıdaki gibi görünür:

route | rstart | rfinish | rfrom |  rto  | rorder
--------------------------------------------------
route1|   A    |    E    |   A   |   B   |   0
route1|   A    |    E    |   B   |   E   |   0
route2|   B    |    D    |   B   |   A   |   0
route2|   B    |    D    |   A   |   E   |   0
route2|   B    |    D    |   E   |   D   |   0 

Ve bunu güncellemek istiyorum

route | rstart | rfinish | rfrom |  rto  | rorder
--------------------------------------------------
route1|   A    |    E    |   A   |   B   |   1
route1|   A    |    E    |   B   |   E   |   2
route2|   B    |    D    |   B   |   A   |   1
route2|   B    |    D    |   A   |   E   |   2
route2|   B    |    D    |   E   |   D   |   3

Geldiğim en iyi şey:

UPDATE routes
SET rorder = 1
FROM routes r
WHERE r.rfrom = r.rstart

İlk kenarı işaretlemek ve

UPDATE r
SET rorder = (SELECT TOP 1 r2.rorder
              FROM routes r
              JOIN routes r2 ON r.rfrom = r2.rto 
                              AND r.rstart = r2.rstart 
                              AND r.rfinish = r2.rfinish 
                              AND r.route = r2.route
                              AND r.rorder = 0
                              AND r2.rorder != 0) + 1
FROM routes r
JOIN routes r2 ON r.rfrom = r2.rto 
                 AND r.rstart = r2.rstart 
                 AND r.rfinish = r2.rfinish 
                 AND r.route = r2.route
                 AND r.rorder = 0
                 AND r2.rorder != 0

Sonraki kenar için sonraki numarayı eklemek. Son sorgu N başlatılmalıdır, N burada tablodaki en uzun yoldur.

ROW_ORDER() 'ı denedim ve doğru bölümlere ayırmak kolay ama bende ORDER BY yapamadım.

İşe yarıyor ama korkunç görünüyor. Bunu geliştirmeme yardım eder misiniz?

0

8 cevap

Yinelemeli bir ortak tablo ifadesi kullanırdım. İşte bir rota ile bir örnek, bunu kolayca rota başına çalıştırmak için değiştirebilirsiniz.

create table #routes
(
    name    varchar(20)
,   startR  varchar(1)
,   endR    varchar(1)
,   fromR   varchar(1)
,   toR     varchar(1)
,   orderZ  int
)

insert into #routes (name, startR, endR, fromR, toR, orderZ)

select 'route2', 'A', 'F', 'E', 'F', 0
union all
select 'route2', 'A', 'F', 'A', 'B', 0
union all
select 'route2', 'A', 'F', 'C', 'D', 0
union all
select 'route2', 'A', 'F', 'B', 'C', 0
union all
select 'route2', 'A', 'F', 'D', 'E', 0


select * from #routes;

with routesTree (name, startR, endR, fromR, toR, orderZ)
as
(
    select
        name, startR, endR, fromR,  toR, orderZ
    from #routes
    where
        startR = fromR
    union all
    select
        R.name, R.startR, R.endR, R.fromR,  R.toR, R.orderZ
    from #routes R
    join routesTree T on T.toR = R.fromR
)

select * into #temp1 FROM routesTree
alter table #temp1
    add RowNum int identity (1,1)

select * From #temp1

drop table #routes
drop table #temp1
2
katma
Evet, fikrini beğendim! Ben kimliğin eklenmesiyle yolu sevmediğim için biraz değiştirdim. Cevabımı, yanıtlara ekleyemediğimden göründüğü gibi gönderiyorum.
katma yazar Alex A., kaynak
Gerçek veri kümesinde bir çalışma beni üzdü. Sorgu, durduğumda bir buçuk saatten fazla çalışıyordu. Ben değişkenleri ve WHILE döngüsünü kullanarak sorgu yaptım. En çok SQL-y yolu değil ama 30 saniyede iş yaptı. yardımınız için teşekkürler ve özyinelemeli sorgular tho için bir örnek.
katma yazar Alex A., kaynak

Yinelemeli bir ortak tablo ifadesi kullanırdım. İşte bir rota ile bir örnek, bunu kolayca rota başına çalıştırmak için değiştirebilirsiniz.

create table #routes
(
    name    varchar(20)
,   startR  varchar(1)
,   endR    varchar(1)
,   fromR   varchar(1)
,   toR     varchar(1)
,   orderZ  int
)

insert into #routes (name, startR, endR, fromR, toR, orderZ)

select 'route2', 'A', 'F', 'E', 'F', 0
union all
select 'route2', 'A', 'F', 'A', 'B', 0
union all
select 'route2', 'A', 'F', 'C', 'D', 0
union all
select 'route2', 'A', 'F', 'B', 'C', 0
union all
select 'route2', 'A', 'F', 'D', 'E', 0


select * from #routes;

with routesTree (name, startR, endR, fromR, toR, orderZ)
as
(
    select
        name, startR, endR, fromR,  toR, orderZ
    from #routes
    where
        startR = fromR
    union all
    select
        R.name, R.startR, R.endR, R.fromR,  R.toR, R.orderZ
    from #routes R
    join routesTree T on T.toR = R.fromR
)

select * into #temp1 FROM routesTree
alter table #temp1
    add RowNum int identity (1,1)

select * From #temp1

drop table #routes
drop table #temp1
2
katma
Evet, fikrini beğendim! Ben kimliğin eklenmesiyle yolu sevmediğim için biraz değiştirdim. Cevabımı, yanıtlara ekleyemediğimden göründüğü gibi gönderiyorum.
katma yazar Alex A., kaynak
Gerçek veri kümesinde bir çalışma beni üzdü. Sorgu, durduğumda bir buçuk saatten fazla çalışıyordu. Ben değişkenleri ve WHILE döngüsünü kullanarak sorgu yaptım. En çok SQL-y yolu değil ama 30 saniyede iş yaptı. yardımınız için teşekkürler ve özyinelemeli sorgular tho için bir örnek.
katma yazar Alex A., kaynak

Getdate() tarafından sipariş gerçek sipariş verecek

declare @tb table (route  varchar(15),rstart char(1),rfinish char(1),rfrom char(1), rto char(1))
    insert into @tb(route,rstart,rfinish,rfrom,rto) values
    ('route1','A','E','A','B'),
    ('route1','A','E','B','E'),
    ('route2','B','D','B','A'),
    ('route2','B','D','A','E'),
    ('route2','B','D','E','D') 

    select *, row_number() over (partition by route order by getdate()) as rno from @tb`
0
katma
Sadece bu örnekte satırlar doğru sırada. Aslında tamamen karışık geliyorlar. Böylece getdate() maalesef yardımcı olmaz.
katma yazar Alex A., kaynak

Getdate() tarafından sipariş gerçek sipariş verecek

declare @tb table (route  varchar(15),rstart char(1),rfinish char(1),rfrom char(1), rto char(1))
    insert into @tb(route,rstart,rfinish,rfrom,rto) values
    ('route1','A','E','A','B'),
    ('route1','A','E','B','E'),
    ('route2','B','D','B','A'),
    ('route2','B','D','A','E'),
    ('route2','B','D','E','D') 

    select *, row_number() over (partition by route order by getdate()) as rno from @tb`
0
katma
Sadece bu örnekte satırlar doğru sırada. Aslında tamamen karışık geliyorlar. Böylece getdate() maalesef yardımcı olmaz.
katma yazar Alex A., kaynak

Yani Dave'in fikrini kullanarak sonuç:

with routesTree (name, startR, endR, fromR, toR, orderZ)
as
(
    select
        name, startR, endR, fromR,  toR, 1
    from #routes
    where
        startR = fromR
    union all
    select
        R.name, R.startR, R.endR, R.fromR,  R.toR, T.orderZ +1
    from #routes R
    join routesTree T on T.toR = R.fromR and T.name = R.name
)
update r
SET r.orderZ = rt.orderZ
from routesTree rt
JOIN #routes r on r.name = rt.name AND r.fromR = rt.fromR AND r.toR = rt.toR
0
katma

Yani Dave'in fikrini kullanarak sonuç:

with routesTree (name, startR, endR, fromR, toR, orderZ)
as
(
    select
        name, startR, endR, fromR,  toR, 1
    from #routes
    where
        startR = fromR
    union all
    select
        R.name, R.startR, R.endR, R.fromR,  R.toR, T.orderZ +1
    from #routes R
    join routesTree T on T.toR = R.fromR and T.name = R.name
)
update r
SET r.orderZ = rt.orderZ
from routesTree rt
JOIN #routes r on r.name = rt.name AND r.fromR = rt.fromR AND r.toR = rt.toR
0
katma

Benim cevabım gerçek veri kümesi üzerinde öldü (200k yolları, 1.2m satır). Bu yüzden problemimi değişkenler ve WHILE döngüsü ile çözdüm.

DECLARE @cnt INT, @maxroute INT
SET @cnt = 1
SET @maxroute = (
                SELECT MAX(cnt) FROM(
                SELECT COUNT(*) as cnt FROM #routes
                GROUP BY name) as tmp
                )
WHILE @cnt <= @maxroute
BEGIN
    IF @cnt = 1
        BEGIN
            UPDATE r 
            SET r.orderZ = 1
            FROM #routes r
            WHERE r.fromR = r.startR
        END
    ELSE
        BEGIN
            UPDATE r 
            SET r.orderZ = @cnt
            FROM #routes r
            JOIN #routes r_prev
                ON r.name = r_prev.name AND r.orderZ = 0 AND r_prev.orderZ != 0
                AND r.fromR = r_prev.toR
        END
    SET @cnt = @cnt + 1
END
0
katma

Benim cevabım gerçek veri kümesi üzerinde öldü (200k yolları, 1.2m satır). Bu yüzden problemimi değişkenler ve WHILE döngüsü ile çözdüm.

DECLARE @cnt INT, @maxroute INT
SET @cnt = 1
SET @maxroute = (
                SELECT MAX(cnt) FROM(
                SELECT COUNT(*) as cnt FROM #routes
                GROUP BY name) as tmp
                )
WHILE @cnt <= @maxroute
BEGIN
    IF @cnt = 1
        BEGIN
            UPDATE r 
            SET r.orderZ = 1
            FROM #routes r
            WHERE r.fromR = r.startR
        END
    ELSE
        BEGIN
            UPDATE r 
            SET r.orderZ = @cnt
            FROM #routes r
            JOIN #routes r_prev
                ON r.name = r_prev.name AND r.orderZ = 0 AND r_prev.orderZ != 0
                AND r.fromR = r_prev.toR
        END
    SET @cnt = @cnt + 1
END
0
katma