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

window_funnel

Описание​

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

Эта функция работает согласно следующим правилам:

  • Подсчет начинается с первого события в цепочке событий. Если первое событие найдено, счетчик событий устанавливается в 1 и начинается скользящее окно. Если первое событие не найдено, возвращается 0.

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

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

Синтаксис​

BIGINT window_funnel(BIGINT window, DATE|DATETIME time, INT mode, array[cond1, cond2, ..., condN])

Параметры​

  • window: Длина скользящего окна. Поддерживаемый тип данных - BIGINT. Единица измерения зависит от параметра time. Если тип данных time - DATE, единица измерения - дни. Если тип данных time - DATETIME, единица измерения - секунды.

  • time: Столбец, содержащий временные метки. Поддерживаются типы DATE и DATETIME.

  • mode: Режим фильтрации цепочки событий. Поддерживаемый тип данных - INT. Диапазон значений: 0, 1, 2.

    • 0 - значение по умолчанию, которое указывает на обычный расчет воронки.
    • 1 указывает на режим DEDUPLICATION, то есть отфильтрованная цепочка событий не может содержать повторяющиеся события. Предположим, параметр array - [event_type = 'A', event_type = 'B', event_type = 'C', event_type = 'D'], а исходная цепочка событий - "A-B-C-B-D". Событие B повторяется, и отфильтрованная цепочка событий - "A-B-C".
    • 2 указывает на режим FIXED, то есть отфильтрованная цепочка событий не может содержать события, нарушающие указанную последовательность. Предположим, используется предыдущий параметр array, а исходная цепочка событий - "A-B-D-C". Событие D прерывает последовательность, и отфильтрованная цепочка событий - "A-B".
    • 4 указывает на режим INCREASE, что означает, что отфильтрованные события должны иметь строго возрастающие временные метки. Дублирующая временная метка нарушает цепочку событий. Этот режим поддерживается начиная с версии 1.5.0.

  • array: Определенная цепочка событий. Должна быть массивом.

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

Возвращает значение типа BIGINT.

Примеры​

Пример 1: Вычислить максимальное количество последовательных событий на основе uid. Скользящее окно составляет 1800 секунд, а режим фильтрации - 0.

В этом примере используется таблица action, в которой данные отсортированы по uid.

mysql> select * from action;
+------+------------+---------------------+
| uid  | event_type | time                |
+------+------------+---------------------+
| 1    | Browse     | 2020-01-02 11:00:00 |
| 1    | Click      | 2020-01-02 11:10:00 |
| 1    | Order      | 2020-01-02 11:20:00 |
| 1    | Pay        | 2020-01-02 11:30:00 |
| 1    | Browse     | 2020-01-02 11:00:00 |
| 2    | Order      | 2020-01-02 11:00:00 |
| 2    | Pay        | 2020-01-02 11:10:00 |
| 3    | Browse     | 2020-01-02 11:20:00 |
| 3    | Click      | 2020-01-02 12:00:00 |
| 4    | Browse     | 2020-01-02 11:50:00 |
| 4    | Click      | 2020-01-02 12:00:00 |
| 5    | Browse     | 2020-01-02 11:50:00 |
| 5    | Click      | 2020-01-02 12:00:00 |
| 5    | Order      | 2020-01-02 11:10:00 |
| 6    | Browse     | 2020-01-02 11:50:00 |
| 6    | Click      | 2020-01-02 12:00:00 |
| 6    | Order      | 2020-01-02 12:10:00 |
+------+------------+---------------------+
17 rows in set (0.01 sec)

Выполните следующий запрос:

select uid,
       window_funnel(1800,time,0,[event_type='Browse', event_type='Click', 
        event_type='Order', event_type='Pay']) AS level
from action
group by uid
order by uid; 
+------+-------+
| uid  | level |
+------+-------+
| 1    |     4 |
| 2    |     0 |
| 3    |     1 |
| 4    |     2 |
| 5    |     2 |
| 6    |     3 |
+------+-------+

Описание результатов:

  • Соответствующая цепочка событий для uid = 1 - "Browse-Click-Order-Pay", возвращается 4. Время последнего события "Browse" (2020-01-02 11:00:00) не соответствует условию и не учитывается.

  • Цепочка событий для uid = 2 не начинается с первого события "Browse", возвращается 0.

  • Соответствующая цепочка событий для uid = 3 - "Browse", возвращается 1. Событие "Click" превышает временное окно в 1800 секунд и не учитывается.

  • Соответствующая цепочка событий для uid = 4 - "Browse-Click", возвращается 2.

  • Соответствующая цепочка событий для uid = 5 - "Browse-Click", возвращается 2. Событие "Order" (2020-01-02 11:10:00) не принадлежит цепочке событий и не учитывается.

  • Соответствующая цепочка событий для uid = 6 - "Browse-Click-Order", возвращается 3.

Пример 2: Вычислить максимальное количество последовательных событий на основе uid. Скользящее окно составляет 1800 секунд, используются режимы фильтрации 0 и 1.

В этом примере используется таблица action1, в которой данные отсортированы по time.

