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

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

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) из цепочки событий. Позиция в массиве соответствует позиции аргумента условия в шаблоне

Тип: Массив.

Пример

Рассмотрим данные в таблице 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' — Считает каждое событие только один раз в цепи, даже если оно выполняет условие несколько раз.

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

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

Тип: Integer.

Пример

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

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

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. Группируем пользователей по уникальному идентификатору uid, используя функцию retention.

Запрос:

Результат:

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

Запрос:

Результат:

Где:

• 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 байт; вычисление приближенное для строк.

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

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