Oracle, типичные задачи SQL. Трансформация перечисленных в колонке значений в строки таблицы

Добро пожаловать в Голливуд. Представляю вам сегодняшних героинь



Сперва мы узнаем, кто был в мужьях у этих красоток. А потом с помощью незамысловатых спецэффектов я вам покажу, в каком порядке они друг с другом бракосочетались. Так что юным девам эта статья будет особенно интересна.

Создадим и заполним базовую таблицу

create table hollywood 
as 
with t (id, actress, husbands) as (
  select 1, 'Анджелина Джоли', 'Джонни Ли Миллер, Билли Боб Торнтон, Брэд Питт' from dual union all
  select 2, 'Шарлиз Терон', null from dual union all
  select 3, 'Пенелопа Крус', 'Хавьер Бардем' from dual
)  
select * from t;

alter table hollywood add primary key (id);

ID	Актриса	Мужья
1	Анджелина Джоли	Джонни Ли Миллер, Билли Боб Торнтон, Брэд Питт
2	Шарлиз Терон
3	Пенелопа Крус	Хавьер Бардем

Из таблицы видно, что Анжелика была замужем трижды. Ее мужья перечислены в колонке через разделитель в порядке очередности их бракосочетания с актрисой. Условимся, что разделитель — это запятая, а пробел после нее — просто мусор.

Лиза Терон вообще ни разу не была замужем (гражданские браки не в счет), и она, по всей видимости, до сих пор ждет своего айтишника. Так что следует взять это на заметку и как следует поторопиться — даме уже, без малого, 40.

Ну и Пенелопа Крус — замужем всего один раз. Какая скука.

Но это все прелюдия, а на деле нужно получить следующий результат

Актриса	Муж	Номер мужа п/п
Анджелина Джоли	Джонни Ли Миллер	1
Анджелина Джоли	Билли Боб Торнтон	2
Анджелина Джоли	Брэд Питт	3
Шарлиз Терон
Пенелопа Крус	Хавьер Бардем	1

По сути надо выполнить операцию, обратную группировке и агрегации функцией listagg.

Будем двигаться от простого к сложному. Для начала предлагаю рассмотреть похожую задачу — извлечение чисел из одиночной строки с разделителем в табличный набор.

with digit_str as (
  select '10,20,30,40,50,100' as str
  from  dual
)
select regexp_substr(str, '(\d+)(,|$)', 1, rownum, 'c', 1) ok
from digit_str
connect by level <= regexp_count(str, '\d+(,|$)')

Получаем:

OK
10
20
30
40
50
100

Для понимания запроса нужно знать, как работают функции регулярных выражений (справка). Сама же идея запроса заключается в следующем:

• Генерируются новые строки с помощью connect by level.
• Через regexp_count вычисляется количество чисел в строке между разделителями – это количество определяет верхнюю границу для генератора строк.
• С помощью regexp_substr извлекаются числа из строки. Номер вхождения шаблона в строку (4-й аргумент регулярки) соответствует значению псевдостолбца rownum — номер п/п сгенерированной строки. Вместо rownum можно было использовать и level, результат был бы аналогичным.

Существуют и другие способы решения задачи, например, такой:

select to_number(column_value) from xmltable('10,20,30,40,50,100')

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

В таблице HOLLYWOOD мы имеем дело не с последовательностью чисел, а с именами знатных мужей. Но их можно также подсчитать с помощью функции regexp_count и извлекать, используя функцию regexp_substr, согласно вышеописанной методе. Теперь нужно вспомнить рецепты маринования бананов и выбрать один из способов генерации строк, когда известно их будущее количество. Для примеров я воспользуюсь 3-м и 5-м способом. Но при выборе наиболее оптимального метода генерации нужно обратить внимание на коммент пользователя xtender.

Объединив подходы, получаем следующее.

Спецэффект № 1.

select actress, 
       trim(regexp_substr(husbands, '(.+?)(,|$)', 1, level, 'c', 1)) as husband, 
       nvl2(husbands, level, null) as husb_no
from hollywood
connect by prior id = id 
           and prior dbms_random.value is not null
           and level <= regexp_count(husbands, '.+?(,|$)')
order by id, 3

Всё ОК – девушки счастливы в браках. Все, кроме Лизы Терон. Для таких, как Лиза, в запросе я использовал nvl2.

Спецэффект № 2.

select h.actress, 
       trim(regexp_substr(h.husbands, '(.+?)(,|$)', 1, x.column_value, 'c', 1)) as husband, 
       nvl2(h.husbands, x.column_value, null) as husb_no
from hollywood h,
     table(cast(multiset(select level 
                         from dual
                         connect by level <= regexp_count(h.husbands, '.+?(,|$)'))
           as sys.odcinumberlist)) x

Это было решение через коллекции.

UPD: Нарисовался еще один замечательный Спецэффект № 3 для Oracle 12c

select h.actress, 
       trim(regexp_substr(h.husbands, '(.+?)(,|$)', 1, x.n, 'c', 1)) as husband, 
       nvl2(h.husbands, x.n, null) as husb_no
from hollywood h,
     lateral(select level n
             from dual
             connect by level <= regexp_count(h.husbands, '.+?(,|$)')) x

Очевидно, что вариантов решения задачи существует немало. Выбор того или иного варианта – дело вкуса и вопрос производительности. Если бы у меня был Оскар, я бы его не задумываясь вручил тому, кто предложит наиболее лаконичный с точки зрения кода и наиболее оптимальный с точки зрения производительности способ.

А пока все. До новых встреч.