Stocker des données numériques de précision arbitraire

Spanner fournit le type NUMERIC qui peut stocker exactement des nombres de précision décimale. La sémantique du type NUMERIC dans Spanner varie entre ses deux dialectes SQL (GoogleSQL et PostgreSQL), en particulier concernant les limites d'échelle et de précision:

  • NUMERIC dans le dialecte PostgreSQL est un type numérique de précision décimale arbitraire (l'échelle ou la précision peut être n'importe quel nombre compris dans la plage acceptée). Cette valeur est donc idéale pour stocker des données numériques de précision arbitraire.

  • NUMERIC dans GoogleSQL est un type numérique à précision fixe (précision=38 et échelle=9). Il ne peut pas être utilisé pour stocker des données numériques de précision arbitraire. Lorsque vous devez stocker des nombres de précision arbitraire dans des bases de données de dialectes GoogleSQL, nous vous recommandons de les stocker sous forme de chaînes.

Précision des types numériques Spanner

La précision correspond au nombre de chiffres dans un nombre. L'échelle correspond au nombre de chiffres à droite de la virgule dans un nombre. Par exemple, le nombre 123,456 a une précision de 6 et une échelle de 3. Spanner comporte trois types numériques:

  • Type d'entier signé de 64 bits appelé INT64 dans le dialecte GoogleSQL et INT8 dans le dialecte PostgreSQL.
  • Type à virgule flottante à précision binaire IEEE 64 bits (double) appelé FLOAT64 dans le dialecte GoogleSQL et FLOAT8 dans le dialecte PostgreSQL.
  • Type NUMERIC de précision décimale.

Examinons chacun d'eux en termes de précision et d'échelle.

INT64 / INT8 représente les valeurs numériques qui n'ont pas de composant fractionnaire. Ce type de données fournit une précision de 18 chiffres avec une échelle de zéro.

FLOAT64 / FLOAT8 ne peut représenter que des valeurs numériques décimales approximatives avec des composants fractionnaires et fournit 15 à 17 chiffres significatifs (nombre de chiffres dans un nombre dont tous les zéros de fin ont été supprimés) de précision décimale. Nous disons que ce type représente des valeurs numériques décimales approximatives, car la représentation binaire à virgule flottante IEEE 64 bits que Spanner utilise ne peut pas représenter avec précision les fractions décimales (base 10) (elle ne peut représenter exactement que les fractions de base 2). Cette perte de précision introduit des erreurs d'arrondi pour certaines fractions décimales.

Par exemple, lorsque vous stockez la valeur décimale 0,2 à l'aide du type de données FLOAT64 / FLOAT8, la représentation binaire la reconvertit en une valeur décimale de 0,20000000000000001 (avec 18 chiffres de précision). De même, (1,4 x 165) devient 230,99999999999999971 et (0,1 + 0,2) devient 0.30000000000000004. C'est pourquoi les floats 64 bits sont décrits comme ne comportant que 15 à 17 chiffres significatifs de précision (seuls certains nombres comportant plus de 15 chiffres décimaux peuvent être représentés sous forme de floats 64 bits sans arrondis). Pour en savoir plus sur le calcul de la précision à virgule flottante, consultez la page Format à virgule flottante à double précision.

Ni INT64 / INT8, ni FLOAT64 / FLOAT8 n'ont la précision idéale pour les calculs financiers, scientifiques ou techniques, qui requièrent généralement une précision de 30 chiffres ou plus.

Le type de données NUMERIC convient à ces applications, car il est capable de représenter des valeurs numériques de précision décimale exacte ayant une précision supérieure à 30 chiffres décimaux.

Le type de données GoogleSQL NUMERIC peut représenter des nombres avec une précision décimale fixe de 38 et une échelle fixe de 9. La plage de NUMERIC GoogleSQL est comprise entre -9999999999999999999999999999,999999999 et 9999999999999999999999999999,999999999.

Le dialecte PostgreSQL NUMERIC peut représenter des nombres avec une précision décimale maximale de 147 455 et une échelle maximale de 16 383.

Si vous devez stocker des nombres supérieurs à la précision et à l'échelle proposées par NUMERIC, les sections suivantes décrivent certaines solutions recommandées.

Recommandation : stocker les nombres de précision arbitraire sous forme de chaînes

Lorsque vous devez stocker un nombre de précision arbitraire dans une base de données Spanner et que vous avez besoin d'une précision supérieure à celle fournie par NUMERIC, nous vous recommandons de stocker la valeur sous forme de représentation décimale dans une colonne STRING / VARCHAR. Par exemple, le nombre 123.4 est stocké en tant que chaîne "123.4".

Avec cette approche, votre application doit effectuer une conversion sans perte entre la représentation interne de l'application du nombre et la valeur de la colonne STRING / VARCHAR pour les lectures et les écritures dans la base de données.

La plupart des bibliothèques de précision arbitraire comprennent des méthodes intégrées qui permettent d'effectuer cette conversion sans perte. Dans Java, par exemple, vous pouvez utiliser la méthode BigDecimal.toPlainString() et le constructeur BigDecimal(String).

