Top 20 sur Group By

**Dracouille** · 17/05/2016, 10h46

Salut salut,

Voilà un jour ou deux maintenant que je me prend la tête sur une requête. Je ne pense pas être loin de la solution, mais c'est pas encore ça... =)

J'ai une table que l'on va appeler Table1, dans celle ci j'ai une vingtaines de champs, je n'en utilise que de 3 ici (IdTable1, IdTable2, Date)
Je ferais ma jointure pour le nom des champs de la Table2 ne vous embêtez pas.

Grosso modo Table1 contient des problèmes, chaque ligne représente un problème, Table2 représente la provenance de ce problème.
J'ai donc besoin d'afficher le top 20 des provenances avec le plus de problèmes par mois. (top 20 des Count(IdTable1) group by mois, et IdTable2)

Cela devrai me ressortir quelques chose comme ça

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
 
Mois ----- IdTable2 ----- Count
1                 2               15
1                 4               20
1                 8               20
1                 18             30
1                 52             32
1                 60             15
1                 44             18
1                 99             50
....
2                 2              50
2                 50             20
2                 99             15
....

Merci de votre aide

**al1_24** · 17/05/2016, 11h27

Quelle(s) requête(s) as-tu déjà essayée(s) ?
Quel problème rencontres-tu avec ? (erreur, résultat non conforme aux attentes)

**Dracouille** · 17/05/2016, 11h36

Ou je fait le group by conforme mais je n'arrive pas a faire la limitation au top 20.
Ou je fait le top 20 mais sans group by, donc il ne va me ressortir que 20 lignes...

Le fait est que la requête est vraiment basique sans le top 20, mais avec, il faut une (voir 2) sous requête que je n'arrive pas a gérer correctement...

**Artemus24** · 18/05/2016, 09h26

Salut Dracouille.

Par TOP 20, j'ai compris que tu désirais récupérer les 20 plus grands count(*) par mois.

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
 
--------------
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 `table1`
--------------
 
