Variables utilisateurs Mysql et utilisation de décimales

Version imprimable

28/02/2018, 15h50
xounet

Variables utilisateurs Mysql et utilisation de décimales

Bonjour,
Dans une requête assez longue je sélectionne une grande quantité de valeurs en provenance d'une table :

Code:

SELECT @v1:=SUM(val1),@v2:=SUM(val2-val1*val2),@v3:=SUM(val3) FROM decimales

Ces valeurs sont donc affectées aux variables @vx ci dessus.
Le code comporte plusieurs requêtes imbriquées dont l'une permet de calculer des sommes, produits... à l'aide de formules telles que :

Code:

SELECT @resultat1:=SUM(val3)/@v2 FROM decimales

Le problème constaté est une différence de précision selon les formules employées :
ex :

Code:

SELECT @resultat1:=@v3/SUM(val2-val1*val2) FROM decimales

ne donne pas (toujours) exactement la même valeur que :

Code:

SELECT @resultat1:=SUM(val3)/SUM(val2-val1*val2) FROM decimales

[edit] : la formule utilisant des variables utilisateurs ne donne pas (toujours) les mêmes résultats que la formule complète[edit]
Le problème supposé vient donc de la précision des décimales stockées dans les variables utilisateurs qui semble variable, puisque en effet si les variables sont définies avec plus de précision en utilisant CAST( xxx AS DECIMAL(10,2)) le résultat est bien le même.
LA question est donc de savoir comment déclarer ou quelle requête utiliser pour être sûr que toutes les variables utilisateurs ont bien la précision désirée (par exemple DECIMAL(10,2)) ?
Y a t il un moyen plus simple que d'écrire ceci à chaque fois :

Code:

SELECT @v:=CAST(SUM(val2-val1*val2) as DECIMAL(10,2)) .....

Peut on l'écrire une seule fois pour toute la requête ?
01/03/2018, 13h19
escartefigue

bonjour,

On est donc dans un problème de déclaration de variables dans le langage applicatif et non dans un problème de SGBD ou de langage SQL
Du coup ce serait plutôt dans le forum PHP que vous pourriez trouver une solution éventuelle

Mais AMHA c'et plutôt du ressort des normes de développement qu'il faut convenir du nombre de décimales des variables en fonction du type de contenu (prix, poids, longueur...)
01/03/2018, 14h39
SQLpro

Citation:

Envoyé par xounet

LA question est donc de savoir comment déclarer ou quelle requête utiliser pour être sûr que toutes les variables utilisateurs ont bien la précision désirée (par exemple DECIMAL(10,2)) ?
....
Peut on l'écrire une seule fois pour toute la requête ?

la réponse est simple : utiliser des vues et interdire l'accès direct aux tables (autrement dit implémenter le MED : Modèle Externe de Données), ce qui devrait être fait dans toutes les applications et que les développeurs ignorent. Cela permet d'obliger de passer par un calcul statifié dans la vue.

A +
01/03/2018, 18h53
escartefigue

Citation:

Envoyé par SQLpro

la réponse est simple : utiliser des vues et interdire l'accès direct aux tables (autrement dit implémenter le MED : Modèle Externe de Données), ce qui devrait être fait dans toutes les applications et que les développeurs ignorent. Cela permet d'obliger de passer par un calcul statifié dans la vue.

A +

OK sans la moindre réserve concernant l'argumentaire sur l'utilisation des vues, mais là ça ne changera rien au problème : si l'utilisateur transmet la valeur via une variable locale dont la précision est insuffisante, l'utilisation d'une vue ne changera rien

Salut xounet.

Citation:

Envoyé par xounet

Le problème supposé vient donc de la précision des décimales stockées dans les variables utilisateurs qui semble variable, puisque en effet si les variables sont définies avec plus de précision en utilisant CAST( xxx AS DECIMAL(10,2)) le résultat est bien le même.

En supposant que le problème vienne des variables intermédiaires, pourquoi les utilisez-vous ?
Sur un simple exemple (voire ci-après), je n'ai rencontré aucun problème avec vos requêtes.

Le problème que vous soulevez n'est pas très clair, car selon les nombres que vous manipulez (comptable, scientifique, fiscal), il existe des règles.
Par exemple, en ce qui concerne la tva, l'arrondi est toujours au plafond.
Pour le scientifique, l'arrondi est au plus près.
Pour le comptable, on ne fait pas d'arrondi (ou si vous préférez un arrondi au plancher).

Il y a aussi le choix du type (integer, deciaml ou double).

Il existe aussi un ordre qu'il faut respecter pour obtenir le bon résultat.
Si vous devez appliquer une suite d'opération, il est conseillé d'utiliser les parentèses, et de faire d'abord les multiplications puis ensuite les divisions.
Sans ce respect des priorités, on peut obtenir des résultats aberrants.

Comment faire ?

1) Utilisez des view, comme vous le conseil SQLPRO.

2) choisissez un arrondi si vous avez besoin de le faire.
Comme nous ne savons pas ce que représente vos valeurs, il est difficile de savoir si les arrondis ont un sens ou pas.

3) de même pour le calcul et la précision recherché.

4) ne passez pas par des variables intermédiaires.

Je reprends votre exemple sous la forme d'une vue :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`     integer unsigned  not null auto_increment primary key,
  `val1`   decimal(10,2)     not null,
  `val2`   decimal(10,2)     not null,
  `val3`   decimal(10,2)     not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`val1`,`val2`,`val3`) values
  (0.25, 14.17, 33.33),
  (0.15, 57.11, 27.27),
  (0.34, 44.79, 36.24)
--------------
 
--------------
select * from `test`
--------------
 
+----+------+-------+-------+
| id | val1 | val2  | val3  |
+----+------+-------+-------+
|  1 | 0.25 | 14.17 | 33.33 |
|  2 | 0.15 | 57.11 | 27.27 |
|  3 | 0.34 | 44.79 | 36.24 |
+----+------+-------+-------+
--------------
SELECT @v1:=SUM(val1),@v2:=SUM(val2-val1*val2),@v3:=SUM(val3) FROM test
--------------
 
+----------------+--------------------------+----------------+
| @v1:=SUM(val1) | @v2:=SUM(val2-val1*val2) | @v3:=SUM(val3) |
+----------------+--------------------------+----------------+
|           0.74 |                  88.7324 |          96.84 |
+----------------+--------------------------+----------------+
--------------
SELECT @resultat1:=SUM(val3)/@v2 FROM test
--------------
 
+---------------------------+
| @resultat1:=SUM(val3)/@v2 |
+---------------------------+
|      1.091371359278008934 |
+---------------------------+
--------------
SELECT @resultat1:=@v3/SUM(val2-val1*val2) FROM test
--------------
 
+-------------------------------------+
| @resultat1:=@v3/SUM(val2-val1*val2) |
+-------------------------------------+
|                1.091371359278008934 |
+-------------------------------------+
--------------
SELECT @resultat1:=SUM(val3)/SUM(val2-val1*val2) FROM test
--------------
 
+-------------------------------------------+
| @resultat1:=SUM(val3)/SUM(val2-val1*val2) |
+-------------------------------------------+
|                      1.091371359278008934 |
+-------------------------------------------+
--------------
SELECT @v:=CAST(SUM(val2-val1*val2) as DECIMAL(10,2)) from test
--------------
 
+------------------------------------------------+
| @v:=CAST(SUM(val2-val1*val2) as DECIMAL(10,2)) |
+------------------------------------------------+
|                                          88.73 |
+------------------------------------------------+
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
  select  res1,
          res2,
          res3,
          res4,
          res1/res2
    from  (  select                           sum(val3)                                     as res1,
                                              sum(val2 * (1 - val1))                        as res2,
                                  sum(val3) / sum(val2 * (1 - val1))                        as res3,
                      cast(round((sum(val3) / sum(val2 * (1 - val1))), 2) as decimal(10,2)) as res4
               from  `test`
          ) as x
--------------
 
--------------
select * from vue
--------------
 
+-------+---------+----------+------+-----------+
| res1  | res2    | res3     | res4 | res1/res2 |
+-------+---------+----------+------+-----------+
| 96.84 | 88.7324 | 1.091371 | 1.09 |  1.091371 |
+-------+---------+----------+------+-----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Comme on peut le voir, le résultat "1.09" est ce que l'on peut attendre si l'on applique un arrondi.

A priori, le problème ne vient pas de MySql, mais d'une mauvaise utilisation.

@+

Bonjour,
D'abord un grand merci pour ces indications.
Artemus24 tes indications sont intéressantes.
Je vais essayer d'être plus précis sur un exemple simple ce qui n'est pas facile car j'utilise des requêtes très longues et je ne garde pas toujours l'historique des différentes versions. Ces lignes de code sont copiées collées de la requête d'origine, il y a 2 valeurs retournées par la requête qui sont concaténées avec '|' la première est un nombre de jours et la deuxième est grosso modo un pourcentage en durée :

