نگاهی سریع به SQL Window Functions

در زندگی روزانه برخی از رویدادها و درک برخی مفاهیم هستند که بعد از آشنایی با آنها، زندگی به دو قسمت قبل و بعد تقسیم می‌شود. در دنیای فنی این نقاط عطف، مفاهیم، کتابخانه‌ها و ابزاری هستند که بعد از آشنایی با آنها تا مدتها انگشت حسرت خواهیم گزید که ای دل غافل، چرا قبلاً با این موضوع، آشنا نشده بودم.

هر چند در گذشته در مقاله شما هم هنوز از SQL-92 استفاده می‌کنید؟ به ضرورت آشنایی با مفاهیم جدید SQL پرداخته بودم اما بخش SQL Window Functions از دید خودم هم مغفول مانده بود. قابلیتی که در SQL-2003 به مجموعه استاندارد SQL اضافه شده است و امروزه در تمامی بانک‌های اطلاعاتی رابطه‌ای رایج، قابل استفاده و بسیار کار راه‌انداز است. سناریوهای زیر را در نظر بگیرید :

• میزان فروش هر ماه را به دست آورده‌اید، اما درصد فروش ماهیانه ( از فروش کل سال ) را هم لازم دارید.
• تعداد کلیک هر محصول را به صورت روزانه دارید و می‌خواهید در هر روز، رتبه فروش هر کالا را محاسبه کنید.
• میزان پرداختی به هر کارمند از ابتدای سال را به صورت تجمعی نیاز دارید (هر ماه به مجموع ماه‌های قبل اضافه شود )
• تیم قهرمان لیگ برتر در بیست سال اخیر را قرار است نمایش دهید اما همزمان، تیم دوم و اختلاف امتیاز این تیم با تیم دوم را هم می خواهید نمایش دهید.
• قصد اعلام نمرات دانشجویان را دارید اما میخواهید هر دانشجو رتبه مطلق و رتبه نرمال شده خود در کلاس را ببیند که رتبه مطلق، همان ترتیب نزولی نمرات است و رتبه نرمال شده هم به این صورت اعمال می شود که تمام نمرات بین دو عدد ۹۰ و ۱۰۰، دو عدد ۸۰ و ۹۰ و … در یک رده قرار میگیرند. یعنی نفری که ۹۸ گرفته است و نفری که ۹۲ گرفته است هر دو رتبه نرمال یک را مشاهده می‌کنند اما اولی رتبه مطلق ۱ و دومی رتبه مطلق ۲ را خواهد دید.

تمام این سناریوها که در دنیای امروز و نیازهای رو به رشد اطلاعاتی آن، برای کاربردهای روزانه بسیار محتمل به نظر می رسند، با توابع پنجره‌ای SQL‌ به راحتی قابل پیاده‌سازی و اجرا هستند. کاری که انجام آن با توابع تجمعی SQL‌ پیچیده خواهد بود .

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

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

• سطرها را به گروه‌ها یا بخش‌هایی تقسیم کنند (‍Partition)
• در هر بخش، سطرها را بر اساس معیار مورد نیاز ما مرتب کنند (Order By)
• بعد از مرتب‌سازی، مبنای محاسبات و اعمال تابع را مشخص کند (از ابتدا تا انتها، از ابتدای بخش تا رکورد جاری، از رکورد جاری تا انتهای بخش – Window Frame)
• محاسبات یا تابع مورد نیاز را بر روی بخش‌های مرتب ایجاد شده و براساس مبنای مشخص شده، انجام دهد (Window Function)

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

در این جا، مبنای گروه‌بندی یا Partitioning کل رکوردها (اندازه Window برابر کل رکوردها)، مرتب سازی بر اساس فیلد Day و تابعی که باید محاسبه شود تابع SUM است (که در اینجا مبنای اعمال آن از ابتدای پارتیشن تا رکورد جاری است). کد زیر، دستور SQL معادل خروجی تصویر فوق به کمک توابع پنجره‌ای است :

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

• تابع پنجره‌ای درقسمت Select نوشته می‌شود.
• کلمه کلیدی Over که نشان‌دهنده استفاده از توابع پنجره‌ای است.
• کلمه کلیدی Partition By درون Over که ملاک اصلی گروه‌بندی سطرها را مشخص می‌کند که البته در این مثال، اعمال نشده است بنابراین کل سطرها، یک گروه یا بخش را تشکیل می‌دهند.
• کلمه کلیدی Order By که ترتیب سطرها درون هر پارتیشن (بخش) را مشخص میکند .
• کلمه کلیدی Rows‌ که مبنای محاسبه و اندازه پنجره را مشخص می‌کند (Window Frame‌ که اگر مشخص نشود، تمام سطرهای یک گروه و یا بسته به تابع مورد استفاده، از ابتدای پارتیشن تا رکورد جاری خواهد بود) و در مثال بعدی قابل مشاهده است.

قبل از اینکه مثالهای دیگری را با هم ببینیم، بهتر است روندی که هنگام اعمال و استفاده از توابع پنجره‌ای در پشت صحنه اتفاق می‌افتد را به صورت تصویری مشاهده کنیم. فرض کنید می خواهیم میانگین دو روزه زمان رکاب‌زنی هر دوچرخه‌سوار را نمایش دهیم. در این حالت، گروه بندی براساس شناسه دوچرخه‌سوار، مرتب سازی براساس تاریخ و تابع پنجره‌ای مورد نیاز، تابع میانگین خواهد بود. روالی که پشت صحنه انجام می‌شود به صورت زیر است ( عبارت ROWS BETWEEN 1 PRECEDING AND CURRENT ROW تعیین کننده اندازه قاب پنجره مورد نیاز برای اعمال تابع فوق خواهد بود.) :

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

در اینجا باز هم گروه‌بندی یا Partitioning نداریم اما با استفاده از Rows به تعیین بازه اعمال تابع تجمعی به ازای هر سطر می پردازیم. کد زیر خروجی بالا را برای ما تولید خواهد کرد :

همانطور که احتمالاً حدس زده‌اید کلمه کلیدی ‍PRECEDING برای بیان سطرهای قبل از سطر جاری به کار می رود و کلمه مقابل آن، Following‌ است که تعیین کننده سطرهای بعد از سطر جاری برای اعمال تابع تجمعی است.

مثالی دیگر را با هم مرور میکنیم. یک سایت تجاری داریم که چندین کمپین تبلیغاتی بر روی آن در حال اجراست. تعداد کلیک‌ها و استقبال کاربران در هر روز را ذخیره کرده‌ایم. حال می‌خواهیم تعداد کل کلیک‌ها به ازای هر کمپین را به دست آوریم :

این کار را به راحتی و به کمک عبارت Partition By‌ به صورت زیر انجام می‌دهیم :

اگر میخواستیم همین کار را با SQL‌ رایج انجام دهیم باید کد زیر را مینوشتیم :

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

حال فرض کنید در همین مثال، می خواهیم بدانیم در روز اول هر کمپین، چه تعداد کلیک داشته ایم. (کدام کمپین‌ها در برخورد اول، مشتری پسندتر بوده‌اند). در اینجا ملاک گروه‌بندی ما کمپین و مرتب سازی ما براساس تاریخ خواهد بود . تابع First_Value به صورت زیر می‌تواند پاسخگوی این نیاز ما باشد :

اگر به میزان کلیک روز قبل و بعد هم در کنار اطلاعات روز جاری نیاز داریم ، میتوانیم از دو تابع LAG و LEAD که اولی سطر قبلی و دومی سطر بعدی را برمیگرداند ، استفاده کنیم (پیش فرض آن یک سطر قبل یا بعد است اما می توانید عددی هم به عنوان پارامتر دوم به آن بدهید که مثلا دو سطر قبل را برگرداند). نام ستونی که میخواهیم اطلاعات آن از یک سطر قبل یا بعد برگردانده شود، پارامتر اصلی این دو تابع است. دقت کنید که این تابع بعد از اعمال Order By‌ در هر پارتیشن، محاسبه می‌شود.

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

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

اگر به این مبحث علاقه‌مند شده‌اید توصیه می‌کنم برای تکمیل مبحث به این آموزش جمع و جور و کامل سایت LearnSQL با عنوان برگه مرور (تقلب) توابع پنجره‌ای مراجعه کنید. این سایت البته یک آموزش تعاملی خوب هم در همین زمینه دارد. این مقاله هم به کمک مثالهای کاربردی، سعی در آشنایی بیشتر خواننده با توابع پنجره ای دارد .