Numerische Daten mit beliebiger Genauigkeit speichern

Spanner bietet den Typ NUMERIC, mit dem Zahlen mit dezimaler Genauigkeit genau gespeichert werden können. Die Semantik des Typs NUMERIC in Spanner variiert zwischen den beiden SQL-Dialekten (GoogleSQL und PostgreSQL), insbesondere hinsichtlich der Einschränkungen bei Skalierung und Genauigkeit:

  • NUMERIC im PostgreSQL-Dialekt ist ein numerischer Typ mit beliebiger dezimaler Genauigkeit (Skalierung oder Genauigkeit können eine beliebige Zahl innerhalb des unterstützten Bereichs sein) und daher ideal zum Speichern numerischer Daten mit beliebiger Genauigkeit geeignet.

  • NUMERIC in GoogleSQL ist ein numerischer Typ mit fester Genauigkeit (Precision=38 und scale=9) und kann nicht zum Speichern numerischer Daten mit beliebiger Genauigkeit verwendet werden. Wenn Sie Zahlen mit beliebiger Genauigkeit in Datenbanken mit GoogleSQL-Dialekten speichern müssen, empfehlen wir, sie als Strings zu speichern.

Genauigkeit numerischer Spanner-Typen

Genauigkeit ist die Anzahl der Ziffern in einer Zahl. Skalieren ist die Anzahl der Ziffern rechts vom Dezimalzeichen in einer Zahl. Die Zahl 123,456 hat beispielsweise eine Genauigkeit von 6 und eine Skala von 3. Spanner hat drei numerische Typen:

  • Vorzeichenbehafteter 64-Bit-Ganzzahltyp mit dem Namen INT64 im GoogleSQL-Dialekt und INT8 im PostgreSQL-Dialekt.
  • Gleitkommatyp mit IEEE 64-Bit (doppelt) mit binärer Genauigkeit namens FLOAT64 im GoogleSQL-Dialekt und FLOAT8 im PostgreSQL-Dialekt.
  • Typ NUMERIC mit Dezimalgenauigkeit.

Sehen wir uns die Genauigkeit und Skalierung an.

INT64 / INT8 stellen numerische Werte dar, die keine Bruchkomponente haben. Dieser Datentyp bietet eine Genauigkeit von 18 Stellen mit einer Skala von null.

FLOAT64 / FLOAT8 kann nur ungefähre Dezimalwerte mit Bruchkomponenten darstellen und bietet 15 bis 17 wichtige Ziffern (Anzahl der Ziffern in einer Zahl, wobei alle nachgestellten Nullen entfernt wurden) mit dezimaler Genauigkeit. Wir nehmen an, dass dieser Typ ungefähre numerische Dezimalwerte darstellt, da die binäre IEEE-64-Bit-Gleitkommadarstellung, die Spanner verwendet, Dezimalbrüche (Basis 10) nicht genau darstellen kann (er kann nur Brüche mit Basis 2 genau darstellen). Dieser Genauigkeitsverlust führt bei einigen Dezimalbrüchen zu Rundungsfehlern.

Wenn Sie beispielsweise den Dezimalwert 0,2 mit dem Datentyp FLOAT64 / FLOAT8 speichern, wird die Binärdarstellung wieder in einen Dezimalwert von 0,20000000000000001 (mit einer Genauigkeit von 18 Stellen) konvertiert. Auf ähnliche Weise wird (1,4 * 165) wieder in 230,999999999999971 und (0,1 + 0,2) wieder in 0,30000000000000004 konvertiert. Aus diesem Grund werden 64-Bit-Gleitkommazahlen mit 15 bis 17 Stellen mit hoher Genauigkeit beschrieben (nur einige Zahlen mit mehr als 15 Dezimalstellen können als 64-Bit-Gleitkommazahl ohne Rundung dargestellt werden). Weitere Informationen zur Berechnung der Gleitkommagenauigkeit finden Sie unter Gleitkommazahl mit doppelter Genauigkeit.

Weder INT64 / INT8 noch FLOAT64 / FLOAT8 bietet die ideale Genauigkeit für finanzielle, wissenschaftliche oder technische Berechnungen, bei denen üblicherweise eine Genauigkeit von 30 Ziffern oder mehr erforderlich ist.