Ce premier code exécuté directement :
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT @date:='2016-12-31' UNION ALL SELECT CONCAT_WS('|', (SELECT @nb_jour:=DATEDIFF(DATE_ADD(date_2, INTERVAL duree YEAR),date_2)) , DATEDIFF( IF( IF(IFNULL(date_1,'9999-12-31')<@date,date_1,@date) < DATE_ADD(date_2, INTERVAL duree YEAR),IF(IFNULL(date_1,'9999-12-31')<@date,date_1,@date),DATE_ADD(date_2, INTERVAL duree YEAR) ) ,date_5) / DATEDIFF(DATE_ADD(date_2, INTERVAL duree YEAR),date_2)*100 ) FROM `ma_table` WHERE `valeur1`>=800 AND `statut`='réel' AND date_2<=@date
donne deux valeurs suivantes :

Citation:

2016-12-31
1096|52.8285
731|67.5787
1826|38.1161
1096|63.5036
365|23.5616
365|21.3699

et ce code qui est identique mais en utilisant la variable @nb_jour:
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT @date:='2016-12-31' UNION ALL SELECT CONCAT_WS('|', (SELECT @nb_jour:=DATEDIFF(DATE_ADD(date_2, INTERVAL duree YEAR),date_2)) , DATEDIFF( IF( IF(IFNULL(date_1,'9999-12-31')<@date,date_1,@date) < DATE_ADD(date_2, INTERVAL duree YEAR),IF(IFNULL(date_1,'9999-12-31')<@date,date_1,@date),DATE_ADD(date_2, INTERVAL duree YEAR) ) ,date_5) / @nb_jour*100 ) FROM `ma_table` WHERE `valeur1`>=800 AND `statut`='réel' AND date_2<=@date
donne :

Citation:

2016-12-31
1096|52.82846715328468
731|67.57865937072503
1826|38.11610076670318
1096|63.503649635036496
365|23.56164383561644
365|21.36986301369863

Par la suite j'ai aussi dans cette requête SUM( formule_avec_@nb_jour ) ) et le résultat ne donnait pas la même chose en remplaçant @nb_jour par DATEDIFF(DATE_ADD(date_2, INTERVAL duree YEAR),date_2)) dans SUM() ou en définissant @nb_jour:=CAST(DATEDIFF(DATE_ADD(date_2, INTERVAL duree YEAR),date_2) as DECIMAL(10,2) et la différence était de l'ordre de +ou-3 /100.
Je n'avais aucun problème de précision à 10e-3 lorsque je n'utilisais pas datediff() de mémoire...
J'utilise aussi php dans le programme mais celui ci arrondit les chiffres selon cette précision/format xxxxxxxxxx,xx à l'aide d'une fonction et uniquement pour afficher le résultat des requêtes et comme les écarts constatés sont de 0,03 parfois ça ne vient pas de php je pense.

Salut xounet.

Vous n'avez pas indiqué la précision que vous désirez obtenir ? Combien de chiffres après la virgule ?

Comme je vous l'ai dit précédemment, vous faites une mauvaise utilisation de MySql, surtout dans le cas de vos calculs.
Il y a trop de répétitions ce qui alourdi inutilement votre requête. Pour éviter cela, faites des imbrications de requêtes.
Une colonne créé dans la sous-requête, sera utilisé au niveau supérieur, ce qui vous évite de la redéfinir à chaque fois.

Etant donné que vous utilisez comme paramètre une date, vous ne pouvez pas utilisez les view.

Attention aux conversions implicites !
Pour obtenir une précision plus grande, vous pouvez modifier le "cast( ... as decimal(15,2))" en augmentant ce 2.
Dans l'exemple ci-après, je me suis limité à six chiffres après la virgule.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
--------------
SET AUTOCOMMIT = 0
--------------

--------------
START TRANSACTION
--------------

--------------
DROP DATABASE IF EXISTS `base`
--------------

--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------

--------------
DROP TABLE IF EXISTS `test`
--------------

