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

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

histogram

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

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

Аргументы

values — Expression, возвращающая входные значения.

Параметры

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

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

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

  • lower — Нижняя граница корзины.
  • upper — Верхняя граница корзины.
  • height — Вычисленная высота корзины.

Пример

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

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

sequenceMatch

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

Синтаксис

примечание

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

Аргументы

• 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:

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

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

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

См. также

• sequenceCount

sequenceCount

Считает количество цепочек событий, которые совпали с шаблоном. Функция ищет цепочки событий, которые не перекрываются. Она начинает искать следующую цепочку после совпадения текущей.

примечание

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

Синтаксис

Аргументы

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

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

Параметры

• pattern — Строка шаблона. См. Синтаксис шаблона.

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

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

Тип: UInt64.

Пример

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

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

sequenceMatchEvents

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

примечание

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

Синтаксис

Аргументы

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

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

Параметры

• pattern — Строка шаблона. См. Синтаксис шаблона.

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

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

Тип: Array.

Пример

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

Вернем временные метки событий для самой длинной цепочки:

См. также

• sequenceMatch

windowFunnel

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

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

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

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

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

Синтаксис

Аргументы

• timestamp — Имя колонки, содержащей временную метку. Поддерживаемые типы данных: Date, DateTime и другие типы без знака (обратите внимание, что хотя временная метка поддерживает тип 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' — Считайте каждое событие только один раз в цепочке, даже если оно выполняет условие несколько раз.

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

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

Тип: Целое.

Пример

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

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

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

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

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

Запрос:

Результат:

retention

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

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

Синтаксис

Аргументы

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

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

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

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

Тип: UInt8.

Пример

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

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

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

Запрос:

Результат:

2. Группируем пользователей по уникальному ID uid, используя функцию retention.

Запрос:

Результат:

3. Подсчитаем общее количество посещений сайта за день.

Запрос:

Результат:

Где:

• r1 — количество уникальных посетителей, которые посетили сайт 1 января 2020 года (условие cond1).
• r2 — количество уникальных посетителей, которые посетили сайт в период между 1 и 2 января 2020 года (условия cond1 и cond2).
• r3 — количество уникальных посетителей, которые посетили сайт 1 и 3 января 2020 года (условия cond1 и cond3).

uniqUpTo(N)(x)

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

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

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

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

uniqUpTo(4)(UserID) вычисляет количество уникальных значений UserID для каждой SearchPhrase, но учитывает только до 4 уникальных значений. Если уникальных значений UserID для SearchPhrase больше 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 значений.

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

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

Пример

Запрос:

Результат:

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, который недостаточно велик для хранения результата — т.е. переполнение произошло.

Запрос:

Результат:

sequenceNextNode

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

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

Синтаксис

Параметры

• 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:

Результат:

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

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

Поведение для forward и first_match

Поведение для backward и last_match

Поведение для base_condition