में पिछला लेख , हमने विंडो फ़ंक्शंस के बारे में बात की - जो विभिन्न विश्लेषणात्मक समस्याओं को हल करने के लिए एक महान उपकरण है; वे आपको जटिल प्रश्न लिखने की आवश्यकता के बिना उन्नत विश्लेषण और डेटा हेरफेर जैसी सुविधाओं तक पहुंच प्रदान कर सकते हैं। इससे पहले कि आप पढ़ना जारी रखें, मैं पहले भाग से शुरुआत करने की सलाह दूंगा क्योंकि यह एसक्यूएल में विंडो फ़ंक्शन कैसे काम करता है इसके मूल विचार को समझने में मदद करेगा। तो अब, जब आप बुनियादी बातों से परिचित हो गए हैं, तो आइए अधिक उन्नत 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 मुझे लगता है कि हमें यहाँ जो कुछ हुआ उस पर करीब से नज़र डालने की ज़रूरत है: सबसे पहले, अब अवरोही क्रम में क्रमबद्ध किया गया है। कर्मचारी_आईडी को विंडो फ़ंक्शन के अनुप्रयोग से उत्पन्न कॉलम में, अब एक संचयी योग है। मेरा मानना है कि आप संचयी योग से परिचित हैं। इसका सार सरल है - संचयी योग या चालू कुल का अर्थ है "अब तक कितना।" संचयी योग की परिभाषा किसी दिए गए अनुक्रम का योग है जो अधिक परिवर्धन के साथ बढ़ रहा है या बड़ा हो रहा है। हमारे उदाहरण में यह है: उच्चतम मान वाले कर्मचारी के लिए, वेतन 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 आइए प्रत्येक रैंकिंग विंडो फ़ंक्शन पर नज़र डालें: विंडो फ़ंक्शन वेतन के अवरोही क्रम में पंक्तियों को रैंक करता है और पंक्ति संख्याएँ निर्दिष्ट करता है। ध्यान दें कि एनी और टोनी का वेतन समान है, लेकिन उन्हें अलग-अलग नंबर दिए गए हैं। row_number() ओवर (वेतन विवरण के आधार पर क्रम) विंडो फ़ंक्शन ) वेतन के घटते क्रम में रैंक प्रदान करता है। यह समान मानों के लिए समान रैंक प्रदान करता है, लेकिन अगले मान को एक नई पंक्ति संख्या मिलती है। रैंक() ओवर (वेतन विवरण के आधार पर क्रम विंडो फ़ंक्शन वेतन के घटते क्रम में रैंक प्रदान करता है। यह समान मानों के लिए समान रैंक प्रदान करता है। सघन_रैंक() ओवर (वेतन विवरण के अनुसार क्रम) विंडो फ़ंक्शन वर्तमान पंक्ति की सापेक्ष (प्रतिशत) रैंक है, जिसकी गणना सूत्र द्वारा की जाती है: (रैंक - 1) / (विभाजन में पंक्तियों की कुल संख्या - 1)। प्रतिशत_रैंक() ओवर (वेतन विवरण के आधार पर क्रम) विंडो फ़ंक्शन पंक्तियों की संख्या को 5 बराबर भागों में विभाजित करता है और प्रत्येक भाग को एक संख्या निर्दिष्ट करता है। भागों की संख्या फ़ंक्शन के अंदर निर्दिष्ट है। ntile(5) ओवर (वेतन विवरण के अनुसार क्रम) ntile(5) नायब! समग्र कार्यों के विपरीत, उदाहरण के लिए, योग (वेतन), रैंकिंग फ़ंक्शन, उदाहरण के लिए, row_number(), अंदर एक कॉलम न लें। हालाँकि, 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 फ़ंक्शन और अनुभाग (विभाग) के भीतर पहला और अंतिम वेतन मान प्रदर्शित करते हैं। फर्स्ट_वैल्यू (वेतन) ओवर (विभाग द्वारा विभाजन) लास्ट_वैल्यू (वेतन) ओवर (विभाग द्वारा विभाजन) इसके बदले में, फ़ंक्शन अनुभाग (विभाग) के भीतर दूसरा वेतन मान दिखाता है। कृपया ध्यान दें कि में, एक अतिरिक्त तर्क निर्दिष्ट किया गया है - अनुभाग के भीतर पंक्ति संख्या। हमारे मामले में, पंक्ति संख्या 2 है, इसलिए फ़ंक्शन दूसरा वेतन मान प्रदर्शित करता है। nth_value(वेतन, 2) ओवर(विभाग द्वारा विभाजन) nth_value() उपरोक्त के अलावा, और फ़ंक्शन भी हैं। फ़ंक्शन का उपयोग वर्तमान पंक्ति से पहले वाली पंक्ति से मान प्राप्त करने के लिए किया जाता है। लीड() फ़ंक्शन का उपयोग उस पंक्ति से मान प्राप्त करने के लिए किया जाता है जो वर्तमान पंक्ति के बाद आती है। लैग() लीड() लैग() 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 में विंडो फ़ंक्शंस की क्षमताओं को बेहतर ढंग से समझने में मदद करेगा और आपको नियमित कार्यों में अधिक आत्मविश्वासी और तेज़ बनाएगा।
