Archiviare dati numerici di precisione arbitraria

Spanner fornisce il tipo NUMERIC in grado di archiviare numeri di precisione decimali in modo esatto. La semantica del tipo NUMERIC in Spanner varia tra i due SQL dialetti (GoogleSQL e PostgreSQL), in particolare per quanto riguarda limiti su scalabilità e precisione:

  • NUMERIC nel dialetto PostgreSQL è un precisione decimale arbitraria tipo numerico (la scala o la precisione possono essere un numero qualsiasi all'interno dell'intervallo supportato) e quindi è la scelta ideale per archiviare dati numerici di precisione arbitraria.

  • NUMERIC in GoogleSQL è una precisione fissa tipo numerico (precisione=38 e scala=9) e non può essere utilizzato per memorizzare dati numerici di precisione arbitraria. Quando devi archiviare numeri di precisione arbitrari nei database di dialetti GoogleSQL, di memorizzarle come stringhe.

Precisione dei tipi numerici di Spanner

La precisione è il numero di cifre di un numero. La scala è il numero di cifre da a destra della virgola decimale in un numero. Ad esempio, il numero 123,456 ha con una precisione di 6 e una scala di 3. Spanner ha tre tipi numerici:

  • Tipo di numero intero a 64 bit con segno denominato INT64 nel dialetto GoogleSQL e INT8 nel dialetto PostgreSQL.
  • Tipo in virgola mobile con precisione binaria IEEE a 64 bit (doppio) denominato FLOAT64 nel dialetto GoogleSQL e FLOAT8 nel dialetto PostgreSQL.
  • Tipo di precisione decimale NUMERIC.

Vediamole in termini di precisione e scalabilità.

INT64 / INT8 rappresenta i valori numerici che non hanno un componente frazionario. Questo tipo di dati fornisce 18 cifre di e la precisione, con una scala pari a zero.

FLOAT64 / FLOAT8 può rappresentare solo valori numerici decimali approssimativi con componenti frazionari e fornisce Da 15 a 17 cifre significative (conteggio delle cifre di un numero con tutti gli zeri finali rimossa) della precisione decimale. Diciamo che questo tipo rappresenta il numero decimale approssimato perché la rappresentazione in virgola mobile IEEE a 64 bit una rappresentazione binaria usata da Spanner non può rappresentare con precisione frazioni decimali (in base 10) (può rappresentare esattamente solo frazioni in base 2). Questa perdita di precisione introduce errori di arrotondamento per alcune frazioni decimali.

Ad esempio, quando memorizzi il valore decimale 0,2 utilizzando i valori FLOAT64 / FLOAT8 tipo di dati, la rappresentazione binaria viene riconvertita in un valore decimale 0,20000000000000001 (a 18 cifre di precisione). Analogamente (1,4 * 165) converte torna a 230,999999999999971 e (0,1 + 0,2) viene riconvertito in 0,30000000000000004. Questo è il motivo per cui i numeri in virgola mobile a 64 bit sono descritti come aventi solo 15-17 cifre significative di precisione (solo alcuni numeri con più di 15 decimali le cifre possono essere rappresentate come numero in virgola mobile a 64 bit senza arrotondamenti). Per ulteriori dettagli su Come viene calcolata la precisione con rappresentazione in virgola mobile, consulta l'articolo Virgola in virgola mobile a precisione doppia standard.

Né INT64 / INT8 né FLOAT64 / FLOAT8 hanno la precisione ideale per i calcoli finanziari, scientifici o ingegneristici, in cui una precisione di 30 è spesso richiesto un numero di almeno più cifre.

Il tipo di dati NUMERIC è adatto per queste applicazioni, in quanto è in grado di rappresentare i valori numerici di precisione decimale esatta con una precisione maggiore più di 30 cifre decimali.

I dati GoogleSQL di NUMERIC può rappresentare numeri con precisione decimale fissa pari a 38 e scala fissa di 9. L'intervallo di NUMERIC di GoogleSQL è -9999999999999999999999999999.999999999 a 999999999999999999999999999.999999999.

Il tipo di dialetto PostgreSQL NUMERIC può rappresentare numeri con un con una precisione decimale massima di 147.455 e una scala massima di 16.383.

Se devi archiviare numeri superiori ai valori di precisione e scala offerte da NUMERIC, le seguenti sezioni descrivono alcuni contenuti consigliati soluzioni.

Consiglio: archivia i numeri di precisione arbitrari come stringhe

Quando devi archiviare un numero di precisione arbitrario in un oggetto Spanner e hai bisogno di maggiore precisione rispetto a quella fornita da NUMERIC, ti consigliamo di memorizzare il valore come rappresentazione decimale in STRING / VARCHAR colonna. Ad esempio, il numero 123.4 viene memorizzato come stringa "123.4".

Con questo approccio, l'applicazione deve eseguire una conversione senza perdita di dati la rappresentazione interna all'applicazione del numero e di STRING / VARCHAR valore della colonna per le letture e le scritture del database.

La maggior parte delle librerie di precisione arbitrarie hanno metodi integrati per eseguire questa operazione senza perdita di dati. In Java, ad esempio, puoi utilizzare BigDecimal.toPlainString() e il BigDecimal(String) come costruttore.

Memorizzare il numero come stringa ha il vantaggio che il valore venga memorizzato con la precisione esatta (fino al limite di lunghezza delle colonne STRING / VARCHAR) e il valore rimangono leggibili da una persona.

Eseguire aggregazioni e calcoli esatti

Per eseguire aggregazioni e calcoli esatti sulle rappresentazioni stringa di numeri di precisione arbitrari, l'applicazione deve eseguire questi calcoli. Non puoi utilizzare le funzioni di aggregazione SQL.

Ad esempio, per eseguire l'equivalente di un'istruzione SQL SUM(value) su un intervallo righe, l'applicazione deve eseguire una query sui valori stringa delle righe, quindi convertirle e sommarle internamente nell'app.

Esegui aggregazioni, ordinamenti e calcoli approssimati

Puoi utilizzare le query SQL per eseguire calcoli aggregati approssimati per trasmettendo i valori a FLOAT64 / FLOAT8.

GoogleSQL 

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

PostgreSQL 

SELECT SUM(value::FLOAT8) FROM my_table

Allo stesso modo, puoi ordinare in base al valore numerico o ai valori limite per intervallo con la trasmissione di:

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;

Questi calcoli sono approssimativi rispetto ai limiti del tipo di dati FLOAT64 / FLOAT8.

Alternative

Esistono altri modi per archiviare numeri di precisione arbitrari in Spanner. Se non memorizzare numeri di precisione arbitrari perché le stringhe non funzionano per di Google Cloud, prendi in considerazione le seguenti alternative:

Archivia valori interi per la scalabilità dell'applicazione

Per archiviare numeri di precisione arbitrari, puoi pre-scalare i valori prima scrivere, in modo che i numeri vengano sempre memorizzati come numeri interi e riscalare i valori dopo la lettura. L'applicazione archivia un fattore di scala fisso e la precisione è limitato alle 18 cifre fornite dal tipo di dati INT64 / INT8.

Prendiamo ad esempio un numero che deve essere memorizzato con una precisione di 5 posizioni decimali. L'applicazione converte il valore in un numero intero moltiplicando per 100.000 (spostando il separatore decimale di 5 posizioni a destra), quindi il valore 12,54321 è memorizzato come 1254321.

In termini monetari, questo approccio è come memorizzare valori in dollari come multipli di milli-centesimi, in modo simile alla memorizzazione delle unità di tempo in millisecondi.

L'applicazione determina il fattore di scalabilità fisso. Se modifichi la scalabilità devi convertire tutti i valori scalati in precedenza nel database.

Questo approccio memorizza valori leggibili da una persona (supponendo che tu conosca i fattore di scala). Inoltre, puoi usare query SQL per eseguire direttamente i calcoli sui valori archiviati nel database, purché il risultato venga scalato correttamente senza overflow.

Archivia il valore intero non scalato e la fare lo scale in colonne separate

Puoi anche archiviare numeri di precisione arbitrari in Spanner utilizzando due elementi:

  • Il valore intero non scalato archiviato in un array di byte.
  • Un numero intero che specifica il fattore di scala.

Innanzitutto l'applicazione converte il decimale di precisione arbitrario in un numero decimale non in scala valore intero. Ad esempio, l'applicazione converte 12.54321 in 1254321. La scala per questo esempio è 5.

Quindi l'applicazione converte il valore intero non scalato in un array di byte utilizzando una rappresentazione binaria standard portatile (ad esempio, completamento a due).

Il database archivia quindi l'array di byte (BYTES / BYTEA) e la scala di numeri interi (INT64 / INT8) in due colonne separate e le riconvertirà alla lettura.

In Java, puoi utilizzare BigDecimal e BigInteger per eseguire questi calcoli:

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

Puoi rileggere un BigDecimal Java utilizzando il seguente codice:

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

Questo approccio archivia i valori con una precisione arbitraria rappresentazione, ma i valori non sono leggibili da una persona nel database. i calcoli devono essere eseguiti dall'applicazione.

Archivia la rappresentazione interna dell'applicazione come byte

Un'altra opzione è serializzare i valori decimali di precisione arbitraria in byte utilizzando la rappresentazione interna dell'applicazione, per poi archiviarli direttamente nel database.

I valori del database memorizzati non sono leggibili da una persona e l'applicazione deve eseguire tutti i calcoli.

Questo approccio presenta problemi di portabilità. Se provi a leggere i valori con un parametro un linguaggio di programmazione o una libreria diversa da quella che l'ha scritta in origine, potrebbe non funzionare. La lettura dei valori potrebbe non funzionare perché librerie di precisione arbitrarie possono avere rappresentazioni serializzate diverse per come array di byte.

Passaggi successivi