MYSQL: Werte ausdehnen auf mehrer Zeilen? (belegte Leitungen berechnen)

[baeckerman83]

Hiho! Ich habe hier Daten in einer MYSQL Tabelle, die kommen aus einer Telefonmaschine. Die Tabelle hat folgende (für diesen Fall wichtige) Felder: datum, anrufzeitpunkt (hh:mm:ss), dauer (in Sekunden), team

Nun möchte ich berechnen wieviele Leitungen belegt waren. Belegt ist eine Leitung ab anrufzeitpunkt bis anrufzeitpunkt+dauer.

Gibt es eine Möglichkeit dies in Mysql zu bewerkstelligen?

Ich habe schon einiges versucht, dass hier ist mein letzter Versuch, wobei das JOIN nicht richtig passt. :( Er verknüpft zu viele Daten. In der Tabelle zahlen stehen einfach nur die Zahlen von 1 bis 3.000.000 damit ich jede Sekunde eines Monats abdecken kann. Hat jemand eine Idee?

SELECT 
	datum, zeit_leitungen, leitungen
FROM (
	SELECT 
		date_format(date_add('2015-04-01 00:00',INTERVAL nummern-1 SECOND),'%Y-%m-%d') as tag,
		date_add('2015-04-08 00:00',INTERVAL nummern-1 SECOND) as zeit_leitungen, 
		SUM(IF(anrufzeitpunkt<=date_add('2015-04-01 00:00',INTERVAL nummern-1 SECOND) AND (anrufzeitpunkt+dauer)>=date_add('2015-04-01 00:00',INTERVAL nummern-1 SECOND),1,0)) as leitungen 
	FROM
		zahlen 
	JOIN anrufdaten ON (
			datum=date_format(date_add('2015-04-01 00:00',INTERVAL nummern-1 SECOND),'%Y-%m-%d') 
		AND
			anrufzeitpunkt<=date_add('2015-04-01 00:00',INTERVAL nummern-1 SECOND)
		AND
			(anrufzeitpunkt+dauer)>=date_add('2015-04-01 00:00',INTERVAL nummern-1 SECOND)
			)
	WHERE 
		nummern>=1 
	AND 
		nummern<=(86400*9) 
	AND
		datum>='2015-04-01' 
	AND 
		datum<='2015-04-09'
	GROUP BY zeit_leitungen
) as leitungen

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

Gibt es eine Möglichkeit dies in Mysql zu bewerkstelligen?

Nur so als Grundidee:
Das Ganze mutet ja an, wie eine Digitalisierung... also eine Umsetzung von Ereignissen in digitale Werte. Eine "Abtasttabelle" hast Hast Du ja auch schon geplant.

Es gilt also, die Ereignisse (ON|OFF) der Anruftabelle zu kumulieren in der "Abtasttabelle". Für jedes Ereignis (ON|OFF) wird ein UNIQUE Zeitdatensatz angelegt und ein Zähler geführt. Den Zähler herauf (Gesprächsbeginn) oder herunter (Gesprächsende) zählen. Ist für den Zeitpunkt schon ein DS vorhanden, wird kein neuer angelegt, sondern nur der Zähler korrigiert.

Das lässt sich mMn mit einem "Insert into ... select from ... on Duplicate Key Update" oder wie das gerade heißt erledigen. Die Eventzeit wird hier als Key benutzt. Ob man Beginn und Ende in einem gemeinsamen Select verarbeiten kann, habe ich jetzt noch nicht überlegt.

Anschließend kannst Du dann die entstandene Tabelle abfragen, zu welchem Zeitpunkt wieviel Leitungen eingeschaltet waren. Die Werte müssen aufgrund der unklaren Grenzen für den Beginn und das Ende der Messung dann noch korrigiert werden um den negativsten Wert des Messzeitraumes (Offset beseitigen). Negative Leitungszahlen gibt es nämlich nicht.

Du benötigst also in deiner Ergebnistabelle nicht für jede Sekunde (Abtastrate) einen Datensatz, sondern nur für jede Sekunde, in der etwas passiert.

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

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

Gibt es eine Möglichkeit dies in Mysql zu bewerkstelligen?

Nur so als Grundidee:
Das Ganze mutet ja an, wie eine Digitalisierung... also eine Umsetzung von Ereignissen in digitale Werte. Eine "Abtasttabelle" hast Hast Du ja auch schon geplant.

Es gilt also, die Ereignisse (ON|OFF) der Anruftabelle zu kumulieren in der "Abtasttabelle". Für jedes Ereignis (ON|OFF) wird ein UNIQUE Zeitdatensatz angelegt und ein Zähler geführt. Den Zähler herauf (Gesprächsbeginn) oder herunter (Gesprächsende) zählen. Ist für den Zeitpunkt schon ein DS vorhanden, wird kein neuer angelegt, sondern nur der Zähler korrigiert.

Klappt so leider noch nicht. Man benötigt in der "Sekundentabelle" für den neuen Datensatz immer den Wert aus dem letzten Datensatz, um diesen dann zu incrementieren oder decrementieren.

Scheint mir inzwischen mit einer Monsterarray-Lösung in einer Programmiersprache doch einfacher zu sein. Das kann man dann ja immer noch abspeichern. Und da man nicht für jede Sekunde einen DS benötigt, könnte der Speicher sogar reichen. :-O

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

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

Gibt es eine Möglichkeit dies in Mysql zu bewerkstelligen?

Nur so als Grundidee:
Das Ganze mutet ja an, wie eine Digitalisierung... also eine Umsetzung von Ereignissen in digitale Werte. Eine "Abtasttabelle" hast Hast Du ja auch schon geplant.

Es gilt also, die Ereignisse (ON|OFF) der Anruftabelle zu kumulieren in der "Abtasttabelle". Für jedes Ereignis (ON|OFF) wird ein UNIQUE Zeitdatensatz angelegt und ein Zähler geführt. Den Zähler herauf (Gesprächsbeginn) oder herunter (Gesprächsende) zählen. Ist für den Zeitpunkt schon ein DS vorhanden, wird kein neuer angelegt, sondern nur der Zähler korrigiert.

Klappt so leider noch nicht. Man benötigt in der "Sekundentabelle" für den neuen Datensatz immer den Wert aus dem letzten Datensatz, um diesen dann zu incrementieren oder decrementieren.

Scheint mir inzwischen mit einer Monsterarray-Lösung in einer Programmiersprache doch einfacher zu sein. Das kann man dann ja immer noch abspeichern. Und da man nicht für jede Sekunde einen DS benötigt, könnte der Speicher sogar reichen. :-O

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.

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

[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]

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

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;

Das "order by start" macht mir noch Bauchschmerzen, weil order eigentlich die Ergebnismenge sortiert. Wir wollen hier aber eine sortierte Ausgangsmenge haben. Die müssen wir vermutlich vorher mit einem Subquery erzeugen?

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

[baeckerman83]

Hallo Robert,

dir auch schon einmal vielen Dank für die Mühe und die Energie die du investiert hast. Das mit den Extra Tabellen ist auch eine Möglichkeit, diese könnte ich dann ja auch Temporär anlegen. Der sortierte Insert ist ja kein Problem, das müsste ich hinbekommen. Ich teste die Lösungen, auch wegen der Performance und werde dann nächste Woche berichten wie ich es umgesetzt habe, oder wenn ich Fragen habe melde ich mich natürlich schon vorher. :)

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

dir auch schon einmal vielen Dank für die Mühe und die Energie die du investiert hast. Das mit den Extra Tabellen ist auch eine Möglichkeit, diese könnte ich dann ja auch Temporär anlegen. Der sortierte Insert ist ja kein Problem, das müsste ich hinbekommen. Ich teste die Lösungen, auch wegen der Performance und werde dann nächste Woche berichten wie ich es umgesetzt habe, oder wenn ich Fragen habe melde ich mich natürlich schon vorher. :)

Dann leg noch einen normalen Index (NICHT unique) auf die Zeitpunkt-Spalte start. Den hatte ich vorhin erst noch vergessen. Bei großen Datenmengen wird der nützlich sein.

CREATE TABLE `telefonate` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`start` TIMESTAMP NULL DEFAULT NULL,
	`action` INT(11) NULL DEFAULT '0',
	PRIMARY KEY (`id`),
	INDEX `start` (`start`)
)
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
;

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

[dedlfix]

Tach!

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

Das "order by start" macht mir noch Bauchschmerzen, weil order eigentlich die Ergebnismenge sortiert. Wir wollen hier aber eine sortierte Ausgangsmenge haben. Die müssen wir vermutlich vorher mit einem Subquery erzeugen?

Was ist der Unterschied zwischen Ergebnis und Ausgabe?

