Windows Function

توابع تجمعی SQL‌ مانند AVG,SUM,COUNT که اغلب به همراه دستور GROUP BY به کار می‌روند، سطرها را مطابق مقادیر ستون موجود در بخش GROUP BY گروه‌بندی می‌کنند و سپس توابع تجمعی را بر روی گروه‌ها اعمال کرده و به ازای هر گروه، یک خروجی را تولید می‌کنند. در مقابل، در بسیاری از سناریوها نیاز داریم هر سطر با آمار و داده‌های مجموعه‌ای مشابه از سطرها (مثلا تمام رکوردهای یک ماه ) مقایسه شده و محاسباتی برپایه آنها انجام شده، به کاربر نمایش داده شود.

در این صورت به توابعی نیاز داریم که :

در Window Function، بر روی رکوردهای جدول گروه بندی انجام می شود و عملیات هایی مطابق آن گروه بندی می توانیم انجام دهیم. تفاوت با آن Group By این است که در Group By عملیات ما طبق یک گروه می باشد ولی در اینجا عملیات ما بر روی یک Window می باشد. (به ادامه مطلب مراجعه نمائید)

فرض کنید ما داده‌ها مربوط به ارائه هر استان رو به صورت زیر داریم و میخوایم مجموع ارائه رو هر شهر رو محاسبه کنیم .به شکل زیر دقت نمائید.

در این مثال مقدار کل فروش‌رسانی شهر البرز 37000 و شهر شیراز 13000 و شهر تهران برابر 55000 می باشد . اکه بخوایم کوئری ای بنویسیم که مجموع هر شهر رو محاسبه کنه میتونیم با استفاده از تابع sum و group by ساده این مقدار رو به دست بیاریم. به شکل زیر دقت کنید:

مشکلی که ما اینجا داریم این هستش که یه سری دیتا ها رو از دست دادیم و فقط داده‌ها شهر و مجموع مقدار سفارش رو داریم.برای اینکه بخوایم این مشکل رو حل کنیم از window function ها استفاده میکنیم.در Window Function، بر روی رکوردهای جدول گروه بندی انجام می شود (اصطلاحا یک پنجره در نظر گرفته می شود ) و میتوانیم عملیات هایی مطابق آن گروه بندی انجام دهیم و به صورت زیر نوشته میشه :

در ادامه به بیان هر یک از توابع پنجره می‌پردازم:

1. Aggregate Window Functions توابع تجمیعی پنجره :

2. Ranking Window Functions توابع رتبه‌بندی پنجره :

3. Value Window Functions توابع مقدار پنجره :

این توابع پنجره به شما اجازه می‌دهند برای هر رکورد، محاسباتی را بر روی نواحی مشخصی از داده‌ها انجام دهید، بدون نیاز به تقسیم داده‌ها به زیرگروه‌ها مجزا. این قابلیت به شما فرصت می‌دهد تا تحلیل‌های پیچیده‌تری را روی داده‌های خود اعمال کنید.

با بعضی از فانکشن ها حتما آشنایی داریم مثل sum,avg,min,max ,...و لی ممکنه با بعضی از فانکشن ها کمتر آشنایی داشته باشیم مثل

LEAD,LAG,NTILE ,... درادامه به این فانکشن ها پرداخته می شود.

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

تابع LAG : دستیابی به مقدار یک فیلد در سطرها قبل از سطر جاری

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

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

تابع LEAD هم مثل تابع LAG می باشد با این تفاوت که به رکورد بعد اشاذه می کند.

این توابع به شما کمک می کنند تا اولین و آخرین رکورد را در یک پارتیشن یا کل جدول شناسایی کنید.در این مثال میخواهیم اولین و آخرین سفارش هر شهر را پیدا کنیم.

این تابع یک عدد منحصر به فرد را برای هر ردیفی که توسط order by واکشی شده است اختصاص می دهد. توابع Ranking باعث می شود تا به هر یک از سطرهای جدول یک مقدار به عنوان امتیاز اختصاص داده شود. این مقدار ممکن است منحصر به هر سطر باشد یا این که چند سطر می تواند دارای مقدار مشابه باشد.