Hallo,
ich habe eine Tabelle mit numerischen Werten (DOUBLE PRECISION), die aus einer Auswertung von Messdaten stammen, und möchte MIN(), MAX(), AVG() abfragen, aber es sind NaN Werte in den Datenfeldern, wie bekomme ich die rausgefiltert?
select MIN(VALUE_DOUBLE), MAX(VALUE_DOUBLE, AVG(VALUE_DOUBLE)
where VALUE_DOUBLE <> NaN
wird nicht ausgeführt, da NaN anscheinend in SQL unbekannt ist.
Auch mit einem CAST('NaN' as DOUBLE PRECISION) wird das Statement nicht ausgeführt
Gruß Ulrich
Filtern von numerischen Werten <> NaN
Moderator: thorben.braun
- martin.koeditz
- Beiträge: 447
- Registriert: Sa 31. Mär 2018, 14:35
Guten Morgen Ulrich,
ich nehme an, dass 'NaN' in deiner Client-Applikation ausgegeben wird. In SQL existiert der "Wert" NULL. Dieser funktioniert jedoch nicht mit den üblichen Operatoren (=, <, >, ...) sondern mit IS und IS NOT.
Deine Abfrage könnte also so aussehen:
Martin
ich nehme an, dass 'NaN' in deiner Client-Applikation ausgegeben wird. In SQL existiert der "Wert" NULL. Dieser funktioniert jedoch nicht mit den üblichen Operatoren (=, <, >, ...) sondern mit IS und IS NOT.
Deine Abfrage könnte also so aussehen:
Grußselect MIN(VALUE_DOUBLE), MAX(VALUE_DOUBLE, AVG(VALUE_DOUBLE)
where VALUE_DOUBLE IS NOT NULL
Martin
Martin Köditz
it & synergy GmbH
it & synergy GmbH
Hallo Martin,
es ist nach einiger Suche etwas verwirrend. 'NaN' ist tatsächlich laut IEEE ein gültiger Wert für den Datentyp double und kann auch in einem Firebird Datenfeld gespeichert werden, ist als nicht NULL. Der Wert wird z.B. auch zurückgegeben für log(1,1) (Firebird 4 Language Reference Seite 424). Auf Clientenseite war das bislang auch kein Problem, der Firebird .Net Client liefert den Wert Double.NaN (nicht NULL!) zurück. Jetzt bin ich aber auf die Idee gekommen, bei großen Mengen von Werten (200.000-300.000 und steigend) die Min/Max/AVG Werte direkt im Server abzurufen, anstatt erst alle Werte in die Client Applikation zu schaufeln. Ich mußte dann aber feststellen, dass die Filterung nicht so einfach ist wie ich das erwartet hatte. Ich könnte mir fix eine UDR zusammenstricken, aber ich befürchte da massive Performance Einbrüche, wenn für jedes select Statement die UDR-Funktion aufgerufen werden würde (Ich werds aber trotzdem mal probieren - aus Neugierde)
Es gibt noch 1-2 denkbare Workarounds, aber mal schauen, vielleicht findet sich ja noch eine Lösung.
Dein Vorschlag mit
funktioniert leider nicht
![Bild](NaN.PNG)
Gruß Ulrich
es ist nach einiger Suche etwas verwirrend. 'NaN' ist tatsächlich laut IEEE ein gültiger Wert für den Datentyp double und kann auch in einem Firebird Datenfeld gespeichert werden, ist als nicht NULL. Der Wert wird z.B. auch zurückgegeben für log(1,1) (Firebird 4 Language Reference Seite 424). Auf Clientenseite war das bislang auch kein Problem, der Firebird .Net Client liefert den Wert Double.NaN (nicht NULL!) zurück. Jetzt bin ich aber auf die Idee gekommen, bei großen Mengen von Werten (200.000-300.000 und steigend) die Min/Max/AVG Werte direkt im Server abzurufen, anstatt erst alle Werte in die Client Applikation zu schaufeln. Ich mußte dann aber feststellen, dass die Filterung nicht so einfach ist wie ich das erwartet hatte. Ich könnte mir fix eine UDR zusammenstricken, aber ich befürchte da massive Performance Einbrüche, wenn für jedes select Statement die UDR-Funktion aufgerufen werden würde (Ich werds aber trotzdem mal probieren - aus Neugierde)
Es gibt noch 1-2 denkbare Workarounds, aber mal schauen, vielleicht findet sich ja noch eine Lösung.
Dein Vorschlag mit
Code: Alles auswählen
select ... where DOUBLE_VALUE is not NULL
Gruß Ulrich
- Dateianhänge
-
- NaN.PNG (15.52 KiB) 13311 mal betrachtet
Hallo Ulrich,
NaN (ungültiger Gleitkommawert) und null (unbekannt, nie gesetzt) ist nicht dasselbe. Die Auswertung bei NaN ist bisschen sperrig. Versuche mal das:
Du kannst NaN nach varchar konvertieren, und dann sind sie vergleichbar. Dabei wird das Vorzeichen entfernt, was für den Vergleich ja gut ist. Alle anderen Versuche, NaNs zu vergleichen, führten bei meinen Tests nicht weit. Die auskommentierte erste select-Zeile steuert noch die Varianten -inf und inf bei, falls Du das auch brauchst.
Getestet hab ich mit Firebird 4.0.4, sollte auch in 3.0.8 und ab 4.0.0 so funktionieren.
Bezogen auf Dein Beispielselect
Links in dem Dunstkreis:
https://www.tabsoverspaces.com/233631-w ... n-firebird
https://github.com/FirebirdSQL/firebird/issues/6750
https://github.com/FirebirdSQL/firebird/issues/6463
Hatte auch mal mit hex_decode oder compare_decfloat experimentiert, aber ohne Erfolg.
Grüße, Volker
NaN (ungültiger Gleitkommawert) und null (unbekannt, nie gesetzt) ist nicht dasselbe. Die Auswertung bei NaN ist bisschen sperrig. Versuche mal das:
Code: Alles auswählen
-- select log(1, 1), log(1, 0.5), log(1, 1.5) from rdb$database
select cast(log(1, 1) as float), cast(log(1, 1) as varchar(20))
from rdb$database
-- where cast(log(1, 1) as float) is distinct from cast(log(1, 1) as float)
where cast(log(1, 1) as varchar(20)) = cast(log(1, 1) as varchar(20))
Getestet hab ich mit Firebird 4.0.4, sollte auch in 3.0.8 und ab 4.0.0 so funktionieren.
Bezogen auf Dein Beispielselect
Code: Alles auswählen
select min(value_double), max(value_double, avg(value_double)
where cast(value_double as varchar(20)) <> 'nan(ind)' -- oder cast(log(1, 1) as varchar(20)) o.ä., was NaN erzeugt
https://www.tabsoverspaces.com/233631-w ... n-firebird
https://github.com/FirebirdSQL/firebird/issues/6750
https://github.com/FirebirdSQL/firebird/issues/6463
Hatte auch mal mit hex_decode oder compare_decfloat experimentiert, aber ohne Erfolg.
Grüße, Volker
Moin Volker,
vielen Dank, das Umwandeln auf string funktioniert tatsächlich! Ich war etwas überrascht, dass der direkte Vergleich
hingegen nicht funktioniert.
Das Umwandeln aller double Werte in strings hat natürlich seinen Preis:
Ausführungszeit ca. 100ms/140k Werte
Ausführungszeit ca. 300ms/140k Werte
Wobei die absolute Zeit sehr langsam ist, ich hab hier nur einen uralt Win10 PC. Aktuelle Hardware ist da sicherlich bedeutend schneller. Es geht hier nur um das Verhältnis. Falls relevant - es läuft der Firebird 5-RC1
Beste Grüße - Ulrich
vielen Dank, das Umwandeln auf string funktioniert tatsächlich! Ich war etwas überrascht, dass der direkte Vergleich
Code: Alles auswählen
select ... from RESULTS where DOUBLE_VALUE <> log(1,1)
Das Umwandeln aller double Werte in strings hat natürlich seinen Preis:
Code: Alles auswählen
SELECT min(VALUE_DOUBLE), max(VALUE_DOUBLE), avg(VALUE_DOUBLE)
FROM RESULTS
Code: Alles auswählen
SELECT min(VALUE_DOUBLE), max(VALUE_DOUBLE), avg(VALUE_DOUBLE)
FROM RESULTS
where CAST(VALUE_DOUBLE as VARCHAR(20)) <> CAST(log(1,1) as VARCHAR(20))
Wobei die absolute Zeit sehr langsam ist, ich hab hier nur einen uralt Win10 PC. Aktuelle Hardware ist da sicherlich bedeutend schneller. Es geht hier nur um das Verhältnis. Falls relevant - es läuft der Firebird 5-RC1
Beste Grüße - Ulrich
Nachtrag:
Ich konnte es mir dann nicht verkneifen eine UDR Function (DOUBLE_IS_NAN) in Free Pascal zu implementieren, die intern eigentlich nur aus einer Zeile Pascal Code besteht.
Das Einbinden in das select Statement ist sehr einfach, ich hatte vorher aber noch nie eine Function in einem where - Statement eingebunden.
Ausführungszeit ca. 1.1s/140k Werte
Naja, aber so schnell wollte ich nicht aufgeben, und habe mir gedacht, dass ich dann eben eine UDR Procedure nur 1x aufrufe und alle 140k Werte direkt an die UDR übergebe um Min/Max und Durchschnitt zu berechnen. Dazu müßte ich aber alle numerischen Werte in einen Text Blob packen und das geht sehr schön mit dem select List() statement:
wobei ich das '|' Zeichen als Trenner zwischen den double Werten verwende.
Ich war dann doch etwas enttäuscht von der Performance des Pascal Codes
Ausführungszeit ca. 800-900ms/140k Werte
Ok, ich berichtige mich - wenn ich die UDR im Relase Modus kompiliere wird der Code deutlich schneller:
Ausführungszeit ca. 100ms/140k Werte
Na bitte - geht doch![Lächeln :)](./images/smilies/icon_e_smile.gif)
Ich konnte es mir dann nicht verkneifen eine UDR Function (DOUBLE_IS_NAN) in Free Pascal zu implementieren, die intern eigentlich nur aus einer Zeile Pascal Code besteht.
Code: Alles auswählen
create or alter function DOUBLE_IS_NAN (doublevalue double precision)
returns boolean
EXTERNAL NAME 'externaludr!double_is_NaN' ENGINE UDR
Code: Alles auswählen
SELECT min(VALUE_DOUBLE), max(VALUE_DOUBLE), avg(VALUE_DOUBLE)
FROM RESULTS
where not DOUBLE_IS_NAN(VALUE_DOUBLE)
Naja, aber so schnell wollte ich nicht aufgeben, und habe mir gedacht, dass ich dann eben eine UDR Procedure nur 1x aufrufe und alle 140k Werte direkt an die UDR übergebe um Min/Max und Durchschnitt zu berechnen. Dazu müßte ich aber alle numerischen Werte in einen Text Blob packen und das geht sehr schön mit dem select List() statement:
Code: Alles auswählen
select List(VALUE_DOUBLE, '|')
from RESULTS
Ich war dann doch etwas enttäuscht von der Performance des Pascal Codes
Ausführungszeit ca. 800-900ms/140k Werte
Ok, ich berichtige mich - wenn ich die UDR im Relase Modus kompiliere wird der Code deutlich schneller:
Ausführungszeit ca. 100ms/140k Werte
Na bitte - geht doch
![Lächeln :)](./images/smilies/icon_e_smile.gif)
Moin Ulrich,
hast Du mal statt
so
getestet? Wie ist denn da der Durchsatz? Ich weiß, sieht schräg aus, aber das Literal rechts im Vergleich müsste performanter sein als die Expression, log ist außerdem eine aufwändige Funktion. Auf 'nan(ind)' komme ich, weil Flamerobin das so ausgibt und es auch als Vergleichswert funktioniert hat (also unabhängig vom Client und seiner Interpretation der Rückgabewerte sein müsste), deswegen stand das oben auch in meinem Abfragebeispiel.
Zum UDR-Ansatz: UDRs haben deutlich mehr Aufruf-Overhead als die alten udfs, da UDRs den Datenbankkontext bereitstellen. Ich hab deswegen bei einem der Entwickler nachgefragt, ob man das nicht in der UDR bei Bedarf abschalten kann, für UDRs, die keinen DB-Kontext brauchen, wie bspw Martins Levenshtein-Funktion neulich. Oder hier eben der NaN-Tester.
Grüße, Volker
hast Du mal statt
Code: Alles auswählen
where CAST(VALUE_DOUBLE as VARCHAR(20)) <> CAST(log(1,1) as VARCHAR(20))
Code: Alles auswählen
where CAST(VALUE_DOUBLE as VARCHAR(20)) <> 'nan(ind)'
Zum UDR-Ansatz: UDRs haben deutlich mehr Aufruf-Overhead als die alten udfs, da UDRs den Datenbankkontext bereitstellen. Ich hab deswegen bei einem der Entwickler nachgefragt, ob man das nicht in der UDR bei Bedarf abschalten kann, für UDRs, die keinen DB-Kontext brauchen, wie bspw Martins Levenshtein-Funktion neulich. Oder hier eben der NaN-Tester.
Grüße, Volker
Wie wäre es mit:
min( case when position('IND', left(value, 10)) > 0 then null else value end )
left(value, 10) liefert bei NAN '1.#IND0000', so dass man die Position abfragen kann.
Innerhalb eines Aggregates kann man auch Ausdrücke anwenden und NULL-Werte werden nicht berücksichtigt.
Das Ergebnis wird bei 500.000 Zeilen in knapp 2 Sekunden geliefert.
Das finde ich ordentlich.
Der Vorteil ist, dass sich das je Spalte direkt anwenden lässt und ein Where unnötig wird.
min( case when position('IND', left(value, 10)) > 0 then null else value end )
left(value, 10) liefert bei NAN '1.#IND0000', so dass man die Position abfragen kann.
Innerhalb eines Aggregates kann man auch Ausdrücke anwenden und NULL-Werte werden nicht berücksichtigt.
Das Ergebnis wird bei 500.000 Zeilen in knapp 2 Sekunden geliefert.
Das finde ich ordentlich.
Der Vorteil ist, dass sich das je Spalte direkt anwenden lässt und ein Where unnötig wird.
Hallo bfuerchau,
Mit
vielen Dank für den interessanten Ansatz.
Mit
komme ich auf ca. 560ms/140k Werteselect min(case when position('ind', left(VALUE_DOUBLE, 10))>0 then null else VALUE_DOUBLE end),
max(case when position('ind', left(VALUE_DOUBLE, 10))>0 then null else VALUE_DOUBLE end),
avg(case when position('ind', left(VALUE_DOUBLE, 10))>0 then null else VALUE_DOUBLE end)
from results