توابع پنجره‌ای

OVER: تجمیع بدون حذف ردیف‌ها

GROUP BY ردیف‌ها را به یک ردیف در هر گروه فشرده می‌کند. گاهی می‌خواهیم هر ردیف بماند و کنارش یک محاسبه‌ی گروهی ببینیم: «رتبه‌ی هر کالا بر اساس قیمت» یا «جمع تجمعی فروش تا این سفارش». تابع پنجره‌ای این کار را می‌کند:

SELECT name, price,
       RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

بخش OVER (...) «پنجره» را تعریف می‌کند:

• PARTITION BY ستون: ردیف‌ها را به گروه‌های مستقل تقسیم می‌کند (اختیاری؛ اگر نباشد کل جدول یک پنجره است).
• ORDER BY ستون: ترتیب درون پنجره.

خروجی همه‌ی ۶ کالا با رتبه‌شان است: Headphones ۱، Backpack ۲، Mouse ۳، Lamp ۴، Notebook ۵، Pen ۶. توابع پنجره‌ای در SELECT اجرا می‌شوند، یعنی بعد از WHERE و GROUP BY.

رتبه‌بندی: ROW_NUMBER، RANK، DENSE_RANK

تفاوت وقتی معلوم می‌شود که مساوی داشته باشیم. سفارش‌ها را بر اساس status مرتب می‌کنیم (دو سفارش cancelled و شش paid، و cancelled الفبایی اول است):

| تابع | دو cancelled | اولین paid | |---|---|---| | ROW_NUMBER() | ۱ و ۲ (همیشه یکتا) | ۳ | | RANK() | ۱ و ۱ (مساوی‌ها رتبه‌ی یکسان) | ۳ (بعد از مساوی‌ها جا خالی می‌ماند) | | DENSE_RANK() | ۱ و ۱ | ۲ (بدون جا خالی) |

انتخاب: شماره‌گذاری یکتا ← ROW_NUMBER، رتبه‌ی مسابقه‌ای ← RANK، «چندمین مقدار متفاوت» ← DENSE_RANK.

جمع تجمعی و LAG

با SUM(...) OVER (ORDER BY ...) جمع تجمعی (running total) می‌گیریم: برای هر ردیف، جمع از اولین ردیف تا همین ردیف.

SELECT order_id, total,
       SUM(total) OVER (ORDER BY order_id) AS running_total
FROM order_totals;   -- CTE درس قبل

با مجموع سفارش‌های ۱۰۱ تا ۱۰۸ (۱۳۰، ۵۰۰، ۳۳۰، ۳۰۰، ۲۴۰، ...) مقدار running_total برای سفارش ۱۰۳ برابر ۱۳۰ + ۵۰۰ + ۳۳۰ = ۹۶۰ است.

LAG(ستون) مقدار ردیف قبلی در همان پنجره را می‌دهد (و LEAD ردیف بعدی را). برای «فاصله‌ی دو خرید متوالی هر مشتری»:

SELECT id, order_date,
       order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_prev
FROM orders;

برای اولین سفارش هر مشتری ردیف قبلی نیست، پس NULL می‌شود. (تفریق دو تاریخ در PostgreSQL تعداد روز است؛ در پایگاه‌های دیگر تابع دیگری لازم است.)

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

یکی از پرکاربردترین الگوهای تحلیل: «برای هر مشتری، گران‌ترین سفارشش». تابع پنجره‌ای را نمی‌توان مستقیم در WHERE گذاشت (بعد از WHERE اجرا می‌شود)، پس در یک CTE یا زیرپرس‌وجو رتبه می‌دهیم و بیرون فیلتر می‌کنیم:

WITH ranked AS (
  SELECT customer_id, order_id, total,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
  FROM customer_order_totals
)
SELECT customer_id, order_id, total FROM ranked WHERE rn = 1;

(customer_order_totals همان مجموع هر سفارش به‌همراه customer_id است.) برای سفارش‌های Sara (۱۳۰، ۵۰۰، ۵۵۰) جواب سفارش ۱۰۷ با ۵۵۰ است. اگر در صورت مساوی بودن همه‌ی بهترین‌ها را می‌خواهید RANK و گرنه ROW_NUMBER.

منابع و مطالعه‌ی بیشتر

• Window Functions، آموزش PostgreSQL: توضیح رسمی با مثال.
• فهرست توابع پنجره‌ای: LAG، LEAD، NTILE و بقیه.

توابع پنجره‌ای مهم‌ترین چیزی است که یک تحلیل‌گر SQL را از مبتدی جدا می‌کند و بیشتر درس‌های ویدیویی فقط یک بار از رویشان رد می‌شوند. سؤال‌های «خروجی چیست؟» اینجا همان کاری را می‌کنند که کاغذ و قلم برای حل مسئله: وادارت می‌کنند پنجره را در ذهن بسازی. مرور فاصله‌دار آن را ماندگار می‌کند.