Discussion :

[marietar]

bonjour
j'ai une base de donnée où j'ai pour chaque pays et pour chaque jour du mois, le nombre de jeux qui ont été activés par exemple.
je vous donne un extrait pour la france et UK, pour quelques mois et quelques jours dans le fichier Excel joint (onglet BDD).



je joins aussi l'Excel qui montre cette base de donnée et des exemples de mes résultats intermédiaires.
Classeur1.xlsx

j'aimerai avoir pour chaque mois, le jour pour lequel on a activé le plus de jeux.



En gros:
- au mois de janvier c'est le jour 6 qui a cumulé le plus de jeux pour les pays france et UK (5 jeux activés)
- au mois de février c'est le jour 9 qui a cumulé le plus de jeux pour les pays france et UK (8 jeux activés)
- etc...
- et si dans un mois, il y a plusieurs jours à égalité, ca me sortir le 1er jour trouvé ou l'ensemble des jours, eu importe pour l'instant.


j'arrive à faire une requête A où je trouve le nombre de jeux activés pour chacune des jours de chaque mois

je le fais avec une requête du genre

Code :

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

1
2
3
4
SELECT EXTRACT(month FROM date) as mois, EXTRACT(day FROM date) as jour, count(*) as nb
from "BDD"
GROUP BY mois, jour
ORDER BY mois, jour;

avec une 2e requête où je fais une requête en me basant sur la requête A

Code :

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

1
2
3
4
5
6
7
SELECT mois, max(nb) AS max_games_of_day_of_month
from (SELECT EXTRACT(month FROM date) as mois, EXTRACT(day FROM date) as jour, count(*) as nb
    from "BDD"
    GROUP BY mois, jour
    ORDER BY mois, jour)
group by mois
ORDER BY mois;

j'obtiens ça : j'arrive pour chaque mois à avoir le nombre de jeux max activés, mais je n'arrive pas à afficher le jour correspondant.



vous savez comment faire?
je dois faire une jointure entre le requête A et la 2e requête?
y a pas moyen de faire ca sans jointure avec des requêtes imbriquées?

merci

[escartefigue]

Bonjour,

Si votre SGBD autorise les fonctions fenêtrées, alors vous pouvez utiliser la requête suivante :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
select * 
from (
     select T1pays
          , T1date
          , T1jeux
          , row_number()
            over(partition by T1pays, left(T1date, 7)
                 order by T1jeux desc
                ) as RN
     from T1           
    ) as XX
where XX.RN=1

