SQL для системного аналитика

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

Основы SQL

SELECT — Выборка данных

-- Простая выборка
SELECT * FROM users;

-- Выборка конкретных колонок
SELECT id, email, created_at FROM users;

-- С условием
SELECT * FROM users WHERE status = 'active';

-- Сортировка
SELECT * FROM users ORDER BY created_at DESC;

-- Ограничение количества
SELECT * FROM users LIMIT 10;

WHERE — Условия

-- Равенство
SELECT * FROM orders WHERE status = 'completed';

-- Неравенство
SELECT * FROM products WHERE price > 1000;

-- Диапазон
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;

-- Список значений
SELECT * FROM users WHERE role IN ('admin', 'manager');

-- Поиск по шаблону
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- NULL значения
SELECT * FROM users WHERE phone IS NULL;

-- Комбинация условий
SELECT * FROM orders 
WHERE status = 'pending' 
  AND created_at > '2026-01-01'
  AND total > 100;

JOIN — Объединение таблиц

-- INNER JOIN (только совпадающие записи)
SELECT 
    orders.id,
    orders.total,
    users.email
FROM orders
INNER JOIN users ON orders.user_id = users.id;

-- LEFT JOIN (все из левой таблицы)
SELECT 
    users.email,
    COUNT(orders.id) as orders_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.email;

-- Множественные JOIN
SELECT 
    orders.id,
    users.email,
    products.name,
    order_items.quantity
FROM orders
INNER JOIN users ON orders.user_id = users.id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;

GROUP BY — Агрегация

-- Подсчет
SELECT status, COUNT(*) as count
FROM orders
GROUP BY status;

-- Сумма
SELECT user_id, SUM(total) as total_spent
FROM orders
GROUP BY user_id;

-- Среднее
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category;

-- Минимум и максимум
SELECT 
    category,
    MIN(price) as min_price,
    MAX(price) as max_price
FROM products
GROUP BY category;

-- HAVING (фильтр после группировки)
SELECT user_id, COUNT(*) as orders_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

Подзапросы

-- В WHERE
SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE total > 1000
);

-- В SELECT
SELECT 
    users.email,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as orders_count
FROM users;

-- В FROM
SELECT avg_total
FROM (
    SELECT user_id, AVG(total) as avg_total
    FROM orders
    GROUP BY user_id
) as user_averages
WHERE avg_total > 500;

Практические запросы для аналитика

1. Анализ активности пользователей

-- Пользователи с количеством заказов
SELECT 
    u.id,
    u.email,
    u.created_at as registration_date,
    COUNT(o.id) as orders_count,
    SUM(o.total) as total_spent,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.email, u.created_at
ORDER BY total_spent DESC;

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

-- Пользователи по месяцам регистрации
SELECT 
    DATE_TRUNC('month', created_at) as cohort_month,
    COUNT(*) as users_count
FROM users
GROUP BY cohort_month
ORDER BY cohort_month;

-- Retention по когортам
SELECT 
    DATE_TRUNC('month', u.created_at) as cohort,
    DATE_TRUNC('month', o.created_at) as order_month,
    COUNT(DISTINCT o.user_id) as active_users
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY cohort, order_month
ORDER BY cohort, order_month;

3. Воронка продаж

SELECT 
    'Посетители' as stage,
    COUNT(DISTINCT user_id) as count
FROM page_views
WHERE page = 'product'

UNION ALL

SELECT 
    'Добавили в корзину',
    COUNT(DISTINCT user_id)
FROM cart_items

UNION ALL

SELECT 
    'Начали оформление',
    COUNT(DISTINCT user_id)
FROM orders
WHERE status != 'cart'

UNION ALL

SELECT 
    'Завершили покупку',
    COUNT(DISTINCT user_id)
FROM orders
WHERE status = 'completed';

4. RFM анализ

SELECT 
    user_id,
    -- Recency (дней с последнего заказа)
    CURRENT_DATE - MAX(created_at::date) as recency,
    -- Frequency (количество заказов)
    COUNT(*) as frequency,
    -- Monetary (общая сумма)
    SUM(total) as monetary
FROM orders
WHERE status = 'completed'
GROUP BY user_id;

5. Топ товаров

SELECT 
    p.id,
    p.name,
    p.category,
    COUNT(oi.id) as times_ordered,
    SUM(oi.quantity) as total_quantity,
    SUM(oi.quantity * oi.price) as total_revenue
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
  AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY p.id, p.name, p.category
ORDER BY total_revenue DESC
LIMIT 10;

Оконные функции (Window Functions)

-- Ранжирование
SELECT 
    name,
    category,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank
FROM products;

-- Накопительная сумма
SELECT 
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) as cumulative_revenue
FROM daily_sales;

-- Скользящее среднее (за 7 дней)
SELECT 
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7d
FROM daily_sales;

-- Сравнение с предыдущим периодом
SELECT 
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as prev_day_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) as diff
FROM daily_sales;

CTE (Common Table Expressions)

