В практике обработки внешних данных часто встречается ситуация, когда информация поступает в «неудобном» виде. Представим таблицу, где в одной колонке указана организация, а в другой — список сотрудников одной строкой, где и фамилии, и имена, и сами записи разделены обычным пробелом (например, «Иван Петров Ольга Иванова»). Задача состоит в том, чтобы трансформировать эту строку в классический вид, где каждой паре «Имя Фамилия» соответствует отдельная строка с названием организации.
Сложность заключается в том, что разделитель между именем и фамилией идентичен разделителю между разными людьми. Если мы просто разобьем строку по пробелу, мы получим массив слов, которые нужно правильно сгруппировать по два. Рассмотрим, как решить эту задачу с помощью SQL и инструментов обработки больших данных, таких как PySpark.
Если мы работаем в среде классического SQL (MS SQL, SQLite, PostgreSQL), одним из самых надежных способов является использование рекурсивных общих табличных выражений (CTE). Проанализируем логику этого подхода: нам нужно сначала пронумеровать каждое слово в строке, а затем объединить нечетные слова (имена) с четными (фамилиями).
Рассмотрим пример запроса, который разделяет строку на слова и собирает их обратно в пары:
WITH vt_data as (
SELECT t.f as f, t.str || ' ' as str
FROM (
SELECT 'Фирма 1' as f, 'Иван Петров Ольга Иванова' as str
UNION ALL
SELECT 'Фирма 2', 'Петр Петров'
UNION ALL
SELECT 'Фирма 3', 'Олег Иванов Петр Петров Анна Петрова'
) as t
),
vt as (
-- Базовая часть рекурсии: выделяем первое слово
SELECT
t.f,
substr(t.str, instr(t.str, ' ') + 1) as str,
substr(t.str, 0, instr(t.str, ' ')) as slovo,
1 as npp
FROM vt_data as t
UNION ALL
-- Рекурсивная часть: идем по строке дальше
SELECT
t.f,
substr(t.str, instr(t.str, ' ') + 1),
substr(t.str, 0, instr(t.str, ' ')),
t.npp + 1 as npp
FROM vt as t
WHERE instr(t.str, ' ') <> 0
)
-- Финальная сборка пар Имя + Фамилия
SELECT
t.f as f,
t.slovo || ' ' || LEAD(t.slovo) OVER(PARTITION BY t.f ORDER BY t.npp) as FI
FROM vt as t
WHERE t.npp % 2 <> 0
ORDER BY t.f, t.npp
Разберем по шагам, что происходит в этом запросе:
instr, чтобы последнее слово также было обработано.substr (подстрока) и instr (поиск позиции символа) мы отсекаем по одному слову за итерацию. Каждому слову присваивается порядковый номер npp.t.npp % 2 <> 0 оставляет в итоговой выборке только те строки, которые начинаются с нечетного индекса (начала пары).При работе с миллионами строк в PySpark стандартный рекурсивный SQL может быть неэффективен или не поддерживаться в полном объеме. Выясним, почему функция SPLIT в Spark ведет себя иначе. В Spark SQL функция split() возвращает массив (Array), и чтобы превратить его в набор строк, нужно использовать функцию explode.
Проанализируем ситуацию, когда необходимо сохранить порядок слов. Для этого в PySpark предусмотрена функция posexplode. Она возвращает не только значение элемента, но и его индекс. Рассмотрим пример логики на языке Python/PySpark:
from pyspark.sql import functions as F
# Предположим, у нас есть DataFrame 'df' с колонками 'организация' и 'сотрудники'
# 1. Разделяем строку на массив слов
df_words = df.withColumn("words", F.split(F.col("сотрудники"), " "))
# 2. Разворачиваем массив в строки с сохранением индекса (pos)
df_exploded = df_words.select("организация", F.posexplode("words").alias("pos", "word"))
# 3. Группируем слова по парам.
# Целочисленное деление индекса на 2 даст одинаковый ID для Имени и Фамилии
df_pairs = df_exploded.withColumn("pair_id", F.floor(F.col("pos") / 2))
# 4. Собираем имя и фамилию обратно
result = df_pairs.groupBy("организация", "pair_id").agg(
F.concat_ws(" ", F.collect_list("word")).alias("Сотрудник")
).drop("pair_id")
result.show()
Этот метод значительно быстрее рекурсии при обработке больших данных, так как Spark оптимизирует работу с массивами. Выделим важные функции PySpark, которые мы использовали: split создает массив, posexplode создает строки с индексами, а concat_ws склеивает результат обратно.
Если структура данных строго фиксирована (только Имя и Фамилия, без отчеств), можно применить хитрость с регулярными выражениями перед разделением строки. Мы можем заменить каждый второй пробел на специальный уникальный символ (например, точку с запятой ;), а затем выполнить разделение именно по этому символу.
Рассмотрим, как это может упростить задачу:
split по символу ; и explode, получив готовые пары без сложной нумерации и группировки.Этот метод часто называют «индустриальным», так как он минимизирует количество операций в плане выполнения запроса, что критично для таблиц с миллионами записей.
При использовании любого из вышеперечисленных методов стоит учитывать следующие нюансы:
Подводя итог, мы проанализировали несколько способов трансформации данных: от классической SQL-рекурсии до современных методов обработки массивов в PySpark. Выбор метода зависит от вашего технологического стека и объема обрабатываемой информации.