SQL विंडो फ़ंक्शंस और उनकी क्षमताओं को समझने के लिए एक शुरुआती मार्गदर्शिका

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

हालाँकि विंडो फ़ंक्शंस लगभग 20 वर्षों से मौजूद हैं, फिर भी बहुत से SQL डेवलपर्स को अभी भी उन्हें समझ पाना मुश्किल लगता है। यहां तक कि अनुभवी डेवलपर्स के लिए भी स्टैक ओवरफ़्लो से कोड को कॉपी और पेस्ट करना असामान्य नहीं है, बिना यह समझे कि यह वास्तव में क्या करता है। यह लेख मदद के लिए यहाँ है! मैं विंडो फ़ंक्शंस को ऐसे तरीके से समझाऊंगा जो समझने में आसान हो और उदाहरण प्रदान करके आपको दिखाऊंगा कि वे वास्तविक दुनिया में कैसे काम करते हैं।

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

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

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

• विंडो फ़ंक्शंस के साथ प्रयुक्त फ़ंक्शंस के प्रकार
  • समग्र कार्य
  • रैंकिंग कार्य
  • मूल्य कार्य
• समग्र विंडो फ़ंक्शंस
• चाबी छीनना

विंडो फ़ंक्शंस के साथ प्रयुक्त फ़ंक्शंस के प्रकार

तीन मुख्य प्रकार के फ़ंक्शन हैं जिनमें विंडो फ़ंक्शंस को पंक्तियों के सेट (एक तथाकथित विंडो) पर लागू किया जा सकता है: ये समग्र, रैंकिंग और मूल्य फ़ंक्शंस हैं। नीचे दी गई छवि में, आप प्रत्येक श्रेणी में आने वाले विभिन्न कार्यों के नाम देख सकते हैं।

समग्र कार्य

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

• SUM: कॉलम में सभी मान जोड़ता है

• COUNT: NULL मानों को छोड़कर, कॉलम में मानों की संख्या की गणना करता है

• AVG: कॉलम में औसत मान ढूँढता है

• MAX: कॉलम में उच्चतम मान की पहचान करता है

• न्यूनतम: कॉलम में न्यूनतम मान की पहचान करता है

  
  

रैंकिंग कार्य

इनका उपयोग विभाजन में प्रत्येक पंक्ति को रैंक या क्रम देने के लिए किया जाता है। यह विशिष्ट मानदंडों का मूल्यांकन करके किया जाता है, जैसे अनुक्रमिक संख्याएं निर्दिष्ट करना या विशिष्ट मूल्यों पर रैंकिंग को आधार बनाना।

• ROW_NUMBER: विभाजन में प्रत्येक नए रिकॉर्ड के लिए एक अनुक्रमिक रैंक संख्या निर्दिष्ट करता है
• रैंक: परिणाम सेट में प्रत्येक पंक्ति के लिए रैंक निर्दिष्ट करता है। इस मामले में, यदि सिस्टम समान मानों का पता लगाता है, तो यह उन्हें समान रैंक प्रदान करेगा और अगले मान को छोड़ देगा।
• DENSE_RANK: परिणाम सेट के विभाजन के भीतर प्रत्येक पंक्ति को एक रैंक प्रदान करता है। RANK फ़ंक्शन के विपरीत, फ़ंक्शन किसी भी बाद के मान को छोड़े बिना समान मानों के लिए रैंक लौटाता है।
• NTILE: हमें यह निर्धारित करने की अनुमति देता है कि वर्तमान लाइन किस समूह से संबंधित है। समूहों की संख्या कोष्ठक में दी गई है।
  

मूल्य कार्य

ये किसी समूह में विभिन्न पंक्तियों के बीच मानों की तुलना करना आसान बनाते हैं और आपको उस समूह में पहले या अंतिम मान के साथ मानों की तुलना करने की सुविधा भी देते हैं। इसका मतलब है कि आप किसी विंडो में विभिन्न पंक्तियों में आसानी से जा सकते हैं और विंडो के आरंभ या अंत में मान देख सकते हैं।

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

