Window Functions: رتبه‌بندی، LAG و جمع تجمعی

تجمیع بدون از دست دادن ردیف‌ها

GROUP BY ردیف‌ها را فشرده می‌کند؛ Window Function (از MySQL 8.0) برای هر ردیف مقداری بر اساس «پنجره‌ای» از ردیف‌های مرتبط حساب می‌کند و ردیف را نگه می‌دارد. با آن کارهایی که قبلاً به زیرکوئری‌های پیچیده یا متغیرهای @row نیاز داشت، در یک خط انجام می‌شود.

SELECT o.id, o.customer_id, o.total_amount,
       SUM(o.total_amount) OVER (PARTITION BY o.customer_id) AS customer_total,
       ROUND(100 * o.total_amount / SUM(o.total_amount) OVER (PARTITION BY o.customer_id), 1) AS pct
FROM orders o;

PARTITION BY گروه را تعیین می‌کند و ORDER BY داخل OVER ترتیب ردیف‌ها در پنجره را.

رتبه‌بندی

-- پرفروش‌ترین ۳ محصول هر طرح (Top-N per group)
WITH ranked AS (
  SELECT d.name AS design, p.sku, SUM(oi.qty) AS sold,
         ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY SUM(oi.qty) DESC, p.id) AS rn
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  JOIN designs d  ON d.id = p.design_id
  GROUP BY d.id, d.name, p.id, p.sku
)
SELECT design, sku, sold FROM ranked WHERE rn <= 3;

Window Function را نمی‌توان در WHERE استفاده کرد (بعد از WHERE محاسبه می‌شود)؛ به همین دلیل نتیجه را در CTE می‌پیچیم و بیرون فیلتر می‌کنیم.

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

WITH m AS (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS ym, SUM(total_amount) AS rev
  FROM orders GROUP BY ym
)
SELECT ym, rev,
       LAG(rev) OVER w AS prev_rev,
       ROUND(100 * (rev - LAG(rev) OVER w) / LAG(rev) OVER w, 1) AS growth_pct,
       SUM(rev) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(rev) OVER (w ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)        AS moving_avg_3m
FROM m
WINDOW w AS (ORDER BY ym);

عبارت WINDOW w AS (...) تعریف پنجره را یک بار می‌نویسد و چند تابع از آن استفاده می‌کنند.

قاب پنجره: ROWS در برابر RANGE

وقتی داخل OVER فقط ORDER BY می‌نویسید، قاب پیش‌فرض RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW است؛ یعنی همه‌ی ردیف‌های هم‌مقدار با ردیف جاری هم داخل جمع می‌آیند. اگر دو سفارش تاریخ یکسان داشته باشند، جمع تجمعی هر دو یکی نشان داده می‌شود. برای جمع تجمعی ردیف‌به‌ردیف صریحاً ROWS بنویسید.

نکته‌هایی که کمتر کسی می‌داند

• ROW_NUMBER() بدون مرتب‌سازی یکتا (مثلاً فقط روی تاریخ) در هر اجرا ممکن است عدد متفاوتی بدهد؛ همیشه id را به‌عنوان شکننده‌ی تساوی اضافه کنید.
• LAST_VALUE(x) OVER (ORDER BY d) تقریباً همیشه خود ردیف جاری را برمی‌گرداند، به‌خاطر همان قاب پیش‌فرض؛ قاب را ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING کنید.
• برای حذف ردیف‌های تکراری با نگه داشتن جدیدترین: ROW_NUMBER روی گروه تکراری، سپس DELETE ... WHERE id IN (SELECT id FROM (...) t WHERE rn > 1)؛ لایه‌ی اضافی derived table برای دور زدن خطای 1093 لازم است.
• LAG(rev, 12) مقدار دوازده ردیف قبل را می‌دهد؛ برای مقایسه با «همین ماه سال قبل»، به شرطی که هیچ ماهی در سری جا نیفتاده باشد (ترکیب با سری تاریخ درس قبل).
• متغیرهای کاربر مثل @rn := @rn + 1 برای شماره‌گذاری در 8.0 منسوخ شده‌اند و ترتیب ارزیابی‌شان تضمینی ندارد؛ کدهای قدیمی را به Window Function تبدیل کنید.