Discussion :

[Invité]

bonjour,

y-a-t-il un moyen de limiter le nombre de données concatennée (donc regroupées avant) dans un group_concat

si j'ai 1000 lignes comme ça :

personne/annee/avis
p1,2025,ok
p1,2024,bad
p1,2023,nop
p1,2026,bad
p1,2027,ok
p1,2022,nop
p2,2025,nop

je veux avoir :

p1 (nop,nop,bad,ok,bad)
p2 (nop)

cad que je veux que le group_concat ne concatenne que 5 tuples par personnes

et non pas ça (p1 en a 6 au lieu des 5 premiers)

p1 (nop,nop,bad,ok,bad,ok)
p2 (nop)

Code sql :

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

1
2
3
4
SELECT personne , GROUP_CONCAT (avis)
FROM matable
GROUP BY personne
ORDER BY annee ASC

si je mets un LIMIT, ça va concerner le Nb de groupes !
donc je suis coincé...

comment faire ?

[skuatamad]

Il faut utiliser row_number dans une sous-requête :

Code :

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

1
2
3
4
5
6
7
8
9
with t as (
select personne, annee, avis
     , row_number() over (partition by personne order by annee) as rn
  from matable
)
select personne , group_concat (avis order by annee asc)
  from t
 where rn <= 5
 group by personne;

[Invité]

bonjour,

merci pour votre aide et/ou solution mais j'ai du mal à comprendre.
je prends un autre exemple.
si j'ai une liste de note d'examen pour chaque etudiant (e,n)
et que je veux la moyenne des 5 dernieres notes et non pas des 10 , comment on integre cela dans le GROUP BY ou dans le AVG?

question subsidaire:

le LIMIT et le ORDER BY, il n'y a pas un moyen de l'appliquer aux tuples avant qu'ils soient regroupé par le GROUP BY ?

[escartefigue]

Voici un exemple de calcul de moyenne des 5 dernières notes par élève :

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
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
with T1(C1, C2, C3) as
    (select 1, 18, '2025-01-10'   union all
     select 1, 15, '2025-01-12'   union all
     select 1, 18, '2025-01-19'   union all
     select 1, 07, '2025-01-25'   union all
     select 1, 14, '2025-02-01'   union all
     select 1, 11, '2025-02-07'   union all
     select 1, 20, '2025-02-10'   union all
     select 2, 08, '2025-01-10'   union all  
     select 2, 06, '2025-01-12'   union all  
     select 2, 11, '2025-01-14'   union all  
     select 2, 12, '2025-01-19'   union all  
     select 2, 16, '2025-01-25'   union all    
     select 2, 11, '2025-02-07'   union all    
     select 2, 11, '2025-02-10'   union all      
     select 3, 15, '2025-01-10'   union all      
     select 3, 19, '2025-01-12'   union all        
     select 3, 18, '2025-01-19'
    )
   , T2(C1, C2, C3) as
    (select C1
          , C2
          ,  row_number()
             over (partition by C1 order by C3 desc)
     from T1
    )
   , T3(ID, NM, PR) as
     (select 1, 'Martin', 'Sidonie'   union all
      select 2, 'Dupont', 'Jean'      union all
      select 3, 'Legros', 'Pascal'
     )
select C1       as "Ident"
     , T3.PR    as "Prénom"
     , T3.NM    as "Nom"
     , cast(avg(C2) as decimal (5,2)) as "Moyenne"
from T2
inner join T3
   on T3.ID=T2.C1
where C3 < 6  
group by C1, PR, NM

Résultat :




C'est la clause ORDER BY de la fonction ROW_NUMBER() qui permet de classer par date décroissante

[Invité]

bonjour,

merci, je pensais pas que le genre de requête "remonter la moyenne des x dernières Y" serait si compliquée.
pour simplifier, je pense qu'il faut créer une vue des 5 dernièr tuples déjà classés, plutôt non ?

[Sve@r]

Bonjour

pour simplifier, je pense qu'il faut créer une vue des 5 dernièr tuples déjà classés, plutôt non ?

C'est exactement à ça que servent les CTE (les Common Table Expression) ie les requêtes with. Créer une table en mémoire contenant des éléments particuliers afin de pouvoir les filtrer ensuite par une requête qui ira interroger ladite table. C'est comme une vue sauf que tout se fait en RAM.

Principe: with toto as (select plein de trucs from table réelle) select * from toto where certains trucs ont certaines valeurs;.

[escartefigue]

Pas vraiment, les Common Table Expression (CTE) servent à externaliser les sous-requêtes de façon d'une part à pouvoir les mutualiser si elles servent à plusieurs reprises dans la même requête principale et d'autre part à simplifier la lecture de la requête principale. Par ailleurs, les CTE sont requises pour les requêtes récursives.
Une CTE n'est pas obligatoirement chargée en RAM, pas plus que tout autre requête, tout dépend de la taille de l'ensemble matérialisé par la requête.
Par ailleurs, une CTE n'est pas un objet de la base de données, alors que la vue en est un, la vue est enregistrée à ce titre dans le catalogue relationnel et fait l'objet de privilèges (GRANT), ce n'est pas le cas d'une CTE.