Параметрические агрегатные функции

Документация по параметрическим агрегатным функциям

Некоторые агрегатные функции могут принимать не только столбцы-аргументы (используемые для сжатия), но и набор параметров — констант для инициализации. В синтаксисе используются две пары скобок вместо одной: первая — для параметров, вторая — для аргументов.

​

histogram

Вычисляет адаптивную гистограмму. Не гарантирует точные результаты.

histogram(number_of_bins)(values)

Функция использует алгоритм A Streaming Parallel Decision Tree Algorithm. Границы интервалов гистограммы корректируются по мере поступления в функцию новых данных. В общем случае ширина интервалов неодинакова.

Аргументы

values — выражение, которое возвращает входные значения.

Параметры

number_of_bins — Верхний предел количества интервалов в гистограмме. Функция автоматически вычисляет количество интервалов. Она пытается достичь указанного количества интервалов, но, если это не удается, использует меньшее количество.

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

• Array из Tuple следующего формата:

  [(lower_1, upper_1, height_1), ... (lower_N, upper_N, height_N)]
  
  • lower — Нижняя граница интервала.
  • upper — Верхняя граница интервала.
  • height — Вычисленная высота интервала.

Пример

SELECT histogram(5)(number + 1)
FROM (
    SELECT *
    FROM system.numbers
    LIMIT 20
)

┌─histogram(5)(plus(number, 1))───────────────────────────────────────────┐
│ [(1,4.5,4),(4.5,8.5,4),(8.5,12.75,4.125),(12.75,17,4.625),(17,20,3.25)] │
└─────────────────────────────────────────────────────────────────────────┘

Гистограмму можно визуализировать с помощью функции bar, например:

WITH histogram(5)(rand() % 100) AS hist
SELECT
    arrayJoin(hist).3 AS height,
    bar(height, 0, 6, 5) AS bar
FROM
(
    SELECT *
    FROM system.numbers
    LIMIT 20
)

┌─height─┬─bar───┐
│  2.125 │ █▋    │
│   3.25 │ ██▌   │
│  5.625 │ ████▏ │
│  5.625 │ ████▏ │
│  3.375 │ ██▌   │
└────────┴───────┘

В этом случае следует помнить, что границы интервалов гистограммы неизвестны.

​

sequenceMatch

Проверяет, есть ли в последовательности цепочка событий, соответствующая шаблону регулярного выражения.

Синтаксис

sequenceMatch(pattern)(timestamp, cond1, cond2, ...)

События, происходящие в одну и ту же секунду, могут располагаться в последовательности в неопределённом порядке, что может повлиять на результат.

Аргументы

• timestamp — Столбец, содержащий временные данные. Обычно используются типы данных Date и DateTime. Также можно использовать любой из поддерживаемых типов данных UInt.

• cond1, cond2 — Условия, описывающие цепочку событий. Тип данных: UInt8. Можно передать до 32 аргументов-условий. Функция учитывает только события, описанные этими условиями. Если последовательность содержит данные, не описанные ни одним условием, функция их пропускает.

Параметры

• pattern — Строка шаблона регулярного выражения. См. Синтаксис шаблона.

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

• 1, если шаблон регулярного выражения совпал.
• 0, если шаблон регулярного выражения не совпал.

Тип: UInt8.

​

Синтаксис шаблона

• (?N) — Соответствует аргументу условия с номером N. Условия нумеруются в диапазоне [1, 32]. Например, (?1) соответствует аргументу, переданному в параметр cond1.

• .* — Соответствует любому количеству событий. Для сопоставления с этим элементом шаблона условные аргументы не требуются.

• (?t operator value) — Задаёт интервал в секундах, который должен разделять два события. Например, шаблон (?1)(?t>1800)(?2) соответствует событиям, между которыми проходит более 1800 секунд. Между этими событиями может находиться произвольное количество любых событий. Можно использовать операторы >=, >, <, <=, ==.

Примеры

Рассмотрим данные в таблице t:

┌─time─┬─number─┐
│    1 │      1 │
│    2 │      3 │
│    3 │      2 │
└──────┴────────┘

Выполните запрос:

SELECT sequenceMatch('(?1)(?2)')(time, number = 1, number = 2) FROM t

┌─sequenceMatch('(?1)(?2)')(time, equals(number, 1), equals(number, 2))─┐
│                                                                     1 │
└───────────────────────────────────────────────────────────────────────┘

