Filtern von numerischen Werten <> NaN

[Groffy]

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

[martin.koeditz]

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:

select MIN(VALUE_DOUBLE), MAX(VALUE_DOUBLE, AVG(VALUE_DOUBLE)
where VALUE_DOUBLE IS NOT NULL

Gruß
Martin

[Groffy]

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

Code: Alles auswählen

select ... where DOUBLE_VALUE is not NULL 

funktioniert leider nicht




Gruß Ulrich

[vr2]

Hallo Ulrich,

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))

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

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

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

[Groffy]

Moin Volker,

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)

hingegen nicht funktioniert.

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

Ausführungszeit ca. 100ms/140k Werte

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))

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

[Groffy]

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.

Code: Alles auswählen

create or alter function DOUBLE_IS_NAN (doublevalue double precision)
returns boolean
EXTERNAL NAME 'externaludr!double_is_NaN' ENGINE UDR

Das Einbinden in das select Statement ist sehr einfach, ich hatte vorher aber noch nie eine Function in einem where - Statement eingebunden.

Code: Alles auswählen

SELECT min(VALUE_DOUBLE), max(VALUE_DOUBLE), avg(VALUE_DOUBLE)
FROM RESULTS
where not DOUBLE_IS_NAN(VALUE_DOUBLE)

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:

Code: Alles auswählen

select List(VALUE_DOUBLE, '|')
from RESULTS

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

[vr2]

Moin Ulrich,

hast Du mal statt

Code: Alles auswählen

where CAST(VALUE_DOUBLE as VARCHAR(20)) <> CAST(log(1,1) as VARCHAR(20))

so

Code: Alles auswählen

where CAST(VALUE_DOUBLE as VARCHAR(20)) <> 'nan(ind)'

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

[bfuerchau]

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.

[Groffy]

Moin Volker,

Code: Alles auswählen

where CAST(VALUE_DOUBLE as VARCHAR(20)) <> 'nan(ind)'

das funktioniert auch, und ist mit ca. 220ms/140k Werten etwas schneller.


Gruß Ulrich

[Groffy]

Hallo bfuerchau,

min( case when position('IND', left(value, 10)) > 0 then null else value end )

vielen Dank für den interessanten Ansatz.

Mit

select 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

komme ich auf ca. 560ms/140k Werte