-- Более читаемый код
WITH active_users AS (
    SELECT user_id
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
    HAVING COUNT(*) >= 3
),
user_stats AS (
    SELECT 
        u.id,
        u.email,
        COUNT(o.id) as orders_count,
        SUM(o.total) as total_spent
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.id IN (SELECT user_id FROM active_users)
    GROUP BY u.id, u.email
)
SELECT * FROM user_stats
ORDER BY total_spent DESC;

Проектирование баз данных

Нормализация

1NF (Первая нормальная форма)

• Атомарные значения (не массивы)
• Уникальные строки
• Первичный ключ

❌ Плохо:

users
id | name  | phones
1  | Ivan  | +7123, +7456

✅ Хорошо:

users
id | name
1  | Ivan

user_phones
id | user_id | phone
1  | 1       | +7123
2  | 1       | +7456

2NF (Вторая нормальная форма)

• Соответствует 1NF
• Нет частичных зависимостей от составного ключа

3NF (Третья нормальная форма)

• Соответствует 2NF
• Нет транзитивных зависимостей

Типы связей

One-to-Many (1:M)

-- Один пользователь — много заказов
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2)
);

Many-to-Many (M:N)

-- Много товаров в много заказов
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP
);

-- Связующая таблица
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER,
    price DECIMAL(10,2)
);

One-to-One (1:1)

-- Один пользователь — один профиль
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255)
);

CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    bio TEXT
);

Индексы

-- Простой индекс
CREATE INDEX idx_users_email ON users(email);

-- Составной индекс
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Частичный индекс
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

Когда создавать индексы:

• Колонки в WHERE
• Колонки в JOIN
• Колонки в ORDER BY
• Foreign keys

Когда НЕ создавать:

• Маленькие таблицы (< 1000 строк)
• Колонки с низкой селективностью (пол, булевы)
• Часто обновляемые колонки

Оптимизация запросов

EXPLAIN — Анализ плана выполнения

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123
  AND status = 'completed';

Что смотреть:

• Seq Scan — полное сканирование (плохо для больших таблиц)
• Index Scan — использование индекса (хорошо)
• Cost — оценка стоимости
• Rows — количество строк

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

1. N+1 запросов

❌ Плохо:

-- В коде: для каждого пользователя отдельный запрос
SELECT * FROM users;
-- Затем для каждого:
SELECT * FROM orders WHERE user_id = ?;

✅ Хорошо:

-- Один запрос с JOIN
SELECT 
    u.*,
    o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

2. SELECT *

❌ Плохо:

SELECT * FROM users;  -- Получаем все колонки

✅ Хорошо:

SELECT id, email, name FROM users;  -- Только нужные

3. Отсутствие LIMIT

❌ Плохо:

SELECT * FROM orders;  -- Может вернуть миллионы строк

✅ Хорошо:

SELECT * FROM orders LIMIT 100;

Транзакции

BEGIN;

-- Перевод денег между счетами
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Если все ок
COMMIT;

-- Если ошибка
ROLLBACK;

ACID свойства:

• Atomicity — все или ничего
• Consistency — данные остаются консистентными
• Isolation — транзакции не мешают друг другу
• Durability — результат сохраняется

Практические задачи

Задача 1: Найти пользователей без заказов

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

Задача 2: Второй по величине заказ

SELECT total
FROM orders
ORDER BY total DESC
LIMIT 1 OFFSET 1;

Задача 3: Дубликаты email

SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Задача 4: Пользователи с ростом заказов

WITH monthly_orders AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', created_at) as month,
        COUNT(*) as orders_count
    FROM orders
    GROUP BY user_id, month
)
SELECT 
    user_id,
    month,
    orders_count,
    LAG(orders_count) OVER (PARTITION BY user_id ORDER BY month) as prev_month
FROM monthly_orders
WHERE orders_count > LAG(orders_count) OVER (PARTITION BY user_id ORDER BY month);

Инструменты

• DBeaver — универсальный SQL клиент
• pgAdmin — для PostgreSQL
• MySQL Workbench — для MySQL
• DataGrip — от JetBrains (платный)
• TablePlus — современный, красивый

Полезные ресурсы

• SQL Zoo — интерактивные упражнения
• LeetCode Database — задачи
• Mode SQL Tutorial
• Книга "SQL Performance Explained" — Markus Winand
• Use The Index, Luke — про индексы

Чек-лист навыков SQL для аналитика

• SELECT, WHERE, ORDER BY, LIMIT
• JOIN (INNER, LEFT, RIGHT)
• GROUP BY, HAVING
• Агрегатные функции (COUNT, SUM, AVG, MIN, MAX)
• Подзапросы
• UNION, UNION ALL
• CASE WHEN
• Работа с датами (DATE_TRUNC, INTERVAL)
• Оконные функции (ROW_NUMBER, RANK, LAG, LEAD)
• CTE (WITH)
• EXPLAIN для анализа запросов
• Понимание индексов
• Проектирование таблиц (нормализация)
• Типы связей (1:M, M:N, 1:1)