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

Эта статья — небольшой гид по функциям работы с массивами в ClickHouse. Мы рассмотрим самые полезные и мощные инструменты, такие как arrayJoin, arrayMap, arrayFilter, и другие. Разберём, как их использовать для решения повседневных задач аналитики данных, на конкретных примерах.

Почему это важно? Потому что умение грамотно работать с массивами позволяет сократить и упростить код, делая его более читаемым и поддерживаемым. Это ключевой навык для тех, кто хочет писать оптимальные запросы.

Вам могут пригодиться array функции, когда стандартные SQL-запросы становятся сложными и трудными для понимания. Например, вместо использования множества подзапросов и объединений для отслеживания последовательности действий пользователя, вы можете использовать функции работы с массивами. Кроме того, использование функций позволяет фильтровать элементы внутри массива, избавляя от необходимости написания сложных условий в подзапросах. Функции работы с массивами в ClickHouse помогут сократить количество кода и упростить запросы, заменяя многократные подзапросы на более элегантные и читабельные решения.

Создание тестовой таблицы:

Прежде чем мы разберем основные функции для работы с массивами в ClickHouse, давайте создадим тестовую таблицу. Эта таблица позволит вам потренироваться в использовании описанных функций на примере. Создадим таблицу с событиями пользователей — такими как клики, просмотры и покупки — и заполним её данными. С этими данными вы сможете экспериментировать, применяя различные функции к массивам и проверяя их работу на практике.

CREATE TABLE user_events (
user_id UInt32,
event_date Date,
event_type String,
item_id UInt32,
price Float32,
quantity UInt32
) ENGINE = MergeTree()
ORDER BY (user_id, event_date);

INSERT INTO user_events VALUES
(1, '2024-07-01', 'click', 1, 10.0, 1),
(1, '2024-07-01', 'click', 2, 15.0, 1),
(1, '2024-07-02', 'view', 3, 20.0, 1),
(1, '2024-07-03', 'purchase', 1, 10.0, 2),
(1, '2024-07-03', 'purchase', 3, 20.0, 1),
(2, '2024-07-01', 'click', 4, 25.0, 1),
(2, '2024-07-02', 'purchase', 2, 15.0, 2),
(2, '2024-07-02', 'purchase', 4, 25.0, 1),
(3, '2024-07-01', 'view', 5, 30.0, 1),
(3, '2024-07-03', 'click', 6, 35.0, 1)

groupArray, arrayStringConcat и groupUniqArray

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

arrayStringConcat: Функция объединяет элементы массива в одну строку, разделяя их указанным разделителем. Это удобно, когда нужно представить последовательность действий или событий в виде одной строки, например, для создания отчетов или визуализации.

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

Чтобы объединить типы событий для каждого пользователя в одну строку, сначала соберем все события в массив с помощью groupArray, а затем объединим их в строку с arrayStringConcat:

SELECT user_id, arrayStringConcat(groupArray(event_type), ', ') AS concatenated_events
FROM user_events
GROUP BY user_id;

user_id	concatenated_events
3	view, click
2	click, purchase, purchase
1	click, click, view, purchase, purchase

Если вас интересуют только уникальные действия каждого пользователя, можно заменить groupArray на groupUniqArray:

SELECT user_id, arrayStringConcat(groupUniqArray(event_type), ', ') AS concatenated_events
FROM user_events
GROUP BY user_id;

user_id	concatenated_events
3	view, click
2	purchase, click
1	purchase, click, view

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

arrayFilter

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

Функция arrayFilter полезна, когда нужно извлечь из массива только те элементы, которые соответствуют определенным критериям. В нашем примере это условие — цена, превышающая 15.

Синтаксис функции: arrayFilter(x -> condition, array)

x: Переменная, представляющая текущий элемент массива. В этом контексте это может быть любое имя переменной, которое вы задаете.

->: Этот оператор отделяет переменную от условия. Это символ, который используется в лямбда-выражениях для указания, что переменная x используется в выражении справа.

condition: Условие, которому должны соответствовать элементы массива. Например, x > 15 означает, что в новый массив попадут только те элементы, которые больше 15.

array: Исходный массив, который фильтруется.

SELECT user_id, event_type, arrayFilter(x -> x > 15, groupArray(price)) AS expensive_items
FROM user_events
GROUP BY user_id, event_type;

user_id	event_type	expensive_items
2	purchase	[25.0]
3	click	[35.0]
1	purchase	[20.0]
3	view	[30.0]
1	view	[20.0]
1	click	[]
2	click	[25.0]

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

arraySlice

arraySlice: Эта функция извлекает подмассив из исходного массива, начиная с указанного индекса и с заданной длиной. Она позволяет выбрать только определенные элементы массива, что удобно для анализа первых или последних элементов.

Например, если вам нужно получить только первые несколько элементов массива, эта функция позволяет сделать это.

Допустим, мы хотим получить первые два элемента массива цен для каждого типа события. Мы можем использовать arraySlice для извлечения этих значений:


SELECT user_id, event_type, arraySlice(groupArray(price), 1, 2) AS first_two_prices
FROM user_events
GROUP BY user_id, event_type;

user_id	event_type	first_two_prices
2	purchase	[15.0, 25.0]
3	click	[35.0]
1	purchase	[10.0, 20.0]
3	view	[30.0]
1	view	[20.0]
1	click	[10.0, 15.0]
2	click	[25.0]

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

arrayDistinct

arrayDistinct: Эта функция удаляет дублирующиеся значения из массива, возвращая массив, содержащий только уникальные элементы. Функция arrayDistinct полезна, когда необходимо избавиться от дубликатов в данных. В случаях, когда пользователи могут отправлять одинаковые события несколько раз (например, многократные нажатия на кнопку), arrayDistinct позволяет создать отчет без лишних повторений.

Предположим, мы хотим получить уникальные цены на товары, купленные пользователями. Мы можем использовать arrayDistinct.

SELECT user_id, event_type, arrayDistinct(groupArray(price)) AS unique_prices
FROM user_events
GROUP BY user_id, event_type;

user_id	event_type	unique_prices
2	purchase	[15.0, 25.0]
3	click	[35.0]
1	purchase	[10.0, 20.0]
3	view	[30.0]
1	view	[20.0]
1	click	[10.0, 15.0]
2	click	[25.0]

arrayEnumerate

Добавим данных:

INSERT INTO user_events VALUES
(1, '2024-07-01', 'purchase', 1, 10.0, 1),
(1, '2024-07-02', 'purchase', 2, 15.0, 2),
(1, '2024-07-03', 'purchase', 3, 20.0, 1),
(2, '2024-07-01', 'purchase', 4, 25.0, 1),
(2, '2024-07-02', 'purchase', 5, 30.0, 1),
(2, '2024-07-03', 'purchase', 6, 35.0, 1),
(3, '2024-07-01', 'purchase', 7, 40.0, 1),
(3, '2024-07-02', 'purchase', 8, 45.0, 1),
(3, '2024-07-03', 'purchase', 9, 50.0, 1);

arrayEnumerate: Эта функция добавляет каждому элементу массива его индекс (позицию) и возвращает массив пар (индекс, значение). Это полезно для анализа последовательности элементов или отслеживания их порядка.

Функция arrayEnumerate помогает, когда нужно проанализировать порядок элементов в массиве или создать уникальные идентификаторы для элементов в массиве. Это полезно, например, для отслеживания последовательности покупок или других событий.

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

SELECT
    user_id,
    arrayEnumerate(groupArray((event_date, item_id, price, quantity))) AS purchase_positions,
    groupArray((event_date, item_id, price, quantity)) AS purchases
FROM user_events
WHERE event_type = 'purchase'
GROUP BY user_id;

user_id	purchase_positions	purchases
3	[1,2,3]	['[2024-07-01, 7, 40.0, 1]', '[2024-07-02, 8, 45.0, 1]', '[2024-07-03, 9, 50.0, 1]']
2	[1,2,3,4,5]	['[2024-07-01, 4, 25.0, 1]', '[2024-07-02, 5, 30.0, 1]', '[2024-07-03, 6, 35.0, 1]', '[2024-07-02, 2, 15.0, 2]', '[2024-07-02, 4, 25.0, 1]']
1	[1,2,3,4,5]	['[2024-07-01, 1, 10.0, 1]', '[2024-07-02, 2, 15.0, 2]', '[2024-07-03, 3, 20.0, 1]', '[2024-07-03, 1, 10.0, 2]', '[2024-07-03, 3, 20.0, 1]']

Теперь мы можем использовать полученные данные для анализа покупок:

WITH purchase_data AS (
    SELECT
        user_id,
        arrayEnumerate(groupArray((event_date, item_id, price, quantity))) AS purchase_positions,
        groupArray((event_date, item_id, price, quantity)) AS purchases
    FROM user_events
    WHERE event_type = 'purchase'
    GROUP BY user_id
)
SELECT
position,
count() AS count_above_30
FROM purchase_data
ARRAY JOIN purchase_positions AS position, purchases AS purchase
WHERE purchase.purchases > 30
GROUP BY position
ORDER BY position;

user_id	count_above_30
1	1
2	1
3	1
5	1

Функция arrayEnumerate удобна для задач, связанных с анализом позиций или порядка элементов в массивах, что может быть полезно при работе с временными рядами или последовательностями действий пользователей.

countEqual

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

Предположим, мы хотим узнать, сколько раз каждый пользователь выполнял действие "purchase" в нашем наборе данных:

SELECT
    user_id,
    countEqual(groupArray(event_type), 'purchase') AS purchase_count
FROM user_events
GROUP BY user_id;

user_id	purchase_count
1	2
2	2
3	0

arrayReduce и arrayMap

arrayMap