--------------
CREATE TABLE `test`
( `id`       integer unsigned  not null auto_increment primary key,
  `date_1`   date              not null,
  `date_2`   date              not null,
  `date_5`   date              not null,
  `duree`    integer unsigned  not null,
  `valeur1`  integer unsigned  not null,
  `statut`   varchar(255)      not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------

--------------
insert into `test` (`date_1`,`date_2`,`date_5`,`duree`,`valeur1`,`statut`) values
  ('2016-12-31','2015-12-31','2015-06-01',3, 900, 'réel'),
  ('2016-12-31','2015-12-31','2015-08-25',2, 900, 'réel'),
  ('2016-12-31','2014-12-31','2015-02-04',5, 900, 'réel'),
  ('2016-12-31','2015-12-31','2015-02-04',3, 900, 'réel'),
  ('2016-12-31','2016-12-31','2016-10-06',1, 900, 'réel'),
  ('2016-12-31','2016-12-31','2016-10-14',1, 900, 'réel')
--------------

--------------
select * from `test`
--------------

+----+------------+------------+------------+-------+---------+--------+
| id | date_1     | date_2     | date_5     | duree | valeur1 | statut |
+----+------------+------------+------------+-------+---------+--------+
|  1 | 2016-12-31 | 2015-12-31 | 2015-06-01 |     3 |     900 | réel   |
|  2 | 2016-12-31 | 2015-12-31 | 2015-08-25 |     2 |     900 | réel   |
|  3 | 2016-12-31 | 2014-12-31 | 2015-02-04 |     5 |     900 | réel   |
|  4 | 2016-12-31 | 2015-12-31 | 2015-02-04 |     3 |     900 | réel   |
|  5 | 2016-12-31 | 2016-12-31 | 2016-10-06 |     1 |     900 | réel   |
|  6 | 2016-12-31 | 2016-12-31 | 2016-10-14 |     1 |     900 | réel   |
+----+------------+------------+------------+-------+---------+--------+
--------------
select  nbjour,
        (cast(datediff(if(val2 < val1, val2, val1), date_5) as decimal(15,2)) * 100) / nbjour as calc

  from  (  select  datediff(val1, date_2)           as nbjour,
                   if(date_1 < comp, date_1, comp)  as val2,
                   val1, date_5

            from  ( select  '2016-12-31'                 as comp,
                            date_2 + interval duree year as val1,
                            ifnull(date_1, '9999-12-31') as date_1,
                            date_2,
                            date_5

                      from  `test`
                     where  `valeur1` >= 800
                       and  `statut`   = 'réel'
                    having  `date_2`  <= comp
                  ) as x
        ) as y
--------------

+--------+-----------+
| nbjour | calc      |
+--------+-----------+
|   1096 | 52.828467 |
|    731 | 67.578659 |
|   1826 | 38.116101 |
|   1096 | 63.503650 |
|    365 | 23.561644 |
|    365 | 21.369863 |
+--------+-----------+
--------------
COMMIT
--------------

--------------
SET AUTOCOMMIT = 1
--------------

Appuyez sur une touche pour continuer...

En rouge, j'ai mis la date que vous utilisez comme paramètre.
Afin d'éviter une répétition inutile de cette date, j'ai utilisé un "having" afin d'y faire référence.

@+

03/03/2018, 14h14
xounet

Bonjour,
Merci pour cette réponse.
C'est justement pour éviter toutes ces lignes de codes que j'ai fait comme cela, les requêtes imbriquées cela est intéressant mais pas systématique dans mon cas.
Par contre je ne comprends pas pourquoi l'utilisation d'une variable utilisateur ou pas change la précision du résultat, tu indiques qu'il y un cast implicite peux tu préciser dans quel cas et comment l'éviter, c'est le sens de ma question : éviter des cast implicites ou alors les expliciter une fois pour toutes.
Concernant la précision attendue et bien il n'y a pas de limites, il faut autant de décimales qu'il est possible d'en obtenir sans être non plus dans le cas d'un calcul de masse du proton, les arrondis se font ultérieurement avec php.
Je trouve que la différence de précision entre les deux formules n'est pas cohérente d'ailleurs j'aurais plutôt pensé que @nb_jour étant entier provoquerai un arrondi dans les calculs, alors que c'est l'inverse.

Salut xounet.

Citation:

Envoyé par xounet

C'est justement pour éviter toutes ces lignes de codes que j'ai fait comme cela, les requêtes imbriquées cela est intéressant mais pas systématique dans mon cas.

Il s'agit de faire sobre en "évitant toutes ces lignes de codes".
Il s'agit de rendre la requête plus lisible et d'éviter des répétitions inutiles qui viennent plomber la performance.

Citation:

Envoyé par xounet

Par contre je ne comprends pas pourquoi l'utilisation d'une variable utilisateur ou pas change la précision du résultat, ...

A cause du type qui est associé à chaque colonne utilisée dans votre requête.
Soit elle est explicite, comme dans la déclaration des colonnes de vos tables. C'est vous qui dites quel type utilisé.
Soit elle est implicite, comme dans un calcul. Là, c'est MySql qui décide à votre place.

J'ai décomposé la requête en plusieurs vues et je vous donne, ci-après, le résultat de la dernière vue, en décomposant le calcul.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 -------------- drop view if exists `vue3` -------------- -------------- create view `vue3` as select nbjour, datediff(if(val2 < val1, val2, val1), date_5) as calc1, datediff(if(val2 < val1, val2, val1), date_5) * 100 as calc2, datediff(if(val2 < val1, val2, val1), date_5) * 100.00 as calc3, (datediff(if(val2 < val1, val2, val1), date_5) * 100.00) / nbjour as calc4, (cast(datediff(if(val2 < val1, val2, val1), date_5) as decimal(15,2)) * 100.00) / nbjour as calc5 from `vue2` -------------- -------------- select * from `vue3` -------------- +--------+-------+-------+----------+-----------+-------------+ | nbjour | calc1 | calc2 | calc3 | calc4 | calc5 | +--------+-------+-------+----------+-----------+-------------+ | 1096 | 579 | 57900 | 57900.00 | 52.828467 | 52.82846715 | | 731 | 494 | 49400 | 49400.00 | 67.578659 | 67.57865937 | | 1826 | 696 | 69600 | 69600.00 | 38.116101 | 38.11610077 | | 1096 | 696 | 69600 | 69600.00 | 63.503650 | 63.50364964 | | 365 | 86 | 8600 | 8600.00 | 23.561644 | 23.56164384 | | 365 | 78 | 7800 | 7800.00 | 21.369863 | 21.36986301 | +--------+-------+-------+----------+-----------+-------------+ -------------- explain vue3 -------------- +--------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------+------+-----+---------+-------+ | nbjour | int(7) | YES | | NULL | | | calc1 | int(7) | YES | | NULL | | | calc2 | bigint(10) | YES | | NULL | | | calc3 | decimal(11,2) | YES | | NULL | | | calc4 | decimal(15,6) | YES | | NULL | | | calc5 | decimal(24,8) | YES | | NULL | | +--------+---------------+------+-----+---------+-------+
J'ai volontairement décomposé le calcul afin de bien montrer la conversion implicite faite par MySql.
Que remarquons-nous ?

Calc1 est en integer.
Calc2 est en bitinteger. "100" est du type integer.
Calc3 devient du décimal parce que "100.00" est maintenant du type décimal. Le résultat est avec deux chiffres après la virgule.
Calc4 devient un décimal mais avec six chiffres après la virgule.
Calc5, mon exemple, est aussi en décimal mais avec huit chiffres après la virgule.

Si vous laissez faire MySql, il va choisir le type qui lui convient le mieux.
Sinon, c'est à vous de préciser ce que vous désirez obtenir comme précision.

Citation:

Envoyé par xounet

... tu indiques qu'il y un cast implicite peux-tu préciser dans quel cas et comment l'éviter, c'est le sens de ma question : éviter des cast implicites ou alors les expliciter une fois pour toutes.

Peut-on éviter les conversions implicites ?

Dans un langage comme le 'C', chaque variable doit être déclarée au préalable dans un type de donnée.
Même en faisant cela, il arrive parfois que l'on soit obligé de faire un calcul avec des entiers et des décimaux.
Le système devra faire un choix entre le type entier ou le type décimal.
Pour éviter les surprises, il y a deux choix possibles :
1) soit explicitement, vous faites en sorte au moment de la déclaration de ces variables, de mettre votre entier dans le type décimal.
2) soit dans le calcul, vous convertissez explicitement l'entier en décimal, en faisant usage d'un cast.

