Видалення повторень
Вихідник бази данихПотреба в усуненні дублікатів з даних зустрічається дуже часто, особливо при вирішенні проблем з якістю даних у середовищах, де дублювання виникло через відсутність обмежень, які б забезпечити унікальність даних. Для демонстрації підготуємо за допомогою наступного коду приклад даних із замовленнями, що дублюються, в таблиці на ім'я MyOrders:
IF OBJECT_ID("Sales.MyOrders") IS NOT NULL DROP TABLE Sales.MyOrders; GO SELECT * INTO Sales.MyOrders FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders;
Уявіть, що вам потрібно усунути дублювання даних, залишивши тільки по одному екземпляру з унікальним orderid значенням. Номери, що дублюються, відзначаються за допомогою функції ROW_NUMBER з секціонуванням за ймовірно унікальним значенням (у нашому випадку orderid) і з використанням довільного впорядкування, якщо вам неважливо, який рядок залишити, а який видалити. Ось код, в якому функція ROW_NUMBER зазначає дублікати:
SELECT orderid, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders;
Потім слід розглянути різні варіантив залежності від кількості рядків, які потрібно видалити, відсотка розмірності таблиці, яка ця кількість складає, активності виробничого середовища та інших обставин. При невеликій кількостірядків, що видаляються, зазвичай достатньо використовувати операцію видалення з повним протоколюванням, в якій видаляються всі екземпляри, у яких номер рядка більше одиниці:
Але якщо число рядків, що видаляються, велике - особливо коли воно становить велику частку рядків таблиці, видалення з повним записомоперації у журналі буде надто повільним. У цьому випадку варто подумати про використання операції неповного протоколювання, наприклад SELECT INTO, для копіювання унікальних рядків (з номером 1) в іншу таблицю. Після цього оригінальна таблиця видаляється, потім нової таблиціприсвоюється ім'я віддаленої таблиці, відтворюються обмеження індекси та тригери. Ось код закінченого рішення:
WITH C AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY ORDERID ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders) SELECT ORDERID, Custid, Empid, Orderdate, Requireddate, Shippeddate, Shi shipcity, shipregion, shippostalcode, shipcountry INTO Sales.OrdersTmp FROM C WHERE n = 1; DROP TABLE Sales.MyOrders; EXEC sp_rename "Sales.OrdersTmp", "MyOrders"; -- відтворення індексів, обмежень та тригерів
Для простоти я не додав сюди жодного контролю транзакцій, але треба завжди пам'ятати, що з даними можуть одночасно працювати кілька користувачів. При реалізації цього методу у виробничому середовищі потрібно дотримуватися наступної послідовності:
Відкрити транзакцію.
Отримати блокування таблиці.
Виконати інструкцію SELECT INTO.
Видалити та перейменувати об'єкти.
Відтворити індекси, обмеження та тригери.
Зафіксувати транзакцію.
Є ще один варіант - відфільтрувати лише унікальні або лише неунікальні рядки. Обчислюються обидві функції - ROW_NUMBER і RANK - на основі впорядкування orderid, приблизно так:
SELECT orderid, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum, RANK() OVER(ORDER BY orderid) AS rnk FROM Sales.MyOrders;
Зауважте, що в результатах лише в одному рядку для кожного унікального значення в orderid збігаються номер і ранг рядка. Наприклад, якщо треба видалити невелику частинуданих, можна інкапсулювати попередній запит визначення CTE, а в зовнішньому запиті виконати інструкцію видалення рядків, у яких різні номеррядки та ранг.
Коли виникає завдання оптимізації бази даних або змінюється її структура, іноді виникає попутне завдання організації вже накопичених даних. Добре, якщо таблиця вже при розробці приведена в нормальну форму, і вся система організована так, що вона не збирає зайвої дублюючої інформації. Якщо ж це не так, то при доопрацюванні такої системи хочеться позбавитися всіх надлишкових даних і зробити все найбільш якісно.
У статті розглянемо завдання видалення дублюючих рядків у таблиці БД. Відразу зазначу, що мова йдепро необхідність видалити рядки, що саме повторюються. Наприклад, записи в таблиці замовлень з полями "код замовлення", "код товару", "код покупця", "дата замовлення" можуть відрізнятися тільки кодом замовлення, так як все ж таки один покупець в один день може замовити один і той же товар кілька разів. А головний показник тут, що все вірно – наявність ключового поля.
Якщо ж ми бачимо таблицю, рясні полями, що повторюються, без явної необхідності кожного запису, то це саме те, що має бути виправлено.
Приклад явно надлишкової таблиці:
Тепер розглянемо, як вирішити цю проблему. Тут можна застосувати кілька способів.
1. Можна написати функцію для порівняння та перебору всіх даних. Це довго та й писати код для одноразового використання не завжди хочеться.
2. Інше рішення – створити запит на вибірку з групуванням даних, щоб отримати тільки унікальні рядки:
SELECT country_id, city_name
FROM mytable
GROUP BY country_id, city_name
Отримуємо наступну вибірку:
Потім отриманий набір даних записуємо в іншу таблицю.
3. У вказаних рішенняхзастосовується додатковий програмний кодчи додаткові таблиці. Однак, було б зручніше зробити все, використовуючи тільки запити SQLбез додаткових таблиць. І ось приклад такого рішення:
DELETE a.* FROM mytable a,
(SELECT
FROM mytable b
) c
WHERE
a.country_id = c.country_id
AND a.city_name = c.city_name
AND a.id > c.mid
Після виконання такого запиту в таблиці залишаться лише унікальні записи:
Тепер розберемося докладніше, як це все працює. При запиті на видалення необхідно задати умову, яка вкаже, які дані потрібно видалити, а які залишити. Нам необхідно видалити всі унікальні записи. Тобто. якщо існує кілька однакових записів (однакові вони, якщо вони рівні значення country_id і city_name), потрібно взяти одну з рядків, запам'ятати її код і видалити всі записи з такими ж значеннями country_id і city_name, але іншим кодом (id).
Рядок SQL запиту:
DELETE a.* FROM mytable a,
вказує, що видалення буде проводитись з таблиці mytable.
Потім запит на вибірку формує допоміжну таблицю, де ми групуємо записи так, щоб усі записи були унікальними:
(SELECT
b.country_id, b.city_name, MIN(b.id) mid
FROM mytable b
GROUP BY b.country_id, b.city_name
) c
MIN(b.id) mid – формує стовпець mid (скорочення min id), до якого вносяться мінімальне значення id, у кожній підгрупі.
В результаті виходить таблиця, що містить унікальні записи та id першого рядка для кожної групи дублюючих записів.
Тепер ми маємо дві таблиці. Одну загальну, що містить усі записи. З неї видалятимуться зайві рядки. Друга містить інформацію про рядки, які потрібно зберегти.
Залишається лише сформувати умову, де вказується: видалити потрібно всі рядки, де збігаються поля country_id та city_name, а id збігатися не буде. У даному випадкувибирається мінімальне значення id, тому видаляються всі записи, id яких більше, ніж обраний у тимчасову таблицю.
Варто зазначити, що описану операцію можна виконати за наявності у таблиці ключового поля. Якщо раптом зустрілася таблиця без унікального ідентифікатора, просто додаємо його:
ALTER TABLE `mytable` ADD `id` INT(11) NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY (`id`)
Виконавши такий запит, отримаємо додатковий стовпець, заповнений унікальними числовими значеннямидля кожного рядка таблиці.
Виконуємо все необхідні дії. Після того, як операцію з очищення таблиці від дублікатів записів виконано, це поле можна також видалити.
(25-07-2009)
У попередній статті ми розглянули вирішення проблеми з дублікатами, спричиненою відсутністю первинного ключа. Розглянемо тепер важчий випадок, коли ключ начебто є, але він є синтетичним, що при неправильному проектуванні теж може призвести до появи дублікатів з погляду предметної області.
Дивна річ, але, розповідаючи на лекціях про недоліки синтетичних ключів, я, проте, постійно стикаюся з тим, що студенти у своїх перших проектах із базами даних їх незмінно використовують. Мабуть, у людині закладено генетичну потребу все перенумеровувати, і допомогти тут може лише психотерапевт. :-)
Отже, нехай є таблиця з первинним ключем id та стовпцем name, який відповідно до обмежень предметної області повинен містити унікальні значення. Однак якщо визначити структуру таблиці в такий спосіб
CREATE TABLE T_pk (ID INT IDENTITY PRIMARY KEY, name VARCHAR (50));то появі дублікатів ніщо не перешкоджає. Треба було б використовувати наступну структурутаблиці:
CREATE TABLE T_pk (ID INT IDENTITY PRIMARY KEY, name VARCHAR (50) UNIQUE);Всі знають, як потрібно правильно вчинити, проте найчастіше доводиться мати з "успадкованою" структурою та даними, які порушують обмеження предметної галузі. Ось приклад:
id name 1 John 2 Smith 3 John 4 Smith 5 Smith 6 TomВи можете запитати: "А чим ця проблема відрізняється від попередньої? Адже тут є навіть простіше рішення - просто видалити всі рядки з кожної групи з однаковими значеннями в стовпці name, залишивши лише рядок з мінімальним/максимальним значенням id. Наприклад, так:"
DELETE FROM T_pk WHERE ID > (SELECT MIN (id) FROM T_pk X WHERE X.name = T_pk.name);Правильно, але я ще не все вам розповів. :-) Уявіть, що у нас є дочірня таблиця T_details, пов'язана з таблицею T_pk зовнішньому ключу:
CREATE TABLE T_details (id_pk INT FOREIGN KEY REFERENCES T_pk ON DELETE CASCADE , барвистий VARCHAR (10 ), PRIMARY KEY (id_pk, color);Ця таблиця може містити такі дані:
id_pk color 1 blue 1 red 2 green 2 red 3 red 4 blue 6 redДля більшої наочності скористаємося запитом
SELECT id, name, color FROM T_pk JOIN T_details ON id= id_pk;щоб побачити імена:
id name color 1 John blue 1 John red 2 Smith green 2 Smith red 3 John red 4 Smith blue 6 Tom redТаким чином, виявляється, що дані, які фактично належать до однієї особи, помилково виявилися рознесеними по різних батьківським записам. Крім того, дублікати опинилися і в цій таблиці:
1 John red 3 John red
Очевидно, що подібні дані призведуть до помилкового аналізу та звітів. Більш того, каскадне видаленняпризведе до втрати даних. Наприклад, якщо ми залишимо лише рядки з мінімальним ідентифікатором у кожній групі в таблиці T_pk, то втратимо рядок
4 Smith blue
у таблиці T_details. Отже, ми повинні при усуненні дублікатів враховувати обидві таблиці.
Процедуру "очищення" даних можна провести у два етапи:
- Виконати оновлення таблиці T_details, приписавши дані, що стосуються одного імені, id з мінімальним номером у групі.
- Видалити дублікати з таблиці T_pk, залишивши лише рядки з мінімальним id у кожній групі з однаковим значенняму стовпці name.
Оновлення таблиці T_details
SELECT id_pk, name, color, RANK () OVER (PARTITION BY name, color ORDER BY name, color, id_pk) dup ,(SELECT MIN (id) FROM T_pk WHERE T_pk.name = X.name) min_id FROM T_pk X JOIN T_de ON id=id_pk;визначає наявність дублікатів (значення dup > 1) та мінімальне значення id у групі однакових імен (min_id). Ось результат виконання цього запиту:
id_pk name color dup min_id 1 John blue 1 1 1 John red 1 1 3 John red 2 1 4 Smith blue 1 2 2 Smith green 1 2 2 Smith red 1 2 6 Tom red 1 6Тепер нам потрібно замінити значення id_pk на значення min_pk для всіх рядків, крім третього, т.к. цей рядок є дублікат другого рядка, про що свідчить значення dup=2. Запит на оновлення можна написати так:
UPDATE T_details SET id_pk=min_id FROM T_details T_d JOIN (SELECT id_pk, name, color, RANK () OVER (PARTITION BY name, color ORDER BY name, color, id_pk) dup ,(SELECT MIN (id) FROM = X.name) min_id FROM T_pk X JOIN T_details ON id = id_pk) Y ON Y.id_pk = T_d.id_pk WHERE dup = 1;
