SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 26.11.2007 21:51:38

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Удаление дубликатов, GROUP BY?

Есть таблица с такими тремя полями:

Код:

create table t1 (
`id` int not null,
`code` int not null,
`date` date not null
) ENGINE = MyISAM

В таблице могут повторятся записи, нужно удалить дубликаты по id таким образом:

найти повторяющиеся id
внутри группы удалить те у кого НЕ максимальный code
если code тоже одинаковые - найти те, у кого НЕ последняя дата
если и дата совпадает - удалить все, кроме одного (любого)...


Как это сделать оптимальным образом?

Отредактированно Shopen (26.11.2007 21:51:58)

Неактивен

 

#2 26.11.2007 22:10:48

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Удаление дубликатов, GROUP BY?

Наверное
SELECT * from t1 order by id, code desc, date desc;
Читать курсором и с помощью IF выбирать то, что надо и записывать в новую таблицу.

Неактивен

 

#3 26.11.2007 22:19:24

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Удаление дубликатов, GROUP BY?

Хм... а без курсоров никак?

Забыл дописать, что code - уникальное значение в таблице, если это имеет конечно какое то значение.
Да и табличка нехилая, а дубликатов в ней мало (<2%) - ворочать ее копированием не очень выгодно.

Отредактированно Shopen (26.11.2007 22:30:02)

Неактивен

 

#4 26.11.2007 23:40:25

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Удаление дубликатов, GROUP BY?

Можно еще такое решение:

CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD UNIQUE(id);
INSERT INTO t2 SELECT * from t1 order by id, code desc, date desc ON DUPLICATE KEY UPDATE SET code=code;

Если без копирования, то надо через подзапросы, что эквивалентно копированию. Кроме того, удобно будет воспользоваться уникальностью поля code, возмножно удастся во временную таблицу заносить не все данные, а только значения code.

Неактивен

 

#5 27.11.2007 01:13:34

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Удаление дубликатов, GROUP BY?

Без копирования не удастся по любому. Глядите, пусть в Вашей табличке
всего две строки, которые выглядят так: id=0, code=0, date='2007-01-01 00:00:00'.

Попробуйте придумать запрос, который бы удовлетворял Вашему условию -
удалить только одну строку из двух.

Варианта решения маленькой проблемы два: или Вы добавляете
поле с primary key, или используете LIMIT. Во втором случае, очевидно, Вам
прийдется использовать курсор или что-то такое, т.к. Вы не сможете одним
запросом получить количество строк, которое нужно удалить, и их же и удалить
(LIMIT позволяет использовать только числа - подзапрос не пройдет).

Неактивен

 

#6 27.11.2007 03:40:12

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Удаление дубликатов, GROUP BY?

code - по сути и есть примари, я чуть повыше написал об этом.

Вот родилось такое решение, насколько оно отражает чего я хочу? ничего я не упустил?

Код:

DELETE *
FROM t1,
(
  SELECT id, COUNT(id) AS cnt, MAX(date) AS date, MAX(code) AS code // достаем максимумы и количество повторов
  FROM t1
  GROUP BY id
  HAVING cnt>1  // оставляем только те, которые повторяются
) AS t2               // и join-им с исходной таблицей
  WHERE 
      t1.id=t2.id    // по id
  AND 
     (t1.date!=t2.date OR t1.code!=t2.code)  // прибивая все, дата которых не совпадает с max или, если дата совпала то если не совпал code

беспокоит внутренний запрос, тяжеловат наверно для большой таблицы?
ну и наверно надо сделать индексы date, code и id

Неактивен

 

#7 27.11.2007 11:14:06

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Удаление дубликатов, GROUP BY?

Вообще, если запрос исправить (сейчас есть несколько ошибок), то, скорее всего, при таком запросе будет Error using GROUP BY

Alias'ы Вы, конечно, жестко выбираете одноименные с именами столбцов...

Неактивен

 

#8 27.11.2007 12:24:59

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Удаление дубликатов, GROUP BY?

LazY написал:

Вообще, если запрос исправить (сейчас есть несколько ошибок), то, скорее всего, при таком запросе будет Error using GROUP BY

Alias'ы Вы, конечно, жестко выбираете одноименные с именами столбцов...

А в чем ошибки?

Можно конечно чуть покрасивше переписать:

Код:

DELETE FROM t1,
(
  SELECT id, COUNT(id) AS cnt, MAX(date) AS maxdate, MAX(code) AS maxcode
  FROM t1
  GROUP BY id
  HAVING cnt>1
) AS t2               
  WHERE 
      t1.id=t2.id    
  AND 
     (t1.date!=t2.maxdate OR t1.code!=t2.maxcode)

но суть то от этого не изменится... а что не так с group by?

Неактивен

 

#9 27.11.2007 13:44:11

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Удаление дубликатов, GROUP BY?

