Качество данных в SQL: находим аномалии, дубликаты и пропуски

Некачественные данные — главный враг аналитика. Прежде чем строить модели или отчёты, нужно знать, с чем имеешь дело. Вот SQL-арсенал для профилирования и валидации данных.

1. Профилирование таблицы: общая статистика

-- Базовый профиль таблицы users
SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT id) AS unique_ids,
    COUNT(*) - COUNT(DISTINCT id) AS duplicate_ids,
    COUNT(*) FILTER (WHERE email IS NULL) AS null_emails,
    COUNT(*) FILTER (WHERE email IS NOT NULL) AS non_null_emails,
    ROUND(COUNT(*) FILTER (WHERE email IS NULL)::numeric / COUNT(*) * 100, 1) AS null_pct,
    MIN(created_at) AS earliest_record,
    MAX(created_at) AS latest_record,
    COUNT(DISTINCT DATE_TRUNC('day', created_at)) AS days_with_data
FROM users;

2. Поиск дубликатов

-- Дубликаты по email
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

-- Полные дубликаты строк
SELECT *, COUNT(*) AS duplicates
FROM orders
GROUP BY id, user_id, amount, status, created_at
HAVING COUNT(*) > 1;

-- Найти ID дублирующихся строк (оставить только лишние)
SELECT id
FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM users
) t
WHERE rn > 1;

3. Анализ NULL по всем столбцам

-- Процент NULL в каждом столбце
SELECT
    column_name,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE column_name IS NOT NULL) AS not_null_count,
    ROUND(COUNT(*) FILTER (WHERE column_name IS NULL)::numeric / COUNT(*) * 100, 1) AS null_pct
FROM users;
-- (Это не динамический SQL — нужно перечислить столбцы вручную)

Для динамического анализа используйте системные представления:

-- Сгенерировать запрос для каждого столбца
SELECT
    'SELECT ''' || column_name || ''' AS col, ' ||
    'COUNT(*) FILTER (WHERE ' || column_name || ' IS NULL) AS nulls, ' ||
    'COUNT(*) AS total FROM users'
FROM information_schema.columns
WHERE table_name = 'users' AND table_schema = 'public';
-- Выполнить полученные строки

4. Поиск выбросов (outliers)

-- Метод IQR (межквартильный размах)
WITH stats AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3
    FROM orders
)
SELECT o.id, o.amount, o.user_id
FROM orders o, stats s
WHERE o.amount < s.q1 - 1.5 * (s.q3 - s.q1)
   OR o.amount > s.q3 + 1.5 * (s.q3 - s.q1)
ORDER BY o.amount DESC;

-- Метод Z-score: |value - mean| / stddev > 3 → выброс
WITH stats AS (
    SELECT AVG(amount) AS mean, STDDEV(amount) AS sd FROM orders
)
SELECT o.id, o.amount,
       ROUND(ABS(o.amount - s.mean) / NULLIF(s.sd, 0), 2) AS z_score
FROM orders o, stats s
WHERE ABS(o.amount - s.mean) / NULLIF(s.sd, 0) > 3
ORDER BY z_score DESC;

5. Несогласованные данные

-- Заказы с несуществующим user_id (нарушение ссылочной целостности)
SELECT o.id, o.user_id, o.amount
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

-- Суммы не сходятся
SELECT
    order_id,
    SUM(item_price * quantity) AS items_total,
    o.total_amount,
    ABS(SUM(item_price * quantity) - o.total_amount) AS discrepancy
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
GROUP BY order_id, o.total_amount
HAVING ABS(SUM(item_price * quantity) - o.total_amount) > 0.01;

-- Отрицательные суммы там где не должно быть
SELECT * FROM orders WHERE amount < 0;

-- Дата создания позже даты изменения
SELECT * FROM orders WHERE updated_at < created_at;

6. Проверка форматов данных

-- Email без @
SELECT id, email FROM users WHERE email NOT LIKE '%@%';

-- Телефоны с нестандартной длиной
SELECT id, phone, LENGTH(REGEXP_REPLACE(phone, '[^0-9]', '', 'g')) AS digit_count
FROM users
WHERE LENGTH(REGEXP_REPLACE(phone, '[^0-9]', '', 'g')) NOT IN (10, 11);

-- Невалидные статусы (не из допустимого списка)
SELECT DISTINCT status FROM orders
WHERE status NOT IN ('pending', 'completed', 'refunded', 'cancelled');

-- Даты в будущем
SELECT * FROM events WHERE event_date > CURRENT_DATE;
SELECT * FROM users WHERE created_at > NOW();

7. Временные аномалии: пробелы в данных

-- Дни без заказов за последние 90 дней
SELECT d::date AS missing_date
FROM generate_series(
    CURRENT_DATE - 90,
    CURRENT_DATE,
    INTERVAL '1 day'
) d
WHERE NOT EXISTS (
    SELECT 1 FROM orders
    WHERE created_at::date = d::date
);

-- Необычно большой перепад между строками (пробел в ID)
SELECT
    id,
    LAG(id) OVER (ORDER BY id) AS prev_id,
    id - LAG(id) OVER (ORDER BY id) AS gap
FROM orders
WHERE id - LAG(id) OVER (ORDER BY id) > 1000  -- обернуть в подзапрос!

-- Правильный способ (с подзапросом):
SELECT id, prev_id, gap
FROM (
    SELECT id, LAG(id) OVER (ORDER BY id) AS prev_id,
           id - LAG(id) OVER (ORDER BY id) AS gap
    FROM orders
) t
WHERE gap > 1000;

8. Несбалансированные категории

-- Распределение статусов
SELECT status, COUNT(*) AS cnt,
       ROUND(COUNT(*)::numeric / SUM(COUNT(*)) OVER () * 100, 1) AS pct
FROM orders
GROUP BY status ORDER BY cnt DESC;

-- Слишком много pending может означать проблему в обработке
-- Слишком мало completed — сбой конверсии

9. Автоматизированные data quality checks

-- Таблица с правилами и результатами
CREATE TABLE dq_checks AS
SELECT
    check_name,
    failed_rows,
    total_rows,
    ROUND(failed_rows::numeric / total_rows * 100, 2) AS fail_pct,
    CASE WHEN failed_rows = 0 THEN '✅ PASS' ELSE '❌ FAIL' END AS status
FROM (
    VALUES
        (
            'null_emails',
            (SELECT COUNT(*) FROM users WHERE email IS NULL),
            (SELECT COUNT(*) FROM users)
        ),
        (
            'duplicate_emails',
            (SELECT COUNT(*) FROM (SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1) t),
            (SELECT COUNT(DISTINCT email) FROM users)
        ),
        (
            'orphan_orders',
            (SELECT COUNT(*) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM users WHERE id = o.user_id)),
            (SELECT COUNT(*) FROM orders)
        ),
        (
            'negative_amounts',
            (SELECT COUNT(*) FROM orders WHERE amount < 0),
            (SELECT COUNT(*) FROM orders)
        )
) AS checks(check_name, failed_rows, total_rows);

SELECT * FROM dq_checks;

10. Мониторинг свежести данных

-- Когда последний раз обновлялась таблица?
SELECT
    relname AS table_name,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    last_analyze,
    last_autoanalyze,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY last_analyze DESC NULLS LAST;

Итог: чеклист качества данных

Запускайте эти проверки регулярно — в идеале автоматически, перед каждым обновлением дашборда или построением модели.