آنچه در این دوره خواهید آموخت

اگر شما قصد دارید در SQL حرفه ای شوید و بتوانید جستجوهای پیشرفته را انجام دهید یا داده ها را به صورت حرفه ای آنالیز کنید، یادگیری Window Functionها بسیار اهمیت دارد.

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

همچنین برای افرادی که قصد دارند به عنوان Senior Developer یا Technical Lead در شرکتی فعالیت کنند حتما یکی از مواردی که در بخش سوال‌های دیتابیس و MySQL مورد ارزیابی قرار می‌گیرد مهارت کار با Window Functionهاست.

موارد استفاده ی Window Functionها بسیار گسترده است و می توانند برای طیف گسترده ای از وظایف تحلیلی و گزارشی اعمال شوند. برخی از موارد استفاده رایج عبارتند از:

• محاسبه مجموع.
• یافتن N مقادیر بالا در یک محدوده از داده ها.
• شناسایی شکاف ها یا جزایر در توالی داده ها.
• تعیین صدک در داده ها
• تجزیه و تحلیل روندها در طول زمان یا درون گروه ها.
• و …

این موارد استفاده، قدرت Window Functionها را در حل مسائل تحلیلی پیچیده با استفاده از  Queryهای SQL نشان می دهد.

این آموزش برای کامل و کاربردی بودن به عنوان یک دوره پیشرفته تقدیم می شود. در این بخش سعی می کنم مروری بر اصلی ترین مطالبی که در دوره آموزش کامل Window Function ها در SQL یاد خواهید گرفت داشته باشیم.

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

آشنایی با مفاهیم Window Functionها

Window Functionها ویژگی قدرتمندی در SQL است که به شما این امکان را می دهد تا محاسبه ها و تحلیل های تان روی داده ها را روی زیرمجموعه ای از مجموعه نتایج یک کوئری که به ردیف خاصی مرتبط هستند انجام دهید. Window Functionها می توانند پرس و جوهای پیچیده را ساده کرده و شما را قادر به حل مسائل مختلف تحلیلی کنند.

عبارت Window Function به طور دقیق و مشخص توانایی این نوع توابع در ایجاد پنجره‌های تحلیلی برای داده‌ها را نمایش می دهد.

آموزش نحوه ی نوشتن (Syntax) توابع Window

Syntax اولیه برای استفاده از Window Functionها در MySQL به صورت زیر است:

FUNCTION_NAME() OVER (PARTITION BY column1 ORDER BY column2 [ROWS | RANGE] BETWEEN frame_start AND frame_end)

• FUNCTION_NAME همان Window Function خاصی است که می خواهید استفاده کنید (به عنوان مثال، SUM()، RANK()، ROW_NUMBER ).
• PARTITION BY مجموعه نتایج را بر اساس یک ستون به پارتیشن ها یا گروه هایی تقسیم می کند که Window Function در هر پارتیشن عمل می کند.
• ORDER BY ترتیب پردازش سطرها در هر پارتیشن را مشخص می کند.
• ROWS یا RANGE فریم یا محدوده پنجره ای (Window Frame) از ردیف های داده را تعریف می کند که Function روی آنها کار می کند.
• BETWEEN شروع و پایان Window Frame را مشخص می کند.

Window Function های رایج و پرکاربرد

برخی از Window Function های رایج عبارتند از:

ROW_NUMBER: یک عدد صحیح منحصر به فرد به هر ردیف در یک پارتیشن اختصاص می دهد.

RANK: یک رتبه منحصر به فرد به هر سطر در یک پارتیشن اختصاص می دهد و Tie (پیوند) ها را به خوبی مدیریت می کند.

DENSE_RANK: شبیه به RANK اما بدون شکاف در رتبه بندی برای مقادیر همسو عمل می کند.

SUM: مجموع یک ستون مشخص شده در Window Frame را محاسبه می کند.

AVG: میانگین یک ستون مشخص شده در Window Frame را محاسبه می کند.

مثال های عملی

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

نکات Optimization در Window Functions

