12 ведущих техник очистки и предварительной обработки данных в SQL

Очистка и подготовка данных являются неотъемлемой частью современного анализа данных. Они помогают выявить и исправить проблемы с данными, которые могут привести к искаженным выводам. SQL, как мощный язык запросов, обеспечивает широкий спектр функций для эффективной обработки данных.

В этой статье мы рассмотрим 12 ведущих техник очистки и предварительной обработки данных в SQL, которые помогут вам подготовить данные для анализа и моделирования.

1. Обнаружение и устранение дубликатов

Дубликаты могут существенно исказить результаты анализа. SQL позволяет обнаруживать и удалять дубликаты с помощью оператора DISTINCT или GROUP BY.

-- Удаление дубликатов по всем столбцам
SELECT DISTINCT * FROM table_name;

-- Удаление дубликатов по определенным столбцам
SELECT DISTINCT column1, column2 FROM table_name;

-- Группировка строк с дубликатами
SELECT column1, column2, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY column1, column2;

2. Обработка пропущенных значений

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

NULL: Обозначает отсутствие значения.
0 или '': Замена пропущенных значений нулями или пустыми строками.
Среднее, Медиана, Мода: Заполнение пропущенных значений статистическими мерами.

-- Замена пропущенных значений нулями
UPDATE table_name SET column_name = 0 WHERE column_name IS NULL;

-- Заполнение пропущенных значений средним значением
UPDATE table_name SET column_name = AVG(column_name) WHERE column_name IS NULL;

3. Выявление и исправление ошибок данных

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

ISNULL(): Проверяет, является ли значение NULL.
NOT NULL: Проверяет, что значение не равно NULL.
BETWEEN: Проверяет, находится ли значение в заданном диапазоне.

-- Выявление строк с отсутствующим значением в столбце "age"
SELECT * FROM table_name WHERE age IS NULL;

-- Исправление некорректных значений в столбце "salary"
UPDATE table_name SET salary = NULL WHERE salary < 0;

4. Преобразование и форматирование данных

Данные могут быть представлены в различных форматах, что может усложнить анализ. SQL позволяет преобразовывать и форматировать данные для единообразия и удобства использования:

CAST(): Конвертирует данные в другой тип данных.
TRIM(): Удаляет пробелы из строки.
UPPER() и LOWER(): Преобразует значения в верхний или нижний регистр.

-- Преобразование столбца "date" в тип даты
ALTER TABLE table_name ALTER COLUMN date TYPE DATE;

-- Удаление пробелов из столбца "name"
UPDATE table_name SET name = TRIM(name);

-- Преобразование значений столбца "category" в верхний регистр
UPDATE table_name SET category = UPPER(category);

5. Объединение и агрегация данных

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

JOIN: Объединяет строки из разных таблиц по общему ключу.
GROUP BY: Группирует строки по общим значениям.
SUM(), COUNT(), AVG(): Агрегируют значения в группах.

-- Объединение таблиц "customers" и "orders" по столбцу "customer_id"
SELECT * FROM customers JOIN orders ON customers.customer_id = orders.customer_id;

-- Группировка продаж по категориям и подсчет общего количества продаж
SELECT category, SUM(sales) AS total_sales
FROM sales
GROUP BY category;

6. Создание вычисляемых столбцов

Вычисляемые столбцы позволяют добавлять новые столбцы к таблице на основе существующих данных. Это полезно для создания новых показателей, трансформаций или объединения данных.

-- Создание вычисляемого столбца для расчета общей стоимости заказа
ALTER TABLE orders ADD COLUMN total_cost AS unit_price * quantity;

7. Разделение и извлечение данных

Разделение и извлечение данных позволяют разделить строки или столбцы на более мелкие части. Это полезно для создания новых таблиц, нормализации данных или преобразования данных в требуемый формат.

SUBSTRING(): Извлекает подстроку из строки.
SPLIT(): Разделяет строку по разделителю.

-- Извлечение домена электронной почты из столбца "email"
SELECT SUBSTRING(email, INSTR(email, '@') + 1) AS email_domain
FROM customers;

-- Разделение столбца "address" на отдельные столбцы для улицы, города и почтового индекса
ALTER TABLE customers
ADD COLUMN street TEXT,
ADD COLUMN city TEXT,
ADD COLUMN zip_code TEXT;

UPDATE customers
SET street = SPLIT_PART(address, ',', 1),
city = SPLIT_PART(address, ',', 2),
zip_code = SPLIT_PART(address, ',', 3);

8. Очистка текстовых данных

Текстовые данные часто содержат шум и несоответствия. SQL предоставляет функции для очистки таких данных:

LOWER(): Преобразует значения в нижний регистр.
REPLACE(): Заменяет один фрагмент текста другим.
TRIM(): Удаляет пробелы из строки.

-- Преобразование значений столбца "description" в нижний регистр
UPDATE products SET description = LOWER(description);

-- Замена всех вхождений "USD" на "$" в столбце "price"
UPDATE products SET price = REPLACE(price, 'USD', '$');

-- Удаление ведущих и хвостовых пробелов из столбца "product_name"
UPDATE products SET product_name = TRIM(product_name);

9. Работа с числами и датами

Числовые и даты являются важными типами данных в анализе данных. SQL предоставляет функции для их преобразования и манипулирования:

ROUND(): Округляет число до заданного количества десятичных знаков.
DATE(): Извлекает дату из строки даты и времени.
ADD_DAYS(): Добавляет указанное количество дней к дате.

-- Округление значений столбца "price" до двух десятичных знаков
UPDATE products SET price = ROUND(price, 2);

-- Извлечение даты из столбца "transaction_date"
SELECT DATE(transaction_date) AS transaction_date
FROM sales;

-- Добавление 7 дней к дате в столбце "ship_date"
UPDATE orders SET ship_date = ADD_DAYS(ship_date, 7);

10. Проверка ограничений данных

Ограничения данных помогают обеспечить целостность и качество данных. SQL позволяет устанавливать ограничения:

PRIMARY KEY: Уникальный идентификатор для каждой строки.
FOREIGN KEY: Ссылка на первичный ключ другой таблицы.
NOT NULL: Не допускает значений NULL в указанном столбце.

-- Установка первичного ключа для столбца "customer_id" в таблице "customers"
ALTER TABLE customers ADD PRIMARY KEY (customer_id);

-- Установка внешнего ключа для столбца "order_id" в таблице "orders", ссылающегося на первичный ключ в таблице "customers"
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- Установка ограничения "NOT NULL" для столбца "name" в таблице "customers"
ALTER TABLE customers ALTER COLUMN name SET NOT NULL;

11. Индексация данных

Индексы улучшают производительность запросов путем упорядочения данных. SQL позволяет создавать индексы с помощью оператора CREATE INDEX.

-- Создание индекса для столбца "customer_id" в таблице "orders"
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

12. Удаление повторяющихся данных

Повторяющиеся данные могут привести к неточности и искажению анализа. SQL позволяет удалять