Datum vergleichen über mehrere Tabellen

mac2004

Benutzer
Beiträge
6
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 :)
 
Werbung:
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.
 
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
 
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=*#
 
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
 
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.
 
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
 
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 :-(
 
Ich habe (leider) kein PostgreSQL :-(

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.
 
Werbung:
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.
 
Zurück
Oben