Функция нашла цепочку событий, в которой число 2 следует за числом 1. Число 3 между ними она пропустила, поскольку оно не описано как событие. Если мы хотим учитывать это число при поиске цепочки событий, приведенной в примере, следует задать для него условие.

SELECT sequenceMatch('(?1)(?2)')(time, number = 1, number = 2, number = 3) FROM t

┌─sequenceMatch('(?1)(?2)')(time, equals(number, 1), equals(number, 2), equals(number, 3))─┐
│                                                                                        0 │
└──────────────────────────────────────────────────────────────────────────────────────────┘

В этом случае функция не смогла найти цепочку событий, соответствующую шаблону регулярного выражения, потому что событие с номером 3 произошло между 1 и 2. Если бы в том же случае мы проверили условие для числа 4, последовательность соответствовала бы шаблону регулярного выражения.

SELECT sequenceMatch('(?1)(?2)')(time, number = 1, number = 2, number = 4) FROM t

┌─sequenceMatch('(?1)(?2)')(time, equals(number, 1), equals(number, 2), equals(number, 4))─┐
│                                                                                        1 │
└──────────────────────────────────────────────────────────────────────────────────────────┘

См. также

• sequenceCount

​

sequenceCount

Подсчитывает количество цепочек событий, соответствующих шаблону регулярного выражения. Функция ищет неперекрывающиеся цепочки событий. Поиск следующей цепочки начинается после обнаружения текущей.

События, происходящие в одну и ту же секунду, могут располагаться в последовательности в неопределённом порядке, что влияет на результат.

Синтаксис

sequenceCount(pattern)(timestamp, cond1, cond2, ...)

Аргументы

• timestamp — Столбец, содержащий временные данные. Обычно используются типы данных Date и DateTime. Также можно использовать любой из поддерживаемых типов данных UInt.

• cond1, cond2 — Условия, описывающие цепочку событий. Тип данных: UInt8. Можно передать до 32 аргументов-условий. Функция учитывает только события, описанные в этих условиях. Если последовательность содержит данные, не описанные ни одним условием, функция их пропускает.

Параметры

• pattern — Строка шаблона регулярного выражения. См. Синтаксис шаблона.

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

• Количество неперекрывающихся цепочек событий, соответствующих шаблону регулярного выражения.

Тип: UInt64.

Пример

Рассмотрим данные в таблице t:

┌─time─┬─number─┐
│    1 │      1 │
│    2 │      3 │
│    3 │      2 │
│    4 │      1 │
│    5 │      3 │
│    6 │      2 │
└──────┴────────┘

Подсчитайте, сколько раз число 2 появляется после числа 1 независимо от количества других чисел между ними:

SELECT sequenceCount('(?1).*(?2)')(time, number = 1, number = 2) FROM t

┌─sequenceCount('(?1).*(?2)')(time, equals(number, 1), equals(number, 2))─┐
│                                                                       2 │
└─────────────────────────────────────────────────────────────────────────┘

​

sequenceMatchEvents

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

События, происходящие в одну и ту же секунду, могут располагаться в последовательности в неопределённом порядке, что влияет на результат.

Синтаксис

sequenceMatchEvents(pattern)(timestamp, cond1, cond2, ...)

Аргументы

• timestamp — Столбец, содержащий временные данные. Обычно используются типы данных Date и DateTime. Также можно использовать любой из поддерживаемых типов данных UInt.

• cond1, cond2 — Условия, описывающие цепочку событий. Тип данных: UInt8. Можно передать до 32 аргументов-условий. Функция учитывает только события, описанные этими условиями. Если последовательность содержит данные, не описанные ни одним условием, функция их пропускает.

Параметры

• pattern — Строка шаблона регулярного выражения. См. Синтаксис шаблона.

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

• Массив временных меток для совпавших аргументов-условий (?N) из цепочки событий. Позиция в массиве соответствует позиции аргумента-условия в шаблоне регулярного выражения.

Тип: Array.

Пример

Рассмотрим данные в таблице t:

┌─time─┬─number─┐
│    1 │      1 │
│    2 │      3 │
│    3 │      2 │
│    4 │      1 │
│    5 │      3 │
│    6 │      2 │
└──────┴────────┘

Возвращает временные метки событий самой длинной последовательности

