Window Functions چیست؟
Window Functions یا همان توابع پنجره ای جز مباحث پیشرفته در کویری نویسی با زبان SQL است. Window Functions انجام تحلیل های زیادی بر روی دیتا را ممکن می سازد و برای افرادی که میخواهند در حوزه هوش تجاری, تحلیل داده و یا مهندسی داده مشغول به کارشوند؛ بسیار ضروری است. ابتدا به مفهوم Window Functions می پردازیم. اگر هنوز با زبان SQL و SQL Sever آشنایی ندارید؛ می توانید برای یادگیری نصب SQL Server و یا انواع داده ها و سایر موارد به این لینک مراجعه کنید.
به طور کلی پنجره (window), مجموعه ای از ردیف ها در خروجی کویری SQL (Resultset) و یا جدول (Table) است. بسته به نحوه نوشتن کویری SQL, خروجی (Resultset) میتواند شامل یک و یا چندین پنجره (window) باشد. درتوابع پنجره ای (Window Functions), ایجاد پنجره با استفاده از عبارت OVER() انجام میشود. از طرفی توابع (Functions) نیز موجموعه ای از توابع تعریف شده در SQL مثل SUM,AVG,MIN,MAX می باشد. بنابراین اعمال Window Functions به معنای اعمال برخی از توابع بر روی یک یا چندین پنجره است. به عنوان مثال می توانیم مجموع فروش (Function) به ازای هر مشتری (Window) را محاسبه کنیم. تفاوتی که در اینجا میان توابع پنجره ای و استفاده از GROUP BY وجود دارد در این است که توابع پنجره ای اصل دیتا را حفظ می کند اما در استفاده از GROUP BY اصل داده وردیف ها حفظ نمی شود. به عنوان در مثال زیر که مجموع qty بر اساس هر مشتری (Custid) است؛ در استفاده از توابع پنجره ای، اصل رکوردهای دیتا حفظ می شود و در هر ردیف مجموع فروش به ازای آن مشتری تکرار می شود.
اما در استفاده از GROUP BY اگر بخواهیم مجموع qty به ازای هر مشتری (Custid) را محاسبه کنیم؛ نمی توانیم به نحوی در SQL کویری بنویسیم که اصل داده ها و ردیف ها در خروجی نمایش داده شوند و خروجی به صورت تصویر زیر خواهد بود.
اجزای توابع پنجره ای (Window Functions)
همانطور که قبلا نیز اشاره کردیم توابع پنجره ای از دو قسمت Window و Function تشکیل شده است. هر چیزی در قسمت OVER() قرار گیرد تعیین کننده پنجره ها (Window) است و هر تابعی که در بخش Function استفاده شود، تعیین کننده نوع محاسبه است. در قسمت تابع، پرکاربردترین موارد شامل ROW_NUMBER()، RANK()، DENSE_RANK()، SUM(Column)، LAG(Column) و LEAD(Column) است.
در قسمت پنجره دو عبارت می تواند قرار گیرد. این دو عبارت در پرانتز قرار می گیرند. عبارت اول که در اکثر موارد ضروری است؛ ORDER BY می باشد. و عبارت دوم که صروری هم نیست اما تعیین کننده تعداد پنجره ها است؛ PARTITION BY است . به عنوان مثال ROW_NUMBER()OVER(PARTITION BY Custid ORDER BY Orderdate) برای هر مشتری (Custid) به ترتیب ستون تاریخ سفارش (Orderdate) شماره سطر ایجاد میکند و خروجی آن به صورت زیر است. در توصیر زیر ستون RN با استفاده از ROW_NUMBER ایجاد شده است.
پرکاربردترین توابع پنجره ای (Window Functions)
برخی از پرکاربردترین توابع پنجره ای که استفاده بسیاری در فراند تحلیل داده و پاکسازی داده ها دارند؛ شامل ROW_NUMBER، RANK، DENSE_RANK، LAG، LEAD و SUM است. در ادامه برخی از کاربردهای این توابع را بررسی میکنیم.
تابع ROW_NUMBER
این تابع برای ایجاد شماره سطر استفاده می شود. کاربرد دیگری که برای این تابع وجود دارد؛ یافتن داده های تکراری است. در این تابع که شکل کامل آن ROW_NUMBER()OVER(PARTITION BY Column ORDER BY Column) است؛ قسمت ORDER BY اجباری بوده اما قسمت PARTITION BY اجباری نیست.
تابع RANK
همانظور که از نام آن نیز مشخص است از این تابع برای رتبه بندی استفاده می شود. در این تابع که شکل کامل آن RANK()OVER(PARTITION BY Column ORDER BY Column) است؛ قسمت ORDER BY اجباری بوده اما قسمت PARTITION BY اجباری نیست. رتبه بندی این تابع فرمول خاصی دارد که ممکن است در هر جا مناسب نباشد. به عنوان مثال در این تابع اگر 4 رتبه 1 وجود داشته باشد، رتبه بعدی که توسط تابع ایجاد می شود؛ شماره 5 خواهد بود.
تابع DENSE_RANK
در این تابع نیز همانطور که از نام آن نیز مشخص است رتبه بندی صورت میگیرد. در این تابع که شکل کامل آن DENSE_RANK()OVER(PARTITION BY Column ORDER BY Column) است؛ قسمت ORDER BY اجباری بوده اما قسمت PARTITION BY اجباری نیست.در این تابع بر خلاف تابع RANK رتبه ها به صورت مورد انتظار ایجاد می شوند. یعنی اگر اگر 4 رتبه 1 وجود داشته باشد، رتبه بعدی که توسط تابع ایجاد می شود؛ شماره 2 خواهد بود.
توابع LAG و LEAD
این توابع بیشتر برای محاسبه تاریخ قبل و بعد از یک تاریخ خاص در دیتا استفاده می شوند. تابع LAG(column) مقدار قبلی ستون Column و تابع LEAD(column) مقدار بعدی ستون Column در سطر جاری را بر میگرداند. در این تابع نیز قسمت ORDER BY اجباری بوده اما قسمت PARTITION BY اجباری نیست. شکل کامل این توابع به صورت LAG(Column)OVER(PARTITION BY Column ORDER BY Column) و یا LEAD(Column)OVER(PARTITION BY Column ORDER BY Column) است.
توابع SUM, MAX,..
این توابع برای محاسبه مجموع و میانگین و مینیمم و ماکزیمم استفاده می شود. شکل این توابع به صورت SUM(Column)OVER(PARTITION BY Column ORDER BY Column) است. به جای SUM هرکدام از توابع MIN,MAX,AVG استفاده کرد. در این توابع در قسمت OVER در پرانتز می توانیم هیچ عبارتی را قرار ندهیم.
سوالات تمرین
در این قسمت تمرینات برای کار با توابع پنجره ای (Window Functions)، قرار داده شده است. برای انجام این تمرینات، از دیتابیس TSQL2012 استفاده کنید. برای دانلود این دیتابیس بر روی TSQL2012 کلیک کنید.
- با استفاده از Window Functions، در جدول SALES.ORDERS کویری بنویسید که به ترتیب ستون ORDERID شماره سطر ایجاد کند.
- با استفاده از Window Functions، در جدول SALES.ORDERS کویری بنویسید که به ترتیب ستون ORDERID شماره سطر ایجاد کند.شماره سطر به ازای هر مشتری (cusitd) مجدد از 1 شروع شود.
- با استفاده از Window Functions،در جدول SALES.ORDERS کویری بنویسید که برای هر مقدار از ستون تاریخ سفارش (orderdate) تاریخ سفارش قبلی را برگرداند.
- در کویری شماره 3 تغییری ایجاد کنید که در مقابل تاریخ سفارش (orderdate) هر مشتری، تاریخ سفارش (orderdate) قبلی همان مشتری را برگرداند.
- در کویری شماره 3 تغییری ایجاد کنید که در مقابل تاریخ سفارش (orderdate) هر مشتری، تاریخ سفارش (orderdate) بعدی همان مشتری را برگرداند.
- کویری بنویسید ابتدا با استفاده از Window Functions اختلاف زمانی بین دو سفارش متوالی را در جدول SALES.ORDERS به روز مجاسبه کند و سپس میانگین اختلاف زمانی بین دو سفارش متوالی را محاسبه کند.( خروجی قسمت دوم یک عدد است)
- کویری بنویسید ابتدا با استفاده از Window Functions اختلاف زمانی بین دو سفارش متوالی به ازای هر مشتری (custid) را در جدول SALES.ORDERS به روز مجاسبه کند و سپس میانگین اختلاف زمانی بین دو سفارش متوالی را به ازای هر مشتری (custid) محاسبه کند.( خروجی کویری تعدادی ریدف در دو ستون است.)
- در جدول Sales.OrderDetails با استفاده از Window Functions،کویری بنویسید که همه ستونهای جدول را نشان دهد و یک ستون نیز که در همه رکورد ها مجموع کل ستون qty است را نمایش دهد (خروجی کویری کل ستونهای جدول است و در ستون آخر برای همه ردیف ها عدد 51317 نمایش داده می شود)
- در جدول Sales.OrderDetails با استفاده از Window Functions،کویری بنویسید که همه ستونهای جدول را نشان دهد و یک ستون نیز که در همه رکورد ها مجموع ستون qty برای همان productid را نمایش دهد.
- در جدول SALES.ORDERS کویری بنویسید که در هر سال سفارش (ستون orderdate) مشتریان برتر (custid) را بر اساس مجموع ستون freight نشان دهد.
پایان
دیدگاه خود را بنویسید