समग्र कार्यों का उपयोग करें

  • 3 मिनट्स

T-SQL परिकलन करने के लिए SUM, MAX, और AVG जैसे समग्र फ़ंक्शंस प्रदान करता है जो एकाधिक मान लेते हैं और एकल परिणाम लौटाते हैं।

समग्र कार्यों के साथ कार्य करना

हमने जिन प्रश्नों को देखा है, उनमें से अधिकांश एक समय में एक पंक्ति पर काम करते हैं, पंक्तियों को फ़िल्टर करने के लिए WHERE क्लॉज का उपयोग करते हैं। लौटाई गई प्रत्येक पंक्ति मूल डेटा सेट में एक पंक्ति से संबंधित होती है.

SQL सर्वर में कई समग्र फ़ंक्शन प्रदान किए जाते हैं। इस खंड में, हम SUM, MIN, MAX, AVG और COUNT जैसे सबसे सामान्य कार्यों को देखेंगे।

समग्र कार्यों के साथ काम करते समय, आपको निम्नलिखित बिंदुओं पर विचार करने की आवश्यकता है:

  • एग्रीगेट फ़ंक्शंस एक एकल (स्केलर) मान लौटाते हैं और इसका उपयोग SELECT कथनों में लगभग कहीं भी एकल मान का उपयोग किया जा सकता है। उदाहरण के लिए, इन फ़ंक्शंस का उपयोग SELECT, HAVING, और ORDER BY क्लॉज़ में किया जा सकता है। हालाँकि, उनका उपयोग WHERE खंड में नहीं किया जा सकता है।
  • COUNT(*) का उपयोग करते समय छोड़कर, सकल फ़ंक्शन NULLs को अनदेखा करते हैं।
  • SELECT सूची में एकत्रित फ़ंक्शंस में कॉलम हेडर नहीं होता है जब तक कि आप AS का उपयोग करके उपनाम प्रदान नहीं करते.
  • SELECT सूची में एकीकृत फ़ंक्शंस SELECT कार्रवाई को पास की गई सभी पंक्तियों पर कार्य करते हैं. यदि कोई GROUP BY क्लॉज नहीं है, तो WHERE क्लॉज में किसी भी फ़िल्टर को संतुष्ट करने वाली सभी पंक्तियों को सारांशित किया जाएगा। आप अगले विषय में GROUP BY के बारे में और जानेंगे।
  • जब तक आप GROUP BY का उपयोग नहीं कर रहे हैं, तब तक आपको समग्र फ़ंक्शंस को उन स्तंभों के साथ संयोजित नहीं करना चाहिए जो समान SELECT सूची के फ़ंक्शंस में शामिल नहीं हैं.

अंतर्निहित फ़ंक्शंस से आगे विस्तृत करने के लिए, SQL सर्वर .NET सामान्य भाषा रनटाइम (CLR) के माध्यम से यूज़र-डिफ़ाइंड एग्रीगेट फ़ंक्शन के लिए एक प्रणाली प्रदान करता है। वह विषय इस मॉड्यूल के दायरे से परे है।

अंतर्निहित समग्र कार्य

जैसा कि उल्लेख किया गया है, Transact-SQL कई अंतर्निहित समग्र कार्य प्रदान करता है। आमतौर पर उपयोग किए जाने वाले कार्यों में शामिल हैं:

प्रकार्य का नाम

सिंटैक्स

विवरण

सवाल

SUM(व्यंजक)

एक कॉलम में सभी गैर-नल संख्यात्मक मानों का योग करता है।

औसत

AVG(व्यंजक)

एक कॉलम (योग/गणना) में सभी गैर-नल संख्यात्मक मानों का औसत लेता है।

मिनट

MIN(व्यंजक)

सबसे छोटी संख्या, सबसे पुरानी दिनांक/समय, या पहली बार आने वाली स्ट्रिंग (कोलेशन सॉर्ट नियमों के अनुसार) देता है.

अधिकतम

MAX(व्यंजक)

सबसे बड़ी संख्या, नवीनतम दिनांक/समय, या अंतिम बार आने वाली स्ट्रिंग (कॉलेशन सॉर्ट नियमों के अनुसार) देता है.

COUNT या COUNT_BIG

COUNT(*) या COUNT(व्यंजक)

(*) के साथ, NULL मानों वाली पंक्तियों सहित सभी पंक्तियों की गणना करता है. जब किसी स्तंभ को व्यंजक के रूप में निर्दिष्ट किया जाता है, तो उस स्तंभ के लिए गैर-नल पंक्तियों की संख्या लौटाता है. COUNT एक int लौटाता है; COUNT_BIG एक big_int लौटाता है।

किसी SELECT खंड में अंतर्निहित एग्रीगेट का उपयोग करने के लिए, MyStore नमूना डेटाबेस में निम्न उदाहरण पर विचार करें:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

इस क्वेरी के परिणाम कुछ इस तरह दिखते हैं:

औसतमूल्य

न्यूनतममूल्य

अधिकतम मूल्य

744.5952

2.2900

3578.2700

ध्यान दें कि उपरोक्त उदाहरण Production.Product तालिका से सभी पंक्तियों को सारांशित करता है। हम किसी विशिष्ट श्रेणी में उत्पादों के लिए औसत, न्यूनतम और अधिकतम मूल्य लौटाने के लिए क्वेरी को आसानी से संशोधित कर सकते हैं, इस तरह एक WHERE खंड जोड़कर:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

SELECT खंड में समुच्चय का उपयोग करते समय, SELECT सूची में संदर्भित सभी स्तंभों का उपयोग किसी समेकित फ़ंक्शन के लिए इनपुट के रूप में किया जाना चाहिए या उन्हें GROUP BY खंड में संदर्भित किया जाना चाहिए.

निम्न क्वेरी पर विचार करें, जो समेकित परिणामों में ProductCategoryID फ़ील्ड को शामिल करने का प्रयास करती है:

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

इस क्वेरी को चलाने से निम्न त्रुटि प्राप्त होती है

संदेश 8120, स्तर 16, स्थिति 1, पंक्ति 1

स्तंभ 'Production.ProductCategoryID' चयन सूची में अमान्य है क्योंकि यह किसी एकीकृत फ़ंक्शन या GROUP BY खंड में शामिल नहीं है.

क्वेरी सभी पंक्तियों को एकल समेकित समूह के रूप में मानती है. इसलिए, सभी स्तंभों को फ़ंक्शन एकत्र करने के लिए इनपुट के रूप में उपयोग किया जाना चाहिए।

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

यह क्वेरी MIN और MAX का उपयोग करके पहली और अंतिम कंपनी को नाम से लौटाती है:

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

यह क्वेरी डेटाबेस के कॉलेशन अनुक्रम में CompanyName के लिए पहला और अंतिम मान लौटाएगी, जो इस मामले में वर्णमाला क्रम है:

MinCustomer

मैक्सकस्टमर

एक बाइक की दुकान

पीली साइकिल कंपनी

अन्य कार्यों को कुल कार्यों के साथ नेस्ट किया जा सकता है।

उदाहरण के लिए, MIN और MAX के मूल्यांकन से पहले, ऑर्डर दिनांक के केवल वर्ष भाग को लौटाने के लिए YEAR स्केलर फ़ंक्शन का निम्न उदाहरण में उपयोग किया जाता है:

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

सुविधानुसार

बिलकुल नया

2008

2021

MIN और MAX फ़ंक्शंस का उपयोग दिनांक डेटा के साथ भी किया जा सकता है, ताकि जल्द से जल्द और नवीनतम कालानुक्रमिक मान वापस आ सकें। हालांकि, AVG और SUM का उपयोग केवल सांख्यिक डेटा के लिए किया जा सकता है, जिसमें पूर्णांक, धन, फ्लोट और दशमलव डेटाप्रकार शामिल हैं.

समग्र फ़ंक्शंस के साथ DISTINCT का उपयोग करना