SELECT sequenceMatchEvents('(?1).*(?2).*(?1)(?3)')(time, number = 1, number = 2, number = 4) FROM t

┌─sequenceMatchEvents('(?1).*(?2).*(?1)(?3)')(time, equals(number, 1), equals(number, 2), equals(number, 4))─┐
│ [1,3,4]                                                                                                    │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

См. также

• sequenceMatch

​

windowFunnel

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

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

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

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

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

Синтаксис

windowFunnel(window, [mode, [mode, ... ]])(timestamp, cond1, cond2, ..., condN)

Аргументы

• timestamp — Имя столбца, содержащего временную метку. Поддерживаемые типы данных: Date, DateTime и другие беззнаковые целочисленные типы (обратите внимание: хотя timestamp поддерживает тип UInt64, его значение не может превышать максимальное значение Int64, то есть 2^63 - 1).
• cond — Условия или данные, описывающие цепочку событий. UInt8.

Параметры

• window — Длина скользящего окна, то есть временной интервал между первым и последним условием. Единица измерения window зависит от самого timestamp и может различаться. Определяется с помощью выражения timestamp of cond1 <= timestamp of cond2 <= ... <= timestamp of condN <= timestamp of cond1 + window.
• mode — Необязательный аргумент. Можно задать один или несколько режимов.
  • 'strict_deduplication' — Если для последовательности событий выполняется одно и то же условие, такое повторяющееся событие прерывает дальнейшую обработку. Примечание: режим может работать неожиданным образом, если для одного и того же события выполняются несколько условий.
  • 'strict_order' — Не допускает появления других событий между нужными. Например, в случае A->B->D->C поиск A->B->C прекращается на D, и максимальный уровень события равен 2.
  • 'strict_increase' — Применяет условия только к событиям со строго возрастающими временными метками.
  • 'strict_once' — Учитывает каждое событие в цепочке только один раз, даже если оно удовлетворяет условию несколько раз.
  • 'allow_reentry' — Игнорирует события, нарушающие строгий порядок. Например, в случае A->A->B->C функция найдёт A->B->C, проигнорировав лишнее A, и максимальный уровень события будет равен 3.

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

Максимальное число последовательно сработавших условий из цепочки в пределах скользящего временного окна. Анализируются все цепочки в выборке.

Тип: Integer.

Пример

Определите, достаточно ли заданного периода времени, чтобы пользователь выбрал телефон и дважды купил его в интернет-магазине.

Задайте следующую цепочку событий:

1. Пользователь вошёл в свою учётную запись в магазине (eventID = 1003).
2. Пользователь ищет телефон (eventID = 1007, product = 'phone').
3. Пользователь оформил заказ (eventID = 1009).
4. Пользователь повторно оформил заказ (eventID = 1010).

Входная таблица:

┌─event_date─┬─user_id─┬───────────timestamp─┬─eventID─┬─product─┐
│ 2019-01-28 │       1 │ 2019-01-29 10:00:00 │    1003 │ phone   │
└────────────┴─────────┴─────────────────────┴─────────┴─────────┘
┌─event_date─┬─user_id─┬───────────timestamp─┬─eventID─┬─product─┐
│ 2019-01-31 │       1 │ 2019-01-31 09:00:00 │    1007 │ phone   │
└────────────┴─────────┴─────────────────────┴─────────┴─────────┘
┌─event_date─┬─user_id─┬───────────timestamp─┬─eventID─┬─product─┐
│ 2019-01-30 │       1 │ 2019-01-30 08:00:00 │    1009 │ phone   │
└────────────┴─────────┴─────────────────────┴─────────┴─────────┘
┌─event_date─┬─user_id─┬───────────timestamp─┬─eventID─┬─product─┐
│ 2019-02-01 │       1 │ 2019-02-01 08:00:00 │    1010 │ phone   │
└────────────┴─────────┴─────────────────────┴─────────┴─────────┘

Узнайте, как далеко пользователь user_id смог продвинуться по цепочке за период с января по февраль 2019 года.

Query

SELECT
    level,
    count() AS c
FROM
(
    SELECT
        user_id,
        windowFunnel(6048000000000000)(timestamp, eventID = 1003, eventID = 1009, eventID = 1007, eventID = 1010) AS level
    FROM trend
    WHERE (event_date >= '2019-01-01') AND (event_date <= '2019-02-02')
    GROUP BY user_id
)
GROUP BY level
ORDER BY level ASC;