Функция arrayMap: Позволяет применить заданное выражение или функцию к каждому элементу массива. Это полезно для преобразования данных в массиве по определенному правилу.

Синтаксис: arrayMap(x -> expression, array)

x: Переменная, представляющая текущий элемент массива.

expression: Выражение или функция, применяемая к каждому элементу массива.

array: Исходный массив, элементы которого будут преобразованы.

Предположим, у нас есть массив цен покупок, и мы хотим удвоить цены тех покупок, которые стоят больше 20:

SELECT
    user_id,
    arrayMap(x -> if(x > 20, x * 2, x), purchases) AS conditional_purchases
FROM user_purchases;

В этом примере arrayMap использует условное выражение, чтобы изменить элементы массива в зависимости от их значения.

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

Синтаксис: arrayMap((x, y) -> expression, array1, array2)

x и y: Переменные для текущих элементов массивов.

expression: Выражение, применяемое к элементам массивов.

array1 и array2: Исходные массивы

arrayReduce

Функция arrayReduce: Применяет агрегатную функцию ко всем элементам массива. Это удобно для выполнения агрегирования данных после их предварительной обработки.

Синтаксис: arrayReduce('aggregate_function', array)

aggregate_function: Название агрегатной функции, такой как sum, avg, min, max, uniq, count и другие.

array: Массив, к элементам которого применяется агрегатная функция.

Мы хотим узнать количество уникальных товаров, которые каждый пользователь покупал на сумму более 30. Мы можем выполнить это в несколько этапов:

Создание временной таблицы: Группируем данные по user_id и создаем массив покупок для каждого пользователя.

WITH purchases AS (
    SELECT
        user_id,
        groupArray((item_id, price, quantity)) AS purchases
    FROM user_events
    WHERE event_type = 'purchase'
    GROUP BY user_id
)

Фильтрация и агрегирование: Применяем arrayFilter, arrayMap и arrayReduce для получения количества уникальных товаров с суммой покупки больше 30.

SELECT
    user_id,
    arrayReduce(
        'uniq',
        arrayMap(p -> p.item_id,
            arrayFilter(p -> p.price * p.quantity > 30, purchases)
        )
    ) AS unique_items
FROM purchases

WITH purchases: Создаем временную таблицу purchases, группируя данные по user_id и создавая массив покупок для каждого пользователя.

arrayFilter: Фильтруем покупки, оставляя только те, где price * quantity > 30.

arrayMap: Преобразуем отфильтрованный массив, извлекая item_id каждой покупки.

arrayReduce: Применяем агрегатную функцию uniq, чтобы подсчитать количество уникальных товаров.

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

user_id	unique_items
3	3
2	1
1	0

arrayJoin

Функция arrayJoin в ClickHouse позволяет "размножить" каждую строку таблицы на несколько строк на основе массива. Не следует путать с обычным JOIN, который используется для объединения данных из разных таблиц. Вместо этого, arrayJoin расширяет одну строку в несколько строк, основываясь на содержимом массива. Это особенно полезно, когда данные хранятся в виде массивов, и вам нужно преобразовать их в табличный формат для дальнейшего анализа.

Отличие от обычного JOIN:

arrayJoin: Работает с массивами внутри одной таблицы, преобразуя массив в множество строк. Не объединяет данные из разных таблиц.

Обычный JOIN: Объединяет данные из двух или более таблиц на основе общего ключа или условия.

Синтаксис:

SELECT
    arrayJoin(array)
FROM
    table_name;

Создание диапазона дат

В этом примере мы создадим диапазон всех дат от минимальной до максимальной даты в таблице price_changes. Используем функцию arrayJoin, чтобы преобразовать массив дат в отдельные строки.

WITH
    -- Определяем минимальную и максимальную дату из таблицы price_changes
    bounds AS (
        SELECT min(change_date) AS start_date, max(change_date) AS end_date
        FROM price_changes
    )

    -- Создаем массив всех дат в диапазоне от start_date до end_date
    SELECT arrayJoin(
        arrayMap(
            x -> toDate(x),
            range(toUInt32(bounds.start_date), toUInt32(bounds.end_date) + 1)
        )
    ) AS date
    FROM bounds

WITH bounds: Создаем временную таблицу bounds, где находим минимальную и максимальную дату из таблицы price_changes.

range: Создаем диапазон чисел от минимальной до максимальной даты, преобразованных в числа.

arrayMap: Преобразуем числа в даты с помощью функции toDate.

arrayJoin: Разворачиваем массив дат в отдельные строки.

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

Мы подробно рассмотрели ключевые функции для работы с массивами в ClickHouse, такие как arrayJoin, arrayMap, arrayReduce, и другие. Эти инструменты позволяют вам эффективно управлять и анализировать массивы данных, что может значительно упростить задачи обработки и агрегации информации. Применение этих инструментов в практике может сделать ваши запросы более компактными, а код более читаемым. Надеюсь, этот гид поможет вам в вашей повседневной работе.

Array функции Clickhouse