DBA: вычищаем клон-записи из таблицы без PK

[bogdan_uman]

Спасибо. Интересный материал

[Kilor]

Тут речь конкретно про PostgreSQL.
А как «одним запросом»? Группировку на оконные функции заменить можно примерно вот так, только получится дольше в работе:

SELECT DISTINCT ON(T::text)
  (array_agg(ctid) OVER(PARTITION BY T::text))[2:]
FROM
  tbl T

А если использовать row_number() OVER(PARTITION BY T::text) > 1, то все равно нам ctid будут нужны для эффективного удаления.

[lavv17]

а having count(*)>1 не ускорит запрос?

[Kilor]

Не влияет примерно никак, разве что чуть дольше:
explain.tensor.ru/archive/explain/824ccea5d7698fdd687f9dc9b619b827:0:2019-12-24#explain
Потому что в узле группировки теперь фильтрация добавилась:

->  HashAggregate (actual time=9.520..10.581 rows=2370 loops=1)
      Group Key: (t.*)::text
      Filter: (count(*) > 1)
      Rows Removed by Filter: 251
      Buffers: shared hit=45

[oxff]

Это очень похоже на один из вопросов, которые я всегда задаю на собеседованиях на уровень Junior Software Developer. Кандидат обязан знать основы языка SQL.

Примерная формулировка: есть таблица "t" с полями id, a, b, c. Нужно удалить записи с дублями по комбинации (a,b), оставив самые ранние записи. Для простоты предполагается, что id это суррогатный ключ, наполняемый из сиквенса, и его значения всегда растут.

Ответ:
delete from t where id not in (select min(id) from t group by a, b).

[dss_kalika]

;with tmp as (
  SELECT
     *
    ,ROW_NUMBER() OVER (PARTITION BY a,b ORDER BY ID) AS RN
  FROM t
)
DELETE 
FROM tmp 
WHERE RN > 1

А так пойдёт?

[Kilor]

Удалять из CTE?..

[oxff]

1) Из CTE действительно нельзя ничего удалять, то есть придется переписать ваш запрос так, чтобы удаление происходило из «t», что в итоге сведется к моему варианту, с тем лишь отличием, что мы будем использовать оконную функцию вместо «group by».

2) Этот вопрос предназначен для джунов и студентов-интернов, которые как правило даже и не слыхали об оконных функциях. А если и слыхали, то как правило, у них есть лишь небольшой опыт с MySQL, в котором отсутствует ROW_NUMBER().

3) Если сравнить планы выполнения, то окажется что вариант с простой группировкой намного проще и работает намного быстрее по сравнению с оконной функцией. При условии, что в таблице несколько миллионов записей, с оконной функцией вы просто устанете ждать окончания, не говоря о потраченных впустую ресурсах. А с простой группировкой это сводится к двум seq scan, и работает очень быстро.

[dss_kalika]

1. Прошу прощения, это был MS SQL. Там вполне можно и этот запрос выполнился бы.
2. Ну, возможно у меня джуны были немного поопытнее — они обычно или ничего вообще не знали или CTE хотя бы на таком уровне умели. В частности, это очень частая задача: удаление полных дублей строк.
3. В данном случае план показывает что мой вариант быстрее (ну, там тоже могут быть сюрпризы, конечно). Но это, опять же, потому что я в топик не с тем SQL влез, очевидно. =)

Ещё раз, прошу пощения, что смутил всех )

[oxff]

Вау, и правда, MSSQL так умеет делать, я погуглил. Спасибо за информацию )

Остальные основные игроки так не умеют. Postgres (топик о нём) до последней 12 версии всегда материализует результаты запроса в CTE, то есть удалять что-то из такой "как бы временной таблицы" в принципе бесполезная идея. Oracle и MySQL тоже этого не разрешают.