Какие запросы писал в SQL?

Опыт написания SQL запросов

Мой опыт в написании SQL запросов обширен и охватывает все уровни сложности — от простых SELECT'ов до сложных аналитических запросов с window functions и CTEs. Это критичная часть работы системного аналитика при проектировании баз данных и определении требований к запросам.

Типы SQL запросов, которые я регулярно пишу

1. Простые SELECT запросы

Получение данных с фильтрацией:

SELECT user_id, email, created_at
FROM users
WHERE status = 'active' AND country = 'RU'
ORDER BY created_at DESC
LIMIT 100;

Использование: базовая выборка данных для отчётов и проверки.

2. JOIN запросы

INNER JOIN — получение связанных данных:

SELECT 
    o.order_id,
    u.user_name,
    o.total_amount,
    p.product_name
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.created_at >= '2024-01-01';

LEFT JOIN — все записи из левой таблицы:

SELECT 
    u.user_id,
    u.user_name,
    COUNT(o.order_id) as total_orders
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.user_name
HAVING COUNT(o.order_id) = 0;

Использование: получение связанных данных из нескольких таблиц.

3. Агрегирующие функции

Базовые агрегации:

SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as total_orders,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_order_value,
    MAX(total_amount) as max_order,
    MIN(total_amount) as min_order
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

Использование: отчёты и KPI анализ.

4. Subqueries (Подзапросы)

Подзапрос в WHERE:

SELECT *
FROM users
WHERE user_id IN (
    SELECT user_id
    FROM orders
    WHERE total_amount > 1000
    AND created_at >= '2024-01-01'
);

Подзапрос в FROM (Derived Table):

SELECT 
    category,
    AVG(monthly_revenue) as avg_revenue
FROM (
    SELECT 
        c.category_name as category,
        DATE_TRUNC('month', o.created_at) as month,
        SUM(o.total_amount) as monthly_revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    JOIN categories c ON p.category_id = c.category_id
    GROUP BY c.category_name, DATE_TRUNC('month', o.created_at)
) monthly_data
GROUP BY category
ORDER BY avg_revenue DESC;

Использование: сложные аналитические запросы.

5. CTE (Common Table Expression)

Рекурсивный CTE:

WITH RECURSIVE category_hierarchy AS (
    SELECT 
        category_id,
        parent_id,
        category_name,
        1 as level
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT 
        c.category_id,
        c.parent_id,
        c.category_name,
        ch.level + 1
    FROM categories c
    INNER JOIN category_hierarchy ch ON c.parent_id = ch.category_id
)
SELECT *
FROM category_hierarchy
ORDER BY level, category_id;

Использование: иерархические данные, рекурсивные структуры.

Множественный CTE:

WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', created_at) as month,
        SUM(total_amount) as revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', created_at)
),
previous_month AS (
    SELECT 
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) as prev_revenue
    FROM monthly_sales
)
SELECT 
    month,
    revenue,
    prev_revenue,
    ROUND(((revenue - prev_revenue) / prev_revenue * 100)::numeric, 2) as growth_percent
FROM previous_month
WHERE prev_revenue IS NOT NULL
ORDER BY month DESC;

Использование: упрощение сложных запросов, повторное использование логики.

6. Window Functions

ROW_NUMBER, RANK, DENSE_RANK:

SELECT 
    user_id,
    order_id,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as order_number,
    RANK() OVER (ORDER BY total_amount DESC) as amount_rank,
    DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('month', created_at) ORDER BY total_amount DESC) as monthly_rank
FROM orders;

LAG и LEAD:

SELECT 
    created_at,
    total_amount,
    LAG(total_amount) OVER (ORDER BY created_at) as prev_order_amount,
    LEAD(total_amount) OVER (ORDER BY created_at) as next_order_amount,
    total_amount - LAG(total_amount) OVER (ORDER BY created_at) as change_from_prev
FROM orders
WHERE user_id = 123
ORDER BY created_at;

Использование: анализ трендов, сравнение с предыдущими значениями.

7. Indirection и обновление данных

UPDATE с условиями:

UPDATE orders
SET status = 'completed',
    updated_at = NOW()
WHERE order_id IN (
    SELECT order_id FROM orders
    WHERE status = 'shipped'
    AND created_at < NOW() - INTERVAL '7 days'
);

DELETE с проверкой:

DELETE FROM users
WHERE user_id NOT IN (
    SELECT DISTINCT user_id FROM orders
)
AND created_at < NOW() - INTERVAL '1 year'
AND status = 'inactive';

Использование: управление данными, архивирование.

8. Аналитические запросы

Когортный анализ:

WITH user_cohort AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', created_at) as cohort_month
    FROM users
),
user_activity AS (
    SELECT 
        uc.user_id,
        uc.cohort_month,
        DATE_TRUNC('month', o.created_at) as order_month,
        COUNT(o.order_id) as order_count
    FROM user_cohort uc
    LEFT JOIN orders o ON uc.user_id = o.user_id
    GROUP BY uc.user_id, uc.cohort_month, DATE_TRUNC('month', o.created_at)
)
SELECT 
    cohort_month,
    DATE_PART('month', order_month - cohort_month) as months_since_signup,
    COUNT(DISTINCT user_id) as users_active
FROM user_activity
WHERE order_month IS NOT NULL
GROUP BY cohort_month, DATE_PART('month', order_month - cohort_month)
ORDER BY cohort_month DESC, months_since_signup ASC;

Использование: анализ поведения пользователей, retention rate.

9. Индексы и оптимизация

Создание индексов:

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created ON orders(created_at DESC);
CREATE INDEX idx_orders_composite ON orders(user_id, created_at);
CREATE INDEX idx_users_email ON users(email) WHERE status = 'active';

Использование: ускорение запросов, оптимизация производительности.

10. EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT u.user_id, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id;

Использование: анализ плана выполнения запроса, выявление узких мест.

Сложные сценарии

Сегментация пользователей по RFM:

WITH rfm AS (
    SELECT 
        user_id,
        MAX(created_at) as last_order,
        COUNT(*) as frequency,
        SUM(total_amount) as monetary,
        NTILE(5) OVER (ORDER BY MAX(created_at) DESC) as recency_score,
        NTILE(5) OVER (ORDER BY COUNT(*)) as frequency_score,
        NTILE(5) OVER (ORDER BY SUM(total_amount)) as monetary_score
    FROM orders
    GROUP BY user_id
)
SELECT 
    user_id,
    recency_score * 100 + frequency_score * 10 + monetary_score as rfm_score,
    CASE 
        WHEN recency_score >= 4 AND frequency_score >= 4 THEN 'VIP'
        WHEN recency_score >= 3 AND frequency_score >= 3 THEN 'High Value'
        WHEN recency_score >= 2 THEN 'At Risk'
        ELSE 'Inactive'
    END as segment
FROM rfm
ORDER BY rfm_score DESC;

Практические навыки

Query optimization:

• Анализ EXPLAIN ANALYZE
• Использование индексов
• Оптимизация JOIN'ов
• Денормализация при необходимости

Performance:

• Использование PARTITION в больших таблицах
• Батчинг операций
• Кэширование результатов
• Асинхронная обработка

Data quality:

• Валидация данных
• Проверка на дубликаты
• NULL-обработка
• Поиск аномалий

Мой опыт с SQL — это основа для определения правильных требований к базам данных и обеспечения эффективной работы аналитических систем.