Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Написать SQL  [new]
V78
Member

Откуда:
Сообщений: 45
Есть небольшая задачка. Как ее решить PL/SQL знаю,
есть ли варианты решения ее путем SQL?

Есть таблица
TAB1
group_id   event_id
  1               1
  1               2
  1               3
  2               4
  2               5
  3               6
event_id уникален
Нужно получить следующее
1. Варианты с участием всех групп и одним событием для группы. Варианты должны присутствовать все группы. Т.е. нужно получить перебор всех возможных событий в группах.
Результат должен быть такого вида
TAB2
  variant     group_id   event_id
    1             1                 1
    1             2                 4
    1             3                 6
    2             1                 2 
    2             2                 4
    2             3                 6
    3             1                 3
    3             2                 4
    3             3                 6
    4             1                 1 
    4             2                 5 
    4             3                 6
  ...            ...                ...   

2. При изменении исходной TAB1 таблицы (только добавление) обновить таблицу TAB2.
В одном варианте должны присутствовать все группы.
20 май 10, 23:25    [8810501]     Ответить | Цитировать Сообщить модератору
 Re: Написать SQL  [new]
-2-
Member

Откуда:
Сообщений: 15330
V78,

В предположении непрерывной нумерации групп, начиная с 1 (обойти через row_number), и небольшой длины группы в символьном представлении:
with t as (
   select 1 group_id, 1 event_id from dual union all
   select 1 group_id, 2 event_id from dual union all
   select 1 group_id, 3 event_id from dual union all
   select 2 group_id, 4 event_id from dual union all
   select 2 group_id, 5 event_id from dual union all
   select 3 group_id, 6 event_id from dual 
),
s as (
   select sys_connect_by_path(event_id, '/') variant
   from t 
   where connect_by_isleaf = 1
   start with group_id = 1 
   connect by group_id=prior group_id + 1
)
select s.variant, t.group_id, t.event_id
from s join t
   on (instr(s.variant||'/', '/'||t.event_id||'/')>0);
Иначе надо перенумеровывать группы через аналитику.
21 май 10, 01:18    [8810733]     Ответить | Цитировать Сообщить модератору
 Re: Написать SQL  [new]
-2-
Member

Откуда:
Сообщений: 15330
V78,

with t as (
   select 1 group_id, 1 event_id from dual union all
   select 1 group_id, 2 event_id from dual union all
   select 1 group_id, 3 event_id from dual union all
   select 2 group_id, 4 event_id from dual union all
   select 2 group_id, 5 event_id from dual union all
   select 3 group_id, 6 event_id from dual 
),
s as (
   select group_id, event_id, count(*) over(partition by group_id) cardinality, row_number() over(partition by group_id order by event_id) rn
   from t
),
u as (
   select variant, rn, group_id, event_id
   from s
   model
   return updated rows
   partition by (group_id)
   dimension by (0 variant, rn)
   measures (event_id, round(exp(sum(ln(case when rn=1 then cardinality else 1 end)) over())/cardinality) other_cardinality)
   rules upsert all (
      event_id[for variant from 1 to other_cardinality[0, 1] increment 1, any] = event_id[0, cv()]
   )
)
select row_number() over(partition by group_id order by variant, rn) variant, group_id, event_id
from u
order by 1, 2
;
21 май 10, 01:50    [8810755]     Ответить | Цитировать Сообщить модератору
 Re: Написать SQL  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18337
До кучи:
with t1 as (select 1 group_id, 1 event_id
from dual union all select  1, 2
from dual union all select  1, 3
from dual union all select  2, 4
from dual union all select  2, 5
from dual union all select  3, 6
from dual)
select variant, group_id, event_id 
from t1
model return all rows
dimension by ( dense_rank() over(order by group_id) gid
             , row_number() over(partition by group_id order by event_id) variant)
measures (group_id
, event_id
, count(event_id) over(partition by group_id) g_pow
, 0 a_pow
, 0 g_cnt
)
rules sequential order(
  a_pow[1,1] = power(10,sum(log(10,g_pow))[any,1])
, g_cnt[1,1] = count(distinct group_id)[any,any]
, event_id[for gid from 1 to g_cnt[1,1] increment 1
          ,for variant from 1 to a_pow[1,1] increment 1
          ] = event_id[cv(),nvl(nullif(mod(cv(variant),g_pow[cv(),1]),0),g_pow[cv(),1])]  
, group_id[any,any] = group_id[cv(),1]
)
order by variant, group_id, event_id
21 май 10, 02:28    [8810781]     Ответить | Цитировать Сообщить модератору
 Re: Написать SQL  [new]
-2-
Member

Откуда:
Сообщений: 15330
andrey_anonymous,

О сколько вариантов чудных до кучи форум нам дает:
with t1 as (select 1 group_id, 1 event_id
from dual union all select  1, 2
from dual union all select  1, 3
from dual union all select  2, 4
from dual union all select  2, 5
from dual union all select  3, 6
from dual),
s as (
   select cast(collect(ge_obj(group_id, event_id)) as ge_tbl) ge, count(distinct group_id) gcnt from t1
),
ss as (
   select rownum variant, value(ss) as ge, gcnt
   from s, table(cast(powermultiset_by_cardinality(ge, gcnt) as ge_tbl_tbl)) ss
),
sss as (
   select variant, gcnt, cast(collect(ge_obj(group_id, event_id)) as ge_tbl) ge
   from ss, table(ss.ge) sss
   group by variant, gcnt
   having count(distinct group_id) = gcnt
)
select variant, group_id, event_id 
from sss, table(ge);
21 май 10, 09:10    [8811125]     Ответить | Цитировать Сообщить модератору
 Re: Написать SQL  [new]
myaucha
Member

Откуда: Москва
Сообщений: 1148
Скоро SQL превратится в PERL
21 май 10, 10:08    [8811412]     Ответить | Цитировать Сообщить модератору
 Re: Написать SQL  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18483
Судя по многословию, скорее в COBOL
21 май 10, 10:12    [8811446]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить