Memorizzare dati numerici di precisione arbitraria

Spanner fornisce il tipo NUMERIC che può memorizzare numeri con precisione decimale esattamente. La semantica del tipo NUMERIC in Spanner varia tra i due dialetti SQL (GoogleSQL e PostgreSQL), soprattutto per quanto riguarda i limiti di scala e precisione:

  • NUMERIC nel dialetto PostgreSQL è un tipo numerico con precisione decimale arbitraria (la scala o la precisione possono essere qualsiasi numero all'interno dell'intervallo supportato) e pertanto è la scelta ideale per archiviare dati numerici di 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 di precisione arbitraria. Quando devi archiviare numeri con precisione arbitraria nei database con dialetto GoogleSQL, ti consigliamo di archiviarli come stringhe.

Precisione dei tipi numerici di Spanner

La precisione è il numero di cifre di un numero. La scala è il numero di cifre a destra del separatore 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 di numero intero con segno a 64 bit chiamato INT64 nel dialetto GoogleSQL e INT8 nel dialetto PostgreSQL.
  • Tipo di virgola mobile binaria a 64 bit (doppia precisione) IEEE denominato FLOAT64 nel dialetto GoogleSQL e FLOAT8 nel dialetto PostgreSQL.
  • Tipo di NUMERIC precisione decimale.

Vediamoli uno per uno in termini di precisione e scala.

INT64 / INT8 rappresenta valori numerici che non hanno una componente frazionaria. Questo tipo di dati fornisce 18 cifre di 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 in un numero con tutti gli zeri finali rimossi) di precisione decimale. Affermiamo che questo tipo rappresenta valori numerici decimali approssimativi perché la rappresentazione binaria IEEE a virgola mobile a 64 bit utilizzata da Spanner non può rappresentare con precisione le frazioni decimali (in base 10) (può rappresentare esattamente solo le 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 riconvertita in un valore decimale pari a 0,20000000000000001 (con 18 cifre di precisione). Allo stesso modo, (1,4 * 165) viene riconvertito in 230,999999999999971 e (0,1 + 0,2) viene riconvertito in 0,30000000000000004. Per questo motivo, 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 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 la sezione Formato in virgola mobile a precisione doppia .

Né INT64 / INT8 né FLOAT64 / FLOAT8 hanno la precisione ideale per calcoli finanziari, scientifici o ingegneristici, in cui è comunemente richiesta una precisione di 30 cifre o più.

Il tipo di dati NUMERIC è adatto a queste applicazioni, in quanto è in grado di rappresentare valori numerici con precisione decimale esatta con una precisione di oltre 30 cifre decimali.

Il tipo di dati GoogleSQL NUMERIC può rappresentare numeri con una precisione decimale fissa di 38 e una scala fissa di 9. L'intervallo di NUMERIC in GoogleSQL è da -99999999999999999999999999999,999999999 a 99999999999999999999999999999,999999999.

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

Se devi archiviare numeri superiori alla precisione e alla scala offerte da NUMERIC, le sezioni seguenti descrivono alcune soluzioni consigliate.

Consiglio: memorizza i numeri di precisione arbitraria come stringhe

Quando devi archiviare un numero di precisione arbitraria in un database Spanner e hai bisogno di una precisione maggiore di quella fornita da NUMERIC, ti consigliamo di archiviare il valore come 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 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. In Java, ad esempio, puoi utilizzare il metodo BigDecimal.toPlainString() e il costruttore BigDecimal(String).

L'archiviazione del numero come stringa presenta il vantaggio che il valore viene archiviato con precisione esatta (fino al limite di lunghezza della colonna STRING / VARCHAR) e il valore rimane leggibile.

Eseguire aggregazioni e calcoli esatti

Per eseguire aggregazioni e calcoli exact sulle rappresentazioni di stringhe di numeri con precisione arbitraria, la tua applicazione deve eseguire questi calcoli. Non puoi utilizzare le funzioni di aggregazione SQL.

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

Eseguire aggregazioni, ordinamenti e calcoli approssimativi

Puoi utilizzare le query SQL per eseguire calcoli aggregati approssimativi eseguendo il cast dei valori su FLOAT64 / FLOAT8.

GoogleSQL 

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

PostgreSQL 

SELECT SUM(value::FLOAT8) FROM my_table

Analogamente, puoi ordinare in base al valore numerico o limitare i valori in base all'intervallo con il casting:

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

Alternative

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

Memorizza valori interi scalati per l'applicazione

Per memorizzare numeri di precisione arbitraria, puoi scalare i valori prima della scrittura, in modo che i numeri vengano sempre memorizzati come numeri interi, e scalare nuovamente i valori dopo la lettura. La tua applicazione memorizza 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 5 cifre decimali. L'applicazione converte il valore in un numero intero moltiplicandolo per 100.000 (spostando il punto decimale di 5 posizioni a destra), quindi il valore 12,54321 viene memorizzato come 1254321.

In termini monetari, questo approccio è simile all'archiviazione di valori in dollari come multipli di millicent, in modo simile all'archiviazione di unità di tempo come 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 memorizza valori leggibili (supponendo che tu conosca il fattore di scala). Inoltre, puoi utilizzare query SQL per eseguire calcoli direttamente sui valori archiviati nel database, a condizione che il risultato venga scalato correttamente e non si verifichi un overflow.

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

Puoi anche memorizzare numeri di precisione arbitraria 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 numero 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.

L'applicazione converte quindi il valore intero non scalato in un array di byte utilizzando una rappresentazione binaria portatile standard (ad esempio, complemento a due big-endian).

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

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

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

Puoi leggere di nuovo un BigDecimal Java utilizzando il seguente codice:

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

Questo approccio memorizza i valori con precisione arbitraria e una rappresentazione portatile, ma i valori non sono leggibili nel database e tutti i calcoli devono essere eseguiti dall'applicazione.

Memorizza 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, quindi archiviarli direttamente nel database.

I valori del database archiviati 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 di programmazione o una libreria diversi da quelli che li hanno scritti originariamente, l'operazione potrebbe non funzionare. La lettura dei valori potrebbe non funzionare perché librerie di precisione arbitraria diverse possono avere rappresentazioni serializzate diverse per gli array di byte.

Passaggi successivi