Полезные алгоритмы для дата-аналитика

Накопительный Lifetime

Рассчитать время «жизни» пользователя — дело нехитрое: от последней даты активности отнимаем первую, вот и Lifetime. Но что, если нам нужно увидеть, как прогрессирует LT с каждой новой активностью? То есть показать зависимость LT от времени. С этим поможет такой запрос:

SELECT user_id,
    activity_month, -- месяц активности
first_pay_date, -- дата первой активности пользователя
last_pay_in_period, -- дата последней активности в данном месяце
-- LT в днях (не количество активных дней!)
dateDiff('day', first_pay_date, last_pay_in_period)+1 AS lt_days,
-- LT в месяцах
    dateDiff('month', first_pay_date, last_pay_in_period)+1 AS lt_months
FROM (
SELECT user_id,
        toStartOfMonth(date_payed) AS activity_month,
        -- Самый первый платеж пользователя (окно по всему пользователю)
     MIN(date_payed) OVER (PARTITION BY user_id) AS first_pay_date,
        -- Последний платеж пользователя внутри текущего месяца (окно по пользователю + месяцу)
     MAX(date_payed) OVER (PARTITION BY user_id, toStartOfMonth(date_payed)) AS last_pay_in_period
FROM datamart.money
)
GROUP BY ALL
ORDER BY 1, 2

При этом учитываем, что в LT входят не только активные, но и вообще все дни или месяцы между первым и последним активными.

Транспонирование

Бывают неприятные ситуации, когда источником данных является электронная таблица, которую заполняют пользователи (например, через Google Sheets). Предположим, они заносят значения неких метрик помесячно, и им удобно, чтобы месяцы были в столбцах, вот так:

Каждый месяц добавляется новый столбец, а число строк не растет. Это ужасно.

Транспонировать такую таблицу в ClickHouse можно запросом:

SELECT
toDate(parseDateTimeBestEffort(date)) AS report_date,
metrica,
    value
FROM db.table_name
ARRAY JOIN
     ['01.01.2024', '01.02.2024', '01.03.2024', '01.04.2024', '01.05.2024', '01.06.2024', '01.07.2024',
'01.08.2024', '01.09.2024', '01.10.2024', '01.11.2024', '01.12.2024', '01.01.2025', '01.02.2025', 
'01.03.2025', '01.04.2025', '01.05.2025', '01.06.2025', '01.07.2025', '01.08.2025', '01.09.2025', 
'01.10.2025', '01.11.2025', '01.12.2025'] AS date,
     [`30.01.2024`, `24.02.2024`, `01.03.2024`, `01.04.2024`, `01.05.2024`, `01.06.2024`, 
`01.07.2024`, `01.08.2024`, `01.09.2024`, `01.10.2024`, `01.11.2024`, `01.12.2024`, `01.01.2025`, 
`01.02.2025`, `01.03.2025`, `01.04.2025`, `01.05.2025`, `01.06.2025`, `01.07.2025`, `01.08.2025`, 
`01.09.2025`, `01.10.2025`, `01.11.2025`, `01.12.2025`] AS value
WHERE value IS NOT NULL;
 

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

