دوال النافذة للترتيب

تريد هدى أن تعرض المنتجين الأكثر مبيعًا من كل فئة في النشرة البريدية لفصل الربيع. يمكنك الحصول على الإيراد لكل منتج بـ GROUP BY. لكن «أعلى اثنين داخل كل فئة» يحتاج إلى شيء لا يستطيعه GROUP BY: ترتيب الصفوف مقابل جيرانها مع إبقاء كل صف. هذه هي مهمة دوال النافذة (window functions)، وحين تمتلكها، تصبح فئة كاملة من الأسئلة قصيرة.

إبقاء الصفوف مع النظر عبرها

يضغط GROUP BY كل مجموعة في صف واحد. أما دالة النافذة فتحسب قيمة عبر مجموعة من الصفوف، هي النافذة، وتلحقها بكل صف، دون أن تضغط شيئًا.

الصياغة دالة يتبعها OVER (...). داخل القوسين، يقسم PARTITION BY الصفوف إلى مجموعات، ويُعاد بدء الحساب في كل مجموعة؛ ويحدّد ORDER BY الترتيب الذي تسير به الدالة. إذا حذفت PARTITION BY، أصبحت النتيجة كلها نافذة واحدة.

ROW_NUMBER وRANK وDENSE_RANK

لا تختلف دوال الترتيب الثلاث إلا في طريقة تعاملها مع التعادل. هؤلاء هم أكثر المشترين تكرارًا في الإمارات في 2025:

WITH uae_orders AS (
  SELECT o.customer_id, COUNT(*) AS orders
  FROM orders AS o
  JOIN customers AS c ON c.customer_id = o.customer_id
  WHERE c.country = 'United Arab Emirates'
    AND o.status <> 'cancelled'
    AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
  GROUP BY o.customer_id
)
SELECT
  customer_id,
  orders,
  ROW_NUMBER() OVER (ORDER BY orders DESC, customer_id) AS row_num,
  RANK()       OVER (ORDER BY orders DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY orders DESC) AS dense_rnk
FROM uae_orders
ORDER BY orders DESC, customer_id
LIMIT 8;

قدّم العميلان 163 و534 كلاهما 11 طلبًا. ومع ذلك يعطيهما ROW_NUMBER الرقمين 4 و5، أي موضعين فريدين، مستخدمًا customer_id لكسر التعادل. ويعطي RANK كليهما 4 ثم يقفز إلى 6، كجدول ترتيب الدوري الرياضي. ويعطي DENSE_RANK كليهما 4 ويكمل بـ 5.

اختر حسب السؤال. «صف واحد بالضبط لكل مجموعة» (آخر طلب، أول زيارة) يعني ROW_NUMBER. و«أعلى 3، مع كل من يتعادل في المركز الثالث» يعني RANK. و«أعلى ثلاث قيم» يعني DENSE_RANK.

أعلى N في كل مجموعة

تعمل دوال النافذة بعد WHERE، في خطوة SELECT، فلا يمكنك التصفية على الترتيب في WHERE الخاص بالـ query نفسه. النمط المعتاد هو CTE يحسب الترتيب، وquery خارجي يصفّيه:

WITH ranked AS (
  SELECT
    category,
    product,
    revenue,
    RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
  FROM product_revenue
)
SELECT category, product, revenue, rnk
FROM ranked
WHERE rnk <= 2;

يمثّل product_revenue هنا الـ CTE الخاص بالإيراد لكل منتج الذي ستكتبه أولًا، مستخدمًا الـ JOIN من درس الـ JOIN. وبناؤه هو التمرين.

الأحداث الأولى: صف واحد لكل عميل

تجيب الأداة نفسها عن سؤال تنتظره هدى منذ أشهر: أي قناة تجلب عملاء جددًا؟ إنها قناة الطلب الأول لكل عميل، أي صف واحد لكل partition:

WITH numbered AS (
  SELECT
    customer_id,
    channel,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date, order_id
    ) AS order_seq
  FROM orders
  WHERE status <> 'cancelled'
)
SELECT channel AS first_order_channel, COUNT(*) AS customers
FROM numbered
WHERE order_seq = 1
GROUP BY channel
ORDER BY customers DESC;

يجلب الويب 268 عميلًا، والتطبيق 172، والـ marketplace 63. قارن ذلك بإجمالي الطلبات حسب القناة وسترى هل يجد الـ marketplace مشترين جددًا في الغالب أم يخدم مشترين حاليين. والعمود order_seq مفيد بحدّ ذاته أيضًا: order_seq = 2 يجد الطلبات الثانية، وهي بداية كل تحليل لتكرار الشراء.

شرائح بدل المواضع

أحيانًا لا يكون السؤال «من الأول؟» بل «في أي شريحة يقع كل عميل؟». يقسم فريق المالية لدى كريم العملاء إلى أرباع حسب إيرادهم الكلي لمقارنة استخدام الخصومات بين الشرائح. توزّع NTILE(n) الصفوف المرتّبة على n شريحة متقاربة الحجم:

WITH lifetime AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.customer_id
),
tiered AS (
  SELECT customer_id, revenue,
         NTILE(4) OVER (ORDER BY revenue DESC) AS quartile
  FROM lifetime
)
SELECT quartile,
       COUNT(*) AS customers,
       ROUND(MIN(revenue), 2) AS min_revenue,
       ROUND(SUM(revenue), 2) AS revenue
FROM tiered
GROUP BY quartile
ORDER BY quartile;

إيراد الربع الأعلى يتضاءل أمامه إيراد الربع الأدنى، وهو الشكل المألوف في الغالبية العظمى من قواعد العملاء. لاحظ التدرّج: جمّع إلى صف واحد لكل عميل، ثم حدّد الشريحة بدالة نافذة، ثم جمّع مجددًا حسب الشريحة. دوال النافذة وGROUP BY ليسا متنافسين؛ معظم التحليلات الحقيقية تستخدم الاثنين، في خطوات منفصلة.

تقسم NTILE حسب عدد الصفوف لا حسب القيمة، فقد يقع عملاء متساوون في الإيراد في شرائح مختلفة. أما الشرائح ذات الحدود الثابتة («أكثر من 2,000 يعني ذهبي») فأداتها الصادقة هي تعبير CASE.

الترتيب نصف دوال النافذة. أما النصف الآخر، المجاميع التراكمية والمقارنة بالصف السابق، فيحوّل الجدول الشهري إلى اتجاه، وهو موضوعنا التالي.