SQL: Найти дубликаты в таблице

Решение: Поиск дубликатов в таблице

SQL-запрос (базовый вариант)

SELECT
  email,
  COUNT(*) as duplicates_count,
  ARRAY_AGG(id ORDER BY id) as user_ids
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicates_count DESC;

Объяснение

Ключевые элементы:

1. GROUP BY email — группируем все записи с одинаковым email

2. COUNT(*) as duplicates_count — считаем, сколько раз встречается каждый email

3. ARRAY_AGG(id ORDER BY id) — собираем все id в массив и сортируем их

   • Работает в PostgreSQL
   • Результат: [1, 5, 12, 18]

4. HAVING COUNT(*) > 1 — показываем только email, которые повторяются 2+ раза

   • WHERE работает на строках, HAVING — на результатах GROUP BY

5. ORDER BY duplicates_count DESC — сортируем по количеству дубликатов (больше всего сначала)

Пример вывода

email              | duplicates_count | user_ids
-------------------|------------------|----------
john@example.com   | 3                | {1,5,12}
marketing@co.ru    | 2                | {8,15}
sales@shop.com     | 2                | {20,33}

Интерпретация: john@example.com есть у трёх пользователей с id 1, 5, 12.

Альтернативы для разных БД

PostgreSQL (с ARRAY_AGG)

SELECT
  email,
  COUNT(*) as duplicates_count,
  ARRAY_AGG(id ORDER BY id) as user_ids
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicates_count DESC;

MySQL (с GROUP_CONCAT)

SELECT
  email,
  COUNT(*) as duplicates_count,
  GROUP_CONCAT(id ORDER BY id SEPARATOR ',') as user_ids
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicates_count DESC;

Результат: user_ids будет строкой "1,5,12"

SQL Server (с STRING_AGG)

SELECT
  email,
  COUNT(*) as duplicates_count,
  STRING_AGG(CAST(id AS VARCHAR), ',') WITHIN GROUP (ORDER BY id) as user_ids
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicates_count DESC;

Более подробный анализ дубликатов

SELECT
  email,
  COUNT(*) as duplicates_count,
  ARRAY_AGG(id ORDER BY created_at) as user_ids_by_creation,
  ARRAY_AGG(name ORDER BY id) as names,
  ARRAY_AGG(created_at ORDER BY created_at) as creation_dates,
  MIN(created_at) as first_created,
  MAX(created_at) as last_created,
  AGE(MAX(created_at), MIN(created_at)) as time_between
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicates_count DESC;

Это поможет понять:

• Порядок создания дубликатов
• Какие имена используются для одного email
• Сколько времени прошло между первым и последним дубликатом

Вариант с самих пользователей с дубликатами

WITH duplicates AS (
  SELECT email
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
)
SELECT
  u.id,
  u.email,
  u.name,
  u.created_at,
  ROW_NUMBER() OVER (PARTITION BY u.email ORDER BY u.created_at) as duplicate_number
FROM users u
JOIN duplicates d ON u.email = d.email
ORDER BY u.email, u.created_at;

Результат:

id  | email              | name    | created_at          | duplicate_number
----|-------------------|---------|---------------------|------------------
1   | john@example.com   | John    | 2024-01-10 10:00:00 | 1
5   | john@example.com   | Jon     | 2024-02-15 14:30:00 | 2
12  | john@example.com   | Johnny  | 2024-03-20 09:15:00 | 3

Это показывает, что это дубликат 1, дубликат 2, дубликат 3.

Поиск дубликатов по нескольким полям

Если нужны дубликаты по email И name:

SELECT
  email,
  name,
  COUNT(*) as duplicates_count,
  ARRAY_AGG(id) as user_ids
FROM users
GROUP BY email, name
HAVING COUNT(*) > 1
ORDER BY duplicates_count DESC;

Экспорт дубликатов для очистки

-- Определить какой id оставить (например, самый старый)
WITH duplicates_ranked AS (
  SELECT
    id,
    email,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) as rn
  FROM users
),
duplicates_to_delete AS (
  SELECT id
  FROM duplicates_ranked
  WHERE rn > 1  -- Оставляем первый (rn=1), удаляем остальные
)
SELECT *
FROM duplicates_to_delete
ORDER BY id;

Результат: список id, которые нужно удалить.

Вариант с мержингом дубликатов

WITH find_master AS (
  -- Для каждого email находим самый старый id (master)
  SELECT
    email,
    MIN(id) as master_id
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
),
duplicates AS (
  SELECT
    u.id,
    u.email,
    fm.master_id
  FROM users u
  JOIN find_master fm ON u.email = fm.email
  WHERE u.id != fm.master_id
)
SELECT
  email,
  COUNT(*) as duplicates_count,
  master_id,
  ARRAY_AGG(id) as ids_to_merge_into_master
FROM duplicates
GROUP BY email, master_id
ORDER BY email;

Это для понимания, какие id нужно объединить в один основной.

Проверка на дубликаты перед вставкой

-- Constraint для уникальности email
ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

-- Или с индексом (允許 NULL дубликаты, если это нужно)
CREATE UNIQUE INDEX idx_users_email_not_null 
ON users(email) 
WHERE email IS NOT NULL;

Это предотвратит создание дубликатов в будущем.