Salaire médian classé par sexe et CSP

**percevalseb** · 22/10/2018, 15h32

Bonjour.

Voilà, pour une entreprise donnée, je dois calculer le salaire médian de ses employés et classer les résultats par sexe et CSP (cadres, ouvriers etc...)
On me donne pour contrainte de réaliser le calcul en une seule requête

J'ai trouvé de nombreux exemples sur le web sur le calcul d'une médiane mais rien sur la possibilité de classifier les résultats sur plusieurs champs, à savoir dans mon cas sexe et CSP, ce qui doit renvoyer plusieurs lignes et non une seule.

Ma table SALAIRES comporte donc les champs id, sexe, csp, brut_soumis.

Pour rappel, le définition d'une médiane est une valeur x qui permet de couper l'ensemble des valeurs en deux parties égales.
Malheureusement, il n'existe pas en SQL de fonction de calcul comme AVG().

J'ai trouvé ce lien qui comporte de nombreux exemples de requêtes http://www.sqlspot.com/sites/sqlspot...dianeEnSQL.pdf
Mais je n'ai pas réussi à les aménager après de nombreux essais infructueux, notamment en incluant des clauses GROUP BY qui me semblent indispensables à la solution de mon problème.

Toutes les suggestions sont les bienvenues, le problème est vraiment ardu !!!

**Oishiiii** · 22/10/2018, 17h44

Bonjour,

La solution peut être très différente d'un SGBD à l'autre. Quel est le vôtre ?

Par exemple sur SQL Server:

Code :

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

1
2
3
4
5
select distinct
	sexe
	, csp
	, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY brut_soumis) OVER (PARTITION BY sexe, csp) AS Mediane
from salaires;

**percevalseb** · 22/10/2018, 18h11

Bonjour, pardonnez-moi de ne pas l'avoir spécifié, j'utilise MySQL.

**percevalseb** · 23/10/2018, 15h01

Existe-t-il une alternative avec MySQL de l'emploi de cette formule ?

PERCENTILE_CONT ( numeric_literal )
WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
OVER ( [ <partition_by_clause> ] )

**SQLpro** · 23/10/2018, 17h38

Lisez l'article que j'ai écrit à ce sujet :
https://sqlpro.developpez.com/optimisation/mediane/

A +

**percevalseb** · 24/10/2018, 15h23

J'ai justement déjà consulté cette documentation, mais les requêtes ne retournent qu'un seul résultat et j'ai besoin d'un résultat multiple.

Voici un exemple simplifié de ce à quoi je suis confronté

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
CREATE TABLE `salaires` (
  `id` tinyint(4) NOT NULL,
  `sexe` char(1) NOT NULL,
  `csp` varchar(16) NOT NULL,
  `net` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
INSERT INTO `salaires` (`id`, `sexe`, `csp`, `net`) VALUES
(1, 'F', 'ouvrier', 1000),
(2, 'F', 'ouvrier', 1200),
(3, 'F', 'ouvrier', 1300),
(4, 'F', 'cadre', 2500),
(5, 'H', 'ouvrier', 1000),
(6, 'H', 'ouvrier', 1000),
(7, 'H', 'ouvrier', 1200),
(8, 'H', 'cadre', 2400),
(9, 'H', 'cadre', 2500),
(10, 'H', 'cadre', 2600),
(11, 'H', 'cadre', 3000);

j'aurai besoin d'afficher comme résultat :

Code :

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

1
2
3
4
5
6
 
sexe     csp       salaire_median
F        ouvrier   1200
F        cadre     2500
H        ouvrier   1000
H        cadre     2550

Il y a tous les cas de figure, nombres de lignes impairs, pairs, doublons etc...

**percevalseb** · 24/10/2018, 15h26

J'ai essayé de modifier cette requête, mais tout ce que je fais est faux

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
SELECT AVG(DISTINCT net) AS MEDIANE
FROM   (SELECT net
        FROM   (SELECT *
                FROM   salaires
                UNION  ALL
                SELECT *
                FROM   salaires) STT
        WHERE  (SELECT COUNT(*)
                FROM   salaires) 
                    <= (SELECT COUNT(*)
                        FROM   (SELECT *
                                FROM   salaires
                                UNION  ALL
                                SELECT *
                                FROM   salaires) AS SOU
                        WHERE  SOU.net <= STT.net)
           AND (SELECT COUNT(*)
                FROM   salaires) 
                    <= (SELECT COUNT(*)
                        FROM   (SELECT *
                                FROM   salaires
                                UNION  ALL
                                SELECT *
                                FROM   salaires) AS SUR
                        WHERE  SUR.net >= STT.net) ) AS T;

**Artemus24** · 27/10/2018, 19h36

Salut à tous.

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
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `salaire`
--------------
 
--------------
CREATE TABLE `salaire`
( `tirage_id`  integer unsigned  NOT NULL auto_increment primary key,
  `sexe`          char(01)       NOT NULL,
  `csp`        varchar(16)       NOT NULL,
  `net`        integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `salaire` (`sexe`, `csp`, `net`) VALUES
  ('F', 'ouvrier', 1000),
  ('F', 'ouvrier', 1200),
  ('F', 'ouvrier', 1300),
  ('F', 'cadre',   2500),
  ('H', 'ouvrier', 1000),
  ('H', 'ouvrier', 1000),
  ('H', 'ouvrier', 1200),
  ('H', 'cadre',   2400),
  ('H', 'cadre',   2500),
  ('H', 'cadre',   2600),
  ('H', 'cadre',   3000)
--------------
 
--------------
select * from `salaire`
--------------
 
+-----------+------+---------+------+
| tirage_id | sexe | csp     | net  |
+-----------+------+---------+------+
|         1 | F    | ouvrier | 1000 |
|         2 | F    | ouvrier | 1200 |
|         3 | F    | ouvrier | 1300 |
|         4 | F    | cadre   | 2500 |
|         5 | H    | ouvrier | 1000 |
|         6 | H    | ouvrier | 1000 |
|         7 | H    | ouvrier | 1200 |
|         8 | H    | cadre   | 2400 |
|         9 | H    | cadre   | 2500 |
|        10 | H    | cadre   | 2600 |
|        11 | H    | cadre   | 3000 |
+-----------+------+---------+------+
--------------
commit
--------------
 
--------------
select    sexe,
          csp,
          net
 
  from    (  SELECT    *,
                       row_number() OVER w as rang
                 from  `salaire`
               WINDOW  w AS (partition by sexe, csp order by net)
             order by  sexe, csp
          ) as t1
 
   where  rang = (  select  cast(floor(avg(rang)) as unsigned)
                      from  (  SELECT    *,
                                         row_number() OVER w as rang
                                   from  `salaire`
                                 WINDOW  w AS (partition by sexe, csp order by net)
                               order by  sexe, csp
                            ) as t2
                     where  t2.sexe = t1.sexe
                       and  t2.csp  = t1.csp
                  group by  sexe, csp
                 )
 
order by  sexe, net
--------------
 
+------+---------+------+
| sexe | csp     | net  |
+------+---------+------+
| F    | ouvrier | 1200 |
| F    | cadre   | 2500 |
| H    | ouvrier | 1000 |
| H    | cadre   | 2500 |
+------+---------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Pour calculer la médiane, il faut le faire en trois temps :

1) ordonner les salaires du plus petit au plus grand, en les numérotant.
Bien sûr, il faut le faire selon le critère que vous avez choisi, par exemple sur sexe et sur csp.

2) la numérotation se fait de 1 à N. Il suffit de calculer la valeur moyenne de ce N.
Par exemple sur N=5, sa moyenne est 3.
Ou encore sur N=10, sa moyenne est 5.5.
Dans tous les cas, prendre la partie entière, ce qui donne pour 5.5 ==> 5.

3) afficher, selon les critères, le numéro de la ligne qui correspond à votre moyenne.

Il y a peut être moyen de simplifier cette requête !

@+

**percevalseb** · 31/10/2018, 13h02

Bonjour Artemus24, excusez-moi de ne vous répondre que maintenant, je n'avais pas vu votre réponse.

J'ai testé votre solution qui me semble très intéressante, malheureusement pour exécuter des fonctions telles que row_number(), il me faudrait au moins la version 8.0 et je n'ai que la version 5.7.14 de MySQL.

**Artemus24** · 31/10/2018, 20h26

Salut percevalseb.

