Разбор задачек с собеседований vol. 9 🍏

Сегодня отдельный душный класс SQL задач - это задачи на работу с текстом.

Задачка у нас с собеса аж в 🔍! Работать будем с таблицей google_file_store(filename, contents) + Postgres. Но я в качестве бонуса расскажу еще как это решать в клике.

Условие:

Найти, сколько раз точные слова bull и bear встречаются в колонке contents. Считать все вхождения, даже если они в одной строке несколько раз. Регистр не важен. Подстроки вроде bullish или bearing не считаем. Вывести слово и число вхождений.

🚨 Как и всегда, первым делом - разбираем условие.


➡️ Ловушка 1: считаем вхождения, а не строки

Первый рефлекс - написать WHERE contents LIKE '%bull%' и посчитать строки. Но в одной ячейке bull может встретиться три раза, а строка при этом одна.


➡️ Ловушка 2: точное слово, а не подстрока

В условии четко сказано, что bullish, bullard, abull и другие покемоны нам не подходят. Что еще раз подтверждает, что LIKE '%bull%' - мысль не туда 🎩

Нам нужно РОВНО слово. Это ключевой момент, и именно на нем многие валятся.


➡️ Ловушка 3: регистр

BULL, Bull, bull - это одно и то же слово. Значит, где-то по пути все приводим к нижнему регистру (LOWER) или используем регистронезависимый флаг регулярки. Забыл - недосчитал 😑


➡️ Ловушка 4: пунктуация

В реальном тексте слова идут с пунктуацией: bull., bull,, (bull), bear!. Если резать только по пробелу, то bull. останется как bull. и не совпадет с bull - и вы недосчитаете 🌧

Поэтому резать надо не по пробелам, а по всем "не-буквам" сразу.


😎 А теперь кодим!

Самый прозрачный подход:

• режем текст на отдельные слова по любым не-буквам,
• приводим к нижнему регистру,
• оставляем только точные bull/bear


SELECT
word,
COUNT(*) AS nentry
FROM (
SELECT unnest(
regexp_split_to_array(lower(contents), '[^a-z]+')
) AS word
FROM google_file_store
) AS words
WHERE word IN ('bull', 'bear')
GROUP BY word;


1️⃣ lower(contents) - гасим регистр (ловушка 3)

2️⃣ regexp_split_to_array(..., '[^a-z]+') - режем по всему, что НЕ буква: пробелы, точки, скобки, дефисы. Так пунктуация не прилипает (ловушка 4)

3️⃣ unnest(...) - разворачиваем массив слов в отдельные строки, теперь одна строка = одно слово (ловушка 1)

4️⃣ WHERE word IN ('bull', 'bear') - оставляем только нужные слова (ловушка 2)

Все четыре ловушки закрыты одним запросом 💪


🎁 БОНУС 1: Мини-фишка Postgres

Postgres умеет искать все совпадения по regex и выдавать их построчно. Граница слова в Postgres - это \y, флаг g - искать все вхождения, i - без учета регистра:


SELECT
'bull' AS word,
COUNT(*) AS nentry
FROM google_file_store,
regexp_matches(contents, '\ybull\y', 'gi')
UNION ALL
SELECT
'bear' AS word,
COUNT(*) AS nentry
FROM google_file_store,
regexp_matches(contents, '\ybear\y', 'gi');


regexp_matches с флагом g возвращает по одной строке на каждое совпадение - поэтому COUNT(*) сразу даёт число вхождений, даже если их несколько в одной ячейке. А \y...\y гарантирует, что мы ловим слово целиком, а не подстроку.


🎁 БОНУС 2: Решение для Clickhouse

Тут есть шикарная функция countMatches - она сразу построчно считает число совпадений regex:


SELECT 'bull' AS word,
sum(countMatches(lower(contents), '\\bbull\\b')) AS nentry
FROM google_file_store
UNION ALL
SELECT 'bear' AS word,
sum(countMatches(lower(contents), '\\bbear\\b')) AS nentry
FROM google_file_store


Обратите внимание: мы тут используем \b, это обозначает границу слова. А чтобы бэкслэш считался корректно, используем именно \\b.

Ну или если хочется в лоб перенести Решение 1, то в ClickHouse это splitByRegexp + ARRAY JOIN:


SELECT word, count() AS nentry
FROM google_file_store
ARRAY JOIN splitByRegexp('[^a-z]+', lower(contents)) AS word
WHERE word IN ('bull', 'bear')
GROUP BY word


ARRAY JOIN - это ровно тот же unnest из постгреса, просто по-кликхаусному)

———
Признавайтесь, кто бы написал LIKE ‘%bull%’ ?? Жду ваших 🔥

Предыдущие разборы:
• Разбор задачек с собеседований vol. 6
• Разбор задачек с собеседований vol. 7
• Разбор задачек с собеседований vol. 8