Generell gilt, dass die Daten in der Tabelle unsortiert vorliegen. Man kann zwar mit ALTER TABLE eine physische Sortierung veranlassen, aber die ist nur bis zu den nächsten Einfügungen oder Löschungen vorhanden. Außerdem betrifft das nicht die InnoDB-Tabellen, die haben ein Eigenleben, was die Reihenfolge anbelangt.

dedlfix.

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

Tach!

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

Das "order by start" macht mir noch Bauchschmerzen, weil order eigentlich die Ergebnismenge sortiert. Wir wollen hier aber eine sortierte Ausgangsmenge haben. Die müssen wir vermutlich vorher mit einem Subquery erzeugen?

Was ist der Unterschied zwischen Ergebnis und Ausgabe?

Generell gilt, dass die Daten in der Tabelle unsortiert vorliegen. Man kann zwar mit ALTER TABLE eine physische Sortierung veranlassen, aber die ist nur bis zu den nächsten Einfügungen oder Löschungen vorhanden. Außerdem betrifft das nicht die InnoDB-Tabellen, die haben ein Eigenleben, was die Reihenfolge anbelangt.

Kann sein, dass InniDB das wirklich besser macht.

Jedenfalls hatte ich bei MyISAM öfter mal das Problem, dass man mit Benutzervariablen in der oben gezeigten Form nur auf eine bereits vorsortierte Menge arbeiten durfte, sonst gab es gelegentlich mal Fehler. Die Sortierung wurde erst nach den Zuweisungen im Select vorgenommen. Sie muss aber vor den Zuweisungen stattfinden, gleich nach oder oprimiert sogar ggf. sogaar noch vor der Filterung der Daten.

Allerdings sollte die Reihenfolge der Abarbeitung spätestens durch den 1:1-Index klar sein.

• Index -> Sortierung
• Filterung (hier nicht vorhanden)
• Datenbuffer beschaffen (Daten aus dem Satz holen)
• Datenmanipulation
• Ausgabemenge erzeugen

So würde ich mir das jedenfalls vorstellen.

Es klappt ja auch alles. Ich habe gestern noch ein paar Hardcore-Tests damit angestellt. Und Du beruhigst mich auch.

Ich denke daher, dass vier einfache Statements hintereinander abgefahren hier wesentlich schneller sind, als ein Monsterstatement, wie es baeckerman83 zuerst versucht hat - wenn man das überhaupt hinbekommt.

Außerdem kann man die Statements ja auch in einer stored Routine zusammenfassen, so dass am Ende doch nur ein Funktionsaufruf übrig bleibt.

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

[dedlfix]

Tach!

Jedenfalls hatte ich bei MyISAM öfter mal das Problem, dass man mit Benutzervariablen in der oben gezeigten Form nur auf eine bereits vorsortierte Menge arbeiten durfte, sonst gab es gelegentlich mal Fehler. Die Sortierung wurde erst nach den Zuweisungen im Select vorgenommen.

Das ist ja auch kein Wunder, denn erst muss die Ergebnismenge berechnet werden, dann kann man sie sortieren. ORDER BY bezieht sich auf das was aus dem SELECT rauskommt, nicht auf das was in den Tabellen steht.

Allerdings sollte die Reihenfolge der Abarbeitung spätestens durch den 1:1-Index klar sein.

• Index -> Sortierung

Ein Index kann für die Sortierung genommen werden, wenn die Bedingungen dafür erfüllt sind. Sprich, wenn man direkt nach Feldinhalten sortiert. Sortiert man nach Werten, die erst in der SELECT-Klausel berechneten werden, kann kein Index helfen.

dedlfix.

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

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

Das "order by start" macht mir noch Bauchschmerzen, weil order eigentlich die Ergebnismenge sortiert. Wir wollen hier aber eine sortierte Ausgangsmenge haben. Die müssen wir vermutlich vorher mit einem Subquery erzeugen?

Was ist der Unterschied zwischen Ergebnis und Ausgabe?

Jetzt verstehe ich erst, warum Du die obige Rückfrage gestellt hast. :-)

