Важно: Данный раздел актуален для Платформы данных в Публичном облаке и On-Premise.
Агрегатные функции работают в привычном для специалистов по базам данных смысле.
RT.WideStore поддерживает также:
При агрегации все NULL пропускаются.
Примеры:
Рассмотрим таблицу:
┌─x─┬────y─┐
│ 1 │ 2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │ 2 │
│ 3 │ 3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘ Выполним суммирование значений в столбце y:
SELECT sum(y) FROM t_null_big
┌─sum(y)─┐
│ 7 │
└─────────┘Теперь с помощью функции groupArray сформируем массив из столбца y:
SELECT groupArray(y) FROM t_null_big
┌─groupArray(y)─┐
│ [2,2,3] │
└────────────────┘groupArray не включает NULL в результирующий массив.
Некоторые агрегатные функции могут принимать не только столбцы-аргументы (по которым производится свёртка), но и набор параметров - констант для инициализации. Синтаксис - две пары круглых скобок вместо одной. Первая - для параметров, вторая - для аргументов.
Рассчитывает адаптивную гистограмму. Не гарантирует точного результата.
Синтаксис:
histogram(number_of_bins)(values)Функция использует A Streaming Parallel Decision Tree Algorithm. Границы столбцов устанавливаются по мере поступления новых данных в функцию. В общем случае столбцы имею разную ширину.
Аргументы:
values — выражение, предоставляющее входные значения.
Параметры:
number_of_bins — максимальное количество корзин в гистограмме. Функция автоматически вычисляет количество корзин. Она пытается получить указанное количество корзин, но если не получилось, то в результате корзин будет меньше.
Возвращаемые значения:
[(lower_1, upper_1, height_1), ... (lower_N, upper_N, height_N)]
где:
Пример:
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(pattern)(timestamp, cond1, cond2, ...)
Предупреждение: События, произошедшие в одну и ту же секунду, располагаются в последовательности в неопределенном порядке, что может повлиять на результат работы функции.
Аргументы:
Параметры:
Возвращаемые значения:
Тип: UInt8.
Синтаксис шаблонов
Примеры:
Пусть таблица 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(pattern)(timestamp, cond1, cond2, ...)Аргументы:
Параметры:
Возвращаемое значение:
Тип: UInt64.
Пример:
Пусть таблица t содержит следующие данные:
┌─time─┬─number─┐
│ 1 │ 1 │
│ 2 │ 3 │
│ 3 │ 2 │
│ 4 │ 1 │
│ 5 │ 3 │
│ 6 │ 2 │
└──────┴─────────┘Вычислим сколько раз число 2 стоит после числа 1, причем между 1 и 2 могут быть любые числа:
SELECT sequenceCount('(?1).*(?2)')(time, number = 1, number = 2) FROM t
┌─sequenceCount('(?1).*(?2)')(time, equals(number, 1), equals(number, 2))─┐
│ 2 │
└────────────────────────────────────────────────────────────────────────────────┘Смотрите также:
Отыскивает цепочки событий в скользящем окне по времени и вычисляет максимальное количество произошедших событий из цепочки.
Функция работает по алгоритму:
Синтаксис:
windowFunnel(window, [mode, [mode, ... ]])(timestamp, cond1, cond2, ..., condN)Аргументы:
Параметры:
Возвращаемое значение:
Максимальное количество последовательно сработавших условий из цепочки в пределах скользящего окна по времени. Исследуются все цепочки в выборке.
Тип: Integer.
Пример:
Определим, успевает ли пользователь за установленный период выбрать телефон в интернет-магазине, купить его и сделать повторный заказ.
Зададим следующую цепочку событий:
Входная таблица:
┌─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-го года.
Запрос:
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;Аналитическая функция, которая показывает, насколько выдерживаются те или иные условия, например, удержание динамики/уровня посещаемости сайта.
Функция принимает набор (от 1 до 32) логических условий, как в WHERE, и применяет их к заданному набору данных.
Условия, кроме первого, применяются попарно: результат второго будет истинным, если истинно первое и второе, третьего - если истинно первое и третье и т.д.
Синтаксис:
retention(cond1, cond2, ..., cond32)Аргументы:
Возвращаемое значение:
Массив из 1 или 0.
Тип: UInt8.
Пример:
Рассмотрим пример расчета функции retention для определения посещаемости сайта.
1. Создадим таблицу для иллюстрации примера.
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);Входная таблица:
Запрос:
SELECT * FROM retention_testОтвет:
┌────────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.
Запрос:
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Результат:
┌─uid─┬──────────┐
│ 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. Рассчитаем количество посещений сайта за день.
Запрос:
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
)Результат:
┌─r1─┬─r2─┬─r3─┐
│ 5 │ 5 │ 5 │
└────┴─────┴────┘Где:
Вычисляет количество различных значений аргумента, если оно меньше или равно N. В случае, если количество различных значений аргумента больше N, возвращает N + 1.
Рекомендуется использовать для маленьких N - до 10. Максимальное значение N - 100.
Для состояния агрегатной функции используется количество оперативки равное 1 + N * размер одного значения байт. Для строк запоминается не криптографический хэш, имеющий размер 8 байт. То есть, для строк вычисление приближённое.
Функция также работает для нескольких аргументов.
Работает максимально быстро за исключением патологических случаев, когда используется большое значение N и количество уникальных значений чуть меньше N.
Пример применения:
Задача: показывать в отчёте только поисковые фразы, по которым было хотя бы 5 уникальных посетителей.
Решение: пишем в запросе:
GROUP BY SearchPhrase HAVING uniqUpTo(4)(UserID) >= 5Возвращает значение следующего события, соответствующего цепочке событий.
Экспериментальная функция, чтобы включить ее, выполните: SET allow_experimental_funnel_functions = 1.
Синтаксис:
sequenceNextNode(direction, base)(timestamp, event_column, base_condition, event1, event2, event3, ...)Параметры:
Аргументы:
Возвращаемые значения:
Пример:
Функцию можно использовать, если есть цепочка событий A->B->C->D->E, и вы хотите определить событие, следующее за B->C, то есть D.
Запрос ищет событие после A->B:
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;Результат:
┌─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 Результат
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 Baske
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 // Совпадение с Home, результат `Null`
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 // Совпадение с 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 // Совпадение с 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 // Начало не может быть исходной точкой, поскольку столбец ref не соответствует '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 // Конец не может быть исходной точкой, поскольку столбец ref не соответствует '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'. К имени агрегатной функции может быть приписан некоторый суффикс. При этом, работа агрегатной функции некоторым образом модифицируется.
К имени любой агрегатной функции может быть приписан суффикс -If. В этом случае, агрегатная функция принимает ещё один дополнительный аргумент - условие (типа UInt8). Агрегатная функция будет обрабатывать только те строки, для которых условие сработало. Если условие ни разу не сработало - возвращается некоторое значение по умолчанию (обычно - нули, пустые строки).
Примеры: sumIf(column, cond), countIf(cond), avgIf(x, cond), quantilesTimingIf(level1, level2)(x, cond), argMinIf(arg, val, cond) и т. п.
С помощью условных агрегатных функций, вы можете вычислить агрегаты сразу для нескольких условий, не используя подзапросы и JOIN-ы. Например, в Яндекс.Метрике, условные агрегатные функции используются для реализации функциональности сравнения сегментов.
К имени любой агрегатной функции может быть приписан суффикс -Array. В этом случае, агрегатная функция вместо аргументов типов T принимает аргументы типов Array(T). Если агрегатная функция принимает несколько аргументов, то это должны быть массивы одинаковых длин. При обработке массивов, агрегатная функция работает, как исходная агрегатная функция по всем элементам массивов.
Пример 1: sumArray(arr) - просуммировать все элементы всех массивов arr. В данном примере можно было бы написать проще: sum(arraySum(arr)).
Пример 2: uniqArray(arr) - посчитать количество уникальных элементов всех массивов arr. Это можно было бы сделать проще: uniq(arrayJoin(arr)), но не всегда есть возможность добавить arrayJoin в запрос.
Комбинаторы -If и -Array можно сочетать. При этом, должен сначала идти Array, а потом If. Примеры: uniqArrayIf(arr, cond), quantilesTimingArrayIf(level1, level2)(arr, cond). Из-за такого порядка получается, что аргумент cond не должен быть массивом.
При использовании этого комбинатора агрегатная функция возвращает то же значение, но типа SimpleAggregateFunction(...). Текущее значение функции может храниться в таблице для последующей работы с таблицами семейства AggregatingMergeTree.
Синтаксис:
<aggFunction>SimpleState(x) Аргументы:
Возвращаемое значение:
Значение агрегатной функции типа SimpleAggregateFunction(...).
Пример:
Запрос:
WITH anySimpleState(number) AS c SELECT toTypeName(c), c FROM numbers(1);Результат:
┌─toTypeName(c)──────────────────────────┬─c─┐
│ SimpleAggregateFunction(any, UInt64) │ 0 │
└──────────────────────────────────────────┴───┘ В случае применения этого комбинатора, агрегатная функция возвращает не готовое значение (например, в случае функции uniq — количество уникальных значений), а промежуточное состояние агрегации (например, в случае функции uniq — хэш-таблицу для расчёта количества уникальных значений), которое имеет тип AggregateFunction(...) и может использоваться для дальнейшей обработки или может быть сохранено в таблицу для последующей доагрегации.
Для работы с промежуточными состояниями предназначены:
В случае применения этого комбинатора, агрегатная функция будет принимать в качестве аргумента промежуточное состояние агрегации, доагрегировать (объединять вместе) эти состояния, и возвращать готовое значение.
Выполняет слияние промежуточных состояний агрегации, аналогично комбинатору -Merge, но возвращает не готовое значение, а промежуточное состояние агрегации, аналогично комбинатору -State.
Преобразует агрегатную функцию для таблиц в агрегатную функцию для массивов, которая применяет агрегирование для соответствующих элементов массивов и возвращает массив результатов. Например, sumForEach для массивов [1, 2], [3, 4, 5] и [6, 7] даст результат [10, 13, 5], сложив соответственные элементы массивов.
При наличии комбинатора Distinct, каждое уникальное значение аргументов, будет учтено в агрегатной функции только один раз. Примеры: sum(DISTINCT x), groupArray(DISTINCT x), corrStableDistinct(DISTINCT x, y) и т.п.
Изменяет поведение агрегатной функции.
Если на вход агрегатной функции передан пустой набор данных, то с помощью комбинатора -OrDefault функция возвращает значение по умолчанию для соответствующего типа данных. Комбинатор применяется к агрегатным функциям, которые могут принимать пустые входные данные.
-OrDefault можно использовать с другими комбинаторами.
Синтаксис:
<aggFunction>OrDefault(x) Аргументы:
Возвращаемые значения:
Возвращает значение по умолчанию для соответствующего типа агрегатной функции, если агрегировать нечего.
Тип данных зависит от используемой агрегатной функции.
Пример:
Запрос:
SELECT avg(number), avgOrDefault(number) FROM numbers(0) Результат:
┌─avg(number)─┬─avgOrDefault(number)─┐
│ nan │ 0 │
└──────────────┴────────────────────────┘ Также -OrDefault может использоваться с другими комбинаторами. Это полезно, когда агрегатная функция не принимает пустые входные данные.
Запрос:
SELECT avgOrDefaultIf(x, x > 10)
FROM
(
SELECT toDecimal32(1.23, 2) AS x
) Результат:
┌─avgOrDefaultIf(x, greater(x, 10))─┐
│ 0.00 │
└──────────────────────────────────────┘ Изменяет поведение агрегатной функции.
Комбинатор преобразует результат агрегатной функции к типу Nullable. Если агрегатная функция не получает данных на вход, то с комбинатором она возвращает NULL.
-OrNull может использоваться с другими комбинаторами.
Синтаксис:
<aggFunction>OrNull(x) Аргументы:
Возвращаемые значения:
Тип: Nullable(aggregate function return type).
Пример:
Добавьте -orNull в конец агрегатной функции.
Запрос:
SELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10 Результат:
┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐
│ ᴺᵁᴸᴸ │ Nullable(UInt64) │
└─────────────────────┴─────────────────────────────────┘ Также -OrNull может использоваться с другими комбинаторами. Это полезно, когда агрегатная функция не принимает пустые входные данные.
Запрос:
SELECT avgOrNullIf(x, x > 10)
FROM
(
SELECT toDecimal32(1.23, 2) AS x
) Результат:
┌─avgOrNullIf(x, greater(x, 10))─┐
│ ᴺᵁᴸᴸ │
└───────────────────────────────────┘ Позволяет поделить данные на группы, а затем по-отдельности агрегирует данные для этих групп. Группы образуются разбиением значений одного из столбцов на интервалы.
Синтаксис:
<aggFunction>Resample(start, end, step)(<aggFunction_params>, resampling_key) Аргументы:
Возвращаемые значения:
Пример:
Рассмотрим таблицу people со следующими данными:
┌─name───┬─age─┬─wage─┐
│ John │ 16 │ 10 │
│ Alice │ 30 │ 15 │
│ Mary │ 35 │ 8 │
│ Evelyn │ 48 │11.5 │
│ David │ 62 │ 9.9 │
│ Brian │ 60 │ 16 │
└─────────┴─────┴──────┘ Получим имена людей, чей возраст находится в интервалах [30,60) и [60,75). Поскольку мы используем целочисленное представление возраста, то интервалы будут выглядеть как [30, 59] и [60,74].
Чтобы собрать имена в массив, возьмём агрегатную функцию groupArray. Она принимает один аргумент. В нашем случае, это столбец name. Функция groupArrayResample должна использовать столбец age для агрегирования имён по возрасту. Чтобы определить необходимые интервалы, передадим в функцию groupArrayResample аргументы 30, 75, 30.
SELECT groupArrayResample(30, 75, 30)(name, age) from people
┌─groupArrayResample(30, 75, 30)(name, age)─────┐
│ [['Alice','Mary','Evelyn'],['David','Brian']] │
└───────────────────────────────────────────────────┘ Посмотрим на результаты.
Jonh не попал в выдачу, поскольку слишком молод. Остальные распределены согласно заданным возрастным интервалам.
Теперь посчитаем общее количество людей и их среднюю заработную плату в заданных возрастных интервалах.
SELECT
countResample(30, 75, 30)(name, age) AS amount,
avgResample(30, 75, 30)(wage, age) AS avg_wage
FROM people
┌─amount─┬─avg_wage────────────────────┐
│ [3,2] │ [11.5,12.949999809265137] │
└─────────┴─────────────────────────────┘ Стандартные агрегатные функции:
Агрегатные функции, специфичные для RT.WideStore:
Вычисляет количество строк или не NULL значений.
RT.WideStore поддерживает следующие виды синтаксиса для count:
count(expr) или COUNT(DISTINCT expr).count() или COUNT(*). Синтаксис count() специфичен для RT.WideStore.Аргументы:
Функция может принимать:
Возвращаемое значение:
count не становится Nullable. Функция возвращает 0, если выражение равно NULL для всех строк.В обоих случаях тип возвращаемого значения UInt64.
Подробности:
RT.WideStore поддерживает синтаксис COUNT(DISTINCT ...). Поведение этой конструкции зависит от настройки count_distinct_implementation. Она определяет, какая из функций uniq* используется для выполнения операции. По умолчанию — функция uniqExact.
Запрос SELECT count() FROM table оптимизирован по умолчанию с использованием метаданных из MergeTree. Если вы хотите управлять безопасностью на уровне строк, отключите оптимизацию при помощи настройки optimize_trivial_count_query.
При этом запрос SELECT count(nullable_column) FROM table может быть оптимизирован включением настройки optimize_functions_to_subcolumns. При optimize_functions_to_subcolumns = 1 функция читает только подстолбец null вместо чтения всех данных столбца. Запрос SELECT count(n) FROM table преобразуется к запросу SELECT sum(NOT n.null) FROM table.
Примеры:
Пример 1:
SELECT count() FROM t
┌─count()─┐
│ 5 │
└──────────┘Пример 2:
SELECT name, value FROM system.settings WHERE name = 'count_distinct_implementation'
┌─name────────────────────────────┬──value─────┐
│ count_distinct_implementation │ uniqExact │
└──────────────────────────────────┴────────────┘
SELECT count(DISTINCT num) FROM t
┌─uniqExact(num)─┐
│ 3 │
└─────────────────┘Этот пример показывает, что count(DISTINCT num) выполняется с помощью функции uniqExact в соответствии со значением настройки count_distinct_implementation.
Агрегатная функция, вычисляющая минимум по группе значений.
Пример:
SELECT min(salary) FROM employees;
SELECT department, min(salary) FROM employees GROUP BY department;Если вам нужна неагрегатная функция для выбора минимум двух значений, см least:
SELECT least(a, b) FROM table;Агрегатная функция, вычисляющая максимум по группе значений.
Пример:
SELECT max(salary) FROM employees;
SELECT department, max(salary) FROM employees GROUP BY department;Если вам нужна неагрегатная функция для выбора максимум двух значений, см greatest:
SELECT greatest(a, b) FROM table;Вычисляет сумму. Работает только для чисел.
Вычисляет среднее арифметическое.
Синтаксис:
avg(x)Аргументы:
Возвращаемое значение:
NaN, если входное значение x— пустое.Пример:
Запрос:
SELECT avg(x) FROM values('x Int8', 0, 1, 2, 3, 4, 5);Результат:
┌─avg(x)─┐
│ 2.5 │
└─────────┘Пример:
построить временную таблицу:
Запрос:
CREATE table test (t UInt8) ENGINE = Memory;Выполните запрос:
SELECT avg(t) FROM test;Результат:
┌─avg(x)─┐
│ nan │
└─────────┘Выбирает первое попавшееся значение. Порядок выполнения запроса может быть официальным и даже каждый раз разным, поэтому результат данной функции не определен. Для получения детерминированного результата можно использовать функцию min или max вместо any.
В некоторых случаях вы всё-таки можете вычислить порядок выполнения запроса. Это - случаи, когда SELECT идёт из подзапроса, в котором используется ORDER BY.
Доступны в запросе SELECTсекции GROUP BYили хотя бы одной агрегатной функции RT.WideStore (в отличие от, например, MySQL), чтобы все выражения в секциях вычислялись SELECTиз ключей или из агрегатных функций. То есть, каждый выбираемый из таблицы, должен быть либо в ключах, либо внутри агрегатных функций. Чтобы получить поведение, как в MySQL, вы можете разместить остальные столбцы в агрегатной функции .HAVINGORDER BYany
Результат равному квадратному корню от varPop(x).
Примечание:
Функция использует вычислительно неустойчивый алгоритм. Если для ваших расчётов необходима вычислительная устойчивость, используйте функцию `stddevPopStable`. Она работает медленнее, но обеспечивает меньшую вычислительную ошибку.
Результат равному квадратному корню от varSamp(x).
Примечание:
Функция использует вычислительно неустойчивый алгоритм. Если для ваших расчётов необходима вычислительная устойчивость, используйте функцию `stddevSampStable`. Она работает медленнее, но обеспечивает меньшую вычислительную ошибку.
Вычисляет значение Σ((x - x̅)^2) / n, где n- размер выборки, x̅- среднее значение x.
То есть дисперсию для настройки параметров. Возвращает Float64.
Примечание:
Функция использует вычислительно неустойчивый алгоритм. Если для ваших расчётов необходима вычислительная устойчивость, используйте функцию `varPopStable`. Она работает медленнее, но обеспечивает меньшую вычислительную ошибку.
Вычисляет значение Σ((x - x̅)^2) / (n - 1), где n- размер выборки, x̅- среднее значение x.
Она представляет собой несмещённую дисперсию случайной величины, если переданные в функции значения выборкой этой случайной величины.
Возвращает Float64. В случае, когда n <= 1, возвращается +∞.
Примечание:
Функция использует вычислительно неустойчивый алгоритм. Если для ваших расчётов необходима вычислительная устойчивость, используйте функцию `varSampStable`. Она работает медленнее, но обеспечивает меньшую вычислительную ошибку.
Синтаксис:covarPop(x, y)
Вычисляет значение Σ((x - x̅)(y - y̅)) / n.
Примечание:
Функция использует вычислительно неустойчивый алгоритм. Если для ваших расчётов необходима вычислительная устойчивость, используйте функцию `covarPopStable`. Она работает медленнее, но обеспечивает меньшую вычислительную ошибку.
Синтаксис:covarSamp(x, y)
Вычисляет значение Σ((x - x̅)(y - y̅)) / (n - 1).
Возвращает Float64. В случае, когда n <= 1, возвращается +∞.
Примечание:
Функция использует вычислительно неустойчивый алгоритм. Если для ваших расчётов необходима вычислительная устойчивость, используйте функцию `covarSampStable`. Она работает медленнее, но обеспечивает меньшую вычислительную ошибку.
Выбирает часто встречающееся значение с помощью алгоритма «heavy hitters». Если существует значение, которое встречается чаще, чем в половине случаев, в каждом потоке выполнения запроса, то возвращается данное значение. В общем случае, результат недетерминирован.
anyHeavy(column)Аргументы:
column — имя столбца.Пример:
Возьмём набор данных OnTime и выберем произвольное часто встречающееся значение в столбце AirlineID.
SELECT anyHeavy(AirlineID) AS resFROM ontime
┌───res─┐
│ 19690 │
└───────┘Выбирает последнее попавшееся значение. Результат так же недетерминирован, как и для функции any.
Вычисляет значение arg при минимальном значении val. Если есть несколько разных значений arg для минимальных значений val, возвращает первое попавшееся из таких значений.
Синтаксис:
argMin(arg, val)Аргументы:
arg — аргумент.val — значение.Возвращаемое значение:
arg, соответствующее минимальному значению val.Тип: соответствует типу arg.
Пример:
Исходная таблица:
┌─user─────┬─salary─┐
│ director │ 5000 │
│ manager │ 3000 │
│ worker │ 1000 │
└──────────┴────────┘Запрос:
SELECT argMin(user, salary) FROM salary;Результат:
┌─argMin(user, salary)─┐
│ worker │
└────────────────────────┘Вычисляет значение arg при максимальном значении val. Если есть несколько разных значений arg для максимальных значений val, возвращает первое попавшееся из таких значений.
Синтаксис:
argMax(arg, val)Аргументы:
arg — аргумент.val — значение.Возвращаемое значение:
arg, соответствующее максимальному значению val.Тип: соответствует типу arg.
Пример:
Исходная таблица:
┌─user─────┬─salary─┐
│ director │ 5000 │
│ manager │ 3000 │
│ worker │ 1000 │
└──────────┴────────┘Запрос:
SELECT argMax(user, salary), argMax(tuple(user, salary), salary) FROM salary;Результат:
┌─argMax(user, salary)─┬─argMax(tuple(user, salary), salary)─┐
│ director │ ('director',5000) │
└────────────────────────┴────────────────────────────────────────┘Вычисляет среднее арифметическое взвешенное.
Синтаксис:
avgWeighted(x, weight)Аргументы:
x — значения. Целые числа или числа с плавающей запятой.weight — веса отдельных значений. Целые числа или числа с плавающей запятой.Типы параметров должны совпадать.
Возвращаемое значение:
NaN, если все веса равны 0.Тип: Float64
Пример:
Запрос:
SELECT avgWeighted(x, w)FROM values('x Int8, w Int8', (4, 1), (1, 0), (10, 2))Результат:
┌─avgWeighted(x, weight)─┐
│ 8 │
└──────────────────────────┘Возвращает массив наиболее часто встречающихся значений в указанном столбце. Результирующий массив упорядочен по убыванию частоты значения (не по самим значениям).
Реализует Filtered Space-Saving алгоритм для анализа TopK, на основе reduce-and-combine алгоритма из методики Parallel Space Saving.
topK(N)(column)Функция не дает гарантированного результата. В некоторых ситуациях могут возникать ошибки, и функция возвращает частые, но не наиболее частые значения.
Рекомендуем использовать значения N < 10, при больших N снижается производительность. Максимально возможное значение N = 65536.
Аргументы:
N – количество значений.x – столбец.Пример:
Возьмём набор данных OnTime и выберем 3 наиболее часто встречающихся значения в столбце AirlineID.
SELECT topK(3)(AirlineID) AS resFROM ontime
┌─res──────────────────┐
│ [19393,19790,19805] │
└───────────────────────┘Возвращает массив наиболее часто встречающихся значений в указанном столбце. Результирующий массив упорядочен по убыванию частоты значения (не по самим значениям). Дополнительно учитывается вес значения.
Синтаксис:
topKWeighted(N)(x, weight)Аргументы:
N — количество элементов для выдачи.x — значение.weight — вес. Каждое значение учитывается weight раз при расчёте частоты. UInt64.Возвращаемое значение:
Возвращает массив значений с максимально приближенной суммой весов.
Пример:
Запрос:
SELECT topKWeighted(10)(number, number) FROM numbers(1000)Результат:
┌─topKWeighted(10)(number, number)───────────┐
│ [999,998,997,996,995,994,993,992,991,990] │
└───────────────────────────────────────────────┘Смотрите также:
Синтаксис:
groupArray(x)или
groupArray(max_size)(x)Составляет массив из значений аргумента. Значения в массив могут быть добавлены в любом (недетерминированном) порядке.
Вторая версия (с параметром max_size) ограничивает размер результирующего массива max_size элементами. Например, groupArray(1)(x) эквивалентно [any(x)].
В некоторых случаях, вы всё же можете рассчитывать на порядок выполнения запроса. Это — случаи, когда SELECT идёт из подзапроса, в котором используется ORDER BY.
Синтаксис:
groupUniqArray(x)или
groupUniqArray(max_size)(x)Составляет массив из различных значений аргумента. Расход оперативной памяти такой же, как у функции uniqExact.
Функция groupUniqArray(max_size)(x) ограничивает размер результирующего массива до max_size элементов. Например, groupUniqArray(1)(x) равнозначно [any(x)].
Вставляет значение в заданную позицию массива.
Синтаксис:
groupArrayInsertAt(default_x, size)(x, pos)Если запрос вставляет вставляется несколько значений в одну и ту же позицию, то функция ведет себя следующим образом:
Аргументы:
x — значение, которое будет вставлено. Выражение, возвращающее значение одного из поддерживаемых типов данных.pos — позиция, в которую вставляется заданный элемент x. Нумерация индексов в массиве начинается с нуля. UInt32.default_x — значение по умолчанию для подстановки на пустые позиции. Опциональный параметр. Выражение, возвращающее значение с типом параметра x. Если default_x не определен, используются значения по умолчанию.size — длина результирующего массива. Опциональный параметр. При использовании этого параметра должно быть указано значение по умолчанию default_x. UInt32.Возвращаемое значение:
Тип: Array.
Примеры:
Запрос:
SELECT groupArrayInsertAt(toString(number), number * 2) FROM numbers(5);Результат:
┌─groupArrayInsertAt(toString(number), multiply(number, 2))─┐
│ ['0','','1','','2','','3','','4'] │
└────────────────────────────────────────────────────────────────┘Запрос:
SELECT groupArrayInsertAt('-')(toString(number), number * 2) FROM numbers(5);Результат:
┌─groupArrayInsertAt('-')(toString(number), multiply(number, 2))─┐
│ ['0','-','1','-','2','-','3','-','4'] │
└──────────────────────────────────────────────────────────────────────┘Запрос:
SELECT groupArrayInsertAt('-', 5)(toString(number), number * 2) FROM numbers(5);Результат:
┌─groupArrayInsertAt('-', 5)(toString(number), multiply(number, 2))─┐
│ ['0','-','1','-','2'] │
└─────────────────────────────────────────────────────────────────────────┘Многопоточная вставка элементов в одну позицию.
Запрос:
SELECT groupArrayInsertAt(number, 0) FROM numbers_mt(10) SETTINGS max_block_size = 1;В результат этого запроса мы получите случайное целое число в диапазоне [0,9].
Например:
┌─groupArrayInsertAt(number, 0)─┐
│ [7] │
└──────────────────────────────────┘Вычисляет скользящее среднее для входных значений.
groupArrayMovingAvg(numbers_for_summing)groupArrayMovingAvg(window_size)(numbers_for_summing)Функция может принимать размер окна в качестве параметра. Если окно не указано, то функция использует размер окна, равный количеству строк в столбце.
Аргументы:
numbers_for_summing — выражение, возвращающее значение числового типа.window_size — размер окна.Возвращаемые значения:
Функция использует округление к меньшему по модулю. Оно усекает десятичные разряды, незначимые для результирующего типа данных.
Пример:
Таблица с исходными данными:
CREATE TABLE t( `int` UInt8, `float` Float32, `dec` Decimal32(2))ENGINE = TinyLog
┌─int─┬─float─┬──dec─┐
│ 1 │ 1.1 │ 1.10 │
│ 2 │ 2.2 │ 2.20 │
│ 4 │ 4.4 │ 4.40 │
│ 7 │ 7.77 │ 7.77 │
└─────┴────────┴──────┘Запросы:
SELECT groupArrayMovingAvg(int) AS I, groupArrayMovingAvg(float) AS F, groupArrayMovingAvg(dec) AS DFROM t
┌─I──────────┬─F──────────────────────────────────────┬─D───────────────────────┐
│ [0,0,1,3] │ [0.275,0.82500005,1.9250001,3.8675] │ [0.27,0.82,1.92,3.86] │
└────────────┴────────────────────────────────────────┴─────────────────────────┘
SELECT groupArrayMovingAvg(2)(int) AS I, groupArrayMovingAvg(2)(float) AS F, groupArrayMovingAvg(2)(dec) AS DFROM t
┌─I──────────┬─F───────────────────────────────────┬─D───────────────────────┐
│ [0,1,3,5] │ [0.55,1.6500001,3.3000002,6.085] │ [0.55,1.65,3.30,6.08] │
└────────────┴─────────────────────────────────────┴─────────────────────────┘Вычисляет скользящую сумму входных значений.
groupArrayMovingSum(numbers_for_summing)groupArrayMovingSum(window_size)(numbers_for_summing)Функция может принимать размер окна в качестве параметра. Если окно не указано, то функция использует размер окна, равный количеству строк в столбце.
Аргументы:
numbers_for_summing — выражение, возвращающее значение числового типа.window_size — размер окна.Возвращаемые значения:
Пример:
Таблица с исходными данными:
CREATE TABLE t( `int` UInt8, `float` Float32, `dec` Decimal32(2))ENGINE = TinyLog
┌─int─┬─float─┬──dec─┐
│ 1 │ 1.1 │ 1.10 │
│ 2 │ 2.2 │ 2.20 │
│ 4 │ 4.4 │ 4.40 │
│ 7 │ 7.77 │ 7.77 │
└─────┴────────┴──────┘Запросы:
SELECT groupArrayMovingSum(int) AS I, groupArrayMovingSum(float) AS F, groupArrayMovingSum(dec) AS DFROM t
┌─I───────────┬─F──────────────────────────────────┬─D────────────────────────┐
│ [1,3,7,14] │ [1.1,3.3000002,7.7000003,15.47] │ [1.10,3.30,7.70,15.47] │
└─────────────┴────────────────────────────────────┴──────────────────────────┘
SELECT groupArrayMovingSum(2)(int) AS I, groupArrayMovingSum(2)(float) AS F, groupArrayMovingSum(2)(dec) AS DFROM t
┌─I───────────┬─F──────────────────────────────────┬─D────────────────────────┐
│ [1,3,6,11] │ [1.1,3.3000002,6.6000004,12.17] │ [1.10,3.30,6.60,12.17] │
└─────────────┴────────────────────────────────────┴──────────────────────────┘Применяет побитовое И для последовательности чисел.
groupBitAnd(expr)Аргументы:
expr – выражение, результат которого имеет тип данных UInt*.
Возвращаемое значение:
Значение типа UInt*.
Пример:
Тестовые данные:
binary decimal00101100 = 4400011100 = 2800001101 = 1301010101 = 85Запрос:
SELECT groupBitAnd(num) FROM tГде num — столбец с тестовыми данными.
Результат:
binary decimal00000100 = 4Применяет побитовое ИЛИ для последовательности чисел.
groupBitOr(expr)Аргументы:
expr – выражение, результат которого имеет тип данных UInt*.
Возвращаемое значение:
Значение типа UInt*.
Пример:
Тестовые данные:
binary decimal00101100 = 4400011100 = 2800001101 = 1301010101 = 85Запрос:
SELECT groupBitOr(num) FROM tГде num — столбец с тестовыми данными.
Результат:
binary decimal01111101 = 125Применяет побитовое ИСКЛЮЧАЮЩЕЕ ИЛИ для последовательности чисел.
groupBitXor(expr)Аргументы:
expr – выражение, результат которого имеет тип данных UInt*.
Возвращаемое значение:
Значение типа UInt*.
Пример:
Тестовые данные:
binary decimal00101100 = 4400011100 = 2800001101 = 1301010101 = 85Запрос:
SELECT groupBitXor(num) FROM tГде num — столбец с тестовыми данными.
Результат:
binary decimal01101000 = 104Bitmap или агрегатные вычисления для столбца с типом данных UInt*, возвращают кардинальность в виде значения типа UInt64, если добавить суффикс -State, то возвращают объект bitmap.
groupBitmap(expr)Аргументы:
expr – выражение, результат которого имеет тип данных UInt*.
Возвращаемое значение:
Значение типа UInt64.
Пример:
Тестовые данные:
UserID1123Запрос:
SELECT groupBitmap(UserID) as num FROM tРезультат:
num3Вычисляет сумму чисел, используя для результата тот же тип данных, что и для входных параметров. Если сумма выйдет за максимальное значение для заданного типа данных, то функция вернёт ошибку.
Работает только для чисел.
Производит суммирование массива ‘value’ по соответствующим ключам заданным в массиве ‘key’. Передача кортежа ключей и значений массива синонимично передаче двух массивов ключей и значений. Количество элементов в ‘key’ и ‘value’ должно быть одинаковым для каждой строки, для которой происходит суммирование. Возвращает кортеж из двух массивов - ключи в отсортированном порядке и значения, просуммированные по соответствующим ключам.
sumMap(key, value), sumMap(Tuple(key, value))Пример:
CREATE TABLE sum_map( date Date, timeslot DateTime, statusMap Nested( status UInt16, requests UInt64 ), statusMapTuple Tuple(Array(Int32), Array(Int32))) ENGINE = Log;INSERT INTO sum_map VALUES ('2000-01-01', '2000-01-01 00:00:00', [1, 2, 3], [10, 10, 10], ([1, 2, 3], [10, 10, 10])), ('2000-01-01', '2000-01-01 00:00:00', [3, 4, 5], [10, 10, 10], ([3, 4, 5], [10, 10, 10])), ('2000-01-01', '2000-01-01 00:01:00', [4, 5, 6], [10, 10, 10], ([4, 5, 6], [10, 10, 10])), ('2000-01-01', '2000-01-01 00:01:00', [6, 7, 8], [10, 10, 10], ([6, 7, 8], [10, 10, 10]));SELECT timeslot, sumMap(statusMap.status, statusMap.requests), sumMap(statusMapTuple)FROM sum_mapGROUP BY timeslot
┌─────────────timeslot─┬─sumMap(statusMap.status, statusMap.requests)─┬─sumMap(statusMapTuple)──────────┐
│ 2000-01-01 00:00:00 │ ([1,2,3,4,5],[10,10,20,10,10]) │ ([1,2,3,4,5],[10,10,20,10,10]) │
│ 2000-01-01 00:01:00 │ ([4,5,6,7,8],[10,10,20,10,10]) │ ([4,5,6,7,8],[10,10,20,10,10]) │
└───────────────────────┴──────────────────────────────────────────────────┴───────────────────────────────────┘Вычисляет выборочный коэффициент асимметрии для последовательности.
Он представляет собой несмещенную оценку асимметрии случайной величины, если переданные значения образуют ее выборку.
skewSamp(expr)Аргументы:
expr — выражение, возвращающее число.
Возвращаемое значение:
Коэффициент асимметрии заданного распределения. Тип — Float64. Если n <= 1 (n — размер выборки), тогда функция возвращает nan.
Пример:
SELECT skewSamp(value) FROM series_with_value_column;Вычисляет коэффициент асимметрии для последовательности.
skewPop(expr)Аргументы:
expr — выражение, возвращающее число.
Возвращаемое значение:
Коэффициент асимметрии заданного распределения. Тип — Float64
Пример:
SELECT skewPop(value) FROM series_with_value_column;Вычисляет выборочный коэффициент эксцесса для последовательности.
Он представляет собой несмещенную оценку эксцесса случайной величины, если переданные значения образуют ее выборку.
kurtSamp(expr)Аргументы:
expr — выражение, возвращающее число.
Возвращаемое значение:
Коэффициент эксцесса заданного распределения. Тип — Float64. Если n <= 1 (n — размер выборки), тогда функция возвращает nan.
Пример:
SELECT kurtSamp(value) FROM series_with_value_column;Вычисляет коэффициент эксцесса последовательности.
kurtPop(expr)Аргументы:
expr — выражение, возвращающее число.
Возвращаемое значение:
Коэффициент эксцесса заданного распределения. Тип — Float64
Пример:
SELECT kurtPop(value) FROM series_with_value_column;Приближённо вычисляет количество различных значений аргумента.
uniq(x[, ...])Аргументы:
Функция принимает переменное число входных параметров. Параметры могут быть числовых типов, а также Tuple, Array, Date, DateTime, String.
Возвращаемое значение:
Детали реализации:
Функция:
Вычисляет хэш для всех параметров агрегации, а затем использует его в вычислениях.
Использует адаптивный алгоритм выборки. В качестве состояния вычисления функция использует выборку хэш-значений элементов размером до 65536.
Этот алгоритм очень точен и очень эффективен по использованию CPU. Если запрос содержит небольшое количество этих функций, использование `uniq` почти так же эффективно, как и использование других агрегатных функций.
Результат детерминирован (не зависит от порядка выполнения запроса).
Эту функцию рекомендуется использовать практически во всех сценариях.
Смотрите также:
Вычисляет точное количество различных значений аргументов.
uniqExact(x[, ...])Функцию uniqExact следует использовать, если вам обязательно нужен точный результат. В противном случае используйте функцию uniq.
Функция uniqExact расходует больше оперативной памяти, чем функция uniq, так как размер состояния неограниченно растёт по мере роста количества различных значений.
Аргументы:
Функция принимает переменное число входных параметров. Параметры могут быть числовых типов, а также Tuple, Array, Date, DateTime, String.
Смотрите также:
Приближённо вычисляет количество различных значений аргумента.
uniqCombined(HLL_precision)(x[, ...])Функция uniqCombined — это хороший выбор для вычисления количества различных значений.
Аргументы:
Функция принимает переменное число входных параметров. Параметры могут быть числовых типов, а также Tuple, Array, Date, DateTime, String.
HLL_precision — это логарифм по основанию 2 от числа ячеек в HyperLogLog. Необязательный, можно использовать функцию как uniqCombined (x [,...]). Для HLL_precision значение по умолчанию — 17, что фактически составляет 96 КБ пространства (2^17 ячеек, 6 бит каждая).
Возвращаемое значение:
Детали реализации:
Функция:
Вычисляет хэш (64-битный для String и 32-битный для всех остальных типов) для всех параметров агрегации, а затем использует его в вычислениях.
Используется комбинация трёх алгоритмов: массив, хэш-таблица и HyperLogLog с таблицей коррекции погрешности.
Для небольшого количества различных значений используется массив. Если размер набора больше, используется хэш-таблица. При дальнейшем увеличении количества значений, используется структура HyperLogLog, имеющая фиксированный размер в памяти.
Результат детерминирован (не зависит от порядка выполнения запроса).
Так как используется 32-битный хэш для не-`String` типов, результат будет иметь очень очень большую ошибку для количества разичных элементов существенно больше `UINT_MAX` (ошибка быстро растёт начиная с нескольких десятков миллиардов различных значений), таким образом в этом случае нужно использовать [uniqCombined64](#agg_function-uniqcombined64)
По сравнению с функцией uniq, uniqCombined:
Потребляет в несколько раз меньше памяти.
Вычисляет с в несколько раз более высокой точностью.
Обычно имеет немного более низкую производительность. В некоторых сценариях uniqCombined может показывать более высокую производительность, чем uniq, например, в случае распределенных запросов, при которых по сети передаётся большое количество состояний агрегации.
Смотрите также:
Использует 64-битный хэш для всех типов, в отличие от uniqCombined.
Вычисляет приблизительное число различных значений аргументов, используя алгоритм HyperLogLog.
uniqHLL12(x[, ...])Аргументы:
Функция принимает переменное число входных параметров. Параметры могут быть числовых типов, а также Tuple, Array, Date, DateTime, String.
Возвращаемое значение:
Детали реализации:
Функция:
Вычисляет хэш для всех параметров агрегации, а затем использует его в вычислениях.
Использует алгоритм HyperLogLog для аппроксимации числа различных значений аргументов.
Используется 2^12 5-битовых ячеек. Размер состояния чуть больше 2.5 КБ. Результат не точный (ошибка до ~10%) для небольших множеств (<10K элементов). Однако для множеств большой кардинальности (10K - 100M) результат довольно точен (ошибка до ~1.6%). Начиная с 100M ошибка оценки будет только расти и для множеств огромной кардинальности (1B+ элементов) функция возвращает результат с очень большой неточностью.
Результат детерминирован (не зависит от порядка выполнения запроса).
Мы не рекомендуем использовать эту функцию. В большинстве случаев используйте функцию uniq или uniqCombined.
Приблизительно вычисляет квантиль числовой последовательности.
Функция использует алгоритм reservoir sampling с размером резервуара до 8192 и случайным генератором чисел для для сэмплирования. Результат не детерминирован. Чтобы получить точную квантиль используйте функцию quantileExact.
Внутренние состояния функций quantile* не объединяются, если они используются в одном запросе. Если вам необходимо вычислить квантили нескольких уровней, используйте функцию quantiles, это повысит эффективность запроса.
Синтаксис:
quantile(level)(expr)Алиас: median.
Аргументы:
level — уровень квантили. Опционально. Константное значение с плавающей запятой от 0 до 1. Мы рекомендуем использовать значение level из диапазона [0.01, 0.99]. Значение по умолчанию: 0.5. При level=0.5 функция вычисляет медиану.expr — выражение, зависящее от значений столбцов, возвращающее данные числовых типов или типов Date, DateTime.Возвращаемое значение:
Тип:
Date.DateTime.Пример:
Входная таблица:
┌─val─┐
│ 1 │
│ 1 │
│ 2 │
│ 3 │
└─────┘Запрос:
SELECT quantile(val) FROM tРезультат:
┌─quantile(val)─┐
│ 1.5 │
└────────────────┘Смотрите также:
Синтаксис:
quantiles(level1, level2, …)(x)Все функции для вычисления квантилей имеют соответствующие функции для вычисления нескольких квантилей: quantiles, quantilesDeterministic, quantilesTiming, quantilesTimingWeighted, quantilesExact, quantilesExactWeighted, quantilesTDigest, quantilesBFloat16. Эти функции вычисляют все квантили указанных уровней в один проход и возвращают массив с вычисленными значениями.
Точно вычисляет квантиль числовой последовательности.
Чтобы получить точный результат, все переданные значения собираются в массив, который затем частично сортируется. Таким образом, функция потребляет объем памяти O(n), где n — количество переданных значений. Для небольшого числа значений эта функция эффективна.
Внутренние состояния функций quantile* не объединяются, если они используются в одном запросе. Если вам необходимо вычислить квантили нескольких уровней, используйте функцию quantiles, это повысит эффективность запроса.
Синтаксис
quantileExact(level)(expr)Алиас: medianExact.
Аргументы:
level — уровень квантили. Опционально. Константное значение с плавающей запятой от 0 до 1. Мы рекомендуем использовать значение level из диапазона [0.01, 0.99]. Значение по умолчанию: 0.5. При level=0.5 функция вычисляет медиану.expr — выражение, зависящее от значений столбцов, возвращающее данные числовых типов или типов Date, DateTime.Возвращаемое значение:
Тип:
Date.DateTime.Пример:
Запрос:
SELECT quantileExact(number) FROM numbers(10)Результат:
┌─quantileExact(number)─┐
│ 5 │
└─────────────────────────┘Как и quantileExact, эта функция вычисляет точный квантиль числовой последовательности данных.
Чтобы получить точное значение, все переданные значения объединяются в массив, который затем полностью сортируется. Сложность алгоритма сортировки равна O(N·log(N)), где N = std::distance(first, last).
Возвращаемое значение зависит от уровня квантили и количества элементов в выборке, то есть если уровень 0,5, то функция возвращает нижнюю медиану при чётном количестве элементов и медиану при нечётном. Медиана вычисляется аналогично реализации median_low, которая используется в python.
Для всех остальных уровней возвращается элемент с индексом, соответствующим значению level * size_of_array. Например:
SELECT quantileExactLow(0.1)(number) FROM numbers(10)
┌─quantileExactLow(0.1)(number)─┐
│ 1 │
└──────────────────────────────────┘При использовании в запросе нескольких функций quantile* с разными уровнями, внутренние состояния не объединяются (то есть запрос работает менее эффективно). В этом случае используйте функцию quantiles.
Синтаксис:
quantileExact(level)(expr)Алиас: medianExactLow.
Аргументы:
level — уровень квантили. Опциональный параметр. Константное занчение с плавающей запятой от 0 до 1. Мы рекомендуем использовать значение level из диапазона [0.01, 0.99]. Значение по умолчанию: 0.5. При level=0.5 функция вычисляет медиану.expr — выражение, зависящее от значений столбцов, возвращающее данные числовых типов, Date или DateTime.Возвращаемое значение:
Тип:
Date.DateTime.Пример:
Запрос:
SELECT quantileExactLow(number) FROM numbers(10)Результат:
┌─quantileExactLow(number)─┐
│ 4 │
└────────────────────────────┘Как и quantileExact, эта функция вычисляет точный квантиль числовой последовательности данных.
Все переданные значения объединяются в массив, который затем сортируется, чтобы получить точное значение. Сложность алгоритма сортировки равна O(N·log(N)), где N = std::distance(first, last).
Возвращаемое значение зависит от уровня квантили и количества элементов в выборке, то есть если уровень 0,5, то функция возвращает верхнюю медиану при чётном количестве элементов и медиану при нечётном. Медиана вычисляется аналогично реализации median_high, которая используется в python. Для всех остальных уровней возвращается элемент с индексом, соответствующим значению level * size_of_array.
Эта реализация ведет себя точно так же, как quantileExact.
При использовании в запросе нескольких функций quantile* с разными уровнями, внутренние состояния не объединяются (то есть запрос работает менее эффективно). В этом случае используйте функцию quantiles.
Синтаксис:
quantileExactHigh(level)(expr)Алиас: medianExactHigh.
Аргументы:
level — уровень квантили. Опциональный параметр. Константное занчение с плавающей запятой от 0 до 1. Мы рекомендуем использовать значение level из диапазона [0.01, 0.99]. Значение по умолчанию: 0.5. При level=0.5 функция вычисляет медиану.expr — выражение, зависящее от значений столбцов, возвращающее данные числовых типов, Date или DateTime.Возвращаемое значение:
Тип:
Date.DateTime.Пример:
Запрос:
SELECT quantileExactHigh(number) FROM numbers(10)Результат:
┌─quantileExactHigh(number)─┐
│ 5 │
└─────────────────────────────┘Точно вычисляет квантиль числовой последовательности.
Чтобы получить точный результат, все переданные значения собираются в массив, который затем частично сортируется. Таким образом, функция потребляет объем памяти O(n), где n — количество переданных значений. Для небольшого числа значений эта функция эффективна.
Эта функция эквивалентна Excel функции PERCENTILE.EXC, тип R6.
Если в одном запросе вызывается несколько функций quantileExactExclusive с разными значениями level, эти функции вычисляются независимо друг от друга. В таких случаях используйте функцию quantilesExactExclusive, запрос будет выполняться эффективнее.
Синтаксис:
quantileExactExclusive(level)(expr)Аргументы:
expr — выражение, зависящее от значений столбцов. Возвращает данные числовых типов, Date или DateTime.Параметры:
level — уровень квантиля. Необязательный параметр. Возможные значения: (0, 1) — граничные значения не учитываются. Значение по умолчанию: 0.5. При level=0.5 функция вычисляет медиану. Float.Возвращаемое значение:
Тип:
Date.DateTime.Пример:
Запрос:
CREATE TABLE num AS numbers(1000);SELECT quantileExactExclusive(0.6)(x) FROM (SELECT number AS x FROM num);Результат:
┌─quantileExactExclusive(0.6)(x)─┐
│ 599.6 │
└───────────────────────────────────┘Вычисляет квантиль числовой последовательности с детерминированной точностью.
Результат детерминирован (не зависит от порядка обработки запроса). Функция оптимизирована для работы с последовательностями, описывающими такие распределения, как время загрузки веб-страниц или время отклика бэкенда.
Внутренние состояния функций quantile* не объединяются, если они используются в одном запросе. Если вам необходимо вычислить квантили нескольких уровней, используйте функцию quantiles, это повысит эффективность запроса.
Синтаксис:
quantileTiming(level)(expr)Алиас: medianTiming.
Аргументы:
level — уровень квантили. Опционально. Константное значение с плавающей запятой от 0 до 1. Мы рекомендуем использовать значение level из диапазона [0.01, 0.99]. Значение по умолчанию: 0.5. При level=0.5 функция вычисляет медиану.
expr — выражение, зависящее от значений столбцов, возвращающее данные типа Float*.
Если в функцию передать отрицательные значения, то её поведение не определено.- Если значение больше, чем 30 000 (например, время загрузки страницы превышает 30 секунд), то оно приравнивается к 30 000.
Точность:
Вычисления точны при соблюдении следующих условий:
В противном случае, результат вычисления округляется до ближайшего множителя числа 16.
Примечание: Для указанного типа последовательностей функция производительнее и точнее, чем quantile
Возвращаемое значение:
Тип: Float32.
Примечание:
Если в функцию `quantileTimingIf` не передать значений, то вернётся NaN. Это необходимо для отделения подобных случаев от случаев, когда результат 0. Подробности про сортировку `NaN` cмотрите в разделе Секция ORDER BY.
Пример:
Входная таблица:
┌─response_time─┐
│ 72 │
│ 112 │
│ 126 │
│ 145 │
│ 104 │
│ 242 │
│ 313 │
│ 168 │
│ 108 │
└────────────────┘Запрос:
SELECT quantileTiming(response_time) FROM tРезультат:
┌─quantileTiming(response_time)─┐
│ 126 │
└─────────────────────────────────┘Смотрите также:
С детерминированной точностью вычисляет квантиль числовой последовательности, учитывая вес каждого элемента.
Результат детерминирован (не зависит от порядка обработки запроса). Функция оптимизирована для работы с последовательностями, описывающими такие распределения, как время загрузки веб-страниц или время отклика бэкенда.
Внутренние состояния функций quantile* не объединяются, если они используются в одном запросе. Если вам необходимо вычислить квантили нескольких уровней, используйте функцию quantiles, это повысит эффективность запроса.
Синтаксис:
quantileTimingWeighted(level)(expr, weight)Алиас: medianTimingWeighted.
Аргументы:
level — уровень квантили. Опционально. Константное значение с плавающей запятой от 0 до 1. Мы рекомендуем использовать значение level из диапазона [0.01, 0.99]. Значение по умолчанию: 0.5. При level=0.5 функция вычисляет медиану.
expr — выражение, зависящее от значений столбцов, возвращающее данные типа Float*.
Если в функцию передать отрицательные значения, то её поведение не определено.- Если значение больше, чем 30 000 (например, время загрузки страницы превышает 30 секунд), то оно приравнивается к 30 000.
weight — столбец с весам элементов последовательности. Вес — это количество повторений элемента в последовательности.
Точность:
Вычисления точны при соблюдении следующих условий:
В противном случае, результат вычисления округляется до ближайшего множителя числа 16.
Примечание:
Для указанного типа последовательностей функция производительнее и точнее, чем quantile.
Возвращаемое значение:
Тип: Float32.
Примечание:
Если в функцию `quantileTimingIf` не передать значений, то вернётся NaN. Это необходимо для отделения подобных случаев от случаев, когда результат 0. Подробности про сортировку `NaN` cмотрите в разделе Секция ORDER BY.
Пример:
Входная таблица:
┌─response_time─┬─weight─┐
│ 68 │ 1 │
│ 104 │ 2 │
│ 112 │ 3 │
│ 126 │ 2 │
│ 138 │ 1 │
│ 162 │ 1 │
└────────────────┴─────────┘Запрос:
SELECT quantileTimingWeighted(response_time, weight) FROM tРезультат:
┌─quantileTimingWeighted(response_time, weight)─┐
│ 112 │
└───────────────────────────────────────────────────┘Смотрите также:
Приблизительно вычисляет квантиль числовой последовательности.
Функция использует алгоритм reservoir sampling с размером резервуара до 8192 и детерминированным алгоритмом сэмплирования. Результат детерминирован. Чтобы получить точную квантиль используйте функцию quantileExact.
Внутренние состояния функций quantile* не объединяются, если они используются в одном запросе. Если вам необходимо вычислить квантили нескольких уровней, используйте функцию quantiles, это повысит эффективность запроса.
Синтаксис:
quantileDeterministic(level)(expr, determinator)Алиас: medianDeterministic.
Аргументы:
level — уровень квантили. Опционально. Константное значение с плавающей запятой от 0 до 1. Мы рекомендуем использовать значение level из диапазона [0.01, 0.99]. Значение по умолчанию: 0.5. При level=0.5 функция вычисляет медиану.expr — выражение, зависящее от значений столбцов, возвращающее данные числовых типов или типов Date, DateTime.determinator — число, хэш которого используется при сэмплировании в алгоритме «Reservoir sampling», чтобы сделать результат детерминированным. В качестве значения можно использовать любое определённое положительное число, например, идентификатор пользователя или события. Если одно и то же значение попадается в выборке слишком часто, то функция выдаёт некорректный результат.Возвращаемое значение:
Тип:
Date.DateTime.Пример:
Входная таблица:
┌─val─┐
│ 1 │
│ 1 │
│ 2 │
│ 3 │
└─────┘Запрос:
SELECT quantileDeterministic(val, 1) FROM tРезультат:
┌─quantileDeterministic(val, 1)─┐
│ 1.5 │
└──────────────────────────────────┘Смотрите также:
Приблизительно вычисляет квантиль числовой последовательности, используя алгоритм t-digest.
Максимальная ошибка 1%. Потребление памяти — log(n), где n — число значений. Результат не детерминирован и зависит от порядка выполнения запроса.
Производительность функции ниже, чем производительность функции quantile или quantileTiming. По соотношению размера состояния к точности вычисления, эта функция значительно превосходит quantile.
Внутренние состояния функций quantile* не объединяются, если они используются в одном запросе. Если вам необходимо вычислить квантили нескольких уровней, используйте функцию quantiles, это повысит эффективность запроса.
Синтаксис:
quantileTDigest(level)(expr)Алиас: medianTDigest.
Аргументы:
level — уровень квантили. Опционально. Константное значение с плавающей запятой от 0 до 1. Мы рекомендуем использовать значение level из диапазона [0.01, 0.99]. Значение по умолчанию: 0.5. При level=0.5 функция вычисляет медиану.expr — выражение, зависящее от значений столбцов, возвращающее данные числовых типов или типов Date, DateTime.Возвращаемое значение:
Тип:
Date.DateTime.Пример:
Запрос:
SELECT quantileTDigest(number) FROM numbers(10)Результат:
┌─quantileTDigest(number)─┐
│ 4.5 │
└───────────────────────────┘Смотрите также:
Приблизительно вычисляет квантиль числовой последовательности, используя алгоритм t-digest. Функция учитывает вес каждого элемента последовательности.
Максимальная ошибка 1%. Потребление памяти — log(n), где n — число значений. Результат не детерминирован и зависит от порядка выполнения запроса.
Производительность функции ниже, чем производительность функции quantile или quantileTiming. По соотношению размера состояния к точности вычисления, эта функция значительно превосходит quantile.
Внутренние состояния функций quantile* не объединяются, если они используются в одном запросе. Если вам необходимо вычислить квантили нескольких уровней, используйте функцию quantiles, это повысит эффективность запроса.
Примечание:
Использование `quantileTDigestWeighted` не рекомендуется для небольших наборов данных и может привести к значительной ошибке. Рассмотрите возможность использования `quantileTDigest` в таких случаях
Синтаксис:
quantileTDigestWeighted(level)(expr, weight)Синоним: medianTDigestWeighted.
Аргументы:
level — уровень квантили. Опционально. Константное значение с плавающей запятой от 0 до 1. Мы рекомендуем использовать значение level из диапазона [0.01, 0.99]. Значение по умолчанию: 0.5. При level=0.5 функция вычисляет медиану.expr — выражение, зависящее от значений столбцов, возвращающее данные числовых типов или типов Date, DateTime.weight — столбец с весам элементов последовательности. Вес — это количество повторений элемента в последовательности.Возвращаемое значение:
Тип:
Date.DateTime.Пример:
Запрос:
SELECT quantileTDigestWeighted(number, 1) FROM numbers(10)Результат:
┌─quantileTDigestWeighted(number, 1)─┐
│ 4.5 │
└───────────────────────────────────────┘Смотрите также:
Приближенно вычисляет квантиль выборки чисел в формате bfloat16. bfloat16 — это формат с плавающей точкой, в котором для представления числа используется 1 знаковый бит, 8 бит для порядка и 7 бит для мантиссы. Функция преобразует входное число в 32-битное с плавающей точкой и обрабатывает его старшие 16 бит. Она вычисляет квантиль в формате bfloat16 и преобразует его в 64-битное число с плавающей точкой, добавляя нулевые биты. Эта функция выполняет быстрые приближенные вычисления с относительной ошибкой не более 0.390625%.
Синтаксис:
quantileBFloat16[(level)](expr)Синоним: medianBFloat16
Аргументы:
Параметры:
level — уровень квантиля. Необязательный параметр. Допустимый диапазон значений от 0 до 1. Значение по умолчанию: 0.5. Float.Возвращаемое значение:
Тип: Float64.
Пример:
В таблице есть столбцы с целыми числами и с числами с плавающей точкой:
┌─a─┬─────b─┐
│ 1 │ 1.001 │
│ 2 │ 1.002 │
│ 3 │ 1.003 │
│ 4 │ 1.004 │
└───┴────────┘Запрос для вычисления 0.75-квантиля (верхнего квартиля):
SELECT quantileBFloat16(0.75)(a), quantileBFloat16(0.75)(b) FROM example_table;Результат:
┌─quantileBFloat16(0.75)(a)─┬─quantileBFloat16(0.75)(b)─┐
│ 3 │ 1 │
└─────────────────────────────┴──────────────────────────────┘Обратите внимание, что все числа с плавающей точкой в примере были округлены до 1.0 при преобразовании к bfloat16.
Версия функции quantileBFloat16, которая учитывает вес каждого элемента последовательности.
См. также:
Выполняет простую (одномерную) линейную регрессию.
simpleLinearRegression(x, y)Параметры:
x — столбец со значениями зависимой переменной.y — столбец со значениями наблюдаемой переменной.Возвращаемые значения:
Константы (a, b) результирующей прямой y = a*x + b.
Примеры:
SELECT arrayReduce('simpleLinearRegression', [0, 1, 2, 3], [0, 1, 2, 3])
┌─arrayReduce('simpleLinearRegression', [0, 1, 2, 3], [0, 1, 2, 3])─┐
│ (1,0) │
└─────────────────────────────────────────────────────────────────────────┘
SELECT arrayReduce('simpleLinearRegression', [0, 1, 2, 3], [3, 4, 5, 6])
┌─arrayReduce('simpleLinearRegression', [0, 1, 2, 3], [3, 4, 5, 6])─┐
│ (1,3) │
└─────────────────────────────────────────────────────────────────────────┘Функция реализует стохастическую линейную регрессию. Поддерживает пользовательские параметры для скорости обучения, коэффициента регуляризации L2, размера mini-batch и имеет несколько методов обновления весов (Adam (по умолчанию), simple SGD, Momentum, Nesterov).
Параметры:
Есть 4 настраиваемых параметра. Они передаются в функцию последовательно, однако не обязательно указывать все, используются значения по умолчанию, однако хорошая модель требует некоторой настройки параметров.
stochasticLinearRegression(1.0, 1.0, 10, 'SGD')
0.00001.0.1.15.Adam (по умолчанию), SGD, Momentum, Nesterov. Momentum и Nesterov более требовательные к вычислительным ресурсам и памяти, однако они имеют высокую скорость схождения и устойчивости методов стохастического градиента.Использование:
stochasticLinearRegression используется на двух этапах: построение модели и предсказание новых данных. Чтобы построить модель и сохранить её состояние для дальнейшего использования, мы используем комбинатор -State. Для прогнозирования мы используем функцию evalMLMethod, которая принимает в качестве аргументов состояние и свойства для прогнозирования.
1. Построение модели
Пример запроса:
CREATE TABLE IF NOT EXISTS train_data( param1 Float64, param2 Float64, target Float64) ENGINE = Memory;
CREATE TABLE your_model ENGINE = Memory AS SELECTstochasticLinearRegressionState(0.1, 0.0, 5, 'SGD')
(target, param1, param2)AS state FROM train_data;Здесь нам также нужно вставить данные в таблицу train_data. Количество параметров не фиксировано, оно зависит только от количества аргументов, перешедших в linearRegressionState. Все они должны быть числовыми значениями. Обратите внимание, что столбец с целевым значением (которое мы хотели бы научиться предсказывать) вставляется в качестве первого аргумента.
2. Прогнозирование
После сохранения состояния в таблице мы можем использовать его несколько раз для прогнозирования или смёржить с другими состояниями и создать новые, улучшенные модели.
WITH (SELECT state FROM your_model) AS model SELECTevalMLMethod(model, param1, param2) FROM test_dataЗапрос возвращает столбец прогнозируемых значений. Обратите внимание, что первый аргумент evalMLMethod это объект AggregateFunctionState, далее идут столбцы свойств.
test_data — это таблица, подобная train_data, но при этом может не содержать целевое значение.
Примечания:
1. Объединить две модели можно следующим запросом:
SELECT state1 + state2 FROM your_modelsгде таблица your_models содержит обе модели. Запрос вернёт новый объект AggregateFunctionState.
2. Пользователь может получать веса созданной модели для своих целей без сохранения модели, если не использовать комбинатор -State.
SELECT stochasticLinearRegression(0.01)(target, param1, param2) FROM train_dataПодобный запрос строит модель и возвращает её веса, отвечающие параметрам моделей и смещение. Таким образом, в приведенном выше примере запрос вернет столбец с тремя значениями.
Смотрите также:
Функция реализует стохастическую логистическую регрессию. Её можно использовать для задачи бинарной классификации, функция поддерживает те же пользовательские параметры, что и stochasticLinearRegression и работает таким же образом.
Параметры:
Параметры те же, что и в stochasticLinearRegression: learning rate, l2 regularization coefficient, mini-batch size, method for updating weights. Смотрите раздел parameters.
stochasticLogisticRegression(1.0, 1.0, 10, 'SGD')1. Построение модели
Смотрите раздел Построение модели в описании stochasticLinearRegression .
Прогнозируемые метки должны быть в диапазоне \[-1, 1\].
2. Прогнозирование
Используя сохраненное состояние, можно предсказать вероятность наличия у объекта метки 1.
WITH (SELECT state FROM your_model) AS model SELECTevalMLMethod(model, param1, param2) FROM test_dataЗапрос возвращает столбец вероятностей. Обратите внимание, что первый аргумент evalMLMethod это объект AggregateFunctionState, далее идут столбцы свойств.
Мы также можем установить границу вероятности, которая присваивает элементам различные метки.
SELECT ans < 1.1 AND ans > 0.5 FROM(WITH (SELECT state FROM your_model)
AS model SELECTevalMLMethod(model, param1, param2) AS ans FROM test_data)Тогда результатом будут метки.
test_data — это таблица, подобная train_data, но при этом может не содержать целевое значение.
Смотрите также: