Daten verdichten/reduzieren: Ansatz gesucht

Java-Forum Allgemeine Themen
1

Hallo zusammen,

gesucht ist eine Methodik oder allgemein bekanntes (aber mir noch unbekanntes) vorgehen, wenn es darum geht, viele Messwerte auf einer Zeitachse in einem bestimmten Zeitfenster zu verdichten/reduzieren.

Anwendungsfall:

Man hat eine (SQL) Datenbank in die Messwerte geloggt werden. z.B. Messwert A wird immer im Minutenrhythmus erfasst. Messwert B im 15min Rhythmus, und Messwert C wird erfasst, wenn das zugehörige Gerät meint einen Messwert mitzuteilen. Also im völlig unklaren Rhythmus.

Das erfassen der Daten in der DB ist nicht das Problem. Jeder Messwert bekommt einen Zeitstempel seiner Messung/Erfassung und fertig.

Was aber, wenn ich die Daten visualisieren möchte. Zeitfenster: z.B. die vergangene Woche.

Von A hab ich dann gut 10000 Werte, von B rund 670 und von C vielleicht nur 5.
Oder vom vergangenen Jahr: rund 525.000 Werte von A, rund 35.000 und von C vielleicht nur 60.

Da es möglich sein sollte über eine HTTP-Schnittstelle die Daten für ein bestimmtes Zeitfenster abzufragen und mit einer JavaScript Library zu visualisieren (Werte auf der Y-Achse, Zeit auf der X-Achse), wäre es Datentransfertechnisch schon übel über 10.000 Werte, ganz zu schweigen von über eine halbe Million Werte zu transportieren um sie dann mit JavaScript in einer Grafik anzuzeigen.

Gibt es ein gängiges Verfahren wie ich, ohne Veränderung der Datenaufzeichnung, die Werte in sowas wie ein gröberes Raster pressen kann (so dass ich z.B. 10.000 Werten reduzieren kann auf 500 Werte), ohne dabei so Ausreißer wie Messwert C - der nur Verhältnismäßig selten vorliegt - unter den Tisch fallen zu lassen?

Hintergrundgedanke, neben der schieren Datenmenge ist z.B. auch: Es macht keinen Sinn 10.000 Messwerte auf der X-Achse in einer nur 400px breiten Grafik anzeigen zu wollen. Theoretisch reichen hier ja 400 Werte.

RRDtool ist mir bekannt. Die Daten liegen aber bereits vor und migrieren in RRD nur für die Visualisierung ist auch doof.

Bin für alle Tipps dankbar.

Gruß
Alex

2

wie allgemein soll es denn werden, hast du schon eine Vorstellung was bei Blackbox-Zauberei idealerweise herauskäme?

vorstellbar an indivdueller Kleinlösung wäre mir hier, dir sicher auch, aber da bisher noch nicht ausgesprochen:
Zeitraum bestimmen, entweder aus vorhandenen Daten oder selber gewählt, Intervall für einen Pixel bestimmen,
in der DB danach gruppieren (nach einem Tag leicht, nach paar Stunden Modulo zu rechnen usw.), einen Durchschnittswert je Intervall holen

Problemchen evtl:

  • bei 365 Tagen und 400 Pixel vielleicht lieber auf ganzen Tag als Intervall gehen statt 21.9 Stunden → 365 Werte, was dann in den restlichen Pixeln darstellen?
    lieber mehr als ein Jahr anzeigen soweit verfügbar?

  • wie steht es um Fall C mit seinen knappen Werten, wäre verkraftbar wenn von den 60 noch 10 durch Durchschnittsbildung verloren gingen?
    falls das droht, wäre aber auch mit allen 60 geladenen Daten die Darstellung schwierig, zwei Werte schließlich so dicht zusammenliegen, dass sie auf einen Pixel fallen,

wenn nötig auf die Anzahl Werte in DB schauen und ab einer bestimmten Grenze einfach alle laden, in Java wie auch immer weiterverarbeiten

  • hier bei dir sind in den höheren Fällen anscheinend die Werte gleichverteilt,
    bei 500.000 Werten könnten allgemein aber auch 400.000 in der ersten Hälfte des Jahres liegen, nur 100.000 in der zweiten,
    hätte das irgendwelche Auswirkungen auf deine Wünsche zur Auswertung?
    na, das ist fast schon allgemeine Frage ‚was genau möchtest du alles berücksichtigt haben?‘ :wink:

  • wäre es bei knappen Intervall (800 DB-Werte zu 400 Pixel) und Ungenauigkeiten der Zeitstempel schlecht,
    falls mal 3 Werte in ein Intervall fallen, nur einer ins nächste, gar keiner ins übernächste?
    letzteres sicher unschön :wink: bei durchgängigen Linien aber durchaus zu verkraften

