توابع پنجره‌ای در SQL: جمع تجمعی، رتبه‌بندی و میانگین متحرک

دیر یا زود گزارشی چیزی می‌خواهد که از GROUP BY برنمی‌آید. هر فروش را کنار مجموعِ منطقه‌اش نشان بده. فروشنده‌ها را در هر منطقه رتبه‌بندی کن. درآمد این ماه را کنار ماه قبل نشان بده. با GROUP BY برای هر گروه یک ردیف می‌گیرید و جزئیات از دست می‌رود؛ آدم‌ها معمولاً سراغ self-join، زیرکوئری یا یک حلقه در کد برنامه می‌روند.

توابع پنجره‌ای (window functions) همهٔ این‌ها را در یک کوئری حل می‌کنند. آن‌ها مقدارها را روی مجموعه‌ای از ردیف‌های مرتبط (همان پنجره) محاسبه می‌کنند، بدون اینکه آن‌ها را در هم فشرده کنند: هر ردیفِ ورودی همچنان در خروجی هست و مقدارِ محاسبه‌شده به شکل یک ستون جدید به آن اضافه می‌شود.

این توابع در PostgreSQL، MySQL 8 به بعد، SQL Server، Oracle و SQLite 3.25 به بعد کار می‌کنند. مثال‌های زیر همان‌طور که هستند در SQLite اجرا می‌شوند. اگر هنوز با JOINها راحت نیستید، از JOINها در SQL با مثال‌های ساده شروع کنید.

داده‌های نمونهٔ ما#

CREATE TABLE sales (rep TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
  ('Ana', 'North', 900), ('Ben', 'North', 700), ('Cleo', 'North', 700), ('Dev', 'North', 400),
  ('Eve', 'South', 1200), ('Finn', 'South', 650), ('Gus', 'South', 300);

CREATE TABLE monthly (month TEXT, revenue INTEGER);
INSERT INTO monthly VALUES
  ('2026-01', 12000), ('2026-02', 15000), ('2026-03', 11000),
  ('2026-04', 18000), ('2026-05', 21000), ('2026-06', 19500);

مقایسهٔ GROUP BY با پنجره#

این مجموعِ هر منطقه با GROUP BY است. هفت ردیف وارد می‌شود و دو ردیف بیرون می‌آید:

SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

حالا همان SUM، اما با یک OVER بعد از آن، که آن را به یک تابع پنجره‌ای تبدیل می‌کند:

SELECT rep, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales
ORDER BY region, amount DESC;

هر هفت ردیف می‌مانند. PARTITION BY region ردیف‌ها را مثل GROUP BY گروه‌بندی می‌کند، اما به‌جای اینکه هر گروه را در یک ردیف فشرده کند، به هر ردیف دسترسی به کلِ گروهش را می‌دهد. با یک OVER () خالی، پنجره کلِ نتیجه است.

ساختار OVER#

function(...) OVER (
    PARTITION BY ...   -- which rows belong together (optional)
    ORDER BY ...       -- the order of rows inside each partition (optional)
    ROWS BETWEEN ...   -- which of those rows to use for this row (optional)
)

همهٔ بخش‌ها اختیاری‌اند و هر کدام نتیجه را تغییر می‌دهد. یکی‌یکی سراغشان می‌رویم.

رتبه‌بندی با ROW_NUMBER، RANK و DENSE_RANK#

توابع رتبه‌بندی داخل پنجره به یک ORDER BY نیاز دارند. تنها تفاوتشان در رفتار با مقدارهای مساوی است، و داده‌های ما یک تساوی دارد: Ben و Cleo هر دو ۷۰۰ فروخته‌اند.

SELECT rep, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
       RANK()       OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM sales
ORDER BY amount DESC, rep;

• ROW_NUMBER همیشه ۱، ۲، ۳، ۴… می‌دهد، بدون تساوی. اینکه Ben یا Cleo عدد ۳ را بگیرد به پایگاه داده بستگی دارد، مگر اینکه یک معیارِ دوم مثل ORDER BY amount DESC, rep اضافه کنید.
• RANK به مقدارهای مساوی یک عدد می‌دهد و بعد عدد را جا می‌اندازد: ۳، ۳، ۵. مثل مسابقه‌ای که دو دونده با هم مدال برنز می‌گیرند.
• DENSE_RANK به مقدارهای مساوی یک عدد می‌دهد و جا نمی‌اندازد: ۳، ۳، ۴.

N تای برتر در هر گروه#

«دو فروشندهٔ برتر در هر منطقه» یکی از رایج‌ترین سؤال‌های گزارش‌گیری است و توابع پنجره‌ای آن را ساده می‌کنند. اما نمی‌توانید مستقیماً روی یک تابع پنجره‌ای فیلتر کنید:

SELECT rep FROM sales WHERE ROW_NUMBER() OVER (ORDER BY amount) <= 2;
-- Error: misuse of window function ROW_NUMBER()

توابع پنجره‌ای بعد از WHERE، GROUP BY و HAVING محاسبه می‌شوند، درست قبل از ORDER BY و LIMIT نهایی. پس رتبه را در یک زیرکوئری یا CTE حساب می‌کنید و بعد نتیجه را فیلتر می‌کنید:

WITH ranked AS (
  SELECT rep, region, amount,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, rep) AS pos
  FROM sales
)
SELECT region, pos, rep, amount
FROM ranked
WHERE pos <= 2
ORDER BY region, pos;

اگر می‌خواهید فروشنده‌های هم‌امتیاز هر دو در فهرست بیایند، به‌جای ROW_NUMBER() از RANK() استفاده کنید.

جمع تجمعی#

به پنجرهٔ یک تابع تجمیعی ORDER BY اضافه کنید تا تجمعی شود: هر ردیف همه‌چیز را از ابتدای پارتیشن تا خودش جمع می‌زند.

SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly;

با اضافه کردن PARTITION BY، جمع تجمعی برای هر گروه از نو شروع می‌شود؛ مثلاً مجموعِ «از ابتدای سال تا امروز» که هر ژانویه صفر می‌شود.

مقایسه با ردیف قبلی: LAG و LEAD#

LAG(column) مقدار را از ردیفِ قبلی در پنجره برمی‌گرداند و LEAD(column) از ردیفِ بعدی. تغییرِ ماه‌به‌ماه به یک تفریقِ ساده تبدیل می‌شود:

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly;

ماه اول ردیفِ قبلی ندارد، پس LAG مقدار NULL برمی‌گرداند. LAG(revenue, 1, 0) به‌جای آن 0 برمی‌گرداند، و LAG(revenue, 12) دوازده ردیف عقب می‌رود که برای مقایسهٔ سال‌به‌سال به کار می‌آید.

قاب‌های پنجره: میانگین متحرک#

قاب (frame) تعیین می‌کند کدام ردیف‌های پارتیشن برای ردیفِ فعلی حساب شوند. میانگینِ متحرکِ سه‌ماهه از ردیف فعلی و دو ردیفِ قبل از آن استفاده می‌کند:

SELECT month, revenue,
       ROUND(AVG(revenue) OVER (
         ORDER BY month
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS avg_3m
FROM monthly;

دو ماه اول میانگینِ ردیف‌های کمتری را می‌گیرند، چون قبل از آن‌ها چیزی نیست. قاب‌های مفیدِ دیگر ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING (پنجره‌ای که ردیف فعلی وسطش است) و ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (کلِ پارتیشن) هستند.

همین پیش‌فرض یک معمای کلاسیک را توضیح می‌دهد: LAST_VALUE(revenue) OVER (ORDER BY month) مقدارِ ردیفِ فعلی را برمی‌گرداند، نه ماهِ آخر را، چون قابِ پیش‌فرض در ردیف فعلی تمام می‌شود. با ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING کلِ پارتیشن را به آن بدهید.

پنجره روی نتایجِ گروه‌بندی‌شده#

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

SELECT region,
       SUM(amount) AS total,
       ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct_of_all
FROM sales
GROUP BY region;

برگهٔ تقلب#

جمع‌بندی#

• تابع پنجره‌ای یک تابعِ معمولی است که بعد از آن OVER (...) می‌آید. روی ردیف‌های مرتبط محاسبه می‌کند اما همهٔ ردیف‌ها را نگه می‌دارد.
• PARTITION BY ردیف‌ها را گروه‌بندی می‌کند، ORDER BY آن‌ها را داخل هر گروه مرتب می‌کند و قاب (ROWS BETWEEN ...) ردیف‌هایی را که در هر محاسبه به کار می‌روند انتخاب می‌کند.
• ROW_NUMBER، RANK و DENSE_RANK فقط در رفتار با تساوی‌ها فرق دارند.
• توابع پنجره‌ای بعد از WHERE و GROUP BY اجرا می‌شوند؛ پس فیلتر روی آن‌ها را در یک CTE یا زیرکوئری انجام دهید.
• حواستان به قابِ پیش‌فرض باشد: برای جمع تجمعیِ دقیق از ROWS استفاده کنید و کلِ پارتیشن را به LAST_VALUE بدهید.

وقتی کوئری‌هایتان میلیون‌ها ردیف را خلاصه می‌کنند، سرعت مهم می‌شود؛ ایندکس‌ها و پلن کوئری در SQL قدم طبیعیِ بعدی است.