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 (GoogleSQL et PostgreSQL), en particulier autour des des 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 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 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 de la méthode FLOAT64 / FLOAT8 type de données, la représentation binaire redevient une valeur décimale de 0,20000000000000001 (à 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 n’ayant que 15-17 bits chiffres significatifs de précision (seuls certains nombres comportant plus de 15 décimales chiffres peuvent être représentés par une valeur flottante de 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 permet représentant des valeurs numériques de précision décimale exacte ayant une précision supérieure à comportant plus de 30 chiffres décimaux.

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

Le dialecte PostgreSQL NUMERIC peut représenter des nombres avec un 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 table Spanner et que vous avez besoin d'une plus grande précision que celle fournie par NUMERIC, nous vous recommandons de stocker la valeur sous forme de représentation décimale dans un 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 La représentation interne à l'application du numéro et les éléments STRING / VARCHAR pour les lectures et les écritures de 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 a l'avantage d'être stocké avec la précision exacte (jusqu'à la limite de longueur de colonne STRING / VARCHAR), et la valeur restent lisibles 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 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 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 en utilisant 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 des entiers (INT64 / INT8) dans deux colonnes distinctes, et 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