Удалить дубликаты по условию из другой таблицы

[SibBear]

Добрый день. Возникла неожиданно сложная задача. Имеется таблица party:

Код

SQL
id | ic | party_number | ml | .....
 

и дочерняя таблица times:

Код

SQL
id | party_id | o_time
 

В первой таблице необходимо удалить дубликаты, причем сравнивать нужно несколько колонок (ic, party_number, ml). Кроме того, нужно удалить дубликаты так, чтобы остались записи, для которых во второй таблице последнее значение o_time - наибольшее. Например, в данном случае:

Код

SQL
party:
id |   ic  | party_number |ml
123|140UD17|   55         |321
234|140UD17|   55         |321
 
times:
id  |party_id|o_time
567 |  123   |10000
568 |  123   |20000
569 |  123   |30000
...........................
2323|  234   |10000
2324|  234   |20000
2325|  234   |30000
2326|  234   |40000
 

в базе должна остаться запись с id=234, т.к. в таблице times ей соответствует бОльшая величина (40000). Как этого добиться в БД SQLite?

[trot]

Для postgresql это решается так:

delete from party where ic=ic and party_number=party_number and ml=ml
and id in (select party_id from (select distinct on (party_id) party_id, id, o_time from times order by  party_id, o_time desc) as t order by o_time limit 1)

[LisandreL]

Пример базы приложите - так проще будет.

[SibBear]

Пример базы приложите - так проще будет.

Код

SQL
CREATE TABLE party(
id INTEGER PRIMARY KEY AUTOINCREMENT,
ic VARCHAR(50) NOT NULL,
party_number VARCHAR(15),
ml VARCHAR(15)
);
 
CREATE TABLE times(
id INTEGER PRIMARY KEY AUTOINCREMENT,
party_id INTEGER NOT NULL,
o_time INTEGER NOT NULL
);
 

[MoPDoBoPoT]

Вариант для Oracle

Код

SQL
DELETE FROM party
WHERE id IN (WITH view_times AS
             (
              SELECT party_id, MAX(o_time) AS o_time
              FROM times
              GROUP BY party_id
             )
             SELECT p1.id
             FROM party AS p1, party AS p2, view_times AS t1, view_times AS t2
             WHERE p1.id <> p2.id
               AND p1.ic = p2.ic
               AND p1.party_number = p2.party_number
               AND p1.ml = p2.ml
               AND p1.id = t1.party_id
               AND p2.id = t2.party_id
               AND t1.o_time < t2.o_time
            )
/
 
DELETE FROM times WHERE party_id NOT IN (SELECT id FROM party)
/
 

Случай, когда максимальные o_time для дубликатов равны, не рассматривается (для приведенного кода оба не удаляться).

[SibBear]

Вариант для Oracle

Код

SQL
DELETE FROM party
WHERE id IN (WITH view_times AS
 
 

Случай, когда максимальные o_time для дубликатов равны, не рассматривается (для приведенного кода оба не удаляться).

Спасибо, конечно, но можно ли обойтись без WITH, его нет в SQLite?

[LisandreL]

Ну очевидный вариант:

Код

SQL
DELETE FROM [party]
WHERE [id] NOT IN ( 
  SELECT [party].[id] FROM [party] JOIN [times] ON [party].[id] = [times].[party_id] 
  GROUP BY [ic], [ml], [party_number] 
  ORDER BY [o_time] DESC
);
 
DELETE FROM [times]
  WHERE [party_id] NOT IN 
  (
    SELECT [id] FROM [party]
  );

Но на большой базе, тем более без индексов будет работать не быстро.

[SibBear]

Большое спасибо!

[MoPDoBoPoT]

Код

SQL
...
SELECT [party].[id] FROM [party] JOIN [times] ON [party].[id] = [times].[party_id] 
GROUP BY [ic], [ml], [party_number] 
ORDER BY [o_time] DESC
...
 

Этот кусок не соответствует стандарту SQL. При использовании GROUP BY в списке выбора (часть SELECT) должны быть перечислены поля, указанные в GROUP BY, + могут быть агрегатные функции.
Собственно, этот подзапрос неправильно работает, он всё время мне выдаёт party_id=234 (даже если для party_id=123 установить самый большой o_time).

Спасибо, конечно, но можно ли обойтись без WITH, его нет в SQLite?

WITH ... AS указал для оптимизации, намекая на то, что лучше эти данные расчитать заранее (например, занеся их во временную таблицу). А так, в запросе вместо view_times можно подставить подзапрос:

Код

SQL
DELETE FROM party
WHERE id IN (SELECT p1.id
             FROM party AS p1,
                  party AS p2,
                  (SELECT party_id, MAX(o_time) AS o_time
                   FROM times
                   GROUP BY party_id) AS t1,
                  (SELECT party_id, MAX(o_time) AS o_time
                   FROM times
                   GROUP BY party_id) AS t2
             WHERE p1.id <> p2.id
               AND p1.ic = p2.ic
               AND p1.party_number = p2.party_number
               AND p1.ml = p2.ml
               AND p1.id = t1.party_id
               AND p2.id = t2.party_id
               AND t1.o_time < t2.o_time
            )
 

[SibBear]

Спасибо. Довольно медленно, но работает.