Удаление повторяющихся значений в базе данных

Ответ 1

Это второй ответ, который я представил для этого вопроса, но я думаю, что, наконец, получил его на этот раз:

DELETE FROM products WHERE id IN (
    SELECT id_to_delete
    FROM (
        SELECT
            t0.id AS id_to_delete,
            t0.price,
            (
                SELECT t1.price
                FROM products AS t1
                WHERE (t0.date < t1.date)
                    AND (t0.name = t1.name)
                ORDER BY t1.date ASC
                LIMIT 1
            ) AS next_price,
            (
                SELECT t2.price
                FROM products AS t2
                WHERE (t0.date > t2.date)
                    AND (t0.name = t2.name)
                ORDER BY t2.date DESC
                LIMIT 1
            ) AS prev_price
        FROM products AS t0
        HAVING (price = next_price) AND (price = prev_price)
    ) AS t
)

Это измененная версия ответа от @vadim_hr.

Изменить: Ниже представлен другой запрос, который фильтрует на JOIN вместо подзапроса. JOIN может быть быстрее предыдущего запроса (см. выше) для больших наборов данных, но я оставлю тестирование производительности до вас.

http://sqlfiddle.com/#!9/ee0655/8

SELECT M.id as id_to_delete
FROM
(
    SELECT
        *,
        (@j := @j + 1) AS j
    FROM
    (SELECT * FROM products ORDER BY name ASC, date ASC) AS mmm
    JOIN
    (SELECT @j := 1) AS mm
) AS M     -- the middle table
JOIN
(
    SELECT
        *,
        (@i := @i + 1) AS i
    FROM
    (SELECT * FROM products ORDER BY name ASC, date ASC) AS lll
    JOIN
    (SELECT @i := 0) AS ll
) AS L     -- the left table
ON M.j = L.i
    AND M.name = L.name
    AND M.price = L.price
JOIN
(
    SELECT
        *,
        (@k := @k + 1) AS k
    FROM
    (SELECT * FROM products ORDER BY name ASC, date ASC) AS rrr
    JOIN
    (SELECT @k := 2) AS rr
) AS R     -- the right table
ON M.j = R.k
    AND M.name = R.name
    AND M.price = R.price

Оба запроса выполняют один и тот же конец, и оба они предполагают, что строки уникальны для name и date (как объяснено в комментарии ниже).

Ответ 2

Вы можете сделать это с помощью некоторой логики самостоятельного объединения.

Подумайте о трех гипотетических строках в таблице.

• Строка, которую вы хотите сохранить.
• Строка b имеет одно и то же имя и цену продукта, а также дату через день после a. Вы хотите удалить это.
• Строка c имеет то же имя и цену продукта, а также дату через 1 день после b. Вы хотите сохранить это.

Итак, если вы можете сделать самосоединение для соответствия этим трем строкам, тогда удалите строку b.

DELETE b FROM MyTable AS a 
JOIN MyTable AS b ON a.name=b.name AND a.price=b.price AND a.date=b.date + INTERVAL 1 DAY 
JOIN MyTable AS c ON b.name=c.name AND b.price=c.price AND b.date=c.date + INTERVAL 1 DAY;

Это работает, даже если есть несколько строк, которые соответствуют условиям для строки b. Он удалит первый, а затем продолжит удаление последующих строк, которые также соответствуют условиям.

Это работает, если вы используете тип данных DATE и сохраняете свои даты как "ГГГГ-ММ-ДД", а не "ДД-ММ-ГГГГ". Вы все равно должны это делать.

Ответ 3

Вы хотите удалить строки, в которых имя и цена продукта совпадают с строками с датой плюс/минус один день.

DELETE row_mid
FROM 
  record_table AS row_mid
  JOIN record_table AS row_prev
  JOIN record_table AS row_next
WHERE
  row_mid.name = row_prev.name 
  AND row_mid.price = row_prev.price
  AND row_mid.date = DATE_SUB(row_prev.date, INTERVAL 1 DAY)
  AND row_mid.name = row_next.name
  AND row_mid.price = row_next.price
  AND row_mid.date = DATE_ADD(row_next.date, INTERVAL 1 DAY);

Ответ 4

