Discussion :

[moimp]

Bonjour à tous,
J'ai une table qui comporte les colonnes: id_user, id_address, update_date
Cette table sert à alimenter une liste PHP des derniers enregistrements consultés (liste récents).
Je voudrais effacer les enregistrements les plus anciens pour ne conserver que les 20 les plus récents de chaque utilisateur (id_user).
Je ne vois pas comment écrire ma requête.

[tatayo]

Bonjour,
La première chose à faire est de calcler un rang:

Code sql :

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

1
2
3
 
select  id_user, id_address, update_date, rank() over (partition by id_user order by update_date desc) as rang
from user

A partir de cette requête, on peut lancer la suppression:

Code sql :

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

1
2
3
4
5
6
7
8
9
 
with CTE as
(
select  id_user, id_address, update_date, rank() over (partition by id_user order by update_date desc) as rang
from user
)
delete from user
inner join cte on cte.id_user  =user.id_user and cte.update_date = user.update_date
where cte.rang >= 20

A vérifier quand même, je n'ai pas de base MySql sous la main.

Tatayo

[moimp]

Bonjour,
Je me suis penché sur ta proposition en la testant sur PhpMyAdmin et en consultant des tutoriels. PhpMyAdmin avec la version MySQL 5.7.4 m'indique plusieurs erreurs:
La première erreur concerne l'alias rang. Si j'enlève l'alias pour tester uniquement select id_user, id_address, update_date, rank() over (partition by id_user order by update_date desc) from user, j'obtiens une erreur:

#1064 - Erreur de syntaxe près de '(partition by id_user order by update_date desc) from dat_recents LIMIT 0, 25' à la ligne 1

CTE n'est disponible qu'à partir de la version 8.0 et mon hébergeur (Ionos) en hébergement mutualisé en est encore à la version 5.7.
Je n'ai pas contrôlé la suite.

[escartefigue]

Bonjour,

En effet, les fonctions analytiques ne sont disponibles que depuis la V8 de MySQL

Mais vous pouvez reproduire l'équivalent de RANK() par d'autres méthodes

Exemple :

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
create table T1
      (  id_user      integer  not null
       , update_date  date     not null
      )
;
insert into T1 (id_user, update_date)
values (1, '2022-10-05') 
     , (1, '2018-11-16')
     , (3, '2020-04-08')
     , (2, '2023-05-30')
     , (1, '2019-06-22')
     , (1, '2019-07-28')
     , (2, '2022-11-11')
     , (2, '2022-11-15')
;
select TA.id_user      as ID
     , TA.update_date  as DT
     , count(*)        as RK  
from T1 as TA
left join T1 as TB
  on TB.id_user=TA.id_user
 and TB.update_date>=TA.update_date
group by TA.id_user
       , TA.update_date

Résultat :

[moimp]

J'obtiens bien une table triée comme je la souhaite mais maintenant, je n'arrive pas à intégrer cette clause SELECT dans ma clause d'effacement:

Code sql :

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

1
2
delete from dat_recents
where TA.ID= 2 and TA.RK>30

[escartefigue]

Il faut utiliser la requête précédente en jointure avec la table à purger comme suit :

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
delete from T1  
using  T1 as T1
inner join 
     (select TA.id_user      as ID
           , TA.update_date  as DT
           , count(*)        as RK  
      from T1 as TA
      left join T1 as TB
         on TB.id_user=TA.id_user
       and TB.update_date>=TA.update_date
      group by TA.id_user
             , TA.update_date
     ) as S1
   on S1.ID = T1.id_user
  and S1.DT = T1.update_date
  and S1.RK > 2
;

[moimp]

1. Je comprends le principe mais pas le détail, notamment au niveau des alias.
2. Lorsque j'essaye d'utiliser votre exemple avec PhpMyAdmin (version MySQL 5.7.40), j'ai une erreur à la ligne 3 (la jointure interne sur la clause select provoque une erreur).
3. Le vrai nom de la table dont je dois supprimer les enregistrements n'apparaît nulle part. J'ai donc remplacé dans la ligne 2 T1 as T1 par dat_recents as T1 mais je ne suis pas sûr que ce soit bon.

[escartefigue]

Il suffit de remplacer le nom de la table dans la clause USING (j'avais mis T1 faute de connaître le vrai nom) et ça doit fonctionner avec MySQL 5.7

Par exemple USING dat_recents as T1

[moimp]

C'est bien ce que j'avais fait (point 3 de #7) mais j'ai toujours une erreur sur inner join (point 2).

[escartefigue]

C'est parce que dans la requête corrélée, il faut également remplacer le nom de la table :

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
delete from T1  
using  dat_recents as T1
inner join 
     (select TA.id_user      as ID
           , TA.update_date  as DT
           , count(*)        as RK  
      from dat_recents as TA
      left join dat_recents as TB
         on TB.id_user=TA.id_user
       and TB.update_date>=TA.update_date
      group by TA.id_user
             , TA.update_date
     ) as S1
   on S1.ID = T1.id_user
  and S1.DT = T1.update_date
  and S1.RK > 2
;

[moimp]

Merci, c'est bon, tout fonctionne.