Avez-vous une solution ou êtes-vous toujours bloqué par ce calcul de la médiane ?

J'ai trouvé une solution assez bizarre qui devrait normalement fonctionner :
--> https://code.i-harness.com/fr/q/13b390

Voici l'exemple qui est donné :

Code :

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

1
2
3
SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2

@+

**SQLpro** · 01/11/2018, 10h17

Envoyé par Artemus24

Code :

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

1
2
3
SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2

C'est celle de Molinaro (mais la votre est incomplète), que j'ai donné dans mon article p13...
https://sqlpro.developpez.com/optimisation/mediane/
Elle est en fait inspirée des travaux de Abromovich, Alexandrova et Birger (1993-1994) et remaniée par Celko.

A +

**percevalseb** · 01/11/2018, 15h03

Bonjour Artemus,

Oui je suis toujours bloqué sur cette requête, quoiqu'on m'a dit de la mettre de côté pour le moment.
La requête de Molinaro fonctionne bien, mais j'ai la médiane sur l'ensemble des salaires. Je ne sais comment l'adapter pour avoir un ensemble de résultats par sexe et csp.

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT AVG(net)
FROM (SELECT S.net
FROM salaires S
CROSS JOIN salaires D
GROUP BY S.net
HAVING SUM(CASE
WHEN S.net = D.net THEN 1
ELSE 0
END) >= ABS(SUM(SIGN(S.net - D.net)))
) AS T

**Artemus24** · 01/11/2018, 16h18

Salut percevalseb.

J'ai repris mon premier exemple que j'ai simplifié mais en gardant le "common table expression".
J'ai décomposé la requête en deux parties, afin de vous montrer le résultat de la sous-requête.

Puis ensuite, j'ai repris mon premier exemple, sans utiliser cette fois-ci le "common table expression".
Et comme précédemment, j'ai décomposé la requête en deux partie, afin de comparer les résultats.

Dans la dernière requête, j'ai repris la solution que j'avais trouvé sur le net, à savoir la solution nommé "Molinaro".
Je ne l'avais pas testé au préalable, et il est vrai quelle ne fonctionnait pas.

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
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `salaire`
--------------
 
