Archivia dati numerici con precisione arbitraria

Spanner fornisce il tipo NUMERIC in grado di archiviare numeri a precisione decimale esattamente. La semantica del tipo NUMERIC in Spanner varia tra i suoi due dialetti SQL (GoogleSQL e PostgreSQL), in particolare intorno ai limiti di scalabilità e precisione:

  • NUMERIC nel dialetto PostgreSQL è un tipo numerico di precisione decimale arbitraria (la scala o la precisione può essere qualsiasi numero compreso nell'intervallo supportato) e, pertanto, è la scelta ideale per l'archiviazione di dati numerici a precisione arbitraria.

  • NUMERIC in GoogleSQL è un tipo numerico a precisione fissa (precisione=38 e scala=9) e non può essere utilizzato per memorizzare dati numerici a precisione arbitraria. Se devi archiviare numeri a precisione arbitraria nei database dialetti GoogleSQL, ti consigliamo di archiviarli come stringhe.

Precisione dei tipi numerici di Spanner

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

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

Esaminiamoli in termini di precisione e scala.

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

FLOAT64 / FLOAT8 possono rappresentare solo valori numerici decimali approssimativi con componenti frazionari e fornisce da 15 a 17 cifre significative (conteggio delle cifre in un numero con tutti gli zeri finali rimossi) di precisione decimale. Diciamo che questo tipo rappresenta valori numerici decimali approssimativi perché la rappresentazione binaria IEEE in virgola mobile a 64 bit utilizzata da Spanner non può rappresentare con precisione frazioni decimali (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 il tipo di dati FLOAT64 / FLOAT8, la rappresentazione binaria viene convertita in un valore decimale di 0,20000000000000001 (con 18 cifre di precisione). Analogamente (1,4 * 165) converte in 230,999999999999971, mentre (0,1 + 0,2) viene convertito in 0,30000000000000004. Per questo motivo, i numeri in virgola mobile a 64 bit hanno solo 15-17 cifre significative di precisione (solo alcuni numeri con più di 15 cifre decimali possono essere rappresentati come numeri in virgola mobile a 64 bit senza arrotondamento). Per ulteriori dettagli su come viene calcolata la precisione in virgola mobile, consulta Formato a virgola mobile a precisione doppia.

Né INT64 / INT8 né FLOAT64 / FLOAT8 offrono la precisione ideale per calcoli finanziari, scientifici o ingegneristici, dove è comunemente richiesta una precisione di almeno 30 cifre.

Il tipo di dati NUMERIC è adatto per queste applicazioni, poiché è in grado di rappresentare i valori numerici a precisione decimale esatta con una precisione superiore a 30 cifre decimali.

Il tipo di dati GoogleSQL NUMERIC può rappresentare i numeri con precisione decimale fissa di 38 e scala fissa pari a 9. L'intervallo di NUMERIC di GoogleSQL è compreso tra 9999999999999999999999999999,999999999 e 999999999999999999999999999,999999999.

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

Se devi memorizzare numeri superiori alla precisione e alla scalabilità offerte da NUMERIC, le sezioni seguenti descrivono alcune soluzioni consigliate.

Suggerimento: memorizza i numeri di precisione arbitraria come stringhe

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

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

La maggior parte delle librerie di precisione arbitraria dispone di metodi integrati per eseguire questa conversione senza perdita di dati. In Java, ad esempio, puoi utilizzare il metodo BigDecimal.toPlainString() e il costruttore BigDecimal(String).

L'archiviazione del numero come stringa offre il vantaggio che il valore venga memorizzato con precisione esatta (fino al limite di lunghezza della colonna STRING / VARCHAR) e che il valore rimanga leggibile.

Eseguire aggregazioni e calcoli esatti

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

Ad esempio, per eseguire l'equivalente di un SQL SUM(value) su un intervallo di righe, l'applicazione deve eseguire una query sui valori delle stringhe per le righe, poi convertirli e sommarli internamente nell'app.

Eseguire aggregazioni, ordinamento e calcoli approssimativi

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

GoogleSQL 

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

PostgreSQL 

SELECT SUM(value::FLOAT8) FROM my_table

Analogamente, puoi ordinare per valore numerico o limitare valori per intervallo con la trasmissione:

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 approssimati ai limiti del tipo di dati FLOAT64 / FLOAT8.

Alternative

Esistono altri modi per archiviare numeri di precisione arbitraria in Spanner. Se l'archiviazione di numeri a precisione arbitraria come stringhe non funziona per la tua applicazione, valuta le seguenti alternative:

Archivia valori interi con scalabilità dell'applicazione

Per memorizzare numeri a precisione arbitraria, puoi pre-scalare i valori prima di 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 è limitata alle 18 cifre fornite dal tipo di dati INT64 / INT8.

Prendiamo, ad esempio, un numero che deve essere memorizzato con una precisione di cinque cifre decimali. L'applicazione converte il valore in un numero intero moltiplicandolo per 100.000 (spostando il punto decimale di 5 posizioni verso destra), per cui il valore 12.54321 viene memorizzato come 1254321.

In termini monetari, questo approccio è simile alla memorizzazione dei valori in dollari sotto forma di multipli di milli-cent, in modo analogo alla memorizzazione delle unità di tempo sotto forma di millisecondi.

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

Questo approccio archivia i valori leggibili da una persona (supponendo che tu conosca il fattore di scalabilità). Inoltre, puoi utilizzare le query SQL per eseguire calcoli direttamente sui valori archiviati nel database, a condizione che il risultato venga scalato correttamente e non abbia un 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 memorizzato in un array di byte.
  • Un numero intero che specifica il fattore di scalabilità.

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

Quindi l'applicazione converte il valore intero non in scala in un array di byte utilizzando una rappresentazione binaria portatile standard (ad esempio il complementare a due di big-endian).

Il database archivia quindi l'array di byte (BYTES / BYTEA) e la scala di numeri interi (INT64 / INT8) in due colonne separate e li converte in fase di lettura.

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

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

Puoi rileggere il testo in un BigDecimal Java utilizzando il seguente codice:

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

Questo approccio archivia i valori con precisione arbitraria e rappresentazione portabile, ma i valori non sono leggibili da una persona nel database e tutti 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 array di byte utilizzando la rappresentazione interna dell'applicazione e quindi archiviarli direttamente nel database.

I valori del database archiviato non sono leggibili e l'applicazione deve eseguire tutti i calcoli.

Questo approccio presenta problemi di portabilità. Se provi a leggere i valori con un linguaggio o una libreria di programmazione diverso da quello in cui sono stati scritti in origine, potrebbe non funzionare. La lettura dei valori potrebbe non funzionare perché librerie di precisione arbitraria possono avere rappresentazioni serializzate diverse per gli array di byte.

Passaggi successivi