دوال النافذة (Window Functions)

المشكلة التي تحلّها

GROUP BY يلخّص كل مجموعة بصف واحد ويفقد تفاصيل الصفوف الفردية. أحيانًا تريد حساب مقارنة مثل متوسط القسم بجانب كل موظف بصفه الأصلي بدون تلخيص — هنا تفيد دالة النافذة.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

كل صف يظهر كما هو، وبجانبه متوسط قسمه — بلا GROUP BY وبلا فقدان أي عمود.

البنية العامة

function_name(...) OVER (
  [PARTITION BY column]
  [ORDER BY column]
)

• PARTITION BY: يقسّم الصفوف إلى مجموعات (مثل GROUP BY لكن دون تلخيصها).
• ORDER BY داخل OVER: يحدّد ترتيب الحساب داخل كل مجموعة (مهم لدوال مثل الترتيب أو التراكم).
• بدون PARTITION BY، تُعامَل كل الصفوف كمجموعة واحدة.

💡 دوال النافذة تُستخدم فقط في SELECT وORDER BY — لا يمكن استخدامها مباشرة في WHERE أو GROUP BY أو HAVING. لتصفية نتيجتها لف الاستعلام بـ CTE أو استعلام فرعي.

الترقيم والترتيب: ROW_NUMBER و RANK و DENSE_RANK

الثلاثة ترقّم الصفوف داخل كل مجموعة، لكن تختلف عند وجود قيم متساوية:

SELECT
  name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk
FROM employees;

اقتناص أول صف لكل مجموعة

نمط شائع جدًا — آخر طلب لكل مستخدم مثلًا:

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

المقارنة بالصف السابق واللاحق: LAG و LEAD

LAG تجلب قيمة من صف سابق، وLEAD من صف لاحق — مفيدة لحساب الفرق بين عملية وسابقتها بدون JOIN مع الجدول نفسه:

SELECT
  order_date,
  total,
  LAG(total)  OVER (ORDER BY order_date) AS prev_total,
  LEAD(total) OVER (ORDER BY order_date) AS next_total,
  total - LAG(total) OVER (ORDER BY order_date) AS diff
FROM daily_sales;

المجموع التراكمي (Running Total)

دوال التجميع نفسها (SUM, COUNT, AVG...) تعمل كدالة نافذة عند إضافة OVER:

SELECT
  order_date,
  total,
  SUM(total) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

بوجود ORDER BY داخل OVER بلا PARTITION BY، تُحسب القيمة تراكميًا من أول صف حتى الصف الحالي.

⚠️ لا تخلط بين ORDER BY الخارجي لترتيب عرض النتيجة، وORDER BY داخل OVER() الذي يتحكّم بترتيب الحساب نفسه — الاثنان مستقلّان تمامًا.

متى GROUP BY ومتى Window Function؟

🎯 التالي: الإدراج أو التحديث الذرّي (UPSERT).