> А в чем ошибки?
А Вы попробуйте его выполнить smile
вообще не совсем понятна конструкция (SELECT ...) AS t2
во-первых, в таких местах подзапросы нельзя использовать
во-вторых, даже если бы было можно, то он должен был бы быть скалярным; а у Вас табличный

Ошибки с GROUP BY возникают, когда начинаются попытки использовать в условиях WHERE и HAVING непосредственно значения столбцов, отличных от тех, по которым группировка

т.е. не проходят запросы вида
SELECT 1 FROM t1 WHERE code != MAX(code) GROUP BY id
или
SELECT 1 FROM t1 GROUP BY id HAVING code != MAX(code)

иначе Вам точно можно было бы выкрутиться одним запросом.

Неактивен

 

#10 27.11.2007 15:07:01

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Удаление дубликатов, GROUP BY?

LazY написал:

> А в чем ошибки?
А Вы попробуйте его выполнить smile
вообще не совсем понятна конструкция (SELECT ...) AS t2
во-первых, в таких местах подзапросы нельзя использовать
во-вторых, даже если бы было можно, то он должен был бы быть скалярным; а у Вас табличный

Эээ, тут вы не правы, вот ссылка: http://dev.mysql.com/doc/refman/5.1/en/ … views.html

Subqueries in the FROM clause can return a scalar, column, row, or table. Subqueries in the FROM clause cannot be correlated subqueries, unless used within the ON  clause of a JOIN operation.

В моем запросе, подзапрос является по сути самостоятельным запросом, который возвращает таблицу никак не свзанную с внешним запросом, в explain  этот запрос так и обозначается - derrived sub.
Дальше происходит просто JOIN с исходной таблицей и все WHERE реботают уже с этим JOIN, но никакого отношения не имеют к агрегирующим функциям внутри подзапроса.

LazY написал:

Ошибки с GROUP BY возникают, когда начинаются попытки использовать в условиях WHERE и HAVING непосредственно значения столбцов, отличных от тех, по которым группировка

т.е. не проходят запросы вида
SELECT 1 FROM t1 WHERE code != MAX(code) GROUP BY id
или
SELECT 1 FROM t1 GROUP BY id HAVING code != MAX(code)

иначе Вам точно можно было бы выкрутиться одним запросом.

Это понятно, именно поэтому у меня и не получается выкрутится одним запросом smile

Неактивен

 

#11 27.11.2007 15:39:00

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Удаление дубликатов, GROUP BY?

Извиняюсь, что вклинюсь в это обсуждение smile
Если code - primary, то они уникальны и имеет смысл делать просто
DELETE FROM t1 WHERE code NOT IN (SELECT MAX(code) as code FROM t1 GROUP BY id)

Но это так, к слову smile

Неактивен

 

#12 27.11.2007 15:45:51

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Удаление дубликатов, GROUP BY?

"Вот черт" - подумал я сначала, - "как все просто" smile

а потом понял, что всех ввел в заблуждение чуть-чуть неверно написав исходную задачу, и сказал "вот черт" еще раз...

на самом деле задача практически такая же, только я перепутал при написании порядок проверки.

сначала нужно проверить ДАТУ и если она совпадает - то тогда берется наибольший код (т.к. он уникальный - то он всегда будет)

извиняюсь

Неактивен

 

#13 27.11.2007 15:51:30

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Удаление дубликатов, GROUP BY?

DELETE FROM t1 WHERE code NOT IN (SELECT MAX(code) as code FROM t1 GROUP BY `date`)

Или я чего-то не понял? smile

Неактивен

 

#14 27.11.2007 16:02:08

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Удаление дубликатов, GROUP BY?

paulus написал:

DELETE FROM t1 WHERE code NOT IN (SELECT MAX(code) as code FROM t1 GROUP BY `date`)

Так не получится, так как дату имеет смысл сравнивать, только если id совпадает.

Неактивен

 

#15 27.11.2007 16:02:31

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Удаление дубликатов, GROUP BY?

Shopen написал:

Эээ, тут вы не правы, вот ссылка: http://dev.mysql.com/doc/refman/5.1/en/ … views.html

А, Вы вот что имели в виду..
Слишком бегло посмотрел и не понял (думал, Вы хотите из подзапроса имя таблицы получить).
В таком случае Вы правы  smile

Неактивен

 

#16 27.11.2007 16:20:34

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Удаление дубликатов, GROUP BY?

paulus написал:

DELETE FROM t1 WHERE code NOT IN (SELECT MAX(code) as code FROM t1 GROUP BY `date`)

Или я чего-то не понял? smile

Скорее забыл smile

id потерялся. а основная задача удалить дубликаты id smile

Неактивен

 

#17 27.11.2007 16:26:24

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Удаление дубликатов, GROUP BY?

