A/B-тесты в PostgreSQL

Привет, Хабр! Сегодня разберём, как реализовать A/B‑тестирование на чистом PostgreSQL, без выгрузки данных в сторонние системы. Рассмотрим полный цикл: от структуры таблиц и оптимизации запросов до статистического анализа (T‑тест, Манна‑Уитни, байесовские методы) и визуализации результатов.

Постановка задачи и проектирование базы

Начнём с основ. Представим, что мы отвечаем за A/B‑тест нового лендинга. Заказчик требует реального времени, а выгрузка в сторонние BI‑системы — это вообще не про нас. Так что нужно построить базу, где все данные будут сразу готовы к анализу.

Для начала создадим таблицу, которая будет собирать все события пользователей. Вот как выглядит базовая структура:

-- Создаем таблицу для хранения событий A/B-теста CREATE TABLE ab_test_events (     event_id      SERIAL PRIMARY KEY,      -- Уникальный идентификатор события     user_id       INTEGER NOT NULL,          -- Идентификатор пользователя     variant       CHAR(1) NOT NULL,          -- Вариант теста: 'A' или 'B'     event_time    TIMESTAMPTZ NOT NULL DEFAULT NOW(),  -- Время события     event_type    TEXT NOT NULL,             -- Тип события (например, 'page_view', 'click', 'submit')     event_payload JSONB NOT NULL           -- Дополнительные данные (например, время до конверсии, метаданные) );

Используем JSONB для event_payload — потому что иногда пользователи творят что угодно: один день клик, другой — заполняют анкету с кучей метаданных. Гибкость и масштабируемость важнее, чем жесткая структура.

Пример данных:

INSERT INTO ab_test_events (user_id, variant, event_type, event_payload) VALUES ('550e8400-e29b-41d4-a716-446655440000', 'A', 'submit', '{"conversion": "1"}'), ('550e8400-e29b-41d4-a716-446655440001', 'B', 'submit', '{"conversion": "0"}'), ('550e8400-e29b-41d4-a716-446655440002', 'A', 'submit', '{"conversion": "1"}'), ('550e8400-e29b-41d4-a716-446655440003', 'B', 'submit', '{"conversion": "1"}');

Никакой крутой код не спасёт ситуацию, если запросы выполняются медленно. Поэтому создаем GIN‑индекс для быстрого поиска по JSONB.

-- Индекс для ускорения поиска по полям внутри JSONB CREATE INDEX idx_event_payload ON ab_test_events USING gin (event_payload);

Агрегация данных:

После того как данные начали сыпаться, самое время собрать статистику. Ведь что толку в красивой таблице, если мы не можем понять, какой вариант лендинга работает лучше? Для нас критичен показатель конверсии — событие типа submit означает успешную регистрацию.

Приведу пример запроса, который группирует данные по вариантам теста и вычисляет конверсию:

-- Агрегируем данные по вариантам A/B-теста SELECT      variant,  -- Группируем по варианту ('A' или 'B')     COUNT(*) AS total_events,  -- Общее количество событий     COUNT(*) FILTER (WHERE event_type = 'submit') AS total_conversions,  -- События регистрации     COUNT(*) FILTER (WHERE event_type = 'submit')::numeric / COUNT(*) AS conversion_rate  -- Расчет конверсии FROM ab_test_events GROUP BY variant;

Фильтрация через конструкцию FILTER позволяет сразу получить нужные подсчеты.

Иногда хочется знать не только конверсию, но и дополнительные детали — например, сколько времени пользователи тратят на регистрацию. Допустим, параметр time_to_submit хранится в event_payload. Тогда запрос будет таким:

-- Вычисляем среднее время до регистрации для каждой группы SELECT      variant,     AVG((event_payload->>'time_to_submit')::numeric) AS avg_time_to_submit  -- Приводим строку к числу FROM ab_test_events WHERE event_payload ? 'time_to_submit'  -- Проверяем наличие ключа в JSONB GROUP BY variant;

Оператор ? позволяет отфильтровать данные и не портить статистику нулевыми значениями.

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

SELECT user_id, COUNT(*)  FROM ab_test_events WHERE event_type = 'submit' GROUP BY user_id HAVING COUNT(*) > 10;

