خانه/ پرس‌وجوهای پیشرفته/ توابع پنجره‌ای

توابع پنجره‌ای: محاسبه بدون فشرده‌کردن ردیف‌ها

پیشرفته ۱۶ دقیقه مطالعه

در فصل ۳ با GROUP BY آشنا شدیم: چند ردیف را می‌گیرد و در یک ردیف خلاصه می‌کند. اما گاهی می‌خواهیم محاسبه‌ای شبیه به تجمیع انجام دهیم — مثل «رتبه‌ی هر دانش‌آموز» یا «جمع تجمعی فروش» — بدون این‌که ردیف‌های اصلی را از دست بدهیم. این دقیقاً کاری است که توابع پنجره‌ای (Window Functions) انجام می‌دهند.

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

ROW_NUMBER — شماره‌گذاری ساده

ساختار کلی هر تابع پنجره‌ای، وجود بخش OVER (...) بعد از تابع است که مشخص می‌کند «پنجره» چطور تعریف شود:

sql

SELECT full_name, grade,
  ROW_NUMBER() OVER (ORDER BY grade DESC) AS rn
FROM students;

ROW_NUMBER() به هر ردیف، بر اساس ترتیب مشخص‌شده در ORDER BY داخل OVER، یک شماره‌ی یکتا و پیوسته می‌دهد — حتی اگر چند ردیف مقدار یکسانی داشته باشند.

RANK در برابر DENSE_RANK — رفتار متفاوت با تساوی

وقتی چند ردیف در ORDER BY مقدار یکسانی دارند (تساوی)، سه تابع رتبه‌بندی رفتار متفاوتی دارند:

تابع	رفتار با تساوی
`ROW_NUMBER()`	به هرکدام، بدون توجه به تساوی، شماره‌ی جدا و پیوسته می‌دهد
`RANK()`	به ردیف‌های مساوی، رتبه‌ی یکسان می‌دهد؛ اما بعد از آن‌ها، در شماره‌گذاری «جهش» می‌کند
`DENSE_RANK()`	به ردیف‌های مساوی، رتبه‌ی یکسان می‌دهد؛ اما بعد از آن‌ها، بدون جهش ادامه می‌دهد

بیا هر سه را کنار هم، روی داده‌ای که واقعاً تساوی دارد (چند دانش‌آموز پایه‌ی یکسان)، مقایسه کنیم:

امتحانش کن — ROW_NUMBER در برابر RANK و DENSE_RANK

دقت کن سه دانش‌آموزی که پایه‌ی ۱۰ دارند، در rnk همگی عدد ۳ می‌گیرند و نفر بعدی مستقیم می‌پرد به ۶ (چون ۳ نفر رتبه‌ی ۳ را گرفته‌اند). اما در drnk، همان سه نفر عدد ۲ می‌گیرند و نفر بعدی بدون جهش، ۳ می‌شود. rn هم بدون توجه به تساوی، فقط پشت‌سرهم شماره می‌دهد.

PARTITION BY — بازنشانی پنجره برای هر گروه

PARTITION BY دقیقاً مثل GROUP BY عمل می‌کند — با این تفاوت که ردیف‌ها را در هم فشرده نمی‌کند، فقط محاسبه را به‌ازای هر «بخش» از نو شروع می‌کند:

sql

SELECT full_name, city, grade,
  RANK() OVER (PARTITION BY city ORDER BY grade DESC) AS city_rank
FROM students;

این‌جا رتبه‌بندی برای هر شهر، جداگانه از صفر شروع می‌شود — یعنی دانش‌آموز اول هر شهر، رتبه‌ی ۱ همان شهر را می‌گیرد، نه رتبه‌ی کلی بین همه‌ی دانش‌آموزان. بیا ببینیم:

امتحانش کن — رتبه‌بندی درون هر شهر با PARTITION BY

در تهران دو دانش‌آموز با پایه‌ی یکسان هستند و هر دو city_rank = 1 می‌گیرند؛ در بقیه‌ی شهرها که فقط یک دانش‌آموز دارند، همان یک نفر هم رتبه‌ی ۱ می‌گیرد — چون پنجره‌ی هرکدام، فقط شامل هم‌شهری‌های خودشان است.

توابع تجمیعی به‌عنوان پنجره‌ای: SUM() OVER

حتی توابعی که تا الان فقط به‌عنوان تجمیعی می‌شناختیم (SUM, AVG, ...) را می‌توان با OVER به‌شکل پنجره‌ای هم استفاده کرد — بدون این‌که GROUP BY لازم باشد:

sql

-- جمع تجمعی درآمد، به ترتیب id
SELECT id, product, price * quantity AS line_total,
  SUM(price * quantity) OVER (ORDER BY id) AS running_total
FROM sales;

این‌جا SUM(...) OVER (ORDER BY id) یعنی «مجموع همه‌ی ردیف‌ها از ابتدا تا همین ردیف» — دقیقاً همان مفهوم جمع تجمعی (Running Total) که در گزارش‌های مالی زیاد دیده می‌شود. بیا امتحان کنیم:

امتحانش کن — جمع تجمعی با SUM() OVER

دقت کن ردیف آخر (که quantityاش NULL بود) مقدار line_totalی NULL دارد، اما running_total همچنان همان عدد قبلی می‌ماند — دقیقاً همان رفتار NULL-safe که در درس SUM دیدیم، این‌بار داخل یک تابع پنجره‌ای.

جمع‌بندی این درس (و کل فصل ۵)

تابع پنجره‌ای بر خلاف GROUP BY، ردیف‌ها را فشرده نمی‌کند؛ فقط یک ستون محاسبه‌شده اضافه می‌کند.
ROW_NUMBER() بدون توجه به تساوی شماره می‌دهد؛ RANK() بعد از تساوی جهش می‌کند؛ DENSE_RANK() جهش نمی‌کند.
PARTITION BY پنجره را به‌ازای هر گروه، از نو شروع می‌کند — شبیه GROUP BY اما بدون فشرده‌سازی.
توابع تجمیعی آشنا (SUM, AVG, ...) را هم می‌توان با OVER به‌صورت پنجره‌ای (مثلاً برای جمع تجمعی) استفاده کرد.

با این درس، فصل ۵ (پرس‌وجوهای پیشرفته) کامل شد! از فصل بعدی سراغ تغییر داده‌ها می‌رویم: چطور با INSERT، UPDATE و DELETE، به‌جای فقط خواندن، داده را واقعاً تغییر دهیم.