SQL में GROUP BY और HAVING का उपयोग कैसे करें

हम Unicorn Companies Database का उपयोग करेंगे, जो DataLab, DataCamp के AI-सक्षम डेटा नोटबुक, पर उपलब्ध है। इन कंपनियों को Unicorn इसलिए कहा जाता है क्योंकि ये स्टार्टअप हैं जिनका मूल्यांकन एक अरब डॉलर से अधिक है। सरलता के लिए, हम तीन तालिकाओं पर ध्यान देंगे: companies, sales, और product_emissions। अपने आप इस ट्यूटोरियल का सारा उदाहरण कोड चलाने के लिए, आप डेटाबेस और सभी कोड सैंपल पहले से लोडेड के साथ निःशुल्क एक DataLab वर्कबुक बना सकते हैं।

SQL GROUP BY का उपयोग

GROUP BY एक SQL कमांड है जिसका उपयोग आमतौर पर डेटा को एग्रीगेट कर इनसाइट्स प्राप्त करने के लिए किया जाता है। जब आप डेटा को समूहित करते हैं, तो तीन चरण होते हैं:

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

SQL GROUP BY उदाहरण 1

हम GROUP BY का एक सरल उदाहरण दिखाकर शुरू कर सकते हैं। मान लें कि हम उन शीर्ष दस देशों को खोजना चाहते हैं जिनमें Unicorn कंपनियों की संख्या सबसे अधिक है। 

SELECT * 
FROM companiesसंचालित द्वारा

यह भी अच्छा होगा कि परिणामों को कंपनियों की संख्या के आधार पर घटते क्रम में क्रमबद्ध किया जाए

SELECT country, COUNT(*) AS n_companies
FROM companies
GROUP BY country
ORDER BY n_companies DESC
LIMIT 10संचालित द्वारा

यहाँ हमारे पास परिणाम हैं। संभवतः आपको US, China और India को रैंकिंग में देखकर आश्चर्य नहीं होगा। आइए इस क्वेरी के पीछे के फैसलों को समझते हैं: 

• सबसे पहले, ध्यान दें कि हमने प्रत्येक समूह के लिए पंक्तियों की गिनती करने हेतु COUNT(*) का उपयोग किया, जो यहाँ देश के अनुरूप है। इसके अलावा, हमने कॉलम का नाम अधिक स्पष्ट बनाने के लिए SQL उपनाम का भी उपयोग किया। यह AS कीवर्ड के बाद नए नाम का उपयोग करके संभव है। COUNT के बारे में अधिक विस्तार से COUNT() SQL FUNCTION ट्यूटोरियल में बताया गया है।

• फ़ील्ड्स को companies तालिका से चुना गया, जहाँ प्रत्येक पंक्ति एक Unicorn कंपनी से संबंधित है। 

• इसके बाद, हमें देश के आधार पर डेटा एग्रीगेट करने के लिए GROUP BY के बाद कॉलम का नाम निर्दिष्ट करना होता है। 

• सही क्रम में देशों को दिखाने के लिए ORDER BY आवश्यक है—सबसे अधिक कंपनियों से लेकर कम कंपनियों तक। 

• हम परिणामों को 10 तक सीमित करते हैं LIMIT का उपयोग करके, जिसके बाद आप चाहते परिणाम पंक्तियों की संख्या आती है।

SQL GROUP BY उदाहरण 2

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

इस बार, हम प्रत्येक प्रोडक्ट लाइन के लिए औसत प्रति यूनिट मूल्य, कुल ऑर्डरों की संख्या, और कुल लाभ निकालने में रुचि रखते हैं:

SELECT 
    product_line,
    AVG(unit_price) AS avg_price,
    SUM(quantity) AS tot_pieces,
    SUM(total) AS total_gain
FROM sales
GROUP BY product_line
ORDER BY total_gain DESCसंचालित द्वारा

• पंक्तियों की गिनती करने के बजाय, हमने औसत मूल्य प्राप्त करने के लिए AVG() फंक्शन और प्रत्येक प्रोडक्ट लाइन के लिए कुल ऑर्डर तथा कुल लाभ निकालने के लिए SUM() फंक्शन का उपयोग किया है। 

• पहले की तरह, हम वह कॉलम निर्दिष्ट करते हैं जो प्रारंभ में डेटासेट को खंडों में विभाजित करता है। फिर एग्रीगेशन फंक्शन हमें प्रोडक्ट लाइन की प्रत्येक विधा के लिए एक पंक्ति प्रदान करेंगे। 

• इस बार, ORDER BY वैकल्पिक है। इसे इसलिए शामिल किया गया है ताकि यह दर्शाया जा सके कि अधिक कुल लाभ हमेशा अधिक औसत मूल्य या कुल पीस के समानुपाती नहीं होते। 

WHERE की सीमाएँ

आइए पिछले उदाहरण को फिर लें। अब, हम क्वेरी पर एक शर्त लगाना चाहते हैं: हम केवल उन प्रोडक्ट लाइनों को फ़िल्टर करना चाहते हैं जिनके कुल ऑर्डर 40,000 से अधिक हैं। चलिए WHERE क्लॉज़ आज़माते हैं:

SELECT 
    product_line,
    AVG(unit_price) AS avg_price,
    SUM(quantity) AS tot_pieces,
    SUM(total) AS total_gain
FROM sales
WHERE SUM(total) > 40000
GROUP BY product_line
ORDER BY total_gain DESCसंचालित द्वारा

यह क्वेरी निम्नलिखित त्रुटि लौटाएगी:

यह त्रुटि इसलिए आती है क्योंकि WHERE क्लॉज़ में एग्रीगेट फंक्शंस पास करना संभव नहीं है। इस समस्या को हल करने के लिए हमें एक नया कमांड चाहिए।