Der Datentyp NUMERIC eignet sich für diese Anwendungen, da er numerische Werte mit exakter dezimaler Genauigkeit mit einer Genauigkeit von mehr als 30 Dezimalstellen darstellen kann.

Mit dem GoogleSQL-Datentyp NUMERIC können Zahlen mit einer festen Dezimalgenauigkeit von 38 und einer festen Skala von 9 dargestellt werden. Der Bereich von GoogleSQL NUMERIC ist -99999999999999999999999999,999999999 bis 99999999999999999999999999.999999999.

Der PostgreSQL-Dialekt NUMERIC kann Zahlen mit einer maximalen Dezimalgenauigkeit von 147.455 und einer maximalen Skalierung von 16.383 darstellen.

Wenn Sie Zahlen speichern müssen, die größer als die Genauigkeit und Skalierung sind, die von NUMERIC angeboten werden, werden in den folgenden Abschnitten einige empfohlene Lösungen beschrieben.

Empfehlung: Beliebige Zahlen als Strings speichern

Wenn Sie eine Zahl mit beliebiger Genauigkeit in einer Spanner-Datenbank speichern müssen und mehr Genauigkeit benötigen als NUMERIC bietet, empfehlen wir, den Wert als dezimale Darstellung in einer STRING / VARCHAR-Spalte zu speichern. Beispielsweise wird die Zahl 123.4 als String "123.4" gespeichert.

Bei diesem Ansatz muss Ihre Anwendung eine verlustfreie Konvertierung zwischen der anwendungsinternen Darstellung der Zahl und dem Spaltenwert STRING / VARCHAR für Lese- und Schreibvorgänge von Datenbanken ausführen.

Die meisten Bibliotheken mit beliebiger Genauigkeit haben für die Durchführung dieser verlustfreien Konvertierung integrierte Methoden. In Java können Sie beispielsweise die Methode BigDecimal.toPlainString() und den Konstruktor BigDecimal(String) verwenden.

Das Speichern der Zahl als String hat den Vorteil, dass der Wert genau (bis zur Spaltenlängenbeschränkung STRING / VARCHAR) gespeichert wird und weiterhin für Menschen lesbar ist.

Exakte Aggregationen und Berechnungen durchführen

Es ist erforderlich, dass die Anwendung die folgenden Berechnungen durchführt, damit exakte Aggregationen und Berechnungen für die Darstellung von Zeichenfolgen für Zahlen mit beliebiger Genauigkeit ausgeführt werden können. Sie können keine SQL-Aggregatfunktionen verwenden.

Wenn Sie beispielsweise das Äquivalent eines SQL-SUM(value) über einen Zeilenbereich ausführen möchten, muss die Anwendung die Stringwerte für die Zeilen abfragen, dann konvertieren und intern in der Anwendung addieren.

Ungefähre Aggregationen, Sortierungen und Berechnungen durchführen

Sie können SQL-Abfragen verwenden, um ungefähre Berechnungen von Aggregationen durchzuführen. Wandeln Sie dazu die Werte in FLOAT64 / FLOAT8 um.

GoogleSQL 

SELECT SUM(CAST(value AS FLOAT64)) FROM my_table

PostgreSQL 

SELECT SUM(value::FLOAT8) FROM my_table

Ebenso können Sie durch den Umwandlungsvorgang Werte nach numerischen Werten oder Grenzwerte nach Bereich sortieren:

GoogleSQL 

SELECT value FROM my_table ORDER BY CAST(value AS FLOAT64);
SELECT value FROM my_table WHERE CAST(value AS FLOAT64) > 100.0;

PostgreSQL 

SELECT value FROM my_table ORDER BY value::FLOAT8;
SELECT value FROM my_table WHERE value::FLOAT8 > 100.0;

Diese Berechnungen entsprechen ungefähr den Grenzwerten des Datentyps FLOAT64 / FLOAT8.

Alternativen

Es gibt andere Möglichkeiten, Zahlen mit beliebiger Genauigkeit in Spanner zu speichern. Wenn das Speichern von Zahlen mit beliebiger Genauigkeit als Strings für Ihre Anwendung nicht funktioniert, sollten Sie die folgenden Alternativen in Betracht ziehen:

Anwendungsskalierte Ganzzahlwerte speichern