Response

┌─level─┬─c─┐
│     4 │ 1 │
└───────┴───┘

Пример с режимом allow_reentry

В этом примере показано, как режим allow_reentry работает с шаблонами повторного входа пользователей:

-- Пример данных: пользователь посещает оформление заказа -> страницу товара -> снова оформление заказа -> оплату
-- Без allow_reentry: останавливается на уровне 2 (страница товара)
-- С allow_reentry: достигает уровня 4 (завершение оплаты)

SELECT
    level,
    count() AS users
FROM
(
    SELECT
        user_id,
        windowFunnel(3600, 'strict_order', 'allow_reentry')(
            timestamp,
            action = 'begin_checkout',      -- Шаг 1: Начало оформления заказа
            action = 'view_product_detail', -- Шаг 2: Просмотр страницы товара
            action = 'begin_checkout',      -- Шаг 3: Повторное оформление заказа (повторный вход)
            action = 'complete_payment'     -- Шаг 4: Завершение оплаты
        ) AS level
    FROM user_events
    WHERE event_date = today()
    GROUP BY user_id
)
GROUP BY level
ORDER BY level ASC;

​

retention

Функция принимает набор условий в качестве аргументов: от 1 до 32 аргументов типа UInt8, указывающих, было ли выполнено определённое условие для события. В качестве аргумента можно указать любое условие (как в WHERE).

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

Синтаксис

retention(cond1, cond2, ..., cond32);

Аргументы

• cond — Выражение, возвращающее результат типа UInt8 (1 или 0).

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

Массив из 1 и 0.

• 1 — Условие для события выполнено.
• 0 — Условие для события не выполнено.

Тип: UInt8.

Пример

Рассмотрим пример вычисления функции retention для определения посещаемости сайта.

1. Создайте таблицу для иллюстрации примера.

Query

CREATE TABLE retention_test(date Date, uid Int32) ENGINE = Memory;

INSERT INTO retention_test SELECT '2020-01-01', number FROM numbers(5);
INSERT INTO retention_test SELECT '2020-01-02', number FROM numbers(10);
INSERT INTO retention_test SELECT '2020-01-03', number FROM numbers(15);

Исходная таблица:

Query

SELECT * FROM retention_test

Response

┌───────date─┬─uid─┐
│ 2020-01-01 │   0 │
│ 2020-01-01 │   1 │
│ 2020-01-01 │   2 │
│ 2020-01-01 │   3 │
│ 2020-01-01 │   4 │
└────────────┴─────┘
┌───────date─┬─uid─┐
│ 2020-01-02 │   0 │
│ 2020-01-02 │   1 │
│ 2020-01-02 │   2 │
│ 2020-01-02 │   3 │
│ 2020-01-02 │   4 │
│ 2020-01-02 │   5 │
│ 2020-01-02 │   6 │
│ 2020-01-02 │   7 │
│ 2020-01-02 │   8 │
│ 2020-01-02 │   9 │
└────────────┴─────┘
┌───────date─┬─uid─┐
│ 2020-01-03 │   0 │
│ 2020-01-03 │   1 │
│ 2020-01-03 │   2 │
│ 2020-01-03 │   3 │
│ 2020-01-03 │   4 │
│ 2020-01-03 │   5 │
│ 2020-01-03 │   6 │
│ 2020-01-03 │   7 │
│ 2020-01-03 │   8 │
│ 2020-01-03 │   9 │
│ 2020-01-03 │  10 │
│ 2020-01-03 │  11 │
│ 2020-01-03 │  12 │
│ 2020-01-03 │  13 │
│ 2020-01-03 │  14 │
└────────────┴─────┘

2. Сгруппируйте пользователей по уникальному идентификатору uid с помощью функции retention.

Query

SELECT
    uid,
    retention(date = '2020-01-01', date = '2020-01-02', date = '2020-01-03') AS r
FROM retention_test
WHERE date IN ('2020-01-01', '2020-01-02', '2020-01-03')
GROUP BY uid
ORDER BY uid ASC

Response

