Удаление дубликатов по одному полю в SQL: DISTINCT, GROUP BY

Быстрый ответ

Выберите уникальные значения одного столбца, сохраняя при этом связанные с ним данные, используя оконные функции SQL:

SELECT DISTINCT ON (col1) col1, col2, col3
FROM your_table
ORDER BY col1, col2;

Такой запрос вернет вам набор уникальных значений для col1, отсортированных по col2 и сопутствующими значениями из col2 и col3.

Практическое применение оконных функций

Оконные функции помогают получить уникальные элементы из одного столбца, не упуская при этом связанных с ним данных. Функция ROW_NUMBER() в сочетании с PARTITION BY обеспечит эффективное разбиение строк.

Работа с уникальными адресами электронной почты

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

WITH RankedEmails AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY timestamp DESC) as rn
  FROM Users
)
SELECT id, email, timestamp
FROM RankedEmails
WHERE rn = 1;

Фильтрация перед нумерацией

Иногда бывает необходимо отфильтровать данные перед применением оконных функций для повышения точности:

WITH FilteredUsers AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY timestamp DESC) as rn
  FROM Users
  WHERE isActive = 1
)
SELECT id, email, timestamp
FROM FilteredUsers
WHERE rn = 1;

Упрощение структуры запросов с помощью CTE

Общие табличные выражения (CTE) значимо упрощают восприятие сложных запросов.

WITH EmailsWithRank AS (
  SELECT email, MAX(timestamp) as MaxTimestamp
  FROM Users
  GROUP BY email
),
LatestUserData AS (
  SELECT U.*
  FROM Users U
  INNER JOIN EmailsWithRank E ON U.email = E.email AND U.timestamp = E.MaxTimestamp
)
SELECT *
FROM LatestUserData;

Применение GROUP BY и агрегатных функций

Если вам не симпатизируют оконные функции, вы можете воспользоваться GROUP BY и агрегатными функциями:

SELECT email, MAX(timestamp) AS LatestTimestamp
FROM Users
GROUP BY email;

Однако имейте в виду ограничения при использовании GROUP BY без применения агрегирующих функций к остальным столбцам.

Оптимизация запросов с помощью индексов

Применение индексов к столбцам, используемым для сортировки или фильтрации данных, может ускорить выполнение запросов. Это особенно актуально при использовании GROUP BY или оконных функций.

Визуализация

Представьте корзину фруктов (🧺) с разнообразными фруктами и их ценами:

| Фрукт (🍉🍊🍎) | Цвет       | Цена |
| --------------- | --------- | ---- |
| 🍉              | Красный   | $2   |
| 🍉              | Красный   | $3   |
| 🍊              | Оранжевый | $2   |
| 🍊              | Зеленый   | $1   |
| 🍎              | Красный   | $1   |

Ваша задача — выбрать уникальные фрукты с учетом цвета и цены:

SELECT DISTINCT ON (Fruit) Fruit, Color, Price FROM Basket;

Получившаяся выборка представляет список уникальных фруктов, так, как если бы вы выбрали эдакого представителя каждого вида фруктов:

| Фрукт (🍉🍊🍎) | Цвет       | Цена |
| --------------- | --------- | ---- |
| 🍉              | Красный   | $2   |
| 🍊              | Оранжевый | $2   |
| 🍎              | Красный   | $1   |

🧺👀 Это как выбор по одному фрукту от каждого сорта из корзины.

Завершение

Определение «последней» строки

При выборе «последней» строки для каждой группы убедитесь, что ваш критерий выборки действительно отражает хронологический порядок, будь то на основе временных меток или других идентификаторов.

Сочетание DISTINCT и ORDER BY

Важно избегать использования ORDER BY без последующего ограничения, например TOP 1, после применения DISTINCT, поскольку DISTINCT учитывает всю строку целиком, что может вызвать неожиданный результат с неупорядоченными данными.

Выборака уникальных значений с помощью DISTINCT

Использование DISTINCT требуется для выбора уникальных значений, однако это может привести к «лемнискатным эффектам» в результатах запросов. Именно поэтому обдуманное использование разных методов, представленных здесь, поможет достичь нужного результата.

Полезные материалы

1. Обсуждение на Stack Overflow о применении DISTINCT в SQL для выборки данных по конкретным столбцам.
2. Обстоятельное руководство по оконным функциям SQL с теорией и практическими примерами.
3. Официальное руководство MySQL по производным таблицам — важный инструмент для работы с дубликатами.
4. Познавательное руководство по общим табличным выражениям (CTE) c разъяснением концепции и примерами использования в SQL-запросах для более понятного представления логики запросов.