none
Lösungsweg für eine SQL-Abfrage gesucht. RRS feed

  • Frage

  • Hallo,

    Gesucht ist eine Abfrage, bei dem die Anzahl der am meisten gleichseitigen Zugriff und deren Zeitpunkt zurückgeben wird.

    In der Tabelle gespeichert ist Startzeit und Stopzeit mit dem Datentyp Datetime.

    Create Table [StatistikTest] (
    [ID] [int] IDENTITY(1,1) not null,
    [Startzeit] [datetime] NOT NULL,
    [Stopzeit] [datetime] NOT NULL)

    INSERT INTO [StatistikTest] ([Startzeit],[Stopzeit]) values ('2012-01-01 10:10:00','2012-01-01 11:10:00') *
    INSERT INTO [StatistikTest] ([Startzeit],[Stopzeit]) values ('2012-01-01 11:10:01','2012-01-01 12:10:00')
    INSERT INTO [StatistikTest] ([Startzeit],[Stopzeit]) values ('2012-01-01 10:15:00','2012-01-01 10:30:00') *
    INSERT INTO [StatistikTest] ([Startzeit],[Stopzeit]) values ('2012-02-01 10:10:00','2012-01-01 10:10:00')
    INSERT INTO [StatistikTest] ([Startzeit],[Stopzeit]) values ('2012-01-01 10:29:00','2012-01-01 10:32:00') *
    INSERT INTO [StatistikTest] ([Startzeit],[Stopzeit]) values ('2012-03-01 23:10:00','2012-03-02 10:10:00')

    Als Ergebnis sollte bei diesen Beispiel der Wert 3 für Max. gleichseitige Zugriff und 2012-01-01 10:30:00 als Zeitpunkt kommen.

    Wie wäre der Lösungsweg, um zu diesen Ergebnis zu kommen?

    Gruß
    Herbert


    • Bearbeitet Billardheld Mittwoch, 26. September 2012 04:59 Tippfehler
    Dienstag, 25. September 2012 13:43

Antworten

  • Hallo Herbert,

    1. es heist VALUES, nicht VALUE bei den Insert Statements.
    2. ein paar Werte erscheinen mir unlogisch; Start am 01.03.12, Stop am 01.01.2012?

    Wenn ich es richtig sehe, möchtest Du als Ergebnis bzw. als Kriterium die vorhandene Datumswerte verwenden? Dann sollte Dir dies hier evtl helfen:

    WITH cte AS
        (SELECT DISTINCT Startzeit AS Zeit FROM [StatistikTest]
         UNION 
         SELECT DISTINCT Stopzeit AS Zeit FROM [StatistikTest])     
    SELECT cte.zeit, COUNT(*) AS Anz
    FROM cte
         INNER JOIN 
         [StatistikTest] AS DST
             ON DST.Startzeit <= cte.Zeit
                AND cte.Zeit <= DST.Stopzeit
    GROUP BY cte.zeit
    HAVING COUNT(*) >= 3


    Olaf Helper

    Blog Xing

    • Als Antwort markiert Billardheld Mittwoch, 26. September 2012 07:58
    Dienstag, 25. September 2012 14:01

