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

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

द्वारा Yonatan Sali
Yonatan Sali HackerNoon profile picture

Yonatan Sali

@yonatansali

Head of RTB, automation enthusiast, and violinist.

8 मिनट read2024/01/20
Read on Terminal Reader
Read this story in a terminal
Print this story

बहुत लंबा; पढ़ने के लिए

आइए अधिक उन्नत SQL अवधारणाओं का अन्वेषण करें। पहली नज़र में यह थोड़ा जटिल लग सकता है, लेकिन मैं सभी संभावित मामलों के लिए शुरुआती लोगों के लिए उपयुक्त सरल उदाहरण प्रदान करूंगा, जिससे मेरी समझ को समझना आसान हो जाएगा।
featured image - SQL विंडो फ़ंक्शंस को समझने के लिए एक शुरुआती मार्गदर्शिका - भाग 2
Yonatan Sali HackerNoon profile picture
Yonatan Sali

Yonatan Sali

@yonatansali

Head of RTB, automation enthusiast, and violinist.

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


तो अब, जब आप बुनियादी बातों से परिचित हो गए हैं, तो आइए अधिक उन्नत 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 


image


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

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


image


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


  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 



image

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


नायब! यदि दोनों विशेषताएँ ओवर() अभिव्यक्ति में निर्दिष्ट हैं, तो विभाजन हमेशा पहले आता है, उसके बाद क्रम आता है । उदाहरण के लिए: ओवर (कर्मचारी_आईडी द्वारा विभाग के आदेश द्वारा विभाजन)


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


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

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


image


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

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


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

 select employee_id, employee_name, department, salary from salary 


image

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

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


image


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


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

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


image

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

 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) फ़ंक्शन के अंदर निर्दिष्ट है।


नायब! समग्र कार्यों के विपरीत, उदाहरण के लिए, योग (वेतन), रैंकिंग फ़ंक्शन, उदाहरण के लिए, row_number(), अंदर एक कॉलम न लें। हालाँकि, ntile(5) फ़ंक्शन में, भागों की संख्या निर्दिष्ट है।

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

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

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


image


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

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


image

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

 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 


image

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

 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 



image

इस प्रकार के कार्य बहुत आम हैं, खासकर जब आपको किसी विशेषता के आरोही या अवरोही क्रम में अनुभागों (समूहों) के भीतर पंक्तियों की एक विशिष्ट संख्या प्रदर्शित करने की आवश्यकता होती है। व्यवहार में, मैं लगातार विंडो फ़ंक्शन 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 


image

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

 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 


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

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

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


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


चाबी छीनना

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


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


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


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


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

L O A D I N G
. . . comments & more!

About Author

Yonatan Sali HackerNoon profile picture
Yonatan Sali@yonatansali
Head of RTB, automation enthusiast, and violinist.

लेबल

इस लेख में चित्रित किया गया था...

Read on Terminal Reader
Read this story in a terminal
 Terminal
Read this story w/o Javascript
Read this story w/o Javascript
 Lite
X REMOVE AD