SQL Abfrage mit Ticketverkettung

Question

Hallo liebe Gemeinschaft,

ich habe folgendes Problem. Ich soll in einer bestehenden Tabelle mit vorhandener Datenausprägung das leere Feld „Tages_Reise „ füllen.

Die Anforderungen sind folgendermaßen definiert:

Ein Ticket besteht aus einer eindeutigen ID und hat mehrere UnterTickets bzw. Fahrkarten. Ich soll nun eine zusammenhängende Tagesreise definieren wo der Passagier nur innerhalb eines Bahnhofs umsteigt. Die Tages Reise wird nur innerhalb eines Tickets definiert.

Erklärung am Beispiel Ticket 1:

Max Mustermann hat ein Ticket mit 5 Fahrscheinen, auf 3 Tage verteilt. Er fährt von Berlin nach Hamburg,von  Hamburg nach München und von Hamburg nach OderHavelLand an einem Tag.

Also wäre die Tages Reise für SUB-Tickets bzw.Fahrscheine (1,2,3) =  B – HH – M – OHV

Für das SUB-Tickets 4 = OHV – HA

Für das SUB-Ticket 5 = AT – BAR

Eines Tagesreise wird also nur dann verkettet wenn ZielBahnhof = neuer StartBahnhof ist und die Reise an einem Tag stattfindet.

Ist das transparent erklärt? Wenn ja könnt ihr mit bitte weiterhelfen wie hier die Herangehensweise wäre?

Datenausprägung Beispiel:

IF Object_id ('TEMPDB.DBO.#TEST', 'U') IS NOT NULL
  DROP TABLE #test;