Брр. Сформулируйте еще раз задачу, пожалуйста. Надо найти строчки с одинаковым id,
датой и максимальным кодом? Тогда вот так:

DELETE FROM t1 WHERE code NOT IN (SELECT MAX(code) as code FROM t1 GROUP BY id, `date`)

Напишите, пожалуйста, что есть, и что требуется. Не забудьте "code - ключевое поле" smile

Неактивен

 

#18 27.11.2007 16:31:09

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Удаление дубликатов, GROUP BY?

Как я понял надо для каждого id оставить только строчки с максимальной датой, а среди них - только строчку с максимальным кодом.

Неактивен

 

#19 27.11.2007 16:42:02

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Удаление дубликатов, GROUP BY?

да, правильно...

пример
группируем по id - находим допустим две строки,
если дата у них одинаковая - то смотрим code, т.к. он pk - оставляем больший, вторую строку прибиваем, остается одна строка.
если дата у них разная - то оставляем запись у которой дата больше. вторую прибиваем, на code в этом случае вообще можно не смотреть

Неактивен

 

#20 27.11.2007 17:03:28

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Удаление дубликатов, GROUP BY?

DELETE FROM t1 WHERE code NOT IN (SELECT MAX(code) as code FROM t1 GROUP BY id, `date`)
DELETE FROM t1 tt WHERE `date` NOT IN (SELECT MAX(`date`) FROM t1 WHERE id = tt.id)

Вот так? wink

Неактивен

 

#21 27.11.2007 17:35:41

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Удаление дубликатов, GROUP BY?

это два варианта? Если да то:

paulus написал:

DELETE FROM t1 WHERE code NOT IN (SELECT MAX(code) as code FROM t1 GROUP BY id, `date`)

не катит - не учитывает, что дата должна быть max. По сути вложенный запрос вынет уникальные id,date и вернет максимальный code/

paulus написал:

DELETE FROM t1 tt WHERE `date` NOT IN (SELECT MAX(`date`) FROM t1 WHERE id = tt.id)

Вааще не понял что тут происходит )

Неактивен

 

#22 27.11.2007 19:47:16

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Удаление дубликатов, GROUP BY?

вариант paulus похож на правду - это не два варианта, это 2 последовательных запроса. Правда второй запрос еще медленнее, чем перебор таблицы.

Неактивен

 

#23 27.11.2007 21:23:57

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Удаление дубликатов, GROUP BY?

Это и есть перебор таблицы. Будут работать относительно быстро, если сначала перебрать
таблицу созданием индексов (отдельных) на code, id и date.

Неактивен

 

#24 28.11.2007 01:39:42

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Удаление дубликатов, GROUP BY?

paulus написал:

1. DELETE FROM t1 WHERE code NOT IN (SELECT MAX(code) as code FROM t1 GROUP BY id, `date`)
2. DELETE FROM t1 tt WHERE `date` NOT IN (SELECT MAX(`date`) FROM t1 WHERE id = tt.id)

Вот так? wink

В принципе идею понял. Даже не думал, что можно зайти с другой стороны, спасибо smile

Но, что то мне подсказывает, что работать это будет ооочень медленно.
Во первых строк у меня > 1 000 000
Количество повторов, как я уже говорил менее 2 процентов. Получается что подзапрос в п. 1 по сути вынет всю! таблицу.
Это подсказывает мне и explain (пример на аналогичной маленькой табличке, строк - 16462):

Код:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    PRIMARY    t1    ALL                    16462    Using where
2    DEPENDENT SUBQUERY    t1    index        ix_id_code_date    111        16462    Using index

Видно что перебираться будут все строки хоть и по идексу, но опять же, этот составной индекс нужно создать только для цели этого удаления, потому что более он нигде применятся не будет,  отдельные индексы вообще не используются, так как у них очень низкая релевантность (поля - id и date почти! уникальны, code - pk).
Т.е. как мне кажется вынимать в подзапросе то что НЕ нужно удалять - не очень эффективно в моем случае. И это только первый запрос!
Со вторым запросом скорее всего аналогичная ситуация, так как NOT IN ()

Мой вариант пока выглядит как то пооптимистичнее, даже без составного индекса ix_id_code_date smile)

Код:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    PRIMARY    <derived2>    ALL                    607    ""
1    PRIMARY    t1    ref    ix_id    ix_id    103    t2.id    1    Using where
2    DERIVED    t1    index        code    111        16462    Using index; Using temporary

Отредактированно Shopen (28.11.2007 01:42:08)

Неактивен

 

#25 29.11.2007 18:18:27

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Удаление дубликатов, GROUP BY?

Составной индекс ix_id_code_date содержит в себе всю таблицу, поэтому неясно, будет ли от него польза. Попробуйте сделать дамп базы и на тестовом сервере сравнить производительность нескольких подходов (два указанные Вами и подход через явно создаваемую временную таблицу).

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson