sql - निम्न स्तरों के परिणामों के आधार पर एक पदानुक्रमित क्वेरी का मूल्यांकन करें




oracle oracle11g (3)

मैं ओरेकल डाटाबेस 11 जी का उपयोग कर रहा हूं और ड्रीम डमी वैल्यू के साथ एक डमी टेबल है, जो अब मैं समझाने की कोशिश करता हूं:

मेरे पास एक मेज है जो आईडी के बीच संबंध का वर्णन करता है जो "समूह" और आईडी समूह के सदस्यों का प्रतिनिधित्व करते हैं। समूह के सदस्य सामान्य सदस्य या कुछ समूह स्वयं (चक्र के बिना) हो सकते हैं नतीजतन, कुछ समूह 1 स्तर के साधारण समूह हैं, जबकि कुछ में संभावित कई स्तर हो सकते हैं। प्रत्येक समूह में एक "न्यूनतम सीमा" मान होता है और प्रत्येक समूह का सदस्य उस समूह के किसी सदस्य के रूप में कुछ मूल्य का होता है, जब वह उस समूह के सदस्य के रूप में कार्य करता है।

अब, मैं क्या करने की कोशिश कर रहा हूं, यह जांचना है कि समूह के सदस्य मूल्यों की एक संख्या के बराबर या समूह की न्यूनतम सीमा मूल्य से अधिक है। यह काफी आसान होगा यदि यह पदानुक्रम का नहीं था। समस्या यह है कि अगर सदस्यों में से कोई एक समूह है तो मैं अपने सदस्य मूल्य के साथ ही भरोसा कर सकता हूं अगर इसके पास अपनी न्यूनतम सीमा को पूरा करने के लिए पर्याप्त सदस्य मान हैं इसलिए मूल्यांकन को पत्ती से जड़ तक जाने की जरूरत है ताकि सबसे कम समूह का मूल्यांकन किया जा सके और इसके आधार पर पदानुक्रम के उच्च स्तर का मूल्यांकन किया जा सके।

दुर्भाग्य से मैं इसे समझने में सक्षम नहीं था। क्या कोई मदद कर सकता है? महत्वपूर्ण नोट : यदि संभव हो तो मैं आर / सीटीटी उपयोग किए बिना इसका समाधान करना चाहूंगा मैं CREATE कीवर्ड का उपयोग करके किसी भी उत्तर को स्वीकार नहीं कर सकता क्योंकि मैं इसका उपयोग करने के लिए प्रतिबंधित हूं।

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)

डेटा के इस सेट में जी 1 ठीक, जी 2 ठीक, जी 3 ठीक नहीं होना चाहिए। जी 1 जी 2 पर निर्भर है, जो बारी-बारी से जी 3 पर निर्भर नहीं है और इसलिए जी 1 ठीक भी है।

डेटा के इस वैकल्पिक सेट में:

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)

जी 1 ठीक नहीं है, क्योंकि यह जी 2 पर निर्भर करता है जो यहां भी जी 3 पर निर्भर है और इसलिए तीनों में इसका परिणाम ठीक नहीं है

विचार प्राप्त करने के लिए यहां एक क्वेरी है 'ओके' मान समूह को न्यूनतम सीमा को संतोषजनक मानते हैं। 'ठीक नहीं' विपरीत है 'डननो' एक ऐसी समस्या है जहां मुझे नहीं पता कि मैं इसे कैसे मूल्यांकन कर सकता हूं।

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

इसके अलावा, किसी भी समूह में विभिन्न स्तरों पर किसी भी संख्या में विभिन्न समूहों को शामिल किया जा सकता है और सदस्य-समूह विभिन्न समूहों में अलग-अलग सदस्य_गुणाओं के पास हो सकते हैं।


Answers

मैंने "द्वारा कनेक्ट" समस्या को हल करने की कोशिश की लेकिन मुझे पुनरुत्थान के अगले स्तर पर गणना के परिणाम उत्तीर्ण करने का कोई तरीका नहीं है।

आर / सीटीटी के साथ इसी तरह की समस्याओं का सामना करना पड़ा। इसके अलावा आर / सीटीई में "समूह" और "विंडो" फ़ंक्शन का उपयोग करने में असमर्थता के कारण मजबूत सीमाएं थीं "सीटीई उपनाम" को रिकर्सिव भाग (पिछली पंक्ति में शामिल होने के लिए) में दो बार उपयोग करने का प्रयास ORA-06000 (आंतरिक सर्वर त्रुटि) की ओर जाता है और ओरेकल से कनेक्शन छोड़ देता है।

आज, यह एक "पंक्ति / सीटीई" पुनरावर्ती लूप प्रदान करने के लिए एक एकल पंक्ति (बाद में: "कर्सर पंक्ति") के साथ, और इस "कर्सर पंक्ति" में एक राज्य (ओक-समूह की सूची) रखने के लिए हुई। जरूरी डेटा पंक्तियां, "पत्तियों से जड़ तक" क्रम में पहले से गिने गए हैं, पंक्ति क्रमांक के द्वारा प्रत्येक कर्ते में "कर्सर पंक्ति" में शामिल हो गए हैं।

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

आपको पेड़ का निर्माण करते समय 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

यह एक सीटीई का उपयोग करता है, लेकिन सिर्फ स्वच्छता के लिए यदि आप चाहते हैं तो आप इसे से छुटकारा पा सकते हैं मैं एक पुनरावर्ती सीटीई का उपयोग करने पर विचार करता हूं क्योंकि यह मानक एसक्यूएल है और आपकी क्वेरी को अधिक पोर्टेबल बनाती है।


MySQL enum के बारे में कुछ पढ़ना, मैं अनुमान लगा रहा हूं कि निकटतम समकक्ष एक साधारण चेक बाधा होगी

CREATE TABLE sizes (
  name VARCHAR2(10) CHECK( name IN ('small','medium','large') )
);

लेकिन यह आपको सूचकांक द्वारा मूल्य का संदर्भ देने की अनुमति नहीं देता है। एक और जटिल विदेशी कुंजी संबंध भी संभव होगा

CREATE TABLE valid_names (
  name_id   NUMBER PRIMARY KEY,
  name_str  VARCHAR2(10)
);

INSERT INTO valid_sizes VALUES( 1, 'small' );
INSERT INTO valid_sizes VALUES( 2, 'medium' );
INSERT INTO valid_sizes VALUES( 3, 'large' );

CREATE TABLE sizes (
  name_id NUMBER REFERENCES valid_names( name_id )
);

CREATE VIEW vw_sizes
  AS 
  SELECT a.name_id name, <<other columns from the sizes table>>
    FROM valid_sizes a,
         sizes       b
   WHERE a.name_id = b.name_id

जब तक आप दृश्य के माध्यम से काम करते हैं, ऐसा लगता है कि आप कार्यक्षमता को अच्छी तरह से दोहरा सकते हैं।

अब, यदि आप पीएल / एसक्यूएल समाधान स्वीकार करते हैं, तो आप कस्टम ऑब्जेक्ट प्रकार बना सकते हैं जिसमें वे मूल्यों के सेट को सीमित करने के लिए तर्क शामिल कर सकते हैं और आईडी प्राप्त करने और मान प्राप्त करने के तरीके हैं।





sql oracle oracle11g