SQL में Aggregate Functions

किसी दिए गए डेटा सेट से रिपोर्ट बनाना, यदि आप डेटा के साथ काम करते हैं, तो एक आवश्यक कौशल है। क्योंकि अंततः, आप अपने पास उपलब्ध डेटा का उपयोग करके महत्वपूर्ण व्यावसायिक प्रश्नों का उत्तर देने में सक्षम होना चाहते हैं। कई बार ये उत्तर रिपोर्ट चार्ट के रूप में प्रस्तुत किए जाते हैं। लेकिन कभी-कभी तालिकाओं के रूप में रिपोर्ट की भी आवश्यकता होती है। दोनों ही मामलों में, आपको सरल गणनाओं का उपयोग करके डेटा का सार प्रस्तुत करने की ज़रूरत पड़ सकती है। SQL में, आप aggregate functions का उपयोग करके डेटा का सार/एग्रीगेशन कर सकते हैं। इन फ़ंक्शनों के साथ, आप ऐसे प्रश्नों का उत्तर दे पाएंगे जैसे:

• some_column_from_the_table का अधिकतम मान क्या है? या
• another_column_from_the_table के संदर्भ में some_column_from_the_table के न्यूनतम मान क्या हैं?

और भी बहुत कुछ।

आइए कुछ डेटा एग्रीगेशन करना शुरू करें।

नोट: इस ट्यूटोरियल के साथ चलने के लिए, आपको PostgreSQL (जिसे आप RDBMS के रूप में उपयोग करेंगे) में बेसिक क्वेरी लिखना आना चाहिए। यह ट्यूटोरियल एक अच्छा रिफ्रेशर हो सकता है।

डेटाबेस सेट करना

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

यदि आप बैकअप रिस्टोर करने में सक्षम रहे, तो आपको डेटाबेस के भीतर international_debt नाम की एक तालिका दिखनी चाहिए (यदि आपके पास पहले से डेटाबेस नहीं है, तो आपको एक बनाना होगा)। आइए जल्दी से तालिका की शुरुआती कुछ पंक्तियाँ देखें (एक साधारण सेलेक्ट क्वेरी इसमें मदद करेगी):

यह तालिका दुनिया भर के विभिन्न देशों के लिए चालू वर्ष में अलग-अलग श्रेणियों में ऋण संबंधी आँकड़ों की जानकारी रखती है (कॉलम indicator_name और indicator_code देखें)। debt कॉलम किसी विशेष श्रेणी में किसी देश के पास (USD में) ऋण की राशि दिखाता है। यह डेटा अर्थशास्त्र के डोमेन से संबंधित है और अक्सर विभिन्न देशों की आर्थिक स्थिति का विश्लेषण करने के लिए उपयोग किया जाता है। यह डेटा World Bank से लिया गया है।

अब जब आपने सफलतापूर्वक डेटाबेस सेट कर लिया है, तो डेटा के बारे में और जानने के लिए कुछ सरल क्वेरियाँ चलाएँ। pgAdmin टूल खोलें और शुरू करें।

सरल जानकारी भी मायने रखती है

ऊपर दिए गए चित्र से आप देख सकते हैं कि एक ही देश के लिए अलग-अलग श्रेणियों में कई डुप्लिकेट प्रविष्टियाँ हैं। यहाँ से तुरंत उठने वाला एक प्रश्न है:

तालिका में किन-किन देशों के रिकॉर्ड मौजूद हैं?

यदि आप केवल country_name कॉलम के साथ एक सेलेक्ट क्वेरी चलाते हैं, तो इस प्रश्न का सही उत्तर नहीं मिलेगा क्योंकि क्वेरी के परिणाम में डुप्लिकेट होंगे। आइए इसे रोकने के लिए DISTINCT कीवर्ड का उपयोग करें।

select distinct country_name from international_debt;
संचालित द्वारा

और यह कुछ इस तरह रिटर्न करेगा:

अब आपको ऊपर के प्रश्न का संतोषजनक उत्तर मिल गया है। अब aggregate functions पर जाने से पहले एक आखिरी प्रश्न:

तालिका में कितने प्रकार के debt indicators हैं?

इसका उत्तर देने वाली क्वेरी ऊपर वाली से मिलती-जुलती होगी। आपको बस कॉलम का नाम बदलना होगा। इसे एक अभ्यास मान लें? परिणाम कुछ इस तरह होना चाहिए:

Aggregate Functions

