1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

Datum vergleichen über mehrere Tabellen

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von mac2004, 8 Februar 2018.

  1. mac2004

    mac2004 Benutzer

    Hallo zusammen,

    ich habe folgende Herausforderung, die ich nicht gelöst bekomme:

    Ich habe 2 Tabellen, in denen es jeweils ein Start- und Enddatum gibt (inkl. Zeitangabe jj:mm:ss).
    Ich möchte nun zählen wie viele gleichzeitige Aktionen innerhalb dieser Zeitbereiche stattgefunden haben.

    Also wenn in „TabelleA“ ein Startdatum entsteht, soll verglichen werden wie viele „Aktionen“ zu dieser Zeit gleichzeitig laufen (aus TabelleA UND TabelleB). Im Idealfall bis auf die Sekunde genau.

    P.S.
    Im Prinzip geht es darum, zu ermitteln wer gerade angemeldet ist und aktiv etwas macht. Leider zieht sich das über 2 Tabellen, da es unterschiedliche Aktionen sind.
    Ich muss aber alle zusammen als Summe haben.

    Hat jemand eine Idee wie das lösbar ist?

    Danke im Voraus :)
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Deine Beschreibung läßt mehr Fragen offen als sie erklärt:

    • was ist noch in den Tabellen, User? Primary Key? Foreign Keys?
    • was ist mit 'offenen' Bereichen, also wenn es ein Startzeitpunkt gibt, aber kein Endzeitpunkt?
    • was ist mit sich 'überlappenden' Zeiten?
    • soll die Auswertung nur über gerade 'aktive' Zeiten sein (in Tabelle A ein Startzeitpunkt, aber kein Endzeitpunkt) erfolgen oder über alle?
    • was ist mit Zeiträumen innerhalb einer Tabelle, die sich überschneiden? Also z.B. gleicher Tag, einmal von 4 bis 6 und der andere von 5 bis 7?

    Ich hmal mal ganz grob was gebaut, aber mit PostgreSQL (ich hab und kann nur das) und dessen RANGE-Typen, hier TSRANGE, die bereits Operatoren für Prüfung auf Überlappung (&&) haben. Ohne denen bekommt man das sicher auch hin, wird aber komplizierter.

    die Tabellen:
    Code:
    test=# select * from a;
     id | u_id |  von_bis   
    ----+------+-----------------------------------------------
      1 |  1 | ["2018-02-08 02:00:00","2018-02-08 04:00:00")
      2 |  1 | ["2018-02-08 05:00:00","2018-02-08 07:00:00")
      3 |  1 | ["2018-02-08 08:00:00",)
      4 |  2 | ["2018-02-08 06:00:00","2018-02-08 08:00:00")
    (4 Zeilen)
    
    test=*# select * from b;
     id | u_id |  von_bis   
    ----+------+-----------------------------------------------
      1 |  1 | ["2018-02-08 05:30:00","2018-02-08 06:30:00")
      2 |  1 | ["2018-02-08 06:30:00","2018-02-08 07:30:00")
      3 |  2 | ["2018-02-08 03:30:00","2018-02-08 04:30:00")
      4 |  2 | ["2018-02-08 06:30:00","2018-02-08 07:30:00")
    (4 Zeilen)
    
    Was überlappt sich?
    Code:
    test=*# select * from a inner join b on a.u_id=b.u_id and a.von_bis && b.von_bis ;
     id | u_id |  von_bis  | id | u_id |  von_bis   
    ----+------+-----------------------------------------------+----+------+-----------------------------------------------
      2 |  1 | ["2018-02-08 05:00:00","2018-02-08 07:00:00") |  1 |  1 | ["2018-02-08 05:30:00","2018-02-08 06:30:00")
      2 |  1 | ["2018-02-08 05:00:00","2018-02-08 07:00:00") |  2 |  1 | ["2018-02-08 06:30:00","2018-02-08 07:30:00")
      4 |  2 | ["2018-02-08 06:00:00","2018-02-08 08:00:00") |  4 |  2 | ["2018-02-08 06:30:00","2018-02-08 07:30:00")
    (3 Zeilen)
    
    Das jetzt zu zählen etc. ist nun eher trivial.
     
  3. mac2004

    mac2004 Benutzer

    Hallo,
    erstmal danke für deine Antwort. Ich versuche es mal genauer zu beschreiben und deine Fragen zu klären:
    - Die Tabellen enthalten die von dir beschriebenen Werte
    - "Offene" Bereiche gibt es keine (Immer Startzeitpunkt und Ende vorhanden)
    - Start und Endzeit stehen in verschiedenen Spalten (in beiden Tabellen)

    Was soll rauskommen:
    Ich will je Startzeit aus Tabelle A die Summe der Überlappungen zwischen Start und Endzeit der weiteren Einträge, also wo zur gleichen Zeit etwas "aktiv" war. Das aber für Tabelle A und Tabelle B.

    Ich hoffe das ist jetzt verständlicher
    Danke schonmal
     
  4. akretschmer

    akretschmer Datenbank-Guru

    also das hier, oder?

    Code:
    test=*# select a.u_id, a.von_bis, count(b.von_bis) from a inner join b on a.u_id=b.u_id and a.von_bis && b.von_bis group by a.u_id, a.von_bis;
     u_id |  von_bis  | count
    ------+-----------------------------------------------+-------
      1 | ["2018-02-08 05:00:00","2018-02-08 07:00:00") |  2
      2 | ["2018-02-08 06:00:00","2018-02-08 08:00:00") |  1
    (2 Zeilen)
    
    test=*#
    
     
  5. mac2004

    mac2004 Benutzer

    Ich probiere es gleich mal aus....
     
  6. mac2004

    mac2004 Benutzer

    Hallo, ich bekomme das leider einfach nicht hin :-(

    Bei meinen Tabellen steht das Datum jeweils in getrennten Spalten, also so etwa:

    Tabelle 1
    ID, Datum_Start, Datum_Ende, Username

    Tabelle2
    ID, Datum_Start, Datum_Ende, Username

    Ziel:
    Ich möchte für jeden Datensatz zählen, wie viele User (über beide Tabellen) zu selber Zeit aktiv waren.
    Reichen würde mir theoretisch auch der Maximalwert gleichzeitiger "Sitzungen".

    LG Mac
     
  7. akretschmer

    akretschmer Datenbank-Guru

    Nun ja, ich kann das auch mit getrennten Spalten machen:

    Code:
    test=# create table b1 as select id, u_id, lower(von_bis) as von, upper(von_bis) as bis from b;
    SELECT 4
    test=*# create table a1 as select id, u_id, lower(von_bis) as von, upper(von_bis) as bis from a;SELECT 4
    test=*# select * from a1;
     id | u_id |  von  |  bis   
    ----+------+---------------------+---------------------
      1 |  1 | 2018-02-08 02:00:00 | 2018-02-08 04:00:00
      2 |  1 | 2018-02-08 05:00:00 | 2018-02-08 07:00:00
      3 |  1 | 2018-02-08 08:00:00 |
      4 |  2 | 2018-02-08 06:00:00 | 2018-02-08 08:00:00
    (4 Zeilen)
    
    test=*# select * from b1;
     id | u_id |  von  |  bis   
    ----+------+---------------------+---------------------
      1 |  1 | 2018-02-08 05:30:00 | 2018-02-08 06:30:00
      2 |  1 | 2018-02-08 06:30:00 | 2018-02-08 07:30:00
      3 |  2 | 2018-02-08 03:30:00 | 2018-02-08 04:30:00
      4 |  2 | 2018-02-08 06:30:00 | 2018-02-08 07:30:00
    (4 Zeilen)
    
    test=*# select a1.u_id, a1.von, a1.bis, count(tsrange(b1.von,b1.bis,'[)')) from a1 inner join b1 on a1.u_id=b1.u_id and tsrange(a1.von,a1.bis,'[)') && tsrange(b1.von,b1.bis,'[)') group by a1.u_id, a1.von, a1.bis;
     u_id |  von  |  bis  | count
    ------+---------------------+---------------------+-------
      2 | 2018-02-08 06:00:00 | 2018-02-08 08:00:00 |  1
      1 | 2018-02-08 05:00:00 | 2018-02-08 07:00:00 |  2
    (2 Zeilen)
    
    test=*#
    

    Was eher etwas mehr Arbeit ist ist der Vergleich, welche Zeiträume sich überschneiden. Ich bastle mir 'on-the-fly' jetzt wieder meinen TSRANGE zusammen, um dies zu ermitteln. Mach Dich halt schlau, was Deine Datenbank für Operatoren hat, um das zu prüfen. Der SQL-Standard bietet OVERLAPS:

    Code:
    (start1, end1) OVERLAPS (start2, end2)
    (start1, length1) OVERLAPS (start2, length2)
    
    Damit sollte man das mit hinreichend Energie auch hinbekommen. Ich mag halt Range-Typen und deren coole Operatoren, aber nicht alle Datenbanken sind so cool wie PostgreSQL.
     
  8. mac2004

    mac2004 Benutzer

    Vielen Dank abermals.
    Ich versuche das nachher mal abzubilden.
    Leider habe ich keine tiefgreifendere SQL Kenntnisse, um das gleich alles zu verstehen.

    Na, schauen wir mal...
    LG Mac
     
  9. akretschmer

    akretschmer Datenbank-Guru

  10. mac2004

    mac2004 Benutzer

    Ich verstehe ehrlich gesagt die Schwierigkeit nicht, auch wenn ich offenbar zu blöd dazu bin XD

    Das kann Excel mit dem „Summenprodukt“ super schnell lösen, jedoch zu langsam was die Performance angeht.

    Das kann dann doch mittels SQL (Standard) kein so großer Akt sein, oder?!

    Ich habe (leider) kein PostgreSQL :-(
     
  11. akretschmer

    akretschmer Datenbank-Guru

    Ist OpenSource, BSD-Lizenz. Läuft auch unter Windows, besser aber unter Linux.

    Aber es geht auch mit normalem SQL, man muß nur sehen, daß man korrekt die Zeitbereiche miteinander vergleicht. Vernünftige Indexe helfen. Range-Typen verfügen über hocheffiziente GiST-Indexe.
     
  12. ukulele

    ukulele Datenbank-Guru

    Werwirr den armen mac2004 doch nicht so. Es braucht nicht zwingend Range-Datentypen um ein paar Zeiträume zu vergleichen, MSSQL hat es auch nicht.
    Code:
    WITH T(ID,von,bis) AS (
       SELECT   A.ID,A.von,A.bis
       FROM   A
       UNION ALL
       SELECT   B.ID,B.von,B.bis
       FROM   B
       )
    SELECT   A.*,T.*
    FROM   A
    LEFT JOIN T
    ON   (   A.von BETWEEN T.von AND T.bis
    OR     A.bis BETWEEN T.von AND T.bis )
    AND     A.ID != T.ID
    
    Ohne Beispieldatensätze etwas schwer aber ich denke du suchst etwas in der Art.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden