# Параметрические агрегатные функции {#aggregate_functions_parametric} Некоторые агрегатные функции могут принимать не только столбцы-аргументы (по которым производится свёртка), но и набор параметров - констант для инициализации. Синтаксис - две пары круглых скобок вместо одной. Первая - для параметров, вторая - для аргументов. ## histogram Рассчитывает адаптивную гистограмму. Не гарантирует точного результата. ``` histogram(number_of_bins)(values) ``` Функция использует [A Streaming Parallel Decision Tree Algorithm](http://jmlr.org/papers/volume11/ben-haim10a/ben-haim10a.pdf). Границы столбцов устанавливаются по мере поступления новых данных в функцию. В общем случае столбцы имею разную ширину. **Параметры** `number_of_bins` — максимальное количество корзин в гистограмме. Функция автоматически вычисляет количество корзин. Она пытается получить указанное количество корзин, но если не получилось, то в результате корзин будет меньше. `values` — [выражение](../syntax.md#syntax-expressions), предоставляющее входные значения. **Возвращаемые значения** - [Массив](../../data_types/array.md) [кортежей](../../data_types/tuple.md) следующего вида: ``` [(lower_1, upper_1, height_1), ... (lower_N, upper_N, height_N)] ``` - `lower` — нижняя граница корзины. - `upper` — верхняя граница корзины. - `height` — количество значений в корзине. **Пример** ```sql SELECT histogram(5)(number + 1) FROM ( SELECT * FROM system.numbers LIMIT 20 ) ``` ```text ┌─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](../functions/other_functions.md#function-bar) можно визуализировать гистограмму, например: ```sql 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 ) ``` ```text ┌─height─┬─bar───┐ │ 2.125 │ █▋ │ │ 3.25 │ ██▌ │ │ 5.625 │ ████▏ │ │ 5.625 │ ████▏ │ │ 3.375 │ ██▌ │ └────────┴───────┘ ``` В этом случае необходимо помнить, что границы корзин гистограммы не известны. ## sequenceMatch(pattern)(timestamp, cond1, cond2, ...) {#function-sequencematch} Проверяет, содержит ли последовательность цепочку событий, которая соответствует шаблону. ```sql sequenceMatch(pattern)(timestamp, cond1, cond2, ...) ``` !!! warning "Предупреждение" События, произошедшие в одну и ту же секунду, располагаются в последовательности в неопределенном порядке, что может повлиять на результат работы функции. **Параметры** - `pattern` — строка с шаблоном. Смотрите [Синтаксис шаблонов](#sequence-function-pattern-syntax). - `timestamp` — столбец, содержащий метки времени. Типичный тип данных столбца — `Date` или `DateTime`. Также можно использовать любой из поддержанных типов данных [UInt](../../data_types/int_uint.md). - `cond1`, `cond2` — условия, описывающие цепочку событий. Тип данных — `UInt8`. Можно использовать до 32 условий. Функция учитывает только те события, которые указаны в условиях. Функция пропускает данные из последовательности, если они не описаны ни в одном из условий. **Возвращаемые значения** - 1, если цепочка событий, соответствующая шаблону найдена. - 0, если цепочка событий, соответствующая шаблону не найдена. Тип: `UInt8`. **Синтаксис шаблонов** - `(?N)` — соответствует условию на позиции `N`. Условия пронумерованы по порядку в диапазоне `[1, 32]`. Например, `(?1)` соответствует условию, заданному параметром `cond1`. - `.*` — соответствует любому количеству событий. Для этого элемента шаблона не надо задавать условия. - `(?t operator value)` — устанавливает время в секундах, которое должно разделять два события. Например, шаблон `(?1)(?t>1800)(?2)` соответствует событиям, которые произошли более чем через 1800 секунд друг от друга. Между этими событиями может находиться произвольное количество любых событий. Операторы могут быть `>=`, `>`, `<`, `<=`. **Примеры** Пусть таблица `t` содержит следующие данные: ```text ┌─time─┬─number─┐ │ 1 │ 1 │ │ 2 │ 3 │ │ 3 │ 2 │ └──────┴────────┘ ``` Выполним запрос: ```sql SELECT sequenceMatch('(?1)(?2)')(time, number = 1, number = 2) FROM t ``` ```text ┌─sequenceMatch('(?1)(?2)')(time, equals(number, 1), equals(number, 2))─┐ │ 1 │ └───────────────────────────────────────────────────────────────────────┘ ``` Функция нашла цепочку событий, в которой число 2 следует за числом 1. Число 3 между ними было пропущено, поскольку оно не описано как событие. Если необходимо учесть это число при поиске цепочки событий, заданной в примере, то необходимо задать для него условие. ```sql SELECT sequenceMatch('(?1)(?2)')(time, number = 1, number = 2, number = 3) FROM t ``` ```text ┌─sequenceMatch('(?1)(?2)')(time, equals(number, 1), equals(number, 2), equals(number, 3))─┐ │ 0 │ └──────────────────────────────────────────────────────────────────────────────────────────┘ ``` В этом случае функция не может найти цепочку событий, соответствующую шаблону, поскольку событие для числа 3 произошло между 1 и 2. Если бы в этом же случае мы бы проверяли условие на событие для числа 4, то цепочка бы соответствовала шаблону. ```sql SELECT sequenceMatch('(?1)(?2)')(time, number = 1, number = 2, number = 4) FROM t ``` ```text ┌─sequenceMatch('(?1)(?2)')(time, equals(number, 1), equals(number, 2), equals(number, 4))─┐ │ 1 │ └──────────────────────────────────────────────────────────────────────────────────────────┘ ``` **Смотрите также** - [sequenceCount](#function-sequencecount) ## sequenceCount(pattern)(time, cond1, cond2, ...) {#function-sequencecount} Вычисляет количество цепочек событий, соответствующих шаблону. Функция отыскивает непересекающиеся цепочки событий. Она начитает искать следующую цепочку только после того, как полностью совпала текущая цепочка событий. !!! warning "Предупреждение" События, произошедшие в одну и ту же секунду, располагаются в последовательности в неопределенном порядке, что может повлиять на результат работы функции. ```sql sequenceCount(pattern)(timestamp, cond1, cond2, ...) ``` **Параметры** - `pattern` — строка с шаблоном. Смотрите [Синтаксис шаблонов](#sequence-function-pattern-syntax). - `timestamp` — столбец, содержащий метки времени. Типичный тип данных столбца — `Date` или `DateTime`. Также можно использовать любой из поддержанных типов данных [UInt](../../data_types/int_uint.md). - `cond1`, `cond2` — условия, описывающие цепочку событий. Тип данных — `UInt8`. Можно использовать до 32 условий. Функция учитывает только те события, которые указаны в условиях. Функция пропускает данные из последовательности, если они не описаны ни в одном из условий. **Возвращаемое значение** - Число непересекающихся цепочек событий, соответствущих шаблону. Тип: `UInt64`. **Пример** Пусть таблица `t` содержит следующие данные: ```text ┌─time─┬─number─┐ │ 1 │ 1 │ │ 2 │ 3 │ │ 3 │ 2 │ │ 4 │ 1 │ │ 5 │ 3 │ │ 6 │ 2 │ └──────┴────────┘ ``` Вычислим сколько раз число 2 стоит после числа 1, причем между 1 и 2 могут быть любые числа: ```sql SELECT sequenceCount('(?1).*(?2)')(time, number = 1, number = 2) FROM t ``` ```text ┌─sequenceCount('(?1).*(?2)')(time, equals(number, 1), equals(number, 2))─┐ │ 2 │ └─────────────────────────────────────────────────────────────────────────┘ ``` **Смотрите также** - [sequenceMatch](#function-sequencematch) ## windowFunnel(window)(timestamp, cond1, cond2, cond3, ...) Отыскивает цепочки событий в скользящем окне по времени и вычисляет максимальное количество произошедших событий из цепочки. ```sql windowFunnel(window)(timestamp, cond1, cond2, cond3, ...) ``` **Параметры** - `window` — ширина скользящего окна по времени в секундах. - `timestamp` — имя столбца, содержащего отметки времени. Тип данных [Date](../../data_types/date.md), [DateTime](../../data_types/datetime.md#data_type-datetime) или [UInt*](../../data_types/int_uint.md). Заметьте, что в случает хранения меток времени в столбцах с типом `UInt64`, максимально допустимое значение соответствует ограничению для типа `Int64`, т.е. равно `2^63-1`. - `cond1`, `cond2`... — условия или данные, описывающие цепочку событий. Тип данных — `UInt8`. Значения могут быть 0 или 1. **Алгоритм** - Функция отыскивает данные, на которых срабатывает первое условие из цепочки, и присваивает счетчику событий значение 1. С этого же момента начинается отсчет времени скользящего окна. - Если в пределах окна последовательно попадаются события из цепочки, то счетчик увеличивается. Если последовательность событий нарушается, то счетчик не растёт. - Если в данных оказалось несколько цепочек разной степени завершенности, то функция выдаст только размер самой длинной цепочки. **Возвращаемое значение** - Целое число. Максимальное количество последовательно сработавших условий из цепочки в пределах скользящего окна по времени. Исследуются все цепочки в выборке. **Пример** Определим, успевает ли пользователь за час выбрать телефон в интернет-магазине и купить его. Зададим следующую цепочку событий: 1. Пользователь вошел в личный кабинет магазина (`eventID=1001`). 2. Пользователь ищет телефон (`eventID = 1003, product = 'phone'`). 3. Пользователь сделал заказ (`eventID = 1009`). Чтобы узнать, как далеко пользователь `user_id` смог пройти по цепочке за час в январе 2017-го года, составим запрос: ```sql SELECT level, count() AS c FROM ( SELECT user_id, windowFunnel(3600)(timestamp, eventID = 1001, eventID = 1003 AND product = 'phone', eventID = 1009) AS level FROM trend_event WHERE (event_date >= '2017-01-01') AND (event_date <= '2017-01-31') GROUP BY user_id ) GROUP BY level ORDER BY level ``` В результате мы можем получить 0, 1, 2 или 3 в зависимости от действий пользователя. ## uniqUpTo(N)(x) Вычисляет количество различных значений аргумента, если оно меньше или равно N. В случае, если количество различных значений аргумента больше N, возвращает N + 1. Рекомендуется использовать для маленьких N - до 10. Максимальное значение N - 100. Для состояния агрегатной функции используется количество оперативки равное 1 + N \* размер одного значения байт. Для строк запоминается некриптографический хэш, имеющий размер 8 байт. То есть, для строк вычисление приближённое. Функция также работает для нескольких аргументов. Работает максимально быстро за исключением патологических случаев, когда используется большое значение N и количество уникальных значений чуть меньше N. Пример применения: ```text Задача: показывать в отчёте только поисковые фразы, по которым было хотя бы 5 уникальных посетителей. Решение: пишем в запросе GROUP BY SearchPhrase HAVING uniqUpTo(4)(UserID) >= 5 ``` [Оригинальная статья](https://clickhouse.yandex/docs/ru/query_language/agg_functions/parametric_functions/)