none
Komplexe SQL Recherche auf einfache Tabelle RRS feed

  • Frage

  • Hallo zusammen,

    nachdem ich schon einmal hier eine Superlösung für ein Problem bekommen habe, bitte ich erneut um Hilfe bei einem komplexen Problem:

    In einer Tabelle wird registriert, wann welche 2 Produkte P1 und P2 zu einer Bestellung für eine bestimmte Filiale Fn zusammengestellt werden. Dabei können die beiden Produkte von einem gleichen Hersteller oder von verschiedenen Herstellern Hn stammen. Sie können an unterschiedlichen Orten Ln lagern. Die Spalte "Lager" bezieht sich nur auf die Spalte Produkt 1. Die beiden Produkte werden als Einzelprodukte oder als Produktkombi zusammen gestellt. Bei einem Kombi werden immer 2 Datensätze generiert.

    Das Problem ist nun, herauszufinden, welche Produkte eines Produktkombis an einem gemeinsamen Lagerort (Ort egal) vorkommen, die Kombis anzugeben und die Anzahl für die einzelnen Filialen, sowie die Gesamtanzahl des Kombis zu ermitteln. Ein Produktkombi besteht aus 2 Datensätzen, wobei die beiden Produkte jeweils in der Spalte "Produkt 1" vorkommen müssen und in der Spalte "Datum" höchstens ein Unterschied von 1 Sekunde in beiden Datensätzen vorkommt. Die relevanten Datensätze habe ich in der obigen Tabelle markiert.

    Das Ergebnis sollte wie folgt aussehen:

    Ich hoffe, hier findet sich wieder ein Mega-Spezialist, der mein Problem in einer SQL-Abfrage lösen kann.

    Mit freundlichem Gruß

    Jürgen Sch

    Donnerstag, 4. Oktober 2012 07:35

Antworten

  • Hallo Jürgen,
    anscheinend ist dieses Thema immer noch offen. Damit es die maximale Aufmerksamkeit erhält, wäre es sinnvoll, dieses als neuen Thread zu eröffnen und gleichzeitig den alten (durch Markierung einer Antwort) zu schließen.
    Bitte erstelle dann auch schon mal ein Skript für die Beispieldaten, da dies die Bereitschaft erhöht, sich mit solchen Themen zu beschäftigen.

    Außerdem scheinen mir die Anforderungen nicht ganz klar zu sein.
    Laut Deinen Screenshots zähle ich eine andere Anzahl von Twins, z. B. P1_P2 kommt nur zweimal in F1 vor, oder?

    Auch die umgekehrte Kombination der beiden Produkte in den Spalten Produkt1 und Produkt2 hast Du nicht erwähnt, wäre aber wichtig. Also P1_P2 ist identisch mit P2_P1 auch wenn das erste gebildet wird über P1/P2 Folgesatz P2 und das zweite über P2/P1 Folgesatz P1. Korrekt?

    Wenn der Hersteller keine Rolle spielt, solltest Du ihn auch aus den Beispieldaten weglassen.

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

    • Als Antwort markiert Jürgen Sch Mittwoch, 17. Oktober 2012 17:59
    Mittwoch, 17. Oktober 2012 09:17
    Beantworter

Alle Antworten

  • Hallo Jürgen,

    das könnte man als "Islands-Problem" betrachten. Sätze auf der gleichen Insel gehören zusammen. Die Inseln bilden wir, indem wir die Differenz von der Zeit zum Rang innerhalb des Datasets bilden. Alle Sätze mit der gleichen Differenz liegen auf der gleichen Insel. Dann kann man nach der Differenz gruppieren und nur die Sätze nehmen, bei denen Anfang und Ende des Bereichs ungleich sind, damit keine Sätze gezählt werden, die eigentlich identisch sind. Wir wollen ja nur Kombinationen zählen, oder? Am Ende zählen wir die Sätze in dem fertigen Dataset und geben sie aus.

    Declare @myTab as Table(Datum datetime, Hersteller char(2), Produkt1
    char(2), Lager char(2), Produkt2 char(2), Filiale char(2));
    
    Insert into @myTab(Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale)
    values('2012-02-01 12:00:00', 'H1', 'P1', 'L1', 'P2', 'F1');
    Insert into @myTab(Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale)
    values('2012-02-01 12:06:20', 'H2', 'P2', 'L1', 'P1', 'F1');
    Insert into @myTab(Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale)
    values('2012-02-01 12:06:21', 'H1', 'P1', 'L1', '', 'F1');
    Insert into @myTab(Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale)
    values('2012-02-01 12:06:21', 'H1', 'P1', 'L1', '', 'F3');
    
    Insert into @myTab(Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale)
    values('2012-02-03 16:40:32', 'H1', 'P1', 'L1', 'P2', 'F2');
    Insert into @myTab(Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale)
    values('2012-02-03 16:40:33', 'H2', 'P2', 'L1', '', 'F2');
    Insert into @myTab(Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale)
    values('2012-02-03 20:00:01', 'H4', 'P5', 'L3', '', 'F1');
    
    Insert into @myTab(Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale)
    values('2012-06-04 03:10:08', 'H3', 'P3', 'L2', 'P4', 'F1');
    Insert into @myTab(Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale)
    values('2012-06-04 03:10:09', 'H3', 'P4', 'L2', 'P3', 'F1');
    
    Insert into @myTab(Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale)
    values('2012-02-01 08:00:02', 'H3', 'P3', 'L3', 'P1', 'F2');
    Insert into @myTab(Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale)
    values('2012-02-01 08:00:25', 'H1', 'P1', 'L4', 'P2', 'F1');
    Insert into @myTab(Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale)
    values('2012-02-01 08:00:26', 'H2', 'P2', 'L4', '', 'F1');
    
    With Ausgangsdaten
    as
    (Select Datum, datediff(SECOND,'2012-01-01', datum) as Zeitdifferenz,
    Hersteller, Produkt1, Lager, Produkt2, Filiale
    from @myTab
    ),
    Islands
    as
    (Select Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale,
    Zeitdifferenz,
    DENSE_RANK() OVER(Partition by Filiale ORDER BY Zeitdifferenz) as drnk,
    Zeitdifferenz -    DENSE_RANK() OVER(Partition by Filiale ORDER BY
    Zeitdifferenz) as diff
    from Ausgangsdaten a
    )
    Select *
    from Islands;
    
    With Ausgangsdaten
    as
    (Select Datum, datediff(SECOND,'2012-01-01', datum) as Zeitdifferenz,
    Hersteller, Produkt1, Lager, Produkt2, Filiale
    from @myTab
    ),
    Islands
    as
    (Select Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale,
    Zeitdifferenz,
    DENSE_RANK() OVER(Partition by Filiale ORDER BY Zeitdifferenz) as drnk,
    Zeitdifferenz -    DENSE_RANK() OVER(Partition by Filiale ORDER BY
    Zeitdifferenz) as diff
    from Ausgangsdaten a
    )
    Select min(Produkt1) + '_' + max(Produkt1) as Produktkombi, min(Produkt1) as
    Produkt1, max(Produkt1) as Produkt2, Filiale
    from Islands
    group by diff, Filiale
    having min(Produkt1) <> max(Produkt1)
    order by 1, Filiale;
    
    With Ausgangsdaten
    as
    (Select Datum, datediff(SECOND,'2012-01-01', datum) as Zeitdifferenz,
    Hersteller, Produkt1, Lager, Produkt2, Filiale
    from @myTab
    ),
    Islands
    as
    (Select Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale,
    Zeitdifferenz,
    DENSE_RANK() OVER(Partition by Filiale ORDER BY Zeitdifferenz) as drnk,
    Zeitdifferenz -    DENSE_RANK() OVER(Partition by Filiale ORDER BY
    Zeitdifferenz) as diff
    from Ausgangsdaten a
    ),
    Kombinationen
    as
    (
    Select min(Produkt1) + '_' + max(Produkt1) as Produktkombi, min(Produkt1) as
    Produkt1, max(Produkt1) as Produkt2, Filiale
    from Islands
    group by diff, Filiale
    having min(Produkt1) <> max(Produkt1)
    )
    Select Produktkombi, Produkt1, Produkt2,
    sum(case when Filiale = 'F1' then 1 else 0 end) as F1,
    sum(case when Filiale = 'F2' then 1 else 0 end) as F2,
    count(distinct Filiale) as Anzahl_Filialen, count(*) as Anzahl_Kombi
    from Kombinationen
    group by Produktkombi, Produkt1, Produkt2
    ;

    Mehr davon gibt es in diesem Buch:
    http://www.insidesql.org/blogs/cmu/sql_server/high-performance-tsql-window-functions
     Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    www.insidesql.org/blogs/cmu

    Donnerstag, 4. Oktober 2012 13:19
    Beantworter
  • Oops! Da fehlte ja noch das Lager im Dense-Rank und der Gruppierung.

    With Ausgangsdaten
    as
    (Select Datum, datediff(SECOND,'2012-01-01', datum) as Zeitdifferenz,
    Hersteller, Produkt1, Lager, Produkt2, Filiale
    from @myTab
    ),
    Islands
    as
    (Select Datum, Hersteller, Produkt1, Lager, Produkt2, Filiale,
    Zeitdifferenz,
    DENSE_RANK() OVER(Partition by Lager, Filiale ORDER BY Zeitdifferenz) as drnk,
    Zeitdifferenz -    DENSE_RANK() OVER(Partition by Lager, Filiale ORDER BY
    Zeitdifferenz) as diff
    from Ausgangsdaten a
    ),
    Kombinationen
    as
    (
    Select min(Produkt1) + '_' + max(Produkt1) as Produktkombi, min(Produkt1) as
    Produkt1, max(Produkt1) as Produkt2, Filiale
    from Islands
    group by diff, Lager, Filiale
    having min(Produkt1) <> max(Produkt1)
    )
    Select Produktkombi, Produkt1, Produkt2,
    sum(case when Filiale = 'F1' then 1 else 0 end) as F1,
    sum(case when Filiale = 'F2' then 1 else 0 end) as F2,
    count(distinct Filiale) as Anzahl_Filialen, count(*) as Anzahl_Kombi
    from Kombinationen
    group by Produktkombi, Produkt1, Produkt2
    ;

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


    Donnerstag, 4. Oktober 2012 13:55
    Beantworter
  • Hallo Christoph,

    sehr herzlichen Dank. Das ist genau, was mein Problem löst. Super, Super!!

    Leider sind meine SQL-Kenntnisse nur rudimentär, so dass ich deinem Code nur schwer folgen kann, aber ich werde mich peu a peu da einarbeiten.

    Eine Bitte habe ich noch an dich und ich würde mich freuen, wenn du mir auch dafür eine Lösung geben könntest.

    Ich möchte die Spalte "Datum" nun außer Acht lassen. Ein ProduktKombi soll dann (je Filiale) ausgegeben und gezählt werden, wenn in einer Filiale Fn zwei Produkte in den Spalten "Produkt 1" und "Produkt 2" vorkommen und das Produkt aus Spalte "Produkt 2" zusätzlich in weiteren Datensätzen der selben Filiale in Spalte "Produkt 1" vorkommt. Das wird bei uns Twin genannt.

    Ausgangstabelle:

    Ergebnis:

    Gruß

    Jürgen

    Freitag, 5. Oktober 2012 07:40
  • Hallo Jürgen,
    ich hoffe das hat noch etwas Zeit. Heute habe ich erst mal eine SQL Schulung und danach eine Woche Urlaub! ;-)

    Aber vielleicht findet sich ja noch jemand anders. Ich hätte da so den ein oder anderen im Auge.

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

    Freitag, 5. Oktober 2012 12:21
    Beantworter
  • Hallo Jürgen,
    anscheinend ist dieses Thema immer noch offen. Damit es die maximale Aufmerksamkeit erhält, wäre es sinnvoll, dieses als neuen Thread zu eröffnen und gleichzeitig den alten (durch Markierung einer Antwort) zu schließen.
    Bitte erstelle dann auch schon mal ein Skript für die Beispieldaten, da dies die Bereitschaft erhöht, sich mit solchen Themen zu beschäftigen.

    Außerdem scheinen mir die Anforderungen nicht ganz klar zu sein.
    Laut Deinen Screenshots zähle ich eine andere Anzahl von Twins, z. B. P1_P2 kommt nur zweimal in F1 vor, oder?

    Auch die umgekehrte Kombination der beiden Produkte in den Spalten Produkt1 und Produkt2 hast Du nicht erwähnt, wäre aber wichtig. Also P1_P2 ist identisch mit P2_P1 auch wenn das erste gebildet wird über P1/P2 Folgesatz P2 und das zweite über P2/P1 Folgesatz P1. Korrekt?

    Wenn der Hersteller keine Rolle spielt, solltest Du ihn auch aus den Beispieldaten weglassen.

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

    • Als Antwort markiert Jürgen Sch Mittwoch, 17. Oktober 2012 17:59
    Mittwoch, 17. Oktober 2012 09:17
    Beantworter