На носу зима, приближаются морозы, а это значит, что сегодня мы будем мариновать бананы. Для этого нам понадобятся следующие ингредиенты:
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;
Отлично! Теперь маринованные бананы полностью готовы.
Буду признателен за:
- новые рецепты
- улучшение имеющихся
- выбор наилучшего рецепта с точки зрения производительности в первую очередь
А пока все, до новых встреч.