CREATE TABLE [DBO].[#test]
  (
     [Ticket_id]        BIGINT NULL,
     [Ticket_sub_id]    TINYINT NULL,
     [Abfahrtdatum]     DATE NULL,
     [Start_Bahnhof]    VARCHAR(15),
     [Ziel_Bahnhof]     VARCHAR(15),
     [Tages_Reise]      VARCHAR(MAX)
    
  )
--Ticket 1
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (1, 1, '2012-11-6', 'B', 'HH' )
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (1, 2, '2012-11-6', 'HH', 'M' )
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (1, 3, '2012-11-6', 'HA', 'OHV')
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (1, 4, '2012-11-7', 'OHV', 'HA')
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (1, 5, '2012-11-8', 'AT', 'BAR')

--Ticket 2
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (2, 1, '2012-10-6', 'B', 'SF' )
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (2, 2, '2012-10-6', 'HH', 'M' )
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (2, 3, '2012-10-6', 'M', 'OHV')
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (2, 4, '2012-11-7', 'KK', 'HA')
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (2, 5, '2012-11-7', 'HA', 'BAR')

--Ticket 3
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (3, 1, '2012-10-6', 'B', 'SF' )
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (3, 2, '2012-10-7', 'SF', 'M' )

--Ticket 4
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (4, 1, '2012-10-6', 'B', 'SF' )
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (4, 2, '2012-10-6', 'SF', 'M' )
INSERT INTO [DBO].[#test]([Ticket_id] , [Ticket_sub_id], [Abfahrtdatum], [Start_Bahnhof], [Ziel_Bahnhof])
VALUES     (4, 3, '2012-10-6', 'M', 'OHV')

gewünschtes Ergebnis:

Ticket_id		Ticket_sub_id		Abfahrtdatum		Start_Bahnhof	Ziel_Bahnhof	Tages_Reise
1		1		06.11.2012		B	HH	B-HH-M
1		2		06.11.2012		HH	M	B-HH-M
1		3		06.11.2012		HA	OHV	B-HH-M
1		4		07.11.2012		OHV	HA	OHV-HA
1		5		08.11.2012		AT	BAR	AT-BAR
2		1		06.10.2012		B	SF	B-SF
2		2		06.10.2012		HH	M	HH-M-OHV
2		3		06.10.2012		M	OHV	HH-M-OHV
2		4		07.11.2012		KK	HA	KK-HA-BAR
2		5		07.11.2012		HA	BAR	KK-HA-BAR
3		1		06.10.2012		B	SF	B-SF
3		2		07.10.2012		SF	M	SF-M
4		1		06.10.2012		B	SF	B-SF-M-OHV
4		2		06.10.2012		SF	M	B-SF-M-OHV
4		3		06.10.2012		M	OHV	B-SF-M-OHV

Answer 1

Hallo Franzl

Hier liegt ein rekursives Problem vor, d.h. Du wirst die Lösung am Besten mit einer rekursiven CTE erreichen. Gib mir 10min und ich füge entsprechenden Code an.

Mit freundlichem Gruß

 Alexander

Answer 2

Das hört sich super an :) Vielen Dank für deine Mühe

Answer 3

So, hier ist eine Lösung:

;WITH TagesReise1 AS (
    SELECT
        T1.Ticket_ID
        ,T1.Ticket_sub_ID
        ,cast(T1.Start_Bahnhof + '-' + T1.Ziel_Bahnhof + '-' + T2.Ziel_Bahnhof AS varchar(8000)) AS Reise
        ,T2.Start_Bahnhof
        ,T2.Ziel_Bahnhof
        ,T1.Abfahrtdatum
    FROM
        #test T1
        INNER JOIN
        #test T2
            ON T1.Ziel_Bahnhof = T2.Start_Bahnhof
            AND T1.Ticket_ID = T2.Ticket_ID
            AND T1.Abfahrtdatum = T2.Abfahrtdatum
    UNION ALL
    SELECT
        T1.Ticket_ID
        ,T2.Ticket_sub_ID
        ,T1.Reise + '-' + T2.Ziel_Bahnhof AS Reise
        ,T2.Start_Bahnhof
        ,T2.Ziel_Bahnhof
        ,T1.Abfahrtdatum
    FROM
        TagesReise1 T1
        INNER JOIN
        #test T2
            ON T1.Ziel_Bahnhof = T2.Start_Bahnhof
            AND T1.Ticket_ID = T2.Ticket_ID
            AND T1.Abfahrtdatum = T2.Abfahrtdatum            
), TagesReise2 AS (
    SELECT
        row_number() OVER(PARTITION BY Ticket_ID, Abfahrtdatum ORDER BY Ticket_sub_ID DESC) AS Rang,
        *
    FROM
        TagesReise1
)
UPDATE T1 SET
    T1.Tages_Reise = T2.Reise
FROM
    #test T1
    INNER JOIN
    TagesReise2 T2
        ON T1.Ticket_ID = T2.Ticket_ID
        AND T1.Abfahrtdatum = T2.Abfahrtdatum
        AND T2.Rang = 1
        AND CHARINDEX(T1.Start_Bahnhof + '-' + T1.Ziel_Bahnhof, T2.Reise) > 0


UPDATE #test SET
    Tages_Reise = Start_Bahnhof + '-' + Ziel_Bahnhof
WHERE
    Tages_Reise IS NULL


SELECT * FROM #test

Du musst aber prüfen, ob sie tatsächlich Deinen Anforderungen entspricht.

Ich bin mir sicher, dass man hier noch etwas eleganter ans Ziel kommt, allerdings fehlt mir momentan die Zeit dafür.

Trotzdem hoffe ich, dass dieser Code Dir etwas weiterhilft!

Mit freundlichem Gruß

 Alexander

P.S.: Bei Deinem Beispiels stimmt etwas nicht. Die Beschreibung passt nicht zu den Daten. Ich habe einfach angenommen, dass bei Ticket_id = 1 und Ticket_sub_ID = 3 ein M und kein HA hingehört.

Answer 4

Auweiha, ja im Beispiel war ein Fehler.

Ich bin begeistert von deinem Statement. Ich versuche es erst einmal zu verstehen und würde dann eventuelll nochmal Fragen stellen.

Jedenfalls ist das Resultat genau das, was ich sehen wollte.

Vielen Dank und Hut ab!

Liebe Grüße,

Franz

Answer 5

Hallo Alexander,

ich habe mich mal in dieses CTE mit rekursiven Abfragen eingelesen. Dein Beispiel hat mir sehr geholfen!

Vielen vielen Dank!

Liebe Grüße,

Franz

Answer 6

Hallo

Es freut mich, wenn ich helfen konnte!

Mit freundlichem Gruß

 Alexander

Answer 7

Hallo Franz,
diese klitzekleine Info über die Datenmenge hättest Du wahrscheinlich am besten vorher gegeben.

Bei 200.000.000 Datensätzen würde ich das nicht unbedingt in einer Transaktion durchführen, sondern über einzelne Partitionen der Tabelle gehen. Weiterhin würde ich wohl keine rekursive CTE verwenden, da diese ja temporäre Ergebnisse aufbaut, die evtl. nicht mehr in den Speicher passen, und auf Platte ausgelagert werden. Weiterhin werden diese temporären Zwischenergebnisse wahrscheinlich kaum so indiziert sein wie Du das für die Datenmenge brauchst. Die angebotene Lösung war schick und ohne Kenntnis der Datenmenge auch legitim, aber sie passt jetzt wohl nicht mehr ganz zum Problem.

Wir hatten hier im Forum schon mal eine ähnliche Aufgabenstellung, wo es um die Routenfindung ging. Dazu habe ich eine Beschreibung hier gepostet:
http://www.insidesql.org/blogs/cmu/sql_server/routenfindung-in-graphen

Man sollte sich überlegen, ob man nicht in dieser Art etwas machen könnte ...
 Verrate auf jeden Fall noch mal etwas zum System und zur eingesetzten Version des SQL Servers.

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu