اندازهی قلم متن
تخمین مدت زمان مطالعهی مطلب:
دو دقیقه
قبل از مطالعه این بخش لطفا آشنایی با Window Functionها در SQL Server بخش اول را مطالعه نمایید.
دربخش اول،در مورد Syntax مربوط به Over Clause صحبت کردیم، و برای درک استفاده از Over Clause، مثالهایی را بررسی نمودیم، در این بخش نیز،به تفاوت Row Clause و Range Clause می پردازیم.
مثال: با ایجاد یک Script،عملیات جمع روی یک فیلد خاص، بوسیله Row Clause و Range Clause انجام میدهیم. تا تفاوت آنها را درک نماییم.
در ادامه Script زیر را اجرا نمایید:
DECLARE @Test TABLE ( RowID INT IDENTITY, FName VARCHAR(20), Salary SMALLINT ); INSERT INTO @Test (FName, Salary) VALUES ('George', 800), ('Sam', 950), ('Diane', 1100), ('Nicholas', 1250), ('Samuel', 1250), ('Patricia', 1300), ('Brian', 3000), ('Thomas', 1600), ('Fran', 2450), ('Debbie', 2850), ('Mark', 2975), ('James', 3000), ('Cynthia', 3000), ('Christopher', 5000); SELECT RowID,FName,Salary, SumByRows = SUM(Salary) OVER (ORDER BY Salary ROWS UNBOUNDED PRECEDING), SumByRange = SUM(Salary) OVER (ORDER BY Salary RANGE UNBOUNDED PRECEDING) FROM @Test ORDER BY RowID;
خروجی بصورت زیر خواهد بود:
با مشاهده شکل بالا، به وضوح میتوان تفاوت Row و Range را تشخیص داد. در Script بالا از UNBOUNDED PRECEDING استفاده کردیم ، و مفهوم قالب آن به شرح ذیل میباشد:
مقدار فیلد Salary سطر جاری = جمع مقادیر فیلد Salary همه سطرهای ماقبل،سطر جاری + مقدار فیلد Salary سطر جاری
Row Clause بصورت فیزیکی به سطرها مینگرد و قالب بیان شده در Script را،روی تمامی سطرها،نسبت به جایگاه آنها در جدول، به ترتیب اعمال مینماید.و در شکل نیز قابل مشاهده می باشد، یعنی به چیدمان سطرها در خروجی که بصورت فیزیکی نمایش داده شده است، توجه می کند، و حاصل جمع هر سطر برابر است با حاصل جمع سطرهای ماقبل + سطر جاری
اما Range Clause:به چیدمان فیزیکی سطرها توجه نمیکند، بلکه بصورت منطقی به مقدار فیلد Salary سطرها توجه مینماید، یعنی مقادیری که در یک محدوده(Range) قرار دارند، حاصل جمع آنها،یکی است.
مقدار فیلد Salary سطر چهار و پنج برابر است با 1250 بنابراین حاصل جمع آنها برابر هم میباشد. و بصورت زیر محاسبه میشود:
800 + 950 + 1100 + 1250 + 1250 =5350
روش بیان شده، در مورد سطرهای 12 و 13 نیز صادق است.
امیدوارم با مثالهایی که در بخش اول و بخش دوم بررسی نمودیم، روش استفاده از Over Clause را درک کرده باشیم.
Window Functionها را به چهار بخش تقسیم بندی شده اند، که به شرح ذیل میباشد:
1- Ranking functions (توابع رتبه بندی)، که بررسی نمودیم.
2- NEXT VALUE FOR ، که در بحث ایجاد Sequence آن را بررسی نمودیم.
3- Aggregate Functions (توابع جمعی)، اکثرا با اینگونه توابع آشنا هستیم.
4- Analytic Functions (توابع تحلیلی) که در بخش بعدی آن را بررسی مینماییم.
یکی از منابع بسیار مفید در مورد Window Function ها کتاب Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions ، می باشد،که بطور کامل به Window Functionها اختصاص دارد و تکنیکهای بسیار مفیدی را بیان میکند. مطالعه آن به علاقمندان، پیشنهاد میگردد.
موفق باشید.