विंडो फ़ंक्शंस के साथ आरंभ करने के लिए, आइए एक काल्पनिक 'वेतन' तालिका बनाएं और इसे डेटा से भरें।

तालिका निर्माण:

 create table salary ( employee_id smallint, employee_name varchar(10), department varchar(20), city varchar(20), salary real )

तालिका भरना:

 insert into salary values ( 1 ,'Tony' ,'R&D', 'New York', 3000); insert into salary values ( 2 ,'James' ,'Project management', 'London', 4000); insert into salary values ( 3 ,'Dina' ,'Engineering', 'Tokyo', 5000); insert into salary values ( 4 ,'Annie' ,'Security', 'London', 3000); insert into salary values ( 5 ,'Tom' ,'R&D', 'New York', 3500); insert into salary values ( 6 ,'Stan' ,'Project management', 'New York', 4200); insert into salary values ( 7 ,'Jessa' ,'Sales', 'London', 5300); insert into salary values ( 8 ,'Ronnie' ,'R&D', 'Tokyo', 2900); insert into salary values ( 9 ,'Andrew' ,'Engineering', 'New York', 1500); insert into salary values (10,'Dean' ,'Sales', 'Tokyo', 3700)

आइए देखें कि क्या हमने 'वेतन' तालिका सफलतापूर्वक भर दी है:

 select * from salary 

अगली क्वेरी हमारी तालिका से कर्मचारियों के नाम और वेतन दिखाएगी:

 select employee_name, salary from salary 

वेतन का योग, औसत वेतन, अधिकतम, न्यूनतम और पंक्तियों की संख्या की गणना समग्र कार्यों के कुछ सामान्य उपयोग के मामले हैं:

जब एक समग्र फ़ंक्शन लागू किया जाता है, तो वेतन एकत्रित होते हैं और एक पंक्ति में दिखाए जाते हैं।

लेकिन क्या होगा यदि हम 'वेतन' तालिका से कर्मचारियों के नाम और वेतन और तीसरे कॉलम में सभी वेतनों का योग प्रदर्शित करना चाहते हैं? यह मान सभी पंक्तियों के लिए समान होना चाहिए.

विंडो फ़ंक्शन का उपयोग करने का यह एक शानदार अवसर है!

 select employee_name, salary, sum(salary) over() as sum_salary from salary 

आइए विंडो फ़ंक्शन पर करीब से नज़र डालें जो sum(salary) over() की प्रत्येक पंक्ति में वेतन के योग की गणना करता है।

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

over() एक्सप्रेशन केवल तभी काम करता है जब over() से पहले अनुरोध किए गए फ़ंक्शन के साथ जोड़ा जाता है।

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

जैसा कि मैंने पहले कहा, सभी फ़ंक्शन जिन पर विंडो फ़ंक्शन लागू होते हैं, उन्हें तीन समूहों में विभाजित किया जा सकता है: समग्र, रैंकिंग और मूल्य फ़ंक्शन।

समग्र फ़ंक्शन sum() , count() , avg() , min() , max() over() एक्सप्रेशन के साथ मिलकर समग्र विंडो फ़ंक्शंस का एक समूह बनाते हैं।

इस लेख में, हम इस विशिष्ट प्रकार के विंडो फ़ंक्शंस पर ध्यान केंद्रित करेंगे।

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

उदाहरणों पर वापस जाएँ!

आइए कर्मचारियों के नाम का अनुरोध करें; उनका वेतन; सभी वेतनों का योग; औसत, अधिकतम और न्यूनतम वेतन; कर्मचारियों की संख्या.

 select employee_name, salary, sum(salary) over(), avg(salary) over(), max(salary) over(), min(salary) over(), count(*) over() from salary 

अब जब यह स्पष्ट हो गया है कि विंडो फ़ंक्शंस क्या हैं, तो आइए कुछ मामलों का पता लगाएं जहां वे आपके काम में उपयोगी हो सकते हैं।

 select employee_name, salary, sum(salary)over(), salary/sum(salary)over() as share from salary order by salary/sum(salary)over() desc 

