WINDOW FUNCTION و پایان محدودیت های GROUP BY - مقدمه

در آموزش WINDOW FUNCTION چیست یکی از مزیت های WINDOW FUNCTION را پایان محدودیت های GROUP BY برشمردیم. حتماً برای شما پیش آمده است که در زمان گروهبندی رکوردها با استفاده از دستور group by، دسترسی به جزئیات رکوردها امکان پذیر نیست. به عنوان مثال پس از استخراج آخرین سفارش هر ماه با گروهبندی بر اساس تاریخ، اگر نیاز به استخراج مشخصات فروشنده یا خریدار و ... داشته باشیم چاره ای جز استفاده از جوین، cross apply، cte و یا subquery نداریم. در هر صورت از هر روشی که استفاده کنیم می بایست مراحل اضافه و بعضاً پیچیده ای را به کوئری اصلی اضافه کنیم تا خروجی مورد نظر تهیه شود. با معرفی Aggregate WINDOW FUNCTION در sql server 2005 در واقع این گونه دردسرها جهت تهیه خروجی مورد نظر پایان یافت.

در آموزش WINDOW FUNCTION و پایان محدودیت های GROUP BY به معرفی یکی از پرکاربردترین توابع WINDOW FUNCTION یعنی Aggregate WINDOW FUNCTION می پردازیم. با توجه به اینکه از قبل با توابع تجمعی(Aggregate Function) در sql server آشنایی داریم معرفی این دسته از WINDOW FUNCTION ها آسان تر خواهد بود.

دانلود اسکریپ های لازم برای آموزش 

---

WINDOW FUNCTION و پایان محدودیت های GROUP BY - روش های دور زدن محدودیت

 Aggregate WINDOW FUNCTION ها عبارتند ازSUM، AVG، MIN، MAX، COUNT. به عنوان یک تفاوت ظاهری بین Aggregate WINDOW FUNCTION با Aggregate Function می توانیم عبارت ()OVER را به عنوان نشانه ای بر تمایز بین این دو بدانیم به نحوی که اگر بعد از یک تابع تجمعی(Aggregate Function) عبارت ()OVER همراه شود می توانیم اینگونه بگوییم که دیگر آن تابع تجمعی را به عنوان Aggregate WINDOW FUNCTION می شناسیم. دو تفاوت عمده بین Aggregate WINDOW FUNCTION با Aggregate Function عبارتند از:

     1. بر خلاف توابع تجمعی، امکان دسترسی به جزئیات بعد از گروهبندی در Aggregate WINDOW FUNCTION وجود دارد.

     2. Aggregate WINDOW FUNCTION را تنها مجاز هستیم در قسمت select و order by کوئری استفاده کنیم.

برای آشنایی بیشتر با مزیت های Aggregate WINDOW FUNCTION و نیز مقایسه کارایی آن با روش های قدیمی که در نسخه های قبلی sql server استفاده می شد اقدام به محاسبه Running Total به همراه نمایش اطلاعات اضافی، در جدول TransactionHistory می کنیم. می دانیم که Running Total از جمع مقدار عددی یک ستون با مجموع مقادیر قبل از آن حاصل می شود و یکی از محاسبات  رایج در سیستم های حسابداری، انبار، بانک و ... است. جدول TransactionHistory تعداد 5000عدد تراکنش واریز/برداشت برای تعداد 100 مشتری را نگهداری می کند و در مجموه 500هزار رکورد دارد. ساختار این جدول به صورت زیر است(جهت دانلود اسکریپت لازم برای ایجاد جدول و درج داده های تستی در آن از لینک ابتدای آموزش استفاده کنید) 

CREATE TABLE dbo.TransactionHistory
(
  actid  INT   NOT NULL,
  tranid INT   NOT NULL,
  val    MONEY NOT NULL,
  trandate datetime default null,
  CONSTRAINT PK_TrnHistory PRIMARY KEY(actid, tranid)  

)

به عنوان اولین روش در محاسبه Running Total برای اکانت آی دی یک (actid=1) از Inner Join استفاده می کنیم. دقت کنید در این روش الزاماً بایستی در جدول فیلد یونیکی مانند Identity و یا کلید اصلی وجود داشته باشد در غیر اینصورت مجبور هستیم با استفاده از توابعی مانند Row_Number یک ستون یونیک در خروجی ایجاد کنیم. نکته قابل توجه دیگر در کوئری زیر این است که برای استخراج اطلاعات اضافی مجبور به نوشتن یک select بیرونی هستیم:

--1.استفاده از جوین
SELECT actid, tranid, val,
       (SELECT SUM(val) 
          FROM TransactionHistory b
         WHERE b.actid = a.actid
           AND b.tranid <= a.tranid) AS [Running Total] --با مقادیر قبلی آن در هر گروه Quantity مجموع  
		    FROM TransactionHistory a
			where actid=1

به عنوان نمونه، خروجی کوئری بالا در نحوه محاسبه Running Total در شکل زیر نمایش داده شده است:

یکی از روش های رایج دیگر در محاسبه  Running Total به همراه نمایش اطلاعات اضافی، استفاده از cursor است. اگرچه همواره در آموزش های مرتبط با کوئری نویسی و افزایش سرعت و کارایی، تذکر داده می شود که از استفاده cursor در طراحی کوئری پرهیز کنید، این روش در نسخه های قدیمی(قبل از sql2005) از سرعت خوبی برخوردار است. 

--2. استفاده از کرسر
--تعریف متغییر جدولی جهت نگهداری نتایج محاسبه
DECLARE @TotalTable table(actid int,tranid int primary key, val money, running_total money)

 --تعریف کرسر
DECLARE forward_cursor CURSOR FAST_FORWARD 
FOR 
SELECT actid, tranid, val
FROM TransactionHistory 
where actid=1


OPEN forward_cursor 

DECLARE @running_total money, 
    @actid int, 
	@tranid int,
    @val money
SET @running_total = 0

FETCH NEXT FROM forward_cursor INTO @actid,@tranid,@val 
WHILE (@@FETCH_STATUS = 0)
BEGIN
     SET @running_total = @running_total + @val --برای هر رکورد Running Total محاسبه 
     INSERT @TotalTable VALUES(@actid, @tranid,@val, @running_total)
     FETCH NEXT FROM forward_cursor INTO @actid,@tranid,@val 
END

CLOSE forward_cursor
DEALLOCATE forward_cursor
--نمایش نتیجه
SELECT * FROM @TotalTable

یکی از عملگرهای مورد علاقه بنده که از sql 2005 معرفی شد و در طراحی برخی کوئری ها و گزارشات پیچیده، چاره ساز است cross apply می باشد که می توانیم از این عملگر در محاسبه Running Total به همراه نمایش اطلاعات اضافی به شکل زیر استفاده کنیم:

--3.Cross Apply (SQL2005)
select
    actid, tranid, val, RunningTotal
from TransactionHistory a 

 cross apply (select sum(val) as runningTotal
                from TransactionHistory b
                where  b.actid = a.actid
           AND b.tranid <= a.tranid
            ) as rt
			where actid=1
order by tranid

و اما به عنوان آخرین روش در محاسبه Running Total به همراه نمایش اطلاعات اضافی از Aggregate WINDOW FUNCTION به صورت زیر استفاده می کنیم:

--********************************************************************************* 
--SQL2005 از نسخه  Window Function با استفاده از  Running Total محاسبه          |
--*********************************************************************************

SELECT actid, tranid, val,
       SUM(val) OVER (PARTITION BY actid ORDER BY  tranid ) [Running Total]
     FROM TransactionHistory
	 	where actid=1

همانطور که در آموزش window function چیست اشاره شد، با استفاده از partition by در عبارت ()OVER در واقع رکوردهای خروجی را بر اساس یک فیلد(در اینجا actid) گروهبندی می کنیم(ماننذ group by) سپس دستور order by باعث مرتب سازی رکوردهای موجود در هر گروه می شود(tranid) نهایتاً تابع SUM بر روی هر کدام از گرو ه ها به صورت مجزا اعمال شده و مجموع فیلد val را برای هر گروه محاسبه می کند.

 با یک مقایسه اجمالی بین چهار روشی که جهت محاسبه Running Total به همراه نمایش اطلاعات اضافی استفاده شد می بینیم که از نظر تعداد خطوط اسکریپت ها و ساده بودن طراحی کوئری، روش آخر یعنی استفاده از Aggregate WINDOW FUNCTION دارای مزیت بر دیگر روش ها است و مهمترین عامل افزایش تعداد دستورات در روش های قدیمی، نیاز به استخراج اطلاعات اضافی بعد از گروهبندی است. از نظر کارایی لازم است در استفاده از WINDOW FUNCTION ها نکاتی را رعایت کنیم که به این مورد در ارزیابی کارایی WINDOW FUNCTION پرداخته می شود. 

---

WINDOW FUNCTION و پایان محدودیت های GROUP BY - جمع بندی

به عنوان یکی از نقطه ضعف های برخی از برنامه نویسان و یا متخصصین حوزه دیتابیس می توان به این نکته اشاره کرد که با تکرار استفاده از یک دستور، کنار گذاشتن آن و استفاده از دستورات جدید ممکن است مشکل به نظر برسد اما اگر به مزیت هایی مانند افزایش سرعت و کارایی، کاهش مدت زمان طراحی و ... توجه کنیم خواهیم پذیرفت که استفاده از دستورات و تکنولوژی جدید در حوزه برنامه نویسی و کار با دیتابیس همواره بر روش های قدیمی ارجحیت دارند.

البته این نکته را متذکر شوم که تنها بیان تئوریک بهینه بودن روش های جدید نسبت به دستورات قدیمی کافی نیست و باید به ارزیابی و مقایسه کارایی این روش ها با یکدیگر پرداخته شود که در رابطه با WINDOW FUNCTION این ارزیابی را در آموزشی جداگانه انجام خواهیم داد. در آموزش WINDOW FUNCTION و پایان محدودیت های GROUP BY سعی شد با استفاده از مثال محاسبه Running Total روش های قدیمی را با جدیدترین روش موجود یعنی استفاده از Aggregate WINDOW FUNCTION مقایسه کنیم.

 

• قبلی
• بعدی