Строки и чистка: TRIM, LOWER, SPLIT_PART и регулярные выражения

Бран · продюсер «Хроник Пограничья»

Хотим ввести правило: имя персонажа уникально, и «WOLF63» с «wolf63» считаются одним именем. Сколько персонажей придётся переименовать? И ещё: маркетинг спрашивает, с каких почт к нам приходят игроки. Только чтобы наши тестировщики в цифры не попали.

В прошлом уроке ты считал несколько метрик за один проход. Все они опирались на точные значения: status = 'failed', platform = 'ios'. Со статусами повезло, их пишет код игры. А имена и почты вводят люди, и в них живёт всё что угодно: лишние пробелы, Caps Lock, домены в разном регистре. Сегодня учимся приводить строки к одному виду, прежде чем их сравнивать и группировать.

Сортировка писем на почте

Почтальон сортирует письма по адресам. «ул. Ленина, 5», «Ленина ул 5», «УЛ. ЛЕНИНА 5 » для него один дом. Он не сравнивает надписи буква в букву, а сначала мысленно приводит их к одному виду и только потом раскладывает по ячейкам.

База так не умеет. Для неё 'WOLF63' и ' wolf63' две разные строки: отличаются и регистр, и два пробела в начале. Поэтому GROUP BY name разложит их в разные стопки, а count(DISTINCT name) посчитает дважды. Приводить к одному виду придётся тебе. Этот шаг называют нормализацией.

TRIM и LOWER

Две функции закрывают большую часть бед:

• trim(s) срезает пробелы в начале и в конце строки. Пробелы внутри не трогает.
• lower(s) переводит все буквы в нижний регистр. Есть и upper(s), если нужны большие.

Посмотри на три имени, которые людям кажутся одинаковыми. Квадратные скобки приклеены через ||, чтобы пробелы было видно:

SELECT '[' || name || ']'       AS raw,
       length(name)             AS len,
       '[' || trim(name) || ']' AS trimmed,
       lower(trim(name))        AS clean
FROM characters
WHERE lower(trim(name)) IN ('wolf63', 'ghostraven776', 'bearstone')
ORDER BY clean, name;

        raw        | len |     trimmed     |     clean
-------------------+-----+-----------------+---------------
 [BEARSTONE]       |   9 | [BEARSTONE]     | bearstone
 [BearStone]       |   9 | [BearStone]     | bearstone
 [  ghostraven776] |  15 | [ghostraven776] | ghostraven776
 [GhostRaven776]   |  13 | [GhostRaven776] | ghostraven776
 [  wolf63]        |   8 | [wolf63]        | wolf63
 [WOLF63]          |   6 | [WOLF63]        | wolf63

length выдаёт пробелы с головой: у ' wolf63' восемь символов, хотя видно шесть. Функции вкладываются друг в друга: lower(trim(name)) сначала срезает пробелы, потом опускает регистр.

Сколько имён схлопнется, если считать их без пробелов и регистра:

SELECT count(DISTINCT name)              AS names,
       count(DISTINCT lower(trim(name))) AS clean_names
FROM characters;

 names | clean_names
-------+-------------
  5602 |        5555

Разница 47: столько имён совпадают с чьим-то другим после нормализации. А чтобы увидеть сами пары, группируй по нормализованному имени и оставляй группы, где строк больше одной. HAVING ты знаешь из урока 17, это и будет третий квест.