найти полное соответствие множеств с подгруппой

Hawker_1

Дата: 21.09.2015 18:05:55

with c as 
  (select 10 as id, 21 as typ, 0 as period from dual
    union all
   select 10 as id, 22 as typ, 0 as period from dual
    union all
   select 10 as id, 23 as typ, 0 as period from dual
    union all
   select 10 as id, 21 as typ, 1 as period from dual
   union all
   select 10 as id, 21 as typ, 2 as period from dual
   union all
   select 10 as id, 21 as typ, 3 as period from dual
   union all
   select 10 as id, 21 as typ, 4 as period from dual
   union all
   select 10 as id, 22 as typ, 1 as period from dual
   union all
   select 10 as id, 22 as typ, 2 as period from dual
   union all
   select 10 as id, 22 as typ, 3 as period from dual
    union all
   select 10 as id, 22 as typ, 4 as period from dual
),
spl as  (
    select 0 as code, 0 as gr from dual
        union all
    select 1 as code, 1 as gr from dual
        union all  
    select 2 as code, 1 as gr from dual
        union all  
    select 3 as code, 1 as gr from dual
        union all      
    select 4 as code, 1 as gr from dual
        union all  
    select 5 as code, 5 as gr from dual
        union all  
    select 6 as code, 6 as gr from dual                                
)
  select * from (   
   select c.id, c.typ, c.period, t.code from c 
    full outer join ( select code from  spl
                        where gr in ( select gr from spl 
                                where code = c.period)) t
    on c.period = spl.code )
    where period is null or code is null;

ORA-00904: "C"."PERIOD": invalid identifier

соответственно вышеуказанный запрос должен вернуть пустоту

Нужно для каждой связки id + typ убедиться, что множество вариантов из данной группы равно (как множество) элементам code группы.

те например для если у связки айди и тип есть период 1, то должны быть ещё 2, 3, 4 и ТОЛЬКО они.

на мой взгляд это решается через full outer join и отсеивание null, но мешает подзапрос.

а

Hawker_1

Дата: 21.09.2015 18:19:17

with c as 
  (select 10 as id, 21 as typ, 0 as period from dual
    union all
   select 10 as id, 22 as typ, 0 as period from dual
    union all
   select 10 as id, 23 as typ, 0 as period from dual
    union all
   select 10 as id, 21 as typ, 1 as period from dual
   union all
   select 10 as id, 21 as typ, 2 as period from dual
   union all
   select 10 as id, 21 as typ, 3 as period from dual
   union all
   select 10 as id, 21 as typ, 4 as period from dual
   union all
   select 10 as id, 21 as typ, 1 as period from dual  -- ERROR!!! 
   union all
   select 10 as id, 22 as typ, 1 as period from dual
   union all
   select 10 as id, 22 as typ, 2 as period from dual
   union all
   select 10 as id, 22 as typ, 3 as period from dual
    union all
   select 10 as id, 22 as typ, 4 as period from dual
),
spl as  (
    select 0 as code, 0 as gr from dual
        union all
    select 1 as code, 1 as gr from dual
        union all  
    select 2 as code, 1 as gr from dual
        union all  
    select 3 as code, 1 as gr from dual
        union all      
    select 4 as code, 1 as gr from dual
        union all  
    select 5 as code, 5 as gr from dual
        union all  
    select 6 as code, 6 as gr from dual                                
),
cross_ as 
    (select g1.code as code_in, g2.code as code_out from v_progress_tariff_gr    g1
        join v_progress_tariff_gr g2 on g1.gr = g2.gr
        order by code_in, code_out)
  select * from (   
   select c1.id, c1.typ, c1.period, t.code from c c1 
    full outer join ( select code_out from cross_ 
                                where code = c1.period) t
    on c1.period = spl.code )
    where period is null or code is null;

