ऐतिहासिक तालिकाओं में शामिल हों

समय-समय पर मुझे ऐसे कार्यों से निपटना पड़ता है जब मुझे मौजूदा DBMS के ढांचे के भीतर दो या दो से अधिक ऐतिहासिक तालिकाओं को एक दूसरे से जोड़ने की आवश्यकता होती है, ताकि आउटपुट पर सुंदर ऐतिहासिक अंतराल प्राप्त हो सके। क्यों? ताकि रिपोर्ट उपयोगकर्ता द्वारा चयनित तिथि के लिए डेटा को सही ढंग से प्रदर्शित कर सके, या प्रसंस्करण के लिए इन डेटा में खींची गई एप्लिकेशन।
कार्यशाला में अक्सर सहकर्मियों और भाइयों को समान कार्यों का सामना करना पड़ता है और सलाह दी जाती है कि उन्हें कैसे हल किया जाए।
इस लेख में मैं अपने अनुभव को साझा करना चाहता हूं कि इस प्रकार की विभिन्न स्थितियों को कैसे हल किया गया।

मैं तुरंत उल्लेख करता हूं कि जब मैं "ऐतिहासिक तालिका" वाक्यांश कहता हूं तो मेरा मतलब है
SCD टाइप 2 या SCD टाइप 6 ।

हम मुख्य रूप से डेटा वेयरहाउस के बारे में भी बात करेंगे। लेकिन नीचे वर्णित कुछ दृष्टिकोण ओएलटीपी समाधानों पर लागू होते हैं। यह माना जाता है कि शामिल तालिकाओं में संदर्भात्मक अखंडता को प्राकृतिक तरीके से बनाए रखा जाता है या बूट पर हानि मान उत्पन्न किया जाता है (कुंजी के साथ मान, लेकिन चर विशेषताओं के डिफ़ॉल्ट मूल्यों के साथ)।

ऐसा लगता है कि 2 टेबल हैं, प्रत्येक में एक कुंजी है, बदलते गुण हैं, रिकॉर्ड के अंतराल की तारीखें हैं - उन्हें कुंजी से जुड़ें और अंतराल पर कुछ सोचें। लेकिन इतना सरल नहीं है।
इस प्रकार, कार्य निम्नानुसार है:



लेकिन एक इष्टतम तरीके से परिणाम कैसे प्राप्त किया जाए यह आपकी स्थिति पर निर्भर करता है।

विकल्प एक - बहुत अधिक डेटा नहीं है, टेबल भी, हर बार सब कुछ "मक्खी पर" पढ़ा जा सकता है

इस विकल्प में, आप बस आपस में तालिकाओं के एक संयोजन के साथ एक दृश्य बना सकते हैं, लेकिन अंतराल के चौराहे को ध्यान में रखते हुए।

कनेक्शन की स्थिति को योजनाबद्ध रूप से निम्नानुसार वर्णित किया जा सकता है:

First_Table.Start_dt <= Second_Table.End_dt AND Second_Table.Start_dt <= First_Table.End_dt

यहां पाठक अपने दिमाग में इसकी कल्पना करना शुरू कर सकता है, और उन विकल्पों की गणना कर सकता है जो इस स्थिति को ध्यान में नहीं रखे जा सकते हैं। अंतरापृष्ठ अंतराल के लिए रेखीय रूप से संभव विकल्प इस तरह दिखते हैं:



यदि आप स्थानों में "अंतराल A" और "अंतराल B" बदलते हैं तो विकल्प भी सही हैं। और, ज़ाहिर है, किसी भी अंतराल का अंत खोला जा सकता है। डिज़ाइन प्रश्न कैसे खुला है (क्या एक असीम रूप से दूर की तारीख चुनी गई है या NULL है) समाधान के लिए मौलिक नहीं है, लेकिन SQL लिखते समय आपको यह विचार करने की आवश्यकता है कि आपके सिस्टम में कौन सा विकल्प है।

जिज्ञासु दिमाग की जाँच कर सकते हैं - हालत सभी विकल्पों को शामिल करता है।

लेकिन, रुको, एक ही तरह से अलग-अलग तालिकाओं से 2 पंक्तियों को जोड़ने के परिणामस्वरूप, आपको 2 प्रारंभ दिनांक और 2 समाप्ति तिथियां मिलती हैं। उनमें से कुछ चुना जाना चाहिए। परिणामी अंतराल के लिए, जो दो इंटरसेक्टिंग अंतरालों को जोड़कर प्राप्त किया जाता है, सीमाओं की गणना 2 प्रारंभ तिथियों में से सबसे बड़ी और 2 अंतिम तिथियों में सबसे छोटी के रूप में की जाएगी। Oracle DBMS फ़ंक्शन के संदर्भ में, यह क्रमशः GREATEST (Start_DT) और LEAST (End_DT) लगता है। परिणामस्वरूप परिणामी अंतराल को 3 तालिका से जोड़ा जा सकता है। कनेक्शन का परिणाम, परिणामी शुरुआत और समाप्ति तिथियों की गणना के बाद, 4 वें तालिका से जुड़ा जा सकता है, आदि।

उपयोग किए गए DBMS के आधार पर, SQL को अलग-अलग डिग्री का इनाम मिलता है, लेकिन परिणाम सही है। यह केवल CREATE VIEW में इसे लपेटने और एक परिणामी प्रदर्शन बनाने के लिए बना हुआ है, जिसे उपभोक्ता (रिपोर्ट, एप्लिकेशन, उपयोगकर्ता) एक्सेस करेंगे और प्रासंगिक तारीख को इंगित करेंगे।

विकल्प दो - बहुत सारा डेटा

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

