Calculs avec les valeurs numériques

Dans ce contexte, le terme calcul fait référence aux opérations mathématiques binaires : addition, soustraction, multiplication et division. Cette section décrit les types de retour attendus pour ces opérations, ainsi que la formule spécifique appliquée pour déterminer la précision et l’échelle lorsque les types de données DECIMAL sont impliqués.

Lorsque des valeurs numériques sont calculées pendant le traitement des requêtes, vous pouvez rencontrer des cas où le calcul est impossible et où la requête renvoie une erreur de dépassement de capacité numérique. Vous pouvez également rencontrer des cas où l’échelle des valeurs calculées varie ou est inattendue. Pour certaines opérations, vous pouvez utiliser un transtypage explicite (promotion de type) ou des paramètres de configuration Amazon Redshift pour contourner ces problèmes.

Pour plus d’informations sur les résultats de calculs similaires avec les fonctions SQL, consultez Fonctions d’agrégation.

Types de retour pour les calculs

Compte tenu de l’ensemble des types de données numériques pris en charge par Amazon Redshift, le tableau suivant présente les types de retour attendus pour les opérations d’addition, de soustraction, de multiplication et de division. La première colonne sur la gauche du tableau représente le premier opérande dans le calcul et la ligne du haut le second opérande.

	INT2	INT4	INT8	DECIMAL	FLOAT4	FLOAT8
INT2	INT2	INT4	INT8	DECIMAL	FLOAT8	FLOAT8
INT4	INT4	INT4	INT8	DECIMAL	FLOAT8	FLOAT8
INT8	INT8	INT8	INT8	DECIMAL	FLOAT8	FLOAT8
DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	FLOAT8	FLOAT8
FLOAT4	FLOAT8	FLOAT8	FLOAT8	FLOAT8	FLOAT4	FLOAT8
FLOAT8	FLOAT8	FLOAT8	FLOAT8	FLOAT8	FLOAT8	FLOAT8

Précision et échelle des résultats DECIMAL calculés

Le tableau suivant résume les règles de calcul de la précision et de l’échelle obtenues lorsque les opérations mathématiques retournent des résultats DECIMAL. Dans cette table, p1 et s1 représentent la précision et l’échelle du première opérande d’un calcul, tandis que p2 et s2 représentent la précision et l’échelle du second opérande. (Quels que soient les calculs, la précision maximale du résultat est de 38 et l’échelle maximale du résultat de 38 également.)

Opération	Précision et échelle du résultat
+ ou -	Évolutivité = max(s1,s2) Précision = max(p1-s1,p2-s2)+1+scale
*	Évolutivité = s1+s2 Précision = p1+p2+1
/	Évolutivité = max(4,s1+p2-s2+1) Précision = p1-s1+ s2+scale

Par exemple, les colonnes PRICEPAID et COMMISSION de la table SALES sont toutes deux des colonnes DECIMAL(8,2). Si vous divisez PRICEPAID par COMMISSION (ou inversement), la formule est appliquée comme suit :

Precision = 8-2 + 2 + max(4,2+8-2+1)
= 6 + 2 + 9 = 17

Scale = max(4,2+8-2+1) = 9

Result = DECIMAL(17,9)

Le calcul suivant constitue la règle générale pour le calcul de la précision et de l’échelle obtenues dans le cas des opérations effectuées sur les valeurs DECIMAL avec les opérateurs définis tels que UNION, INTERSECT et EXCEPT, ou les fonctions comme COALESCE et DECODE :

Scale = max(s1,s2)
Precision = min(max(p1-s1,p2-s2)+scale,19)

Par exemple, une table DEC1 avec une colonne DECIMAL(7,2) est jointe à une table DEC2 avec une colonne DECIMAL(15,3) pour créer une table DEC3. Le schéma DEC3 indique que la colonne devient une colonne NUMERIC(15,3).

create table dec3 as select * from dec1 union select * from dec2;

Résultat

select "column", type, encoding, distkey, sortkey
from pg_table_def where tablename = 'dec3';