┌─uid─┬─r───────┐
│   0 │ [1,1,1] │
│   1 │ [1,1,1] │
│   2 │ [1,1,1] │
│   3 │ [1,1,1] │
│   4 │ [1,1,1] │
│   5 │ [0,0,0] │
│   6 │ [0,0,0] │
│   7 │ [0,0,0] │
│   8 │ [0,0,0] │
│   9 │ [0,0,0] │
│  10 │ [0,0,0] │
│  11 │ [0,0,0] │
│  12 │ [0,0,0] │
│  13 │ [0,0,0] │
│  14 │ [0,0,0] │
└─────┴─────────┘

3. Вычислите общее число посещений сайта за каждый день.

Query

SELECT
    sum(r[1]) AS r1,
    sum(r[2]) AS r2,
    sum(r[3]) AS r3
FROM
(
    SELECT
        uid,
        retention(date = '2020-01-01', date = '2020-01-02', date = '2020-01-03') AS r
    FROM retention_test
    WHERE date IN ('2020-01-01', '2020-01-02', '2020-01-03')
    GROUP BY uid
)

Response

┌─r1─┬─r2─┬─r3─┐
│  5 │  5 │  5 │
└────┴────┴────┘

Где:

• r1- количество уникальных посетителей, посетивших сайт 2020-01-01 (условие cond1).
• r2- количество уникальных посетителей, посетивших сайт в определённый период времени между 2020-01-01 и 2020-01-02 (условия cond1 и cond2).
• r3- количество уникальных посетителей, посетивших сайт в определённый период времени 2020-01-01 и 2020-01-03 (условия cond1 и cond3).

​

uniqUpTo(N)(x)

Вычисляет количество различных значений аргумента до заданного предела N. Если число различных значений аргумента превышает N, функция возвращает N + 1, в противном случае вычисляется точное значение.

Рекомендуется использовать при небольших значениях N, до 10. Максимальное значение N — 100.

Для состояния агрегатной функции эта функция использует объём памяти, равный 1 + N * размер одного значения в байтах. При работе со строками функция хранит некриптографический хеш размером 8 байт; для строк вычисление является приближённым.

Например, у вас может быть таблица, в которой регистрируется каждый поисковый запрос, выполненный пользователями на вашем сайте. Каждая строка в таблице представляет собой один поисковый запрос, со столбцами для ID пользователя, поискового запроса и временной метки запроса. Вы можете использовать uniqUpTo для создания отчёта, который показывает только те ключевые слова, по которым было не менее 5 уникальных пользователей.

SELECT SearchPhrase
FROM SearchLog
GROUP BY SearchPhrase
HAVING uniqUpTo(4)(UserID) >= 5

uniqUpTo(4)(UserID) вычисляет число уникальных значений UserID для каждого SearchPhrase, но считает только до 4 уникальных значений. Если для какого-либо SearchPhrase уникальных значений UserID больше 4, функция возвращает 5 (4 + 1). Затем условие HAVING отфильтровывает значения SearchPhrase, для которых число уникальных значений UserID меньше 5. В результате вы получите список поисковых ключевых слов, которые использовали как минимум 5 уникальных пользователей.

​

sumMapFiltered

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

Синтаксис

sumMapFiltered(keys_to_keep)(keys, values)

Параметры

• keys_to_keep: Array ключей, используемый для фильтрации.
• keys: Array ключей.
• values: Array значений.

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

• Возвращает кортеж из двух массивов: ключи в отсортированном порядке и значения, просуммированные для соответствующих ключей.

Пример

Query

CREATE TABLE sum_map
(
    `date` Date,
    `timeslot` DateTime,
    `statusMap` Nested(status UInt16, requests UInt64)
)
ENGINE = Log

INSERT INTO sum_map VALUES
    ('2000-01-01', '2000-01-01 00:00:00', [1, 2, 3], [10, 10, 10]),
    ('2000-01-01', '2000-01-01 00:00:00', [3, 4, 5], [10, 10, 10]),
    ('2000-01-01', '2000-01-01 00:01:00', [4, 5, 6], [10, 10, 10]),
    ('2000-01-01', '2000-01-01 00:01:00', [6, 7, 8], [10, 10, 10]);

Query

SELECT sumMapFiltered([1, 4, 8])(statusMap.status, statusMap.requests) FROM sum_map;

Response

   ┌─sumMapFiltered([1, 4, 8])(statusMap.status, statusMap.requests)─┐
1. │ ([1,4,8],[10,20,10])                                            │
   └─────────────────────────────────────────────────────────────────┘

​

sumMapFilteredWithOverflow

