Numerische Daten mit beliebiger Genauigkeit speichern

Spanner bietet den Typ NUMERIC, mit dem sich Zahlen mit Dezimalgenauigkeit genau speichern lassen. Die Semantik des Typs NUMERIC in Spanner variiert zwischen seinen beiden SQL-Dialekten (GoogleSQL und PostgreSQL), insbesondere im Hinblick auf die Limits für Skalierung und Genauigkeit:

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

  • 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 GoogleSQL-Dialektdatenbanken speichern müssen, empfehlen wir, sie als Strings zu speichern.

Genauigkeit von numerischen 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 namens INT64 im GoogleSQL-Dialekt und INT8 im PostgreSQL-Dialekt.
  • IEEE 64-Bit-Gleitkommatyp (doppelt) mit binärer Genauigkeit mit dem Namen FLOAT64 im GoogleSQL-Dialekt und FLOAT8 im PostgreSQL-Dialekt.
  • Typ NUMERIC mit Dezimalgenauigkeit.

Sehen wir uns die Genauigkeit und Skalierung an.

INT64 / INT8 steht für numerische Werte, die keine Bruchkomponente haben. Dieser Datentyp bietet eine Genauigkeit von 18 Stellen mit einer Skala von null.

FLOAT64 / FLOAT8 kann nur ungefähre dezimale numerische Werte mit Bruchkomponenten darstellen und bietet 15 bis 17 signifikante Ziffern (Anzahl der Ziffern in einer Zahl ohne nachgestellte Nullen) mit dezimaler Genauigkeit. Wir sagen, dass dieser Typ ungefähre dezimale Zahlenwerte darstellt, da die binäre Darstellung mit IEEE-64-Bit-Gleitkomma, die Spanner verwendet, dezimale Brüche (Basis 10) nicht genau darstellen kann (es kann nur Basis-2-Brüche exakt 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 zurück 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 so beschrieben, dass sie nur eine signifikante Genauigkeit von 15 bis 17 Stellen haben (nur einige Zahlen mit mehr als 15 Dezimalstellen können als 64-Bit-Gleitkommazahl ohne Rundung dargestellt werden). Weitere Informationen zur Berechnung der Gleitkommazahl finden Sie unter Gleitkommazahl mit doppelter Genauigkeit.

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

Der Datentyp NUMERIC eignet sich für diese Anwendungen, da er numerische Werte 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 liegt zwischen -99999999999999999999999999999.999999999 und 9999999999999999999999999999.999999999.

Der PostgreSQL-Dialekt NUMERIC kann Zahlen mit einer maximalen dezimalen Genauigkeit von 147.455 und einer maximalen Skala 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 und mehr Genauigkeit benötigen, als NUMERIC bereitstellt, empfehlen wir, den Wert als Dezimalwert in einer Spalte vom Typ STRING oder VARCHAR 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 der Datenbank 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änge von STRING / VARCHAR) gespeichert wird und weiterhin visuell 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 Aggregatberechnungen 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 in Spanner auch mit zwei Elementen 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 Bytearray (BYTES / BYTEA) und die Ganzzahlskala (INT64 / INT8) in zwei separaten Spalten und konvertiert sie beim Lesen zurück.

In Java können Sie für die Berechnungen BigDecimal und BigInteger verwenden:

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