column |     type      | encoding | distkey | sortkey 
-------+---------------+----------+---------+---------
c1     | numeric(15,3) | none     | f       | 0       

Dans l’exemple ci-dessus, la formule est appliquée comme suit :

Precision = min(max(7-2,15-3) + max(2,3), 19)
= 12 + 3 = 15

Scale = max(2,3) = 3

Result = DECIMAL(15,3)

Remarques sur les opérations de division

Pour les opérations de division, divide-by-zero les conditions renvoient des erreurs.

La limite d’échelle de 100 est appliquée après le calcul de la précision et de l’échelle. Si l’échelle de résultat calculée est supérieure à 100, les résultats de la division sont mis à l’échelle comme suit :

• Précision = precision - (scale - max_scale)

• Évolutivité = max_scale

Si la précision calculée est supérieure à la précision maximale (38), la précision est réduite à 38, et l’échelle devient le résultat de : max((38 + scale - precision), min(4, 100))

Conditions de dépassement de capacité

Le dépassement de capacité est contrôlé pour tous les calculs numériques. Les données DECIMAL avec une précision de 19 ou moins sont stockées en tant qu’entiers 64 bits. Les données DECIMAL avec une précision supérieure à 19 sont stockées sous forme d’entiers 128 bits. La précision maximale de toutes les valeurs DECIMAL est 38 et l’échelle maximale 37. Les erreurs de dépassement de capacité se produisent quand une valeur dépasse ces limites, qui s’appliquent aux jeux de résultats intermédiaires et finaux :

• Le transtypage explicite se traduit par des erreurs de dépassement de capacité à l’exécution lorsque les valeurs de données spécifiques ne correspondent pas à la précision ou à l’échelle spécifiée par la fonction cast. Par exemple, vous ne pouvez pas effectuer une conversion de type de toutes les valeurs de la colonne PRICEPAID de la table SALES (une colonne DECIMAL(8,2)) et retourner un résultat DECIMAL(7,3) :

  select pricepaid::decimal(7,3) from sales;
  ERROR:  Numeric data overflow (result precision)

  Cette erreur se produit, parce que certaines des valeurs les plus grandes de la colonne PRICEPAID ne peuvent pas être converties.

• Les opérations de multiplication produisent des résultats dans lesquels l’échelle du résultat est la somme des échelles de chaque opérande. Si les deux opérandes ont une échelle de 4, par exemple, l’échelle du résultat est 8, ce qui ne laisse que 10 chiffres à gauche de la virgule. Par conséquent, il est relativement facile de se trouver en situation de dépassement de capacité lors de la multiplication de deux grands nombres ayant une échelle significative.

  L’exemple suivant entraîne une erreur de dépassement de capacité.

  SELECT CAST(1 AS DECIMAL(38, 20)) * CAST(10 AS DECIMAL(38, 20));
  ERROR: 128 bit numeric data overflow (multiplication)

  Vous pouvez contourner l’erreur de dépassement de capacité en utilisant la division au lieu de la multiplication. Utilisez l’exemple suivant pour diviser par 1 divisé par le diviseur d’origine.

  SELECT CAST(1 AS DECIMAL(38, 20)) / (1 / CAST(10 AS DECIMAL(38, 20)));
  +----------+
  | ?column? |
  +----------+
  | 10       |
  +----------+

Calculs numériques avec les types INTEGER et DECIMAL

Lorsqu’un des opérandes d’un calcul est de type de données INTEGER et que l’autre opérande est DECIMAL, l’opérande INTEGER est implicitement converti en DECIMAL :

• INT2 (SMALLINT) est converti en DECIMAL(5,0)

• INT4 (INTEGER) est converti en DECIMAL(10,0)

• INT8 (BIGINT) est converti en DECIMAL(19,0)

Par exemple, si vous multipliez SALES.COMMISSION, colonne DECIMAL(8,2) et SALES.QTYSOLD, colonne SMALLINT, le calcul est converti comme suit :

DECIMAL(8,2) * DECIMAL(5,0)