Перейти на PRO

Какой был самый интересный кейс работы с SQL?

Тип вопроса: SQL и базы данных Вероятность: 33% Категория: Data Инженер

Ответ

Самый интересный кейс — это рефакторинг и оптимизация монолитного отчета для финансового департамента, который выполнялся более 40 минут. Запрос представлял собой цепочку из 12 вложенных подзапросов и LEFT JOIN по неуникальным полям.

Задача: Рассчитать помесячную динамику LTV (Lifetime Value) для когорт пользователей, с разбивкой по каналам привлечения, учитывая только тех, кто совершил повторные покупки, и исключая тестовые аккаунты.

Исходная проблема: Полное сканирование огромных таблиц orders и users, множественные временные таблицы на диске, неправильные оценки кардинальности оптимизатором.

Мое решение:

  1. Разбил логику на четкие CTE (Common Table Expressions) для читаемости и повторного использования.
  2. Заменил подзапросы в SELECT и JOIN на предварительно агрегированные CTE, чтобы избежать многократного выполнения одной логики.
  3. Ввел оконные функции для расчета накопленного итога внутри когорты, что устранило необходимость в самосоединении (self-join).
  4. Добавил покрывающие индексы (covering indexes) для колонок, используемых в WHERE, GROUP BY и JOIN.

Ключевой фрагмент оптимизированного запроса (на PostgreSQL):

WITH user_cohorts AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month,
        acquisition_channel
    FROM users
    WHERE is_test_account = FALSE
    GROUP BY user_id, acquisition_channel
),
user_revenue AS (
    SELECT 
        o.user_id,
        DATE_TRUNC('month', o.created_at) AS revenue_month,
        SUM(o.amount) AS monthly_revenue
    FROM orders o
    INNER JOIN user_cohorts uc ON o.user_id = uc.user_id
    WHERE o.status = 'completed'
    GROUP BY o.user_id, DATE_TRUNC('month', o.created_at)
    HAVING COUNT(o.id) > 1 -- Только с повторными покупками
)
SELECT 
    uc.cohort_month,
    uc.acquisition_channel,
    ur.revenue_month,
    SUM(ur.monthly_revenue) OVER (
        PARTITION BY uc.cohort_month, uc.acquisition_channel, ur.user_id 
        ORDER BY ur.revenue_month
    ) AS cumulative_ltv,
    COUNT(DISTINCT ur.user_id) AS active_users
FROM user_cohorts uc
JOIN user_revenue ur ON uc.user_id = ur.user_id
ORDER BY 1, 2, 3;

Результат: Время выполнения сократилось с 40+ минут до ~8 секунд. Это было достигнуто за счет:

  • Резкого уменьшения операций ввода-вывода (I/O).
  • Перехода от Nested Loops Joins к более эффективным Hash Joins.
  • Позволило бизнесу запускать отчет ad-hoc, а не раз в сутки.

Ответ 18+ 🔞

А, вспомнил один случай, просто ёперный театр! Задача была — разобраться с одним монструозным отчётом для финансистов. Этот запрос, блядь, выполнялся сорок с лишним минут, представляешь? Сорок! Чувак, который его писал, видимо, был большой любитель головоломок, потому что там была цепочка из двенадцати вложенных подзапросов, и всё это склеивалось через LEFT JOIN по каким-то левым полям, от которых у оптимизатора волнение ебать начиналось.

Суть была — посчитать помесячный LTV по когортам, да ещё с разбивкой по каналам, да ещё только для тех, кто покупал больше одного раза, и тестовые аккаунты нахуй выкинуть. В общем, стандартная хотелка бизнеса, но реализованная так, будто на дворе 2002-й год и базы на дискетах.

Проблемы были классические: запрос сканировал в лоб таблицы orders и users целиком, гонял терабайты промежуточных данных на диск, а планировщик запросов смотрел на это всё и говорил «да похуй», выдавая абсолютно бредовый план выполнения.

Что я сделал? Ну, во-первых, взял и разобрал эту пиздопроебибну конструкцию на нормальные, читаемые блоки — CTE. Вместо того чтобы десять раз в разных местах вычислять одно и то же, я вынес логику в отдельные «кирпичики». Потом, самое главное — убрал эти ебушки-воробушки из SELECT и JOIN, заменив их на уже готовые, агрегированные данные из этих самых CTE.

Третье — применил оконные функции. Раньше, чтобы посчитать накопительный итог для когорты, запрос делал самосоединение (self-join), что по сложности было хуже, чем хуй с горы. Я это заменил на SUM() OVER (PARTITION BY ... ORDER BY ...). И, конечно, не забыл про индексы. Накидал покрывающих индексов именно на те поля, которые участвуют в отборах (WHERE), группировках (GROUP BY) и соединениях (JOIN).

Вот скелет того, во что это превратилось:

WITH user_cohorts AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month,
        acquisition_channel
    FROM users
    WHERE is_test_account = FALSE
    GROUP BY user_id, acquisition_channel
),
user_revenue AS (
    SELECT 
        o.user_id,
        DATE_TRUNC('month', o.created_at) AS revenue_month,
        SUM(o.amount) AS monthly_revenue
    FROM orders o
    INNER JOIN user_cohorts uc ON o.user_id = uc.user_id
    WHERE o.status = 'completed'
    GROUP BY o.user_id, DATE_TRUNC('month', o.created_at)
    HAVING COUNT(o.id) > 1 -- Только с повторными покупками
)
SELECT 
    uc.cohort_month,
    uc.acquisition_channel,
    ur.revenue_month,
    SUM(ur.monthly_revenue) OVER (
        PARTITION BY uc.cohort_month, uc.acquisition_channel, ur.user_id 
        ORDER BY ur.revenue_month
    ) AS cumulative_ltv,
    COUNT(DISTINCT ur.user_id) AS active_users
FROM user_cohorts uc
JOIN user_revenue ur ON uc.user_id = ur.user_id
ORDER BY 1, 2, 3;

И что ты думаешь? Запрос, который раньше мучился 40+ минут, стал отрабатывать примерно за 8 секунд. Я сам от себя охуел, когда увидел. Эффект достигнут за счёт того, что мы убили овердохуища операций ввода-вывода, заставили СУБД делать хэш-джойны вместо тупых вложенных циклов, и в целом оптимизатор перестал быть хитрой жопой и начал работать как надо. Финансисты теперь этот отчёт могут дергать когда захотят, а не ждать ночного запуска. Вот так-то.

© ХакСобесов, 2026
support@hacksobesov.com