--------------
CREATE TABLE `table1`
(
  `idTable1`    integer unsigned NOT NULL auto_increment primary key,
  `idTable2`    integer unsigned NOT NULL,
  `date`        date             NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `table1` (`idTable2`,`date`) values
  ( 2,'2016-01-01'),( 2,'2016-01-02'),( 2,'2016-01-03'),( 2,'2016-01-04'),( 2,'2016-01-05'),( 2,'2016-01-06'),( 2,'2016-01-07'),( 2,'2016-01-08'),
  ( 2,'2016-01-09'),( 2,'2016-01-10'),( 2,'2016-01-11'),( 2,'2016-01-12'),( 2,'2016-01-13'),( 2,'2016-01-14'),( 2,'2016-01-15'),
  ( 4,'2016-01-01'),( 4,'2016-01-02'),( 4,'2016-01-03'),( 4,'2016-01-04'),( 4,'2016-01-05'),( 4,'2016-01-06'),( 4,'2016-01-07'),( 4,'2016-01-08'),
  ( 4,'2016-01-09'),( 4,'2016-01-10'),( 4,'2016-01-11'),( 4,'2016-01-12'),( 4,'2016-01-13'),( 4,'2016-01-14'),( 4,'2016-01-15'),( 4,'2016-01-16'),
  ( 4,'2016-01-17'),( 4,'2016-01-18'),( 4,'2016-01-19'),( 4,'2016-01-20'),
  ( 2,'2016-02-01'),( 2,'2016-02-01'),( 2,'2016-02-02'),( 2,'2016-02-02'),( 2,'2016-02-03'),( 2,'2016-02-03'),( 2,'2016-02-04'),( 2,'2016-02-04'),
  ( 2,'2016-02-05'),( 2,'2016-02-05'),( 2,'2016-02-06'),( 2,'2016-02-06'),( 2,'2016-02-07'),( 2,'2016-02-07'),( 2,'2016-02-08'),( 2,'2016-02-08'),
  ( 2,'2016-02-09'),( 2,'2016-02-09'),( 2,'2016-02-10'),( 2,'2016-02-10'),( 2,'2016-02-11'),( 2,'2016-02-11'),( 2,'2016-02-12'),( 2,'2016-02-12'),
  ( 2,'2016-02-13'),( 2,'2016-02-13'),( 2,'2016-02-14'),( 2,'2016-02-14'),( 2,'2016-02-15'),( 2,'2016-02-15'),( 2,'2016-02-16'),( 2,'2016-02-16'),
  ( 2,'2016-02-17'),( 2,'2016-02-17'),( 2,'2016-02-18'),( 2,'2016-02-18'),( 2,'2016-02-19'),( 2,'2016-02-19'),( 2,'2016-02-20'),( 2,'2016-02-20'),
  ( 2,'2016-02-21'),( 2,'2016-02-21'),( 2,'2016-02-22'),( 2,'2016-02-22'),( 2,'2016-02-23'),( 2,'2016-02-23'),( 2,'2016-02-24'),( 2,'2016-02-24'),
  ( 2,'2016-02-25'),( 2,'2016-02-25'),
  (99,'2016-02-01'),(99,'2016-02-02'),(99,'2016-02-03'),(99,'2016-02-04'),(99,'2016-02-05'),(99,'2016-02-06'),(99,'2016-02-07'),(99,'2016-02-08'),
  (99,'2016-02-09'),(99,'2016-02-10'),(99,'2016-02-11'),(99,'2016-02-12'),(99,'2016-02-13'),(99,'2016-02-14'),(99,'2016-02-15')
--------------
 
--------------
select * from table1
--------------
 
+----------+----------+------------+
| idTable1 | idTable2 | date       |
+----------+----------+------------+
|        1 |        2 | 2016-01-01 |
|        2 |        2 | 2016-01-02 |
|        3 |        2 | 2016-01-03 |
|        4 |        2 | 2016-01-04 |
|        5 |        2 | 2016-01-05 |
|        6 |        2 | 2016-01-06 |
|        7 |        2 | 2016-01-07 |
|        8 |        2 | 2016-01-08 |
|        9 |        2 | 2016-01-09 |
|       10 |        2 | 2016-01-10 |
|       11 |        2 | 2016-01-11 |
|       12 |        2 | 2016-01-12 |
|       13 |        2 | 2016-01-13 |
|       14 |        2 | 2016-01-14 |
|       15 |        2 | 2016-01-15 |
|       16 |        4 | 2016-01-01 |
|       17 |        4 | 2016-01-02 |
|       18 |        4 | 2016-01-03 |
|       19 |        4 | 2016-01-04 |
|       20 |        4 | 2016-01-05 |
|       21 |        4 | 2016-01-06 |
|       22 |        4 | 2016-01-07 |
|       23 |        4 | 2016-01-08 |
|       24 |        4 | 2016-01-09 |
|       25 |        4 | 2016-01-10 |
|       26 |        4 | 2016-01-11 |
|       27 |        4 | 2016-01-12 |
|       28 |        4 | 2016-01-13 |
|       29 |        4 | 2016-01-14 |
|       30 |        4 | 2016-01-15 |
|       31 |        4 | 2016-01-16 |
|       32 |        4 | 2016-01-17 |
|       33 |        4 | 2016-01-18 |
|       34 |        4 | 2016-01-19 |
|       35 |        4 | 2016-01-20 |
|       36 |        2 | 2016-02-01 |
|       37 |        2 | 2016-02-01 |
|       38 |        2 | 2016-02-02 |
|       39 |        2 | 2016-02-02 |
|       40 |        2 | 2016-02-03 |
|       41 |        2 | 2016-02-03 |
|       42 |        2 | 2016-02-04 |
|       43 |        2 | 2016-02-04 |
|       44 |        2 | 2016-02-05 |
|       45 |        2 | 2016-02-05 |
|       46 |        2 | 2016-02-06 |
|       47 |        2 | 2016-02-06 |
|       48 |        2 | 2016-02-07 |
|       49 |        2 | 2016-02-07 |
|       50 |        2 | 2016-02-08 |
|       51 |        2 | 2016-02-08 |
|       52 |        2 | 2016-02-09 |
|       53 |        2 | 2016-02-09 |
|       54 |        2 | 2016-02-10 |
|       55 |        2 | 2016-02-10 |
|       56 |        2 | 2016-02-11 |
|       57 |        2 | 2016-02-11 |
|       58 |        2 | 2016-02-12 |
|       59 |        2 | 2016-02-12 |
|       60 |        2 | 2016-02-13 |
|       61 |        2 | 2016-02-13 |
|       62 |        2 | 2016-02-14 |
|       63 |        2 | 2016-02-14 |
|       64 |        2 | 2016-02-15 |
|       65 |        2 | 2016-02-15 |
|       66 |        2 | 2016-02-16 |
|       67 |        2 | 2016-02-16 |
|       68 |        2 | 2016-02-17 |
|       69 |        2 | 2016-02-17 |
|       70 |        2 | 2016-02-18 |
|       71 |        2 | 2016-02-18 |
|       72 |        2 | 2016-02-19 |
|       73 |        2 | 2016-02-19 |
|       74 |        2 | 2016-02-20 |
|       75 |        2 | 2016-02-20 |
|       76 |        2 | 2016-02-21 |
|       77 |        2 | 2016-02-21 |
|       78 |        2 | 2016-02-22 |
|       79 |        2 | 2016-02-22 |
|       80 |        2 | 2016-02-23 |
|       81 |        2 | 2016-02-23 |
|       82 |        2 | 2016-02-24 |
|       83 |        2 | 2016-02-24 |
|       84 |        2 | 2016-02-25 |
|       85 |        2 | 2016-02-25 |
|       86 |       99 | 2016-02-01 |
|       87 |       99 | 2016-02-02 |
|       88 |       99 | 2016-02-03 |
|       89 |       99 | 2016-02-04 |
|       90 |       99 | 2016-02-05 |
|       91 |       99 | 2016-02-06 |
|       92 |       99 | 2016-02-07 |
|       93 |       99 | 2016-02-08 |
|       94 |       99 | 2016-02-09 |
|       95 |       99 | 2016-02-10 |
|       96 |       99 | 2016-02-11 |
|       97 |       99 | 2016-02-12 |
|       98 |       99 | 2016-02-13 |
|       99 |       99 | 2016-02-14 |
|      100 |       99 | 2016-02-15 |
+----------+----------+------------+
--------------
select month(date) as mois, idTable2, count(*) as nbre
from table1
group by mois, idTable2
order by mois, nbre desc
--------------
 
+------+----------+------+
| mois | idTable2 | nbre |
+------+----------+------+
|    1 |        4 |   20 |
|    1 |        2 |   15 |
|    2 |        2 |   50 |
|    2 |       99 |   15 |
+------+----------+------+
--------------
select @i:=if(@prec=mois, @i:=@i+1,1) as rang,
       @prec:=mois as mois,
       idTable2,
       nbre
from (
    select month(date) as mois, idTable2, count(*) as nbre
    from table1
    group by mois, idTable2
    order by mois, nbre desc
) as x,(select @i:=1, @prec:='') as y
--------------
 
+------+------+----------+------+
| rang | mois | idTable2 | nbre |
+------+------+----------+------+
|    1 |    1 |        4 |   20 |
|    2 |    1 |        2 |   15 |
|    1 |    2 |        2 |   50 |
|    2 |    2 |       99 |   15 |
+------+------+----------+------+
--------------
select mois, idTable2, nbre
from (
    select @i:=if(@prec=mois, @i:=@i+1,1) as rang,
           @prec:=mois as mois,
           idTable2,
           nbre
    from (
        select month(date) as mois, idTable2, count(*) as nbre
        from table1
        group by mois, idTable2
        order by mois, nbre desc
    ) as x,(select @i:=1, @prec:='') as y
) as z
where rang <=20
order by mois, rang
--------------
 
+------+----------+------+
| mois | idTable2 | nbre |
+------+----------+------+
|    1 |        4 |   20 |
|    1 |        2 |   15 |
|    2 |        2 |   50 |
|    2 |       99 |   15 |
+------+----------+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

J'ai décomposé cela en trois requêtes afin de voir les étapes intermédiaires.

@+

**Dracouille** · 18/05/2016, 11h20

Salut, merci de ta réponse.
A première vu, la requête fonctionne niquel.

Cependant, aurais tu une solution intermédiaire ou la requête n'utiliserai pas de variables (@i et @prec) ?
Si cela ne te prend pas trop de temps, bien entendu vu que celle ci fonctionne.... =)