Du hast Dich verlesen! Ich schrieb nicht "Ausgabemenge", sondern "Ausgangsmenge" im Sinne von "Die Menge, von der man ausgeht, mit der man anfängt". Naja, "Eingangsmenge" wäre vielleicht der bessere Ausdruck gewesen, um Missverständnissen vorzubeugen. Aber durch dein Foleposting ist mir das Missverständnis dann erst klar geworden.

Und das beantwortet nun auch die Frage. Dadurch, dass die Indizierte Spalte, deren Index für das "order by" benutzt wird, unangetastet (sie wird nicht manipuliert) bleibt, kann der Index für die Eingangsmenge der Operation benutzt werden. Ein zusätzliches Subselect zur Herstellung dieser Rahmenbedingungen (Sortierung) ist also nicht notwendig.

Kannst Du da zustimmen?

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

[dedlfix]

Tach!

Was ist der Unterschied zwischen Ergebnis und Ausgabe?

Du hast Dich verlesen! Ich schrieb nicht "Ausgabemenge", sondern "Ausgangsmenge" [...]

Da hast du wohl wahr.

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

Dadurch, dass die Indizierte Spalte, deren Index für das "order by" benutzt wird, unangetastet (sie wird nicht manipuliert) bleibt, kann der Index für die Eingangsmenge der Operation benutzt werden.

Ja, kann^[1]. Aber erst zur Sortierung, und die findet nach der SELECT-Klausel-Auswertung statt. Dass der Index zur Sortierung verwendet werden kann, garantiert nicht, dass die Reihenfolge der Datensatzbearbeitung sich ebenfalls nach diesen Index richtet.

dedlfix.

---

1. Ob oder ob nicht, sagt einem ein vorangestelltes EXPLAIN. Dessen Ausgabe bezieht sich aber nicht nur auf das Statement, sondern kann sich beispielsweise je nach Menge der Daten ändern. ↩︎

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

Ja, kann^[1]. Aber erst zur Sortierung, und die findet nach der SELECT-Klausel-Auswertung statt. Dass der Index zur Sortierung verwendet werden kann, garantiert nicht, dass die Reihenfolge der Datensatzbearbeitung sich ebenfalls nach diesen Index richtet.

Das habe ich versucht, auf das gesamte Statement anzuwenden. Das geht leider nicht. Ich habe leider auch keine vernünftige Erklärung finden können, wie man EXPLAIN anzuwenden hat - auch bei MySQL nicht.

Please EXPLAIN TFFM

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

---

1. Ob oder ob nicht, sagt einem ein vorangestelltes EXPLAIN. Dessen Ausgabe bezieht sich aber nicht nur auf das Statement, sondern kann sich beispielsweise je nach Menge der Daten ändern. ↩︎

[dedlfix]

Tach!

Please EXPLAIN TFFM

Understanding the Query Execution Plan

dedlfix.

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

Please EXPLAIN TFFM

Understanding the Query Execution Plan

Das hab ich wohl schon angeschaut und, soweit ich es für relevant fand. ausprobiert. Aber echte Beispiele (insbesondere Negativ-Beisiele) sind da ja auch nicht vorhanden. MMn fehlen die Negativ-Beispiele in manuals immer. Einige wenige "Do not try" mit kurzer knackiger Begründung könnten seiteweise theoretische Excerpte über positive Möglichkeiten obsolet machen.

Dir aber - schon wegen des Archiv-Wertes - meinen DNO :-)

Danke.

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

[dedlfix]

Tach!

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

Dann nimm doch DATETIME oder setz den Default-Wert auf NULL.

dedlfix.

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

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

Dann nimm doch DATETIME

Das geht in Ordnung, allerdings hat sich da auch gerade etwas getan bei MySQL.

oder setz den Default-Wert auf NULL.

Das will mein Client immer nicht akzeptieren. Automatischer Fehler sozusagen. Ich muss das Tool mal updaten ;-)

Kannst Du mir was zu den Bauchschmerzen mit 'order by' sagen?
Es funktioniert zwar alles, auch mit meinen Nachträgen in meinen Testdaten (bunt gemischte Zeiten), aber ich trau dem MySQL nicht. .

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

[dedlfix]

Tach!

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

Dann nimm doch DATETIME

Das geht in Ordnung, allerdings hat sich da auch gerade etwas getan bei MySQL.

Ja, man kann DATETIME nun auch automatisch aktualisieren lassen. Es gibt aber weiterhin nur die Automatik, dass die erste TIMESTAMP-Spalte auf die Update-Automatik gesetzt wird. Alle weiteren und alle DATETIMEs verhalten sich friedlich.

