ابزار و کتابخانه ها

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

چرا آشنایی با 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)
ساختار اصلی توابع پنجره‌ای در SQL به همراه یک مثال کاربردی

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

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

Stylus

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

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

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

منبع : Kaggle Analytics Functions Micro Course

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

منبع :How Window Functions Work

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

Stylus

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

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

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

Stylus

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

Stylus

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

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

Stylus

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

Stylus

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

Stylus

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

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

امتیاز کاربران: ۴٫۵۸ ( ۱۰ رای)

مجتبی بنائی

دانشجوی دکترای نرم‌افزار دانشگاه تهران (yun.ir/smbanaie)، مدرس دانشگاه و فعال در حوزه توسعه نرم‌افزار و مهندسی داده که تمرکز کاری خود را در چند سال اخیر بر روی مطالعه و تحقیق در حوزه کلان‌داده و زیرساخت‌های پردازش داده و تولید محتوای تخصصی و کاربردی به زبان فارسی و انتشار آنها در سایت مهندسی داده گذاشته است. مدیریت پروژه‌های نرم‌افزاری و طراحی سامانه‌های مقیاس‌پذیر اطلاعاتی از دیگر فعالیتهای صورت گرفته ایشان در چند سال گذشته است.

۶ دیدگاه

  1. سلام
    امروز برای  window functions s سوالی برایم پیش اومده بود که با مطالب استاد بزرگوار مواجه شدم
    تشکر از استاد بنایی عزیز و گرامی
    سال ۱۳۸۴ در مقطع کارشناسی کامپیوتر سعادتی نصیب ماشد تا دانشجوی استاد گرامی آقای بنایی باشیم
    موفق و پیروز باشید

  2. سلام استاد اگر بخواهیم بخش بندی براساس ماههای شمسی باشد باید چکار کنیم؟

    1. سلام. تاریخ فارسی را به عنوان یک فیلد باید به جدول اضافه کنیم تا بتوانیم این گروه بندی را انجام دهیم. اگر تاریخ موجود در جداول شما ، میلادی باشد، بعید می دانم روش سریع و مناسبی برای تبدیل آن به فارسی در اس کیو ال و گروه بندی بر اساس آن وجود داشته باشد .

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

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

جای خالی در معادله زیر را با کی برد انگلیسی وارد کنید : * Time limit is exhausted. Please reload CAPTCHA.

این سایت از اکیسمت برای کاهش هرزنامه استفاده می کند. بیاموزید که چگونه اطلاعات دیدگاه های شما پردازش می‌شوند.

دکمه بازگشت به بالا