3

hast du schon eine Vorstellung was bei Blackbox-Zauberei idealerweise herauskäme?

Die Schnittstelle zur HTTP-Welt wäre eine Tabelle in der Form:

Zeitstempel1, Wert1, Wert2, Wert…, Wertn
Zeitstempel2, Wert1, Wert2, Wert…, Wertn
Zeitstempel3, Wert1, Wert2, Wert…, Wertn
Zeitstempel…, Wert1, Wert2, Wert…, Wertn
Zeitstempeln, Wert1, Wert2, Wert…, Wertn

Die Daten in der DB könnte ich vom Zeitstempel her auf Minutengenauigkeit patchen (sprich anhand der Sekunden runden).
Somit hätte ich das Raster in der DB schon mal von Sekunden auf Minuten angehoben. Das sollte die Situation ein klein wenig verbessern.

Ich weiß, ist bisher sehr allgemein. Hab jetzt auch weiter recherchiert.

Hab bei meiner Recherche was von „sql aggregate functions“ gefunden. Ähnlich scheinen „windowing functions“ zu sein. Allerdings wird letzteres wohl von nicht so vielen DBs (inkl. der aktuell genutzten MySQL) unterstützt.
Irgendwie scheint es damit möglich zu sein Zeilen in der DB Anhang einer Funktion (z.B. average) zusammen zu fassen. Das wäre eigentlich genau das was ich brauche und sogar auf DB-Ebene. Wie das allerdings mit Fall B und C funktioniert, wo nicht für jede Minute ein Wert vorhanden ist, ist mir noch nicht klar. Muss das mal ausprobieren.

vorstellbar an indivdueller Kleinlösung wäre mir hier, dir sicher auch, aber da bisher noch nicht ausgesprochen:
Zeitraum bestimmen, entweder aus vorhandenen Daten oder selber gewählt, Intervall für einen Pixel bestimmen,
in der DB danach gruppieren (nach einem Tag leicht, nach paar Stunden Modulo zu rechnen usw.), einen Durchschnittswert je Intervall holen

Problemchen evtl:

  • bei 365 Tagen und 400 Pixel vielleicht lieber auf ganzen Tag als Intervall gehen statt 21.9 Stunden → 365 Werte, was dann in den restlichen Pixeln darstellen?
    lieber mehr als ein Jahr anzeigen soweit verfügbar?

Da bin ich flexibel. Es geht um die Anzeige an sich. Wenn links und rechts in der Grafik noch ein klein wenig mehr steht/zu sehen ist, ist das nicht tragisch.

  • wie steht es um Fall C mit seinen knappen Werten, wäre verkraftbar wenn von den 60 noch 10 durch Durchschnittsbildung verloren gingen?

Schwierig… Müsste man ausprobieren. Aber so prinzipiell ist das ja ein prinzipbedingtes Problem: Wenn ich verdammt viele Werte zusammen mit verdammt wenigern Werten anzeigen will, dann gehen die wenigen Werte zwagsläufig ein wenig, weil die Auflösung der Anzeige gezwungenermaßen niedriger ist.
Blöd wäre es nur, wenn gar nix mehr davon zu sehen ist. Denn dann könnte man interpretieren, dass keinerlei Werte vorliegen.

wenn nötig auf die Anzahl Werte in DB schauen und ab einer bestimmten Grenze einfach alle laden, in Java wie auch immer weiterverarbeiten

Daran hab ich auch schon gedacht. Ich glaube ich muss hier mal tests fahren um da einen Erfahrungswert zu bekommen.

  • hier bei dir sind in den höheren Fällen anscheinend die Werte gleichverteilt,
    bei 500.000 Werten könnten allgemein aber auch 400.000 in der ersten Hälfte des Jahres liegen, nur 100.000 in der zweiten,
    hätte das irgendwelche Auswirkungen auf deine Wünsche zur Auswertung?

