Post #12077
407
☀Объяснение:
При загрузке данных из разных источников (CSV, старые базы, ручной ввод) часто появляются дубли. Первичного ключа нет, и нужно выявить все строки-дубликаты (а не просто узнать, какие комбинации полей дублируются). Например, клиент Иванов Иван с email ivan@mail.ru может встретиться три раза с разными
Что делает ROW_NUMBER()?
Оконная функция
Пример запроса:
sql
Если таблица не имеет поля
Почему другие варианты не подходят:
C (GROUP BY + HAVING) – покажет, какие комбинации полей встречаются более одного раза, но не выдаст сами записи. Например, вы узнаете, что (Иванов, ivan@mail.ru) дублируется, но не получите три строки для анализа.
B (временная таблица) – технически можно: вставить уникальные записи во временную таблицу, потом сравнить. Но это громоздкий и медленный способ, особенно для больших объёмов.
D (добавить PK) – автогенерируемый ключ не найдёт существующие дубли. Он только предотвратит новые дубли, если добавить уникальное ограничение.
Реальный кейс:
В одной компании при миграции из старой CRM в новую выяснилось, что в таблице
Что должен зафиксировать аналитик в требованиях к качеству данных:
«Периодически проводить проверку на дубли по критическим полям с использованием оконных функций».
«Результаты проверки должны включать все дублирующиеся строки, а не только комбинации полей».
Вывод: Для выявления полных дубликатов записей (а не просто факта дублирования) оконная функция
При загрузке данных из разных источников (CSV, старые базы, ручной ввод) часто появляются дубли. Первичного ключа нет, и нужно выявить все строки-дубликаты (а не просто узнать, какие комбинации полей дублируются). Например, клиент Иванов Иван с email ivan@mail.ru может встретиться три раза с разными
id (если бы id был). Нужно найти все эти три записи, чтобы потом оставить одну (чистка данных).Что делает ROW_NUMBER()?
Оконная функция
ROW_NUMBER() присваивает уникальный номер каждой строке внутри группы. Группа определяется PARTITION BY (поля, по которым ищем дубли). Порядок нумерации внутри группы задаётся ORDER BY (можно по любому полю, например, по условному id, если есть, или дате). Первая строка в группе получает номер 1, вторая – 2 и т.д. Все строки с номером > 1 – дубликаты.Пример запроса:
sql
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY fio, email, phone ORDER BY id) AS rn
FROM clients
)
SELECT * FROM ranked WHERE rn > 1;
Если таблица не имеет поля
id, можно использовать ORDER BY (SELECT NULL) или любую константу – тогда порядок произвольный, но дубли всё равно будут отмечены.Почему другие варианты не подходят:
C (GROUP BY + HAVING) – покажет, какие комбинации полей встречаются более одного раза, но не выдаст сами записи. Например, вы узнаете, что (Иванов, ivan@mail.ru) дублируется, но не получите три строки для анализа.
B (временная таблица) – технически можно: вставить уникальные записи во временную таблицу, потом сравнить. Но это громоздкий и медленный способ, особенно для больших объёмов.
D (добавить PK) – автогенерируемый ключ не найдёт существующие дубли. Он только предотвратит новые дубли, если добавить уникальное ограничение.
Реальный кейс:
В одной компании при миграции из старой CRM в новую выяснилось, что в таблице
customers15% записей – дубли по ФИО+email. Аналитик использовал ROW_NUMBER(), сгенерировал отчёт с дублями и передал бизнесу на чистку. Без оконных функций пришлось бы писать сложные самосоединения, которые работали бы часы.Что должен зафиксировать аналитик в требованиях к качеству данных:
«Периодически проводить проверку на дубли по критическим полям с использованием оконных функций».
«Результаты проверки должны включать все дублирующиеся строки, а не только комбинации полей».
Вывод: Для выявления полных дубликатов записей (а не просто факта дублирования) оконная функция
ROW_NUMBER() – самый эффективный и наглядный инструмент.- 🤔 1