Sinon, ce n'est pas grave, et un grand merci à toi.

Dracouille.

**Artemus24** · 18/05/2016, 12h19

Salut Dracouille.

Envoyé par Dracouille

Cependant, aurais tu une solution intermédiaire ou la requête n'utiliserai pas de variables (@i et @prec) ?

Et je fais comment pour obtenir, par mois, les 20 plus gros problèmes ?

Par mois, je suis obligé de trier par ordre décroissant, la colonne nommé "nbre".
Pour récupérer les 20 premières lignes, je suis obligé de les numéroter, en allant du plus grand au plus petit. Est-ce bien ce que tu voulais ?

Je suppose que tu as pensé au "limit".
Cela ne peut pas fonctionner car ce "limit" prend 20 lignes sur le total des lignes à l'extraction (du select) et non par mois.

Si quelqu'un a une autre solution à proposer, je suis preneur.

@+

**Dracouille** · 18/05/2016, 12h44

Effectivement au début j'avais penser a un top ou limit.
Mais dans la sous requête, puisque, comme tu dit, sinon je n'aurais que 20 lignes au totale...

Cela dit, s'il n'y a pas de parade a cela, pas grave. Je voulais juste savoir si tu en avais une. Potentiellement.

(Je vais attendre une journée ou deux avant de passer en résolu, savoir si quelqu'un a une autre idée)

Merci en tous cas

**aieeeuuuuu** · 18/05/2016, 13h20

Bonjour,

Pour une solution sans utiliser les variable (par ailleurs déconseillée par la doc de MySQL), ilet comme MySQL n'implémente pas les fonctions analytiques qui auraient étaient fort utiles dans votre cas, faut faire des auto-jointures, qui risquent fort de vous donner des temps de réponse catastrophiques :

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
 
SELECT b.mois, b.idTable2, b.nbre, COUNT(*) AS Rang
FROM (
	select month(date) as mois, idTable2, count(*) as nbre
	from table1
	group by mois, idTable2
) B
INNER JOIN (
	select month(date) as mois, idTable2, count(*) as nbre
	from table1
	group by mois, idTable2
) P
	ON  P.mois = b.mois
	AND P.nbre <=  b.nbre
GROUP BY b.mois, b.idTable2, b.nbre
HAVING COUNT(*) <= 20
order by b.mois,  COUNT(*) DESC ,b.nbre desc
;

Sur un principe similaire, vous pourriez faire une sous requete incluant le LIMIT, mais là encore, les temps de réponse risquent d'être très mauvais.

Testez sur vos données réelles (quelle est la volumétrie ?) mais à mon avis, la solution proposée par Artemus24 sera de loin la plus rapide...

NB : dans tous les cas, il faudra spécifier les règles dans le cas ou il y a des exæquo...

**Dracouille** · 18/05/2016, 14h03

Salut,

Je viens de jeter un coup d’œil aux fonctions analytiques, et je ne connaissais pas du tous. Je vais m'y intéresser de plus prêt pour ma culture perso.

La requête me parait bonne, et effectivement elle est un plus plus couteuse (le double). Cependant, la charge de donnée devrait être entre 1k et max 2k lignes par ans donc le sgbd devrait pouvoir supporter.
(Petite erreur ligne 14 le chevron n'est pas dans le bon sens.)

Dans tous les cas pour les ex æquo, vu que le chiffre est spécifié en dur dans le requête il ne me ressortira que les 20 premières triées par le order by. Pas moyen d'en ressortir plus.
Ou faut en sortir 25 et faire le trie après au niveau du code de l'application....

En attendant merci a toi aussi =)

**aieeeuuuuu** · 18/05/2016, 14h18

Envoyé par Dracouille

Dans tous les cas pour les ex æquo, vu que le chiffre est spécifié en dur dans le requête il ne me ressortira que les 20 premières triées par le order by. Pas moyen d'en ressortir plus.

plus non, mais moins, oui : s'il y en a 15 idTable2 avec 2 occurrences, et 10 avec 1 occurrence, seuls les 15 ressortiront...

**Dracouille** · 18/05/2016, 14h26

Nom : 2016-05-18_145840.png
Affichages : 105
Taille : 3,8 Ko

Effectivement, je viens de faire le test. (tu m'as devancé de quelques minutes

)
On vois bien que les doublons sont a 3 et si je positionne ma limit a 2 évidement aucune ne s'affiche.

J'vais chercher comment contourner le problème.

**Artemus24** · 18/05/2016, 16h24

Salut à tous.

Envoyé par Dracouille

Cependant, aurais-tu une solution intermédiaire ou la requête n'utiliserai pas de variables (@i et @prec) ?

Qu'est-ce qui ne te plait pas dans cette solution ?

J'ai repris l'exemple de aieeeuuuuu qui fonctionne parfaitement.
Je ne connaissais pas le principe de propagation du count(*) sur deux tables.
Il y a quand même des contraintes dans sa mise en oeuvre, en autre d'avoir des valeurs distinctes pour la colonne nbre.

Envoyé par aieeeuuuuu

Pour une solution sans utiliser les variable (par ailleurs déconseillée par la doc de MySQL)

Je n'ai jamais compris pourquoi mettre une solution à la disposition des utilisateurs, pour ensuite la déconseiller.
A priori, chez moi, cela fonctionne parfaitement.

Envoyé par aieeeuuuuu

MySQL n'implémente pas les fonctions analytiques

Ne serait-ce pas l'autre nom du CTE (Common Table Expression) ?

Envoyé par aieeeuuuuu

Sur un principe similaire, vous pourriez faire une sous-requête incluant le LIMIT, mais là encore, les temps de réponse risquent d'être très mauvais.

Le limit ne fonctionne pas car Dracouille recherche une limite à 20 lignes mais par mois.
Ou alors, je n'ai pas compris l'usage de ce que tu veux faire de ce limit.

Envoyé par Dracouille

(Petite erreur ligne 14 le chevron n'est pas dans le bon sens.)

Oui en effet, car on aura le bas du tableau (les plus petite valeurs).

Envoyé par Dracouille

Dans tous les cas pour les ex æquo, vu que le chiffre est spécifié en dur dans le requête il ne me ressortira que les 20 premières triées par le order by. Pas moyen d'en ressortir plus.

Si tu demandes 20 lignes, il va te sortir tes 20 lignes. Dans le cas des ex æquo, il y a un problème car il va manquer des lignes.

Pour contourner le problème des ex æquo, tu peux demander 30 lignes et traiter à l'affichage que les 20 premières lignes.
Sachant que si tu as des doublons, tu peux ajouter quelques lignes supplémentaires à l'affichage.

@+

**Dracouille** · 18/05/2016, 17h15

Envoyé par Artemus24

Salut à tous.

Qu'est-ce qui ne te plait pas dans cette solution ?

J'ai repris l'exemple de aieeeuuuuu qui fonctionne parfaitement.
Je ne connaissais pas le principe de propagation du count(*) sur deux tables.
Il y a quand même des contraintes dans sa mise en oeuvre, en autre d'avoir des valeurs distinctes pour la colonne nbre.

Tous simplement parce que je n'utilise pas un 'requeteur' MySQL et lors des déclaration de la variable @i il ne va pas reconnaitre l'@.
Le seul moyen était de faire une procédure stocké, et je voulais attendre avant de faire ca, si quelqu'un n'avais pas une autre idée...

**aieeeuuuuu** · 18/05/2016, 17h52

Envoyé par Artemus24

Il y a quand même des contraintes dans sa mise en oeuvre, en autre d'avoir des valeurs distinctes pour la colonne nbre.

Pourquoi ? je ne comprend pas le problème soulevé.

Envoyé par Artemus24

Je n'ai jamais compris pourquoi mettre une solution à la disposition des utilisateurs, pour ensuite la déconseiller.

En fait pour être précis (mais de mémoire quand même, j'ai la flemme d'aller vérifier), il me semble qu'il est uniquement déconseillé de lire et d'affecter une variable dans la même requete.

Envoyé par Artemus24

Ne serait-ce pas l'autre nom du CTE (Common Table Expression) ?

Non, il s'agit par exemple de la fonction DENSE_RANK qui aurait était bien utile ici...

Une CTE (non implémenté non plus sous MySQL) aurait toutefois permis de rendre la requete un peu plus lisible, en factorisant la sous requete présente deux fois... mais cela n'aurait a priori rien changé aux performances

Envoyé par Artemus24

Le limit ne fonctionne pas car Dracouille recherche une limite à 20 lignes mais par mois.
Ou alors, je n'ai pas compris l'usage de ce que tu veux faire de ce limit.

Non en effet, ce n'est pas possible non plus avec MySQL.
L'idée que j'avais en tête nécessiterait un opérateur comme le APPLY de SQL Server. on oublie donc

**Artemus24** · 18/05/2016, 19h02

Salut aieeeuuuuu.

Envoyé par aieeeuuuuu

Pourquoi ? je ne comprend pas le problème soulevé.

J'aurai dû être plus précis. Je parlais des ex æquo.
Si toutes les valeurs (je parle du count(*) dans ton exemple) sont différentes, le rang est bien incrémenté (avec des valeurs 1, 2, 3, ...).
Si nous avons un doublon, nous avons par exemple comme rang 1, 3, 3, 4, ...

C'est juste de cette contrainte dont le parlais.

Envoyé par aieeeuuuuu

L'idée que j'avais en tête nécessiterait un opérateur comme le APPLY de SQL Server. on oublie donc

Je n'ai jamais utilisé cela : APPLY. En ce moment, j'essaye de comprendre les trigger sous SQL Server.

Envoyé par Dracouille

Tous simplement parce que je n'utilise pas un 'requeteur' MySQL et lors des déclaration de la variable @i il ne va pas reconnaître l'@.

Je ne comprends pas bien ce que tu dis. Est-ce que ta requête est destinée à MySql ?

Envoyé par Dracouille

Le seul moyen était de faire une procédure stocké, et je voulais attendre avant de faire ca, si quelqu'un n'avais pas une autre idée...

Si tu as des difficultés à faire ta procédure stockée (pour MySql), je peux m'en charger.
Le principe va être identique à ma solution, sans passer, bien sûr, par les variable @i. Je ferrais un cursor à la place.

@+

**Artemus24** · 19/05/2016, 00h53

Salut à tous.

Voici ce que l'on peut obtenir avec Microsoft SQL Server :

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
-- ===========
-- Paramétrage
-- ===========
 
SET NOCOUNT ON
 
-- ==================
-- Lien vers Database
-- ==================
 
use tempdb
 
Le contexte de la base de données a changé*; il est maintenant 'tempdb'.
 
-- ==========================
-- Suppression Table 'compet'
-- ==========================
 
IF OBJECT_ID(N'dbo.table1', N'U') IS NOT NULL
    DROP TABLE dbo.table1
 
-- =======================
-- création table 'table1'
-- =======================
 
create table dbo.table1 (
  idTable1    integer NOT NULL identity(1, 1) primary key,
  idTable2    integer NOT NULL,
  date        date    NOT NULL
);
 
-- =======================
-- insertion dans 'table1'
-- =======================
 
INSERT INTO dbo.table1 (idTable2,date) values
  ( 2,'2016-01-01'),( 2,'2016-01-02'),( 2,'2016-01-03'),( 2,'2016-01-04'),( 2,'2016-01-05'),( 2,'2016-01-06'),( 2,'2016-01-07'),
  ( 2,'2016-01-08'),( 2,'2016-01-09'),( 2,'2016-01-10'),( 2,'2016-01-11'),( 2,'2016-01-12'),( 2,'2016-01-13'),( 2,'2016-01-14'),
  ( 2,'2016-01-15'),( 2,'2016-01-16'),( 2,'2016-01-17'),( 2,'2016-01-18'),( 2,'2016-01-19'),( 2,'2016-01-20'),
  ( 4,'2016-01-01'),( 4,'2016-01-02'),( 4,'2016-01-03'),( 4,'2016-01-04'),( 4,'2016-01-05'),( 4,'2016-01-06'),( 4,'2016-01-07'),
  ( 4,'2016-01-08'),( 4,'2016-01-09'),( 4,'2016-01-10'),( 4,'2016-01-11'),( 4,'2016-01-12'),( 4,'2016-01-13'),( 4,'2016-01-14'),
  ( 4,'2016-01-15'),( 4,'2016-01-16'),( 4,'2016-01-17'),( 4,'2016-01-18'),( 4,'2016-01-19'),( 4,'2016-01-20'),
  ( 5,'2016-01-01'),( 5,'2016-01-01'),( 5,'2016-01-01'),( 5,'2016-01-02'),( 5,'2016-01-02'),( 5,'2016-01-02'),( 5,'2016-01-03'),
  ( 5,'2016-01-03'),( 5,'2016-01-03'),( 5,'2016-01-04'),( 5,'2016-01-04'),( 5,'2016-01-04'),( 5,'2016-01-05'),( 5,'2016-01-05'),
  ( 5,'2016-01-05'),( 5,'2016-01-06'),( 5,'2016-01-06'),( 5,'2016-01-06'),( 5,'2016-01-07'),( 5,'2016-01-07'),( 5,'2016-01-07'),
  ( 5,'2016-01-08'),( 5,'2016-01-08'),( 5,'2016-01-08'),( 5,'2016-01-09'),( 5,'2016-01-09'),( 5,'2016-01-09'),( 5,'2016-01-10'),
  ( 5,'2016-01-10'),( 5,'2016-01-10'),( 5,'2016-01-11'),( 5,'2016-01-11'),( 5,'2016-01-11'),( 5,'2016-01-12'),( 5,'2016-01-12'),
  ( 2,'2016-02-01'),( 2,'2016-02-01'),( 2,'2016-02-02'),( 2,'2016-02-02'),( 2,'2016-02-03'),( 2,'2016-02-03'),( 2,'2016-02-04'),
  ( 2,'2016-02-04'),( 2,'2016-02-05'),( 2,'2016-02-05'),( 2,'2016-02-06'),( 2,'2016-02-06'),( 2,'2016-02-07'),( 2,'2016-02-07'),
  ( 2,'2016-02-08'),( 2,'2016-02-08'),( 2,'2016-02-09'),( 2,'2016-02-09'),( 2,'2016-02-10'),( 2,'2016-02-10'),( 2,'2016-02-11'),
  ( 2,'2016-02-11'),( 2,'2016-02-12'),( 2,'2016-02-12'),( 2,'2016-02-13'),( 2,'2016-02-13'),( 2,'2016-02-14'),( 2,'2016-02-14'),
  ( 2,'2016-02-15'),( 2,'2016-02-15'),( 2,'2016-02-16'),( 2,'2016-02-16'),( 2,'2016-02-17'),( 2,'2016-02-17'),( 2,'2016-02-18'),
  ( 2,'2016-02-18'),( 2,'2016-02-19'),( 2,'2016-02-19'),( 2,'2016-02-20'),( 2,'2016-02-20'),( 2,'2016-02-21'),( 2,'2016-02-21'),
  ( 2,'2016-02-22'),( 2,'2016-02-22'),( 2,'2016-02-23'),( 2,'2016-02-23'),( 2,'2016-02-24'),( 2,'2016-02-24'),( 2,'2016-02-25'),
  ( 2,'2016-02-25'),
  ( 5,'2016-02-01'),( 5,'2016-02-02'),( 5,'2016-02-03'),( 5,'2016-02-04'),( 5,'2016-02-05'),( 5,'2016-02-06'),( 5,'2016-02-07'),
  ( 5,'2016-02-08'),
  (99,'2016-02-01'),(99,'2016-02-02'),(99,'2016-02-03'),(99,'2016-02-04'),(99,'2016-02-05'),(99,'2016-02-06'),(99,'2016-02-07'),
  (99,'2016-02-08'),(99,'2016-02-09'),(99,'2016-02-10'),(99,'2016-02-11'),(99,'2016-02-12'),(99,'2016-02-13'),(99,'2016-02-14'),
  (99,'2016-02-15');
 
-- =======
-- Requête
-- =======
 
SELECT month(date) as mois,
       idTable2,
       count(*)    as nbre,
       rank() over (partition by month(date) order by month(date), count(*) desc) as rang
FROM   table1
group by month(date), idTable2
 
mois        idTable2    nbre        rang
----------- ----------- ----------- --------------------
          1           5          35                    1
          1           2          20                    2
          1           4          20                    2
          2           2          50                    1
          2          99          15                    2
          2           5           8                    3
 
Appuyez sur une touche pour continuer...

C'est quand même plus simple à mettre en oeuvre qu'avec MySql.

@+

**Dracouille** · 19/05/2016, 09h25

Envoyé par Artemus24

Je ne comprends pas bien ce que tu dis. Est-ce que ta requête est destinée à MySql ?

Oui en effet voila une image, c'est mieux qu'un long discours.

Nom : 2016-05-19_091932.png
Affichages : 90
Taille : 2,1 Ko

C'est un requeteur intégré a l'IDE que j'utilise, mais il ne reconnais pas le @.
Pour passer outre cette erreur, il faut que je stock la requête sur MySQL et que je l'appel via l'IDE.
C'était juste pour éviter de stocké une requête alors que toutes les autres sont compilé dans le projet. Juste une question de cohérence. C'est tout

Pour ce qui est du Rank partition, j'avais vus pas mal de solution comme ça sur Google, et effectivement c'est beaucoup plus simple a mettre en œuvre, et beaucoup plus clair.

**aieeeuuuuu** · 19/05/2016, 09h57

Envoyé par Artemus24

Si toutes les valeurs (je parle du count(*) dans ton exemple) sont différentes, le rang est bien incrémenté (avec des valeurs 1, 2, 3, ...).
Si nous avons un doublon, nous avons par exemple comme rang 1, 3, 3, 4, ...

C'est juste de cette contrainte dont le parlais.

Ce n'est pas vraiment une contrainte : il suffit de savoir quoi faire en cas d’exæquo.
Si par exemple la regle est de prendre les 20 premiers, et en cas d'exæquo prendre les idTable les plus petits (totalement arbitraire...), alors la requete devient :

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
 
 
SELECT b.mois, b.idTable2, b.nbre, COUNT(*) AS Rang
FROM (
	select month(date) as mois, idTable2, count(*) as nbre
	from table1
	group by mois, idTable2
) B
INNER JOIN (
	select month(date) as mois, idTable2, count(*) as nbre
	from table1
	group by mois, idTable2
) P
	ON  P.mois = b.mois
	AND (
		P.nbre >  b.nbre
		OR
		(P.nbre =  b.nbre AND P.idTable2 > b.idTable2)
	)
GROUP BY b.mois, b.idTable2, b.nbre
HAVING COUNT(*) <= 20
order by b.mois,  COUNT(*) DESC ,b.nbre desc
;

**Dracouille** · 19/05/2016, 11h17

En effet la solution du And imbriquer permet d'afficher 20 lignes.
Mais selon le cahier des charges, je m’arrête a la gare avant les doublons donc sa résout le problème =)

Merci les gars. J'ai appris 1-2 trucs...

**Artemus24** · 20/05/2016, 14h54

Salut à tous.

La solution que tu proposes aieeeuuuuu ne fonctionne pas. J'ai fait un test sur le jeu d'essai de Dracouille. Voici le résultat :

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
--------------
DROP TABLE IF EXISTS `sample`
--------------
 
--------------
create table sample
select month(date) as mois, idTable2, count(*) as nbre
from table1
group by mois, idTable2
order by mois, nbre desc
--------------
 
--------------
select * from sample
--------------
 
+------+----------+------+
| mois | idTable2 | nbre |
+------+----------+------+
|    1 |        5 |   35 |
|    1 |        2 |   20 |
|    1 |        4 |   20 |
|    2 |        2 |   50 |
|    2 |       99 |   15 |
|    2 |        5 |    8 |
+------+----------+------+
--------------
select mois, idTable2, nbre, rang
from (
    select cast(@i:=if(@prec1=mois, if(@prec2=nbre,@i, @i:=@i+1),1) as unsigned) as rang,
    @prec1:=mois as mois, @prec2:=nbre as nbre, idTable2
    from sample,(select @i:=1, @prec1:='', @prec2:='') as x
) as y
--------------
 
+------+----------+------+------+
| mois | idTable2 | nbre | rang |
+------+----------+------+------+
|    1 |        5 |   35 |    1 |
|    1 |        2 |   20 |    2 |
|    1 |        4 |   20 |    2 |
|    2 |        2 |   50 |    1 |
|    2 |       99 |   15 |    2 |
|    2 |        5 |    8 |    3 |
+------+----------+------+------+
--------------
select tb1.mois, tb1.idTable2, tb1.nbre, count(*) as rang
from       sample as tb1
inner join sample as tb2
on  tb2.mois   = tb1.mois
and tb2.nbre  >= tb1.nbre
group by tb1.mois, tb1.idTable2, tb1.nbre
order by tb1.mois, count(*)
--------------
 
+------+----------+------+------+
| mois | idTable2 | nbre | rang |
+------+----------+------+------+
|    1 |        5 |   35 |    1 |
|    1 |        2 |   20 |    3 |
|    1 |        4 |   20 |    3 |
|    2 |        2 |   50 |    1 |
|    2 |       99 |   15 |    2 |
|    2 |        5 |    8 |    3 |
+------+----------+------+------+
--------------
select tb1.mois, tb1.idTable2, tb1.nbre, count(*) as rang
from       sample as tb1
inner join sample as tb2
on    tb2.mois  = tb1.mois
and ((tb2.nbre  > tb1.nbre) OR
     (tb2.nbre  = tb1.nbre AND tb2.idTable2 > tb1.idTable2))
group by tb1.mois, tb1.idTable2, tb1.nbre
order by tb1.mois, count(*)
--------------
 
+------+----------+------+------+
| mois | idTable2 | nbre | rang |
+------+----------+------+------+
|    1 |        4 |   20 |    1 |
|    1 |        2 |   20 |    2 |
|    2 |       99 |   15 |    1 |
|    2 |        5 |    8 |    2 |
+------+----------+------+------+
--------------
select tb1.mois, tb1.idTable2, tb1.nbre, count(*) as rang
from       sample as tb1
inner join sample as tb2
on  tb2.mois      = tb1.mois
and ((tb2.nbre  > tb1.nbre) OR
     (tb2.nbre  = tb1.nbre AND tb2.idTable2 >= tb1.idTable2))
group by tb1.mois, tb1.idTable2, tb1.nbre
order by tb1.mois, count(*)
--------------
 
+------+----------+------+------+
| mois | idTable2 | nbre | rang |
+------+----------+------+------+
|    1 |        5 |   35 |    1 |
|    1 |        4 |   20 |    2 |
|    1 |        2 |   20 |    3 |
|    2 |        2 |   50 |    1 |
|    2 |       99 |   15 |    2 |
|    2 |        5 |    8 |    3 |
+------+----------+------+------+
 
Appuyez sur une touche pour continuer...

Voici mes explications :

1) j'ai créé une table de nom "sample" afin de simplifier l'écriture des requêtes. J'ai fait le vidage de cette table afin de voir le résultat obtenu.

