Какой сложности запросы писал на PostgreSQL?

Ответ

В своей практике я работал с запросами различной сложности в PostgreSQL, от простых CRUD операций до сложных аналитических запросов. Давайте разберем примеры.

Уровень 1: Базовые CRUD операции

-- SELECT с WHERE
SELECT * FROM users WHERE email = 'user@example.com';

-- INSERT
INSERT INTO users (name, email, created_at) 
VALUES ('John Doe', 'john@example.com', NOW());

-- UPDATE
UPDATE users SET name = 'Jane Doe' WHERE id = 1;

-- DELETE
DELETE FROM users WHERE id = 1;

Уровень 2: Джойны и агрегация

-- INNER JOIN с GROUP BY
SELECT 
    d.name,
    COUNT(u.id) as employee_count,
    AVG(u.salary) as avg_salary
FROM departments d
INNER JOIN users u ON d.id = u.department_id
GROUP BY d.id, d.name
HAVING COUNT(u.id) > 5
ORDER BY avg_salary DESC;

-- LEFT JOIN с фильтром
SELECT 
    u.name,
    COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id AND p.status = 'published'
GROUP BY u.id, u.name;

Уровень 3: Оконные функции (Window Functions)

Оконные функции очень мощны для аналитики данных.

-- ROW_NUMBER для нумерации
SELECT 
    id,
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM users
WHERE department_id = 1;

-- RANK vs DENSE_RANK (разница при одинаковых значениях)
SELECT 
    id,
    name,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dense_rank
FROM users;

-- LAG/LEAD для доступа к предыдущим/следующим строкам
SELECT 
    id,
    name,
    salary,
    LAG(salary) OVER (ORDER BY hire_date) as prev_salary,
    LEAD(salary) OVER (ORDER BY hire_date) as next_salary,
    salary - LAG(salary) OVER (ORDER BY hire_date) as salary_change
FROM users;

-- SUM OVER для скользящей суммы
SELECT 
    id,
    created_at,
    amount,
    SUM(amount) OVER (
        ORDER BY created_at 
        ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
    ) as rolling_7day_sum
FROM transactions;

Уровень 4: Рекурсивные CTE (Common Table Expressions)

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

-- Получить всех подчиненных и их подчиненных
WITH RECURSIVE employee_hierarchy AS (
    -- Базовый случай: босс
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Рекурсивный случай: подчиненные
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
    WHERE eh.level < 10  -- ограничение глубины
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

-- Поиск пути в графе
WITH RECURSIVE graph AS (
    SELECT id, parent_id, 1 as depth, ARRAY[id] as path
    FROM categories
    WHERE id = 1
    
    UNION ALL
    
    SELECT c.id, c.parent_id, g.depth + 1, g.path || c.id
    FROM categories c
    INNER JOIN graph g ON c.parent_id = g.id
    WHERE g.depth < 10
)
SELECT * FROM graph;

Уровень 5: JSON операции и поиск

PostgreSQL имеет встроенную поддержку JSON.

-- Работа с JSON колонками
SELECT 
    id,
    data->>'name' as name,
    data->'settings'->>'theme' as theme,
    (data->>'age')::integer as age
FROM users
WHERE data->>'role' = 'admin';

-- JSONB с операторами
SELECT *
FROM users
WHERE data @> '{"status": "active"}';

-- JSON агрегация
SELECT 
    department_id,
    json_agg(
        json_build_object(
            'id', id,
            'name', name,
            'salary', salary
        )
    ) as employees
FROM users
GROUP BY department_id;

Уровень 6: Полнотекстовый поиск (Full-Text Search)

-- Индекс FTS
CREATE INDEX idx_articles_fts ON articles 
    USING GIN (to_tsvector('russian', content));

-- Поиск
SELECT 
    id,
    title,
    ts_rank(to_tsvector('russian', content), query) as rank
FROM articles,
to_tsquery('russian', 'базы & данные') query
WHERE to_tsvector('russian', content) @@ query
ORDER BY rank DESC;

-- Поиск с фонетикой (требует расширение)
SELECT *
FROM articles
WHERE soundex(title) = soundex('PostgreSQL');

Уровень 7: Сложные аналитические запросы

-- Когортный анализ
WITH user_cohorts AS (
    SELECT 
        id,
        DATE_TRUNC('month', created_at) as cohort_month
    FROM users
),
user_activity AS (
    SELECT 
        u.id,
        u.cohort_month,
        DATE_TRUNC('month', o.created_at) as activity_month,
        EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.created_at), u.cohort_month)) as months_since_signup
    FROM user_cohorts u
    LEFT JOIN orders o ON u.id = o.user_id
)
SELECT 
    cohort_month,
    months_since_signup,
    COUNT(DISTINCT id) as users
FROM user_activity
GROUP BY cohort_month, months_since_signup
ORDER BY cohort_month, months_since_signup;

-- Анализ воронки
WITH funnel AS (
    SELECT 
        user_id,
        'visit' as event,
        created_at,
        LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) as prev_event_time
    FROM events
    WHERE event_type IN ('visit', 'signup', 'purchase')
)
SELECT 
    user_id,
    COUNT(*) as total_events,
    COUNT(*) FILTER (WHERE event = 'visit') as visits,
    COUNT(*) FILTER (WHERE event = 'signup') as signups,
    COUNT(*) FILTER (WHERE event = 'purchase') as purchases
FROM funnel
GROUP BY user_id;

Уровень 8: Оптимизация сложных запросов

-- EXPLAIN ANALYZE для анализа производительности
EXPLAIN ANALYZE
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id
HAVING COUNT(o.id) > 10;

-- Использование MATERIALIZED VIEW для кеша сложных запросов
CREATE MATERIALIZED VIEW user_order_stats AS
SELECT 
    u.id,
    u.name,
    COUNT(o.id) as order_count,
    SUM(o.amount) 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.name;

CREATE INDEX idx_user_order_stats ON user_order_stats(order_count);

Мой опыт

В реальных проектах я работал с:

• E-commerce платформы: запросы для аналитики продаж, когортного анализа, рекомендаций
• CRM системы: иерархические запросы для организационных структур, поиск контактов с FTS
• Аналитические дашборды: сложные агрегации, оконные функции, MATERIALIZED VIEW'ы
• Системы реал-тайм: оптимизация запросов через индексы и правильное использование EXPLAIN

Главное: всегда профилируй запросы через EXPLAIN ANALYZE перед production, используй индексы правильно, и не пиши запросы, которые нельзя оптимизировать.