SQL
कार्य (विश्लेषणात्मक)
खोज…
परिचय
आप मानों के समूहों के आधार पर मूल्यों को निर्धारित करने के लिए विश्लेषणात्मक कार्यों का उपयोग करते हैं। उदाहरण के लिए, आप इस प्रकार के फ़ंक्शन का उपयोग रनिंग योग, प्रतिशत, या किसी समूह में शीर्ष परिणाम निर्धारित करने के लिए कर सकते हैं।
वाक्य - विन्यास
- FIRST_VALUE (scalar_expression) OVER ([partition_by_clause] order_by_clause [row_range_clause])
- LAST_VALUE (scalar_expression) OVER ([विभाजन_by_clause] आदेश_by_clause [पंक्तियों_ व्यवस्था_क्लाज])
- LAG (scalar_expression [, ऑफसेट] [, डिफ़ॉल्ट]) OVER ([विभाजन_by_clause] order_by_clause)
- LEAD (scalar_expression [, ऑफसेट], [डिफ़ॉल्ट]) OVER ([विभाजन_by_clause] order_by_clause)
- PERCENT_RANK () OVER ([partition_by_clause] order_by_clause)
- CUME_DIST () OVER ([partition_by_clause] order_by_clause)
- PERCENTILE_DISC (num_literal) विथिन ग्रुप (ORDER BY ऑर्डर_by_exp [ASC | DESC]) OVER ([<part_by_clause>])
- PERCENTILE_CONT (num_literal) WithIN GROUP (ORDER BY order_by_exp_ [ASC | DESC]) OVER ([<part_by_clause>])
FIRST_VALUE
आप पहली बार एक निर्धारित परिणाम सेट में पहला मान निर्धारित करने के लिए FIRST_VALUE फ़ंक्शन का उपयोग करते हैं, जिसे आप स्केलर अभिव्यक्ति का उपयोग करके पहचानते हैं।
SELECT StateProvinceID, Name, TaxRate,
FIRST_VALUE(StateProvinceID)
OVER(ORDER BY TaxRate ASC) AS FirstValue
FROM SalesTaxRate;
इस उदाहरण में, FIRST_VALUE फ़ंक्शन का उपयोग राज्य या प्रांत की ID को सबसे कम कर दर के साथ वापस करने के लिए किया जाता है। OVER क्लॉज का उपयोग टैक्स दरों को न्यूनतम दर प्राप्त करने के लिए किया जाता है।
| StateProvinceID | नाम | कर दर | FirstValue |
|---|---|---|---|
| 74 | यूटा स्टेट सेल्स टैक्स | 5.00 | 74 |
| 36 | मिनेसोटा राज्य बिक्री कर | 6.75 | 74 |
| 30 | मैसाचुसेट्स राज्य बिक्री कर | 7.00 | 74 |
| 1 | कनाडा के जी.एस.टी. | 7.00 | 74 |
| 57 | कनाडा के जी.एस.टी. | 7.00 | 74 |
| 63 | कनाडा के जी.एस.टी. | 7.00 | 74 |
LAST_VALUE
LAST_VALUE फ़ंक्शन ऑर्डर किए गए परिणाम सेट में अंतिम मान प्रदान करता है, जिसे आप स्केलर अभिव्यक्ति का उपयोग करके निर्दिष्ट करते हैं।
SELECT TerritoryID, StartDate, BusinessentityID,
LAST_VALUE(BusinessentityID)
OVER(ORDER BY TerritoryID) AS LastValue
FROM SalesTerritoryHistory;
यह उदाहरण दिए गए मानों में प्रत्येक LAST_VALUE लिए अंतिम मान वापस करने के लिए LAST_VALUE फ़ंक्शन का उपयोग करता है।
| TerritoryID | आरंभ करने की तिथि | BusinessentityID | LastValue |
|---|---|---|---|
| 1 | 2005-07-01 00.00.00.000 | 280 | 283 |
| 1 | 2006-11-01 00.00.00.000 | 284 | 283 |
| 1 | 2005-07-01 00.00.00.000 | 283 | 283 |
| 2 | 2007-01-01 00.00.00.000 | 277 | 275 |
| 2 | 2005-07-01 00.00.00.000 | 275 | 275 |
| 3 | 2007-01-01 00.00.00.000 | 275 | 277 |
LAG और लीड
LAG फ़ंक्शन समान परिणाम सेट में वर्तमान पंक्ति से पहले पंक्तियों पर डेटा प्रदान करता है। उदाहरण के लिए, किसी SELECT कथन में, आप पिछली पंक्ति के मानों के साथ वर्तमान पंक्ति में मानों की तुलना कर सकते हैं।
आप उन मानों को निर्दिष्ट करने के लिए एक स्केलर अभिव्यक्ति का उपयोग करते हैं जिनकी तुलना की जानी चाहिए। ऑफसेट पैरामीटर वर्तमान पंक्ति से पहले पंक्तियों की संख्या है जिसका उपयोग तुलना में किया जाएगा। यदि आप पंक्तियों की संख्या निर्दिष्ट नहीं करते हैं, तो एक पंक्ति के डिफ़ॉल्ट मान का उपयोग किया जाता है।
डिफ़ॉल्ट पैरामीटर मान जब में अभिव्यक्ति ऑफसेट एक है किया जाना चाहिये कि निर्दिष्ट करता है NULL मूल्य। यदि आप कोई मान निर्दिष्ट नहीं करते हैं, तो NULL का मान लौटाया जाता है।
LEAD फ़ंक्शन पंक्ति सेट में वर्तमान पंक्ति के बाद पंक्तियों पर डेटा प्रदान करता है। उदाहरण के लिए, किसी SELECT कथन में, आप वर्तमान पंक्ति में मानों की तुलना निम्न पंक्ति में मानों से कर सकते हैं।
आप उन मानों को निर्दिष्ट करते हैं जिनकी तुलना स्केलर अभिव्यक्ति का उपयोग करके की जानी चाहिए। ऑफसेट पैरामीटर वर्तमान पंक्ति के बाद पंक्तियों की संख्या है जिसका उपयोग तुलना में किया जाएगा।
आप मूल्य जब में अभिव्यक्ति ऑफसेट एक है किया जाना चाहिये कि निर्दिष्ट NULL डिफ़ॉल्ट पैरामीटर का उपयोग कर मूल्य। यदि आप इन मापदंडों को निर्दिष्ट नहीं करते हैं, तो एक पंक्ति के डिफ़ॉल्ट का उपयोग किया जाता है और NULL का मान लौटाया जाता है।
SELECT BusinessEntityID, SalesYTD,
LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lead value",
LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lag value"
FROM SalesPerson;
यह उदाहरण BusinessEntityID कॉलम के आधार पर रिकॉर्ड किए गए रिकॉर्ड के साथ, ऊपर और नीचे सूचीबद्ध कर्मचारियों के साथ तिथि करने के लिए प्रत्येक कर्मचारी के बिक्री मूल्यों की तुलना करने के लिए LEAD और LAG फ़ंक्शन का उपयोग करता है।
| BusinessEntityID | SalesYTD | लीड मूल्य | लाग मान |
|---|---|---|---|
| 274 | 559697.5639 | 3763178.1787 | 0.0000 |
| 275 | 3763178.1787 | 4251368.5497 | 559697.5639 |
| 276 | 4251368.5497 | 3189418.3662 | 3763178.1787 |
| 277 | 3189418.3662 | 1453719.4653 | 4251368.5497 |
| 278 | 1453719.4653 | 2315185.6110 | 3189418.3662 |
| 279 | 2315185.6110 | 1352577.1325 | 1453719.4653 |
PERCENT_RANK और CUME_DIST
PERCENT_RANK फ़ंक्शन पंक्ति के सापेक्ष पंक्ति की रैंकिंग की गणना करता है। प्रतिशत समूह में पंक्तियों की संख्या पर आधारित है जिनका वर्तमान पंक्ति की तुलना में कम मूल्य है।
परिणाम सेट में पहला मान हमेशा शून्य का एक प्रतिशत रैंक होता है। सेट में उच्चतम रैंक वाले - या अंतिम - मान का मूल्य हमेशा एक होता है।
CUME_DIST फ़ंक्शन मानों के समूह में एक निर्दिष्ट मान की सापेक्ष स्थिति की गणना करता है, उस मान से कम या उसके बराबर मानों का प्रतिशत निर्धारित करके। इसे संचयी वितरण कहा जाता है।
SELECT BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
AS "Percent Rank",
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
AS "Cumulative Distribution"
FROM Employee;
इस उदाहरण में, आप विभाजन के लिए एक ORDER क्लॉज का उपयोग करते हैं - या समूह - कर्मचारियों की नौकरी के शीर्षक के आधार पर SELECT स्टेटमेंट द्वारा प्राप्त की गई पंक्तियाँ, प्रत्येक समूह के परिणामों के साथ जो कि कर्मचारियों द्वारा उपयोग किए गए बीमार छुट्टी के घंटों के आधार पर छांटे गए हैं।
| BusinessEntityID | नौकरी का नाम | SickLeaveHours | प्रतिशत रैंक | संचयी बटवारा |
|---|---|---|---|---|
| 267 | एप्लिकेशन विशेषज्ञ | 57 | 0 | 0.25 |
| 268 | एप्लिकेशन विशेषज्ञ | 56 | .333333333333333 | 0.75 |
| 269 | एप्लिकेशन विशेषज्ञ | 56 | .333333333333333 | 0.75 |
| 272 | एप्लिकेशन विशेषज्ञ | 55 | 1 | 1 |
| 262 | चीफ फाइनेंशियल ऑफिसर को असिस्टेंट | 48 | 0 | 1 |
| 239 | लाभ विशेषज्ञ | 45 | 0 | 1 |
| 252 | क्रेता | 50 | 0 | .111111111111111 |
| 251 | क्रेता | 49 | 0.125 | .333333333333333 |
| 256 | क्रेता | 49 | 0.125 | .333333333333333 |
| 253 | क्रेता | 48 | 0.375 | .555555555555555 |
| 254 | क्रेता | 48 | 0.375 | .555555555555555 |
PERCENT_RANK फ़ंक्शन प्रत्येक समूह के भीतर प्रविष्टियों को रैंक करता है। प्रत्येक प्रविष्टि के लिए, यह उसी समूह में प्रविष्टियों का प्रतिशत लौटाता है जिसमें निम्न मान होते हैं।
CUME_DIST फ़ंक्शन समान है, सिवाय इसके कि यह वर्तमान मान से कम या इसके बराबर मानों का प्रतिशत लौटाता है।
PERCENTILE_DISC और PERCENTILE_CONT
PERCENTILE_DISC फ़ंक्शन पहले प्रविष्टि के मूल्य को सूचीबद्ध करता है, जहां संचयी वितरण उस प्रतिशत से अधिक है, जो आप numeric_literal पैरामीटर का उपयोग करके प्रदान करते हैं।
मान रोसेट या विभाजन द्वारा समूहीकृत हैं, जैसा कि WITHIN GROUP क्लॉज द्वारा निर्दिष्ट किया गया है।
PERCENTILE_CONT समारोह के समान है PERCENTILE_DISC समारोह है, लेकिन पहले मिलान प्रवेश और अगली प्रविष्टि के योग का औसत देता है।
SELECT BusinessEntityID, JobTitle, SickLeaveHours,
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
AS "Cumulative Distribution",
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
OVER(PARTITION BY JobTitle) AS "Percentile Discreet"
FROM Employee;
मिलान या 0.5 प्रतिशत से अधिक वाली पंक्ति से सटीक मान ज्ञात करने के लिए, आप PERCENTILE_DISC फ़ंक्शन में संख्यात्मक शाब्दिक के रूप में प्रतिशतक पास करते हैं। परिणाम सेट में Percentile Discreet स्तंभ उस पंक्ति के मूल्य को सूचीबद्ध करता है जिस पर संचयी वितरण निर्दिष्ट प्रतिशत से अधिक है।
| BusinessEntityID | नौकरी का नाम | SickLeaveHours | संचयी बटवारा | परसेंटाइल डिसक्रीट |
|---|---|---|---|---|
| 272 | एप्लिकेशन विशेषज्ञ | 55 | 0.25 | 56 |
| 268 | एप्लिकेशन विशेषज्ञ | 56 | 0.75 | 56 |
| 269 | एप्लिकेशन विशेषज्ञ | 56 | 0.75 | 56 |
| 267 | एप्लिकेशन विशेषज्ञ | 57 | 1 | 56 |
मानों के एक सेट पर गणना के आधार पर, आप PERCENTILE_CONT फ़ंक्शन का उपयोग करें। परिणामों में "परसेंटाइल कंटिन्यूअस" कॉलम परिणाम मूल्य के योग के औसत मूल्य और अगले उच्चतम मिलान मूल्य को सूचीबद्ध करता है।
SELECT BusinessEntityID, JobTitle, SickLeaveHours,
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
AS "Cumulative Distribution",
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
OVER(PARTITION BY JobTitle) AS "Percentile Discreet",
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
OVER(PARTITION BY JobTitle) AS "Percentile Continuous"
FROM Employee;
| BusinessEntityID | नौकरी का नाम | SickLeaveHours | संचयी बटवारा | परसेंटाइल डिसक्रीट | प्रतिशतक सतत |
|---|---|---|---|---|---|
| 272 | एप्लिकेशन विशेषज्ञ | 55 | 0.25 | 56 | 56 |
| 268 | एप्लिकेशन विशेषज्ञ | 56 | 0.75 | 56 | 56 |
| 269 | एप्लिकेशन विशेषज्ञ | 56 | 0.75 | 56 | 56 |
| 267 | एप्लिकेशन विशेषज्ञ | 57 | 1 | 56 | 56 |