تحسب دالة النافذة قيمة لكل صف من مجموعة صفوف مرتبطة به، هي نافذته، دون دمج الصفوف كما يفعل GROUP BY. تكتبها بالشكل function() OVER (PARTITION BY ... ORDER BY ...). يقسم PARTITION BY الصفوف إلى مجموعات، ويحدد ORDER BY الترتيب داخل كل مجموعة.
يبقى كل صف في النتيجة ويحمل مجموع منطقته: 1000 لـ north و500 لـ south. وتصبح حصة كل مندوب تعبيرًا واحدًا (لدى ana 50 بالمئة من north). وأي دالة تجميع (sum وcount وavg وmin وmax) تصبح دالة نافذة حين تضيف OVER. وOVER () الفارغ يجعل النتيجة كلها نافذة واحدة.
ترقيم الصفوف باستخدام ROW_NUMBER وRANK وDENSE_RANK
لا تختلف دوال الترتيب الثلاث إلا حين تتساوى الصفوف في قيمة ORDER BY:
لدى ben وcy 85 كلاهما. ما زالت row_number تعطيهما 2 و3، ومن يحصل على 2 غير محدد ما لم يكسر ORDER BY التعادل. وتعطي rank كليهما 2 ثم تقفز إلى 4 لـ dee. أما dense_rank فتعطي كليهما 2 وتكمل بـ 3.
| الدالة | التعادل | فجوات بعد التعادل |
|---|---|---|
row_number() | أرقام مختلفة | لا |
rank() | الرقم نفسه | نعم |
dense_rank() | الرقم نفسه | لا |
الحصول على أعلى N صفوف في كل مجموعة
تُقيَّم دوال النوافذ بعد WHERE، فلا يمكنك التصفية بها مباشرة. رقّم الصفوف في استعلام فرعي أو CTE، ثم صفِّ في الخارج:
يعيد هذا أفضل مندوبَين في كل منطقة. استخدم rank() بدل row_number() لتحتفظ بكل صف يتعادل على المركز الثاني. وللصف الأول فقط في كل مجموعة يكون DISTINCT ON أقصر.
حساب مجموع تراكمي أو متوسط متحرك
مع ORDER BY داخل OVER تعمل دالة التجميع على إطار. افتراضيًا يمتد الإطار من أول صف في القسم حتى الصف الحالي، وهذا يعطي مجموعًا تراكميًا. وتحدد عبارة ROWS BETWEEN إطارًا مختلفًا، مثل الصف الحالي والصفين قبله:
يبلغ المجموع التراكمي 130 في اليوم 5، والمتوسط لثلاثة أيام في اليوم 5 هو (30 + 20 + 50) / 3 = 33.3.
الإطار الافتراضي هو RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. وهو يشمل كل صف يتساوى مع الصف الحالي في قيمة ORDER BY، فيحصل صفان لهما اليوم نفسه على المجموع التراكمي نفسه. اكتب ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW لتضيفهما واحدًا تلو الآخر.
المقارنة بالصف السابق: LAG وLEAD
تقرأ lag(col) قيمة من الصف الذي يسبق الصف الحالي في ترتيب النافذة، وتقرأ lead(col) من الصف الذي يليه. وتعيد كلتاهما NULL بعد الحافة ما لم تمرر قيمة افتراضية كوسيط ثالث.
ليس لليوم 1 صف سابق، فتكون previous وchange NULL. وتعرّف عبارة WINDOW نافذة مرة واحدة، باسم، لتشترك فيها عدة دوال.
تعمل first_value مع الإطار الافتراضي، أما last_value فلا: ينتهي الإطار الافتراضي عند الصف الحالي، فتعيد قيمة الصف الحالي. أضف ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ليصبح الإطار القسم كله. ومن دوال النوافذ الأخرى ntile(n) وpercent_rank() وcume_dist() وnth_value()، ويسردها كلها مرجع دوال النوافذ في PostgreSQL.
تحديد الصفوف التي تعدّها دالة التجميع
يعمل FILTER (WHERE ...) على دوال التجميع في النوافذ أيضًا، للمجاميع المشروطة في كل صف:
SELECT day,
count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;
الأسئلة الشائعة
ما الفرق بين GROUP BY وPARTITION BY؟
يعيد GROUP BY صفًا واحدًا لكل مجموعة. أما PARTITION BY داخل OVER فيحتفظ بكل صف ويعطي كلًّا منها قيمة محسوبة على مجموعته، فتستطيع عرض مبلغ الصف بجانب مجموع مجموعته.
هل يمكن استخدام دالة نافذة في WHERE؟
لا. تعمل دوال النوافذ بعد WHERE وGROUP BY وHAVING، فلا يُسمح بها إلا في قائمة الاختيار وفي ORDER BY. احسب القيمة في استعلام فرعي أو CTE ثم صفِّ في الاستعلام الخارجي.
لماذا تعيد LAST_VALUE قيمة الصف الحالي؟
مع ORDER BY داخل OVER ينتهي الإطار الافتراضي عند الصف الحالي (والصفوف المتساوية معه)، فيكون آخر صف في الإطار هو الصف الحالي. أضف ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ليصبح الإطار القسم (partition) كله.
ما الفرق بين ROWS وRANGE في الإطار؟
يعدّ ROWS الصفوف الفعلية: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW هو الصف الحالي والصفان قبله. أما RANGE فيعمل على قيمة ORDER BY، فتكون الصفوف ذات القيم المتساوية (peers) في الإطار معًا دائمًا.