Если у одного пользователя слишком много событий, возможно, он тестирует систему, а не является реальным пользователем.

Иногда вариант A имеет больше пользователей, чем B. Проверяем:

SELECT variant, COUNT(*) AS users FROM ab_test_events WHERE event_type = 'submit' GROUP BY variant;

Если разница > 10%, тесты могут быть смещены.

Если пользователи видели A и B, это может создать шум в данных:

SELECT user_id, COUNT(DISTINCT variant)  FROM ab_test_events WHERE event_type = 'submit' GROUP BY user_id HAVING COUNT(DISTINCT variant) > 1;

Таких пользователей можно исключить из анализа.

Освоить мощные навыки анализа данных (анализ требований + статистика + BI) можно на курсе «Аналитик данных».

Статистический анализ: тесты, доверительные интервалы и байесовский разбор

T-тест для оценки разницы в конверсии

T‑тест применяется, если распределение данных нормально. Проверим это:

Прежде чем применять T‑тест, важно убедиться, что данные нормально распределены:

SELECT variant, AVG((event_payload->>'conversion')::numeric) AS avg_conversion,               STDDEV((event_payload->>'conversion')::numeric) AS stddev_conversion FROM ab_test_events WHERE event_type = 'submit' GROUP BY variant;

Если стандартное отклонение слишком велико, T‑тест может быть неточным.

Теперь применим T‑тест:

WITH stats AS (     SELECT          variant,         AVG((event_payload->>'conversion')::numeric) AS avg_conversion,         VARIANCE((event_payload->>'conversion')::numeric) AS variance,         COUNT(*) AS cnt     FROM ab_test_events     WHERE event_type = 'submit'     GROUP BY variant ) SELECT     a.avg_conversion AS conversion_A,     b.avg_conversion AS conversion_B,     (a.avg_conversion - b.avg_conversion) /      sqrt((a.variance / a.cnt) + (b.variance / b.cnt)) AS t_statistic FROM stats a, stats b WHERE a.variant = 'A' AND b.variant = 'B';

Если t_statistic > 2 или < -2, разница между группами A и B статистически значима. Если t_statistic ближе к 0, различие случайное.

Манна-Уитни

Если данные не подчиняются нормальному распределению (например, если конверсии редкие или неравномерные), применяется тест Манна‑Уитни.

Перед запуском теста, проверим:

SELECT variant, COUNT(*) AS cnt,         SUM((event_payload->>'conversion')::numeric) AS total_conversions FROM ab_test_events WHERE event_type = 'submit' GROUP BY variant;

Если между группами A и B большая разница в количестве наблюдений, Манна‑Уитни предпочтительнее T‑теста.

Манна‑Уитни работает по ранжированию значений:

WITH ranked AS (   SELECT        user_id,       variant,       (event_payload->>'conversion')::numeric AS conversion,       RANK() OVER (ORDER BY (event_payload->>'conversion')::numeric) AS rnk   FROM ab_test_events   WHERE event_type = 'submit' ) SELECT      variant,      SUM(rnk) AS total_rank,      COUNT(*) AS cnt FROM ranked GROUP BY variant;

Если суммы рангов сильно различаются, разница между вариантами значима.

Байесовский анализ

Байесовский анализ не только оценивает вероятность, но и показывает распределение вероятности конверсии.

Сначала вычислим 95% доверительный интервал:

SELECT      variant,     PERCENTILE_CONT(0.025) WITHIN GROUP (ORDER BY (event_payload->>'conversion')::numeric) AS lower_bound,     PERCENTILE_CONT(0.975) WITHIN GROUP (ORDER BY (event_payload->>'conversion')::numeric) AS upper_bound FROM ab_test_events WHERE event_type = 'submit' GROUP BY variant;

Теперь применим PL/Python для моделирования вероятностей.

Создадим функцию:

CREATE OR REPLACE FUNCTION beta_distribution(alpha numeric, beta numeric) RETURNS TABLE(x numeric, pdf numeric) AS $$     import numpy as np     from scipy.stats import beta as beta_dist     x = np.linspace(0, 1, 100)     pdf = beta_dist.pdf(x, alpha, beta)     return list(zip(x, pdf)) $$ LANGUAGE plpython3u;

Вызовем функцию:

SELECT * FROM beta_distribution(5, 2);

