Pull to refresh

Comments 11

UFO landed and left these words here
Тут речь конкретно про 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 будут нужны для эффективного удаления.
Не влияет примерно никак, разве что чуть дольше:
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

Это очень похоже на один из вопросов, которые я всегда задаю на собеседованиях на уровень 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).

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


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

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

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

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

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


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

Only those users with full accounts are able to leave comments. Log in, please.