2) j'ai repris ma solution en traitant cette fois-ci le cas du doublon. Cette solution n'est pas nécessairement conforme à ce qui se fait.
Je veux dire que si j'ai plusieurs ex æquo, par exemple deux deuxièmes, j'obtiens la séquence suivante : 1 - 2 - 2 - 3 - 4.
Alors qu'en principe (enfin d'après ce que j'ai pu voir), la bonne séquence est : 1 - 2 - 2 - 4 - 5

Avec cette solution, on dépasse le nombre de lignes imposées (<=20) car on tient compte des doublons.
Selon moi, c'est la bonne façon de faire (on peut aussi discuter de la séquence à produire).

3) c'est ta première solution, sauf que la numérotation du rang est la suivante : 1 - 3 - 3 - 4 - 5. Les doublons ne sont pas conforme à ce que l'on attend.

4) c'est ta seconde solution, mais cette fois-ci, il manque des lignes dans le select.
Cela implique que l'on ne peut pas mettre la condition stricte "tb2.idTable2 >= tb1.idTable2" dans le test. Il est nécessaire de mettre l'égalité.

5) j'ai repris ta seconde solution en mettant cette fois-ci l'égalité "tb2.idTable2 >= tb1.idTable2", mais ça ne fonctionne pas mieux.
La séquence produite ne tient plus compte des égalités : 1 - 2 - 3 - 4 - 5.

Envoyé par "aieeeuuuuu

Si par exemple la règle est de prendre les 20 premiers, et en cas d'ex æquo prendre les idTable les plus petits (totalement arbitraire...), alors la requête devient :

J'ai bien compris que ton but est justement de ne plus gérer les doublons.
Je me demande si avec MySql, on peut hors "user_defined_variables", produire un résultat conforme à ce que l'on attend !

Le count(*) donne le nombre de lignes qui sont appareillées et non une séquentialité dans la numérotation des lignes.
D'où le fait que la N ième ligne va ajouter à son résultat ce qu'elle a déjà obtenu à la N-1 ième ligne.
C'est pourquoi on passe de 1 à 3 dans le cas d'un doublon au second rang.

@+

Top 20 sur Group By

Requêtes MySQL

Discussions similaires

Partager

Partager