डेटा की गणना कब और कैसे करें? हर बार विस्तृत डेटा डाउनलोड करने के बाद।
इस मामले में, कार्य तुच्छ के लिए नीचे आता है, मुख्य बात यह है कि डेटा की व्यावसायिक तिथि (रिपोर्टिंग तिथि, प्रासंगिकता की तारीख, जो इसे कॉल करता है) को जानना है जो अभी लोड किया गया है। यह ETL \ ELT फ्रेमवर्क का कार्य है, जो इन प्रक्रियाओं को नियंत्रित करता है। हम तारीख (या तारीखों का असतत सेट) में रुचि रखते हैं। एक प्रक्रिया बनाई जाती है (चाहे संग्रहीत हो, या ईटीएल \ ईएलटी यह पहले से ही धर्म द्वारा निर्धारित किया जाता है) जो इनपुट पर एक तारीख या तारीख का एक सेट लेता है। और फिर एक लूप में, यह एसक्यूएल को निष्पादित करना शुरू कर देता है, जो कुंजी द्वारा सभी आवश्यक तालिकाओं में शामिल हो जाता है, और फॉर्म की एक शर्त प्रत्येक ऐतिहासिक तालिका पर लगाई जाती है: WHERE Input_Date BETWEEN Current_Table.Start_DT and Current_Table.End_DT । अधिकांश प्रणालियों में इस तरह का एसक्यूएल जो मैंने कभी देखा है, बहुत जल्दी काम करता है, क्योंकि रिकॉर्ड बहुत ही चयनात्मक स्थिति से फ़िल्टर किए जाते हैं, और फिर जल्दी से कनेक्ट होते हैं।
परिणामों को पहले से ही खिड़की में परिवर्तन को उजागर करने के लिए तंत्र के अनुसार रखा गया है, क्योंकि शोकेस एससीडी टाइप 2 या एससीडी टाइप 6 का सार है, इसलिए आपको यह जांचने की आवश्यकता है कि परिणामी रिकॉर्ड को संशोधित किया गया है या नहीं।

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

विकल्प तीन - डेटा मॉडल एक (या एक सीमित सेट) कुंजी के आसपास "स्नोफ्लेक" का प्रतिनिधित्व करता है, और यहां तक ​​कि बहुत अधिक डेटा भी हो सकता है

यह विकल्प डेटा वेयरहाउस के लिए विशिष्ट होता है, जिसमें तीसरा सामान्य रूप के करीब मॉडल होता है।
मॉडल का एक योजनाबद्ध संस्करण कुछ इस तरह दिखता है:



इस तरह की योजना के साथ, गणना प्रक्रिया को अनुकूलित करना और तिथियों के पूरे सेट से चयन करना संभव है, जिसमें कोई वास्तविक परिवर्तन थे। कैसे? हां, यह बहुत सरल है - सभी तालिकाओं से Key_id और Start_dt के अद्वितीय संयोजनों का चयन करें। यानी UN_ (ध्यान दें, UNION ALL नहीं) Key_id, Start_dt संयोजनों से। UNION ALL (या DISTINCTs में से सभी को मिलाना) से UNION या DISTINCT का चयन करना, उपयोग किए गए DBMS और डेवलपर के धर्म पर निर्भर करता है।
इस तरह के अनुरोध के परिणामस्वरूप, हमें कुंजियों और तिथियों का एक सेट मिलता है, जब इन चाबियों के साथ कुछ हुआ था। इसके अलावा, प्राप्त सेट समानता की स्थिति के तहत तालिकाओं से जुड़ा हो सकता है Key_id और किसी विशेष तालिका के रिकॉर्डिंग रिकॉर्ड अंतराल में प्राप्त Start_dt की घटना। कई लोग अनुमान लगाएंगे कि इस नमूने में बिल्कुल सभी चाबियाँ मौजूद होंगी, क्योंकि वे कम से कम एक बार भंडार में गिर गए थे। लेकिन ऐसा विकल्प अभी भी प्रदर्शन में फायदेमंद हो सकता है यदि भौतिक डेटा मॉडल विशिष्ट Key_id के लिए क्वेरी प्रदर्शन प्रदान करने की अनुमति देता है। और उन मामलों में जब Key_id का एक महत्वपूर्ण सबसेट होता है जो अक्सर राज्य बदलते हैं।

वर्णित विकल्प को दो तरीकों से लागू किया जा सकता है (डीबीएमएस और डेटा की मात्रा पर निर्भर करता है)। आप इसे विराम के माध्यम से देखने के माध्यम से कर सकते हैं (Oracle के लिए, आप यह भी कर सकते हैं - प्रक्रिया को गति देने के लिए भौतिक रूप से) या एक प्रक्रिया और एक मध्यवर्ती तालिका के माध्यम से दो चरणों में। एक मध्यवर्ती तालिका, वास्तव में, एक बड़ी (लौकिक) एक हो सकती है और कई स्टोरफ्रंट के लिए उपयोग की जा सकती है यदि कुंजी संयोजन डेटा प्रकारों से मेल खाते हैं। यानी प्रक्रिया के पहले चरण में, एक चयन किया जाता है, और इसके परिणाम एक तालिका में संग्रहीत किए जाते हैं। दूसरे चरण में, चरण 1 से तालिका विस्तृत डेटा के साथ जुड़ जाती है। चरण १.५ तालिका पर आँकड़ों का संग्रह हो सकता है, यदि यह इस विशेष मामले में उचित है।

कई और विकल्प हो सकते हैं, क्योंकि डेवलपर्स और ग्राहकों का जीवन और कल्पना बहुत अधिक समृद्ध है, लेकिन मुझे उम्मीद है कि कम से कम इन वर्णित विकल्पों से किसी को समय बचाने में मदद मिलेगी या इससे भी अधिक इष्टतम समाधान बनाने का आधार होगा।