A/B-тесты в PostgreSQL13.03.2025 14:16

Привет, Хабр! Сегодня разберём, как реализовать 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 марта «Базовый сбор требований в работе дата-аналитика».

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

© Habrahabr.ru