Является ли ваш MySQL достаточно новым для поддержки CTE? Это довольно интересная проблема, которую я видел с планированием даты. Код всегда выглядит неудобно. Чтобы проверить результаты без удаления, вы можете переключить знак комментария с помощью select и delete и прокомментировать t. [Name] - это нулевая строка.

WITH

cte AS  (
        SELECT a.ID
            , a.[Name]
            , a.[Date]
            , a.Price
            , NextDate = max(npc.[Date])    -- Next Price change
            , PrevDate = max(lpc.[Date])    -- Next Price change
        FROM    yourTable as a  -- Base Table
            LEFT JOIN
                yourTable as npc    -- Looking for Next Price Change
            ON a.[Name] = npc.[Name]
                and a.[Date] < npc.[Date]
                and a.Price <> npc.Price
            LEFT JOIN
                yourTable as lpc    -- Looking for Last Price Change
            ON a.[Name] = lpc.[Name]
                and a.[Date] > lpc.[Date]
                and a.Price <> lpc.Price
        GROUP BY a.ID, a.[Name], a.[Date], a.Price
    ) 

----SELECT f.*, [Check] = CASE WHEN t.[Name] is null THEN 'DELETE' ELSE '' END
DELETE f
FROM 
        yourTable as f
    LEFT JOIN
        (
            SELECT [Name], [GoodDate] = Max([Date])
            FROM cte
            GROUP BY [Name], PrevDate
            UNION
            SELECT [Name], [GoodDate] = Min([Date])
            FROM cte
            GROUP BY [Name], PrevDate
            UNION
            SELECT [Name], [GoodDate] = Max([Date])
            FROM cte
            GROUP BY [Name], NextDate
            UNION
            SELECT [Name], [GoodDate] = Min([Date])
            FROM cte
            GROUP BY [Name], NextDate
        ) as t
    ON t.[Name] = f.[Name] and t.[GoodDate] = f.[Date]
WHERE t.[Name] is null
--ORDER BY f.[Name], f.[Date]

Ответ 5

Вы можете обнаружить prev Id и next Id, затем выбрать строки для удаления:

SELECT * 
FROM 
  (SELECT 
      *,
      (SELECT next_id.id 
       FROM a next_id 
       WHERE next_id.id > current.id 
       ORDER BY next_id.id ASC LIMIT 1) as next_id,
      (SELECT prev_id.id 
       FROM a prev_id 
       WHERE prev_id.id < current.id 
       ORDER BY prev_id.id DESC LIMIT 1) as prev_id 
   FROM a current) t
WHERE 
   EXISTS (SELECT 1 
           FROM a next 
           WHERE next.name = t.name AND t.price = next.price AND next.id=t.next_id) 
   AND
   EXISTS (SELECT 1 
           FROM a prev 
           WHERE prev.name = t.name AND t.price = prev.price AND prev.id=t.prev_id)

Я тестировал эти запросы на обоих ваших примерах. Демо.

UPDATE. Если столбец Id не является уникальным, тогда логика должна быть исправлена ​​с prev Id + next Id до prev Date + next Date. В любом случае общая концепция останется прежней. Запрос будет выглядеть следующим образом:

SELECT * 
FROM 
  (SELECT 
      *,
      (SELECT next_date.date 
       FROM a next_date 
       WHERE next_date.date > current.date AND next_date.name = current.name
       ORDER BY next_date.date ASC LIMIT 1) as next_date,
      (SELECT prev_date.date
       FROM a prev_date 
       WHERE prev_date.date < current.date AND prev_date.name = current.name
       ORDER BY prev_date.date DESC LIMIT 1) as prev_date
   FROM a current) t
WHERE 
   EXISTS (SELECT 1 
           FROM a next 
           WHERE next.name = t.name AND t.price = next.price AND next.date=t.next_date) 
   AND
   EXISTS (SELECT 1 
           FROM a prev 
           WHERE prev.name = t.name AND t.price = prev.price AND prev.date=t.prev_date)

Демо для второго запроса.

Ответ 6

все ваши данные повторяются, ведь вы хотите сохранить? ваше объяснение сбивает с толку.

вы можете сохранить самые старые данные с одинаковой ценой и удалить другое:

with Ranked as (
select  name, price, date,
    dense_rank() 
    over (partition by name, price, date 
order by date desc) as DupeCount
from    Your_table P
)
delete  R
from    Ranked R
where   R.DupeCount <> 1

Ответ 7

Ну, я не могу написать точный код для вашего сценария, но вы можете написать Function\Procedure и следовать этому псевдокоду

r = allrows
tobeDeleted = []
unique = []
for (var i=0;i<rows.length; i++){
    unique.push(rows[i]->id);
    dd = true;
    while (dd){
        if ((rows[i]->price == rows[i+1]->price) AND (rows[i]->name == rows[i+1]->price)){
            tobeDeleted.push(rows[i]->id);
            i++;
        }else{
            dd= false;
        }
    }
}

//tobeDeleted contains ids of rows to be deleted
//

Ответ 8

Попробуйте выполнить следующий запрос, надеясь, что он вам поможет.

(у меня нет mysql, я попытался преобразовать синтаксис в свой sql - так что я сожалею о любой синтаксической ошибке.)

(я тестировал его на sqlserver со случайными датами и разными продуктами, он хорошо работает и получает желаемый результат)

/* get the data grouped by name with NewField continousDate to create continous dates for every product depends on the order of date
then save it to temporary table called tempWithContinousDate*/

CREATE TEMPORARY Table tempWithContinousDate Table  (id INT,name varchar(50),price DECIMAL(12,2),date DATE,continousDate DATE)

insert into tempWithContinousDate(id,name,price,date,continousDate)
select id,name,price,date,Date_Add(minimumDate,INTERVAL rn DAY)ContinousDate
from(
select t1.id,t1.name,t1.price,t1.date,min(t2.Date)minimumDate,count(*) rn
          from 
             (select id,name,price,date from yourTable) t1
          inner join 
            (select id,name,price,date from yourTable) t2 
          on t1.name=t2.name and t1.date>=t2.date
 group by t1.id,t1.name,t1.price,t1.date
 ) t




/* get the data grouped by name and price with NewField GroupDate to group every continous dates 
then save it to temporary table called tempData*/
CREATE TEMPORARY Table tempData (id INT,name varchar(50),price DECIMAL(12,2),date DATE,groupDate DATE)

insert into tempData(id,name,price,date,groupDate)
select id,name,price,date,DATE_SUB(continousDate, INTERVAL rowNumber DAY) groupDate
from(
select t1.id,t1.name,t1.price,t1.date,t1.continousDate,count(*) rowNumber
          from 
             (select id,name,price,date,continousDate from tempWithContinousDate) t1
          inner join 
            (select id,name,price,date,continousDate from tempWithContinousDate) t2 
          on t1.name=t2.name and t1.price=t2.price and t1.date>=t2.date
 group by t1.id,t1.name,t1.price,t1.date,t1.continousDate
 ) t



 /*select * from yourTable where id  in*/
 delete from yourTable where id not in
(select id from 
 (

/* query to order every continous data asscending using the date field */
select firstData.id,firstData.name,firstData.price,firstData.date,count(*) rn 
from  tempData firstData
left join  tempData secondData
on firstData.name=secondData.name and firstData.price=secondData.price and firstData.groupDate=secondData.groupDate
and firstData.date>=secondData.date
group by firstData.id,firstData.name,firstData.price,firstData.date


/* query to order every continous data  Descending using the date field */
union all
select firstData.id,firstData.name,firstData.price,firstData.date,count(*) rn 
from  tempData firstData
left join  tempData secondData
on firstData.name=secondData.name and firstData.price=secondData.price and firstData.groupDate=secondData.groupDate
and firstData.date<=secondData.date
group by firstData.id,firstData.name,firstData.price,firstData.date

 )allData where rn=1  

)       

Ответ 9

Вы можете использовать ниже фрагмент кода. Дайте мне знать, если он работает.

DELETE FROM record_table
WHERE id NOT IN (
    (SELECT MIN(id) FROM record_table GROUP BY name, price),
    (SELECT MAX(id) FROM record_table GROUP BY name, price)
)

Ответ 10

Вы можете использовать EXISTS