Alle Antworten

  • Hallo Herbert,

    1. es heist VALUES, nicht VALUE bei den Insert Statements.
    2. ein paar Werte erscheinen mir unlogisch; Start am 01.03.12, Stop am 01.01.2012?

    Wenn ich es richtig sehe, möchtest Du als Ergebnis bzw. als Kriterium die vorhandene Datumswerte verwenden? Dann sollte Dir dies hier evtl helfen:

    WITH cte AS
        (SELECT DISTINCT Startzeit AS Zeit FROM [StatistikTest]
         UNION 
         SELECT DISTINCT Stopzeit AS Zeit FROM [StatistikTest])     
    SELECT cte.zeit, COUNT(*) AS Anz
    FROM cte
         INNER JOIN 
         [StatistikTest] AS DST
             ON DST.Startzeit <= cte.Zeit
                AND cte.Zeit <= DST.Stopzeit
    GROUP BY cte.zeit
    HAVING COUNT(*) >= 3


    Olaf Helper

    Blog Xing

    • Als Antwort markiert Billardheld Mittwoch, 26. September 2012 07:58
    Dienstag, 25. September 2012 14:01
  • Hi,

    eine Lösung, mit der Du ggf. Zusatzinfos ermitteln könntest...

    VG

       Workaholic63

    select top 1 x.id, COUNT(*) as Ueberlappungen, MAX(start) MaxIntervallStart, MIN(stop) as MinIntervallStop,  (convert(numeric(18,10), MIN(stop)) - convert(numeric(18,10),MAX(start))) * 24 * 60 as MinutenOverlapp
    from (
    select st1.ID, case when st1.Startzeit >= st2.Startzeit then st1.Startzeit else st2.Startzeit end as Start,
            case when st1.Stopzeit <= st2.Stopzeit then st1.Stopzeit else st2.Stopzeit end as Stop,
            (convert(numeric(18,10), case when st1.Stopzeit <= st2.Stopzeit then st1.Stopzeit else st2.Stopzeit end) - convert(numeric(18,10), case when st1.Startzeit >= st2.Startzeit then st1.Startzeit else st2.Startzeit end)) * 24 * 60 as Dauer
    from StatistikTest st1
    inner join StatistikTest st2 on st1.id <= st2.id and (st1.Startzeit < st2.stopzeit and st1.Stopzeit > st2.Startzeit)
    ) x
    group by x.id
    order by 2 desc

    • Als Antwort vorgeschlagen Workaholic63 Dienstag, 25. September 2012 16:45
    • Nicht als Antwort vorgeschlagen Billardheld Mittwoch, 26. September 2012 04:53
    Dienstag, 25. September 2012 16:45
  • @Olaf:

    zu Pkt. 1. und 2. stimmt ist ein Tippfehler, ist zwischenzeitlich korrigiert...Danke

    Deine Lösung, die zum richtigen Ergebnis führt, dauert bei ca. 45.000 Datensätze mehr als 2 Min. Wie könnte man das noch optimieren?

    @Workaholic63:

    auch Deine Lösung führt zum Ergebnis, aber liefert bei den ca. 45.000 Datensätze (ca. 30sek.) leider falsche Ergebnisse. Warum das so ist, muss ich mir mal schauen!?

    Danke euch beiden für die Vorschläge, super.



    • Bearbeitet Billardheld Mittwoch, 26. September 2012 06:05
    Mittwoch, 26. September 2012 05:13
  • Wie könnte man das noch optimieren?

    Hallo Herbert,

    hast Du Dir schon den Ausführungsplan der Abfrage angesehen, ob Indizes verwendet werden (sofern überhaupt welche vorhanden)?

    Ein Hauptproblem ist hierbei halt, das zum Ermitteln der potentiellen Datumswerte (die CTE) zweimal ein Full Table Scan über die Tabelle läuft. Hier wäre es besser, sich eine (temporäre) Tabelle mit den paar Datumswerte zu erstellen, die für die aktuelle Auswertung von Interesse sind. Wenn es Werte im Halbstunden Takt sind, kommst Du für einen Monat auf max 1.488 Wert, statt 45 Tsd.

    Alternative kannst Du auch dynamisch per rekursiver CTE die Werte generieren:

    DECLARE @startTime datetime, @endTime datetime;
    SET @startTime = {d N'2012-08-01'}
    SET @endTime = {d N'2012-09-01'}
    
    ;WITH times AS
        (SELECT @startTime AS Zeit
         UNION ALL
         SELECT DATEADD(minute, 30, Zeit) AS Zeit
         FROM times
         WHERE times.Zeit < @endTime)
    SELECT *
    FROM times
    OPTION (MAXRECURSION 1489)


    Olaf Helper

    Blog Xing

    Mittwoch, 26. September 2012 06:08
  • Hallo Olaf,

    richtig, mit Indizes auf die Spalte [Startzeit] und [Stopzeit] sehr viel schneller. Mit einer temporäre Tabelle, wurde sich die Zeit wieder verdoppelt - bringt in sofern nichts, wenn über alle Daten gefragt wird - Indizes reicht.

    Unter umständen kann eine Temporäre Tabelle durchaus schneller sein, es kommt auf die Datenmenge an.

    Da nur die 1 Zeile für mich interesant ist, habe ich den Code etwas geändert.

    WITH cte AS
        (SELECT DISTINCT Startzeit AS Zeit FROM [StatistikTest]
         UNION 
         SELECT DISTINCT Stopzeit AS Zeit FROM [StatistikTest])     
    SELECT TOP 1 cte.zeit, COUNT(*) AS Anz
    FROM cte
         INNER JOIN 
         [StatistikTest] AS DST
             ON DST.Startzeit <= cte.Zeit
                AND cte.Zeit <= DST.Stopzeit
    GROUP BY cte.zeit
    HAVING COUNT(*) >= 1
    order by 2 desc, 1 desc

    Übrigens, die Werte sind im Sekundentakt.

    Gruß
    Herbert





    • Bearbeitet Billardheld Donnerstag, 27. September 2012 05:58
    Mittwoch, 26. September 2012 08:18