Das kommt auf die Messquelle an. Es kann durchaus sein dass der erfassungsintervall mal geändert wird und sich dann solche Effekte ergeben. Aber die Regel ist das nicht.

  • wäre es bei knappen Intervall (800 DB-Werte zu 400 Pixel) und Ungenauigkeiten der Zeitstempel schlecht,
    falls mal 3 Werte in ein Intervall fallen, nur einer ins nächste, gar keiner ins übernächste?
    letzteres sicher unschön bei durchgängigen Linien aber durchaus zu verkraften

Mit der reduzieren auf allgemeine Minutengenauigkeit dürfte sich das Problem ein klein wenig entschärfen. Insgeamt wäre es natürlich schlecht, aber verkraftbar.

Aktuell hoffe auch auf die Aggregierungfunktionen der DB. Ich muss mich hier aber noch schlau machen und das mal ausprobieren.
Wäre mir das liebste wenn es so funktioniert. Ansonsten müsste ich das in Java nachbauen, was aber das abfragen von u.U. sehr vielen Daten aus der DB mit sich bringt (–> kostet zeit).

4

Ich habe auch an einer ähnliche Aufgabe zu knabbern. Man muss ja ganz klar sagen, dass mehr wie 1000 Werte pro Graph einfach unsinnig ist. Es gibt ja dann noch die Charts, die zoomen können (amChart z.B.). Hier kann man schon überlegen auch mal 5000 - 10.000 Werte zu übertragen. Bei 30.000 Werte ist aber das Chart so grottig langsam, dass es gar kein Sinn macht und auch mit Zoom ist das schon zu viel. Wenn man keine Werte verfälschen möchte, muss man einfach die Anzahl aufgrund der Zeit oder Anzahl Datensätze einschränken.

Wenn man auch mal den Mittelwert über eine Zeitspanne anzeigen darf, dann würde ich schon ernsthaft überlegen beim Speichern der Daten bereits die Daten in einer separaten Tabelle aufzubereiten, oder zumindest im Intervall über einen Job. Das Lesen aus so großen Tabellen, wo die Daten während des Auslesens aufbereitet werden, sind einfach nur langsam und machen dem User keine Freude. Es ist gang und gäbe für Reporting Zwecke die Daten zu denormalisieren und separat (teils auch auf einem separaten DB Server) so zu speichern, dass das Auslesen fix von statten geht. Dafür gibt es ja sogar fertige Software. Auch solltest du bedenken, dass solche Abfragen häufig den DB Server runter ziehen. Dann hat man schnell mal Supportanfragen am Hals, wo jemand sagt: “jeden Montag und Mittwoch zwischen 10 und 11 Uhr läuft alles so schrecklich langsam” :). Und dann finde mal das Problem :).
Ich kann mir z.B. eine Lösung vorstellen, wo die Daten jeweils 15, 5, und 1 minütig per Mittelwertbildung abgelegt wird. Und je nachdem, was der User für einen Zeitraum zur Anzeige wählt, wird die passende Tabelle ausgelesen. Auch kannst du aus solchen Tabellen sehr alte Daten oft wieder löschen. Für den seltenen Fall, dass man mal 5 Jahre in die Vergangenheit schauen mag, kann man vermutlich auch die Variante mit den kleinen Zeitraum und den Originaldaten wählen.

*** Edit ***

Mal ganz nebenbei und aus reinem Interesse: Was für eine JS Chart Lib hast du dir raus gesucht?

5

Thematisch kann ich wohl nicht so viel dazu sagen, aber dass ich dauernd an https://optimization.mccormick.northwestern.edu/index.php/Piecewise_linear_approximation denken muss, schon.
[ot]
Irgendwann bin ich mal über d3.js gestolpert (man kommt ja kaum drumrum). Da dachte ich mir: Hey, ich bastle mal eine Chart-Library in JS. Zu diesem Zeitpunkt dachte ich mir schon: Ja, da gibt’s bestimmt schon was, aber ich google mal NICHT danach… Naja, ziemlich bald habe ich dann DOCH gegooglet, und eines der ersten Suchergebnisse war so eine Seite “Die 50 besten JavaScript Chart-Libraries” :rolleyes: (Und mindestens (!) 49 davon bauten auf d3.js auf … :rolleyes: ). Ich habe keine Ahnung davon, denke aber, dass da ziemlich viel gepfuscht wird. (Kein allgemeines Vorurteil gegenüber JavaScript-Programmierern, aber … naja, so halb, wie wir Java-Programmerer es uns eben auch von den C+±Programmierern anhören müssen :D). Aber wenn z.B. Google seine JS-Charts veröffentlicht, wird das wohl nicht komplett zusammengestümperter Mist sein. [/ot]