आपको डुप्लिकेट पंक्तियों को हटाने के लिए SELECT क्लॉज में DISTINCT के उपयोग के बारे में पता होना चाहिए। जब एक एग्रीगेट फ़ंक्शन के साथ उपयोग किया जाता है, तो DISTINCT सारांश मान की गणना करने से पहले इनपुट कॉलम से डुप्लिकेट मानों को हटा देता है। DISTINCT मानों की अद्वितीय घटनाओं को सारांशित करते समय उपयोगी होता है, जैसे ऑर्डर तालिका में ग्राहक।

निम्न उदाहरण उन ग्राहकों की संख्या लौटाता है, जिन्होंने ऑर्डर दिए हैं, चाहे उन्होंने कितने भी ऑर्डर दिए हों:

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

COUNT(<some_column>) केवल यह गिनता है कि स्तंभ में कितनी पंक्तियों का कोई मान है. यदि कोई नल मान नहीं हैं, तो COUNT(<some_column>) COUNT(*) के समान होगा। COUNT (DISTINCT <some_column>) यह गणना करता है कि स्तंभ में कितने भिन्न मान हैं.

NULL के साथ समग्र फ़ंक्शंस का उपयोग करना

आपके डेटा में NULLs की संभावित उपस्थिति के बारे में पता होना महत्वपूर्ण है, और NULL कुल फ़ंक्शन सहित T-SQL क्वेरी घटकों के साथ कैसे इंटरैक्ट करता है। इसके बारे में जागरूक होने के लिए कुछ विचार हैं:

  • (*) विकल्प के साथ उपयोग किए गए COUNT के अपवाद के साथ, T-SQL एग्रीगेट फ़ंक्शन NULLs पर ध्यान नहीं देते हैं। उदाहरण के लिए, एक SUM फ़ंक्शन केवल गैर-नल मान जोड़ देगा। नल शून्य का मूल्यांकन नहीं करते हैं। COUNT(*) किसी भी स्तंभ में मान या गैर-मान पर ध्यान दिए बिना सभी पंक्तियों की गणना करता है.
  • एक कॉलम में नल की उपस्थिति एवीजी के लिए गलत गणना का कारण बन सकती है, जो केवल आबादी वाली पंक्तियों का योग करेगी और उस योग को गैर-नल पंक्तियों की संख्या से विभाजित करेगी। AVG(<स्तंभ) और (SUM(>स्तंभ<>)/COUNT(*)) के बीच परिणामों में अंतर हो सकता है.

उदाहरण के लिए, t1 नामक निम्न तालिका पर विचार करें:

सी 1

सी 2

1

शून्य

2

10

3

20

4

30

5

40

6

50

यह क्वेरी इस अंतर को दर्शाती है कि AVG NULL को कैसे हैंडल करता है और आप SUM/COUNT(*) परिकलित स्तंभ के साथ औसत परिकलित कैसे कर सकते हैं:

SELECT SUM(c2) AS sum_nonnulls, 
    COUNT(*) AS count_all_rows, 
    COUNT(c2) AS count_nonnulls, 
    AVG(c2) AS average, 
    (SUM(c2)/COUNT(*)) AS arith_average
FROM t1;

परिणाम होगा:

sum_nonnulls

count_all_rows

count_nonnulls

औसत निकालना

arith_average

150

6

5

30

25

इस परिणामसेट में, औसत नामित स्तंभ कुल है जो आंतरिक रूप से 150 का योग प्राप्त करता है और कॉलम c2 में गैर-शून्य मानों की गणना से विभाजित होता है। गणना 150/5, या 30 होगी। arith_average नामक कॉलम स्पष्ट रूप से योग को सभी पंक्तियों की गिनती से विभाजित करता है, इसलिए गणना 150/6, या 25 है।

यदि आपको सभी पंक्तियों को सारांशित करने की आवश्यकता है, चाहे NULL हो या नहीं, तो NULLs को किसी अन्य मान से बदलने पर विचार करें जिसे आपके एग्रीगेट फ़ंक्शन द्वारा अनदेखा नहीं किया जाएगा। आप इस उद्देश्य के लिए COALESCE फ़ंक्शन का उपयोग कर सकते हैं।