La première solution est la mieux car dès le départ, vous allez travailler dans le même type.
De ce fait, au moment du calcul, aucune conversion ne se fera et vous gagnerez en performance.
Surtout si vous vous trouvez dans une boucle de plusieurs millions d'itérations.

Oui, on peut les éviter, en faisant en sorte que toutes vos variables soient dans le même type.

Dans votre cas, vous ne pouvez pas éviter les conversions implicites puisque vous utilisez des fonctions produisant des types différents, comme on vient de le voir dans mon exemple ci-dessus.

Comment faire ? En faisant au mieux !
J'ai préféré appliquer une seule conversion dans le calcul, même si le résultat produit plus de chiffres après la virgule que ce qui est prévu.
Si vous désirez un maximum de chiffres après la virgule, voici ce que je propose :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 -------------- create view `vue3` as select nbjour, (cast(datediff(if(val2 < val1, val2, val1), date_5) as decimal(65,30)) * 100.00) / nbjour as calc5 from `vue2` -------------- -------------- select * from `vue3` -------------- +--------+-----------------------------------+ | nbjour | calc5 | +--------+-----------------------------------+ | 1096 | 52.828467153284671532846715328467 | | 731 | 67.578659370725034199726402188782 | | 1826 | 38.116100766703176341730558598028 | | 1096 | 63.503649635036496350364963503650 | | 365 | 23.561643835616438356164383561644 | | 365 | 21.369863013698630136986301369863 | +--------+-----------------------------------+ -------------- explain vue3 -------------- +--------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------+------+-----+---------+-------+ | nbjour | int(7) | YES | | NULL | | | calc5 | decimal(65,30) | YES | | NULL | | +--------+----------------+------+-----+---------+-------+
--> https://dev.mysql.com/doc/refman/8.0...teristics.html

