Строковые функции SQL: TRIM, LOWER, SPLIT_PART и нормализация текста

Сначала определим задачу

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

UTM source может содержать параметры и хвосты, а email — пробелы после копирования. Приведи source к lower(trim(source)), но не удаляй всё после символа без проверки: в URL это может быть значимая часть. Для домена отдели canonicalization от простого форматирования.

Как работает конструкция

TRIM убирает пробелы по краям, LOWER и UPPER приводят регистр, REPLACE заменяет фрагменты, а SPLIT_PART выделяет часть строки по разделителю. В PostgreSQL regexp_replace решает более сложные шаблоны. Нормализацию лучше делать до GROUP BY и сохранять исходное значение для аудита.

Собери пары raw_value → normalized_value и посмотри самые частые преобразования. Добавь quality_flag для пустых и подозрительных строк. Группируй по нормализованному полю, а в отчёте показывай охват и количество исправленных значений.

• Зафиксируй одну строку результата.
• Отдели условия по исходным строкам от условий по рассчитанным значениям.
• Назови период, ключи связи и правило для повторов.

Запрос по шагам

Названия таблиц здесь условные. При переносе сохрани зерно и бизнес-условия, а не буквальный синтаксис примера.

SELECT raw_channel, LOWER(TRIM(raw_channel)) AS channel,
       SPLIT_PART(LOWER(TRIM(landing_url)), '?', 1) AS landing_path,
       COUNT(*) AS sessions
FROM sessions GROUP BY 1, 2, 3 ORDER BY sessions DESC;

Проверка результата

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

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

Границы и альтернативы

LOWER не решает транслитерацию и не делает разные сущности одинаковыми. TRIM не убирает невидимые символы во всех случаях. SPLIT_PART с отсутствующим разделителем возвращает исходную строку. Не перезаписывай raw-значение.

Используй простые функции для повторяемых правил и документируй преобразование. Если нормализация влияет на идентичность, согласуй её с владельцем данных. Сохраняй raw и normalized рядом — это небольшая цена за воспроизводимый аудит.

Задание для самостоятельной проверки

Сделай витрину каналов из raw_channel, normalized_channel и normalization_rule. Найди долю строк, где значение изменилось. Для email проверь дубли до и после lower/trim и отдельно обсуди, можно ли считать совпавшие адреса одним пользователем.

• Сначала запиши ожидаемый результат словами.
• Проверь запрос на маленькой контрольной выборке.
• Объясни, что запрос считает и чего не доказывает.

Решение для команды

Используй простые функции для повторяемых правил и документируй преобразование. Если нормализация влияет на идентичность, согласуй её с владельцем данных. Сохраняй raw и normalized рядом — это небольшая цена за воспроизводимый аудит.