--------------
CREATE TABLE `salaire`
( `tirage_id`  integer unsigned  NOT NULL auto_increment primary key,
  `societe`       char(01)       NOT NULL,
  `sexe`          char(01)       NOT NULL,
  `csp`        varchar(16)       NOT NULL,
  `net`        integer           NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `salaire` (`societe`,`sexe`, `csp`, `net`) VALUES
  ('A', 'F', 'ouvrier', 1000),
  ('A', 'F', 'ouvrier', 1200),
  ('A', 'F', 'ouvrier', 1300),
  ('A', 'F', 'cadre',   2500),
  ('A', 'H', 'ouvrier', 1000),
  ('A', 'H', 'ouvrier', 1000),
  ('A', 'H', 'ouvrier', 1200),
  ('A', 'H', 'cadre',   2400),
  ('A', 'H', 'cadre',   2500),
  ('A', 'H', 'cadre',   2600),
  ('A', 'H', 'cadre',   3000),
  ('B', 'F', 'cadre',   2600),
  ('B', 'H', 'cadre',   2400),
  ('B', 'H', 'cadre',   2500),
  ('B', 'H', 'cadre',   2800)
--------------
 
--------------
select * from `salaire`
--------------
 
+-----------+---------+------+---------+------+
| tirage_id | societe | sexe | csp     | net  |
+-----------+---------+------+---------+------+
|         1 | A       | F    | ouvrier | 1000 |
|         2 | A       | F    | ouvrier | 1200 |
|         3 | A       | F    | ouvrier | 1300 |
|         4 | A       | F    | cadre   | 2500 |
|         5 | A       | H    | ouvrier | 1000 |
|         6 | A       | H    | ouvrier | 1000 |
|         7 | A       | H    | ouvrier | 1200 |
|         8 | A       | H    | cadre   | 2400 |
|         9 | A       | H    | cadre   | 2500 |
|        10 | A       | H    | cadre   | 2600 |
|        11 | A       | H    | cadre   | 3000 |
|        12 | B       | F    | cadre   | 2600 |
|        13 | B       | H    | cadre   | 2400 |
|        14 | B       | H    | cadre   | 2500 |
|        15 | B       | H    | cadre   | 2800 |
+-----------+---------+------+---------+------+
--------------
commit
--------------
 
--------------
SELECT     societe,
           sexe,
           csp,
           net,
           row_number() OVER (partition by societe, sexe, csp order by net) as rang,
           count(*)     OVER (partition by societe, sexe, csp)              as nbre
     from  `salaire`
 order by  societe, sexe, csp, net
--------------
 
+---------+------+---------+------+------+------+
| societe | sexe | csp     | net  | rang | nbre |
+---------+------+---------+------+------+------+
| A       | F    | cadre   | 2500 |    1 |    1 |
| A       | F    | ouvrier | 1000 |    1 |    3 |
| A       | F    | ouvrier | 1200 |    2 |    3 |
| A       | F    | ouvrier | 1300 |    3 |    3 |
| A       | H    | cadre   | 2400 |    1 |    4 |
| A       | H    | cadre   | 2500 |    2 |    4 |
| A       | H    | cadre   | 2600 |    3 |    4 |
| A       | H    | cadre   | 3000 |    4 |    4 |
| A       | H    | ouvrier | 1000 |    1 |    3 |
| A       | H    | ouvrier | 1000 |    2 |    3 |
| A       | H    | ouvrier | 1200 |    3 |    3 |
| B       | F    | cadre   | 2600 |    1 |    1 |
| B       | H    | cadre   | 2400 |    1 |    3 |
| B       | H    | cadre   | 2500 |    2 |    3 |
| B       | H    | cadre   | 2800 |    3 |    3 |
+---------+------+---------+------+------+------+
--------------
with cte as (  SELECT  societe,
                       sexe,
                       csp,
                       net,
                       row_number() OVER (partition by societe, sexe, csp order by net) as rang,
                       count(*)     OVER (partition by societe, sexe, csp)              as nbre
                 from  `salaire`
            )
 
   select  societe,sexe, csp, net
     from  cte
    where  rang = floor((nbre+1)/2)
 order by  societe, sexe, csp, net
--------------
 
+---------+------+---------+------+
| societe | sexe | csp     | net  |
+---------+------+---------+------+
| A       | F    | cadre   | 2500 |
| A       | F    | ouvrier | 1200 |
| A       | H    | cadre   | 2500 |
| A       | H    | ouvrier | 1000 |
| B       | F    | cadre   | 2600 |
| B       | H    | cadre   | 2500 |
+---------+------+---------+------+
--------------
SELECT           t1.tirage_id,
                 t1.societe,
                 t1.sexe,
                 t1.csp,
                 t1.net,
                 count(distinct t2.tirage_id)+1 as rang,
                 count(distinct t3.tirage_id)   as nbre
           from  `salaire` as t1
 
left outer join  `salaire` as t2
             on  t2.societe   = t1.societe
            and  t2.sexe      = t1.sexe
            and  t2.csp       = t1.csp
            and  t2.tirage_id < t1.tirage_id
 
     inner join  `salaire` as t3
             on  t3.societe   = t1.societe
            and  t3.sexe      = t1.sexe
            and  t3.csp       = t1.csp
 
       group by  t1.tirage_id, t1.societe, t1.sexe, t1.csp, t1.net
--------------
 
+-----------+---------+------+---------+------+------+------+
| tirage_id | societe | sexe | csp     | net  | rang | nbre |
+-----------+---------+------+---------+------+------+------+
|         1 | A       | F    | ouvrier | 1000 |    1 |    3 |
|         2 | A       | F    | ouvrier | 1200 |    2 |    3 |
|         3 | A       | F    | ouvrier | 1300 |    3 |    3 |
|         4 | A       | F    | cadre   | 2500 |    1 |    1 |
|         5 | A       | H    | ouvrier | 1000 |    1 |    3 |
|         6 | A       | H    | ouvrier | 1000 |    2 |    3 |
|         7 | A       | H    | ouvrier | 1200 |    3 |    3 |
|         8 | A       | H    | cadre   | 2400 |    1 |    4 |
|         9 | A       | H    | cadre   | 2500 |    2 |    4 |
|        10 | A       | H    | cadre   | 2600 |    3 |    4 |
|        11 | A       | H    | cadre   | 3000 |    4 |    4 |
|        12 | B       | F    | cadre   | 2600 |    1 |    1 |
|        13 | B       | H    | cadre   | 2400 |    1 |    3 |
|        14 | B       | H    | cadre   | 2500 |    2 |    3 |
|        15 | B       | H    | cadre   | 2800 |    3 |    3 |
+-----------+---------+------+---------+------+------+------+
--------------
select  societe,
        sexe,
        csp,
        net
 
  from  (    SELECT        t1.tirage_id,
                           t1.societe,
                           t1.sexe,
                           t1.csp,
                           t1.net,
                           count(distinct t2.tirage_id)+1 as rang,
                           count(distinct t3.tirage_id)   as nbre
                     from  `salaire` as t1
 
          left outer join  `salaire` as t2
                       on  t2.societe   = t1.societe
                      and  t2.sexe      = t1.sexe
                      and  t2.csp       = t1.csp
                      and  t2.tirage_id < t1.tirage_id
 
               inner join  `salaire` as t3
                       on  t3.societe   = t1.societe
                      and  t3.sexe      = t1.sexe
                      and  t3.csp       = t1.csp
 
                 group by  t1.tirage_id, t1.societe, t1.sexe, t1.csp, t1.net
        )  as x
 
 where  rang = floor((nbre+1)/2)
 order by  societe, sexe, csp, net
--------------
 
+---------+------+---------+------+
| societe | sexe | csp     | net  |
+---------+------+---------+------+
| A       | F    | cadre   | 2500 |
| A       | F    | ouvrier | 1200 |
| A       | H    | cadre   | 2500 |
| A       | H    | ouvrier | 1000 |
| B       | F    | cadre   | 2600 |
| B       | H    | cadre   | 2500 |
+---------+------+---------+------+
--------------
select distinct  t1.societe,
                 t1.sexe,
                 t1.csp,
                 t1.net
 
           from  `salaire` as t1
     inner join  `salaire` as t2
             on  t2.societe = t1.societe
            and  t2.sexe    = t1.sexe
            and  t2.csp     = t1.csp
       group by  t1.tirage_id
         having  sum(sign(1-sign(t2.net - t1.net))) = floor((count(*)+1)/2)
       order by  t1.societe, t1.sexe, t1.csp, t1.net
--------------
 
+---------+------+---------+------+
| societe | sexe | csp     | net  |
+---------+------+---------+------+
| A       | F    | cadre   | 2500 |
| A       | F    | ouvrier | 1200 |
| A       | H    | cadre   | 2500 |
| A       | H    | ouvrier | 1000 |
| B       | F    | cadre   | 2600 |
| B       | H    | cadre   | 2500 |
+---------+------+---------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

J'espère que cela vous convient cette fois-ci !

@+

**percevalseb** · 02/11/2018, 19h53

Bonjour Artemus,

Je suis très impressionné que vous ayez pu trouver la solution.

J'ai bien suivi votre démonstration et je pense avoir compris. J'ai testé votre dernière requête, impeccable, je n'aurai pas besoin de changer de version de MySQL.

Merci infiniment pour le temps que vous aurez consacré à mon problème. Je marque le sujet comme résolu.

**Artemus24** · 03/11/2018, 11h28

Salut percevalseb.

Je vous conseille de passer à la version mysql 8.0.13 afin d'utiliser les CTE et les fonctions de fénêtrage.
Et de ce fait, la bonne solution est la première requête.

L'astuce pour calculer la valeur médiane, est en premier lieu de trier vos lignes sur votre critère.
Ici, c'est sur société, sexe et csp.
Puis ensuite de faire une numérotation allant de 1 à N.

Dans un deuxième temps, déterminer le nombre de lignes selon vos critères.
Deux cas peuvent se présenter :
a) soit le nombre de lignes est impair.
Le calcul de "(nbre+1)/2" donne un entier qui va représenter le milieu de votre liste.
C'est exactement la valeur médiane !
Exemple :

Code :

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

1
2
3
4
5
6
7
+---------+------+---------+------+------+------+
| societe | sexe | csp     | net  | rang | nbre |
+---------+------+---------+------+------+------+
| B       | H    | cadre   | 2400 |    1 |    3 |
| B       | H    | cadre   | 2500 |    2 |    3 |
| B       | H    | cadre   | 2800 |    3 |    3 |
+---------+------+---------+------+------+------+

Autrement dit, votre résultat sera 2500.

b) soit le nombre de lignes est pair.
Le calcul de "(nbre+1)/2" ne va pas vous donnez un entier.
Le choix st alors de prendre la partie entière en faisant "floor((nbre+1)/2)".
Et comme la valeur du milieu n'existe pas, puisque nous avons un nombre pair de ligne, il faut faire un choix.

Code :

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

1
2
3
4
5
6
7
8
+---------+------+---------+------+------+------+
| societe | sexe | csp     | net  | rang | nbre |
+---------+------+---------+------+------+------+
| A       | H    | cadre   | 2400 |    1 |    4 |
| A       | H    | cadre   | 2500 |    2 |    4 |
| A       | H    | cadre   | 2600 |    3 |    4 |
| A       | H    | cadre   | 3000 |    4 |    4 |
+---------+------+---------+------+------+------+