mysql> select * from action1 order by time;
+------+------------+---------------------+ 
| uid  | event_type | time                |     
+------+------------+---------------------+
| 1    | Browse     | 2020-01-02 11:00:00 |
| 2    | Browse     | 2020-01-02 11:00:01 |
| 1    | Click      | 2020-01-02 11:10:00 |
| 1    | Order      | 2020-01-02 11:29:00 |
| 1    | Click      | 2020-01-02 11:29:50 |
| 1    | Pay        | 2020-01-02 11:30:00 |
| 1    | Click      | 2020-01-02 11:40:00 |
+------+------------+---------------------+
7 rows in set (0.03 sec)

Выполните следующий запрос:

select uid,
       window_funnel(1800,time,0,[event_type='Browse', 
        event_type='Click', event_type='Order', event_type='Pay']) AS level
from action1
group by uid
order by uid;
+------+-------+
| uid  | level |
+------+-------+
| 1    |     4 |
| 2    |     1 |
+------+-------+
2 rows in set (0.02 sec)

Для uid = 1 событие "Click" (2020-01-02 11:29:50) является повторяющимся событием, но оно все равно учитывается, поскольку используется режим 0. Поэтому возвращается 4.

Измените mode на 1 и выполните запрос снова.

+------+-------+
| uid  | level |
+------+-------+
| 1    |     3 |
| 2    |     1 |
+------+-------+
2 rows in set (0.05 sec)

Самая длинная цепочка событий, отфильтрованная после дедупликации, - "Browse-Click-Order", возвращается 3.

Пример 3: Вычислить максимальное количество последовательных событий на основе uid. Скользящее окно составляет 1900 секунд, используются режимы фильтрации 0 и 2.

В этом примере используется таблица action2, в которой данные отсортированы по time.

mysql> select * from action2 order by time;
+------+------------+---------------------+
| uid  | event_type | time                |
+------+------------+---------------------+
| 1    | Browse     | 2020-01-02 11:00:00 |
| 2    | Browse     | 2020-01-02 11:00:01 |
| 1    | Click      | 2020-01-02 11:10:00 |
| 1    | Pay        | 2020-01-02 11:30:00 |
| 1    | Order      | 2020-01-02 11:31:00 |
+------+------------+---------------------+
5 rows in set (0.01 sec)

Выполните следующий запрос:

select uid,
       window_funnel(1900,time,0,[event_type='Browse', event_type='Click', 
        event_type='Order', event_type='Pay']) AS level
from action2
group by uid
order by uid;
+------+-------+
| uid  | level |
+------+-------+
| 1    |     3 |
| 2    |     1 |
+------+-------+
2 rows in set (0.02 sec)

Для uid = 1 возвращается 3, поскольку используется режим 0 и событие "Pay" (2020-01-02 11:30:00) не нарушает цепочку событий.

Измените mode на 2 и выполните запрос снова.

select uid,
       window_funnel(1900,time,2,[event_type='Browse', event_type='Click', 
        event_type='Order', event_type='Pay']) AS level
from action2
group by uid
order by uid;
+------+-------+
| uid  | level |
+------+-------+
| 1    |     2 |
| 2    |     1 |
+------+-------+
2 rows in set (0.06 sec)

Возвращается 2, поскольку событие "Pay" нарушает цепочку событий и счетчик событий останавливается. Отфильтрованная цепочка событий - "Browse-Click".

Пример 4: Вычислить максимальное количество последовательных событий на основе uid. Скользящее окно составляет 1900 секунд, используются режимы фильтрации 0 и 4.

В этом примере используется таблица action3, в которой данные отсортированы по time.

select * from action3 order by time;
+------+------------+---------------------+
| uid  | event_type | time                |
+------+------------+---------------------+
| 1    | Browse     | 2020-01-02 11:00:00 |
| 1    | Click      | 2020-01-02 11:00:01 |
| 2    | Browse     | 2020-01-02 11:00:03 |
| 1    | Order      | 2020-01-02 11:00:31 |
| 2    | Click      | 2020-01-02 11:00:03 |
| 2    | Order      | 2020-01-02 11:01:03 |
+------+------------+---------------------+
3 rows in set (0.02 sec)

Выполните следующий запрос:

select uid,
       window_funnel(1900,time,0,[event_type='Browse', event_type='Click',
        event_type='Order']) AS level
from action3
group by uid
order by uid;
+------+-------+
| uid  | level |
+------+-------+
|    1 |     3 |
|    2 |     3 |
+------+-------+

Для uid = 1 и uid = 2 возвращается 3.

Измените mode на 4 и выполните запрос снова.

select uid,
       window_funnel(1900,time,4,[event_type='Browse', event_type='Click',
        event_type='Order']) AS level
from action3
group by uid
order by uid;
+------+-------+
| uid  | level |
+------+-------+
|    1 |     3 |
|    2 |     1 |
+------+-------+
1 row in set (0.02 sec)

Для uid = 2 возвращается 1, поскольку используется режим 4 (строго возрастающий). "Click" происходит в ту же секунду, что и "BROWSE". Поэтому "Click" и "Order" не учитываются.

Ключевые слова​

window funnel, funnel, window_funnel