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

[xtender]

Уже перечисляли как-то все типы на форуме:
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

[xtender]

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

[xtender]

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

[xtender]

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

[Alhymik]

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

[inv2004]

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

[dAllonE]

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

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

[CertainMan]

Вариант для 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.

[CertainMan]

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

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

[dAllonE]

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

[CertainMan]

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

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

[Alhymik]

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

[CertainMan]

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

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

[minamoto]

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

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