Início / dba / Perguntas / 273542
Accepted
AnonymousCoward
Asked: 2020-08-11 15:10:54 +0800 CST

Excluindo todos os registros em uma tabela, exceto os 10 mais recentes para cada usuário

  • 772

Eu tenho uma tabela de atividades que tem uma referência de chave estrangeira para uma tabela de usuários. Um usuário pode ter muitas atividades e eu tenho um trabalho que preencherá suas atividades diariamente. Depois que esse trabalho for executado, quero executar uma consulta SQL que excluirá os registros mais antigos por usuário e manterá os 10 mais recentes. Este é o esquema:

usuários

id name
1  Jimmy
2  Johnny

user_activities

id user_id activity date
1  1       foo      2020-08-07 00:00:00
2  1       bar      2020-08-08 00:00:00
3  1       baz      2020-08-09 00:00:00
4  2       foo      2020-08-07 00:00:00
5  2       bar      2020-08-08 00:00:00
6  2       baz      2020-08-09 00:00:00

Como posso escrever uma consulta para truncar registros até os 10 últimos para cada usuário?

mysql greatest-n-per-group

1 respostas

  1. Best Answer

    Isso funciona com o mysql 5.xe acima

    mysql tem desde a versão a função de janela ROW_NUMBER para que a subconsulta possa ser escrita sem variáveis ​​definidas pelo usuário

    Eu adicionei alguns dados para mostrar que funciona

    A ideia é simples, ordene todas as datas por user_id e mantenha as 10 mais novas, o rownumber define a posição do registro ordenado por data decrescente

    CREATE TABLE user_date
    (`id` int, `user_id` int, `activity` varchar(3), `date` varchar(19))
;
    
INSERT INTO user_date
    (`id`, `user_id`, `activity`, `date`)
VALUES
    (1, 1, 'foo', '2020-08-07 00:00:00'),
    (2, 1, 'bar', '2020-08-08 00:00:00'),
    (3, 1, 'baz', '2020-08-09 00:00:00'),
    (4, 2, 'foo', '2020-08-07 00:00:00'),
    (5, 2, 'bar', '2020-08-08 00:00:00'),
    (6, 2, 'baz', '2020-08-09 00:00:00')
    ,
    (7, 2, 'baz', '2020-08-10 00:00:00'),
    (8, 2, 'baz', '2020-08-10 00:00:00'),
    (9, 2, 'baz', '2020-08-10 00:00:00'),
    (10, 2, 'baz', '2020-08-10 00:00:00'),
    (11, 2, 'baz', '2020-08-10 00:00:00'),
    (12, 2, 'baz', '2020-08-10 00:00:00'),
    (13, 2, 'baz', '2020-08-10 00:00:00'),
    (14, 2, 'baz', '2020-08-10 00:00:00'),
    (15, 2, 'baz', '2020-08-10 00:00:00'),
    (16, 2, 'baz', '2020-08-10 00:00:00')
;

    DELETE u1
FROM
       user_date AS u1
   JOIN
(
SELECT  id
  FROM
     (
      SELECT  @row_num := IF(@prev_value=o.user_id,@row_num+1,1) AS 'RowNumber'
             ,o.id
             ,@prev_value := o.user_id
        FROM user_date o,
             (SELECT @row_num := 1) x,
             (SELECT @prev_value := '') y
       ORDER BY o.user_id, o.`date` DESC
     ) subquery
 WHERE `RowNumber` > 10 ) u2 ON u1.id = u2.id  
 

    SELECT * FROM user_date

    identificação | user_id | atividade | encontro               
-: | ------: | :------- | :------------------
 1 | 1 | foo | 07-08-2020 00:00:00
 2 | 1 | barra | 08-08-2020 00:00:00
 3 | 1 | baz | 09-08-2020 00:00:00
 7 | 2 | baz | 2020-08-10 00:00:00
 8 | 2 | baz | 2020-08-10 00:00:00
 9 | 2 | baz | 2020-08-10 00:00:00
10 | 2 | baz | 2020-08-10 00:00:00
11 | 2 | baz | 2020-08-10 00:00:00
12 | 2 | baz | 2020-08-10 00:00:00
13 | 2 | baz | 2020-08-10 00:00:00
14 | 2 | baz | 2020-08-10 00:00:00
15 | 2 | baz | 2020-08-10 00:00:00
16 | 2 | baz | 2020-08-10 00:00:00

    db<>fique aqui

    Para SQL Server

    CREATE TABLE user_date
    (id int, user_id int, activity varchar(3), date varchar(19))
;
    
INSERT INTO user_date
    (id, user_id, activity, date)
VALUES
    (1, 1, 'foo', '2020-08-07 00:00:00'),
    (2, 1, 'bar', '2020-08-08 00:00:00'),
    (3, 1, 'baz', '2020-08-09 00:00:00'),
    (4, 2, 'foo', '2020-08-07 00:00:00'),
    (5, 2, 'bar', '2020-08-08 00:00:00'),
    (6, 2, 'baz', '2020-08-09 00:00:00')
    ,
    (7, 2, 'baz', '2020-08-10 00:00:00'),
    (8, 2, 'baz', '2020-08-10 00:00:00'),
    (9, 2, 'baz', '2020-08-10 00:00:00'),
    (10, 2, 'baz', '2020-08-10 00:00:00'),
    (11, 2, 'baz', '2020-08-10 00:00:00'),
    (12, 2, 'baz', '2020-08-10 00:00:00'),
    (13, 2, 'baz', '2020-08-10 00:00:00'),
    (14, 2, 'baz', '2020-08-10 00:00:00'),
    (15, 2, 'baz', '2020-08-10 00:00:00'),
    (16, 2, 'baz', '2020-08-10 00:00:00')
;
GO

    DELETE u1
FROM
       user_date AS u1
   JOIN
(
SELECT  id
  FROM
     (
      SELECT    ROW_NUMBER() OVER(PARTITION BY o.user_id ORDER BY o.[date] DESC) 
                    AS RowNumber
             ,o.id
             ,o.user_id
        FROM user_date o
     ) subquery
 WHERE RowNumber > 10 ) u2 ON u1.id = u2.id  
 
GO

    

    3 linhas afetadas
    

    


    


    

    SELECT * FROM user_date
GO

    

    identificação | user_id | atividade | encontro               
-: | ------: | :------- | :------------------
 1 | 1 | foo | 07-08-2020 00:00:00
 2 | 1 | barra | 08-08-2020 00:00:00
 3 | 1 | baz | 09-08-2020 00:00:00
 7 | 2 | baz | 2020-08-10 00:00:00
 8 | 2 | baz | 2020-08-10 00:00:00
 9 | 2 | baz | 2020-08-10 00:00:00
10 | 2 | baz | 2020-08-10 00:00:00
11 | 2 | baz | 2020-08-10 00:00:00
12 | 2 | baz | 2020-08-10 00:00:00
13 | 2 | baz | 2020-08-10 00:00:00
14 | 2 | baz | 2020-08-10 00:00:00
15 | 2 | baz | 2020-08-10 00:00:00
16 | 2 | baz | 2020-08-10 00:00:00

    

    

    db<>fique aqui
    
                                                                
    
                                                                
    
                                                                
    
                                                                
    
                                                                
    
                                                                    
      
                                                                        
      • 
                                                                        
    • 
                                                                            2
                                                                        
      • 
                                                                        
    • 
                                                                        
      • 
                                                                        
      • 
                                                                    
    


                                                                
    
                                                            
                                                            
    
                                                        
    
                                                     
    2. 
                                                
                                                

                                            

                                        

                                    

                                

                            

                            

                                
relate perguntas