Alhymik Oct 20 2015 at 15:16

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

3 min

42K

Oracle*Programming*SQL*

From sandbox

+15

Comments 14

xtender Oct 20 2015 at 16:18

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

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

xtender Oct 20 2015 at 16:51

Упомянутый мной в модели 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 Oct 20 2015 at 16:53

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

Alhymik Oct 20 2015 at 21:06

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

inv2004 Oct 21 2015 at 13:01

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

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


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

CertainMan Oct 24 2015 at 04:32

Вариант для 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 Oct 24 2015 at 04:39

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

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

dAllonE Oct 24 2015 at 19:47

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

CertainMan Oct 24 2015 at 20:12

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

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

Alhymik Oct 25 2015 at 00:58

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

CertainMan Oct 25 2015 at 01:29

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

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

minamoto Oct 22 2015 at 13:07

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

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

Show the best of all time