6

@freezly
Das was du beschreibst klingt nach einer re-implementierung des rrdtools … Das hatte ich eigentlich nicht vor.

Das System ist nicht so groß als dass man einen weiteren DB-Server hinstellen kann/muss/sollte.
Ich habe bereits ähnliches am laufen. Da sind in rund 4 Jahren rund 0,8 Millionen Zeilen in der DB entstanden, die rund 250MB benötigen. Mehr als 5 Jahre würde ich auf dauer nicht in der DB halten wollen. D.h. die Datenmenge hält sich noch einigermaßen in Grenzen und lässt sich noch mit kleinen, schwachbrüstigen Rechnern bewältigen.
Einsatzgebiet liegt im Heimautomatisierungsbereich. Da stellen sich die wenigsten dedizierte DB-Server hin um ihre Messdaten der Heizung, Lüftung, und weiterer Sensorik zu erfassen.

Was ich in der Vergangenheit schonmal gemacht habe, und was recht gut funktioniert:

alle Daten ausnahmslos 1x pro Minute erfassen. Das macht das auswerten und auslesen aus der DB recht einfach. Hier hatte ich einfach jede x-te Zeile (x basierend auf der skalierung des Zeitfensters und der Grafikbreite) abgefragt. Das geht schnell, reduziert die Datenmenge und liefert brauchbare Ergebnisse. Je größer die Zeitspanne, desto größer die “unschärfe”. Aber das war vernachlässigbar.

Problem ist jetzt aber, dass ich mit willkürlichen Zeitabständen der einzelnen Daten klarkommen muss. D.h. hier funktioniert das abfragen jeder x-ten Zeile im entsprechenden Zeitfenster nicht mehr.

Was ich mir aktuell vorstellen kann, aber noch recherchieren und testen muss:

  • Prüfen wie viele Messwerte ich für die ideale Anzeige in der Grafik bräuchte. Beispielsweise 800px --> 800 Werte
    ** Prüfen wie viele Messwerte die abzufragende Messreihe in diesem Zeitraum hat.
    *** Wenn <= 800, dann direkt so übernehmen und in das entsprechende Zeit<->Pixel Raster stecken
    *** Wenn > 800, dann über eine aggregate-function Werte zusammenfassen und somit auf max. 800 Werte reduzieren.

Aktuell schaut es aber danach aus, als ob ich mit aggregate-functions das nicht hin bekomme, sondern windowing-functions brauche, welche von der geplanten H2 Datenbank aber (noch) nicht unterstützt werden. Mal schauen.

Eine JS-Lib hab ich nicht gewählt. Hab darauf nur indirekt Einfluss, da ich ein quasi fertiges 3rd-Party-Frontend habe und nur im Backend eine entsprechende HTTP-Schnittstelle unterstützen muss um die Daten zu liefern.

7

So, hab mich mal ein wenig mit AggregateFunctions ausseinander gesetzt.

Wie schon befürchtet, ist es damit, also den AggregateFunctions (zumindest für H2 Database) nicht direkt möglich mehr wie eine Ergebniszeile zu produzieren. ABER man könnte damit trotzdem die Daten verdichten:

H2 erlaubt das anlegen von eigenen Aggregate-Functions. Hineingefüttert wird jede Zeile die vom Select-Statement erfasst wird.
In der Funktion kann man nun x Werte zusammenzählen und deren Durchschnitt in einer Liste speichern. Am Ende gibt man die Liste als Array wieder zurück.

Da für eine Grafik nur wenige hundert Werte benötigt werden, dürfte einem das Array auch nicht so schnell um die Ohren fliegen. Die Frage ist nur: Wie schnell ist das?

Hatte ja schon erwähnt dass ich in einem anderen DB mit einem Aufzeichnungszeitraum zurück bis 2011 oder 2012 (weiß n icht mehr genau) 0.8 Mio Zeilen in der DB hatte.

Nun ja, das war ein kleiner Irrtum. Faktisch sind wir aktuell bei rund 68 Mio Zeilen, sprich 68 Mio Messwerten. Für eine Auswertung in der Hausautomation klingt das nach overkill. Aber ich benutze diese Daten mal für einen Test der gebastelten Aggregate-Funktion um zu sehen wie schnell das ganze ist.

Sofern die Geschwindigkeit passt, habe ich noch folgendes Problem:

  1. Ich würde mich mit dieser Lösug erstmal auf H2 festlegen… Wäre aber nicht so tragisch.
  2. Ich muss noch schauen wie ich das mit Messwerten mache, die nicht in jeder Minute einen Messwert vorweisen können.

Bin in SQL nicht mehr ganz so fit. Mag mir da jmd. unter die Arme greifen?

Folgende DB-Struktur:

  • id (int)
  • timestamp (int) <---------- unixtime
  • entityid (int)
  • data (double)

Wie müsste ein SQL-Statement aussehen, das mir für jede volle Minute (Werte treten nur zu vollen Minuten auf) zwischen timestamp A und timestamp B eine Ergebniszeile liefert. Entweder mit dem tatsächlichen Wert, oder falls nicht vorhanden, mit “null”.

Parallel dazu mach ich mich selbst mal auf die Suche …

8

ich kann dir jetzt kein fertiges SELECT Statement bieten, ich würde aber den Timestamp um die Sekunden kürzen und daran gruppieren:
[SQL]SELECT AVG(data), SCHNEIDE_SEKUNDEN_AB(timestamp) from measure where timestamp < ‘01.01.2015 00:00:00’ and timestamp > ‘01.01.2014 00:00:00’ GROUP BY SCHNEIDE_SEKUNDEN_AB(timestamp)[/SQL]

Hier bleibt dir nur noch die Aufgabe für das kürzen um die Sekunden.

9

Das kürzen ist kein Thema.
Aber dein Statement würde den Durchschschnitt aller Werte (hier im Beispiel: (A+B+C)/3 ) für jede einzelne Minute im gewählten Zeitfenster anzeigen.

Ich hab mir mal folgende Beispieldaten angelegt:

A: 1x pro Minute
B: Alle 2 Minuten

SELECT * FROM TEST order by ts, id;
ID  	TS  	                NAME    VALUE  
1	2015-11-24 00:00:00.0	A	1.0
15	2015-11-24 00:00:00.0	B	1.0
2	2015-11-24 00:01:00.0	A	2.0
3	2015-11-24 00:02:00.0	A	2.0
16	2015-11-24 00:02:00.0	B	2.0
4	2015-11-24 00:03:00.0	A	3.0
5	2015-11-24 00:04:00.0	A	4.0
17	2015-11-24 00:04:00.0	B	2.0
6	2015-11-24 00:05:00.0	A	4.0
7	2015-11-24 00:06:00.0	A	5.0
18	2015-11-24 00:06:00.0	B	3.0
8	2015-11-24 00:07:00.0	A	4.0
9	2015-11-24 00:08:00.0	A	4.0
19	2015-11-24 00:08:00.0	B	3.0
10	2015-11-24 00:09:00.0	A	3.0
11	2015-11-24 00:10:00.0	A	2.0
20	2015-11-24 00:10:00.0	B	1.0
12	2015-11-24 00:11:00.0	A	2.0
13	2015-11-24 00:12:00.0	A	1.0
14	2015-11-24 00:12:00.0	A	0.0
21	2015-11-24 00:12:00.0	B	1.0

Mit meiner Aggregationsfunktion geht dann sowas (beispielsweise immer 2 Werte zu einem Durchschnitt zusammenfassen):

SELECT avgwindow(value) FROM TEST where name ='A' ;
AVGWINDOW(VALUE)  
(1.5, 2.5, 4.0, 4.5, 3.5, 2.0, 0.5)

Passt soweit. Für A hätte ich die Daten nun verdichtet. Je zwei Werte bilden einen Durchschnitt.

Geht auch für B:

SELECT avgwindow(value) FROM TEST where name ='B' ;
AVGWINDOW(VALUE)  
(1.5, 2.5, 2.0)

Problem: Beide Abfragen umfassen den selben Zeitraum, aber bei B gibt’s weniger Daten zum errechnen des Durchschnitts, somit ist das Array kürzer :frowning:

Ich müsste, um B sauber in ein gleichlanges Array wie A zu bekommen, auch die nicht vorhandenen Minuten berücksichtigen. Quasi eine Art Join mit einer fiktiven Tabelle in der es jede Minute einen Timestamp , aber keine Daten gibt.
Das hab ich noch nicht hinbekommen.

Es wäre schon hilfreich, wenn ich ein Statement hätte das mir sowas liefert (A und B gleichzeitig, im Minutenraster):

