Oracle, типичные задачи SQL. Размножение строк таблицы в зависимости от значения числа в колонке / Хабр

На носу зима, приближаются морозы, а это значит, что сегодня мы будем мариновать бананы. Для этого нам понадобятся следующие ингредиенты:

ID	INGREDIENT	MEASURE	QUANTITY
1	Банан	Штука	3
2	Петрушка	Ветка	2
3	Вода	Литр	3
4	Соль	Ложка	1
5	Уксус	Ложка	2

Необходимо получить набор данных, показывающий ингредиенты в банке по единично с учетом их количества:

INGREDIENT	MEASURE	QUANTITY
Банан	Штука	1
Банан	Штука	1
Банан	Штука	1
Петрушка	Ветка	1
Петрушка	Ветка	1
Вода	Литр	1
Вода	Литр	1
Вода	Литр	1
Соль	Ложка	1
Уксус	Ложка	1
Уксус	Ложка	1

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

Для начала как следует прокипятим банку:

create table bottle 
as 
with t (id, ingredient, measure, quantity) as (
  select 1, 'Банан', 'Штука', 3 from dual union all
  select 2, 'Петрушка', 'Ветка', 2 from dual union all
  select 3, 'Вода', 'Литр', 3 from dual union all
  select 4, 'Соль', 'Ложка', 1 from dual union all
  select 5, 'Уксус', 'Ложка', 2 from dual 
)
select * from t;

alter table bottle add primary key (id);

А теперь непосредственно рецепты приготовления.

Способ 1

Надо полагать, не самый скорый, особенно при очень большом количестве строк:

select b.ingredient, b.measure, 1 quantity
from bottle b, (
     select level lvl
     from dual 
     connect by level <= (select max(quantity) from bottle)) x
where b.quantity >= x.lvl
order by b.id

Способ 2

Среди домохозяек бытует мнение, что можно создать стационарную таблицу с большим количество строк и уникальным ключом:

create table multiplier_rows as
select rownum as row_num
from dual 
connect by level <= 10000;

alter table multiplier_rows add primary key (row_num);

И использовать ее коллективно так же, как подзапрос X из первого способа:

select b.ingredient, b.measure, 1 quantity
from bottle b, multiplier_rows x
where b.quantity >= x.row_num
order by b.id

Является ли данный способ более эффективным — вопрос спорный.

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

Способ 3

Через рекурсивный запрос. Рекомендуют ведущие банановеды:

select ingredient, measure, 1 quantity
from bottle
connect by prior id = id 
           and prior dbms_random.value is not null
           and level <= quantity
order by id

Способ 4

В продолжение темы — сделал рекурсию, но уже через WITH + UNION ALL, вот что получилось:

with boo (id, i, m, q) as (
  select id, ingredient, measure, quantity
  from bottle 
  union all
  select id, i, m, q-1
  from boo
  where q > 1
)
select i ingredient, m measure, 1 quantity  
from boo
order by id

Способ 5

Через коллекции. Для тех, конечно, кто умеет их готовить:

select b.ingredient, b.measure, 1 quantity
from bottle b, 
     table(cast(multiset(select null 
                         from dual 
                         connect by level <= b.quantity)
           as sys.odcinumberlist)) x
order by b.id

Способ 6

select b.ingredient, b.measure, 1 quantity
from bottle b, 
     table (select cast(collect(1) as sys.odcinumberlist)
            from dual 
            connect by level <= b.quantity) x
order by b.id

Способ 7

И, наконец, комплимент от шеф-повара:

select ingredient, measure, 1 quantity
from bottle
model 
  partition by (id, ingredient, measure, quantity)  
  dimension by (0 d)
  measures(0 m) 
  rules iterate (10000) until m[iteration_number] = iteration_number (
    m[iteration_number] = cv(quantity) - 1
  )
order by id

Моделька с бананами — это я уже чисто ради эстетики.

UPD: Способ 8

Для Oracle 12c, по комментам xtender -а — подарок от заведения.

select b.ingredient, b.measure, 1 quantity
from bottle b, 
     lateral(select null 
             from dual 
             connect by level <= b.quantity) x
order by b.id

Вот и все, осталось только покрепче закрутить банку и вместе со всем содержимым выкинуть ее в мусорное ведро:

drop table bottle;
drop table multiplier_rows;

Отлично! Теперь маринованные бананы полностью готовы.

Буду признателен за:

новые рецепты
улучшение имеющихся
выбор наилучшего рецепта с точки зрения производительности в первую очередь

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