Soit prendre celle juste inférieure au milieu, c'est-à-dire "2500",
soit prendre celle juste supérieure au milieu, c'est-à-dire "2600".

J'ai constaté qu'il existe une erreur dans le tri.
Au lieu de mettre ceci "and t2.tirage_id < t1.tirage_id", vous devez plutôt mettre cela "and concat(t2.net,t2.tirage_id) < concat(t1.net,t1.tirage_id)".
Pourquoi ? A cause des doublons sur le montant net. Il faut que chaque montant soit unique.

La dernière solution ne fonctionne pas correctement s'il existe des doublons.

A vous de faire des tests plus poussés afin de vérifier tous les cas possibles du calcul de la médiane.

@+

**percevalseb** · 07/11/2018, 17h51

Bonjour Artemus,

Malheureusement, il n'a pas été possible de changer de version MySQL ce qui m'aurait grandement aidé.
J'ai poussé plus loin les tests. Apparemment, quand on a un nombre pairs de lignes, il faut faire la moyenne des 2 valeurs du milieu. C'est ce que l'on ma demandé de faire et c'est ce que je retrouve dans les exemples de calculs de médianes.

J'ai donc repris votre requête que j'ai modifié :

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
  select t4.societe,
         t4.sexe,
         t4.csp,
         ROUND(AVG(t4.net),2) as median from (
  select  societe,
          sexe,
          csp,
          net
    from  (    SELECT        t1.id,
                             t1.societe,
                             t1.sexe,
                             t1.csp,
                             t1.net,
                             count(distinct t2.id)+1 as rang,
                             count(distinct t3.id)   as nbre
                       from  salaires as t1
 
            left outer join  salaires as t2
                         on  t2.societe   = t1.societe
                        and  t2.sexe      = t1.sexe
                        and  t2.csp       = t1.csp
                        and  t2.id < t1.id
 
                 inner join  salaires as t3
                         on  t3.societe   = t1.societe
                        and  t3.sexe      = t1.sexe
                        and  t3.csp       = t1.csp
                   group by  t1.id, t1.societe, t1.sexe, t1.csp, t1.net
          )  as x
   where  rang >= nbre/2 and rang <= (nbre/2) + 1
   order by  societe, sexe, csp, net) as t4
         group by t4.societe,
                   t4.sexe,
                   t4.csp

Malheureusement, la requête met plus d'une minute à s'exécuter sur une table qui comporte 4000 lignes environ même avec des champs indexés.
De plus, si j'isole une partie de la requête pour tester la numérotation, je constate des erreurs, ce que je n'arrive pas à comprendre pourquoi.
Par conséquent, des résultats sont partiellement faux.

Sinon, j'ai aussi adapté cette requête :

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
       select t3.societe,
              t3.sexe,
              t3.csp,
              AVG(t3.net) as median from (
       select distinct  sum(sign(1-sign(t2.net - t1.net))) as rang,
                        t1.societe,
                        t1.sexe,
                        t1.csp,
                        t1.net
                  from  salaires as t1
            inner join  salaires as t2
                    on  t2.societe = t1.societe
                   and  t2.sexe    = t1.sexe
                   and  t2.csp     = t1.csp
              group by  t1.id
                having  rang >= count(*)/2 and rang <= count(*)/2 + 1
              order by  t1.societe, t1.sexe, t1.csp, t1.net) as t3
              group by t3.societe,
                        t3.sexe,
                        t3.csp

