ClickHouse Tool — Как повысить скорость расчета Retention

база данных

Эта статья была впервые опубликована на:Уокер ИИ

Удержание пользователей является важной функцией основных платформ анализа данных. Предприятия обычно используют коэффициент удержания для измерения активности пользователей, что также является прямым показателем, который может напрямую отражать ценность функций продукта. Коэффициент удержания является одним из наиболее важных показателей для измерения. качество пользователя, поэтому расчет различных коэффициентов удержания является самым основным навыком анализа данных. Итак, вот несколько практических примеров анализа удержания пользователей.

1. Подготовьте

Понимать несколько традиционных методов расчета текущего коэффициента удержания и понимать, что ClickHouse предоставляет функцию удержания (cond1, cond2, ...) для расчета коэффициента удержания.

Создать таблицу: таблица основной информации о пользователе: login_event

CREATE TABLE login_event --用户登录事件
(
    `accountId` String COMMENT '账号的ID', --用户唯一ID
    `ds` Date COMMENT '日期' --用户登录日期
)
ENGINE = MergeTree
PARTITION BY accountId
ORDER BY accountId

Производная: вставить данные для входа пользователя за август.

--插入数据
insert into login_event values (10001,toDate('2020-08-01'), (10001,toDate('2020-08-08')), (10001,toDate('2020-08-09')), (10001,toDate('2020-08-10')), (10001,toDate('2020-08-12')),
(10001,toDate('2020-08-13')), (10001,toDate('2020-08-14')), (10001,toDate('2020-08-15')), (10001,toDate('2020-08-16')), (10001,toDate('2020-08-17')), (10001,toDate('2020-08-18')),
(10001,toDate('2020-08-20')), (10001,toDate('2020-08-22')), (10001,toDate('2020-08-23')), (10001,toDate('2020-08-24')), (10002,toDate('2020-08-20')), (10002,toDate('2020-08-22')), (10002,toDate('2020-08-23')), (10002,toDate('2020-08-01')), (10002,toDate('2020-08-11')), (10002,toDate('2020-08-12')), (10002,toDate('2020-08-13')), (10002,toDate('2020-08-20')),
(10002,toDate('2020-08-15')), (10002,toDate('2020-08-30')), (10002,toDate('2020-08-20')), (10002,toDate('2020-08-01')), (10002,toDate('2020-08-06')), (10002,toDate('2020-08-24')), (10003,toDate('2020-08-05')), (10003,toDate('2020-08-08')), (10003,toDate('2020-08-09')), (10003,toDate('2020-08-10')), (10003,toDate('2020-08-11')), (10003,toDate('2020-08-13')),
(10003,toDate('2020-08-15')), (10003,toDate('2020-08-16')), (10003,toDate('2020-08-18')), (10003,toDate('2020-08-20')), (10003,toDate('2020-08-01')), (10003,toDate('2020-08-21')),
(10003,toDate('2020-08-22')), (10003,toDate('2020-08-24')), (10003,toDate('2020-08-26')), (10003,toDate('2020-08-25')), (10003,toDate('2020-08-27')), (10003,toDate('2020-08-28')),
(10003,toDate('2020-08-29')), (10003,toDate('2020-08-30')), (10004,toDate('2020-08-01')), (10004,toDate('2020-08-02')), (10004,toDate('2020-08-03')), (10004,toDate('2020-08-04')),
(10004,toDate('2020-08-05')), (10004,toDate('2020-08-08')), (10004,toDate('2020-08-09')), (10004,toDate('2020-08-10')), (10004,toDate('2020-08-11')), (10004,toDate('2020-08-14')),
(10004,toDate('2020-08-15')), (10004,toDate('2020-08-16')), (10004,toDate('2020-08-17')), (10004,toDate('2020-08-19')), (10004,toDate('2020-08-20')), (10004,toDate('2020-08-21')),
(10004,toDate('2020-08-22')), (10004,toDate('2020-08-23')), (10004,toDate('2020-08-24')), (10004,toDate('2020-08-23')),(10004,toDate('2020-08-23')), (10004,toDate('2020-08-25')),
(10004,toDate('2020-08-27')), (10004,toDate('2020-08-30'));

2. Тематический анализ

Чтобы рассчитать второй, 3-й, 7-й, 14-й и 30-й уровень удержания активных пользователей в определенный день, мы разделим решение проблемы на три этапа:

  • Найдите ежедневных активных пользователей

  • Найдите статус входа активных пользователей в определенный день 2, 3, 6, 13 и 29 числа.

  • Подсчитайте количество активных пользователей, вошедших в систему 2-го, 3-го, 6-го, 13-го и 29-го числа в определенный день, и рассчитайте коэффициент удержания за N дней.

Решение первое:


--计算出2020-08-01活跃用户在第2、3、6、13、29日的留存数，计算出留存率
SELECT
    ds,
    count(accountIdD0) AS activeAccountNum,
    count(accountIdD1) / count(accountIdD0) AS `次留`,
    count(accountIdD3) / count(accountIdD0) AS `3留`,
    count(accountIdD7) / count(accountIdD0) AS `7留`,
    count(accountIdD14) / count(accountIdD0) AS `14留`,
    count(accountIdD30) / count(accountIdD0) AS `30留`
FROM
( --使用LEFT JOIN 找到2020-08-01当日活跃用户在第2、3、6、13、29日的登录用户
    SELECT DISTINCT
        a.ds AS ds,
        a.accountIdD0 AS accountIdD0,
        IF(b.accountId = '', NULL, b.accountId) AS accountIdD1,
        IF(c.accountId = '', NULL, c.accountId) AS accountIdD3,
        IF(d.accountId = '', NULL, d.accountId) AS accountIdD7,
        IF(e.accountId = '', NULL, e.accountId) AS accountIdD14,
        IF(f.accountId = '', NULL, f.accountId) AS accountIdD30
    FROM
    (--找出2020-08-01当日活跃用户
        SELECT DISTINCT
            ds,
            accountId AS accountIdD0
        FROM login_event
        WHERE ds = '2020-08-01'
        ORDER BY ds ASC
    ) AS a
    LEFT JOIN test.login3_event AS b ON (b.ds = addDays(a.ds, 1)) AND (a.accountIdD0 = b.accountId)
    LEFT JOIN test.login3_event AS c ON (c.ds = addDays(a.ds, 2)) AND (a.accountIdD0 = c.accountId)
    LEFT JOIN test.login3_event AS d ON (d.ds = addDays(a.ds, 6)) AND (a.accountIdD0 = d.accountId)
    LEFT JOIN test.login3_event AS e ON (e.ds = addDays(a.ds, 13)) AND (a.accountIdD0 = e.accountId)
    LEFT JOIN test.login3_event AS f ON (f.ds = addDays(a.ds, 29)) AND (a.accountIdD0 = f.accountId)
) AS temp
GROUP BY ds

结果：
-----------------------------------------
┌─────────ds─┬─activeAccountNum─┬─次留─┬──3留─┬─7留─┬─14留─┬─30留─┐
│ 2020-08-01 │                4 │ 0.25 │ 0.25 │   0 │  0.5 │ 0.75 │
└────────────┴──────────────────┴──────┴──────┴─────┴──────┴──────┘

1 rows in set. Elapsed: 0.022 sec.

Решение второе:


--判断2020-08-01活跃用户在第2、3、6、13、29日的留存数，计算出留存率，计算出留存率
SELECT DISTINCT
    b.ds AS ds,
    ifnull(countDistinct(if(a.ds = b.ds, a.accountId, NULL)), 0) AS activeAccountNum,
    ifnull(countDistinct(if(a.ds = addDays(b.ds, 1), b.accountId, NULL)) / activeAccountNum, 0) AS `次留`,
    ifnull(countDistinct(if(a.ds = addDays(b.ds, 2), b.accountId, NULL)) / activeAccountNum, 0) AS `3留`,
    ifnull(countDistinct(if(a.ds = addDays(b.ds, 6), b.accountId, NULL)) / activeAccountNum, 0) AS `7留`,
    ifnull(countDistinct(if(a.ds = addDays(b.ds, 13), b.accountId, NULL)) / activeAccountNum, 0) AS `14留`,
    ifnull(countDistinct(if(a.ds = addDays(b.ds, 29), b.accountId, NULL)) / activeAccountNum, 0) AS `30留`
FROM
  --使用INNER JOIN找出2020-08-01活跃用户在后续1～30日登录情况
(
    SELECT
        ds,
        accountId
    FROM login_event
    WHERE (ds <= addDays(toDate('2020-08-01'), 29)) AND (ds >= '2020-08-01')
) AS a
INNER JOIN
--找出2020-08-01当日活跃用户
(
    SELECT DISTINCT
        accountId,
        ds
    FROM test.login3_event
    WHERE ds = '2020-08-01'
) AS b ON a.accountId = b.accountId
GROUP BY ds

结果：
-----------------------------------------
┌─────────ds─┬─activeAccountNum─┬─次留─┬──3留─┬─7留─┬─14留─┬─30留─┐
│ 2020-08-01 │                4 │ 0.25 │ 0.25 │   0 │  0.5 │ 0.75 │
└────────────┴──────────────────┴──────┴──────┴─────┴──────┴──────┘

1 rows in set. Elapsed: 0.019 sec.

Решение третье:


--根据数组下标SUM(r[index])获取2020-08-01活跃用户在第2、3、6、13、29日的留存数，计算出留存率
SELECT
    toDate('2020-08-01') AS ds,
    SUM(r[1]) AS activeAccountNum,
    SUM(r[2]) / SUM(r[1]) AS `次留`,
    SUM(r[3]) / SUM(r[1]) AS `3留`,
    SUM(r[4]) / SUM(r[1]) AS `7留`,
    SUM(r[5]) / SUM(r[1]) AS `14留`,
    SUM(r[6]) / SUM(r[1]) AS `30留`   
FROM
--找到2020-08-01活跃用户在第2、3、6、13、29日的登录情况，1/0 => 登录/未登录
(
    WITH toDate('2020-08-01') AS tt   
SELECT
    accountId,
    retention(
      toDate(ds) = tt, 
      toDate(subtractDays(ds, 1)) = tt, 
      toDate(subtractDays(ds, 2)) = tt, 
      toDate(subtractDays(ds, 6)) = tt,
      toDate(subtractDays(ds, 13)) = tt,
      toDate(subtractDays(ds, 29)) = tt
    ) AS r
  --找出2020-08-01活跃用户在后续1～30日登录数据
FROM login_event
WHERE (ds >= '2020-08-01') AND (ds <= addDays(toDate('2020-08-01'), 29))
GROUP BY accountId
)
GROUP BY ds


结果：
-----------------------------------------
┌─────────ds─┬─activeAccountNum─┬─次留─┬──3留─┬─7留─┬─14留─┬─30留─┐
│ 2020-08-01 │                4 │ 0.25 │ 0.25 │   0 │  0.5 │ 0.75 │
└────────────┴──────────────────┴──────┴──────┴─────┴──────┴──────┘

1 rows in set. Elapsed: 0.009 sec.

3. Резюме

  • Первый метод заключается в использовании традиционного метода многотабличной ассоциации.Программисты, знакомые с ClickHouse, знают, что многотабличная ассоциация является естественным врагом ClickHouse, а скорость работы относительно низкая.

  • Второй метод использует ассоциацию таблиц, оценивает разницу дат с помощью функции ЕСЛИ и находит требуемые пользовательские данные даты.По сравнению с первым методом, ассоциация нескольких таблиц уменьшается, а скорость работы повышается.

  • Третий метод заключается в использовании собственной функции удержания ClickHouse. Функция удержания является расширенной функцией агрегирования в ClickHouse. Эта функция может принимать несколько условий. На основе результата первого условия следующие условия выполняются как 1, если не выполняются, это 0 и, наконец, возвращает массив из 1 и 0. Коэффициент удержания можно рассчитать, подсчитав количество соответствующих единиц в массиве.

Сравнивая три метода расчета, использование собственной функции удержания ClickHouse работает быстрее и эффективнее при больших объемах данных. Проблема медленной скорости и низкой эффективности в способе вычисления коэффициента удержания пользователя в известном уровне техники решается, тем самым достигается эффект повышения скорости вычисления и эффективности вычисления.

PS: Для получения дополнительной технической галантереи, пожалуйста, обратите внимание на [Публичный аккаунт | xingzhe_ai] и обсудите с ходоками!

Категории