برای استفاده از Window Functionها در SQL، نکات بهینه سازی بسیار مهمی هم وجود دارد که در بخشی مجزا به آنها خواهیم پرداخت. با رعایت کردن این نکته های مهم، کوئری های حرفه ای تر و سریع تری خواهید داشت و دیتابیس شما در شرایط بهینه تری کار خواهد کرد.

Window Frame Specification

Window Frame Specification یکی از موضوعات عمیق در این دوره است که اغلب در ROWS یا RANGE عبارت OVER یک Window Function تعریف می‌شود و زیرمجموعه ردیف‌های داخل یک پارتیشن را تعیین می‌کند که Window Function روی آن کار می‌کند. این ویژگی به عنوان یک مرز پویا برای محاسبات نسبت به ردیف فعلی عمل می کند و مشخص می کند روی کدام ردیف ها باید Window Function مورد نظرمان اعمال شود.

به عنوان مثال، تعیین فریمی مانند "ROWS BETWEEN 2 PRECEDING AND 2 FLOOWING" شامل دو ردیف قبل و دو ردیف بعد از ردیف فعلی است که به شما امکان می دهد محاسباتی را انجام دهید که به این زیر مجموعه داده خاص بستگی دارد.

Window Frame Specification یکی از ویژگی های بسیار مهم از Window Function است که کنترل دقیقی بر زیر مجموعه داده های مورد استفاده در عملیات تحلیلی ارائه می دهد و این توابع را به ابزاری قدرتمند برای کوئری‌ها و تجزیه و تحلیل SQL تبدیل می کند.

استفاده از چند Window Function در یک کوئری

استفاده از چند Window Function در یک کوئری شما را قادر می سازد تا عملیات تحلیلی مختلفی را به طور همزمان بر روی یک مجموعه داده مشابه انجام دهید. این بدان معنی است که می توانید معیارها یا رتبه بندی های مختلف را در هر پارتیشن یا Window Frame بدون نیاز به کوئری جداگانه محاسبه کنید.

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

مدیریت مقادیر NULL در Window Function

مدیریت مقادیر NULL در Window Functionها برای تجزیه و تحلیل دقیق و معنادار بسیار مهم است. هنگامی که مقادیر NULL در Window Frame وجود دارند، می توانند بر محاسبات تأثیر بگذارند. به‌طور پیش‌فرض، اکثر Window Functionها، NULL‌ها را به‌عنوان مقادیری با رتبه پایین‌تر در نظر می‌گیرند، که به طور بالقوه بر توابع رتبه‌بندی مانند RANK و DENSE_RANK تأثیر می‌گذارند. با این حال، می‌توانید نحوه برخورد با مقادیر NULL را با استفاده از گزینه IGNORE NULLS یا RESPECT NULLS در Window Frame Specification کنترل کنید. این کار به شما این امکان را می دهد که تجزیه و تحلیل خود را به گونه ای انجام دهید که درنظر گرفتن NULL ها با نیازهای خاص شما مطابقت داشته باشد همچنین می توانید روی نتایج تحلیلی تان وقتی با مجموعه داده هایی کار می کنید که شامل مقادیر تعریف نشده هستند، اطمینان بیشتری داشته باشید.

سوالهای مصاحبه ی شغلی

Window Functionها اغلب موضوع بحث در مصاحبه های شغلی مرتبط با پایگاه داده هستند، به ویژه برای نقش هایی که شامل تجزیه و تحلیل داده ها، مدیریت پایگاه داده یا توسعه SQL می شود. همچنین برای شرکت ها و مجموعه های داده محور، همیشه در مصاحبه های شغلی موقعیت های شغلی Senior و Lead، سوالهایی در ارتباط با Window Functionها از شما پرسیده خواهد شد. در این بخش به سوال های تشریحی و تمرین های مختلفی در این زمینه خواهیم پرداخت.

این سوالات طیفی از موضوعات مرتبط با توابع پنجره، از مفاهیم اولیه آنها تا کاربردهای عملی را پوشش می دهد. آماده شدن برای این سوالات باید به شما کمک کند مهارت خود را در استفاده از Window Functionها در مصاحبه های SQL نشان دهید.