Konsolidierung von Zeiträumen, Anzahl Belegungen

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

Nochmal nachgedacht:

Ausgangssituation:
Es gibt eine Tabelle mit (id,) zeit_start, zeit_dauer

Separierung:

Daraus machen wir (mit zwei Statements) eine Tabelle mit (id, ) zeit, aktion
aus zeit_start wird zeit und +1 für aktion
aus (zeit_start + dauer) wird zeit und -1 für aktion

zeit ist hier noch nicht unique, es können also pro Zeitpunkt noch mehrere Events auftreten

Konsolidierung:

Im nächsten Schritt tragen wir mittels einer Benutzervariablen den aktuellen Stand zusammen und erzeugen eine neue Tabelle mit zeit (unique) und status
zeit ist die Zeit aus der anderen Tabelle
status erhält den Wert der aktuellen Benutzervariable,
die sich aus Benutzervariable + aktion berechnet.

Die neue Tabelle wird mit dem schon erwähnten
"INSERT INTO … SELECT FROM … ON DUPLICATE KEY UPDATE"
erzeugt und hat nun für jeden Änderungszeitpunkt die aktuelle Anzahl von aktiven Leitungen,
nachdem man den Wert von status noch um den größten negativen Wert (offset) korrigiert hat.

Mit Glück gibt es hier Einen, der aus meinen drei (vier) Statements eins machen kann.

Und den Kern der Lösung ausprobiert:

CREATE TABLE `telefonate` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`start` TIMESTAMP NULL DEFAULT NULL,
	`action` INT(11) NULL DEFAULT '0',
	PRIMARY KEY (`id`)
)
COMMENT='Testtabelle zu SelfHTML \r\nhttp://forum.selfhtml.org/self/2015/apr/17/mysql-werte-ausdehnen-auf-mehrer-zeilen-belegte-leitungen-berechnen/1637707#m1637707'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `leitungen` (
	`dummy` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`zeit` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
	`anzahl` INT(11) NOT NULL,
	PRIMARY KEY (`zeit`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Musterdaten:

"id";"start";"action"
"1";"2015-04-17 20:47:16";"1"
"2";"2015-04-17 20:47:29";"1"
"3";"2015-04-17 20:47:43";"1"
"4";"2015-04-17 20:47:51";"-1"
"5";"2015-04-17 20:48:01";"1"
"6";"2015-04-17 20:48:13";"1"
"7";"2015-04-17 20:48:21";"-1"
"8";"2015-04-17 20:48:29";"-1"
"9";"2015-04-17 20:48:39";"1"
"10";"2015-04-17 20:48:50";"1"
"11";"2015-04-17 20:48:58";"-1"
"12";"2015-04-17 20:49:19";"-1"
"13";"2015-04-17 20:49:27";"-1"
"14";"2015-04-17 20:49:36";"1"
"15";"2015-04-17 20:49:49";"-1"
"16";"2015-04-17 20:49:59";"-1"
"17";"2015-04-17 20:50:20";"1"
"18";"2015-04-17 20:50:26";"1"
"19";"2015-04-17 20:50:32";"-1"
"20";"2015-04-17 20:50:49";"-1"

Kern-Statement:

SET @akt:= 0;

INSERT INTO leitungen(zeit, anzahl)
SELECT start, @akt:=@akt + action
FROM telefonate 
order by start
ON DUPLICATE KEY UPDATE anzahl=@akt;

Ergebnis:

"dummy";"zeit";"anzahl"

"2015-04-17 21:08:16";"2015-04-17 20:47:16";"1"
"2015-04-17 21:08:16";"2015-04-17 20:47:29";"2"
"2015-04-17 21:08:16";"2015-04-17 20:47:43";"3"
"2015-04-17 21:08:16";"2015-04-17 20:47:51";"2"
"2015-04-17 21:08:16";"2015-04-17 20:48:01";"3"
"2015-04-17 21:08:16";"2015-04-17 20:48:13";"4"
"2015-04-17 21:08:16";"2015-04-17 20:48:21";"3"
"2015-04-17 21:08:16";"2015-04-17 20:48:29";"2"
"2015-04-17 21:08:16";"2015-04-17 20:48:39";"3"
"2015-04-17 21:08:16";"2015-04-17 20:48:50";"4"
"2015-04-17 21:08:16";"2015-04-17 20:48:58";"3"
"2015-04-17 21:08:16";"2015-04-17 20:49:19";"2"
"2015-04-17 21:08:16";"2015-04-17 20:49:27";"1"
"2015-04-17 21:08:16";"2015-04-17 20:49:36";"2"
"2015-04-17 21:08:16";"2015-04-17 20:49:49";"1"
"2015-04-17 21:08:16";"2015-04-17 20:49:59";"0"
"2015-04-17 21:08:16";"2015-04-17 20:50:20";"1"
"2015-04-17 21:08:16";"2015-04-17 20:50:26";"2"
"2015-04-17 21:08:16";"2015-04-17 20:50:32";"1"
"2015-04-17 21:08:16";"2015-04-17 20:50:49";"0"

die Spalte dummy gibt es nur, weil MySQL mit Timestamp-Spalten ja Mystik betreibt und ich die hier nicht gebarauchen konnte beim Update.

Es scheint also so zu funktionieren, wie ich mir das gedacht habe.
Wie Du aus deiner ersten Tabelle nun meine Event-Tabelle Telefonate machst (mit zwei Statements) wirst Du vermutlich wissen, sonst frag nochmal.

Wie man jetzt aus der Ergebnistabelle die Anzahl belegter Leitungen zu einem bestimmten Zeitpunkt rausholt, wirst Du auch selber wissen. Stichwort "limit 1".

Spirituelle Grüße
Euer Robert
[e-mail]