À noter : "date" est un mot réservé SQL, il est donc préférable de ne pas l'utiliser comme nom de colonne.
D'une façon générale, l'utilisation d'un préfixe ou d'un suffixe pour chaque colonne d'une même table permet d'éviter ce genre de piège (par exemple ci-dessus, le préfixe T1 pour toutes les colonnes de la table T1, ainsi T1date n'est pas un nom réservé )

[marietar]

Merci je teste ça ce soir.
Je me trompe peut être mais je pense que je ne vais pas avoir les mois et les jours séparément comme dans l'exemple que j'ai montré ?
Je vais regarder et je vous redis ca

[escartefigue]

il suffit d'adapter

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
select * 
from (
     select T1pays
          , year(T1date)  as T1annee
          , month(T1date) as T1mois
          , T1jeux
          , row_number()
            over(partition by T1pays, left(T1date, 7)
                 order by T1jeux desc
                ) as RN
     from T1           
    ) as XX
where XX.RN=1

Selon le SGBD, la syntaxe peut changer, un cast de la date en char() peut être nécessaire.

Exemple, sous PG, on fera plutôt :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
select * 
from (
     select T1pays
          , extract(year from T1date)  as T1annee
          , extract(month from T1date) as T1mois
          , T1jeux
          , row_number()
            over(partition by T1pays, left(cast(T1date as char(10)), 7)
                 order by T1jeux desc
                ) as RN
     from T1           
    ) as XX
where XX.RN=1

[marietar]

merci, j'ai pas les moyens de tester avant ce soir mais en lisant le code, j'ai l'impression qu'il manque un GROUP BY.

je veux regrouper les mois et les jours quel que soit le pays.
en gros je au préalable connaitre le nom de jeux activé pour la 1er jour du mois de janvier (en sommant les pays france et UK).
Là j'ai l'impression que je classe en fonction du nombre de jeux activé par jour, par moi et par pays.

mais merci pour la piste, je vais reflechir à ce soir.

[escartefigue]

Il n'est pas nécessaire d'utiliser un GROUP BY

Je n'avais pas vu que le SGBD était MySQL, c'était dans le titre
Avec MySQL, les fonctions fenêtrées ne sont apparues qu'avec la V8, donc si vous avez une version inférieure, ROW_NUMBER() ne fonctionnera pas !
À vérifier par un select @@version.


Voici un exemple complet sous MySQL V8 :

Création de la table :

Code SQL :

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

1
2
3
4
5
6
7
create table T1 
      (  T1ident   integer  not null primary key
       , T1pays    char(10) not null
       , T1date    date     not null
       , T1jeux    integer  not null
      )
;

Création d'un jeu d'essai réduit :

Code SQL :

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
insert into T1
      (T1ident, T1pays, T1date, T1jeux)
values (11, 'france', '2022-01-01', 1)      
     , (12, 'france', '2022-01-02', 1)
     , (13, 'france', '2022-01-03', 2)
     , (14, 'france', '2022-01-04', 1)
     , (15, 'france', '2022-01-05', 2)
     , (16, 'france', '2022-01-06', 3)
     , (21, 'france', '2022-02-01', 1)
     , (22, 'france', '2022-02-02', 5)
     , (23, 'france', '2022-02-03', 1)
     , (24, 'france', '2022-02-04', 2)
     , (81, 'UK','2022-01-01', 5)      
     , (82, 'UK', '2022-01-02', 1)
     , (83, 'UK', '2022-01-03', 2)
     , (84, 'UK', '2022-01-04', 2)
     , (85, 'UK', '2022-01-05', 3)
     , (86, 'UK', '2022-01-06', 1)
     , (91, 'UK', '2022-02-01', 2)
     , (92, 'UK', '2022-02-02', 2)
     , (93, 'UK', '2022-02-03', 1)
     , (94, 'UK', '2022-02-04', 4)
;

La requête (j'ai ajouté le CAST de la date, c'est plus propre et plus portable sur un autre SGBD si besoin) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
select * 
from (
     select T1pays
          , year(T1date)  as T1annee
          , month(T1date) as T1mois
          , T1jeux
          , row_number()
            over(partition by T1pays, left(cast(T1date as char(10)), 7)
                 order by T1jeux desc
                ) as RN
     from T1           
    ) as XX
where XX.RN=1

Et le résultat obtenu :

T1pays	T1annee	T1mois	T1jeux	RN
france	2022	1	3	1
france	2022	2	5	1
UK	2022	1	5	1
UK	2022	2	4	1

[marietar]

merci mais il y a toujours le meme detail qui n'est pas pris en compte dans votre réponse.
je vais le repréciser mais c'est pas grave si vous répondez pas.

dans le résultat final, je ne veux pas distinguer les pays france ou UK, je veux cumuler (ou additioner) le nombre de jeux pour un meme jour et un meme mois (pour les 2 pays).

[escartefigue]

D'accord, du coup il faut bien un regroupement.
Le principe reste le même, voici la requête adaptée pour ce besoin :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select * 
from (
     select year(T1date)  as T1annee
          , month(T1date) as T1mois
          , T1date
          , sum(T1jeux)   as Cumul
          , row_number()
            over(partition by left(cast(T1date as char(10)), 7)
                 order by sum(T1jeux) desc) as RN
     from T1
     group by T1date
    ) as XX
where RN=1
;

Et le résultat obtenu avec le même jeu d'essai :

T1annee	T1mois	T1date	Cumul	RN
2022	1	2022-01-01	6	1
2022	2	2022-02-02	7	1

[marietar]

ok merci bcp