Advanced Data Analytics Hinglish
SQL Advanced Techniques
विंडो फ़ंक्शंस के साथ डेटा विश्लेषण
SQL में, विंडो फ़ंक्शंस डेटा की पंक्तियों के एक सेट पर गणना करते हैं जो किसी तरह से वर्तमान पंक्ति से संबंधित होते हैं। इन्हें एग्रीगेट फ़ंक्शंस की तरह सोचें, लेकिन ये पंक्तियों को एक समूह में संक्षिप्त नहीं करते हैं। इसके बजाय, प्रत्येक पंक्ति अपनी पहचान बनाए रखती है।
यह जादू OVER() क्लॉज़ के माध्यम से होता है, जो उस 'विंडो' को परिभाषित करता है जिस पर फ़ंक्शन काम करता है। आइए कुछ सबसे उपयोगी विंडो फ़ंक्शंस देखें।
रैंकिंग और अनुक्रमण
अक्सर आपको डेटा को रैंक करने की आवश्यकता होती है - जैसे कि बिक्री के हिसाब से टॉप कर्मचारियों को ढूंढना या स्कोर के आधार पर छात्रों को रैंक करना। यहाँ तीन फ़ंक्शंस काम आते हैं: ROW_NUMBER(), RANK(), और DENSE_RANK()। वे समान लगते हैं, लेकिन उनमें महत्वपूर्ण अंतर हैं।
SELECT
employee_name,
salary,
ROW_NUMBER() OVER(ORDER BY salary DESC) AS row_num,
RANK() OVER(ORDER BY salary DESC) AS rank_val,
DENSE_RANK() OVER(ORDER BY salary DESC) AS dense_rank_val
FROM employees;
आइए कल्पना करें कि हमारे पास दो कर्मचारी हैं जिनका वेतन समान है। यहाँ बताया गया है कि प्रत्येक फ़ंक्शन उन्हें कैसे हैंडल करेगा:
| employee_name | salary | row_num | rank_val | dense_rank_val |
|---|---|---|---|---|
| प्रिया | 90000 | 1 | 1 | 1 |
| रोहन | 85000 | 2 | 2 | 2 |
| समीर | 85000 | 3 | 2 | 2 |
| अंजलि | 78000 | 4 | 4 | 3 |
ध्यान दें:
ROW_NUMBER()प्रत्येक पंक्ति को एक अद्वितीय, अनुक्रमिक संख्या देता है, भले ही मान समान हों।RANK()समान मानों को समान रैंक देता है, लेकिन फिर अगले रैंक के लिए एक गैप छोड़ देता है (रैंक 2 के बाद रैंक 4 आता है)।DENSE_RANK()भी समान मानों को समान रैंक देता है, लेकिन यह कोई गैप नहीं छोड़ता (रैंक 2 के बाद रैंक 3 आता है)।
अब, आइए देखें। यह समय के साथ ट्रेंड्स को ट्रैक करने के लिए एक शक्तिशाली तकनीक है। LEAD() और LAG() फ़ंक्शंस इसे सरल बनाते हैं। LAG() पिछली पंक्ति से डेटा प्राप्त करता है, जबकि LEAD() अगली पंक्ति से डेटा प्राप्त करता है। यह मासिक बिक्री या वेबसाइट ट्रैफ़िक की तुलना करने के लिए एकदम सही है।
SELECT
sale_month,
monthly_sales,
LAG(monthly_sales, 1) OVER(ORDER BY sale_month) AS previous_month_sales
FROM monthly_reports;
जटिल क्वेरीज़ को सरल बनाना
जैसे-जैसे आपके प्रश्न जटिल होते जाते हैं, वे जल्दी से नेस्टेड का एक उलझा हुआ जाल बन सकते हैं। उन्हें पढ़ना और डीबग करना मुश्किल होता है। सौभाग्य से, एक बेहतर तरीका है: कॉमन टेबल एक्सप्रेशंस (CTEs)।
एक CTE एक अस्थायी, नामित रिजल्ट सेट है जिसे आप एक SELECT, INSERT, UPDATE, या DELETE स्टेटमेंट के भीतर संदर्भित कर सकते हैं। आप WITH क्लॉज़ का उपयोग करके एक CTE को परिभाषित करते हैं, जो आपकी मुख्य क्वेरी से पहले आता है। यह आपकी लंबी क्वेरी को तार्किक, पठनीय चरणों में तोड़ने जैसा है।
CTEs न केवल पठनीयता में सुधार करते हैं, बल्कि वे रिकर्सिव क्वेरीज़ को भी सक्षम करते हैं - कुछ ऐसा जो सबक्वेरीज़ नहीं कर सकतीं।
पदानुक्रमित डेटा, जैसे कि एक कंपनी का संगठनात्मक चार्ट, को नेविगेट करने के लिए रिकर्सिव CTEs आवश्यक हैं। आप एक प्रारंभिक बिंदु (जैसे सीईओ) से शुरू करते हैं और फिर बार-बार कर्मचारियों और उनके प्रबंधकों के बीच संबंधों को नेविगेट करते हैं जब तक कि आप पूरे पदानुक्रम का पता नहीं लगा लेते।
उन्नत जॉइन्स
आपने पहले से ही INNER और LEFT जॉइन्स का उपयोग किया होगा। अब दो और शक्तिशाली प्रकारों पर विचार करें: SELF JOIN और CROSS JOIN।
Self Join
other
एक सेल्फ जॉइन एक नियमित जॉइन है, लेकिन इसमें एक टेबल को खुद से जोड़ा जाता है। यह तब उपयोगी होता है जब किसी टेबल में पंक्तियों के बीच पदानुक्रमित या तुलनात्मक संबंध होते हैं।
कल्पना कीजिए कि आपके पास एक employees टेबल है जिसमें employee_id, employee_name, और manager_id कॉलम हैं। manager_id उसी टेबल में किसी अन्य कर्मचारी के employee_id को संदर्भित करता है। एक सेल्फ जॉइन आपको प्रत्येक कर्मचारी को उनके प्रबंधक के साथ एक ही पंक्ति में सूचीबद्ध करने की अनुमति देता है।
दूसरी ओर, CROSS JOIN एक कार्टेशियन उत्पाद लौटाता है - यह पहली टेबल की प्रत्येक पंक्ति को दूसरी टेबल की प्रत्येक पंक्ति के साथ जोड़ता है। यदि टेबल A में 100 पंक्तियाँ हैं और टेबल B में 100 पंक्तियाँ हैं, तो एक CROSS JOIN 10,000 पंक्तियाँ लौटाएगा। इसे सावधानी से उपयोग करें, क्योंकि यह अनजाने में बहुत बड़े रिजल्ट सेट बना सकता है। यह सभी संभावित संयोजनों, जैसे कि सभी शर्ट और पैंट संयोजनों को उत्पन्न करने के लिए उपयोगी है।
क्वेरी परफॉर्मेंस को समझना
एक क्वेरी लिखना एक बात है; एक कुशल क्वेरी लिखना दूसरी बात है। जैसे-जैसे डेटासेट बड़े होते जाते हैं, खराब लिखी गई क्वेरीज़ धीमी हो सकती हैं। यहीं पर क्वेरी परफॉर्मेंस ट्यूनिंग आती है।
पहला कदम यह समझना है कि आपका डेटाबेस आपकी क्वेरी को कैसे निष्पादित कर रहा है। अधिकांश SQL सिस्टम EXPLAIN नामक एक कमांड प्रदान करते हैं। जब आप अपनी SELECT स्टेटमेंट से पहले EXPLAIN लगाते हैं, तो डेटाबेस क्वेरी नहीं चलाता है। इसके बजाय, यह आपको दिखाता है - डेटा को पुनः प्राप्त करने के लिए उठाए जाने वाले कदमों का खाका।
इस योजना का विश्लेषण करके, आप अक्षमताओं की पहचान कर सकते हैं। उदाहरण के लिए, यदि आप देखते हैं कि डेटाबेस एक बड़े टेबल पर 'फुल टेबल स्कैन' कर रहा है, तो यह एक संकेत हो सकता है कि आपको अक्सर फ़िल्टर किए जाने वाले कॉलम पर एक इंडेक्स जोड़ने की आवश्यकता है। एक इंडेक्स एक किताब के पीछे के इंडेक्स की तरह काम करता है, जो डेटाबेस को हर पेज को पलटे बिना सीधे जानकारी खोजने की अनुमति देता है।
SQL में एक कॉमन टेबल एक्सप्रेशन (CTE) को परिभाषित करने के लिए किस कीवर्ड का उपयोग किया जाता है?
मान लीजिए कि आपके पास बिक्री डेटा है। यदि आप प्रत्येक बिक्री पंक्ति की तुलना पिछली महीने की बिक्री से करना चाहते हैं, तो कौन सा विंडो फ़ंक्शन सबसे उपयुक्त होगा?