@+

Bonjour,
Merci pour ta réponse, toujours aussi complète cela m'a permis d'avancer un peu plus.
Je viens de faire quelques tests pour voir le comportement de mysql et il y des choses que je n'explique pas :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT @test:=1 UNION ALL SELECT @test UNION ALL SELECT @test/3.00000000000000000000000 UNION ALL SELECT @test/3 UNION ALL SELECT CAST(@test/3 as DECIMAL(65,30)) UNION ALL SELECT CAST(@test/3.0000000000000000000000000000000000000000 as DECIMAL(65,30)) UNION ALL SELECT CAST(1.00000000000000000000000000000000000000000/3.0000000000000000000000000000000000000000 as DECIMAL(65,30))
Cela m'affiche :

Citation:

1
1
0.3333333333333333
0.3333333333333333
0.333333333333333300000000000000
0.333333333333333300000000000000
0.333333333333333333333333333333

Alors que :
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT @test:=1 UNION ALL SELECT @test/3.00000000000000000000000 UNION ALL SELECT @test/3 UNION ALL SELECT CAST(@test/3 as DECIMAL(65,30)) UNION ALL SELECT CAST(@test/3.0000000000000000000000000000000000000000 as DECIMAL(65,30)) UNION ALL SELECT CAST(1.00000000000000000000000000000000000000000/3.0000000000000000000000000000000000000000 as DECIMAL(65,30))
Affiche :

Citation:

1
0.3333333333333333
0.3333333333333333
0.3333333333333333
0.3333333333333333
0.3333333333333333

et enfin :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT @test:=1.00000000000000000000000000000000000000000 UNION ALL SELECT @test UNION ALL SELECT @test/3.00000000000000000000000 UNION ALL SELECT @test/3 UNION ALL SELECT CAST(@test/3 as DECIMAL(65,30)) UNION ALL SELECT CAST(@test/3.0000000000000000000000000000000000000000 as DECIMAL(65,30)) UNION ALL SELECT CAST(1.00000000000000000000000000000000000000000/3.0000000000000000000000000000000000000000 as DECIMAL(65,30))
Donne :

Citation:

1.00000000000000000000000000000000000000000
1.00000000000000000000000000000000000000000
0.3333333333333333
0.3333333333333333
0.333333333333333300000000000000
0.333333333333333300000000000000
0.333333333333333333333333333333

Si j'arrive à trouver l'explication je pense pouvoir résoudre mon problème de précision ou mieux le maîtriser en tous les cas.

05/03/2018, 14h29
aieeeuuuuu

Bonjour,

Vous assignez et selectionnez la même variable dans un même SELECT ce qui est à éviter.

un peu de lecture...
avc notamment :

Citation:

If the value of a user variable is selected in a result set, it is returned to the client as a string.

(ce qui peut changer le type de la colonne entre vos différentes requete UNION).

aussi :

Citation:

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement.
[...]
For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed.
[...]
the order of evaluation for expressions involving user variables is undefined.

Et surtout :

Citation:

Assignment of decimal and real values does not preserve the precision or scale of the value

Salut xounet.

Un nombre avec un point décimal est considéré comme un décimal. Dans le cas contraire, il s'agit d'un entier.

Un nombre décimal est transcrit sous forme binaire, à l'inverse du gros système (IBM ou BULL) qui est codifié DCB.
Le nombre de chiffres sera stocké dans zéro à quatre octets, comme ci-après dans le tableau des correspondances :
Code:

1 2 3 4 5 6 7 8 9 +--------------------+-----------------+ | Nombre de chiffres | nombre d'octets | +--------------------+-----------------+ | 0 | 0 | | 1 à 2 | 1 | | 3 à 4 | 2 | | 5 à 6 | 3 | | 7 à 9 | 4 | +--------------------+-----------------+
Un nombre décimal de neuf chiffres sera stocké au maximum dans quatre octets.
Si par exemple nous avons "decimal(30,6)" nous avons 24 chiffres en partie entière et 6 chiffres en partie fractionnaire.
Pour la partie entière, 24 se décompose en 9+9+6, ce qui donne 4+4+3=11 octets. Pour la partie fractionnaire, 6 donne 3 octets.
Au total, ce nombre sera stocké dans 11+6=17 octets.
Ce qui importe pour la précision du nombre, c'est le nombre d'octet utilisé pour la partie fractionnaire.

