komplizierte Abfrage auf ein Zeitfenster

Question

Hallo zusammen,

wir grübeln bei uns im Büro über einen Ansatz für ein Select-Statement - mittlerweile hat sich zumindest bei mir das sprichwörtliche "Brett vor dem Kopf" eingestellt und es geht nichts mehr... vielleicht kann ja jemand aus dem Forum helfen und den richtigen Denkanstoß beitragen... vielen Dank!

Gegeben ist eine Bewegt-Daten-Tabelle des MS Sql Servers 2005 mit einpaar Mio. Datensätzen. U.a. sind die Felder "Buchungsdatum" (DateTime, Datum) und "Kundennummer" (String) enthalten.

Das zu untersuchende Zeitfenster ist definiert als zusammenhängende (aber innerhalb des Datenbestands beliebige) Periode von genau 72 Stunden ohne Datumsgrenzenbindung, gilt also bspw. auch von 07.04.2010 16.00 Uhr einschließlich bis 10.04.2010 16:00 Uhr ausschließlich.

Gesucht sind nun alle Kundennummern, die mindestens ein Zeitfenster mit mehr als 3 Buchungen in einem dieser Zeitfenster vorweisen können. 

Hat dafür jemand eine Idee oder sogar ein Code-Schnipsel? Vielen Dank!

Answer 1

Hallo.

mit DateAdd kann man auf ein Datum Werte addieren, wie eben +72h. Mit Between kann man den Zeitbereich einschränken. Mit Count und Group by kann man je Kundennummern die Bewegungen zählen, mit Having kann man auf eine Mindestanzahl einschränken. Das ein wenig kombiniert sollte das Ergebnis liefern.

DECLARE @start datetime

SET @start = {ts N'2010-04-07 16:00:00.000'}

 

SELECT COUNT(*)

      ,Kundennummer

FROM Buchungsdatum

WHERE Buchungsdatum BETWEEN @start

                    AND DATEADD(hh, @start, 72)

GROUP BY Kundennummer

HAVING COUNT(*) > 3

 

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 2

Servus Leute,

der folgende Code müsste direkt funzen bei euch. (Feldnamen und Tabellennamen entsprechend ändern)

 

SELECT     AufNr, COUNT(AufNr) AS Anz
FROM         Tab01
WHERE     (AufDatum < CONVERT(DATETIME, '2007-09-02 03:00:03', 102)) AND (AufDatum > CONVERT(DATETIME, '2007-09-01 05:15:04', 102))
GROUP BY Aufnr
HAVING      (COUNT(AufNr) > 3)

 

gute zeit micki

Answer 3

Vielen Dank für die prompte Antwort!

Leider löst das mein Problem nicht ganz... der oben stehende Lösungsvorschlag setzt einen konkreten Startwert voraus - ich benötige aber ALLE Kundennummern, die IRGENDWO im Datenbestand ein solches Zeitfenster zu den geschilderten Bedingungen haben und dort mindestens drei Buchungen vorliegen haben. Es handelts ich also um eine Art "moving target"...

Den obigen Lösungsvorschlag müsste man also in eine Art Schleife einbauen und alle in Frage kommenden Datumswerte einzeln testen...

 

Answer 4

Ein Cursor sollte immer die allerletzte Wahl sein. Mit etwas Fantasie und logischem Denken bekommt man meistens auch eine mengenbasierte Lösung hin; so auch hier und die ist auch nicht kompliziert. Man selektiert alle vorhandenen Datumswerte und joint damit; der Rest ist wie zuvor. Bei den Datenmenge kann es aber eine Weile Laufen ...

Wenn nur die eindeutigen Kundennummern benötigt werden, müsste man die dann noch mal filtern.

WITH dates (Buchungsdatum)

AS (SELECT DISTINCT Buchungsdatum

    FROM Buchungen)

 

SELECT COUNT(*) AS Anzahl

      ,BU.Kundennummer

      ,dates.Buchungsdatum

FROM dates

     INNER JOIN

     Buchungen AS BU

         ON BU.Buchungsdatum BETWEEN

            dates.Buchungsdatum

            AND DATEADD(hh, 72, dates.Buchungsdatum)

GROUP BY BU.Kundennummer

        ,dates.Buchungsdatum

HAVING COUNT(*) > 3

 

 

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 5

Olaf war ein bißchen schneller. ;)

Wenn ich das richtig verstanden habe, reichen Dir jeweils die vollen Stunden als Begrenzung des Zeitfensters?
In dem Fall würde ich den Distinct nicht auf das gesamte Buchungsfatum, sondern nur die vollen Stunden nehmen.

Answer 6

Hallo Ahle,

wenn ich das richtig verstehe, wären das bei angenommen 3 Mil Datensätze ungefähr (2^3000000-1)/3 Möglichkeiten.

Wenn du das in eine Procedur mit Schleife packst, wird niemand in deinem Büro so alt um das Ergebnis zu sehen.

Aber die Abfrage sieht wie folgt aus:

SELECT     testtab01.MaterialNr, COUNT(testtab01.MaterialNr) AS Expr1
FROM         testtab01 INNER JOIN
                      testtab01 AS testtab01_1 ON testtab01.MaterialNr = testtab01_1.MaterialNr AND testtab01.ID <> testtab01_1.ID AND
                      testtab01.EDatum < testtab01_1.EDatum AND testtab01_1.EDatum < DATEADD(hh, 72, testtab01.EDatum)
GROUP BY testtab01.MaterialNr
HAVING      (COUNT(testtab01.MaterialNr) > 2)

Erklärung:

Testtab01 und testtab01_1 sind die selbe Tabelle

Feld: MaterialNR ist bei dir die Kundennummer

Feld:EDatum dein Datumsfeld

Feld: ID muss ein eindeutiger Index sein

ALLES KLAR ?

gruß micki

 

 

Answer 7

Hallo Olaf,

vielen Dank, das ist genau der Denkanstoß, den ich gesucht habe.

Respekt - ich hatte mir schon gedacht, dass es eine kurze und schleifenlose Variante geben müsste, dass sie so einfach und selbsterklärend ist, hatte ich mir nicht vorgestellt.

Auch den anderen Unterstützern ein herzlichen Dankeschön, hoffentlich kann ich mich bei Gelegenheit mal revangieren.

Answer 8

Nochmal... da hat der Fehlerteufel zugeschlagen die Abfrage muss wie folgt lauten

 

SELECT     testtab01.MaterialNr, COUNT(testtab01.MaterialNr) AS Expr1, testtab01.EDatum
FROM         testtab01 INNER JOIN
                      testtab01 AS testtab01_1 ON testtab01.MaterialNr = testtab01_1.MaterialNr AND testtab01.ID <> testtab01_1.ID AND
                      testtab01.EDatum < testtab01_1.EDatum AND testtab01_1.EDatum < DATEADD(hh, 72, testtab01.EDatum)
GROUP BY testtab01.MaterialNr, testtab01.EDatum
HAVING      (COUNT(testtab01.MaterialNr) > 2)

 

gruß micki