TS  			VALUE_A	VALUE_B  
2015-11-24 00:00:00.0	1.0	1.0
2015-11-24 00:01:00.0	2.0	null
2015-11-24 00:02:00.0	2.0	2.0
2015-11-24 00:03:00.0	3.0	null
2015-11-24 00:04:00.0	4.0	2.0
2015-11-24 00:05:00.0	4.0	null
2015-11-24 00:06:00.0	5.0	3.0
2015-11-24 00:07:00.0	4.0	null
2015-11-24 00:08:00.0	4.0	3.0
2015-11-24 00:09:00.0	3.0	null
2015-11-24 00:10:00.0	2.0	1.0
2015-11-24 00:11:00.0	2.0	null
2015-11-24 00:12:00.0	1.0	1.0
2015-11-24 00:12:00.0	0.0	null

Oder alternativ (nur B, im Minutenraster):

TS  			VALUE_B  
2015-11-24 00:00:00.0	1.0
2015-11-24 00:01:00.0	null
2015-11-24 00:02:00.0	2.0
2015-11-24 00:03:00.0	null
2015-11-24 00:04:00.0	2.0
2015-11-24 00:05:00.0	null
2015-11-24 00:06:00.0	3.0
2015-11-24 00:07:00.0	null
2015-11-24 00:08:00.0	3.0
2015-11-24 00:09:00.0	null
2015-11-24 00:10:00.0	1.0
2015-11-24 00:11:00.0	null
2015-11-24 00:12:00.0	1.0
2015-11-24 00:12:00.0	null

Spätestens mit letzteren könnte ich pro Messwert eine Abfrage starten und bekäme pro Abfrage mit Hilfe der noch zu ergänzenden avgwindow-Funktion ein Array mit der verdichteten Werten.
Die einzelnen Arrays (die im selben Raster liegen) könnte ich dann auf Java-Seite zusammenführen und an die HTML-GUI reichen.

10

@freezly
als gewünschte Eingabe für die ‘Aggregate-Function’ mit ‘einen Wert je Minute’ fehlen da aber die evtl. Minuten ohne Werte,
wobei das immer unschön wird, etwa Join mit einer Tabelle mit allen Minuten aller Zeiten… (5 Mio. pro Jahr…)

was genau leistet denn die ‘Aggregate-Function’ anderes als ein solches AVG mit GROUP BY dynamisch auf das aktuelle Zielintervall?
wenn unter den Ergebnissen Einträge fehlen kann man das beim Befüllen der Chart ja sicher behandeln

11

was genau leistet denn die ‚Aggregate-Function‘ anderes

avg nimmt alle Zeilen einer Spalte und errechnet den kompletten durchschnitt aus. Das Ergebnis ist ein Wert.
meine funktion nimmt immer je zwei aufeinanderfolgende Zeilen einer Spalte und rechnet dafür den durchschnitt aus. Das Ergebnis ist ein Array mit Werten.
Siehe obiges Beispiel.

als ein solches AVG mit GROUP BY dynamisch auf das aktuelle Zielintervall?

Kannst du da vllt. ein Beispiel auf oben genannte Testdaten machen? Komm leider nicht ganz mit…

12

na nur das SQL von freezy auf den gewünschten Bereich, etwa wenn du einen Wert je zwei Minuten willst, dann auf 2 Minuten gruppieren,
den gruppierten Zeitwert freilich mit im SELECT abfragen, das vergrößtert die Daten gegenüber nur Werten

kommt drauf an was diese Unixtime nun genau ist, z.B. moduo 120 sec oder modulo 120.000 ms, evtl. mit ± verschieben,
krumme Schaltsekunden ignoriert Unixtime ja glaube ich wenn kürzlich richtig gelesen? problematisch kann es freilich immer werden

[sql]SELECT AVG(data), unixtime % 120000 from measure where unixtime < … and unixtime >… GROUP BY unixtime % 120000[/SQL]

eine Änderung des Zeitmodells hin auf abstrakte Minutenzählung wäre evtl. hilfreich

bei B =
15 2015-11-24 00:00:00.0 B 1.0
16 2015-11-24 00:02:00.0 B 2.0
17 2015-11-24 00:04:00.0 B 2.0
kommt genau 1 Wert pro Doppelminute, sowieso passend,

falls mal auf eine Minute gruppiert, dann wäre das Ergebnis in etwa
Minute 0, Wert 1
Minute 2, Wert 2
Minute 4, Wert 2
usw., mit fehlenden Daten dazwischen,