Là par contre, la requête s'exécute sans délai, mais il y a un problème de numérotation sur les doublons. Sinon, c'est conforme pour les lignes impairs et pairs sans doublons.
Si je classe la grosse table de 4000 lignes selon les critères societe, sexe et csp, il n'y a jamais de doublons sur net, alors ça passe mais un jour il pourrait y en avoir, alors c'est pas encore top à 100% lol

**Artemus24** · 08/11/2018, 01h29

Salut percevalseb.

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
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS base
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS base
        DEFAULT CHARACTER SET latin1
        DEFAULT COLLATE       latin1_general_ci
--------------
 
--------------
DROP TABLE IF EXISTS `salaire`
--------------
 
--------------
CREATE TABLE `salaire`
( id         integer unsigned  NOT NULL auto_increment primary key,
  sexe          char(01)       NOT NULL,
  categorie  varchar(16)       NOT NULL,
  net        decimal(15,2)     NOT NULL,
  index `idx` (`sexe`,`categorie`)
) ENGINE=InnoDB
  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `salaire` (`sexe`,`categorie`,`net`) VALUES
  ('F', 'ouvrier',    1300.0),
  ('H', 'ouvrier',    1000.0),
  ('Z', 'cadre',      1000.0),
  ('F', 'ouvrier',    1200.0),
  ('H', 'ouvrier',    1200.0),
  ('H', 'cadre',      1000.0),
  ('Z', 'cadre',      1000.0),
  ('F', 'ouvrier',    1000.0),
  ('H', 'cadre',      2600.0),
  ('H', 'cadre',      2400.0),
  ('Z', 'cadre',      1000.0),
  ('F', 'cadre',      2500.0),
  ('H', 'ouvrier',    1000.0),
  ('H', 'cadre',      3000.0),
  ('Z', 'cadre',      1000.0)
--------------
 
--------------
select    *
    from  `salaire`
order by  sexe, categorie, net
--------------
 
+----+------+-----------+---------+
| id | sexe | categorie | net     |
+----+------+-----------+---------+
| 12 | F    | cadre     | 2500.00 |
|  8 | F    | ouvrier   | 1000.00 |
|  4 | F    | ouvrier   | 1200.00 |
|  1 | F    | ouvrier   | 1300.00 |
|  6 | H    | cadre     | 1000.00 |
| 10 | H    | cadre     | 2400.00 |
|  9 | H    | cadre     | 2600.00 |
| 14 | H    | cadre     | 3000.00 |
|  2 | H    | ouvrier   | 1000.00 |
| 13 | H    | ouvrier   | 1000.00 |
|  5 | H    | ouvrier   | 1200.00 |
|  3 | Z    | cadre     | 1000.00 |
|  7 | Z    | cadre     | 1000.00 |
| 11 | Z    | cadre     | 1000.00 |
| 15 | Z    | cadre     | 1000.00 |
+----+------+-----------+---------+
--------------
commit
--------------
 
--------------
drop view if exists `vue_1`
--------------
 
--------------
create view `vue_1` as
     SELECT  sexe,
             categorie,
             net,
             row_number() OVER (partition by sexe, categorie order by net) as rang,
             count(*)     OVER (partition by sexe, categorie)              as nbre
       from  `salaire`
   order by  sexe, categorie, net, rang
--------------
 
--------------
select * from `vue_1`
--------------
 
