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

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

  • Dans le dialecte PostgreSQL, NUMERIC est un type numérique de précision décimale arbitraire (l'échelle ou la précision peut correspondre à n'importe quel nombre compris dans la plage acceptée). Il s'agit donc d'un choix idéal pour stocker des données numériques de précision arbitraire.

  • Dans GoogleSQL, NUMERIC est un type numérique à précision fixe (precision=38 et scale=9) qui 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 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 de 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 sans 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 avec tous les zéros finaux supprimés) de précision décimale. Nous indiquons que ce type représente des valeurs numériques décimales approximatives, car la représentation binaire IEEE 64 bits à virgule flottante utilisée par Spanner ne peut pas représenter avec précision les fractions décimales (base 10) (elle ne peut représenter exactement que les 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 reconvertit la 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 n'ayant que 15 à 17 chiffres significatifs de précision (seuls certains nombres comportant plus de 15 chiffres décimaux peuvent être représentés par un float 64 bits sans arrondi). 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, lorsqu'une précision de 30 chiffres ou plus est généralement requise.

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 avec 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 GoogleSQL NUMERIC est comprise entre -99999999999999999999999999999,999999999 et 99999999999999999999999999999.999999999.

Le type de 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 à l'application du nombre et la valeur de 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).

Le stockage du nombre sous forme de chaîne présente l'avantage d'être stocké avec une précision exacte (jusqu'à la limite de longueur de colonne STRING / VARCHAR), et 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 approximatifs 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 les valeurs entières ajustées 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 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 entière (INT64 / INT8) dans deux colonnes distinctes, puis les reconvertit à la lecture.

Dans 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.

Étapes suivantes