All geek questions in one place

Evaluate hierarchical query based on results from lower levels

I am using Oracle Database 11g, and below is a dummy table with dummy values, which I am now trying to explain:

I have a table that describes the relationship between identifiers that represent "groups" and identifiers that are members of a group. Group members can be either simple members or some of the groups themselves (without cycles). As a result, some groups are simple level 1 groups, while some of them can have potentially many levels. Each group has a “minimum limit” value, and each member of a group has a certain meaning, including a group, when it acts as a member of that group.

Now what I'm trying to do is just check that the sum of the values ​​of the group members is equal to or greater than the minimum limit value for the group. It would be pretty easy if not for the hierarchy. The problem is that if one of the members is a group, I can calculate with its member value ONLY if I myself have sufficient element values ​​to satisfy my minimum limit. Therefore, the assessment should go from leaf to root, in order to first evaluate the lowest group and based on the fact that higher levels of the hierarchy are evaluated.

Unfortunately, I could not figure it out. Can anyone help? Important Note . I would like to solve this problem without using R / CTE, if possible. I cannot accept any answer using the CREATE keyword, as I am limited to using it.

CREATE TABLE "MYGROUPS" 
(   
"MYGROUP_ID" VARCHAR2(20 BYTE), 
"MYGROUP_LIMIT" Number, 
"MEMBER" VARCHAR2(20 BYTE), 
"MEMBER_VALUE" Number
);

insert into mygroups
(Select 'g0'  ,1  ,'00'  ,1 from dual) union
(Select 'g1'  ,5  ,'01'  ,1 from dual) union
(Select 'g1'  ,5  ,'02'  ,1 from dual) union
(Select 'g1'  ,5  ,'03'  ,1 from dual) union
(Select 'g1'  ,5  ,'g2'  ,3 from dual) union
(Select 'g2'  ,3  ,'02'  ,2 from dual) union
(Select 'g2'  ,3  ,'05'  ,2 from dual) union
(Select 'g2'  ,3  ,'g3'  ,2 from dual) union
(Select 'g3'  ,5  ,'03'  ,1 from dual) union
(Select 'g3'  ,5  ,'05'  ,1 from dual)

This data set should result in g1 OK, g2 OK, g3 NOT OK. G1 depends on g2, which in turn does not depend on g3 to be OK, and therefore g1 is also OK.

In this alternate dataset:

insert into mygroups
(Select 'g0'  ,1  ,'00'  ,1 from dual) union
(Select 'g1'  ,5  ,'01'  ,1 from dual) union
(Select 'g1'  ,5  ,'02'  ,1 from dual) union
(Select 'g1'  ,5  ,'03'  ,1 from dual) union
(Select 'g1'  ,5  ,'g2'  ,3 from dual) union
(Select 'g2'  ,3  ,'02'  ,1 from dual) union
(Select 'g2'  ,3  ,'05'  ,1 from dual) union
(Select 'g2'  ,3  ,'g3'  ,2 from dual) union
(Select 'g3'  ,5  ,'03'  ,1 from dual) union
(Select 'g3'  ,5  ,'05'  ,1 from dual)

G1 is NOT OK, because it depends on g2, which here also depends on g3, and therefore all three here lead to NOT OK

Here is a request to get this idea. Values ​​of "OK" rated the group as meeting the minimum limit. "NOT OK" is the opposite. "DUNNO" is a problem when I do not know how I can evaluate it.

select connect_by_root mygroup_id as root, mygroups.*,level
from ( Select mygroups.*,
             sum(member_value) over (partition by mygroup_id) sum_of_values,
             CASE 
                  WHEN sum (CASE WHEN member like 'g%' THEN 1 END) over (partition by mygroup_id) > 0 THEN 'DUNNO'
                  WHEN sum(member_value) over (partition by mygroup_id) >= mygroup_limit THEN 'OK'
                  WHEN sum(member_value) over (partition by mygroup_id) < mygroup_limit THEN 'NOT OK'
             END eval
        From mygroups ) mygroups
connect by prior member = mygroup_id

, , - member_values ​​ .

+4
2

"connect by". .

R/CTE . , , - "" "" R/CTE. "CTE" ( ) ORA-06000 ( ) Oracle.

"R/CTE" ( " " ) ( OK-) "" ". , " ", " " .

with GRP as (
     select A.*, row_number() over (order by L) N
       from (
         select mygroup_id, min(level) L
           from  mygroups m
          start with not exists(select 1 from mygroups m2
                                 where m2.mygroup_id=m.mygroup_id and m2.member like 'g%'
                               )
        connect by member=prior mygroup_id
          group by mygroup_id
       ) A
),
Cursor_tab(mygroup_id,N,list,result) as(
   select NULL,N,lpad(',',2000),0
     from GRP where N=1
 union all
   select G.mygroup_id,Q.N+1,
          ltrim(Q.list)||decode(R.column_value,0,'',G.mygroup_id||','),
          TO_NUMBER(R.column_value)
     from Cursor_tab Q, GRP G,
          table(cast(multiset(
           select decode(sign(sum(member_value)-min(mygroup_limit)),-1,0,1)
             from mygroups m
            where m.mygroup_id=G.mygroup_id
              and (member not like 'g%' or Q.list like '%,'||member||',%')
          ) as sys.odcivarchar2list)) R
    where G.N=Q.N
)
select mygroup_id, decode(result,1,'OK','NOT OK')
  from Cursor_tab
 where mygroup_id is not null
0

MYGROUP_ID MYGROUP_LIMIT , . CONNECT_BY_ROOT, .

, , . , , ; , , MEMBER, STARTING_GROUP STARTING_GROUP_LIMIT

with all_hierarchies as (
 select member_value
      , connect_by_root mygroup_id as starting_group
      , connect_by_root mygroup_limit as starting_group_limit
   from mygroups m
connect by prior member = mygroup_id
        )
select starting_group
  from all_hierarchies
 group by starting_group
having sum(member_value) >= min(starting_group_limit)

, , . , , - :

with all_hierarchies as (
 select member_value
      , connect_by_root mygroup_id as starting_group
      , connect_by_root mygroup_limit as starting_group_limit
   from mygroups m
connect by prior member = mygroup_id
        )
, qualifying_groups as (
select starting_group
  from all_hierarchies
 group by starting_group
having sum(member_value) >= min(starting_group_limit)
       )
select a.*
  from all_hierarchies a
  join qualifying_groups q
    on a.starting_group = q.starting_group

CTE, . , . CTE, SQL .

0

Source: https://habr.com/ru/post/1653507/

More articles:


All Articles