Перейти к содержимому
Последнее обновление

RETENTION

Описание​

Функция retention принимает от 1 до 32 аргументов типа Bool, которые указывают, выполнено ли определённое условие для события. В качестве аргумента можно указать любое условие.

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

Проще говоря, первый элемент возвращаемого массива показывает, истинно или ложно условие event_1, второй элемент показывает, истинны ли оба условия event_1 и event_2, а третий элемент показывает, истинны ли оба условия event_1 и event_3, и так далее. Если event_1 ложно, возвращается массив, целиком состоящий из false.

Синтаксис​

RETENTION(<event_1> [, <event_2>, ... , <event_n>]);

Параметры​

ПараметрОписание
<event_n>Условие события с номером n, тип Bool.

Возвращаемое значение​

  • true: условие выполнено.
  • false: условие не выполнено. Массив значений типа Bool длиной не более 32. Длина итогового массива совпадает с количеством входных параметров. Если в агрегации не участвуют данные, возвращается значение NULL. Если в вычислении участвуют несколько столбцов и хотя бы один из них содержит значение NULL, строка с этим значением NULL не участвует в агрегации и отбрасывается. Для обработки значений NULL можно применить функцию IFNULL к столбцу, участвующему в вычислении. Подробнее см. в примерах ниже.

Примеры​

  1. Создание таблицы для примеров и вставка тестовых данных
CREATE TABLE retention_test(
    `uid` int COMMENT 'user id', 
    `date` datetime COMMENT 'date time' 
) DUPLICATE KEY(uid) 
DISTRIBUTED BY HASH(uid) BUCKETS AUTO
PROPERTIES ( 
    "replication_allocation" = "tag.location.default: 1"
);

INSERT into retention_test values 
(0, '2022-10-12'),
(0, '2022-10-13'),
(0, '2022-10-14'),
(1, '2022-10-12'),
(1, '2022-10-13'),
(2, '2022-10-12');
  1. Расчёт удержания пользователей
SELECT 
    uid,     
    RETENTION(date = '2022-10-12') AS r,
    RETENTION(date = '2022-10-12', date = '2022-10-13') AS r2,
    RETENTION(date = '2022-10-12', date = '2022-10-13', date = '2022-10-14') AS r3 
FROM retention_test 
GROUP BY uid 
ORDER BY uid ASC;
+------+------+--------+-----------+
| uid  | r    | r2     | r3        |
+------+------+--------+-----------+
|    0 | [1]  | [1, 1] | [1, 1, 1] |
|    1 | [1]  | [1, 1] | [1, 1, 0] |
|    2 | [1]  | [1, 0] | [1, 0, 0] |
+------+------+--------+-----------+
  1. Обработка значений NULL в особых случаях: повторное создание таблицы и вставка данных
CREATE TABLE retention_test2(
    `uid` int, 
    `flag` boolean,
    `flag2` boolean
) DUPLICATE KEY(uid) 
DISTRIBUTED BY HASH(uid) BUCKETS AUTO
PROPERTIES ( 
    "replication_allocation" = "tag.location.default: 1"
);

INSERT into retention_test2 values (0, false, false), (1, true,  NULL);

SELECT * from retention_test2;
+------+------+-------+
| uid  | flag | flag2 |
+------+------+-------+
|    0 |    1 |  NULL |
|    1 |    0 |     0 |
+------+------+-------+
  1. При выполнении вычислений для пустой таблицы данные не участвуют в агрегации, поэтому возвращаются значения NULL.
SELECT RETENTION(date = '2022-10-12') AS r FROM retention_test2 where uid is NULL;
+------+
| r    |
+------+
| NULL |
+------+
  1. В вычислении участвует только столбец flag. Поскольку при uid = 0 значение flag равно true, возвращается 1.
select retention(flag) from retention_test2;
+-----------------+
| retention(flag) |
+-----------------+
| [1]             |
+-----------------+
  1. Когда в вычислении участвуют столбцы flag и flag2, строка с uid = 0 исключается из агрегации, поскольку значение flag2 равно NULL. В агрегации участвует только строка с uid = 1, поэтому возвращается 0.
select retention(flag,flag2) from retention_test2;
+-----------------------+
| retention(flag,flag2) |
+-----------------------+
| [0, 0]                |
+-----------------------+
  1. Для обработки значений NULL можно использовать функцию IFNULL, чтобы заменить NULL на false. Это позволяет включить в агрегацию обе строки: с uid = 0 и с uid = 1.
select retention(flag,IFNULL(flag2,false)) from retention_test2;;
+-------------------------------------+
| retention(flag,IFNULL(flag2,false)) |
+-------------------------------------+
| [1, 0]                              |
+-------------------------------------+
Report issue