SQL HAVING का उपयोग

WHERE की तरह, HAVING क्लॉज़ तालिका की पंक्तियों को फ़िल्टर करता है। जहाँ WHERE पूरी तालिका को फ़िल्टर करने की कोशिश करता है, वहीं HAVING GROUP BY द्वारा परिभाषित प्रत्येक समूह के भीतर पंक्तियों को फ़िल्टर करता है।

SQL HAVING उदाहरण 1

यहाँ पिछले उदाहरण को फिर से लेते हैं, और WHERE शब्द को HAVING से बदलते हैं।

SELECT 
    product_line,
    AVG(unit_price) AS avg_price,
    SUM(quantity) AS tot_pieces,
    SUM(total) AS total_gain
FROM sales
GROUP BY product_line
HAVING SUM(total) > 40000
ORDER BY total_gain DESCसंचालित द्वारा

इस बार यह तीन पंक्तियाँ देगा। अन्य प्रोडक्ट लाइनें मानदंड पर खरी नहीं उतरीं, इसलिए हम छह परिणामों से घटकर तीन पर आ गए। 

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

SQL HAVING उदाहरण 2

अंतिम उदाहरण के रूप में, हम product_emissions नामक तालिका का उपयोग करेंगे, जिसमें कंपनियों द्वारा प्रदान किए गए उत्पादों का उत्सर्जन शामिल है।

इस बार, हम उस प्रत्येक कंपनी के लिए औसत उत्पाद कार्बन फुटप्रिंट (pcf) दिखाने में रुचि रखते हैं जो “Technology Hardware & Equipment” इंडस्ट्री ग्रुप से संबंधित है। साथ ही, प्रत्येक कंपनी के उत्पादों की संख्या देखना भी उपयोगी होगा ताकि समझा जा सके कि उत्पादों की संख्या और कार्बन फुटप्रिंट के बीच कोई संबंध है या नहीं। हम फिर से HAVING का उपयोग उन कंपनियों को निकालने के लिए करते हैं जिनका औसत कार्बन फुटप्रिंट 100 से अधिक है।

SELECT pe.company, count(product_name) AS n_products, avg(carbon_footprint_pcf) AS avg_carbon_footprint_pcf
FROM product_emissions AS pe
WHERE industry_group = 'Technology Hardware & Equipment'
GROUP BY pe.company, industry_group
having avg_carbon_footprint_pcf>100
ORDER BY n_productsसंचालित द्वारा

उपनाम का उपयोग करने की कोशिश करने के बाद एक त्रुटि आई। HAVING क्लॉज़ के लिए नया कॉलम नाम मौजूद नहीं होता, इसलिए वह क्वेरी को फ़िल्टर नहीं कर पाएगा। आइए इसे सही करें:

SELECT pe.company, count(product_name) AS n_products, avg(carbon_footprint_pcf) AS avg_carbon_footprint_pcf
FROM product_emissions AS pe
WHERE industry_group = 'Technology Hardware & Equipment'
GROUP BY pe.company, industry_group
having avg(carbon_footprint_pcf)>100
ORDER BY n_productsसंचालित द्वारा

इस बार शर्त काम कर गई, और हम तालिका से परिणाम देख सकते हैं। हमने अभी सीखा कि कॉलम उपनामों का उपयोग HAVING में नहीं किया जा सकता क्योंकि यह शर्त SELECT से पहले लागू होती है। इसी कारण यह नए नामों से फ़ील्ड्स को पहचान नहीं पाता।

SQL Order of Execution

क्वेरी लिखते समय कमांड्स का क्रम यह होता है:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BYसंचालित द्वारा

लेकिन आपको खुद से एक प्रश्न पूछना चाहिए। SQL कमांड्स किस क्रम में निष्पादित होते हैं? मनुष्य के तौर पर हम अक्सर मान लेते हैं कि कंप्यूटर SQL को ऊपर से नीचे पढ़ता और समझता है। पर वास्तविकता अलग है। यह है सही execution क्रम:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMITसंचालित द्वारा

तो, क्वेरी प्रोसेसर SELECT से शुरू नहीं करता, बल्कि पहले यह तय करता है कि किन तालिकाओं को शामिल करना है, और SELECT का निष्पादन HAVING के बाद होता है। यह समझाता है कि HAVING ALIAS के उपयोग की अनुमति क्यों नहीं देता, जबकि ORDER BY को इससे समस्या नहीं होती। इसके अलावा, यह execution क्रम स्पष्ट करता है कि एग्रीगेटेड डेटा पर शर्तें लागू करने के लिए HAVING को GROUP BY के साथ क्यों उपयोग किया जाता है, जबकि WHERE ऐसा नहीं कर सकता। 

यदि आप और जानना चाहते हैं, तो हमारा ट्यूटोरियल SQL Order of Execution: Understanding How Queries Run विस्तार से समझाता है—मैं इसे पढ़ने की सलाह दूँगा।

अगले स्तर पर जाएँ

यह ट्यूटोरियल पढ़ने के बाद, आपको GROUP BY और HAVING के बीच अंतर स्पष्ट होना चाहिए। आप इन अवधारणाओं में महारत हासिल करने के लिए DataLab पर अभ्यास कर सकते हैं।

यदि आप SQL लर्निंग पाथ के अगले स्तर पर जाना चाहते हैं, तो हमारा Intermediate SQL कोर्स कर सकते हैं। यदि आपको अभी भी SQL की बुनियाद मजबूत करनी है, तो आप Introduction to SQL कोर्स पर वापस जाकर भाषा की मूलभूत बातें सीख सकते हैं।