Zählen von Datensätzen im überlappenden Zeitraum

[Muschelpuster]

Moin zusammen,

ich habe eine Tabelle mit Angaben von Zeiträumen, also primär für mich wichtig sind eine Start- und eine Endzeit. Zum Testen habe ich mir einfach mal eine Tabelle mit 3 Spalten erstellt:

• ID (int)
• Start (int)
• Ende (int)

Start und Ende fülle ich nun erst einmal nur mit Zahlen. Nun möchte ich pro Zeile wissen, wie viele Datensätze es gibt, die auch irgendwie in diesen Zeitraum liegen. Meine erster Test scheitert schon:

select T1.ID as ID1, T1.Start, T1.Ende, count(T2.ID) as Zaehler from trunk as T1 join trunk as T2 on T2.`Start` >= T1.`Start`;

Egal wie ich das Join gestalte, ich bekomme von meinen 3 Testzeilen nur eine zurück und der Count ist 6. Und dabei habe ich noch nicht einmal die Endzeit in's Spiel gebracht. Ist Join hier überhaupt der richtige Ansatz?

Niels

 

[Kampfgummibaerlie]

Also, ich denke es ist bereits falsch, die Spalten Start und Ende als Integer zu definieren

ID als int ist auch gut so, die anderen beiden sind jedoch eher mit dem Spaltentypen "Time", "Timestamp" oder "Date" versehen

Ich bin eigentlich ein PostgreSQL-Nutzer, wo vieles für so einen Zweck einfacher ist

Aber ich denke du brauchst den Befehl "Where"

Wenn du ein wenig mehr Informationen über deine Datenbank gibst, könnte (ich zumindest) dir besser helfen

 

[akretschmer]

bin mir nicht sicher, was genau du suchst. Ob sich da Bereiche überschneiden? Mal als Fingerübung, mit PostgreSQL:

edb=*> select * from muschelpuster;
 id | von_bis
----+---------
  1 | [1,5)
  2 | [2,4)
  3 | [3,7)
  4 | [8,17)
(4 rows)

edb=*> select *, case when m1.von_bis && m2.von_bis then 1 else 0 end as ueberlappen_sich from muschelpuster m1 cross join muschelpuster m2 where m1.id != m2.id and m1.id < m2.id;
 id | von_bis | id | von_bis | ueberlappen_sich
----+---------+----+---------+------------------
  1 | [1,5)   |  2 | [2,4)   |                1
  1 | [1,5)   |  3 | [3,7)   |                1
  1 | [1,5)   |  4 | [8,17)  |                0
  2 | [2,4)   |  3 | [3,7)   |                1
  2 | [2,4)   |  4 | [8,17)  |                0
  3 | [3,7)   |  4 | [8,17)  |                0
(6 rows)

Paßt das soweit?

 

[akretschmer]

Also, ich denke es ist bereits falsch, die Spalten Start und Ende als Integer zu definieren

Sicher, aber a) neigen MySQL-User immer wieder zu diesem Fehler und b) soll das hier eher zur Vereinfachung dienen.

 

[Muschelpuster]

Danke Euch so weit.
Natürlich ist das nur eine DB zum Spielen. Ich mache sicher genug Fehler, aber solche dann doch nicht ;-)
Am Ende sind es Spalten mit Zeitwerten, aber da ist so viel drin, dass ich erst einmal im Sandkasten spiele.

Angelehnt an obiges Beispiel habe folgende Tabelle:

ID   | Start   | Ende
-----+---------+---------
1    | 1       | 5
2    | 2       | 4
3    | 3       | 7
4    | 8       | 17
5    | 6       | 7

Das Ergebnis soll so aussehen:

ID   | Start   | Ende    | Count
-----+---------+---------+---------
1    | 1       | 5       | 3
2    | 2       | 4       | 2
3    | 5       | 7       | 2
4    | 8       | 17      | 1
5    | 6       | 7       | 2