dedlfix.

[Jörg Reinholz]

Moin!

Ich lesen immer INTERVAL.

Nun möchte ich berechnen wieviele Leitungen belegt waren. Belegt ist eine Leitung ab anrufzeitpunkt bis anrufzeitpunkt+dauer.

Warum nicht etwa so:

select count(id) from gespräche where start <= zeitpunkt and zeitpunkt <= ende

oder so:

select count(id) as leitungen from gespräche where zeitpunkt between start, ende;

Natürlich musst Du den Zeitpunkt wissen. Hast Du mehrere bietet sich an, diese mit foreach zu call-en.

Jörg Reinholz

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

Ich lesen immer INTERVAL.

Nun möchte ich berechnen wieviele Leitungen belegt waren. Belegt ist eine Leitung ab anrufzeitpunkt bis anrufzeitpunkt+dauer.

Warum nicht etwa so:

select count(id) from gespräche where start <= zeitpunkt and zeitpunkt <= ende

oder so:

select count(id) as leitungen from gespräche where zeitpunkt between start, ende;

Natürlich musst Du den Zeitpunkt wissen. Hast Du mehrere bietet sich an, diese mit foreach zu call-en.

Ich habe das so verstanden, dass ein Chart entstehen soll, wieviele Leitungen zu welchem Zeitpunkt benutzt wurden. So wie Du das vorschlägst, müssten dafür entsprechend der Abtastrate viele Selects durchgeführt werden. Das wären nach baeckerman83s Vorgabe 3.000.000 pro Tag.

Wie lange soll die Datenbank daran arbeiten?

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

[Jörg Reinholz]

Moin!

Ich habe das so verstanden, dass ein Chart entstehen soll, wieviele Leitungen zu welchem Zeitpunkt benutzt wurden. So wie Du das vorschlägst, müssten dafür entsprechend der Abtastrate viele Selects durchgeführt werden. Das wären nach baeckerman83s Vorgabe 3.000.000 pro Tag.

Wie lange soll die Datenbank daran arbeiten?

Ja! Und wie stellt man die Anzahl der belegten Leitungen pro Sekunde und für einen Tag graphisch dar? Schmaler als 1 Pixel kann der Balken nicht werden!

Nehmen wir mal an, das Chart soll 300 Pixel breit werden (+Rand und Kram):

Dann weiß ich auch nicht, warum die Abtastrate willkürlich auf eine Sekunde festgelegt wurde. Das kann man ja auch sehr viel genauer haben und pro Tag locker eine Phantastiliarde Zeitpunkte abfragen um nach dem Wegwerfen von 1 Phantastiliarde - 300 Datentupeln einen Graph zu bekommen.

Andererseits MUSS man sich dann fragen, warum für die Powerpoint-Präsentation nicht gleich nur die maximal darstellbaren 300 Zeitpunkte abgefragt werden.

Jörg Reinholz

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

Ich habe das so verstanden, dass ein Chart entstehen soll, wieviele Leitungen zu welchem Zeitpunkt benutzt wurden. So wie Du das vorschlägst, müssten dafür entsprechend der Abtastrate viele Selects durchgeführt werden. Das wären nach baeckerman83s Vorgabe 3.000.000 pro Tag.

Wie lange soll die Datenbank daran arbeiten?

Ja! Und wie stellt man die Anzahl der belegten Leitungen pro Sekunde und für einen Tag graphisch dar? Schmaler als 1 Pixel kann der Balken nicht werden!

Das würde hier eine Treppenkurve ergeben, ählich einem gesampleten Tonsignal.

Man benötigt in der Ergebnistabelle aber nicht pro Sekunde (oder wie fein man auflösen will) einen Datensatz, sondern nur pro Event (also ON|OFF). Und diese Tabelle kann dann so fein auflösen, wie es der Spaltentyp für die Zeit (und Dauer) eben zulässt. Solange kein neuer Datensatz vorliegt, wird der alte Wert gehalten, daher Treppenkurve.

Ich finde es jedenfalls spannend, ob hier jemand eine funktionstüchtige und sparsame Lösung mit SQL bzw. MySQL zustande bringt.

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

[Jörg Reinholz]

Moin!