DELETE FROM test t1
WHERE EXISTS
(
  SELECT * 
  FROM test t2 
  WHERE t1.name = t2.name AND t1.price = t2.price AND t1.day = DATE_SUB(t2.DAY, INTERVAL 1 DAY)
) AND
EXISTS(
  SELECT * 
  FROM test t3 
  WHERE t1.name = t3.name AND t1.price = t3.price AND t1.day = DATE_ADD(t3.DAY, INTERVAL 1 DAY)
)

или IN для решения вашей проблемы

DELETE FROM test t1
WHERE t1.day IN (
  SELECT DATE_SUB(t2.day, INTERVAL 1 DAY)
  FROM test t2 
  WHERE t1.NAME = t2.NAME AND t1.price = t2.price
) AND t1.day IN (
  SELECT DATE_ADD(t3.day, INTERVAL 1 DAY) 
  FROM test t3 
  WHERE t1.NAME = t3.NAME AND t1.price = t3.price
)

sqlfiddle demo

Ответ 11

Вы можете использовать следующую логику:

• рейтинг по цене
• группа по id, name, price
• получить минимальную дату
• получить максимальную дату

Следуя примеру запроса и скрипта:

SET @prev_value = NULL;
SET @rank_count = 0;

select distinct
  `name`,
  `price`,
  `date`
from 
(
  (
  select 
    id,
    name,
    price,
    CASE
      WHEN @prev_value = price THEN @rank_count
      WHEN @prev_value := price THEN @rank_count := @rank_count + 1
    END AS rank,
    min(`date`) as `date`
  from 
    `prices`
   group by 
     `name`, 
     `price`, 
     `rank`
   )
   union distinct
   (
   select 
    id,
    name,
    price,
    CASE
      WHEN @prev_value = price THEN @rank_count
      WHEN @prev_value := price THEN @rank_count := @rank_count + 1
    END AS rank,
    max(`date`) as `date`
  from 
    `prices`
   group by 
     `name`, 
     `price`, 
     `rank`
  )
  order by `id`, `date`
) as `result`

sqlfiddle

Ответ 12

Мы должны спросить себя, когда мы должны удалить запись?

Ответ: Запись может быть удалена,

• если существует другая запись с тем же именем с той же ценой и более ранняя дата, в то время как нет записи с тем же именем, с другой ценой между обеими датами.

  и

• если существует другая запись с тем же именем с той же ценой и более поздняя дата, в то время как нет записи с тем же именем, с другой ценой между обеими датами.

Включение обоих требований в SQL приводит к следующему:

DELETE FROM PriceTable t
WHERE 
  EXISTS ( SELECT *
           FROM PriceTable tmp1 
           WHERE t.name  = tmp1.name  AND 
                 t.price = tmp1.price AND 
                 t.date  > tmp1.date  AND
                 NOT EXISTS (SELECT * 
                             FROM PriceTable tmp2
                             WHERE t.name    = tmp2.name  AND 
                                   t.price  != tmp2.price AND 
                                   t.date    > tmp2.date  AND 
                                   tmp1.date < tmp2.date 
                            )
         )
  AND
  EXISTS ( SELECT *
           FROM PriceTable tmp1 
           WHERE t.name  = tmp1.name  AND 
                 t.price = tmp1.price AND 
                 t.date  < tmp1.date  AND
                 NOT EXISTS (SELECT * 
                             FROM PriceTable tmp2
                             WHERE t.name    = tmp2.name  AND 
                                   t.price  != tmp2.price AND 
                                   t.date    < tmp2.date  AND 
                                   tmp1.date > tmp2.date 
                            ) 
         );

Ответ 13

Изменить: после дальнейшего рассмотрения представляется невозможным решить эту проблему с помощью пользовательской переменной trickery (обратите внимание на другие решения, используя их). Хотя я думаю, что приведенное ниже решение будет "скорее всего работать в 99% случаев", MySQL не гарантирует порядок оценки переменных: ссылка 1 и ссылка 2.

Оригинальный ответ:

(Я работаю в соответствии с предположениями, что products.name определяется как NOT NULL, и оба products.id и products.price не являются отрицательными [могут также содержать простой патч, если обрабатывают негативы]).

Запрос:

SET
    @one_prior_id := NULL,
    @one_prior_price := NULL,
    @one_prior_name := NULL,
    @two_prior_id := NULL,
    @two_prior_price := NULL,
    @two_prior_name := NULL
;

