20.10.2015 12:16
Alhymik 14 11796 Источник
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)


  1. xtender
    20.10.2015 16:18
    #8620863
    +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


    1. xtender
      20.10.2015 16:19
      #8620865

      С 12c помимо lateral, можно еще использовать его ANSI аналог — apply


    1. xtender
      20.10.2015 16:51
      #8620919

      Упомянутый мной в модели 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]
     )


    1. xtender
      20.10.2015 16:53
      #8620923

      C 12c, кстати, добавился 7-й тип: JSON_TABLE
      и в 1.2 забыл добавил pipelined генераторы


    1. Alhymik
      20.10.2015 21:06
      #8621367

      xtender, круто, спасибо, будем углубляться


  1. inv2004
    21.10.2015 13:01
    #8622029

    Ораклом я в данный момент не занимаюсь, хотел бы показать как это решается в альтернативной базе 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


  1. dAllonE
    21.10.2015 14:02
    #8622139
    +3

    Не холивара ради, а диалога для приведу решение задачи в PostgreSQL:

    SELECT ingredient, measure, 1 AS quantity FROM (select t.*,generate_series(1,t.quantity,1) from bottle AS t) AS t;


    1. CertainMan
      24.10.2015 04:32
      #8625657
      +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. CertainMan
      24.10.2015 04:39
      #8625661
      +1

      Ой, не успел отредактировать. В запросе было много лишнего:

      select ingredient, measure, 1 as quantity
from bottle, generate_series(1, quantity)
order by id;


      1. dAllonE
        24.10.2015 19:47
        #8626021

        Да, так гораздо аккуратнее. Спасибо за вариант!


        1. CertainMan
          24.10.2015 20:12
          #8626031

          Если что, фраза «В запросе было много лишнего» была про мой же первый вариант.
          Изначально там вообще было generate_series(1, 10000), поэтому условие g.s <= b.quantity было необходимо. Потом вспомнил про lateral и, следовательно, про возможность использовать b.quantity вместо 10000. Заменил, но то, что условие стало ненужным, заметил слишком поздно.

          Кстати, спасибо Alhymik за интересную задачку. Заставила обратить внимание на несколько возможностей SQL, котрые до этого не использовал, а они, оказывается, очень удобны. В частности LATERAL и «CREATE TABLE table_name AS ...».


          1. Alhymik
            25.10.2015 00:58
            #8626167

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


            1. CertainMan
              25.10.2015 01:29
              #8626177

              В Голливуд как раз только-только запостил :)
              Открыл для себя конструкцию ROWS FROM. Спасибо еще раз :)
              Правда, все равно несколько громоздко получилось.

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


  1. minamoto
    22.10.2015 13:07
    #8623493

    Проверил на MS SQL варианты, которые в лоб (с минимальными доработками) работают на T-SQL: 2 и 4.

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