## Контекст (таблицы и пример данных) ### Таблицы - `users(id, full_name, email, city, age, registe
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `users(id, full_name, email, city, age, registered_at)`
- `orders(id, user_id, order_date, amount, status)`
- `payments(id, order_id, payment_date, payment_type, paid_amount)`
### Пример данных
**users**
| id | full_name | email | city | age | registered_at |
|---:|----------------|---------------------|----------|----:|---------------|
| 1 | Ivan Petrov | ivan@mail.com | Helsinki | 29 | 2025-01-10 |
| 2 | Anna Ivanova | anna@gmail.com | Espoo | 34 | 2025-02-01 |
| 3 | Ivan Petrov 2 | ivan@mail.com | Vantaa | 41 | 2025-02-05 |
**orders**
| id | user_id | order_date | amount | status |
|----:|--------:|-------------|--------:|--------|
| 10 | 1 | 2025-03-01 | 120.00 | paid |
| 11 | 2 | 2025-03-02 | 2500.00 | new |
| 12 | 1 | 2025-03-05 | 80.00 | paid |
**payments**
| id | order_id | payment_date | payment_type | paid_amount |
|----:|---------:|-------------|--------------|------------:|
| 100 | 10 | 2025-03-01 | card | 120.00 |
| 101 | 11 | 2025-03-03 | transfer | 1000.00 |
| 102 | 11 | 2025-03-04 | transfer | 1500.00 |
---
## Задача
Удалить дубликаты пользователей по `email`, оставив запись с минимальным `id`.
Ответы
```sql
DELETE FROM users u
USING users u2
WHERE u.email = u2.email
AND u.id > u2.id;
```
Есть несколько альтернативных подходов для удаления дубликатов по email, каждый со своими преимуществами. Вот основные варианты:
1. Использование подзапроса (CTE или вложенный запрос)
С CTE (Common Table Expression):
```sql
WITH duplicates AS (
SELECT id,
email,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
FROM users
)
DELETE FROM users
WHERE id IN (
SELECT id
FROM duplicates
WHERE rn > 1
);
```
Без CTE:
```sql
DELETE FROM users
WHERE id IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
FROM users
) t
WHERE rn > 1
);
```
2. Использование EXISTS
```sql
DELETE FROM users u1
WHERE EXISTS (
SELECT 1
FROM users u2
WHERE u2.email = u1.email
AND u2.id < u1.id
);
```
3. DELETE с использованием RANK/DENSE_RANK
```sql
DELETE FROM users
WHERE id IN (
SELECT id
FROM (
SELECT id,
DENSE_RANK() OVER (PARTITION BY email ORDER BY id) as rank_num
FROM users
) ranked
WHERE rank_num > 1
);
```
4. Создание временной таблицы (надежный вариант для больших таблиц)
```sql
-- Шаг 1: Создаем временную таблицу с уникальными записями
CREATE TABLE users_temp AS
SELECT DISTINCT ON (email) *
FROM users
ORDER BY email, id; -- или другой критерий, какой оставить
-- Шаг 2: Удаляем старую таблицу
DROP TABLE users;
-- Шаг 3: Переименовываем временную таблицу
ALTER TABLE users_temp RENAME TO users;
-- Шаг 4: Создаем индекс на email для предотвращения будущих дубликатов
CREATE UNIQUE INDEX idx_unique_email ON users(email);
```
5. С использованием MIN()/MAX() для сохранения определенной записи
Сохраняем запись с минимальным ID:
```sql
DELETE FROM users
WHERE id NOT IN (
SELECT MIN(id)
FROM users
GROUP BY email
);
```
Сохраняем запись с максимальным ID:
```sql
DELETE FROM users
WHERE id NOT IN (
SELECT MAX(id)
FROM users
GROUP BY email
);
```
6. Безопасный подход с проверкой (для PostgreSQL)
```sql
-- Сначала проверяем, что удалится
SELECT u1.*
FROM users u1
JOIN users u2 ON u1.email = u2.email AND u1.id > u2.id;
-- Если результат правильный, выполняем удаление
DELETE FROM users u1
USING users u2
WHERE u1.email = u2.email
AND u1.id > u2.id;
```
7. Для MySQL (синтаксис отличается)
```sql
-- В MySQL нельзя ссылаться на удаляемую таблицу в подзапросе
DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.email = u2.email
AND u1.id > u2.id;
```
Сравнение подходов:
Метод Преимущества Недостатки
Ваш оригинальный (USING) Лаконичный, хорошая производительность Только для PostgreSQL, может быть неочевидным
CTE с ROW_NUMBER() Четкий контроль (оставляем первую/последнюю запись) Требует PostgreSQL 9.1+ или аналоги
EXISTS Стандартный SQL, понятная логика Может быть медленнее на больших объемах
MIN()/MAX() Просто понять, что останется Нельзя выбрать произвольную запись для сохранения
Временная таблица Безопасно, можно делать сложные преобразования Требует больше места и времени
Рекомендации:
1. Для стандартной задачи — ваш оригинальный запрос или вариант с EXISTS хороши
2. Для сложной логики (например, сохранять запись с самой поздней датой) — используйте ROW_NUMBER()
3. Для больших таблиц — сначала проверьте план выполнения, возможно, лучше использовать временную таблицу
4. Для безопасности — всегда делайте бекап или работайте в транзакции:
```sql
BEGIN;
DELETE FROM users u1
USING users u2
WHERE u1.email = u2.email AND u1.id > u2.id;
-- Проверьте результат, затем COMMIT или ROLLBACK
COMMIT;
```
Самый безопасный и понятный вариант на сегодня:
```sql
WITH duplicates AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC, id) as rn
FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);
```
Этот вариант позволяет точно контролировать, какая запись остается (например, самая свежая по created_at).