Какие сложные SQL-запросы приходилось писать при тестировании и какие подходы используешь?

Сложные SQL-запросы в практике QA Automation

В моей практике QA Automation сложные SQL-запросы возникали в нескольких ключевых сценариях: валидация данных после ETL-процессов, сравнение датасетов между разными системами, анализ связанных данных в нормализованных базах, и генерация тестовых данных с соблюдением бизнес-правил.

Примеры сложных запросов

1. Рекурсивные запросы для иерархических структур

При тестировании каталогов продуктов или оргструктур часто нужны рекурсивные запросы:

WITH RECURSIVE category_tree AS (
    -- Якорь рекурсии
    SELECT category_id, parent_id, name, 1 as level
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Рекурсивная часть
    SELECT c.category_id, c.parent_id, c.name, ct.level + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree ORDER BY level, name;

Такой запрос помогает проверить целостность иерархии и выявить циклические зависимости.

2. Агрегация с оконными функциями

Для анализа временных рядов или ранжирования данных:

SELECT 
    user_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER(PARTITION BY user_id ORDER BY order_date) as cumulative_total,
    AVG(order_amount) OVER(PARTITION BY user_id ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg,
    RANK() OVER(PARTITION BY DATE_TRUNC('month', order_date) ORDER BY order_amount DESC) as monthly_rank
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

Этот запрос проверяет корректность расчетов накопленных сумм и скользящих средних.

3. Многотабличные JOIN с условной логикой

При тестировании сложных бизнес-процессов:

SELECT 
    u.user_id,
    u.email,
    COUNT(DISTINCT o.order_id) as total_orders,
    SUM(CASE WHEN o.status = 'completed' THEN o.amount ELSE 0 END) as completed_amount,
    MAX(CASE WHEN p.premium_level = 'gold' THEN 1 ELSE 0 END) as is_gold_user
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
LEFT JOIN user_premium p ON u.user_id = p.user_id
    AND p.expiry_date > CURRENT_DATE
WHERE u.registration_date > '2023-01-01'
GROUP BY u.user_id, u.email
HAVING COUNT(DISTINCT o.order_id) > 5
ORDER BY completed_amount DESC;

Методологические подходы

Стратегия построения запросов

1. Инкрементальная разработка — начинаю с простого SELECT, постепенно добавляя JOIN, WHERE, GROUP BY
2. Использование CTE (Common Table Expressions) для улучшения читаемости:

WITH filtered_orders AS (
    SELECT * FROM orders WHERE status = 'active'
),
user_totals AS (
    SELECT user_id, SUM(amount) as total
    FROM filtered_orders
    GROUP BY user_id
)
SELECT u.name, ut.total
FROM users u
JOIN user_totals ut ON u.id = ut.user_id;

Подходы к тестированию SQL-запросов

1. Сравнение с эталоном — запрос должен возвращать ожидаемый результат на подготовленных данных
2. Проверка граничных условий — NULL значения, пустые множества, дубликаты
3. Анализ производительности — проверка выполнения на больших объемах данных
4. Валидация бизнес-логики — каждый JOIN и WHERE должен соответствовать требованиям

Инструменты и практики

• DBUnit для управления тестовыми данными
• Liquibase/Flyway для контроля миграций
• EXPLAIN ANALYZE для анализа плана выполнения
• Логирование запросов в автоматизированных тестах для отладки

Типичные проблемы и решения

1. Проблема: Разные диалекты SQL в dev/prod средах
   Решение: Использование абстракционных слоев или ORM с возможностью переключения диалектов

2. Проблема: Несогласованные данные между системами
   Решение: Создание запросов для сравнения контрольных сумм (checksum) ключевых метрик

3. Проблема: Большой объем тестовых данных
   Решение: Генерация минимально необходимых данных с помощью рекурсивных CTE:

WITH RECURSIVE numbers AS (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 1000
)
SELECT 
    n as id,
    'user_' || n as username,
    NOW() - (n || ' days')::INTERVAL as created_date
FROM numbers;

Ключевой принцип: SQL в автоматизации — не самоцель, а инструмент для проверки корректности данных и бизнес-логики. Каждый сложный запрос должен быть документирован, протестирован и сопровождаться понятными assertion'ами в автотестах.