WINDOW FUNCTION و توابع تحلیلی - قسمت اول

در آموزش توابع رتبه بندی با Ranking Window Function و مثال هایی از کاربرد آن آشنا شدیم. توابع رتبه بندی و تجمعی(Aggregate/Ranking Window Function ) از نسخه 2005  sql server معرفی شده اند اما در sql server 2012 امکانات و توابع جدیدی به Window Function اضافه شد که یکی از آن ها توابع تحلیلی(Analytic Window Function) بود که به دو دسته Window offset functions و window distribution functions دسته بندی می شوند. در اولین قسمت از آموزش WINDOW FUNCTION و توابع تحلیلی قصد داریم به معرفی و کاربرد Window offset functions بپردازیم.

پیشنهاد می شود در ابتدا آموزش WINDOW FUNCTION چیست و آموزش Rows و Range در WINDOW FUNCTION را مطالعه کنید.

---

WINDOW FUNCTION و توابع تحلیلی - قسمت اول

همانطور که می دانید مفهوم offset در زبان های برنامه نویسی و sql server همواره به محل قرارگیری(داده/رکورد) مربوط می شود. به عنوان یک مثال ساده می توان به paging اشاره کرد که در آن لازم است هر بار تعداد ثابتی رکورد(page size)، از یک محل متفاوت(متناسب با شماره صفحه) خوانده شود که یکی از روش ها برای انجام paging در sql server استفاده از offset_fetch است. اما Window offset functions امکان دسترسی به رکوردهای قبلی یا بعدی رکورد جاری را برای ما فراهم می کند که در ادامه به معرفی هر یک می پردازیم.

به منظور ارائه مثال عملی همراه با توضیح عملکرد هر یک از توابع، از جدول OrderItem که حاوی سوابق فروش آیتم های مختلف توسط فروشندگان به صورت زیر است استفاده می کنیم: 

--ایجاد جدول
CREATE TABLE OrderItem (
      OrderID smallint identity, 
      Supplier nvarchar(50),
      ProductType nvarchar(20), 
      OrderAmt money, 
      OrderDate char(10));

--درج رکوردهای تستی در جدول
INSERT INTO OrderItem VALUES
    (N'رضایی''s',N'سخت افزار',2121.09,'1396/01/12'),(N'کریمی',N'الکتریکی',12347.87,'1396/01/18'),
      (N'نادری',N'سخت افزار',999.99,'1396/01/22'), (N'محمدی',N'سخت افزار',6532.09,'1396/01/31'),
      (N'کشوری',N'ظروف آشپزخانه',3421.10,'1396/02/03'),(N'کشوری',N'ظروف آشپزخانه',1290.90,'1396/02/07'),
      (N'سلطانی',N'لبنی',12987.01,'1396/02/10'),(N'ربیعی''s',N'لوازم التحریر',43235.67,'1396/02/21'),
      (N'احسنی',N'ورزشی',89320.19,'1396/03/10'),(N'ترابی',N'ورزشی',53821.19,'1396/03/12'),
      (N'حسابی',N'لوازم التحریر',3245.59,'1396/03/14'),(N'مهری',N'بهداشتی',3331.59,'1396/03/19'),
      (N'تصدیق''s',N'پوشاک',2321.01,'1396/03/21'),(N'بندامیر',N'الکتریکی',3456.01,'1396/04/03'),
      (N'بندامیر',N'الکتریکی',1253.87,'1396/04/21'),(N'کشوری',N'ظروف آشپزخانه',3332.89,'1396/04/20'),
      (N'کیقبادی',N'منسوجات',345.11,'1396/04/22'),(N'هاشمی',N'سخت افزار',2347.09,'1396/05/03'),
      (N'ربیعی',N'لوازم التحریر',1243.78,'1396/05/08'),(N'احسنی',N'ورزشی',89876.90,'1396/05/10'),
      (N'پوری',N'لبنی',1231.22,'1396/05/10'),(N'تصدیقs''s',N'پوشاک',3246.98,'1396/05/12'),
      (N'عطار',N'بهداشتی',2325.98,'1396/05/14'),(N'مهری',N'بهداشتی',8786.99,'1396/05/21'),
      (N'نادری',N'سخت افزار',12341.09,'1396/05/22');

 

1. WINDOW FUNCTION و توابع تحلیلی - FIRST_VALUE

این تابع، اولین رکورد از بین مجموعه رکوردهای مرتب شده را بر می گرداند. فرمت کلی تابع First_Value به صورت زیر است: 

FIRST_VALUE ( [scalar_expression )   
    OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )  

به منظور نمایش اولین/کمترین میزان فروش در هر ماه، کوئری زیر را طراحی می کنیم:

 

دو نکته مهم در مورد کوئری بالا وجود دارد:

a.  تابع First_Value همواره اولین مقدار از داخل مجموعه رکورد/پارتیشن را بر می گرداند و این مقدار الزاماً کوچکترین/کمترین مقدار نیست و بستگی به نحوه مرتب سازی در order by داخل عبارت OVER دارد.

b. همانطور که در آموزش Rows و Range در WINDOW FUNCTION اشاره شد، فریم پیش فرض در کوئری بالا RANGE UNBOUNDED PRECEDING است.(با جایگذاری فریم، نتیجه را مقایسه کنید)

 2. WINDOW FUNCTION و توابع تحلیلی - LAST_VALUE

 این تابع، آخرین رکورد از بین مجموعه رکوردهای مرتب شده را بر می گرداند. فرمت کلی تابع Last_Value به صورت زیر است:

LAST_VALUE ( [scalar_expression )   
    OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )  

همانطور که مشخص است، فرمت این تابع با First_Value یکسان است. اما تفاوت مهم بین این دو تابع در تعیین فریم است. به نحوی که اگر در تابع Last_Value فریم را تعیین نکنیم و از فریم پیش فرض یعنی RANGE UNBOUNDED PRECEDING استفاده شود. در این حالت Last_Value برای یک رکورد، برابر با مقدار همان رکورد است! زیرا بایستی آخرین رکورد بین رکورد جاری و اولین رکورد انتخاب شود که در واقع همان رکورد جاری می شود. این مورد در کوئری زیر قابل مشاهده است:

 

برای تولید نتیجه دلخواه، یعنی نمایش آخرین مقدار موجود به ازای فیلد مورد نظر در هر پارتیشن، باید از فریم  rows between unbounded preceding and unbounded following استفاده کنیم به این معنی که آخرین مقدار را از بین اولین رکورد تا آخرین رکورد موجود در گروه نمایش بدهد:

نکته: خروجی توابع تجمعی Min و Max با توابع تحلیلی First_value و Last_Value یکسان نیست.

ممکن است با مشاهده خروجی کوئری های بالا اینگونه برداشت شود که همواره تابع First_Value کوچکترین مقدار و تابع Last_Value بزرگترین مقدار فیلد مورد نظر را نمایش می دهد و عملکردی مشابه با توابع تجمعی Min و Max دارند. اما بین این دو تابع تفاوت های زیر وجود دارد:

     1. با اجرای توابع First_value و Last_Value به جزئیات اطلاعات دسترسی داریم اما در توابع Min و Max اینگونه نیست.

     2. در توابع First_value و Last_Value به ترتیب اولین و آخرین رکورد بر اساس نوع مرتب سازی انتخاب می شود اما در توابع Min و Max کوچکترین و بزرگترین مقدار.

کوئری زیر تفاوت خروجی این توابع را نمایش می دهد:

 همانطور که در شکل بالا مشخص است با توجه به مرتب سازی نزولی بر اساس فیلد OrderAmt، خروجی تابع First_Value(اولین مقدار) برابر با تابع Max(بزرگترین مقدار) و خروجی تابع Last_Value(آخرین مقدار) برابر با تابع Min(کوچکترین مقدار) شده است. 

 3. WINDOW FUNCTION و توابع تحلیلی - LEAD

از این تابع برای دستیابی به مقدار یک فیلد در رکوردهای بعدی استفاده می شود. فرمت کلی آن به صورت زیر است:

LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

پارامترهای موجود در فرمت بالا به صورت زیر هستند:

• value_expression : نام ستون مورد نظر
• Offset: تعداد رکورد بعد از رکورد جاری
• Default: مقدار پیش فرض در صورت خارج از رنج بودن Offset
• query_partition_clause: گروهبندی روی ستون خاص

به عنوان یک مثال از کاربرد تابع LEAD در جدول OrderItem، اختلاف فروش یک کالا را با میزان فروش بعد از آن محاسبه می کنیم. 

عبارت (LEAD(OrderAmt,1,0 در کوئری بالا به مقدار فیلد OrderAmt در یک رکورد(عدد یک) بعد از رکورد جاری اشاره دارد. پارامتر سوم(عدد صفر) به این معنا است که در صورت موجود نبودن رکورد بعدی(رکوردهای پایانی در هر پارتیشن) مقدار صفر را لحاظ کند.

نکته: در زمان اجرای واقعی تابع LEAD به LAST_VALUE تبدیل می شود.

به عنوان مثال کوئری بالا در زمان اجرا به صورت زیر تبدیل می شود:

--LAST-VALUE با  LEAD جایگزینی تابع  
SELECT Supplier, ProductType, OrderDate,OrderAmt,
OrderAmt - LAST_VALUE(OrderAmt) OVER(PARTITION BY ProductType 
           ORDER BY substring(OrderDate,6,2) ROWS Between 1 FOLLOWING AND 1 FOLLOWING) AS diffnext
		  
FROM OrderItem

 

 4. WINDOW FUNCTION و توابع تحلیلی - LAG

از این تابع برای دستیابی به مقدار یک فیلد در رکورد/های قبلی استفاده می شود. فرمت کلی آن به صورت زیر است:

LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

پارامترهای موجود در این تابع نیز مانند تابع LEAD هستند. به عنوان مثالی از کاربرد این تابع، این بار اختلاف فروش یک کالا را با میزان فروش قبل از آن در جدول OrderItem محاسبه می کنیم:

 در تابع LAG بر خلاف تابع LEAD در صورتیکه هیچ رکوردی قبل از یک رکورد موجود نباشد، مقدار تعیین شده در تابع(در اینجا مقدار صفر) جایگزین آن می شود. که این مورد در تصویر بالا به ازای رکوردهای ابتدایی هر پارتیشن رخ داده است.

نکته: مانند تابع LEAD، در زمان اجرای واقعی تابع LAG نیز به LAST_VALUE تبدیل می شود اما با فریمی متفاوت.

--LAST-VALUE با  LAG جایگزینی تابع  
SELECT Supplier, ProductType, OrderDate,OrderAmt,
OrderAmt - LAST_VALUE(OrderAmt) OVER(PARTITION BY ProductType 
           ORDER BY substring(OrderDate,6,2) ROWS Between 1 PRECEDING AND 1 PRECEDING) AS diffprev
		  
FROM OrderItem

با توضیحات و مثال های ارائه شده، با نحوه عملکرد و تفاوت توابع  Window offset functions آشنا شدیم. به عنوان یک مثال تکمیلی، مقایسه مجموع فروش ماه جاری با مجموع فروش یک ماه قبل و یک ماه بعد از آن را به همراه میانگین فروش سه ماهه بطور جداگانه با استفاده از توبع First_Value ،Latst_Vale و Lead ،Lag انجام می دهیم.

--**************************************************************************************************************
-- مقایسه مجموع فروش ماه جاری با مجموع فروش یک ماه  قبل و یک ماه بعد از آن و میانگین فروش سه ماهه    |
--First_Value/Latst_Vale و Lead/Lag با استفاده از توابع                                                      |
--**************************************************************************************************************
--First_Value/Latst_Vale روش اول با استفاده از توابع  
--1. ایجاد گروه ها
--2.Select محاسبه توابع داخل دستور 
SELECT substring(OrderDate,6,2) month, SUM(OrderAmt) AS month_amount,
FIRST_VALUE(SUM(OrderAmt)) OVER (ORDER BY substring(OrderDate,6,2) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                                      AS previous_month_amount,--میزان فروش ماه قبل نسبت به ماه جاری
LAST_VALUE(SUM(OrderAmt)) OVER (ORDER BY substring(OrderDate,6,2) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                                      AS next_month_amount--میزان فروش ماه بعد نسبت به ماه جاری
, AVG(SUM(OrderAmt)) OVER ( ORDER BY substring(OrderDate,6,2)
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg3months --میانگین فروش سه ماهه
FROM OrderItem 
GROUP BY substring(OrderDate,6,2) ORDER BY substring(OrderDate,6,2)

 --2.LEAD/LAG روش دوم با استفاده از 
SELECT substring(OrderDate,6,2) month, SUM(OrderAmt) AS month_amount,
LAG(SUM(OrderAmt), 1,0) OVER (ORDER BY substring(OrderDate,6,2)) AS previous_month_amount,
LEAD(SUM(OrderAmt), 1,0) OVER (ORDER BY substring(OrderDate,6,2)) AS next_month_amount
, AVG(SUM(OrderAmt)) OVER
       ( ORDER BY substring(OrderDate,6,2)
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg3months

FROM OrderItem 
GROUP BY substring(OrderDate,6,2) ORDER BY substring(OrderDate,6,2); 

نتیجه اجرای کوئری بالا:

مقایسه خروجی و بررسی آن، به عنوان تمرین بر عهده شما باشد.

---

WINDOW FUNCTION و توابع تحلیلی - قسمت اول

همانطور که در ابتدای آموزش اشاره شد توابع تحلیلی(Analytic Window Function) به دو دسته Window offset functions و window distribution functions تقسیم می شوند. در این آموزش به عنوان اولین قسمت به معرفی و بررسی  Window offset functions پرداختیم و با ارائه مثال هایی، قدرت این توابع را در طراحی کوئری هایی که نیاز به حرکت بین رکوردها و دسترسی به داده های قبل و یا بعد از رکورد جاری دارند را مشاهده کردیم. در دومین قست از آموزش WINDOW FUNCTION و توابع تحلیلی به معرفی و بررسی window distribution functions پرداخته خواهد شد.