+------+-----------+---------+------+------+
| sexe | categorie | net     | rang | nbre |
+------+-----------+---------+------+------+
| F    | cadre     | 2500.00 |    1 |    1 |
| F    | ouvrier   | 1000.00 |    1 |    3 |
| F    | ouvrier   | 1200.00 |    2 |    3 |
| F    | ouvrier   | 1300.00 |    3 |    3 |
| H    | cadre     | 1000.00 |    1 |    4 |
| H    | cadre     | 2400.00 |    2 |    4 |
| H    | cadre     | 2600.00 |    3 |    4 |
| H    | cadre     | 3000.00 |    4 |    4 |
| H    | ouvrier   | 1000.00 |    1 |    3 |
| H    | ouvrier   | 1000.00 |    2 |    3 |
| H    | ouvrier   | 1200.00 |    3 |    3 |
| Z    | cadre     | 1000.00 |    1 |    4 |
| Z    | cadre     | 1000.00 |    2 |    4 |
| Z    | cadre     | 1000.00 |    3 |    4 |
| Z    | cadre     | 1000.00 |    4 |    4 |
+------+-----------+---------+------+------+
--------------
select    sexe,
          categorie,
          cast(avg(net) as decimal(15,2)) as net
    from  `vue_1`
   where  rang between case mod(nbre,2) when 1 then (nbre+1)/2 else nbre/2   end
                   and case mod(nbre,2) when 1 then (nbre+1)/2 else nbre/2+1 end
group by  sexe, categorie
order by  sexe, categorie
--------------
 
+------+-----------+---------+
| sexe | categorie | net     |
+------+-----------+---------+
| F    | cadre     | 2500.00 |
| F    | ouvrier   | 1200.00 |
| H    | cadre     | 2500.00 |
| H    | ouvrier   | 1000.00 |
| Z    | cadre     | 1000.00 |
+------+-----------+---------+
--------------
drop view if exists `vue_2`
--------------
 
--------------
create view `vue_2` as
           select  t1.sexe,
                   t1.categorie,
                   t1.net,
                   count(distinct t2.id)+1 as rang,
                   count(distinct t3.id)   as nbre
 
             from  `salaire` as t1
 
  left outer join  `salaire` as t2
               on  t2.sexe               = t1.sexe
              and  t2.categorie          = t1.categorie
              and  concat(t2.net,t2.id)  < concat(t1.net,t1.id)
 
       inner join  `salaire` as t3
               on  t3.sexe               = t1.sexe
              and  t3.categorie          = t1.categorie
 
         group by  t1.id
         order by  t1.sexe, t1.categorie,t1.net, rang
--------------
 
--------------
select * from `vue_2`
--------------
 
+------+-----------+---------+------+------+
| sexe | categorie | net     | rang | nbre |
+------+-----------+---------+------+------+
| F    | cadre     | 2500.00 |    1 |    1 |
| F    | ouvrier   | 1000.00 |    1 |    3 |
| F    | ouvrier   | 1200.00 |    2 |    3 |
| F    | ouvrier   | 1300.00 |    3 |    3 |
| H    | cadre     | 1000.00 |    1 |    4 |
| H    | cadre     | 2400.00 |    2 |    4 |
| H    | cadre     | 2600.00 |    3 |    4 |
| H    | cadre     | 3000.00 |    4 |    4 |
| H    | ouvrier   | 1000.00 |    1 |    3 |
| H    | ouvrier   | 1000.00 |    2 |    3 |
| H    | ouvrier   | 1200.00 |    3 |    3 |
| Z    | cadre     | 1000.00 |    1 |    4 |
| Z    | cadre     | 1000.00 |    2 |    4 |
| Z    | cadre     | 1000.00 |    3 |    4 |
| Z    | cadre     | 1000.00 |    4 |    4 |
+------+-----------+---------+------+------+
--------------
select    sexe,
          categorie,
          cast(avg(net) as decimal(15,2)) as net
    from  `vue_2`
   where  rang between case mod(nbre,2) when 1 then (nbre+1)/2 else nbre/2   end
                   and case mod(nbre,2) when 1 then (nbre+1)/2 else nbre/2+1 end
group by  sexe, categorie
order by  sexe, categorie
--------------
 
+------+-----------+---------+
| sexe | categorie | net     |
+------+-----------+---------+
| F    | cadre     | 2500.00 |
| F    | ouvrier   | 1200.00 |
| H    | cadre     | 2500.00 |
| H    | ouvrier   | 1000.00 |
| Z    | cadre     | 1000.00 |
+------+-----------+---------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

**percevalseb** · 09/11/2018, 17h25

Merci pour votre dernier message Artemus,

Je considère le sujet comme totalement résolu, je vais pourvoir passer à d'autres défis

Salaire médian classé par sexe et CSP

Requêtes MySQL

Discussions similaires

Partager

Partager