Добро пожаловать в Голливуд. Представляю вам сегодняшних героинь
Сперва мы узнаем, кто был в мужьях у этих красоток. А потом с помощью незамысловатых спецэффектов я вам покажу, в каком порядке они друг с другом бракосочетались. Так что юным девам эта статья будет особенно интересна.
Создадим и заполним базовую таблицу
Из таблицы видно, что Анжелика была замужем трижды. Ее мужья перечислены в колонке через разделитель в порядке очередности их бракосочетания с актрисой. Условимся, что разделитель — это запятая, а пробел после нее — просто мусор.
Лиза Терон вообще ни разу не была замужем (гражданские браки не в счет), и она, по всей видимости, до сих пор ждет своего айтишника. Так что следует взять это на заметку и как следует поторопиться — даме уже, без малого, 40.
Ну и Пенелопа Крус — замужем всего один раз. Какая скука.
Но это все прелюдия, а на деле нужно получить следующий результат
По сути надо выполнить операцию, обратную группировке и агрегации функцией listagg.
Будем двигаться от простого к сложному. Для начала предлагаю рассмотреть похожую задачу — извлечение чисел из одиночной строки с разделителем в табличный набор.
Получаем:
Для понимания запроса нужно знать, как работают функции регулярных выражений (справка). Сама же идея запроса заключается в следующем:
Существуют и другие способы решения задачи, например, такой:
В таблице HOLLYWOOD мы имеем дело не с последовательностью чисел, а с именами знатных мужей. Но их можно также подсчитать с помощью функции regexp_count и извлекать, используя функцию regexp_substr, согласно вышеописанной методе. Теперь нужно вспомнить рецепты маринования бананов и выбрать один из способов генерации строк, когда известно их будущее количество. Для примеров я воспользуюсь 3-м и 5-м способом. Но при выборе наиболее оптимального метода генерации нужно обратить внимание на коммент пользователя xtender.
Объединив подходы, получаем следующее.
Спецэффект № 1.
Всё ОК – девушки счастливы в браках. Все, кроме Лизы Терон. Для таких, как Лиза, в запросе я использовал nvl2.
Спецэффект № 2.
UPD: Нарисовался еще один замечательный Спецэффект № 3 для Oracle 12c
Очевидно, что вариантов решения задачи существует немало. Выбор того или иного варианта – дело вкуса и вопрос производительности. Если бы у меня был Оскар, я бы его не задумываясь вручил тому, кто предложит наиболее лаконичный с точки зрения кода и наиболее оптимальный с точки зрения производительности способ. Да, Лео?
А пока все. До новых встреч.
Сперва мы узнаем, кто был в мужьях у этих красоток. А потом с помощью незамысловатых спецэффектов я вам покажу, в каком порядке они друг с другом бракосочетались. Так что юным девам эта статья будет особенно интересна.
Создадим и заполним базовую таблицу
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
Очевидно, что вариантов решения задачи существует немало. Выбор того или иного варианта – дело вкуса и вопрос производительности. Если бы у меня был Оскар, я бы его не задумываясь вручил тому, кто предложит наиболее лаконичный с точки зрения кода и наиболее оптимальный с точки зрения производительности способ. Да, Лео?
А пока все. До новых встреч.
Комментарии (7)
CertainMan
25.10.2015 01:02Oracle в наличии нет, но есть желание решить задачку и есть PostgreSQL:
select hw.actress, h.h as husband, case when h.h is not null then h.n end as husb_no from hollywood hw, rows from ( regexp_split_to_table(husbands, E'\\s*,\\s*'), nullif(id, null) ) with ordinality h (h, x, n) order by hw.id, h.n;
Оно же с комментариями:
select hw.actress, h.h as husband, -- Выводятся номера только для тех строк, которые сформированы с помощью regexp_split_to_table case when h.h is not null then h.n end as husb_no from hollywood hw, -- ROWS FROM позволяет комбинировать результаты работы нескольких функций rows from ( -- Функция разбивает столбец husbands по запятой (попутно удаляя все пробелы по обе стороны запятой), -- выводя каждый фрагмент отдельной строкой. При этом для строк с husbands is null ничего не выводится. -- E'...' - строка, "заэскейпленная" в стиле C regexp_split_to_table(husbands, E'\\s*,\\s*'), -- Функция используется, чтобы сохранить в выводе строки с husbands is null (ликвидирует дискриминацию Шарлиз) -- Просто возвращает id, если он не null, что гарантирует наличие в выводе всех строк исходной таблицы nullif(id, null) -- Нумерует строки, получаемые из ROWS FROM -- Нумерация начинается с начала (с 1) для каждой строки исходной таблицы ) with ordinality -- Задается алиас для результатов конструкции ROWS FROM h (h, x, n) order by hw.id, h.n;
alexeyknyshev
Это всё конечно интересно. Но такие задачи возникают при наличии ненормализованных отношений в таблицах, а именно условие 1NF в данном случае нарушено. Фактически, то, что вы проделали: нормализовали данные для отношений <Актриса, Муж>. А следовательно главная проблема тут кроется именно не в том, как это сделать средставми SQL, а в том, что изначально архитектура отношений построена неверно (и решать надо именно эту проблему в первую очередь).
Alhymik
Тут вообще проблемы нет. Я показал, как решать типичную задачу SQL, которая встречается сплошь и рядом, безотносительно к архитектуре отношений. На практике не у всех, кто выбирает данные, есть доступ к базе на запись, а уж тем более изменение структуры. Работаем, как правило, с тем, что есть. А последний раз на практике я с подобным сталкивался, работая с настроечной таблицей отчетов, где кроме уникального ID отчета, были поля с настройками, клобы со скриптами, и одно из полей — это список ролей пользователей через запятую, для которых формирование отчета будет разрешено. Табличка была небольшая, «разматывание» ролей, чтоб сопоставить их с системными, в производительности ничего не отнимало, что-то нормализовывать тупо не было смысла. Т. е. стереотип о том, что ненормализовано, значит неправильно, на практике работает не всегда.
xtender
Не совсем верно, например, такие задачи часто возникают, когда хотят получить отчет в разрезах по указанным периодам, и для этого часто нужен генератор дат.
vbif
Если всё подряд нормализовывать, то любой самый безобидный запрос будет превращаться в стопку джоинов.