Блог

Главная - Вопросы по программированию - PostgreSQL как найти дубликаты по агрегированному значению

PostgreSQL как найти дубликаты по агрегированному значению

#sql #postgresql

Вопрос:

У меня есть два столика. Скриншоты со следующей структурой:

  1. идентификатор (bigint)
  2. Оригинальное имя файла (строка)
  3. Дата создания (Дата)

И второе-это подписи на скриншотах:

  1. Скриншот (внешний ключ для скриншотов)
  2. подпись (бигинт)

Таким образом, таблица скриншотов имеет отношение один ко многим. Так что теперь мне нужно найти все дубликаты. Это означает, что скриншот имеет точно такие же подписи. Я попытался сделать следующее:

 SELECT os.id, os."originalFilename", array_agg(signature) as signatures from "development".screenshots os
left join development."screenshotSignatures" s on os.id = s."screenshotId"
WHERE GROUP BY os.id, os."originalFilename" ;

и из этого запроса я получаю следующий результат: введите описание изображения здесь

Так что теперь мне нужно как-то найти дубликаты, но я не знаю, как это сделать. Я подумал, что могу сделать это как-нибудь из таблицы скриншотов. В результате будет приятно иметь что-то вроде: скриншоты (a,b,c) имеют одинаковый набор подписей. Позже мне нужно будет удалить самые старые скриншоты. Есть какие-нибудь идеи? Спасибо

Ответ №1:

Просто используйте HAVING предложение:

 WITH signature_list AS (
  SELECT os.id, os."originalFilename", array_agg(signature ORDER BY signature) as signatures 
  from "development".screenshots os
  left join development."screenshotSignatures" s on os.id = s."screenshotId"
  GROUP BY os.id, os."originalFilename"
)
SELECT signatures, array_agg(id) as duplicate_ids, array_agg(originalFilename) as duplicate_filenames
FROM signature_list 
GROUP BY signatures
HAVING COUNT(*) > 1

Комментарии:

1. Спасибо за ваш ответ, но в нем говорится: Не удается разрешить подписи столбцов в предложении HAVING. Я сделал что-то не так?

2. Хорошо, давайте сначала соберем подписи для каждого имени файла, а затем найдем дубликаты подписей.

3. Звучит круто, но как это сделать? Не могли бы вы предоставить какой-нибудь код?

4. Я обновил свой ответ — он выполняет агрегацию внутри CTE, а затем использует HAVING предложение.

5. Спасибо тебе, парень! Ты мой герой!

Метки: