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

image

На носу зима, приближаются морозы, а это значит, что сегодня мы будем мариновать бананы. Для этого нам понадобятся следующие ингредиенты:
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

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

image

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;

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

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

А пока все, до новых встреч.
Поделиться публикацией

Похожие публикации

Комментарии 14

    +4
    Уже перечисляли как-то все типы на форуме:
    www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=975092&msg=13293372

    Распишу, что сходу вспомню:
    1. всевозможные пивотные джойны
    1.1 join с большой таблицей(можно встретить all_objects/all_source)
    1.2 join с любым генератором(simple pivot, multiset,lateral [до 12c недокументировано])

    2. модель (model)
    2.1 iterate
    2.2 for

    3. group by grouping sets/cube/rollup
    3.1 select 1 from dual group by cube(1,1,1);
    3.2 select 1 from dual group by rollup(1,1,1);
    3.3 select 1 from dual group by grouping sets(1,1,1);

    4. рекурсия
    4.1 connect by
    4.2 recursive subquery factoring (with)

    5. xmltable, примеры ниже на самом деле однотипные, просто их можно варьировать:
    5.1 select * from xmltable('1 to 3' columns n for ordinality);
    5.2 select * from xmltable('1 to xs:integer(.)' passing 10 columns n for ordinality);
    5.3 select * from xmltable('1 to xs:integer(.)' passing 10 columns n int path '.');
    5.4 select * from xmltable('for $N in (1 to 5) for $M in (1 to 3) return $N*$M' passing 10 columns n int path '.');

    6. connect by + (dbms_random.value/connect_by_root) [версионно-зависимое, есть еще варианты с sys_guid и тд вместо dbms_random]

    PS. простой connect by c dbms_random и тому подобными, т.е. 6-й в моем списке и 3-й из поста, я не советую использовать, причины легко гуглятся.
    PPS. еще по теме: blogs.oracle.com/sql/entry/row_generators_part_2
      0
      С 12c помимо lateral, можно еще использовать его ANSI аналог — apply
        0
        Упомянутый мной в модели 2.2:
        with bottle(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 bottle
        model
             partition by (id)
             dimension by(1 n)
             measures(ingredient, measure, quantity)
             rules(
               ingredient[for n from 1 to quantity[1] increment 1] = ingredient[1]
              ,measure   [for n from 1 to quantity[1] increment 1] = measure   [1]
              ,quantity  [for n from 1 to quantity[1] increment 1] = quantity  [1]
             )
        
          0
          C 12c, кстати, добавился 7-й тип: JSON_TABLE
          и в 1.2 забыл добавил pipelined генераторы
            0
            xtender, круто, спасибо, будем углубляться
            0
            Ораклом я в данный момент не занимаюсь, хотел бы показать как это решается в альтернативной базе kdb:
            <code>
            / Делаю табличку. Только первые буквы беру для простоты
            
            q)t:([] i:`b`p`w`s`u; m:`sh`ve`li`lo`lo; q:3 2 3 1 2)
            q)t
            i m  q
            ------
            b sh 3
            p ve 2
            w li 3
            s lo 1
            u lo 2
            
            / решение
            q)ungroup update til each q from t
            i m  q
            ------
            b sh 0
            b sh 1
            b sh 2
            p ve 0
            p ve 1
            w li 0
            w li 1
            w li 2
            s lo 0
            u lo 0
            u lo 1
            </code>
            


            т.е. практически только одна операция: ungroup
              +3
              Не холивара ради, а диалога для приведу решение задачи в PostgreSQL:

              SELECT ingredient, measure, 1 AS quantity FROM (select t.*,generate_series(1,t.quantity,1) from bottle AS t) AS t;
                +1
                Вариант для PostgreSQL без подзапроса:
                select b.ingredient, b.measure, 1 as quantity
                from bottle b
                inner join generate_series(1, b.quantity) g (s)
                  on g.s <= b.quantity
                order by b.id;
                
                Перед generate_series подразумевается lateral, но функция и так может обращаться к полям таблицы, указанной в from ранее.
                Еще в Postgres работает способ 4, если после with добавить recursive.
                  +1
                  Ой, не успел отредактировать. В запросе было много лишнего:
                  select ingredient, measure, 1 as quantity
                  from bottle, generate_series(1, quantity)
                  order by id;
                  
                    0
                    Да, так гораздо аккуратнее. Спасибо за вариант!
                      0
                      Если что, фраза «В запросе было много лишнего» была про мой же первый вариант.
                      Изначально там вообще было generate_series(1, 10000), поэтому условие g.s <= b.quantity было необходимо. Потом вспомнил про lateral и, следовательно, про возможность использовать b.quantity вместо 10000. Заменил, но то, что условие стало ненужным, заметил слишком поздно.

                      Кстати, спасибо Alhymik за интересную задачку. Заставила обратить внимание на несколько возможностей SQL, котрые до этого не использовал, а они, оказывается, очень удобны. В частности LATERAL и «CREATE TABLE table_name AS ...».
                        0
                        LATERAL- это да, сам впервые узнал. Докину в статью пожалуй.
                        «CREATE TABLE table_name AS ...» — тоже вещь. Кроме того, что часто просто удобно, загружает данные в таблицу плотно, без свободных областей в «куче» — используется для direct-path загрузок.
                        Запостил еще пару задачек, возможно также обнаружите для себя что-то новое: голивуд и выборы. В PostgreSQLтоже.
                          0
                          В Голливуд как раз только-только запостил :)
                          Открыл для себя конструкцию ROWS FROM. Спасибо еще раз :)
                          Правда, все равно несколько громоздко получилось.

                          По выборам пока пытаюсь что-нибудь покрасивее соорудить, может уже завтра.
                          И я все-же больше по postgres, а в таких задачках приходится использовать инструменты, специфичные для конкретной СУБД.
                          Хотя, некоторые решения вполне можно портировать туда-обратно.
                  0
                  Проверил на MS SQL варианты, которые в лоб (с минимальными доработками) работают на T-SQL: 2 и 4.

                  С теми же данными 2-й способ в разы лучше (по стоимости в плане выполнения) 4-го — 12% стоимости от всего пакета против 52%.

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

                  Самое читаемое