Numerische Daten mit beliebiger Genauigkeit speichern

Spanner bietet den Typ NUMERIC, mit dem Zahlen mit Dezimalstellen genau gespeichert werden können. Die Semantik des Typs NUMERIC in Spanner unterscheidet sich zwischen den beiden SQL-Dialekten (GoogleSQL und PostgreSQL), insbesondere in Bezug auf die Grenzen für Skalierung und Genauigkeit:

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

  • NUMERIC in GoogleSQL ist ein numerischer Datentyp mit fester Genauigkeit (Genauigkeit=38 und Skalierung=9) und kann nicht zum Speichern numerischer Daten mit beliebiger Genauigkeit verwendet werden. Wenn Sie in GoogleSQL-Datenbanken Zahlen mit beliebiger Genauigkeit speichern möchten, empfehlen wir, sie als Strings zu speichern.

Genauigkeit der 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, der im GoogleSQL-Dialekt INT64 und im PostgreSQL-Dialekt INT8 genannt wird.
  • IEEE-64-Bit-Gleitkommatyp (doppelt) mit binärer Genauigkeit, im GoogleSQL-Dialekt FLOAT64 und im PostgreSQL-Dialekt FLOAT8 genannt.
  • Dezimalstellen NUMERIC

Sehen wir uns die Genauigkeit und Skalierung an.

INT64 ÷ INT8 stellt 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 Dezimalzahlen mit Bruchkomponenten darstellen und bietet eine Dezimalgenauigkeit von 15 bis 17 Stellen (Anzahl der Ziffern in einer Zahl ohne Nachkommanullen). Dieser Typ stellt ungefähre Dezimalzahlen dar, da die von Spanner verwendete IEEE-64-Bit-Gleitkomma-Binärdarstellung keine exakten Dezimalbrüche (Basis 10) darstellen kann. Nur Brüche mit Basis 2 können genau dargestellt werden. 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 konvertiert (mit einer Genauigkeit von 18 Stellen). Auf ähnliche Weise wird (1,4 * 165) wieder in 230,999999999999971 und (0,1 + 0,2) wieder in 0,30000000000000004 konvertiert. Deshalb werden 64-Bit-Gleitkommazahlen nur mit einer Genauigkeit von 15 bis 17 Stellen beschrieben. Nur einige Zahlen mit mehr als 15 Stellen nach dem Dezimaltrennzeichen können ohne Rundung als 64‑Bit-Gleitkommazahl dargestellt werden. Weitere Informationen zur Berechnung der Gleitkommazahl finden Sie unter Doppelte Genauigkeit.

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

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

Der GoogleSQL-Datentyp NUMERIC kann Zahlen mit einer festen Dezimalgenauigkeit von 38 und einer festen Skalierung von 9 darstellen. Der Bereich von GoogleSQL NUMERIC ist -99999999999999999999999999999.999999999 bis 99999999999999999999999999999.999999999.

Der PostgreSQL-Dialekttyp 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 und mehr Genauigkeit benötigen als NUMERIC bereitstellt, empfehlen wir, den Wert als Dezimalwert in einer STRING-/VARCHAR-Spalte zu speichern. Die Zahl 123.4 wird beispielsweise als String "123.4" gespeichert.

Bei diesem Ansatz ist es erforderlich, dass die Anwendung eine verlustfreie Konvertierung zwischen der anwendungsinternen Darstellung der Zahl und dem Spaltenwert STRING / VARCHAR für Lese- und Schreibvorgänge der Datenbank durchführt.

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.

Die Zahl als String zu speichern, hat den Vorteil, dass der Wert genau (bis zum Spaltenlängenlimit STRING / VARCHAR) gespeichert wird und weiterhin 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 mit SQL-Abfragen ungefähre Berechnungen von Aggregationen ausfü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 noch andere Möglichkeiten für die Speicherung von Zahlen mit beliebiger Genauigkeit in Spanner. 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. Die Anwendung speichert einen festen Skalierungsfaktor und die Genauigkeit ist auf die vom Datentyp INT64 / INT8 bereitgestellten 18 Ziffern beschränkt.

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 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 Byte-Array (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