SELECT
  concat(
'INSERT INTO db.table_name_long ',
'SELECT toDate(parseDateTimeBestEffort(date)) AS report_date, metrica, value ',
'FROM db.table_name ARRAY JOIN [',
arrayStringConcat(arrayMap(x -> concat('''', x, ''''), groupArray(name)), ','),
'] AS date, [',
arrayStringConcat(arrayMap(x -> concat('`', x, '`'), groupArray(name)), ','),
'] AS value WHERE value IS NOT NULL'
  )
FROM system.columns
WHERE database = 'db'
  AND table = 'table_name'
  AND match(name, '^[0-9]{2}\.[0-9]{2}\.[0-9]{4}$')

На выходе получим строку с искомым SQL-запросом с нужным числом столбцов. Просто выполнится это в два шага, а не в один, что легко ставится на расписание в Airflow или другом планировщике.

«Острова» — периоды активности

Предположим, есть ситуация: некая сущность меняет свой статус время от времени, и нам нужно хранить исторические данные об этом. Мы получаем статусы ежедневно, но объектов много, и хранить их состояние на каждую дату накладно. Например, есть у пользователя услуга, которая у него то подключена, то отключена, и получаем мы только подключенные:

(Здесь будем для простоты считать, что service_id не уникален в рамках одного юзера, то есть как бы содержит в себе идентификаторы услуги и пользователя.)

Мы хотим сократить количество строк за счет указания для каждой сущности периодов активности, то есть дат начала и окончания непрерывного подключения. Эти периоды — основа известного алгоритма «Острова»:

Преобразовать первую таблицу во вторую можно с помощью такого запроса:

SELECT
             -- для каждого "острова" найдем дату начала и окончания периода
             MIN(date) AS island_start,
MAX(date) AS island_end,
service_id, island_id
FROM
(
SELECT date, service_id,
         -- считаем номера островов:
         -- если предыдущая дата - не вчера, то имеем разрыв периода
         -- и тогда это новый "остров"
     SUM(IF(DATE_DIFF('day', d_prev, date)=1, 0, 1)
          OVER (PARTITION BY service_id ORDER BY date) AS island_id
FROM
(
     SELECT date, service_id,
          -- найдем предыдущую дату подключенной услуги
         lagInFrame(date) OVER
             (PARTITION BY service_id
                 ORDER BY date
                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS d_prev
     FROM
     ( -- Берем все даты и идентификаторы подключенных в эти даты услуг из сырых данных
        SELECT DISTINCT date, service_id
        FROM db.services
     ) AS d
)
)
GROUP BY ALL
ORDER BY service_id, island_id

Склеивание пользователей по email

Предположим, у нас есть таблица registrations с пользователями нашего приложения и таблица transactions с платежами пользователей. В registrations есть уникальный идентификатор юзера user_id и его email, причем пользователь может иметь несколько аккаунтов, а почту указывать в них одну и ту же. Вопрос: если он платил только с одного аккаунта, как пометить остальные его аккаунты как финансово активные? Ведь платит человек, а не аккаунт.

Покажем на примере данных:

Таблица registrations

Таблица money

Нам нужен запрос, выбирающий пользователей, которые ни разу нам не платили. В указанном примере это пользователь с user_id = 5. Вот как выглядит такой запрос:

SELECT DISTINCT r3.user_id AS no_trans_id
FROM (
  SELECT DISTINCT r.email AS trans_email
             -- это "запятнанные" имейлы
             -- (если хоть один имейл юзера "запятнан", то остальные - тоже)  
  FROM
  ( -- Все имейлы каждого юзера и все юзеры каждого имейла за всё время:
             SELECT DISTINCT email, user_id
             FROM db.registrations
  ) AS r
  -- Кто из этих юзеров платил?
  JOIN
  (
                            SELECT DISTINCT user_id
                            FROM db.test_transactions
  ) AS t ON t.user_id=r.user_id
) AS trans
-- Возьмем всех юзеров каждого "запятнанного" имейла
LEFT JOIN
(
             SELECT DISTINCT user_id, email
             FROM db.test_registrations
) AS r2 ON r2.email=trans.trans_email
-- Возьмем только тех юзеров, чей имейл не "запятнан"
RIGHT JOIN (
  SELECT DISTINCT user_id
  FROM db.test_registrations
) AS r3 ON r3.user_id=r2.user_id
WHERE r2.user_id IS null

Бонус: тестовые датасеты

Напоследок небольшой лайфхак о том, где брать тестовые данные нужной структуры для ваших алгоритмов. Искать их в сети муторно, и далеко не всегда найдешь именно то, что нужно под конкретную задачу. Но в эпоху бурного развития нейросетей очевидным решением видится делегирование этой задачи ИИ-ассистенту.

В промпте указываем параметры нужных данных и просим сгенерировать скрипт на питоне, который сформирует датасет и SQL-запросы для вставки его в СУБД. Эти запросы просто берем и запускаем.

А если данных должно быть так много, что запрос станет некомфортно огромным, можно в промпте же запросить вставку таблицы в СУБД прямо из питона.

Например, вот такой промпт удовлетворил мою потребность в создании таблицы с тестовыми данными веб-аналитики (скормил его Claude Sonnet):

'''
Я хочу подсоединить к данным о рекламных кампаниях данные о целевых действиях (регистрациях на сайте).
Сгенерируй датасет системы веб-аналитики с полями:
Дата и время(event_datetime) в формате YYYY-MM-DD HH:mm:ss
Кампания (utm_campaign)
Источник (utm_source)
Канал (utm_medium)
Событие (event_name)
Идентификатор клиента посетителя (client_id)
Идентификатор пользователя сайта (user_id)
 
Даты — в диапазоне от 2026-03-01 до 2026-03-31.
Количество — по 30–40 строк на каждый день
Кампания — часть значений пуста, а часть заполнена нашими кампаниями
Источник — google и другие источники (bing, (direct), адреса внешних сайтов и другие популярные).
Для наших кампаний — обязательно google
Канал — cpc для наших кампаний и соответствующие medium для других источников (organic, referral, (none) и т. д.).
Событие — не менее 1 page view на каждого client_id плюс другие события.
Часть событий — registration, в том числе для наших кампаний (это и есть целевое событие).
Сделай так, чтобы некоторые client_id имелись в разных днях, будто посетитель возвращался на сайт.
Поле user_id должно быть заполнено только для тех client_id, у которых уже случилось событие registration, 
т. е. в строке этого события и позже.
'''

Резюме

Мы рассмотрели 4 лайфхака-алгоритма, решающих различные задачи аналитики данных на SQL:

• вычисление накопительного Lifetime в разрезе по месяцам активности;
• транспонирование таблиц с произвольным количеством столбцов;
• преобразование длинных таблиц с историческими данными в короткие с помощью механизма «Острова»;
• склеивание мультиаккаунтов пользователей по email, чтобы выявить неактивных.

Дополнительно показали способ формирования тестовых датасетов с помощью нейропомощников.