Window Functions کامل: رتبه، مقایسه با قبل و frame

محاسبه روی «پنجره‌ای» از ردیف‌ها، بدون گروه‌بندی

aggregate معمولی ردیف‌ها را در هم ادغام می‌کند؛ window function هر ردیف را نگه می‌دارد و کنارش مقداری محاسبه‌شده از ردیف‌های مرتبط می‌گذارد. نحو کلی: func(...) OVER (PARTITION BY ... ORDER BY ... frame).

رتبه‌بندی

SELECT d.reed, d.code, s.pieces,
       row_number() OVER w AS rn,      -- 1,2,3,4
       rank()       OVER w AS rnk,     -- 1,2,2,4
       dense_rank() OVER w AS drnk     -- 1,2,2,3
FROM designs d
JOIN (SELECT design_code, sum(qty) AS pieces FROM order_items GROUP BY 1) s
  ON s.design_code = d.code
WINDOW w AS (PARTITION BY d.reed ORDER BY s.pieces DESC);

برای «سه نقشه‌ی پرفروش هر شانه» نمی‌توانید window function را در WHERE بگذارید (WHERE قبل از آن اجرا می‌شود)؛ یک لایه زیرکوئری لازم است: SELECT * FROM (...) t WHERE rn <= 3. از نسخه‌ی 15 پستگرس این الگو را تشخیص می‌دهد و محاسبه‌ی row_number هر partition را بعد از رسیدن به ۳ متوقف می‌کند.

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

WITH m AS (
  SELECT date_trunc('month', o.created_at AT TIME ZONE 'Asia/Tehran') AS month,
         sum(oi.line_total_rial) AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
)
SELECT month, revenue,
       lag(revenue) OVER (ORDER BY month) AS prev,
       round(100.0 * (revenue - lag(revenue) OVER (ORDER BY month))
             / NULLIF(lag(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct,
       sum(revenue) OVER (ORDER BY month) AS running_total,
       avg(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3m
FROM m ORDER BY month;

frame: کدام ردیف‌ها در پنجره‌اند

frame پیش‌فرض وقتی ORDER BY دارید RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW است. کلمه‌ی RANGE یعنی ردیف‌های هم‌مقدار (peers) با ردیف فعلی هم داخل پنجره‌اند.

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

• جمع تجمعی با frame پیش‌فرض برای ردیف‌های هم‌تاریخ یک عدد تکراری نشان می‌دهد (چون همه‌ی peerها با هم جمع می‌شوند)؛ برای جمع ردیف‌به‌ردیف ROWS بنویسید یا id را به ORDER BY اضافه کنید.
• last_value(x) OVER (ORDER BY ...) تقریباً همیشه خود ردیف فعلی را برمی‌گرداند، چون frame پیش‌فرض به CURRENT ROW ختم می‌شود؛ frame را تا UNBOUNDED FOLLOWING باز کنید.
• aggregate پنجره‌ای FILTER هم می‌پذیرد: count(*) FILTER (WHERE status = 'cancelled') OVER (PARTITION BY customer_id).
• عبارت EXCLUDE CURRENT ROW در frame، ردیف فعلی را از محاسبه کنار می‌گذارد؛ برای «میانگین همکاران به‌جز خودم» در مقایسه‌ی بافنده‌ها بی‌نظیر است.
• توابع پنجره‌ای که تعریف OVER یکسان دارند با یک مرتب‌سازی محاسبه می‌شوند؛ هر تعریف متفاوت یک Sort جدا می‌خواهد. با بند WINDOW تعریف‌ها را یکسان و خوانا نگه دارید.