SQL विंडो फ़ंक्शंस को समझने के लिए एक शुरुआती मार्गदर्शिका - भाग 2

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

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

सामग्री अवलोकन

• Cumulative Sum
• Ranking Window Functions
• Use cases
• Offset window functions
• Key Takeaways

संचयी योग

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

आइए कर्मचारी आईडी, कर्मचारी का नाम, विभाग, वेतन और सभी वेतनों का योग पूछें:

 select employee_id, employee_name, department, salary, sum(salary) over() from salary 

अब, हम पैरामीटर द्वारा ऑर्डर को ओवर() एक्सप्रेशन में जोड़ देंगे:

 select employee_id, employee_name, department, salary, sum(salary) over(order by employee_id desc) from salary 

मुझे लगता है कि हमें यहाँ जो कुछ हुआ उस पर करीब से नज़र डालने की ज़रूरत है:

1. सबसे पहले, कर्मचारी_आईडी को अब अवरोही क्रम में क्रमबद्ध किया गया है।

   
   

2. विंडो फ़ंक्शन के अनुप्रयोग से उत्पन्न कॉलम में, अब एक संचयी योग है।

   
   

मेरा मानना है कि आप संचयी योग से परिचित हैं। इसका सार सरल है - संचयी योग या चालू कुल का अर्थ है "अब तक कितना।" संचयी योग की परिभाषा किसी दिए गए अनुक्रम का योग है जो अधिक परिवर्धन के साथ बढ़ रहा है या बड़ा हो रहा है।

हमारे उदाहरण में यह है: उच्चतम कर्मचारी_आईडी मान वाले कर्मचारी के लिए, वेतन 3700 है, और संचयी योग भी 3700 है। दूसरे कर्मचारी का वेतन 1500 है, और संचयी योग 5200 है। तीसरा कर्मचारी 2900 के वेतन के साथ, संचयी योग 8100 है, इत्यादि।

ओवर() एक्सप्रेशन में पैरामीटर द्वारा ऑर्डर ऑर्डर निर्दिष्ट करता है। विंडो फ़ंक्शंस को एकत्रित करने के मामले में, यह संचयी कुल के लिए क्रम निर्धारित करता है।

ओवर() एक्सप्रेशन में, पार्टीशन बाय और ऑर्डर बाय एट्रिब्यूट्स दोनों को निर्दिष्ट किया जा सकता है।

 select employee_id, employee_name, department, salary, sum(salary) over(partition by department order by employee_id desc) from salary 

इस मामले में, संचयी कुल की गणना अनुभागों द्वारा की जाएगी।

संचयी योग पर चर्चा करने के बाद, हमें यह कहना होगा कि शायद यह संचयी योग का एकमात्र प्रकार है जिसका अक्सर उपयोग किया जाता है। इसके विपरीत, संचयी औसत और संचयी गणना का उपयोग शायद ही कभी किया जाता है।

फिर भी, हम संचयी औसत गणना का एक उदाहरण देंगे - यह हमें एक निश्चित बिंदु तक मूल्यों की श्रृंखला का औसत बताता है:

 select employee_id, employee_name, department, salary, avg(salary) over(order by employee_id desc) from salary 

विंडो फ़ंक्शंस की रैंकिंग

हम मानों के एक सेट के भीतर किसी मान की स्थिति निर्धारित करने के लिए रैंकिंग विंडो फ़ंक्शंस का उपयोग करते हैं। OVER क्लॉज़ के भीतर ORDER BY अभिव्यक्ति रैंकिंग के लिए आधार तय करती है, प्रत्येक मान को उसके निर्दिष्ट विभाजन के भीतर एक रैंक सौंपी जाती है। जब पंक्तियाँ रैंकिंग मानदंड के लिए समान मान साझा करती हैं, तो उन्हें समान रैंक दी जाती है।

यह देखने के लिए कि रैंकिंग विंडो कैसे काम करती है, आइए वेतन तालिका से निम्नलिखित कॉलम का अनुरोध करें: कर्मचारी आईडी, कर्मचारी का नाम, विभाग और वेतन:

 select employee_id, employee_name, department, salary from salary 

अब, हम विंडो फ़ंक्शन row_number() ओवर() के साथ एक और कॉलम जोड़ते हैं:

 select employee_id, employee_name, department, salary, row_number() over() from salary 