आइए एक aggregate function के साथ क्वेरी चलाकर शुरू करें और उसी क्रम में आगे बढ़ें। रास्ते में, आप सिंटैक्स और वे निर्माण सीखेंगे जिनका पालन SQL में aggregate functions लागू करते समय करना होता है।

select sum(debt) from international_debt;
संचालित द्वारा

और परिणाम:

SUM() aggregate function के साथ, आप किसी कॉलम (जिसमें संख्यात्मक मान हों) के पार अंकगणितीय योग निकाल सकते हैं। उपरोक्त क्वेरी से, आपको तालिका में सूचीबद्ध देशों का कुल लंबित ऋण पता चल गया।

नोट करें कि SUM() योग की गणना करते समय NULL मानों को नहीं मानता। अब, इस प्रश्न का उत्तर ढूँढते हैं:

सबसे अधिक ऋण की राशि क्या है?

यहाँ MAX() aggregate function काम आता है:

select max(debt) from international_debt;
संचालित द्वारा

और उत्तर है:

SUM() की तरह, MAX() भी अपनी गणनाओं के दौरान NULL प्रविष्टियों पर विचार नहीं करता। इसी तरह का एक MIN() फ़ंक्शन भी है। Comments सेक्शन में debt कॉलम का न्यूनतम मान बताइए? अब, यह जाँचना एक अच्छा विचार होगा कि debt कॉलम में कोई अमान्य प्रविष्टि तो नहीं है ताकि अब तक के परिणाम सही हों।

नोट करें कि आप इन फ़ंक्शनों का उपयोग ऊपर दिखाए अनुसार लोअर केस में भी कर सकते हैं।

जब आप यह क्वेरी चलाते हैं: select * from international_debt where debt is null;, तो आपको खाली परिणाम मिलना चाहिए। अब तालिका में मौजूद अलग-अलग देशों की कुल संख्या पता करते हैं।

select count(distinct(country_name)) from international_debt;
संचालित द्वारा

और आप देखते हैं कि तालिका में कुल 124 अलग-अलग देश मौजूद हैं। ऊपर दी गई क्वेरी में आपने जिन फ़ंक्शनों का अनुक्रम लागू किया है, उस पर ध्यान दें। हाँ, यहाँ ऐसा करना मान्य है, यानी एक से अधिक aggregate function को तार्किक रूप से जोड़ना।

अब मान लीजिए, आप debt कॉलम का औसत मान देखना चाहते हैं। इसके लिए फ़ंक्शन है AVG():

select avg(debt) from international_debt;

आप मान को 1306633214.966397971 (USD) देखते हैं। इन परिणामों को उपयुक्त कॉलम नामों के साथ प्रस्तुत करना एक अच्छा विचार है। ऊपर के परिणामों से आप देख सकते हैं कि परिणाम लौटाते समय PostgreSQL कॉलम नाम को क्वेरी में शामिल aggregate function के नाम से बदल देता है। इसलिए, इन कॉलमों को उचित उपनाम देना अच्छा रहता है। आप ऐसा इस तरह कर सकते हैं:

select avg(debt) as Average_Debt_By_A_Country from international_debt;
संचालित द्वारा

अब परिणाम कहीं अधिक व्याख्यात्मक है:

अब इसे थोड़ा और जटिल स्तर पर ले चलते हैं। ऐसे प्रश्नों का उत्तर देने के लिए कि another_column_from_the_table के संदर्भ में some_column_from_the_table के न्यूनतम मान क्या हैं?, आपको aggregate function को GROUP BY क्लॉज के साथ जोड़ना होगा। देखें कैसे।

Aggregate Functions + GROUP BY + अन्य

मान लीजिए आप एक रिपोर्ट बनाना चाहते हैं जिसमें आप country_name और उनके ऋणों का योग दिखाएँगे। उदाहरण निम्न है:

ऐसी रिपोर्टें वास्तविक दुनिया में अक्सर उपयोग होती हैं। तो, ऐसी रिपोर्ट पाने के लिए क्वेरी क्या होगी? आपको debt पर SUM() फ़ंक्शन का उपयोग करना होगा। और आपको ऋणों के योग के साथ country_name भी दिखाना होगा। निम्न क्वेरी चलाएँ:

select country_name, sum(debt) from international_debt;
संचालित द्वारा

क्या यह निम्न त्रुटि नहीं देता?

ERROR: column "international_debt.country_name" must appear in the GROUP BY clause or be used in an aggregate function