Sie können Zahlen mit beliebiger Genauigkeit speichern. Skalieren Sie dazu die Werte vor dem Schreiben, sodass Zahlen immer als Ganzzahlen gespeichert und die Werte nach dem Lesen noch einmal skaliert werden. Ihre Anwendung speichert einen festen Skalierungsfaktor und die Genauigkeit ist auf die 18 Ziffern beschränkt, die vom Datentyp INT64 / INT8 bereitgestellt werden.

Nehmen Sie beispielsweise eine Zahl, die mit einer Genauigkeit von 5 Dezimalstellen gespeichert werden muss. Die Anwendung konvertiert den Wert durch Multiplikation mit 100.000 in eine Ganzzahl (Verschiebung des Dezimalpunkts um 5 Stellen nach rechts), sodass der Wert 12,54321 als 1254321 gespeichert wird.

In monetärer Hinsicht ist dieser Ansatz vergleichbar mit dem Speichern von Dollarwerten als das Vielfache von Millicents, ähnlich wie das Speichern von Zeiteinheiten in Millisekunden.

Die Anwendung bestimmt den festen Skalierungsfaktor. Wenn Sie den Skalierungsfaktor ändern, ist es erforderlich, dass Sie alle zuvor skalierten Werte in der Datenbank konvertieren.

Dieser Ansatz speichert für Menschen lesbare Werte (vorausgesetzt, Sie kennen den Skalierungsfaktor). Sie können mit SQL-Abfragen auch Berechnungen von direkt in der Datenbank gespeicherten Werten durchführen, sofern das Ergebnis korrekt skaliert wird und nicht überläuft.

Unskalierten ganzzahligen Wert und die Skalierung in separaten Spalten speichern

Sie können Zahlen mit beliebiger Genauigkeit auch mithilfe von zwei Elementen in Spanner speichern:

  • Der nicht skalierte, ganzzahlige Wert, der in einem Byte-Array gespeichert ist
  • Eine Ganzzahl, die den Skalierungsfaktor angibt

Zuerst konvertiert die Anwendung die beliebige Dezimalzahl in einen nicht skalierten, ganzzahligen Wert. Beispielsweise konvertiert die Anwendung 12.54321 in 1254321. Die Skala für dieses Beispiel ist 5.

Dann konvertiert die Anwendung den nicht skalierten ganzzahligen Wert in ein Byte-Array unter Verwendung einer tragbaren Standard-Binärdarstellung (beispielsweise das Big-Endian-Zweierkomplement).

Die Datenbank speichert dann das Byte-Array (BYTES / BYTEA) und die Ganzzahlskalierung (INT64 / INT8) in zwei separaten Spalten und konvertiert sie beim Lesen wieder.

In Java können Sie mit BigDecimal und BigInteger die folgenden Berechnungen ausführen:

byte[] storedUnscaledBytes = bigDecimal.unscaledValue().toByteArray();
int storedScale = bigDecimal.scale();

Sie können mit folgendem Code in ein Java-BigDecimal zurücklesen:

BigDecimal bigDecimal = new BigDecimal(
    new BigInteger(storedUnscaledBytes),
    storedScale);

Bei diesem Ansatz werden Werte mit beliebiger Genauigkeit und einer portablen Darstellung gespeichert. Die Werte sind jedoch in der Datenbank nicht lesbar und es ist erforderlich, alle Berechnungen von der Anwendung ausführen zu lassen.

Interne Darstellung der Anwendung als Byte speichern

Eine andere Option ist die Serialisierung der Dezimalwerte mit beliebiger Genauigkeit mit der internen Darstellung der Anwendung in Byte-Arrays und dann die Speicherung direkt in der Datenbank.

Die gespeicherten Datenbankwerte sind nicht lesbar und es ist erforderlich, dass die Anwendung alle Berechnungen durchführt.

Bei diesem Ansatz gibt es Probleme mit der Portabilität. Wenn Sie versuchen, die Werte mit einer anderen Programmiersprache oder Bibliothek zu lesen, als sie ursprünglich geschrieben wurden, funktioniert das möglicherweise nicht. Das Zurücklesen der Werte funktioniert möglicherweise deshalb nicht, weil verschiedene Bibliotheken mit beliebiger Genauigkeit unterschiedliche serialisierte Repräsentationen für Byte-Arrays haben können.

Nächste Schritte