Ich versuche, kumulative Multiplikation durchzuführen. Ich versuche zwei Methoden, dies zu tun
Hinweis: Die Daten in value -Spalte werden niemals ganzzahlig sein und die Werte werden Dezimalstellen haben. Um das Approximationsproblem zu zeigen, habe ich Beispielwerte als ganze Zahlen behalten.
Bei dieser Methode verwende EXP + LOG + SUM() Over(Order by) Technik, um die kumulative Multiplikation zu finden. In dieser Methode sind Werte nicht genau; Es gibt ein Rundungs- und Approximationsproblem im Ergebnis.
Diese Methode funktioniert perfekt ohne Rundungs- oder Approximationsprobleme.
%Vor% Kann mir jemand sagen, warum in Methode 1 Werte nicht genau sind und wie man sie beheben kann? Ich versuchte, indem ich die Datentypen zu Float und durch das Erhöhen der scale in numeric , aber keine Verwendung änderte.
Ich möchte wirklich Methode 1 verwenden, die viel schneller als Methode 2 ist.
Bearbeiten: Jetzt kenne ich den Grund für die Approximation. Kann jemand eine Lösung für dieses Problem finden?
In reinem T-SQL arbeiten LOG und EXP mit dem Typ float (8 Bytes), der nur 15-17 signifikante Ziffern . Selbst die letzte 15. Stelle kann ungenau werden, wenn Sie genügend große Werte addieren. Ihre Daten sind numeric(22,6) , also reichen 15 signifikante Ziffern nicht aus.
POWER kann numeric type mit einer potenziell höheren Genauigkeit zurückgeben, aber das ist für uns wenig hilfreich, da sowohl LOG als auch LOG10 trotzdem nur float zurückgeben können.
Um das Problem zu demonstrieren, ändere ich den Typ in Ihrem Beispiel in numeric(15,0) und verwende POWER anstelle von EXP :
Ergebnis
%Vor%Jeder Schritt hier verliert an Genauigkeit. Das Berechnen von LOG verliert die Präzision, SUM verliert die Präzision, EXP / POWER verliert die Präzision. Mit diesen integrierten Funktionen glaube ich nicht, dass Sie viel dagegen tun können.
Also, die Antwort ist - verwenden Sie CLR mit C # decimal Typ (nicht double ), die höhere Genauigkeit unterstützt (28-29 signifikante Ziffern). Ihr ursprünglicher SQL-Typ numeric(22,6) würde darin passen. Und Sie brauchen den Trick nicht mit LOG/EXP .
Hoppla! Ich habe versucht, ein CLR-Aggregat zu erstellen, das Product berechnet. Es funktioniert in meinen Tests, aber nur als ein einfaches Aggregat, d.h.
Das funktioniert:
%Vor% Und sogar OVER (PARTITION BY) funktioniert:
Das Ausführen von product mit OVER (PARTITION BY ... ORDER BY ...) funktioniert jedoch nicht (überprüft mit SQL Server 2014 Express 12.0.2000.8):
Falsche Syntax in der Nähe des Schlüsselwortes 'ORDER'.
Eine Suche ergab, dass Element verbinden , das als geschlossen ist "Wird nicht behoben" und diese Frage .
Der C # -Code:
%Vor%Installieren Sie die CLR-Assembly:
%Vor%Diese Frage behandelt die Berechnung einer laufenden SUM in Tolle Details und Paul White zeigt in seiner Antwort , wie man eine CLR-Funktion schreibt, die den laufenden SUM effizient berechnet. Es wäre ein guter Anfang für das Schreiben einer Funktion, die laufendes Produkt berechnet.
Beachten Sie, dass er einen anderen Ansatz verwendet. Anstatt eine benutzerdefinierte Funktion aggregiert zu erstellen, erstellt Paul eine Funktion, die eine Tabelle zurückgibt. Die Funktion liest die Originaldaten in den Speicher und führt alle erforderlichen Berechnungen durch.
Es kann einfacher sein, den gewünschten Effekt zu erzielen, indem Sie diese Berechnungen auf Ihrer Client-Seite mit einer Programmiersprache Ihrer Wahl durchführen. Lesen Sie einfach die gesamte Tabelle und berechnen Sie das laufende Produkt auf dem Client. Das Erstellen der CLR-Funktion ist sinnvoll, wenn das auf dem Server berechnete laufende Produkt ein Zwischenschritt in komplexeren Berechnungen ist, die Daten weiter aggregieren würden.
Eine weitere Idee, die Ihnen einfällt.
Finden Sie eine .NET-Mathematikbibliothek von Drittanbietern, die die Funktionen Log und Exp mit hoher Genauigkeit anbietet. Erstellen Sie eine CLR-Version dieser skalaren -Funktionen. Verwenden Sie dann die Methode EXP + LOG + SUM() Over (Order by) , wobei SUM die integrierte T-SQL-Funktion ist, die Over (Order by) und Exp und Log benutzerdefinierte CLR-Funktionen unterstützt, die nicht float , sondern hohe Werte zurückgeben. Genauigkeit decimal .
Beachten Sie, dass Berechnungen mit hoher Genauigkeit auch langsam sein können. Und die Verwendung von CLR-Skalarfunktionen in der Abfrage kann es auch verlangsamen.
Tags und Links sql sql-server sql-server-2008 sql-server-2012