hier müsste nun der Java-Client intelligent reagieren,
die Daten im Chartmodell mit Minute 0, 1, 2, 3, 4 usw. passend eintragen, die Lücken bei 1, 3 usw. leer lassen

13

Wenn ich das richtig verstehe willst du die NULL Werte für B mit Werten füllen. Aber welche Logik soll dahinter stecken? Ich kann gerade keine sinnvolle Logik erkennen, die aus Nichts etwas macht. Einzig, den letzten Wert nehmen. Aber das ist eine Logik, die du nicht in der Abfrage haben willst. Das wird bestimmt sehr langsam und kompliziert.

*** Edit ***

Ich stelle mir gerade die Frage, wieso unbedingt Werte vorhanden sein müssen, wenn keine da sind. Wenn ich einen Zeitraum wähle, in dem keine Daten vorhanden sind, dann wird kein Graph gezeichnet. Wenn ich einen Zeitraum wähle, wo nur ein Datenpunkt für B da wäre, dann wird dieser gezeichnet. Habe ich 2 Werte, dann habe ich eine durchgezogene Linie. Bei mehr Werten einen entsprechenden Graph. Das entspricht ja auch der Wirklichkeit. Wenn du dir Zwischenpunkte ausdenkst, können die nur falsch sein und erfüllt dein Diagramm nicht mit mehr Leben, als eh da ist.

Wenn dann ein Graph 20mal mehr Datenpunkte hat, als der andere, wieso muss der User dieselbe Anzahl sehen?

14

Wenn ich das richtig verstehe willst du die NULL Werte für B mit Werten füllen.

Fast. Ich hätte gerne in der Minute, in der B keinen Wert in der DB hat einen Null-Wert.

Hintergrund:

Die Schnittstelle zu HTML-GUI besagt:

Zeitstempel1, Messwert_A, Messwert_B, Messwert_C…
Zeitstempel2, Messwert_A, Messwert_B, Messwert_C…
Zeitstempel3, Messwert_A, Messwert_B, Messwert_C…
…

Wenn ich jetzt für A für jede Minute einen Wert habe und für B nicht, dann muss ich ja wissen wann ich für B „null“ einsetzen muss. Wenn mir die DB das sagen kann wäre das toll. Wenn nicht, dann halt nicht.

SlaterB’s letzten Post muss ich ein zweites mal lesen… Bedauere es gerade sehr dass ich in den letzten Jahren fast keinen Kontakt mit SQL hatte.

Sehe da noch nicht wo avg für A und B jeweils getrennt aus zwei Zeilen den Durchschnitt (quasi (A+A’)/2) errechnet.

15

die Zwischenwerte sind nicht für die Anzahl da, sondern für korrekte zeitliche Zuordnung,
angenommen eine Graphik zeigt 6 Werte für 2010, 2011, 2012, 2013, 2014, 2015

dann ist eine Ergebnisliste mit 6 Werten ohne Zeitangaben [4, 7, 6, 2, 3, 3] ziemlich passend,
aber nur zwei Werte [4, 3] ohne Angaben welches Jahr jeweils, das ist nix,

dagegen wäre wiederum [4, 0, 0, 0, 0, 3] ok, die vier 0en nicht um viele Punkte anzuzeigen zu haben, sondern damit die 3 korrekt dem Jahr 2015 zugeordnet ist,

ein DB-Ergebnis-Liste
2010, 4
2015, 3
wäre freilich genauso eindeutig, könnte für die Graphik korrekt verarbeitet werden

die Aggregationsfunktion von tuxedo wollte hier eben auch ein Array,
hat seine Vorteile (nicht auf Zeitpunkte zu schauen, Vollständigkeit angenommen), aber auch seine Nachteile

*** Edit ***

da ging es bei mir auch nur um B und die Frage nach Zeiten, fehlenden Einträgen,
nach A und B ist getrennt zu gruppieren, das ist doch wieder leicht,

wenn als Attribut, getrennte Einträge in der Tabelle,

abType;unixtime;data
A;5433;2
B;5433;5
A;6433;7
A;7534;9
B;7534;8

→ dann etwa
[sql]SELECT AVG(data), abType, unixtime % 120000 from measure where unixtime < … and unixtime >… GROUP BY abType, unixtime % 120000[/sql]
wie man auch alles andere gruppiert

wenn zwei verschiedene Spalten,

unixtime;dataA;dataB
5433;2;5
6433;7;?
7534;9;8

