Зображення створене за допомогою ChatGPT
1. Робота з відсутніми даними
Відсутні дані — одна з найпоширеніших проблем у наборах даних. Ось як можна ефективно вирішити цю проблему за допомогою SQL.
1.1 Ідентифікація відсутніх даних
Перший крок у роботі з відсутніми даними — це їх ідентифікація. Використовуйте SQL оператори IS NULL або IS NOT NULL для визначення рядків з відсутніми значеннями.
SELECT *
FROM sales
WHERE region IS NULL;
Цей запит знаходить рядки, де відсутнє значення в полі region.
1.2 Замінити відсутні значення
Іноді відсутні значення можна замінити на значення за замовчуванням за допомогою функції COALESCE().
SELECT id, COALESCE(region, ‘Unknown’) AS region
FROM sales;
Цей запит замінює значення NULL у колонці region на "Unknown".
1.3 Імпутація відсутніх значень
Для числових полів відсутні значення можна замінити на похідні метрики, наприклад, середнє значення.
UPDATE sales
SET amount = (
SELECT AVG(amount)
FROM sales
WHERE amount IS NOT NULL
)
WHERE amount IS NULL;
Цей запит заповнює відсутні значення у колонці amount середнім значенням для цієї колонки.
1.4 Видалення рядків з відсутніми значеннями
Якщо відсутні значення занадто поширені або неважливі, можна видалити ці рядки.
DELETE FROM sales
WHERE region IS NULL;
2. Видалення дублікатів
Дублікати можуть спотворювати аналіз та збільшувати метрики. Ось як їх знайти та видалити.
2.1 Ідентифікація дублікатів
Використовуйте GROUP BY та HAVING COUNT(*) > 1, щоб знайти дублікати.
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;
Цей запит знаходить клієнтів з дубльованими замовленнями.
2.2 Видалення дублікатів за допомогою ROW_NUMBER()
Віконна функція ROW_NUMBER() — це потужний інструмент для позначення дублікатів.
WITH RankedOrders AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM orders
)
DELETE FROM orders
WHERE id IN (
SELECT id
FROM RankedOrders
WHERE rn > 1
);
Цей запит зберігає перше виникнення кожного дубліката і видаляє решту.
2.3 Видалення дублікатів під час запиту
При запитах даних використовуйте DISTINCT, щоб отримати лише унікальні рядки.
SELECT DISTINCT customer_id, region
FROM customers;
3. Виправлення невідповідностей у даних
Невідповідні дані можуть бути різними — різні формати, недійсні записи або несумісні категорії. Ось як з ними впоратись.
3.1 Стандартизація форматів
Стандартизація забезпечує послідовність у текстових даних. Використовуйте функції, такі як LOWER(), UPPER(), і TRIM().
UPDATE customers
SET email = LOWER(email);
Цей запит перетворює всі електронні адреси на малі літери для забезпечення послідовності.
3.2 Виправлення недійсних даних
Ідентифікуйте та виправляйте недійсні записи за допомогою умовних оновлень.
UPDATE employees
SET salary = 50000
WHERE salary < 20000;
Цей запит оновлює зарплати, які нижчі за розумний поріг, до дійсного значення.
3.3 Об’єднання схожих записів
Консолідуйте схожі категорії за допомогою виразів CASE.
UPDATE products
SET category = CASE
WHEN category IN (‘Books’, ‘Book’) THEN ‘Books’
ELSE category
END;
Цей запит об’єднує категорії "Books" і "Book" в одну категорію.
3.4 Перевірка цілісності даних
Переконайтеся, що значення даних відповідають очікуваним правилам.
Наприклад, перевірка майбутніх дат:
SELECT *
FROM orders
WHERE order_date > CURRENT_DATE;
Цей запит позначає рядки, де дата замовлення (order_date) помилково встановлена на майбутнє.
Чисті дані — точніші інсайти
Очищення даних — це основа ефективного аналізу даних. Оволодіваючи цими SQL техніками, ви зможете вирішувати проблеми з відсутніми даними, усувати дублікати та виправляти невідповідності, гарантуючи, що ваші набори даних будуть точними та надійними.
Чисті дані не тільки покращують точність ваших висновків, але й заощаджують час у майбутньому. Навіть якщо ці запити не здаються складними, вони закладають основу для більш складних завдань з маніпулювання даними та аналізу. Оволодіння цими основами дає вам впевненість для роботи з більш складними наборами даних і готує вас до вирішення більш складних завдань, коли ваші аналітичні навички будуть зростати.
Тому не забувайте витрачати час на очищення своїх даних — це того варте. Почніть практикувати ці техніки на своїх власних наборах даних і побачите різницю, яку вони принесуть у ваші аналізи!
Перекладено з: Data Cleaning Techniques Every Analyst Should Know