Эта функция работает так же, как sumMap, но дополнительно принимает в качестве параметра массив ключей для фильтрации. Это может быть особенно полезно при работе с ключами высокой мощности. От функции sumMapFiltered она отличается тем, что выполняет суммирование с переполнением, то есть возвращает для результата суммирования тот же тип данных, что и у аргумента.

Синтаксис

sumMapFilteredWithOverflow(keys_to_keep)(keys, values)

Параметры

• keys_to_keep: Array ключей для фильтрации.
• keys: Array ключей.
• values: Array значений.

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

• Возвращает кортеж из двух массивов: ключи в отсортированном порядке и значения, просуммированные для соответствующих ключей.

Пример

В этом примере мы создаем таблицу sum_map, вставляем в нее несколько строк данных, а затем используем sumMapFilteredWithOverflow, sumMapFiltered и функцию toTypeName, чтобы сравнить результат. Поскольку в созданной таблице requests имел тип UInt8, функция sumMapFiltered повысила тип суммируемых значений до UInt64, чтобы избежать переполнения, тогда как sumMapFilteredWithOverflow сохранила тип UInt8, которого недостаточно для хранения результата, — то есть произошло переполнение.

Query

CREATE TABLE sum_map
(
    `date` Date,
    `timeslot` DateTime,
    `statusMap` Nested(status UInt8, requests UInt8)
)
ENGINE = Log

INSERT INTO sum_map VALUES
    ('2000-01-01', '2000-01-01 00:00:00', [1, 2, 3], [10, 10, 10]),
    ('2000-01-01', '2000-01-01 00:00:00', [3, 4, 5], [10, 10, 10]),
    ('2000-01-01', '2000-01-01 00:01:00', [4, 5, 6], [10, 10, 10]),
    ('2000-01-01', '2000-01-01 00:01:00', [6, 7, 8], [10, 10, 10]);

Query

SELECT sumMapFilteredWithOverflow([1, 4, 8])(statusMap.status, statusMap.requests) as summap_overflow, toTypeName(summap_overflow) FROM sum_map;

Query

SELECT sumMapFiltered([1, 4, 8])(statusMap.status, statusMap.requests) as summap, toTypeName(summap) FROM sum_map;

Response

   ┌─sum──────────────────┬─toTypeName(sum)───────────────────┐
1. │ ([1,4,8],[10,20,10]) │ Tuple(Array(UInt8), Array(UInt8)) │
   └──────────────────────┴───────────────────────────────────┘

Response

   ┌─summap───────────────┬─toTypeName(summap)─────────────────┐
1. │ ([1,4,8],[10,20,10]) │ Tuple(Array(UInt8), Array(UInt64)) │
   └──────────────────────┴────────────────────────────────────┘

​

sequenceNextNode

Возвращает значение следующего события, совпавшего с цепочкой событий.

Экспериментальная функция. Чтобы включить её, задайте SET allow_experimental_funnel_functions = 1.

Синтаксис

sequenceNextNode(direction, base)(timestamp, event_column, base_condition, event1, event2, event3, ...)

Параметры

• direction — Используется для выбора направления.

  • forward — Вперед.
  • backward — Назад.

• base — Используется для задания базовой точки.

  • head — Установить базовую точку на первое событие.
  • tail — Установить базовую точку на последнее событие.
  • first_match — Установить базовую точку на первое совпадение event1.
  • last_match — Установить базовую точку на последнее совпадение event1.

Аргументы

• timestamp — Имя столбца, содержащего временную метку. Поддерживаемые типы данных: Date, DateTime и другие беззнаковые целочисленные типы.
• event_column — Имя столбца, содержащего значение следующего возвращаемого события. Поддерживаемые типы данных: String и Nullable(String).
• base_condition — Условие, которому должна удовлетворять базовая точка.
• event1, event2, … — Условия, описывающие цепочку событий. UInt8.

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

• event_column[next_index] — Если шаблон совпал и следующее значение существует.
• NULL - Если шаблон не совпал или следующего значения не существует.

Тип: Nullable(String).

Пример

Это можно использовать, когда события имеют вид A->B->C->D->E и нужно определить событие, следующее за B->C, то есть D.

Запрос для поиска события, следующего за A->B:

Query

CREATE TABLE test_flow (
    dt DateTime,
    id int,
    page String)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(dt)