→ dann zwei Average zu bilden
[sql]SELECT AVG(dataA), AVG(dataB), unixtime % 120000 from measure where unixtime < … and unixtime >… GROUP BY unixtime % 120000[/sql]

16

Bastle gerade mit H2. Den Modulo-Operator kann ich innerhalb des Selects verwenden. Aber nicht im Group-By.

Hab ich ihn nur im Select, dann rechner avg() wieder den Durchschnitt von A+B aus.
Glaub ich muss das mal noch in MySQL und Postgres testen.

17

ohne Modulo oder Substring oder sonstiges zu Gruppieren wäre man SQL-technisch wirklich arg begrenzt…
Count all employees by even/odd employee id : GROUP BY*«Select Query«*Oracle PL / SQL

“wieder den Durchschnitt von A+B aus.” kann ich noch weniger nachvollziehen, falls A oder B ein einfaches Attribut ist,
danach zu gruppieren, getrennt auszuwerden, ist ja nun das A und O (wenn nicht A und B) einer jeden Datenbank…

[sql]select X, count(*) from Y group by X [/sql] tippe ich oft mehrmals am Tag ein…,
damit etwa hier herausfinden, dass 2000 Einträge vom Typ A, 1000 Einträge vom Typ B usw.,

und alle anderen Funktionen müssen dann selbstverständlich auch getrennt rechnen

18

Vielleicht mach ich ja auch was falsch oder die Syntax bei H2 ist ein wenig anders. Kommt mir auch spanisch vor.

[SQL]select ts, avg(value) from test group by ts[/SQL]

funktioniert ja auch an und für sich, aber rechnet eben für alle Werte von A und B die auf dem selben Zeitstempel liegen den durchschnitt aus.

[SQL]select ts%120, avg(value) from test group by ts % 120[/SQL]

Funktioniert (nun) seltsamerweise auch (kein Plan wieso das jetzt auf einmal geht), liefert aber auch nicht das gewünschte Ergebnis: Hier hab ich das gleiche wie eben, nur eben alle 2min den Durschnitt von A+B

Was ich brauche ist der Durchschnit von A bei Minute x + A bei Minute x+1, sowie B bei Minute x + B bei Minute x+1

Oder zur Not in getrennten Statements für A und B, aber auf jedenfall den Durschschnitt von Wert bei Minute x + Wert bei Minutex+1.

19

[QUOTE=tuxedo]Fast. Ich hätte gerne in der Minute, in der B keinen Wert in der DB hat einen Null-Wert.

Hintergrund:

Die Schnittstelle zu HTML-GUI besagt:

Zeitstempel1, Messwert_A, Messwert_B, Messwert_C…
Zeitstempel2, Messwert_A, Messwert_B, Messwert_C…
Zeitstempel3, Messwert_A, Messwert_B, Messwert_C…
[/QUOTE]

Du musst doch sicherlich die Werte in ein anderes Format wandeln, wenn du es an den Browser schicken willst. Wenn du z.B. Json nutzt, dann kannst du ja per Map<Zeitstempel,JsonObject> deine Json Objekte zusammenstellen und dann erst an den Browser schicken. Fehlende Werte ergänzt du vor dem eigentlichen Senden dann einfach durch null.

20

Du hinkst hinterher :wink: Logo kann ich im Java-Code fehlende Werte ergänzen mit „null“ füllen.
Die Idee war das die DB erledigen zu lassen. Und falls die das nicht kann, dann kann ich’s immer noch im Java-Code machen. ABER dazu muss ich wissen zu welcher Minute kein Wert vorliegt. Und mit meiner Aggregate-Funktion geht das nicht, die spuckt ja nur die „komprimierten“ Werte ohne zeitlichen Zusammenhang aus.

Habe, abgesehen von der AggregateFunktion noch keinen funktionierenden Ansatz gesehen mit dem ich zwei (oder mehr) Werte UNTEREINANDER zu einem Durchschnittswert zusammenrechnen kann.
Entweder Slater’s Ansatz funktioniert nur bei größeren DBs, oder es steckt noch der Wurm drin.

Postgres kann das über eine Window-Funktion (Probably the Coolest SQL Feature: Window Functions | Java, SQL and jOOQ.). Da kann man sagen:

Gib mit Alle Werte im Zeitraum von X bis Y, aber fasse immer 5 (preceding 5) Zeilen zusammen und rechne für Spalte ‚foobar‘ den Durchschnittswert aus.

Leider kann H2 das nicht, und nachbilden der funtionalität erweist sich gerade als schwierig.