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

Spanner fournit le type NUMERIC, qui peut stocker des nombres à précision décimale avec une précision exacte. La sémantique du type NUMERIC dans Spanner varie entre ses deux dialectes SQL (GoogleSQL et PostgreSQL), en particulier en ce qui concerne 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 dans la plage compatible). Il est donc idéal 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) et ne peut pas être utilisé pour stocker des données numériques à précision arbitraire. Lorsque vous devez stocker des nombres de précision arbitraire dans des bases de données de dialecte 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 propose trois types numériques:

  • Type d'entier signé 64 bits appelé INT64 dans le dialecte GoogleSQL et INT8 dans le dialecte PostgreSQL.
  • Type à virgule flottante à précision binaire (double) IEEE 64 bits appelé FLOAT64 dans le dialecte GoogleSQL et FLOAT8 dans le dialecte PostgreSQL.
  • Type NUMERIC à 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 ne possèdent 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 une précision décimale de 15 à 17 chiffres significatifs (nombre de chiffres dans un nombre sans zéros à la fin). Nous indiquons que ce type représente des valeurs numériques décimales approximatives, car la représentation binaire à virgule flottante IEEE 64 bits utilisée par Spanner ne peut pas représenter les fractions décimales (base 10) de manière précise (elle ne peut représenter que des fractions en 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 la raison pour laquelle les nombres à virgule flottante 64 bits sont décrits comme n'ayant qu'une précision de 15 à 17 chiffres significatifs (seuls certains nombres comportant plus de 15 chiffres décimaux peuvent être représentés sous la forme d'un nombre à virgule flottante 64 bits sans arrondi). Pour en savoir plus sur le mode de calcul de la précision à virgule flottante, consultez la page Format à virgule flottante à double précision.

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

Le type de données NUMERIC convient à ces applications, car il peut représenter des valeurs numériques exactes avec une précision décimale de plus de 30 chiffres décimaux.

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

Le type NUMERIC du dialecte PostgreSQL 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 correspond à la chaîne "123.4".

Avec cette approche, votre application doit effectuer une conversion sans perte entre la représentation interne à l'application du nombre et la valeur de la colonne STRING / VARCHAR pour les lectures et les écritures sur 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).

L'avantage du stockage d'un nombre sous forme de chaîne est que la valeur est stockée avec une précision exacte (jusqu'à la limite de longueur de la colonne STRING / VARCHAR). De plus, la valeur reste lisible par l'homme.

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 exécuter des requêtes SQL pour effectuer des calculs d'agrégation approximatifs en convertissant (à l'aide d'une opération de casting) 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.

Alternatives

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 par 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 se limite 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 alors le tableau d'octets (BYTES / BYTEA) et l'échelle entière (INT64 / INT8) dans deux colonnes distinctes, puis les reconvertit à la lecture.

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

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.

Étape suivante