ORDER BY id;

INSERT INTO test_flow VALUES (1, 1, 'A') (2, 1, 'B') (3, 1, 'C') (4, 1, 'D') (5, 1, 'E');

SELECT id, sequenceNextNode('forward', 'head')(dt, page, page = 'A', page = 'A', page = 'B') as next_flow FROM test_flow GROUP BY id;

Response

┌─id─┬─next_flow─┐
│  1 │ C         │
└────┴───────────┘

Поведение forward и head

ALTER TABLE test_flow DELETE WHERE 1 = 1 settings mutations_sync = 1;

INSERT INTO test_flow VALUES (1, 1, 'Home') (2, 1, 'Gift') (3, 1, 'Exit');
INSERT INTO test_flow VALUES (1, 2, 'Home') (2, 2, 'Home') (3, 2, 'Gift') (4, 2, 'Basket');
INSERT INTO test_flow VALUES (1, 3, 'Gift') (2, 3, 'Home') (3, 3, 'Gift') (4, 3, 'Basket');

SELECT id, sequenceNextNode('forward', 'head')(dt, page, page = 'Home', page = 'Home', page = 'Gift') FROM test_flow GROUP BY id;

                  dt   id   page
 1970-01-01 09:00:01    1   Home // Базовая точка, совпадение с Home
 1970-01-01 09:00:02    1   Gift // Совпадение с Gift
 1970-01-01 09:00:03    1   Exit // Результат

 1970-01-01 09:00:01    2   Home // Базовая точка, совпадение с Home
 1970-01-01 09:00:02    2   Home // Нет совпадения с Gift
 1970-01-01 09:00:03    2   Gift
 1970-01-01 09:00:04    2   Basket

 1970-01-01 09:00:01    3   Gift // Базовая точка, нет совпадения с Home
 1970-01-01 09:00:02    3   Home
 1970-01-01 09:00:03    3   Gift
 1970-01-01 09:00:04    3   Basket

Поведение backward и tail

SELECT id, sequenceNextNode('backward', 'tail')(dt, page, page = 'Basket', page = 'Basket', page = 'Gift') FROM test_flow GROUP BY id;

                 dt   id   page
1970-01-01 09:00:01    1   Home
1970-01-01 09:00:02    1   Gift
1970-01-01 09:00:03    1   Exit // Базовая точка, не совпадает с Basket

1970-01-01 09:00:01    2   Home
1970-01-01 09:00:02    2   Home // Результат
1970-01-01 09:00:03    2   Gift // Совпадает с Gift
1970-01-01 09:00:04    2   Basket // Базовая точка, совпадает с Basket

1970-01-01 09:00:01    3   Gift
1970-01-01 09:00:02    3   Home // Результат
1970-01-01 09:00:03    3   Gift // Базовая точка, совпадает с Gift
1970-01-01 09:00:04    3   Basket // Базовая точка, совпадает с Basket

Поведение параметров forward и first_match

SELECT id, sequenceNextNode('forward', 'first_match')(dt, page, page = 'Gift', page = 'Gift') FROM test_flow GROUP BY id;

                 dt   id   page
1970-01-01 09:00:01    1   Home
1970-01-01 09:00:02    1   Gift // Базовая точка
1970-01-01 09:00:03    1   Exit // Результат

1970-01-01 09:00:01    2   Home
1970-01-01 09:00:02    2   Home
1970-01-01 09:00:03    2   Gift // Базовая точка
1970-01-01 09:00:04    2   Basket  The result

1970-01-01 09:00:01    3   Gift // Базовая точка
1970-01-01 09:00:02    3   Home // Результат
1970-01-01 09:00:03    3   Gift
1970-01-01 09:00:04    3   Basket

SELECT id, sequenceNextNode('forward', 'first_match')(dt, page, page = 'Gift', page = 'Gift', page = 'Home') FROM test_flow GROUP BY id;

                 dt   id   page
1970-01-01 09:00:01    1   Home
1970-01-01 09:00:02    1   Gift // Базовая точка
1970-01-01 09:00:03    1   Exit // Не совпадает с Home

1970-01-01 09:00:01    2   Home
1970-01-01 09:00:02    2   Home
1970-01-01 09:00:03    2   Gift // Базовая точка
1970-01-01 09:00:04    2   Basket // Не совпадает с Home

