Discussion :

[xounet]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Peut on l'écrire une seule fois pour toute la requête ?

[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...)

[SQLpro]

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 +

[escartefigue]

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

[Artemus24]

Salut 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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

@+

[xounet]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

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.

[Artemus24]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

@+

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

[Artemus24]

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

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

Sélectionner tout - Visualiser dans une fenêtre à part

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

@+

[xounet]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

1
1
0.3333333333333333
0.3333333333333333
0.333333333333333300000000000000
0.333333333333333300000000000000
0.333333333333333333333333333333

Alors que :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

1
0.3333333333333333
0.3333333333333333
0.3333333333333333
0.3333333333333333
0.3333333333333333

et enfin :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

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.

[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 :

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 :

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 :

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

[Artemus24]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

@+

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

au lieu de

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT 1+2/3

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Et dans d'autres fonctions aussi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[xounet]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

@+

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Artemus24]

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.

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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.

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 !

@+

[xounet]

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