Problème select max() sur un select count()

Version imprimable

14/02/2015, 12h37
totoAussi

Problème select max() sur un select count()
Bonjour,

Soit la table arrivee (qui est simple, et qui sert à enregistrer les utilisateurs qui arrivent à l'école à telle jour et à telle heure) avec les données suivantes :

id nom jour heure

1 Alice Lundi 11

2 Alice Lundi 13

3 Alice Mardi 11

4 Céline Mercredi 14

5 Céline Mercredi 13

6 Céline Jeudi 14

7 Maud Vendredi 15

8 Maud Samedi 15

9 Maud Samedi 16

Maintenant, je cherche la requête qui me permet de trouver pour chaque utilisateur, le jour le plus fréquent et l'heure le plus fréquent, càd que le résultat de la requête doit retourner ces lignes :

Alice Lundi 11

Céline Mercredi 14

Maud Samedi 15

=> car :
-Alice vient souvent le Lundi, et souvent à 11h
-Céline vient souvent le Mercredi, et souvent à 14h
-Maud vient souvent le Samedi, et souvent à 15h

Pour l'instant, ma requête est celle-là mais elle ne donne pas le résultat voulu :
Code:

1 2 select nom, jour, max(count_heure) from (SELECT nom, jour, COUNT(heure) as count_heure FROM arrivee GROUP BY nom, jour) as alias_table group by nom
Merci d'avance, cordialement.

PS : mon sgbd est mysql.

Code:

1
2
3
4
5
6
7
8
WITH T1 AS (
SELECT ID, NOM, COUNT(*) AS NB_HEURE 
FROM   ARRIVEE
GROUP  BY  ID, NOM)
SELECT ID, NOM, MAX(NB_JOUR) NB_MAX_JOUR,
                MAX(NB_HEURE) AS NB_MAX_HEURE
FROM   T1
GROUP  BY ID, NOM;

A +

Citation:
Envoyé par SQLpro
Code:

1 2 3 4 5 6 7 8 WITH T1 AS ( SELECT ID, NOM, COUNT(*) AS NB_HEURE FROM ARRIVEE GROUP BY ID, NOM) SELECT ID, NOM, MAX(NB_JOUR) NB_MAX_JOUR, MAX(NB_HEURE) AS NB_MAX_HEURE FROM T1 GROUP BY ID, NOM;
A +
@SQLpro, Je suis pas convaincu que cette solution convienne... déjà, t'as pas de colonne nb_jour dans ta vue ...

@TotoAussi, En fait, la question que je me pose, c'est qu'est ce qu'il se passe si ton heure la plus fréquente n'est pas sur ton jour le plus fréquent ?

D'apres ce que je vois, ta recherche d'information est complètement indépendante... d'un coté tu recherches un jour fréquent, de l'autre, une heure fréquente, et tu combines cela comme si c'etait l'heure et le jour idéal... enfin, c'est l'idée qui en sort quand je lis tes explications...

Par exemple, si Alice vient 2 mardi à 14h et 15h, et lundi à 11h et mercredi à 11h, ca va te sortir "Alice mardi 11h" alors qu'elle est jamais venue à cet horraire la... c'est bien ce que tu veux ?

Quoi qu'il en soit, essayons cela :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 --Si tu veux utiliser mon code, tu supprimes de la with arrivee as ( select '1' as ID,'Alice' as nom,'Lundi' as jour,'11' as heure from dual union select '2','Alice','Lundi','13' from dual union select '3','Alice','Mardi','11' from dual union select '4','Celine','Mercredi','14' from dual union select '5','Celine','Mercredi','13' from dual union select '6','Celine','Jeudi','14' from dual union select '7','Maud','Vendredi','15' from dual union select '8','Maud','Samedi','15' from dual union select '9','Maud','Samedi','16' from dual )--jusque la select A.nom, A.jour, B.heure from ( select nom, max(jour) keep (dense_rank first order by nb_jour desc) as jour from ( select nom, jour, count(*) nb_jour from arrivee group by nom, jour ) group by nom ) A inner join (select nom, max(heure) keep (dense_rank first order by nb_heure desc) as heure from ( select nom, heure, count(*) nb_heure from arrivee group by nom, heure ) group by nom ) B on A.nom = B.nom
A, c'est ton jour idéal pour chaque personne,
B c'est ton heure idéal pour chaque personne...
combinés avec une jointure sur le nom (c'est assez moche) puisque ton ID n'est qu'une PK identifiant ta ligne

Alors, ca fonctionne tant que ton jour max est unique... s'il ne l'est plus, ca choisit le jour max selon l'alphabet, ce qui n'a juste aucune valeur fonctionnelle...
T'auras le même problème avec l'heure, ca choisira l'heure max...

Tu n'utilises pas le standard de SQL en instruisant la fonction keep.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--Si tu veux utiliser mon code, tu supprimes de la
with arrivee  as (
select '1' as ID,'Alice' as nom,'Lundi' as jour,'11' as heure from dual union
select '2','Alice','Lundi','13' from dual union
select '3','Alice','Mardi','11' from dual union
select '4','Celine','Mercredi','14' from dual union
select '5','Celine','Mercredi','13' from dual union
select '6','Celine','Jeudi','14' from dual union
select '7','Maud','Vendredi','15' from dual union
select '8','Maud','Samedi','15' from dual union
select '9','Maud','Samedi','16' from dual
)--jusque la
 select A.nom ,A.jour ,(select B.heure from arrivee B where A.nom=B.nom group by B.nom,B.heure having count(B.heure)=(select max(count(C.heure)) from arrivee C group by C.nom,C.heure))
 from arrivee A
 group by A.jour,A.nom
 having count(A.nom)=(select max(count(D.nom)) from arrivee D group by D.nom,D.jour)

sinon

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
--Si tu veux utiliser mon code, tu supprimes de la
with arrivee  as (
select '1' as ID,'Alice' as nom,'Lundi' as jour,'11' as heure from dual union
select '2','Alice','Lundi','13' from dual union
select '3','Alice','Mardi','11' from dual union
select '4','Celine','Mercredi','14' from dual union
select '5','Celine','Mercredi','13' from dual union
select '6','Celine','Jeudi','14' from dual union
select '7','Maud','Vendredi','15' from dual union
select '8','Maud','Samedi','15' from dual union
select '9','Maud','Samedi','16' from dual
)--jusque la
 
select A.nom,A.jour,B.heure
 
from    (    select    nom,     count(jour) nb_jour,jour 
                    from arrivee
                    group by nom, jour
           having count(nom)=(select max(count(D.nom)) from arrivee D group by D.nom,D.jour)
 
 
        ) A
 
inner join    (select    distinct nom,heure, count(heure) nb_heure 
                from arrivee
                group by nom, heure
         having count(heure)=(select max(count(D.heure)) from arrivee D group by D.nom,D.heure)
        ) 
   B on A.nom = B.nom

18/02/2015, 01h31
bstevy
Je suis désolé, mais aucune de tes solutions ne fonctionnent... il suffit de rajouter deux lignes dans le jeu de test, et ca ne sort plus aucun résultat valable...
par exemple
Code:

1 2 3 union select '10', 'Maud', 'Samedi', '17' from dual union select '11', 'Maud', 'Mardi', '15' from dual
Avec ces deux lignes, aucune de tes requêtes ne sort les bons résultats.

Après, c'est vrai qu'avec mysql, ca peut poser un probleme d'utiliser le keep... mais j'ai pas envie de faire 15 requêtes imbriquées pour avoir le même résultat. J'en ai déjà trois, c'est bien suffisant !

MySQL supporte seulement les fonctions analytiques ?

@bstevy et @islamov2000 :
Proposer une sémantique 100% Oracle alors que l'auteur a bien précisé être sous MySQL n'a strictement aucun intérêt pour l'auteur.
Ça vous permet de vous entraîner seul dans votre coin, ce qui est une bonne démarche mais dans ce cas-là gardez vos résultats pour vous et comparez au résultat attendu, sinon ça devient compliqué de s'en sortir si tous les membres traduisent les requêtes dans tous les dialectes SQL existants.

De manière générale, si vous n'avez pas le SGBD de l'auteur sous la main mais que vous voulez participer à la résolution de son problème, utilisez SQL Fiddle qui supporte MySQL, SQL-Server, Oracle, PostgreSQL et SQLite.

Citation:

Envoyé par bstevy

Après, c'est vrai qu'avec mysql, ca peut poser un probleme d'utiliser le keep
MySQL supporte seulement les fonctions analytiques ?

Keep n'est :

pas nécessairement une fonction analytique.
pas une fonction tout court. La fonction c'est FIRST ou LAST.

Et afin de conclure cette digression sous Oracle, il y a une fonction native qui répond directement à la problématique de l'auteur :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 with arrivee (id, nom, jour, heure) as ( select 1, 'Alice' , 'Lundi' , 11 from dual union all select 2, 'Alice' , 'Lundi' , 13 from dual union all select 3, 'Alice' , 'Mardi' , 11 from dual union all select 4, 'Celine', 'Mercredi', 14 from dual union all select 5, 'Celine', 'Mercredi', 13 from dual union all select 6, 'Celine', 'Jeudi' , 14 from dual union all select 7, 'Maud' , 'Vendredi', 15 from dual union all select 8, 'Maud' , 'Samedi' , 15 from dual union all select 9, 'Maud' , 'Samedi' , 16 from dual ) select nom , stats_mode(jour) as jour , stats_mode(heure) as heure from arrivee group by nom order by nom asc; NOM JOUR HEURE ------ -------- ---------- Alice Lundi 11 Celine Mercredi 14 Maud Samedi 15

19/02/2015, 01h03
bstevy
Citation:
Envoyé par Waldar

Keep n'est :

pas nécessairement une fonction analytique.
pas une fonction tout court. La fonction c'est FIRST ou LAST.
Hmm, quand je demandais pour les fonctions analytiques, je pensais pas à keep en fait... je voulais utiliser un "rank over", un truc comme ca ... mais je sais pas si ca passe sur MySQL donc je vais regarder ton lien

id	nom	jour	heure
1	Alice	Lundi	11
2	Alice	Lundi	13
3	Alice	Mardi	11
4	Céline	Mercredi	14
5	Céline	Mercredi	13
6	Céline	Jeudi	14
7	Maud	Vendredi	15
8	Maud	Samedi	15
9	Maud	Samedi	16