Lors d'une opération, MySql va choisir en fonction des types des nombres, la meilleure représentation possible du stockage en mémoire. Par exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 -------------- select cast(1/3 as decimal(65,30)) -------------- +----------------------------------+ | cast(1/3 as decimal(65,30)) | +----------------------------------+ | 0.333333333000000000000000000000 | +----------------------------------+ -------------- select cast(1.0/3 as decimal(65,30)) -------------- +----------------------------------+ | cast(1.0/3 as decimal(65,30)) | +----------------------------------+ | 0.333333333000000000000000000000 | +----------------------------------+ -------------- select cast(1/3.0 as decimal(65,30)) -------------- +----------------------------------+ | cast(1/3.0 as decimal(65,30)) | +----------------------------------+ | 0.333333333000000000000000000000 | +----------------------------------+ -------------- select cast(1.0/3.0 as decimal(65,30)) -------------- +----------------------------------+ | cast(1.0/3.0 as decimal(65,30)) | +----------------------------------+ | 0.333333333333333333000000000000 | +----------------------------------+
Comme on le voie ici, opération entier sur entier donne la précision du decimal.
Opération décimal et entier donne la même précision que celle du décimal.
Opération sur deux décimaux, il y a addition de la précision de chacun de ces deux décimaux.
La précision se fait par paquet de neuf chiffres, soit un stockage en memoire d'un multiple de quatre octets.

Il ne faut pas confondre le stockage en mémoire avec le format de la représentation de ce nombre à l'affichage.
Quand j'ai fait la division, le résultat est stocké en mémoire, donc non visible.
Quand je demande le résultat de ce nombre, il s'agit alors du format à l'affichage.
Même si je demande 30 chiffres à l'affichage dans la partie fractionnaire, je n'obtiens que les chiffres qui sont stockés en mémoire.

Comme je vous l'ai dit précédemment, évitez d'utiliser des variables intermédiaires (User-Defined Variables) dans vos calculs.
Utilisez dans vos tables un type qui vous conviendra au mieux des calculs que vous allez faire.
Si vous avez besoin du maximum de précision pour un pourcentage, mettez "decimal(33,30)".
Reprenez mon exemple, avec des requêtes imbriquées en utilisant la conversion explicite.

@+

05/03/2018, 17h16
xounet

Merci aieeeuuuuu !
J'étais en train de lire cette documentation justement et effectivement il faut prendre des précautions en utilisant les variables, notamment au niveau de la précision, le cast implicite en string ne pose aucun problème en général ni même l'assignation et la sélection simultanée.

J'ai résolu ce problème en m'inspirant des précédents post notamment artemus24. Ca pouvait se deviner, mais sous MYSQL, la meilleure solution que j'ai trouvée qui passe vraiment partout c'est d'indiquer à chaque étape de calcul ou formule la précision des nombres employés en multipliant celui-ci par "1.0000000000000000000000000" (voir peut être plus...) et d'éviter les variables utilisateurs (et peut être passer par des requêtes imbriquées à la place ) (lorsque l'on souhaite une grande précisions).

Je conseille donc d'écrire par exemple :

Code:

SELECT 1.0000000000000000000000000+2*1.0000000000000000000000000/(3*1.0000000000000000000000000)

au lieu de

Code:

SELECT 1+2/3

De même j'ai amélioré la précision des calculs comme ceci :

Code:

SELECT ( DATEDIFF(date1,date2)*1.0000000000000000000000000 ) / ( duree*1.0000000000000000000000000 )

Et dans d'autres fonctions aussi :

Code:

SELECT LEAST(pourcentage*1.0000000000000000000000000,1.0000000000000000000000000),GREATEST(pourcentage*1.0000000000000000000000000,0.0000000000000000000000000)

Enfin, privilégiez les requêtes imbriquées ou tables temporaires :

Code:

SELECT CONCAT_WS('|',2*un_tier,@test/(3*1.0000000000000000000000000)) FROM ( SELECT 1*1.0000000000000000000000000/(3*1.0000000000000000000000000) as un_tier,(SELECT @test:=1.0000000000000000000000000) ) T

donne :
0.66666666666666666666666666666|0.3333333333333333

Je pense qu'il y a d'autres exemples sur le même principe notamment avec les fonctions mathématiques, en tous les cas c'est efficace et vraiment nécessaire.
05/03/2018, 17h45
xounet
Citation:

Envoyé par Artemus24

Salut xounet.

Reprenez mon exemple, avec des requêtes imbriquées en utilisant la conversion explicite.

@+

Dans mon cas je ne vois pas comment imbriquer les requêtes puisque je fais SUM() :
Code:

1 2 SELECT (SELECT @nb_jour:=DATEDIFF(DATE_ADD(date_2, INTERVAL duree YEAR),date_2)) ),SUM ( formule_avec_@nb_jour ) FROM ma_table WHERE CONDITIONS
ça ne peut pas marcher
06/03/2018, 11h51
Artemus24