Es soll am Ende zu einem Chart führen, wie viel Vorgänge gleichzeitig aktiv waren. Da sich außerhalb der Zeitpunkte von Start und Ende der Datensätze ja nichts ändern kann ergeben diese die optimalen Datenpunkte. Nun ist in diesem vereinfachten Beispiel natürlich fraglich, ob die Zeiträume 1-2 und 2-3 am Zeitpunkt 2 mit 2 gezählt werden sollen oder eher mit 1. In der Praxis gibt es dann ja viel mehr Datenpunkte und hochgradig selten exakt gleiche Zeitstempel. Von daher sollte das am Ende egal sein.

Niels

 

[akretschmer]

da scheinen einige Dinge nicht zu passen. Mal angenommen, die Ende-Grenzen zählen mit. Für id=3, also 3 bis 7, haben wir id 1,2,3,5, also 4. Du hast auch im Ergebniss für diesen Datensatz einen falsche Start-Wert. So wäre es richtig (ich nutze int4-ranges und habe Ende immer +1 gemacht, damit das damit inklusive wird):

edb=*> select * from muschelpuster;
 id | von_bis
----+---------
  8 | [1,6)
  9 | [2,5)
 10 | [3,8)
 11 | [8,18)
 12 | [6,8)
(5 rows)

edb=*> select *, (select count(1) from muschelpuster where von_bis && m.von_bis) from muschelpuster m;
 id | von_bis | count
----+---------+-------
  8 | [1,6)   |     3
  9 | [2,5)   |     3
 10 | [3,8)   |     4
 11 | [8,18)  |     1
 12 | [6,8)   |     2
(5 rows)

edb=*>

sorry, meine ID's sind etwas höher, weil ich die nochmals eingetragen habe, um auf Deine Werte zu kommen.

 

[Muschelpuster]

Danke, das sieht cool aus. Ich habe bislang nur die Start-Grenzen betrachtet, da die komplette Betrachtung noch komplett außerhalb meines Horizontes lag. Ich glaube den Feldtyp Range gibt es bei mySQL nicht. Es gibt ein Range-Query was ich aber nach einem groben Studium der Doc als nicht passend verworfen hatte.
mySQL findet eine vereinfachte Abfrage nach obigen Beispiel noch uncool:

select * , (select count(t2.id) from test as t2 where (t2.start > start and t2.start < ende)) as Z from  test;

Das liefert für alle Zeilen Z=0.

Ich habe gerade 2 Vorteile:

1. Die Struktur der DB kann ich noch anpassen, denn aktuell bekomme ich die Daten nur als HTTP-Requests angeliefert und presse die über node-red in die DB. Da ist natürlich erst einmal der 1. faule Gedanke diese so zu übernehmen, wie sie angeliefert werden. Aber natürlich kann ich auch im node-red schon die Daten für die anstehenden Auswertungen aufbereiten.
2. Ich bin recht frei in der Wahl der DB-Engine. MySQL ist nur meine erste Wahl, da ich da etwas Erfahrung mit habe. Aber außer noch weniger Erfahrung wie mit mySQL spricht nichts dagegen PostgreSQL zu nehmen. Der Feldtyp Range spricht auf jeden Fall dafür ;-)

Niels

 

[akretschmer]

ja, die RANGE-Datentypen sind eines von unzähligen coolen Features in PG ...

 

[Muschelpuster]

Ich überlege gerade ob ich nicht 2 Tabellen schreiben sollte. In der 2. Tabelle stelle ich mir nur eine Spalte 'Messpunkte' vor und würde pro Datensatz 2 Zeilen schreiben (Anfangs- und Endzeit). Dann könnte ich doch auf dieser Tabelle für meinen gewünschten Auswertezeitraum die relevanten Messpunkte lesen und den Counter aus der anderen Tabelle pro Messpunkt dazu ermitteln.
Das macht doch Sinn, oder?

Niels

 

[Muschelpuster]

Ich habe jetzt mal was Neues gebaut:

CREATE TABLE IF NOT EXISTS `Daten` (
  `D_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `D_Start` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `D_Ende` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`D_ID`)

Die Inhalte sehen wie folgt aus:

select * from Daten;
D_ID | D_Start             | D_Ende
-----+---------------------+---------------------
   1 | 2021-03-04 07:00:00 | 2021-03-04 09:00:00
   2 | 2021-03-04 07:30:00 | 2021-03-04 08:00:00
   3 | 2021-03-04 07:00:00 | 2021-03-04 07:10:00
   4 | 2021-03-04 08:10:00 | 2021-03-04 10:10:00
 
select * from Messpunkte;
D_ID | M_Zeit
-----+---------------------
   1 | 2021-03-04 07:00:00
   2 | 2021-03-04 09:00:00
   3 | 2021-03-04 07:30:00
   4 | 2021-03-04 07:00:00
   5 | 2021-03-04 07:10:00
   6 | 2021-03-04 10:00:00
   7 | 2021-03-04 08:10:00
   8 | 2021-03-04 10:10:00

Manuelle Testabfragen liefern die gewünschten Werte:

select count(D.D_ID) from Daten as D where (D.D_Start <= '2021-03-04 07:00:00' and D.D_Ende > '2021-03-04 07:00:00');
>1
select count(D.D_ID) from Daten as D where (D.D_Start <= '2021-03-04 09:00:00' and D.D_Ende > '2021-03-04 09:00:00');
>1
select count(D.D_ID) from Daten as D where (D.D_Start <= '2021-03-04 07:30:00' and D.D_Ende > '2021-03-04 07:30:00');
>2
select count(D.D_ID) from Daten as D where (D.D_Start <= '2021-03-04 08:00:00' and D.D_Ende > '2021-03-04 08:00:00');
>1
select count(D.D_ID) from Daten as D where (D.D_Start <= '2021-03-04 07:10:00' and D.D_Ende > '2021-03-04 07:10:00');
>2
select count(D.D_ID) from Daten as D where (D.D_Start <= '2021-03-04 08:10:00' and D.D_Ende > '2021-03-04 08:10:00');
>2
select count(D.D_ID) from Daten as D where (D.D_Start <= '2021-03-04 10:10:00' and D.D_Ende > '2021-03-04 10:10:00');
>0

Nur wenn ich das jetzt mal zusammen baue, dann kommt der Stinkefinger aus der DB:

select M.M_Zeit, (count(D.D_ID) as Wert from Daten as D where (D.D_Start <= M.M_Zeit and D.D_Ende > M.M_Zeit)) from Messpunkte as M;
/* SQL Fehler (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as Wert from Daten as D where (D.D_Start <= M.M_Zeit and D.D_Ende > M.M_Zeit)) f' at line 1 */

Niels

 

[Muschelpuster]

Wie fast immer sitzt das Problem ja vor dem System. Eine eingebettete Abfrage braucht schon ein neues Select - ist ja kein Join!
Und schon klappt das wie gewünscht:

> select M.M_Zeit as Zeit,
    -> (select count(D.D_ID) as Wert from Daten as D where (D.D_Start <= M.M_Zeit and D.D_Ende > M.M_Zeit)) as Wert
    -> from Messpunkte as M group by M.M_Zeit;
+---------------------+------+
| Zeit                | Wert |
+---------------------+------+
| 2021-03-04 07:00:00 |    2 |
| 2021-03-04 07:10:00 |    1 |
| 2021-03-04 07:30:00 |    2 |
| 2021-03-04 08:10:00 |    2 |
| 2021-03-04 09:00:00 |    1 |
| 2021-03-04 10:00:00 |    1 |
| 2021-03-04 10:10:00 |    0 |
+---------------------+------+
7 rows in set (0.00 sec)

Bleibt also nur die Frage, ob dieses Vorgehen Sinn macht oder man die Messreihe auch mit sinnvollem Aufwand aus den Daten gewinnen kann.

Niels