संदर्भित तालिका में बिल्कुल समान मानों की टी-एसक्यूएल खोज

My task is to find business objects which are exactly same as given object (ones which have exactly same set of properties with exactly same values). Performance is critical.

वस्तुओं की औसत संख्या के आधार पर दृष्टिकोण भिन्न हो सकता है, आमतौर पर कुछ बनाम दर्जनों।

यह मानते हुए कि वस्तुओं में गुणों की संख्या भिन्न होती है:

मैं चयन-प्रदर्शन के लिए dyad (PropertyValues.PropertyId, PropertyValues.PropertyValue) पर एक समग्र गैर-अद्वितीय अनुक्रमणिका के साथ शुरू करूंगा।

फिर, एक इकाई आईडी दिया गया है, मैं एक कर्सर में इसकी संपत्ति, संपत्ति मूल्य जोड़े का चयन करूंगा।

[संपादित करें: सुनिश्चित नहीं है कि (entityid, propertyid) आपके सिस्टम में अद्वितीय है या यदि आप एक इकाई के लिए एक ही प्रॉपर्टी आईडी के कई उदाहरणों की अनुमति दे रहे हैं, उदा। पसंदीदा रंग:

              entityid  propertyid property value
                1            17     blue
                1            17     dark blue
                1            17     sky blue
                1            17     ultramarine

आपको मोनैड (PropertyValues.entityid) पर एक गैर-अद्वितीय अनुक्रमणिका या (PropertyValues.entityid,PropertyValues.propertyid) पर एक समग्र अनुक्रमणिका की भी आवश्यकता होगी; यदि आप एक ही संपत्ति को एक से अधिक बार किसी इकाई से संबद्ध होने से रोकना चाहते हैं तो समग्र अनुक्रमणिका अद्वितीय होगी।

यदि कोई संपत्ति कई बार हो सकती है, तो संभवतः आपके गुण तालिका में एक CanBeMultivalued ध्वज होना चाहिए। यदि आप इसे रोकना चाहते हैं तो आपके पास त्रिभुज (इकाई, संपत्ति आईडी, संपत्ति मूल्य) पर एक अद्वितीय अनुक्रमणिका होनी चाहिए:

        entityid  propertyid property value
                1            17     blue
                1            17     blue

यदि आपके पास यह त्रिभुज अनुक्रमित है, तो आपको PropertyValues ​​​​तालिका में (entityid) अनुक्रमणिका या (entityid, propertyid) समग्र अनुक्रमणिका की आवश्यकता नहीं होगी।

[/ संपादित करें]

फिर मैं मिलान करने वाली इकाई आईडी स्टोर करने के लिए एक अस्थायी तालिका बनाउंगा।

फिर मैं दिए गए इकाई की संपत्ति, संपत्ति मूल्य जोड़े, एक समय में एक जोड़ी को पकड़ने के लिए उपरोक्त मेरे कर्सर को फिर से चालू कर दूंगा, और प्रत्येक पुनरावृत्ति के साथ एक चयन कथन जारी करूंगा:

      insert into temp
      select entityid from PropertyValues
      where propertyid = mycursor.propertyid and propertyvalue = mycursor.propertyvalue

लूप के अंत में, आपके पास उन सभी संस्थाओं के लिए आपकी अस्थायी तालिका में एंटिटीड्स का एक गैर-विशिष्ट सेट होता है, जिसमें दिए गए ऑब्जेक्ट के साथ कम से कम एक गुण समान होता है। लेकिन जो आप चाहते हैं उनमें सभी गुण समान होने चाहिए।

चूंकि आप जानते हैं कि दी गई वस्तु में कितने गुण हैं, आप केवल उन संस्थाओं को लाने के लिए निम्न कार्य कर सकते हैं जिनमें दिए गए ऑब्जेक्ट के साथ सभी गुण समान हैं:

       select entityid from temp
       group by entityid having count(entityid) =  {the number of properties in the given object}

परिशिष्ट:

सभी संभावित मैचों का चयन करने के लिए दिए गए ऑब्जेक्ट की पहली संपत्ति-मूल्य जोड़ी का उपयोग करने के बाद, आपकी अस्थायी तालिका में कोई भी संभावित मिलान नहीं होगा; बल्कि इसमें ऐसे एंटिटीड्स होंगे जो पूर्ण मिलान नहीं थे, जिन्हें किसी तरह से छोड़ दिया जाना चाहिए, या तो अनदेखा किया जाना चाहिए (आपके समूह द्वारा ... क्लॉज) या स्पष्ट रूप से अस्थायी तालिका से हटा दिया जाना चाहिए।

साथ ही, लूप के पहले पुनरावृत्ति के बाद, आप इस संभावना का पता लगा सकते हैं कि अस्थायी तालिका और संपत्ति वैल्यू तालिका के बीच एक आंतरिक जुड़ाव कुछ प्रदर्शन लाभ प्रदान कर सकता है:

        select entityid from propertvalues
        >> inner join temp on temp.entityid = propertyvalues.entityid <<
        where propertyid = mycursor.propertyid and propertyvalue = mycursor.propertyvalue

और आप पहले पुनरावृत्ति के बाद अस्थायी से एंटिटीड्स को हटाने का भी प्रयास कर सकते हैं:

        delete from temp
        where not exists
        (
         select entityid from propertyvalues
         inner join temp on temp.entityid = propertyvalues.entityid
         where propertyid = mycursor.propertyid and propertyvalue = mycursor.propertyvalue
        )

वैकल्पिक रूप से, यदि आप संपत्ति-आवृत्ति के बारे में कुछ मेटाडेटा संग्रहीत करते हैं, तो इस लूपिंग दृष्टिकोण को और अधिक अनुकूलित करना संभव होगा। इष्टतम रूप से, किसी दिए गए निकाय के लिए मिलान खोजते समय, आप कम से कम बार-बार आने वाले गुण-मान युग्म से प्रारंभ करना चाहेंगे। आप दिए गए ऑब्जेक्ट के गुण-मूल्य जोड़े को आरोही आवृत्ति द्वारा ऑर्डर कर सकते हैं, ताकि आपके लूप में आप सबसे पहले दुर्लभतम की तलाश कर सकें। यह संभावित मैचों के सेट को लूप के पहले पुनरावृत्ति पर अपने सबसे छोटे संभव आकार तक कम कर देगा।

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

इसे देखने का एक तरीका यह है कि यदि मेरे पास आपके पास सभी बेस बॉल कार्ड हैं तो हमारे पास उतना ही बेसबॉल कार्ड नहीं है जितना मेरे पास अधिक हो सकता है। लेकिन अगर आपके पास सभी बेसबॉल कार्ड हैं जो मेरे पास हैं तो हमारे पास बिल्कुल वही बेसबॉल कार्ड हैं। यह थोड़ा अधिक जटिल है जैसा कि हम टीम द्वारा देख रहे हैं। टीम द्वारा मैच की गिनती, मेरी गिनती और आपकी गिनती की गणना की जा सकती है और उन 3 गणनाओं की तुलना की जा सकती है लेकिन वह 3 जोड़ है। यह समाधान 2 जॉइन है और मुझे लगता है कि यह 3 जॉइन विकल्प से तेज होगा।

मेरे लिए बोनस प्रश्नों का कोई मतलब नहीं था। एक तालिका में परिवर्तन के रूप में लेकिन उस तालिका का नाम किसी भी तालिका से मेल नहीं खाता। उन बोनस प्रश्नों के लिए एक पूर्ण तालिका विवरण की आवश्यकता है।

नीचे 2 शामिल होने का विकल्प है:

    select [m1].[IDa] as [EntityId1], [m1].[IDb] as [EntityId2]
    from
    (   select [PV1].[EntityId] as [IDa], [PV2].[EntityId] as [IDb]
        from [PropertyValue] as [PV1] 
        left outer join [PropertyValue] as [PV2] 
            on  [PV2].[EntityId] <> [PV1].[EntityId]
            and [PV2].[PropertyId] = [PV1].[PropertyId]
            and [PV2].[PropertyValue] = [PV1].[PropertyValue] 
        group by [PV1].[EntityId], [PV2].[EntityId]
        having count(*) = count([PV2].[EntityId])
    ) as [m1]
    join 
    (   select [PV1].[EntityId] as [IDa], [PV2].[EntityId] as [IDb]
        from [PropertyValue] as [PV1] 
        right outer join [PropertyValue] as [PV2] 
            on  [PV2].[EntityId] <> [PV1].[EntityId]
            and [PV2].[PropertyId] = [PV1].[PropertyId]
            and [PV2].[PropertyValue] = [PV1].[PropertyValue] 
        group by [PV1].[EntityId], [PV2].[EntityId]
        having count(*) = count([PV1].[EntityId]))
    ) as [m2]
    on [m1].[IDa] = [m2].[IDa] and [m1].[IDb] = [m2].[IDb] 

नीचे 3 जॉइन काउंट आधारित विकल्प है:

    select [m1].[IDa] as [EntityId1], [m1].[IDb] as [EntityId2]
    from
    (   select [PV1].[EntityId] as [IDa], [PV2].[EntityId] as [IDb], COUNT(*) as [count]
        from [PropertyValue] as [PV1] 
        join [PropertyValue] as [PV2] 
            on  [PV2].[EntityId] <> [PV1].[EntityId]
            and [PV2].[PropertyId] = [PV1].[PropertyId]
            and [PV2].[PropertyValue] = [PV1].[PropertyValue] 
        group by [PV1].[EntityId], [PV2].[EntityId]
    )   as [m1]
    join 
    (   select [PV1].[EntityId] as [IDa], COUNT(*) as [count]
        from [PropertyValue] as [PV1] 
        group by [PV1].[EntityId]
        having count(*) = count([PV1].[sID]))
    )   as [m2]
    on [m1].[IDa] = [m2].[IDa] and [m1].[count] = [m2].[count]
    join 
    (   select [PV2].[EntityId] as [IDb], COUNT(*) as [count]
        from [PropertyValue] as [PV2] 
        group by [PV2].[EntityId]
    )   as [m3]
    on [m1].[IDb] = [m3].[IDb] and [m1].[count] = [m3].[count]

मेरा काम उन व्यावसायिक वस्तुओं को खोजना है जो बिल्कुल दी गई वस्तु के समान हैं (जिनके पास बिल्कुल समान मूल्यों के साथ गुणों का एक ही सेट है)।

यदि "दिया गया objec" t को उदा के रूप में वर्णित किया गया है। #PropertyValues, तो क्वेरी होगी:

create table #PropertyValues(
    [PropertyId] [int] NOT NULL,
    [PropertyValue] [int] NOT NULL
)

insert #PropertyValues
select
    3, 3 -- e.g.

declare
    @cnt int

select @cnt = count(*) from #PropertyValues

select 
    EntityId
from 
    PropertyValues pv
    left join  #PropertyValues t on t.PropertyId = pv.PropertyId and t.PropertyValue = pv.PropertyValue
group by
    EntityId
having 
    count(t.PropertyId) = @cnt
    and count(pv.PropertyId) = @cnt

drop table #PropertyValues

लेकिन अगर प्रदर्शन इतना महत्वपूर्ण है, तो आप टेबल एंटिटीज पर विशेष अनुक्रमित फ़ील्ड बना सकते हैं, उदा। EntityIndex varchar(8000), जो प्रॉपर्टी वैल्यू टेबल पर ट्रिगर द्वारा कन्वर्ट (चार (10), प्रॉपर्टी आईडी) + कन्वर्ट (चार (10), प्रॉपर्टी वैल्यू) (इकाई के सभी गुणों के लिए, क्रमबद्ध!) के रूप में भरा जाएगा। तो इस क्षेत्र द्वारा बहुत तेजी से खोज करना संभव होगा।

मुझे लगता है कि यह सिर्फ एक साधारण आत्म-जुड़ना है:

select P2.EntityID,E.EntityName
from PropertyValues P1
inner join PropertyValues P2
on   P1.PropertyID    = P2.PropertyID
and  P1.PropertyValue = P2.PropertyValue
inner join Entity E
on P2.EntityID = E.EntityID
where P1.EntityId = 1
and   P2.EntityId <> 1
group by P2.EntityID, E.EntityName