Salut xounet.

Vous faites n'importe quoi.
Pourquoi manipulez-vous des nombres dans vos exemples alors que vous manipulez des colonnes ?

C'est idiot ce genre de déclaration :

Code:

SELECT ( DATEDIFF(date1,date2)*1.0000000000000000000000000 ) / ( duree*1.0000000000000000000000000 )

Par contre ça, vous devez utiliser la conversion explicite :

Code:

SELECT CAST(DATEDIFF(date1,date2) as DECIMAL (65,30) / duree

Citation:

Envoyé par xounet

ça ne peut pas marcher

Un exemple plus réaliste avec un jeu d'essai sur le bienvenue.
Pour répondre partiellement à votre question, utilisez les jointures.

@+
06/03/2018, 14h08
xounet
Artemus24,
tu penses que c'est n'importe quoi mais ayant essayé plusieurs solutions dont le CAST(... DECIMAL(65,30)), la seule qui aboutit à une résultat plus précis et dans tous les cas de figures c'est bien celle que j'ai donnée un peu plus haut et aucune autre !
Après tu fais comme tu veux si tu penses arriver au même résultat tant mieux.
En revanche concernant les requêtes imbriquées je ne suis pas sûr que cela soit efficace car cela oblige à créer une table intermédiaire et cela peut surcharger la mémoire vive ou le processeur. Ne vaut il pas mieux écrire directement la valeur de "x" (date-val) dans la somme dans cet exemple plutôt que d'avoir T2 ?
Code:

1 2 3 4 5 SELECT SUM(x+45/x*val+val1*x) FROM ( SELECT date-val as x, date,val,val1 FROM ( SELECT 1 as date,2 as val,3 as val1 UNION ALL SELECT 45 as date,278 as val,43 as val1) T ) T2

Salut xounet.

Je n'ai pas mis le '-1' que vous avez obtenu dans votre dernier message.
Cela sous-entend que quelqu'un d'autre que moi, n'est pas d'accord avec vos propos.

Citation:

Envoyé par xounet

tu penses que c'est n'importe quoi mais ayant essayé plusieurs solutions dont le CAST(... DECIMAL(65,30)), la seule qui aboutit à un résultat plus précis et dans tous les cas de figures c'est bien celle que j'ai donnée un peu plus haut et aucune autre !

Vous ne désirez pas modifier votre requête et de ce fait, vous ne savez pas comment passer d'un type integer au type decimal avec un maximum de précision.

La solution, je vous l'ai communiqué, dans un exemple précédent, que je reproduis ci-après :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 select nbjour, (cast(datediff(if(val2 < val1, val2, val1), date_5) as decimal(65,30)) * 100) / nbjour as calc from ( select datediff(val1, date_2) as nbjour, if(date_1 < comp, date_1, comp) as val2, val1, date_5 from ( select '2016-12-31' as comp, date_2 + interval duree year as val1, ifnull(date_1, '9999-12-31') as date_1, date_2, date_5 from `test` where `valeur1` >= 800 and `statut` = 'réel' having `date_2` <= comp ) as x ) as y -------------- +--------+-----------------------------------+ | nbjour | calc | +--------+-----------------------------------+ | 1096 | 52.828467153284671532846715328467 | | 731 | 67.578659370725034199726402188782 | | 1826 | 38.116100766703176341730558598028 | | 1096 | 63.503649635036496350364963503650 | | 365 | 23.561643835616438356164383561644 | | 365 | 21.369863013698630136986301369863 | +--------+-----------------------------------+
La conversion explicite, vous ne devez pas la faire sur toutes vos colonnes, mais juste sur la première fonction, sur "datediff".

Citation:

Envoyé par xounet

Après tu fais comme tu veux si tu penses arriver au même résultat tant mieux.

C'est vous qui êtes venu chercher des conseils. En ce qui me concerne, je sais faire et je n'ai aucun problème avec la précision des types decimaux.

Citation:

Envoyé par xounet

En revanche concernant les requêtes imbriquées je ne suis pas sûr que cela soit efficace car cela oblige à créer une table intermédiaire et cela peut surcharger la mémoire vive ou le processeur.

Si vous croyez être dans le vrai, je vais m'abstenir de vous donner d'autres conseils, car vous semblez maîtriser votre problème.

Mettez votre sujet à résolu !

@+

07/03/2018, 09h08
xounet

Oui c'est résolu en suivant ce que j'ai écrit : https://www.developpez.net/forums/d1.../#post10058807