Microsoft SQL Server
कभी खंड

पैरामीटर

टिप्पणियों

OVER के साथ एकत्रीकरण कार्यों का उपयोग करना

कार तालिका का उपयोग करके, हम खर्च किए गए प्रत्येक कॉस्ट्यूमर की कुल, अधिकतम, न्यूनतम और औसत राशि की गणना करेंगे और कई बार (COUNT) वह मरम्मत के लिए एक कार लाए।

Id CustomerId मैकेनिक की मॉडल स्थिति कुल लागत

SELECT CustomerId,  
       SUM(TotalCost) OVER(PARTITION BY CustomerId) AS Total,
       AVG(TotalCost) OVER(PARTITION BY CustomerId) AS Avg,
       COUNT(TotalCost) OVER(PARTITION BY CustomerId) AS Count,
       MIN(TotalCost) OVER(PARTITION BY CustomerId) AS Min,
       MAX(TotalCost) OVER(PARTITION BY CustomerId) AS Max
  FROM CarsTable
 WHERE Status = 'READY'

खबरदार कि इस फैशन में OVER का उपयोग करके वापस लौटी पंक्तियों को एकत्रित नहीं करेंगे। उपरोक्त क्वेरी निम्नलिखित वापस करेगी:

डुप्लिकेटेड पंक्ति (रों) रिपोर्टिंग उद्देश्यों के लिए उपयोगी नहीं हो सकती है।

यदि आप केवल डेटा एकत्र करना चाहते हैं, तो आप समुचित समुच्चय कार्यों के साथ ग्रुप बाय क्लॉज का उपयोग करना बेहतर होगा:

SELECT CustomerId,  
       SUM(TotalCost) AS Total,
       AVG(TotalCost) AS Avg,
       COUNT(TotalCost) AS Count,
       MIN(TotalCost) AS Min,
       MAX(TotalCost)  AS Max
  FROM CarsTable
 WHERE Status = 'READY'
GROUP BY CustomerId

संचयी योग

आइटम विक्रय तालिका का उपयोग करते हुए, हम यह पता लगाने की कोशिश करेंगे कि तारीखों के माध्यम से हमारी वस्तुओं की बिक्री कैसे बढ़ रही है। ऐसा करने के लिए, हम बिक्री तिथि के अनुसार आइटम ऑर्डर के प्रति कुल बिक्री के संचयी योग की गणना करेंगे।

SELECT item_id, sale_Date 
       SUM(quantity * price) OVER(PARTITION BY item_id ORDER BY sale_Date ROWS BETWEEN UNBOUNDED PRECEDING) AS SalesTotal
  FROM SalesTable

सबसे हाल के रिकॉर्ड खोजने के लिए एकत्रीकरण funtions का उपयोग करना

लाइब्रेरी डेटाबेस का उपयोग करते हुए, हम प्रत्येक लेखक के लिए डेटाबेस में जोड़ी गई अंतिम पुस्तक खोजने की कोशिश करते हैं। इस सरल उदाहरण के लिए हम प्रत्येक रिकॉर्ड के लिए हमेशा बढ़े हुए आईडी को जोड़ते हैं।

SELECT MostRecentBook.Name, MostRecentBook.Title
FROM ( SELECT Authors.Name,
              Books.Title,
              RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.Id DESC) AS NewestRank
       FROM Authors
       JOIN Books ON Books.AuthorId = Authors.Id
     ) MostRecentBook
WHERE MostRecentBook.NewestRank = 1

आरएसी के बजाय, दो अन्य कार्यों का उपयोग ऑर्डर करने के लिए किया जा सकता है। पिछले उदाहरण में परिणाम समान होगा, लेकिन वे अलग-अलग परिणाम देते हैं जब आदेश प्रत्येक रैंक के लिए कई पंक्तियाँ देता है।

• RANK() : डुप्लिकेट को समान रैंक मिलती है, अगली रैंक डुप्लिकेट की संख्या को पिछली रैंक को ध्यान में रखती है
• DENSE_RANK() : डुप्लिकेट को समान रैंक मिलती है, अगली रैंक हमेशा पिछले की तुलना में एक अधिक होती है
• ROW_NUMBER() : प्रत्येक पंक्ति को एक अद्वितीय 'रैंक', डुप्लिकेट को बेतरतीब ढंग से 'रैंकिंग' देगा

उदाहरण के लिए, यदि तालिका में एक गैर-अद्वितीय स्तंभ क्रिएशनडेट था और ऑर्डर उसी के आधार पर किया गया था, तो निम्नलिखित प्रश्न:

SELECT Authors.Name,
       Books.Title,
       Books.CreationDate,
       RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS RANK,
       DENSE_RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS DENSE_RANK,
       ROW_NUMBER() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS ROW_NUMBER,
FROM Authors
JOIN Books ON Books.AuthorId = Authors.Id

परिणाम में हो सकता है:

NTile का उपयोग करके डेटा को समान रूप से विभाजित बाल्टी में विभाजित करना

मान लीजिए कि आपके पास कई परीक्षाओं के लिए परीक्षा के अंक हैं और आप उन्हें प्रति परीक्षा में चतुर्थांश में विभाजित करना चाहते हैं।

-- Setup data:
declare @values table(Id int identity(1,1) primary key, [Value] float, ExamId int)
insert into @values ([Value], ExamId) values
(65, 1), (40, 1), (99, 1), (100, 1), (90, 1), -- Exam 1 Scores
(91, 2), (88, 2), (83, 2), (91, 2), (78, 2), (67, 2), (77, 2) -- Exam 2 Scores

-- Separate into four buckets per exam:
select ExamId, 
       ntile(4) over (partition by ExamId order by [Value] desc) as Quartile, 
       Value, Id 
from @values 
order by ExamId, Quartile

जब आप वास्तव में बाल्टियों की एक निश्चित संख्या और लगभग समान स्तर के लिए भरे हों, तो ntile बढ़िया काम करती है। ध्यान दें कि इन अंकों को केवल ntile(100) का उपयोग करके प्रतिशत में अलग करना तुच्छ होगा।