и даже так не помогает, ну не хочет соединяться с подзапросом *(

Добрый Э - Эх

Дата: 21.09.2015 18:25:01

Hawker_1,

lateral тебе нужен. или [CROSS | OUTER] APPLY.
без них невозможно сделать коррелированное встроенное представление.

Добрый Э - Эх

Дата: 21.09.2015 18:32:08

Добрый Э - Эх,

а вообще, задача твоя - суть есть реляционное деление.
обычно через [not] exists-подзапросы решается, или на GROUP BY + HAVING

Дата: 21.09.2015 19:52:06

For fun с коллекциями и multiset:

create or replace
  type NumList
    as table of number
/
with c as (
           select 10 as id, 21 as typ, 0 as period from dual union all
           select 10 as id, 22 as typ, 0 as period from dual union all
           select 10 as id, 23 as typ, 0 as period from dual union all
           select 10 as id, 21 as typ, 1 as period from dual union all
           select 10 as id, 21 as typ, 2 as period from dual union all
           select 10 as id, 21 as typ, 3 as period from dual union all
           select 10 as id, 21 as typ, 4 as period from dual union all
           select 10 as id, 22 as typ, 1 as period from dual union all
           select 10 as id, 22 as typ, 2 as period from dual union all
           select 10 as id, 22 as typ, 3 as period from dual union all
           select 10 as id, 22 as typ, 4 as period from dual
          ),
   spl as (
           select 0 as code, 0 as gr from dual union all
           select 1 as code, 1 as gr from dual union all  
           select 2 as code, 1 as gr from dual union all  
           select 3 as code, 1 as gr from dual union all      
           select 4 as code, 1 as gr from dual union all  
           select 5 as code, 5 as gr from dual union all  
           select 6 as code, 6 as gr from dual                                
          ),
     x as (
           select  id,
                   typ,
                   cast(collect(period) as NumList) s
             from  c
             group by id,
                      typ
          ),
     y as (
           select  cast(collect(code) as NumList) s
             from  spl
             group by gr
          )
select  *
  from  x
  where not exists (
                    select  1
                      from  y
                      where x.s multiset except y.s = NumList()
                        and y.s multiset except x.s = NumList()
                   )
/

        ID        TYP S
---------- ---------- ----------------------
        10         21 NUMLIST(0, 4, 3, 2, 1)
        10         22 NUMLIST(0, 4, 3, 2, 1)

SQL>

SY.

Дата: 21.09.2015 20:00:06

SY
For fun с коллекциями и multiset:

Ну а так - LISTAGG (или XMLAGG если число елементов в группе большое):

with c as (
           select 10 as id, 21 as typ, 0 as period from dual union all
           select 10 as id, 22 as typ, 0 as period from dual union all
           select 10 as id, 23 as typ, 0 as period from dual union all
           select 10 as id, 21 as typ, 1 as period from dual union all
           select 10 as id, 21 as typ, 2 as period from dual union all
           select 10 as id, 21 as typ, 3 as period from dual union all
           select 10 as id, 21 as typ, 4 as period from dual union all
           select 10 as id, 22 as typ, 1 as period from dual union all
           select 10 as id, 22 as typ, 2 as period from dual union all
           select 10 as id, 22 as typ, 3 as period from dual union all
           select 10 as id, 22 as typ, 4 as period from dual
          ),
   spl as (
           select 0 as code, 0 as gr from dual union all
           select 1 as code, 1 as gr from dual union all  
           select 2 as code, 1 as gr from dual union all  
           select 3 as code, 1 as gr from dual union all      
           select 4 as code, 1 as gr from dual union all  
           select 5 as code, 5 as gr from dual union all  
           select 6 as code, 6 as gr from dual                                
          ),
     x as (
           select  id,
                   typ,
                   listagg(period,',') within group(order by period) s
             from  c
             group by id,
                      typ
          ),
     y as (
           select  listagg(code,',') within group(order by code) s
             from  spl
             group by gr
          )
select  *
  from  x
  where not exists (
                    select  1
                      from  y
                      where x.s = y.s
                   )
/

        ID        TYP S
---------- ---------- ----------
        10         21 0,1,2,3,4
        10         22 0,1,2,3,4

SQL>

SY.

Hawker_1

Дата: 22.09.2015 10:05:09

Добрый Э - Эх
Добрый Э - Эх, а вообще, задача твоя - суть есть реляционное деление. обычно через [not] exists-подзапросы решается, или на GROUP BY + HAVING

да вот что-то не решается как раз, обидно, что нельзя соединяться с коррелированным подзапросом.

Метод SY через коллекции работает, спасибо, но что-то у мя подозрение, что мультисеты завалятся на больших объёмах...

сейчас пусть так, а на выходных подумаю как средствами "обычного" sql решить сию тему

dbms_photoshop

Дата: 23.09.2015 00:38:01

Hawker_1

Добрый Э - Эх
Добрый Э - Эх, а вообще, задача твоя - суть есть реляционное деление. обычно через [not] exists-подзапросы решается, или на GROUP BY + HAVING

да вот что-то не решается как раз, обидно, что нельзя соединяться с коррелированным подзапросом.

Да не надо здесь никаких соединений с коррелированными подзапросами.
Элементарно надо каким либо из методов найти "хэш группы" и по нему соединяться. Всё.

Чтоб не завязывать на ora_hash - смотри здесь 9451456 мой пример и выше от -2-.

Скоррелировать (почти) все что угодно можно если обернуть inline view в табличную функцию ну или с помощью трюка cast + multiset.
В этом случае написанная тобой логика - полная ерунда.

select *
  from (select c.id, c.typ, c.period, t.column_value
          from c
          full outer join table(cast(multiset (select code
                                                from spl
                                               where gr in
                                                     (select gr
                                                        from spl
                                                       where code = c.period)) as sys.odcivarchar2list)) t
            on c.period = t.column_value)
 where period is null
    or column_value is null;

Не забывай, что скоррелированное inline view возвращает набор строк для каждой строки из c.

dbms_photoshop

Дата: 23.09.2015 01:09:22

SY
XMLAGG если число елементов в группе большое

Не стоит советовать ерунду. 17727498
Я надеюсь вам и без меня про его производительность хорошо известно.

Hawker_1

Дата: 23.09.2015 11:19:43

dbms_photoshop

SY
XMLAGG если число елементов в группе большое

Не стоит советовать ерунду. 17727498
Я надеюсь вам и без меня про его производительность хорошо известно.

listagg в качестве сворачивателя множества в трубочку подходит идеально, так что в целом SY прав

от мультисетов я отказался ввиду тормознутости оных.