Man benötigt in der Ergebnistabelle aber nicht pro Sekunde (oder wie fein man auflösen will) einen Datensatz, sondern nur pro Event (also ON|OFF).

Kommt darauf an. Wenn es ein großes Callcenter mit, sagen wir mal, 300 Plätzen ist und das durchschnittliche Gespräch nur 1 Minute dauern soll, dann wären das

300*2 = 600 Ereignisse/Minute.

Sicherlich gäbe es am Ende eine Datenreduktion, weil übereinander liegende Ereignispunkte ausgeschlossen werden könnten, aber bei dem ganzen Herumrechnen, Sortieren und Auschließen kommt vor lauter Anstrengung niemand dazu, nachzurechnen, wie viel einfacher und stromsparender es doch wäre, einfach aller 1, 5 oder 10 Minuten einen Balken für die Präsentation zu fabrizieren...

Jörg Reinholz

[dedlfix]

Tach!

Ich lass mal die bereits hervorgebrachten Argumente über die Sinnhaftigkeit einer Auflösung in 3 Millionen Schritten unbeachtet.

Ich habe hier Daten in einer MYSQL Tabelle, die kommen aus einer Telefonmaschine. Die Tabelle hat folgende (für diesen Fall wichtige) Felder: datum, anrufzeitpunkt (hh:mm:ss), dauer (in Sekunden), team

Das ist ein nicht sehr günstiger Aufbau für das Vorhaben. Wenn du die 3 Mio Datensätze aus der Zahlentabelle mit den Datensätzen der Telefonanlage verknüpfen möchtest, muss du für jeden Datensatz erstmal die Nummer in ein Datum bestehend aus Einzelwerten konvertiert werden oder aus den Einzelwerten eine Nummer gebildet werden. Und das für jeden Datensatz einzeln. Ein Index kann jedenfalls nicht verwendet werden. Ein Nebenziel sollte also sein, die Anzahl der Rechenoperationen auf ein Minimum zu reduzieren, am besten so, dass ein Index verwendet werden kann.

Das heißt, dass die Werte vorher so umformatiert werden müssen, dass sie sich direkt vergleichen lassen, ohne berechnet zu werden.

Du wirst die Daten aus der Telefonanlage sicher nicht als MySQL-Tabelle bekommen sondern sie zeilenweise in einer Datei vorliegen haben und sie irgendwie anders ins das DBMS einlesen. Dabei solltest du eine Normalisierung der Daten vornehmen, so dass zwei DATETIME-Felder entstehen. Eins für den Beginn und eins für das Ende der Leitungsbelegung.

Nun möchte ich berechnen wieviele Leitungen belegt waren. Belegt ist eine Leitung ab anrufzeitpunkt bis anrufzeitpunkt+dauer. Gibt es eine Möglichkeit dies in Mysql zu bewerkstelligen?

Sicherlich mehrere.

An der Zahlentabelle führt meines Erachtens kein Weg vorbei. Ansonsten gibt es nur Ergebnisse für Zeiten, in denen mindestens eine Leitung belegt war. Ein Weg ist, die Zahlentabelle zu selektieren und für jeden Eintrag in einer Subquery die betroffenen Datensätze zu zählen. Das ist vor allem einfacher zu verstehen und einfacher separat zu testen als ein großes Join-Gebilde anzufertigen. Man kann die Subquery nämlich einzeln auf einen bestimmten Zeitpunkt loslassen und so stichprobenhaft kontrollieren, ob das gelieferte Ergebnis stimmt, ohne erst eine große Ergebnismenge aus allen Daten zu erstellen.

Mein erst Versuch wäre, in der äußeren Query aus den Nummern in der Zahlentabelle und dem Monatsanfang einen konkreten Zeitpunkt als DATETIME zu berechnen. Das gibt dann zwar 3 Mio Berechnungen, aber das ist der Kompromiss. Sonst müsste die Zahlentabelle eine DATETIME-Tabelle werden und für jeden Monat neu erstellt werden. (Es kann aber durchaus sein, dass das aus Performancegründen so gemacht werden muss.) Jedenfalls kannst du diese Query schon einmal abschicken und dich überzeugen, dass sekundenfeine Ergebnisse entstehen und auch gleich einen Eindruck von der Laufzeit bekommen, wenn sie nur diese eine Aufgabe erfüllt.