अब समझते हैं कि इसका अर्थ क्या है। जब आप किसी aggregate function (जैसे SUM()) का उपयोग किसी अनएग्रीगेटेड कॉलम जैसे country_name के साथ करते हैं, तो आपको उस अनएग्रीगेटेड कॉलम को GROUP BY क्लॉज में पास करना होता है। तो, सही क्वेरी होगी:

select country_name, sum(debt) as total_debt from international_debt group by country_name;
संचालित द्वारा

और परिणाम बिल्कुल सही है:

नोट करें कि क्वेरी में उपनाम का उपयोग किया गया है।

अब मान लीजिए आपको इस रिपोर्ट को total_debt के अनुसार अवरोही क्रम में क्रमबद्ध करना है। ORDER BY क्लॉज याद है? हाँ, आप aggregate functions को ORDER BY क्लॉज के साथ भी जोड़ सकते हैं:

select country_name, sum(debt) as total_debt from international_debt
group by country_name order by total_debt desc;
संचालित द्वारा

अब परिणाम उल्टे क्रम में सॉर्ट होना चाहिए:

नोट करें कि आपने ORDER BY क्लॉज में किस कॉलम का उपयोग किया है।

अब एक और महत्वपूर्ण प्रश्न:

विभिन्न श्रेणियों में सबसे अधिक ऋण की राशि क्या है (उल्टे क्रम में सॉर्टेड)?

यहाँ आपको MAX() फ़ंक्शन का उपयोग करना होगा। अब इस प्रश्न का उत्तर देने वाली क्वेरी लिखना कठिन नहीं होना चाहिए।

select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc;
संचालित द्वारा

और आपको एक साफ-सुथरी रिपोर्ट मिलती है:

आप ऐसी रिपोर्टों में पंक्तियों की संख्या सीमित भी कर सकते हैं। मान लीजिए आप ऊपर की रिपोर्ट में केवल शीर्ष पाँच प्रविष्टियाँ शामिल करना चाहते हैं। आप यह LIMIT क्लॉज का उपयोग करके कर सकते हैं।

select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc
limit 5;
संचालित द्वारा

इस ट्यूटोरियल की अंतिम रिपोर्ट का समय। आपको ऊपर दी गई रिपोर्ट में देशों के नाम शामिल करने हैं। आप यह कैसे कर सकते हैं? निम्न क्वेरी आपको यह करने देगी:

select country_name, indicator_code, max(debt) as maximum_debt from international_debt
group by country_name, indicator_code order by maximum_debt desc;
संचालित द्वारा

एक और बेहतरीन रिपोर्ट:

ऊपर की क्वेरी में, आपने SELECT क्लॉज के बाद country_name कॉलम जोड़ा और उसे GROUP BY के बाद भी जोड़ा। आप इस प्रारूप का विस्तार किसी भी संख्या तक कर सकते हैं।

ऐसी रिपोर्टें बनाते समय GROUP BY, ORDER BY और LIMIT का क्रम बहुत महत्वपूर्ण होता है। यदि आप गलती से क्रम बदल देते हैं, तो आपको त्रुटियों का सामना करना पड़ेगा। स्वयं देखें:

select country_name, sum(debt) as total_debt from international_debt
order by total_debt desc group by country_name;
संचालित द्वारा

और आपको मिलता है:

ERROR:  syntax error at or near "group"
LINE 1: ... from international_debt order by total_debt desc group by c...संचालित द्वारा

ऊपर की क्वेरी में, आपने GROUP BY से पहले ORDER BY क्लॉज रखा है जो स्वीकार्य नहीं है। वास्तव में, जब आप aggregate functions का उपयोग नहीं भी कर रहे होते हैं, तब भी यह लागू नहीं होता। सही क्रम है - GROUP BY -> ORDER BY -> LIMIT। इसे हमेशा याद रखें।

आगे कैसे बढ़ें

बधाई हो! आप इस ट्यूटोरियल के अंत तक पहुँच गए। इस ट्यूटोरियल में, आपने PostgreSQL में अलग-अलग aggregate functions के बारे में जाना और सीखा कि उनसे उपयोगी रिपोर्टें कैसे तैयार करें। यह वास्तव में एक डेटा साइंटिस्ट के लिए अहम कौशल है। अपनी SQL स्किल्स को व्यवस्थित तरीके से अगले स्तर पर ले जाने के लिए, आप निम्न DataCamp कोर्स कर सकते हैं:

• Joining Data in SQL
• Intermediate SQL
• SQL for Exploratory Data Analysis