Le stockage du nombre sous forme de chaîne présente l'avantage que la valeur est stockée avec une précision exacte (jusqu'à la limite de longueur des colonnes STRING / VARCHAR), et que la valeur reste lisible par l'humain.

Effectuer des agrégations et des calculs exacts

Pour procéder à des agrégations et des calculs exacts sur des représentations sous forme de chaînes de nombres de précision arbitraire, votre application doit effectuer ces calculs. Vous ne pouvez pas utiliser les fonctions d'agrégation SQL.

Par exemple, pour exécuter l'équivalent d'une fonction SUM(value) SQL sur une plage de lignes, l'application doit interroger les valeurs de chaîne des lignes, puis les convertir et les additionner en interne.

Effectuer des agrégations, des tris et des calculs approximatifs

Vous pouvez utiliser des requêtes SQL pour effectuer des calculs d'agrégation approximaux en convertissant les valeurs en FLOAT64 / FLOAT8.

GoogleSQL 

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

PostgreSQL 

SELECT SUM(value::FLOAT8) FROM my_table

De même, vous pouvez effectuer un tri par valeur numérique ou limiter les valeurs par plage grâce à une opération de 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;

Ces calculs correspondent approximativement aux limites du type de données FLOAT64 / FLOAT8.

Autres méthodes

Il existe d'autres moyens de stocker des nombres de précision arbitraire dans Spanner. Si le stockage de nombres de précision arbitraire sous forme de chaînes ne fonctionne pas pour votre application, envisagez les solutions suivantes :

Stocker des valeurs entières mises à l'échelle pour l'application

Pour stocker des nombres de précision arbitraire, vous pouvez mettre à l'échelle les valeurs avant l'écriture (pour que les nombres soient toujours stockés sous forme d'entiers), puis les remettre à l'échelle après la lecture. Votre application stocke un facteur d'échelle fixe, et la précision est limitée aux 18 chiffres fournis par le type de données INT64 / INT8.

Prenons par exemple un nombre devant être stocké avec une précision de cinq décimales. L'application convertit la valeur en un entier en la multipliant par 100 000 (en décalant le signe décimal de cinq chiffres vers la droite). La valeur 12,54321 est donc stockée en tant que 1254321.

En termes monétaires, cette approche revient à stocker des valeurs en dollars en tant que multiples de millicentimes, tout comme vous stockeriez des unités de temps sous forme de millisecondes.

L'application détermine le facteur d'échelle fixe. Si vous le modifiez, vous devez convertir toutes les valeurs précédemment mises à l'échelle dans votre base de données.

Cette approche stocke des valeurs lisibles par l'homme (en supposant que vous connaissiez le facteur d'échelle). En outre, vous pouvez émettre des requêtes SQL pour effectuer des calculs directement sur les valeurs stockées dans la base de données, à condition que le résultat soit mis à l'échelle correctement et qu'il ne déborde pas.

Stocker l'échelle et la valeur entière non mise à l'échelle dans des colonnes distinctes

Vous pouvez également stocker des nombres de précision arbitraire dans Spanner à l'aide de deux éléments:

  • La valeur entière non mise à l'échelle stockée dans un tableau d'octets
  • Un entier spécifiant le facteur d'échelle

Tout d'abord, votre application convertit le nombre décimal de précision arbitraire en une valeur entière non mise à l'échelle. L'application peut par exemple convertir 12.54321 en 1254321. Dans cet exemple, l'échelle est 5.

Ensuite, l'application convertit la valeur entière non mise à l'échelle en un tableau d'octets à l'aide d'une représentation binaire portable standard (par exemple, le complément à deux en mode big-endian).

La base de données stocke ensuite le tableau d'octets (BYTES / BYTEA) et l'échelle d'entiers (INT64 / INT8) dans deux colonnes distinctes, puis les reconvertit à la lecture.

En Java, vous pouvez utiliser BigDecimal et BigInteger pour effectuer les calculs suivants:

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

Vous pouvez relire la valeur avec une classe BigDecimal Java à l'aide du code suivant :

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

Cette approche stocke les valeurs avec une précision arbitraire et une représentation portable, mais celles-ci ne sont pas lisibles par l'homme dans la base de données, et tous les calculs doivent être effectués par l'application.

Stocker la représentation interne de l'application sous forme d'octets

Une autre option consiste à sérialiser les valeurs décimales de précision arbitraire dans des tableaux d'octets à l'aide de la représentation interne de l'application, puis à les stocker directement dans la base de données.

Les valeurs de base de données stockées ne sont pas lisibles par l'homme et l'application doit effectuer tous les calculs.

Cette approche présente toutefois des problèmes de portabilité. Si vous essayez de lire les valeurs avec une bibliothèque ou un langage de programmation différent de celui qui les a écrites, il est possible que vous n'y parveniez pas. La lecture des valeurs peut échouer, car différentes bibliothèques de précision arbitraire peuvent posséder des représentations sérialisées distinctes pour les tableaux d'octets.

Étapes suivantes