Продвинутые функции SQL в ClickHouse: обработка строк, дат и условная логика. Урок 4

Добро пожаловать в четвертую статью нашего курса по 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.

Использованные референсы и материалы

1. Официальная документация по функциям ClickHouse SQL: https://clickhouse.com/docs/en/sql-reference/functions
2. Статья о CASE в SQL https://www.geeksforgeeks.org/sql/sql-case-statement/
3. Под капотом PREWHERE в ClickHouse: сравниваем планы запросов 

SQL-блокнот к Уроку 4 бесплатного курса доступен в нашем репозитории на GitHub