विंडो फ़ंक्शन row_number() ओवर() ने पंक्तियों का क्रम बदले बिना उन्हें संख्याएँ निर्दिष्ट की हैं। अब तक, यह हमारे लिए बहुत अधिक मूल्य नहीं लाता है, क्या ऐसा होता है?

लेकिन क्या होगा यदि हम पंक्तियों को वेतन के घटते क्रम में क्रमांकित करना चाहें? इसे प्राप्त करने के लिए, हमें सॉर्टिंग ऑर्डर निर्दिष्ट करने की आवश्यकता है, दूसरे शब्दों में, पैरामीटर द्वारा ऑर्डर को ओवर() अभिव्यक्ति में पास करना होगा।

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

हम तुलना के लिए शेष रैंकिंग फ़ंक्शन को क्वेरी में जोड़ देंगे:

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc), rank() over(order by salary desc), dense_rank() over(order by salary desc), percent_rank() over(order by salary desc), ntile(5) over(order by salary desc) from salary

आइए प्रत्येक रैंकिंग विंडो फ़ंक्शन पर नज़र डालें:

1. विंडो फ़ंक्शन row_number() ओवर (वेतन विवरण के आधार पर क्रम) वेतन के अवरोही क्रम में पंक्तियों को रैंक करता है और पंक्ति संख्याएँ निर्दिष्ट करता है। ध्यान दें कि एनी और टोनी का वेतन समान है, लेकिन उन्हें अलग-अलग नंबर दिए गए हैं।

   
   

2. विंडो फ़ंक्शन रैंक() ओवर (वेतन विवरण के आधार पर क्रम ) वेतन के घटते क्रम में रैंक प्रदान करता है। यह समान मानों के लिए समान रैंक प्रदान करता है, लेकिन अगले मान को एक नई पंक्ति संख्या मिलती है।

   
   

3. विंडो फ़ंक्शन सघन_रैंक() ओवर (वेतन विवरण के अनुसार क्रम) वेतन के घटते क्रम में रैंक प्रदान करता है। यह समान मानों के लिए समान रैंक प्रदान करता है।

   
   

4. विंडो फ़ंक्शन प्रतिशत_रैंक() ओवर (वेतन विवरण के आधार पर क्रम) वर्तमान पंक्ति की सापेक्ष (प्रतिशत) रैंक है, जिसकी गणना सूत्र द्वारा की जाती है: (रैंक - 1) / (विभाजन में पंक्तियों की कुल संख्या - 1)।

   
   

5. विंडो फ़ंक्शन ntile(5) ओवर (वेतन विवरण के अनुसार क्रम) पंक्तियों की संख्या को 5 बराबर भागों में विभाजित करता है और प्रत्येक भाग को एक संख्या निर्दिष्ट करता है। भागों की संख्या ntile(5) फ़ंक्शन के अंदर निर्दिष्ट है।

   
   

बक्सों का इस्तेमाल करें

रैंकिंग विंडो फ़ंक्शंस का उपयोग करके व्यावहारिक कार्यों का पता लगाने का समय आ गया है। हम कर्मचारी आईडी, कर्मचारी का नाम, विभाग और वेतन प्रदर्शित करेंगे, और वेतन के घटते क्रम में पंक्ति संख्याएँ निर्दिष्ट करेंगे।

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

कभी-कभी, आपको विभागों (अनुभागों) के भीतर वेतन के घटते क्रम में पंक्तियों को क्रमांकित करने की आवश्यकता हो सकती है। यह ओवर() अभिव्यक्ति में विशेषता द्वारा विभाजन जोड़कर किया जा सकता है:

 select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) from salary 

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

 select * from ( select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) as rn from salary ) as t2 where rn = 1 

और एक और उदाहरण, यदि हमें प्रत्येक शहर में उच्चतम वेतन वाले तीन कर्मचारियों को प्रदर्शित करने की आवश्यकता है, तो हम निम्नलिखित कार्य करेंगे:

 select * from ( select employee_id, employee_name, city, salary, row_number() over(partition by city order by salary desc) as rn from salary ) as t2 where rn <= 3 

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

ऑफसेट विंडो फ़ंक्शंस