हमने चौथे कॉलम में प्रत्येक वेतन के लिए कुल वेतन बजट के प्रतिशत की गणना की है। जेसा का वेतन वेतन के पूरे बजट का लगभग 15% है।

ध्यान दें कि हमने वह फॉर्मूला भी रखा है जो प्रतिशत salary/sum(salary)over() गणना order by बाद क्रम में करता है। एक विंडो फ़ंक्शन न केवल आउटपुट select में पाया जा सकता है, बल्कि क्रमबद्ध order by में भी पाया जा सकता है।

दूसरा उदाहरण: आइए वेतन की तुलना कंपनी के औसत वेतन से करें।

 select employee_name, salary, avg(salary)over(), salary-avg(salary)over() as diff_salary from salary order by salary-avg(salary)over() 

जैसा कि हम देख सकते हैं, एंड्रयू का वेतन औसत से 2110 कम है, और जेसा का औसत से 1690 अधिक है।

आइए तीन कॉलम का अनुरोध करें: कर्मचारी का नाम, विभाग और वेतन। साथ ही, हम उन्हें विभाग के अनुसार क्रमबद्ध करेंगे।

 select employee_name, department, salary from salary order by department 

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

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

लेकिन क्या होगा यदि हम सभी वेतनों के योग का नहीं, बल्कि प्रत्येक विभाग के वेतन के योग का अनुरोध करना चाहते हैं, जैसा कि अंतिम कॉलम में दिखाया गया है:

हम इसे over() अभिव्यक्ति में पैरामीटर partition by जोड़कर कर सकते हैं:

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

Partition by हमें विंडो फ़ंक्शन को सभी पंक्तियों (संपूर्ण विंडो) पर नहीं, बल्कि कॉलम अनुभागों पर लागू करने की अनुमति देता है।

क्या यह एक साधारण समूहीकरण जैसा नहीं लगता? प्रत्येक विभाग के लिए वेतन की राशि की गणना करने के लिए, हम विभागों द्वारा एक समूह बनाएंगे (विंडो फ़ंक्शंस के स्लैंग में अनुभाग) और राशि की गणना करेंगे:

 select department, sum(salary) from salary group by department 

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

आइए विंडो फ़ंक्शंस पर वापस जाएं:

 select employee_name, department, salary, sum(salary)over(partition by department), salary/sum(salary)over(partition by department) as shape from salary order by department 

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

चाबी छीनना

संक्षेप में दुहराना:

• एक विंडो फ़ंक्शन पंक्तियों के एक सेट में गणना करता है जो किसी तरह वर्तमान पंक्ति से संबंधित होते हैं,

• मुख्य प्रकार के फ़ंक्शन जिन पर विंडो फ़ंक्शंस लागू होते हैं वे हैं समग्र, रैंकिंग और मूल्य फ़ंक्शंस,

• विंडो फ़ंक्शन का उपयोग करने के लिए, आपको over() क्लॉज को लागू करने की आवश्यकता है जो क्वेरी परिणाम सेट के भीतर एक विंडो (पंक्तियों का एक सेट) को परिभाषित करता है। विंडो फ़ंक्शन तब विंडो में प्रत्येक पंक्ति के लिए एक मान की गणना करता है,

• उस कॉलम को निर्दिष्ट करने के लिए जिसके लिए आप एकत्रीकरण करना चाहते हैं, आपको over() क्लॉज में खंड partition by जोड़ना होगा। Partition by कुछ हद तक समूहीकरण के समान है, लेकिन प्रति समूह एक पंक्ति के बजाय सभी पंक्तियों को कुल फ़ंक्शन के साथ लागू करता है।

  
  

अभी के लिए बस इतना ही! अगले कुछ लेखों में, मैं शुरुआती लोगों के लिए उपयुक्त सरल उदाहरणों के साथ अधिक उन्नत SQL अवधारणाओं का पता लगाने जा रहा हूँ, इसलिए बने रहें!