Início / dba / Perguntas / 120122
Accepted
Mark D
Asked: 2015-11-05 12:44:04 +0800 CST

Excluindo linhas da tabela onde os valores são menores que um valor agregado

  • 772

Estou tentando remover duplicatas de uma tabela.

CREATE TABLE media_views (
  `companyid` INT (11) NOT NULL,
  `channel` INT(11) NOT NULL,
  `date` DATE NOT NULL,
  `views` INT(11) NOT NULL DEFAULT 0
  PRIMARY KEY (`companyid`,`channel`,`date`,`views`)
) ENGINE=InnoDB;

INSERT INTO media_views (companyid, channel, `date`, views)
     VALUES (1, 0,  '2015-10-01', 15),
            (1, 0,  '2015-10-01', 25),
            (1, 0,  '2015-10-01', 541),
            (1, 5,  '2015-10-01', 2),
            (1, 8,  '2015-10-01', 53),
            (1, 91, '2015-10-01', 8571),
            (3, 12, '2015-10-01', 11),
            (1, 0,  '2015-10-02', 1),
            (1, 0,  '2015-10-02', 13),
            (1, 5,  '2015-10-02', 17),
            (1, 91, '2015-10-03', 37),
            (3, 0,  '2015-10-03', 73);

Agora o problema é que tenho erros "duplicados", tenho registros que coincidem com as datas mas as visualizações são diferentes. Apenas os valores máximos das visualizações são realmente úteis. Então, posso resolver esse problema fazendo o seguinte.

SELECT companyid, channel, date, MAX(views) 
FROM media_views 
GROUP BY companyid, channel, date 
HAVING COUNT(1) >= 1;

Isso claramente me fornece o seguinte:

+-----------+---------+------------+------------+
| companyid | channel | date       | MAX(views) |
+-----------+---------+------------+------------+
| 1         | 0       | 2015-10-01 | 541        |
| 1         | 0       | 2015-10-02 | 13         |
| 1         | 5       | 2015-10-01 | 2          |
| 1         | 5       | 2015-10-02 | 17         |
| 1         | 8       | 2015-10-01 | 53         |
| 1         | 91      | 2015-10-01 | 8571       |
| 1         | 91      | 2015-10-03 | 37         |
| 3         | 0       | 2015-10-03 | 73         |
| 3         | 12      | 2015-10-01 | 11         |
+-----------+---------+------------+------------+
9 rows in set (0.11 sec)

O que é ótimo, mas como faço para voltar e excluir as linhas duplicadas e menores que o valor max(views)?

mariadb clustered-primary-key

2 respostas

  1. Best Answer

    Você pode selecionar as linhas para continuar usando uma junção à esquerda - http://sqlfiddle.com/#!9/3a677/3

    select *
from media_views m1
left join media_views m2
  on (m1.companyid = m2.companyid
      and m1.channel = m2.channel
      and m1.date = m2.date
      and m1.views < m2.views)
where m2.companyid is null;

    E você pode excluir os outros usando uma junção interna semelhante - http://sqlfiddle.com/#!9/d5d36/2

    delete m1 from media_views m1
join media_views m2
  on (m1.companyid = m2.companyid
      and m1.channel = m2.channel
      and m1.date = m2.date
      and m1.views < m2.views);

    E não se esqueça de modificar a chave primária após excluí-los, para que não seja mais possível.

    • 1 
  2. delete from media_views
where (companyid,channel,date,views) 
not in 
(
SELECT companyid, channel, date, MAX(views) 
FROM media_views 
GROUP BY companyid, channel, date 
HAVING COUNT(1) >= 1
);
    • 0

relate perguntas