Добро пожаловать в четвертую статью нашего курса по ClickHouse! В прошлый раз мы научились основам: вставлять, выбирать и агрегировать данные. Теперь, когда вы можете получать базовую статистику, пришло время углубить свои навыки и научиться “разговаривать” с данными на более сложном языке.
Сегодня мы изучим мощные инструменты, которые позволят вам преобразовывать, форматировать и анализировать данные на совершенно новом уровне. Мы разберем функции для работы со строками, датами и временем, а также освоим условную логику, которая сделает ваши запросы по-настоящему гибкими.
Манипуляции со строками: извлекаем смысл из текста
Текстовые данные, такие как URL, имена, описания или логи, часто требуют предварительной обработки. ClickHouse предлагает богатый набор функций для этого.
- Конкатенация:
concat(s1, s2, ...)— объединяет несколько строк в одну. - Длина строки:
length(s)— возвращает количество символов в строке. - Изменение регистра:
lower(s),upper(s)— приводят строку к нижнему или верхнему регистру. - Извлечение подстроки:
substring(s, offset, length)— вырезает часть строки. - Поиск и замена:
position(haystack, needle)(находит позицию подстроки),replace(haystack, needle, replacement)(заменяет подстроку). - Поиск по шаблону: LIKE (простой поиск по шаблону с
%и_), multiSearchAny(haystack, [needle1, needle2, …]) (эффективный поиск любой из нескольких подстрок).
Практический пример: Давайте из нашей таблицы access_logs извлечем доменное имя из поля ip_address (для примера представим, что это домен) и создадим читаемое описание события.
SELECT
user_id,
url,
concat('Event type "', event_type, '" on page ', url) AS event_description,
substring(ip_address, 1, position(ip_address, '.') - 1) AS first_ip_segment
FROM
my_first_db.access_logs
WHERE
ip_address = '192.168.1.1'
LIMIT 5;
Работа с датой и временем: точность — ключ к аналитике
Аналитика временных рядов — одна из главных задач ClickHouse. Поэтому функции для работы с датами и временем здесь особенно мощные и оптимизированные.
- Текущее время:
now()— возвращает текущее время сервера. - Форматирование:
formatDateTime(datetime, format)— преобразует дату в строку заданного формата (например,%Y-%m-%d %H:%M:%S). - Извлечение компонентов:
toYear(date),toMonth(date),toDayOfWeek(date),toHour(datetime). - Округление:
toStartOfDay(datetime),toStartOfWeek(datetime),toStartOfMonth(datetime)— очень полезные функции для группировки по дням, неделям или месяцам. - Арифметика:
date_add(unit, value, date),date_diff(unit, start_date, end_date)— позволяют прибавлять или вычитать временные интервалы.
Практический пример: Посчитаем количество событий по дням недели.
SELECT
toDayOfWeek(timestamp) AS day_of_week,
count() AS event_count
FROM
my_first_db.access_logs
GROUP BY
day_of_week
ORDER BY
day_of_week;
Условная логика: CASE WHEN
Конструкция CASE ... WHEN ... THEN ... ELSE ... END — это один из самых полезных инструментов в SQL. Она позволяет применять логику “если-то-иначе” прямо внутри запроса, создавая новые, вычисляемые столбцы на лету. Это идеально подходит для сегментации данных.
Практический пример: Разделим все события в нашей таблице access_logs на категории в зависимости от их продолжительности.
SELECT
user_id,
duration_ms,
CASE
WHEN duration_ms > 400 THEN 'Very Long'
WHEN duration_ms > 150 AND duration_ms <= 400 THEN 'Normal'
ELSE 'Short'
END AS duration_category
FROM
my_first_db.access_logs
LIMIT 10;
Краткий взгляд на подзапросы
Подзапрос — это SELECT запрос, вложенный в другой запрос. Они позволяют создавать более сложные выборки. Например, можно выбрать всех пользователей, которые совершили определенное событие.
Практический пример: Найдем все события пользователей, которые хотя бы раз заходили на страницу /contact.
SELECT
timestamp,
user_id,
event_type,
url
FROM
my_first_db.access_logs
WHERE
user_id IN (
SELECT DISTINCT user_id
FROM my_first_db.access_logs
WHERE url = '/contact'
);
Внутренний запрос сначала находит всех уникальных пользователей (DISTINCT user_id), посетивших страницу /contact, а внешний запрос затем выбирает все события для этих пользователей.
Заключение
Вы освоили еще один мощный пласт инструментов в ClickHouse. Функции для работы со строками, датами и условная логика превращают простые запросы в гибкий инструмент для глубокого анализа данных. Теперь вы можете не просто агрегировать данные, но и очищать их, обогащать и сегментировать прямо на лету.
В следующей статье мы вернемся к архитектуре и сделаем глубокое погружение в семейство движков MergeTree.
Использованные референсы и материалы
- Официальная документация по функциям ClickHouse SQL: https://clickhouse.com/docs/en/sql-reference/functions
- Статья о CASE в SQL https://www.geeksforgeeks.org/sql/sql-case-statement/
- Под капотом PREWHERE в ClickHouse: сравниваем планы запросов