Чем выше alpha и beta, тем уже доверительный интервал.

Если распределения сильно пересекаются, разница между группами незначительна.

Если variant A имеет высокий пик в beta‑распределении, он значительно лучше variant B.

Оптимизация производительности

Оптимизация — это не разовое действие, а постоянный процесс.

Partial indexes и BRIN-индексы

Если ваша таблица растёт до гигантских размеров, индексировать всё подряд не стоит. Частичные индексы — наше всё для актуальных данных.

-- Частичный индекс для ускорения выборок по последним 30 дням CREATE INDEX idx_recent_events ON ab_test_events (event_time) WHERE event_time > NOW() - INTERVAL '30 days';

Если данных слишком много, можно применить BRIN‑индексы, которые работают быстрее и экономят место:

-- BRIN-индекс для эффективного сканирования временных данных CREATE INDEX idx_brin_time ON ab_test_events USING brin (event_time);

GROUPING SETS: многомерная агрегация в одном запросе

Когда требуется собрать несколько уровней агрегации (например, по дням и по вариантам), GROUPING SETS приходят на помощь:

-- Используем GROUPING SETS для получения детальной и сводной статистики SELECT      variant,     DATE_TRUNC('day', event_time) AS day,     COUNT(*) AS total_events FROM ab_test_events GROUP BY GROUPING SETS (     (variant, DATE_TRUNC('day', event_time)),  -- Статистика по каждому дню для каждого варианта     (variant)                                  -- Общая статистика по каждому варианту );

GROUPING SETS позволяют одним запросом получить сразу несколько уровней агрегации.

Визуализация результатов

Первым делом подключаемся к PostgreSQL, выполняем запрос и загружаем данные в DataFrame.

import psycopg2 import pandas as pd import matplotlib.pyplot as plt  # Устанавливаем соединение с базой данных conn = psycopg2.connect(     host="localhost",         # Адрес сервера базы данных     database="production_db", -- Имя базы данных     user="prod_user",         # Пользователь базы данных     password="supersecret"    # Пароль (в продакшене храните в секьюрном хранилище!) )  # SQL-запрос для получения агрегированных данных по вариантам query = """ WITH aggregated AS (     SELECT          variant,         COUNT(*) AS total_events,         COUNT(*) FILTER (WHERE event_type = 'submit') AS total_conversions,         COUNT(*) FILTER (WHERE event_type = 'submit')::numeric / COUNT(*) AS conversion_rate,         STDDEV((event_payload->>'conversion')::numeric) AS std_dev     FROM ab_test_events     GROUP BY variant ) SELECT * FROM aggregated; """  # Загружаем данные в DataFrame df = pd.read_sql(query, conn) conn.close()  # Выводим данные для проверки print(df)

А теперь построим график:

import matplotlib.pyplot as plt import pandas as pd  # Симулируем данные, как если бы они были получены из запроса data = {     'variant': ['A', 'B'],     'conversion_rate': [0.15, 0.22],  # Пример конверсии: 15% для A и 22% для B     'std_dev': [0.02, 0.03]           # Пример стандартного отклонения } df = pd.DataFrame(data)  # Создаем график plt.figure(figsize=(10, 6)) bars = plt.bar(df['variant'], df['conversion_rate'], yerr=df['std_dev'], capsize=8)  # Настраиваем оси и заголовок plt.xlabel('Вариант теста') plt.ylabel('Конверсия') plt.title('Результаты A/B-теста: Конверсия по вариантам') plt.grid(axis='y', linestyle='--', alpha=0.7) plt.ylim(0, 0.3)  # Устанавливаем лимиты по оси Y  # Добавляем числовые аннотации над столбиками for bar in bars:     height = bar.get_height()     plt.text(bar.get_x() + bar.get_width()/2, height + 0.01, f'{height:.2%}',               ha='center', va='bottom')  plt.show()

---

В заключение всем, кому интересна сфера аналитики данных, рекомендую посетить открытый урок 25 марта «Базовый сбор требований в работе дата-аналитика».

Рассмотрим жизненный цикл задачи в анализе данных, обсудим, какие ключевые вопросы необходимо задать и как грамотно зафиксировать ответы. Также разберем, как справляться с возражениями и избегать лишнего стресса в процессе. Записаться