1970-01-01 09:00:01    3   Gift // Базовая точка
1970-01-01 09:00:02    3   Home // Совпадает с Home
1970-01-01 09:00:03    3   Gift // Результат
1970-01-01 09:00:04    3   Basket

Поведение параметров backward и last_match

SELECT id, sequenceNextNode('backward', 'last_match')(dt, page, page = 'Gift', page = 'Gift') FROM test_flow GROUP BY id;

                 dt   id   page
1970-01-01 09:00:01    1   Home // Результат
1970-01-01 09:00:02    1   Gift // Базовая точка
1970-01-01 09:00:03    1   Exit

1970-01-01 09:00:01    2   Home
1970-01-01 09:00:02    2   Home // Результат
1970-01-01 09:00:03    2   Gift // Базовая точка
1970-01-01 09:00:04    2   Basket

1970-01-01 09:00:01    3   Gift
1970-01-01 09:00:02    3   Home // Результат
1970-01-01 09:00:03    3   Gift // Базовая точка
1970-01-01 09:00:04    3   Basket

SELECT id, sequenceNextNode('backward', 'last_match')(dt, page, page = 'Gift', page = 'Gift', page = 'Home') FROM test_flow GROUP BY id;

                 dt   id   page
1970-01-01 09:00:01    1   Home // Matched with Home, the result is null
1970-01-01 09:00:02    1   Gift // Base point
1970-01-01 09:00:03    1   Exit

1970-01-01 09:00:01    2   Home // The result
1970-01-01 09:00:02    2   Home // Matched with Home
1970-01-01 09:00:03    2   Gift // Base point
1970-01-01 09:00:04    2   Basket

1970-01-01 09:00:01    3   Gift // Результат
1970-01-01 09:00:02    3   Home // Совпадение с Home
1970-01-01 09:00:03    3   Gift // Базовая точка
1970-01-01 09:00:04    3   Basket

Поведение base_condition

CREATE TABLE test_flow_basecond
(
    `dt` DateTime,
    `id` int,
    `page` String,
    `ref` String
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(dt)
ORDER BY id;

INSERT INTO test_flow_basecond VALUES (1, 1, 'A', 'ref4') (2, 1, 'A', 'ref3') (3, 1, 'B', 'ref2') (4, 1, 'B', 'ref1');

SELECT id, sequenceNextNode('forward', 'head')(dt, page, ref = 'ref1', page = 'A') FROM test_flow_basecond GROUP BY id;

                  dt   id   page   ref
 1970-01-01 09:00:01    1   A      ref4 // head не может быть базовой точкой, так как столбец ref head не совпадает с 'ref1'.
 1970-01-01 09:00:02    1   A      ref3
 1970-01-01 09:00:03    1   B      ref2
 1970-01-01 09:00:04    1   B      ref1

SELECT id, sequenceNextNode('backward', 'tail')(dt, page, ref = 'ref4', page = 'B') FROM test_flow_basecond GROUP BY id;

                  dt   id   page   ref
 1970-01-01 09:00:01    1   A      ref4
 1970-01-01 09:00:02    1   A      ref3
 1970-01-01 09:00:03    1   B      ref2
 1970-01-01 09:00:04    1   B      ref1 // tail не может быть базовой точкой, так как столбец ref tail не совпадает с 'ref4'.

SELECT id, sequenceNextNode('forward', 'first_match')(dt, page, ref = 'ref3', page = 'A') FROM test_flow_basecond GROUP BY id;

                  dt   id   page   ref
 1970-01-01 09:00:01    1   A      ref4 // Эта строка не может быть базовой точкой, так как столбец ref не совпадает с 'ref3'.
 1970-01-01 09:00:02    1   A      ref3 // Базовая точка
 1970-01-01 09:00:03    1   B      ref2 // Результат
 1970-01-01 09:00:04    1   B      ref1

SELECT id, sequenceNextNode('backward', 'last_match')(dt, page, ref = 'ref2', page = 'B') FROM test_flow_basecond GROUP BY id;

                  dt   id   page   ref
 1970-01-01 09:00:01    1   A      ref4
 1970-01-01 09:00:02    1   A      ref3 // Результат
 1970-01-01 09:00:03    1   B      ref2 // Базовая точка
 1970-01-01 09:00:04    1   B      ref1 // Эта строка не может быть базовой точкой, так как столбец ref не совпадает с 'ref2'.