ये फ़ंक्शन आपको वर्तमान पंक्ति से उनकी दूरी के आधार पर अन्य पंक्तियों से डेटा वापस करने की अनुमति देते हैं। इसे और अधिक विज़ुअल बनाने के लिए, आइए पहले_वैल्यू(), लास्ट_वैल्यू(), और nth_वैल्यू() फ़ंक्शंस से गुज़रें।

 select t1.*, first_value(salary)over(partition by department), last_value(salary)over(partition by department), nth_value(salary,2)over(partition by department) from salary as t1 order by department 

 select t1.*, first_value(salary)over(partition by department order by salary decs), last_value(salary)over(partition by department order by salary decs), nth_value(salary,2)over(partition by department order by salary decs) from salary as t1 order by department

फ़ंक्शन फर्स्ट_वैल्यू (वेतन) ओवर (विभाग द्वारा विभाजन) और लास्ट_वैल्यू (वेतन) ओवर (विभाग द्वारा विभाजन) अनुभाग (विभाग) के भीतर पहला और अंतिम वेतन मान प्रदर्शित करते हैं।

इसके बदले में, फ़ंक्शन nth_value(वेतन, 2) ओवर(विभाग द्वारा विभाजन) अनुभाग (विभाग) के भीतर दूसरा वेतन मान दिखाता है। कृपया ध्यान दें कि nth_value() में, एक अतिरिक्त तर्क निर्दिष्ट किया गया है - अनुभाग के भीतर पंक्ति संख्या। हमारे मामले में, पंक्ति संख्या 2 है, इसलिए फ़ंक्शन दूसरा वेतन मान प्रदर्शित करता है।

उपरोक्त के अलावा, लैग() और लीड() फ़ंक्शन भी हैं। लैग() फ़ंक्शन का उपयोग वर्तमान पंक्ति से पहले वाली पंक्ति से मान प्राप्त करने के लिए किया जाता है। लीड() फ़ंक्शन का उपयोग उस पंक्ति से मान प्राप्त करने के लिए किया जाता है जो वर्तमान पंक्ति के बाद आती है।

 select t1.*, lag(salary)over(order by salary), lead(salary)over(order by salary) from salary as t1 

जैसा कि आप देख सकते हैं, फ़ंक्शन लैग (वेतन) ओवर (वेतन के अनुसार क्रम) वेतन को एक पंक्ति से नीचे स्थानांतरित कर देता है, और फ़ंक्शन लीड (वेतन) ओवर (वेतन के अनुसार क्रम) वेतन को एक पंक्ति से ऊपर स्थानांतरित कर देता है। हालाँकि ये फ़ंक्शन काफी समान हैं, मुझे लैग() का उपयोग करना अधिक सुविधाजनक लगता है।

 select t1.*, lag(salary)over(partition by department order by salary) from salary as t1 order by department 

यहां, लैग() पहले जैसा ही कार्य करता है, लेकिन अब विशेष रूप से अनुभागों (विभागों) के भीतर।

चाबी छीनना

और अंत में, आज हमने जो कवर किया है उसका एक त्वरित अवलोकन:

• संचयी योग एक अनुक्रम के चालू कुल का प्रतिनिधित्व करता है, जो प्रत्येक बाद के जोड़ के साथ जमा होता है।

  
  

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

  
  

• ऑफसेट विंडो फ़ंक्शंस में f irst_value() , Last_value() , और nth_value() शामिल हैं, जो वर्तमान पंक्ति से उनकी दूरी के आधार पर अन्य पंक्तियों से डेटा पुनर्प्राप्ति को सक्षम करते हैं। लैग() और लीड() फ़ंक्शंस के बारे में मत भूलना। लैग() फ़ंक्शन वर्तमान पंक्ति से पहले वाली पंक्ति से मान प्राप्त करने के लिए आसान हो सकता है, जबकि लीड() फ़ंक्शन का उपयोग वर्तमान पंक्ति के बाद वाली पंक्ति से मान प्राप्त करने के लिए किया जाता है।

  
  

मुझसे जुड़ने के लिए धन्यवाद. मुझे आशा है कि यह लेख आपको SQL में विंडो फ़ंक्शंस की क्षमताओं को बेहतर ढंग से समझने में मदद करेगा और आपको नियमित कार्यों में अधिक आत्मविश्वासी और तेज़ बनाएगा।