Der nächste Schritt ist, die spätere Subquery zu erstellen - zunächst einmal ohne die äußere Query. Diese zählt alle Datensätze, für die ein bestimmter Zeitpunkt BETWEEN Beginn AND Ende liegt. An dieser Stelle findet keinerlei Berechnung statt, nur zwei Vergleiche, die das DBMS mit je einem Index auf den beiden Spalten sehr schnell erledigen können sollte. Stichproben liefern plausible (oder noch besser korrekte) Ergebnisse? Fein, dann die Subquery in die äußere Query einfügen und finale Tests starten. Laufzeit auch ok? Wenn nicht, dann kommt die Performanceoptimierung an die Reihe.

SUM(IF(anrufzeitpunkt<=date_add('2015-04-01 00:00',INTERVAL nummern-1 SECOND) AND (anrufzeitpunkt+dauer)>=date_add('2015-04-01 00:00',INTERVAL nummern-1 SECOND),1,0)) as leitungen

Gibt es einen Grund, warum du für einen betroffenen Datensatz eine 1 aufsummierst, statt die betroffenen Datensätze zu zählen?

dedlfix.

[baeckerman83]

Vielen Dank für eure Hilfe! Mit so vielen antworten habe ich gar nicht gerechnet! Die Daten liegen in einer Datenbank, denn die Anlage schreibt direkt in Datenbanken. Die Aufteilung habe ich mir nicht ausgedacht, man kennt das ja von Aufgaben die zu erledigen sind, Interessant ist die Leitungs Belegung pro Sekunde, und da muss ich dann noch höchste Leitungs Belegungen mit der dazugehörigen Sekunde raussuchen für einen Bericht.

Gibt es einen Grund, warum du für einen betroffenen Datensatz eine 1 aufsummierst, statt die betroffenen Datensätze zu zählen?

Ja ich hatte einige Sachen ausprobiert und versucht auf die Performance zu achten. Wenn ich schon am Anfang Minuten warten muss ist das zu lang. Die Abfrage der 3 Mio Zahlen und Berechnung des Zeitstempels geht ganz schnell. Der Join ist dann das Problem.

Der nächste Schritt ist, die spätere Subquery zu erstellen - zunächst einmal ohne die äußere Query. Diese zählt alle Datensätze, für die ein bestimmter Zeitpunkt BETWEEN Beginn AND Ende liegt. An dieser Stelle findet keinerlei Berechnung statt, nur zwei Vergleiche, die das DBMS mit je einem Index auf den beiden Spalten sehr schnell erledigen können sollte. Stichproben liefern plausible (oder noch besser korrekte) Ergebnisse? Fein, dann die Subquery in die äußere Query einfügen und finale Tests starten. Laufzeit auch ok? Wenn nicht, dann kommt die Performanceoptimierung an die Reihe.

Das werde ich mir noch einmal anschauen, ohne SUM und dann zählen mit COUNT. Das war ja so ähnlich meine Idee, nur falsch umgesetzt. Wo muss ich dann Zählen? Dann im select oder? Also ohne Join?

Das Ergebnis werde ich hier natürlich dann auch zeigen.

[robertroth]

Liebe Mitdenker, liebe Wissende, liebe Neugierige,

Ja ich hatte einige Sachen ausprobiert und versucht auf die Performance zu achten. Wenn ich schon am Anfang Minuten warten muss ist das zu lang. Die Abfrage der 3 Mio Zahlen und Berechnung des Zeitstempels geht ganz schnell. Der Join ist dann das Problem.

Du benötigst nicht für jede Sekunde einen Datensatz, sondern nur für jeden Event (ON, OFF). Und da könnten ja beträchtliche Lücken entstehen oder aber die Abstände (die Auflösung) sogar kleiner werden. Gib uns doch bitte mal eine Größenordnung.

Hat Jörg nachher sogar noch Recht, und Du rechnest ein Callcenter mit 300 Plätzen durch? ;-)

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

[baeckerman83]

Hiho! Nein ein CallCenter ist es nicht, es geht um etwas ähnliches, Telefongewinnspiele, Votings und Co. Und dazu dient die Auswertung der Portnutzung der Plattform. Leider sind wir immer noch nicht fertig. Es sah erst alles gut aus, auch mit den Tipps hier, aber sobald die Menge der Datensätze ansteigt benötigt die Abfrage zu viel Zeit, wir sind also noch am optimieren. Sobald ein Ergebniss vorliegt schreibe ich das.