SELECT @two_prior_id AS id_to_delete
FROM (
    SELECT *
    FROM products
    ORDER BY name, date
) AS t
WHERE IF(
    (
        (name  = @one_prior_name)
        AND
        (name  = @two_prior_name)
        AND
        (price = @one_prior_price)
        AND
        (price = @two_prior_price)
    ), (
        GREATEST(
            1,
            IFNULL(@two_prior_id := @one_prior_id, 0),
            IFNULL(@two_prior_price := @one_prior_price, 0),
            LENGTH(IFNULL(@two_prior_name := @one_prior_name, 0)),
            IFNULL(@one_prior_id := id, 0),
            IFNULL(@one_prior_price := price, 0),
            LENGTH(IFNULL(@one_prior_name := name, 0))
        )
    ), (
        LEAST(
            0,
            IFNULL(@two_prior_id := @one_prior_id, 0),
            IFNULL(@two_prior_price := @one_prior_price, 0),
            LENGTH(IFNULL(@two_prior_name := @one_prior_name, 0)),
            IFNULL(@one_prior_id := id, 0),
            IFNULL(@one_prior_price := price, 0),
            LENGTH(IFNULL(@one_prior_name := name, 0))
        )
    )
)

Возврат запроса, основанный на вашем примере "Пример 1:"

+--------------+
| id_to_delete |
+--------------+
|            2 |
|            6 |
+--------------+

Возврат запроса, основанный на вашем примере "Пример 2:"

+--------------+
| id_to_delete |
+--------------+
|            2 |
|            3 |
|            4 |
|            5 |
|            6 |
+--------------+

Как работает запрос:

• Сделайте простое "разбиение" таблицы products с помощью ORDER BY

• Прокрутите упорядоченный набор результатов, отслеживая 2 набора переменных: 1-й набор для хранения цены и имени строки "один предыдущий" (строка "один предыдущий" находится непосредственно над текущей строкой) и второй набор переменных для хранения строки "два предшествующих" (строка "два предшествующих" находится непосредственно над строкой "один предыдущий" ).

• GREATEST и LEAST идентичны, за исключением того, что первое возвращает значение, которое будет оцениваться как true для IF, и последнее будет оцениваться как false. Реальной точкой этих функций является обновление наших переменных цикла.

• Подробнее о обновлении переменных в подзапросах см. .

Фактический DELETE:

SET
    @one_prior_id := NULL,
    @one_prior_price := NULL,
    @one_prior_name := NULL,
    @two_prior_id := NULL,
    @two_prior_price := NULL,
    @two_prior_name := NULL
;

DELETE FROM products WHERE id IN (
    SELECT * FROM (
        SELECT @two_prior_id AS id_to_delete
        FROM (
            SELECT *
            FROM products
            ORDER BY name, date
        ) AS t1
        WHERE IF(
            (
                (name  = @one_prior_name)
                AND
                (name  = @two_prior_name)
                AND
                (price = @one_prior_price)
                AND
                (price = @two_prior_price)
            ), (
                GREATEST(
                    1,
                    IFNULL(@two_prior_id := @one_prior_id, 0),
                    IFNULL(@two_prior_price := @one_prior_price, 0),
                    LENGTH(IFNULL(@two_prior_name := @one_prior_name, 0)),
                    IFNULL(@one_prior_id := id, 0),
                    IFNULL(@one_prior_price := price, 0),
                    LENGTH(IFNULL(@one_prior_name := name, 0))
                )
            ), (
                LEAST(
                    0,
                    IFNULL(@two_prior_id := @one_prior_id, 0),
                    IFNULL(@two_prior_price := @one_prior_price, 0),
                    LENGTH(IFNULL(@two_prior_name := @one_prior_name, 0)),
                    IFNULL(@one_prior_id := id, 0),
                    IFNULL(@one_prior_price := price, 0),
                    LENGTH(IFNULL(@one_prior_name := name, 0))
                )
            )
        )
    ) AS t2
)

Важное примечание

Посмотрите, как приведенный выше запрос на удаление выполняет 2 внутренних выбора? Убедитесь, что вы включили это, иначе вы случайно удалите последнюю строку! Попробуйте выполнить без SELECT (...) AS t2, чтобы понять, что я имею в виду.