دوال النوافذ

دوال النوافذ — Window Functions

GROUP BY يطوي عدة صفوف إلى صف واحد لكل مجموعة. أحياناً تريد حساب تجميع — مجموع، ترتيب، فرق عن الصف السابق — دون فقدان الصفوف الأصلية. هنا تأتي دوال النوافذ عبر الكلمة OVER.

دوال النوافذ متاحة في SQLite منذ الإصدار 3.25 (سبتمبر 2018)، PostgreSQL، MySQL 8+، وكل قواعد البيانات الحديثة. الأقدم من ذلك يحتاج subqueries مرتبطة وهي أبطأ بكثير.

الفكرة الأساسية: `OVER` لا يطوي الصفوف

قارن:

query.sql

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  total_halalas INTEGER
);

INSERT INTO orders VALUES
  (1, 1, 5000),
  (2, 1, 7000),
  (3, 2, 3000),
  (4, 2, 4000),
  (5, 2, 9000);

-- GROUP BY يفقد الصفوف — يعطي صفاً واحداً لكل عميل
SELECT customer_id, SUM(total_halalas) AS total
FROM orders
GROUP BY customer_id;

-- النافذة تحتفظ بكل صف وتضيف العمود
SELECT
  id,
  customer_id,
  total_halalas,
  SUM(total_halalas) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

Output:

الأول 2 صفوف. الثاني 5 صفوف، كل صف يعرف مجموع عميله بجانبه.

`ROW_NUMBER()` — ترقيم تسلسلي

أعطِ كل صف رقماً حسب ترتيب معيّن:

query.sql

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  created_at TEXT,
  total_halalas INTEGER
);

INSERT INTO orders VALUES
  (101, 1, '2026-01-05', 5000),
  (102, 1, '2026-02-12', 7000),
  (103, 1, '2026-03-20', 3000),
  (104, 2, '2026-01-08', 9000),
  (105, 2, '2026-02-25', 2000);

-- ترتيب طلبات كل عميل من الأقدم للأحدث
SELECT
  id,
  customer_id,
  created_at,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS order_seq
FROM orders;

Output:

النتيجة: العميل 1 يحصل على 1، 2، 3 لطلباته الثلاثة. العميل 2 يحصل على 1، 2.

استخدامه الشائع: “آخر طلب لكل عميل” بكتابة WHERE order_seq = 1 بعد ترتيب تنازلي.

`RANK` و `DENSE_RANK` — التعامل مع التعادل

ROW_NUMBER يعطي رقماً فريداً حتى عند التساوي. RANK يكرر الرقم ويقفز، DENSE_RANK يكرر دون قفز:

query.sql

CREATE TABLE scores (
  player TEXT,
  score INTEGER
);

INSERT INTO scores VALUES
  ('Aziz', 90),
  ('Sara', 90),
  ('Omar', 80),
  ('Lina', 70);

SELECT
  player,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK()       OVER (ORDER BY score DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dr
FROM scores;

Output:

player	score	rn	rk	dr
Aziz	90	1	1	1
Sara	90	2	1	1
Omar	80	3	3	2
Lina	70	4	4	3

اختر بناءً على معنى الترتيب: في المسابقات تستخدم RANK (ميدالية فضية مكررة، لا برونزية). في تقارير “أعلى N” تستخدم ROW_NUMBER لتفادي التساوي.

`LAG` و `LEAD` — الصف السابق والتالي

LAG يسحب قيمة من صف سابق ضمن النافذة، LEAD من لاحق. مفيد لحساب الفروقات والاتجاهات:

query.sql

CREATE TABLE orders (
  customer_id INTEGER,
  created_at TEXT,
  total_halalas INTEGER
);

INSERT INTO orders VALUES
  (1, '2026-01-05', 5000),
  (1, '2026-02-12', 7000),
  (1, '2026-03-20', 3000),
  (2, '2026-01-08', 9000),
  (2, '2026-02-25', 2000);

-- الفجوة الزمنية بين كل طلب والذي قبله لنفس العميل
SELECT
  customer_id,
  created_at,
  LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_order_at,
  total_halalas,
  total_halalas - LAG(total_halalas) OVER (PARTITION BY customer_id ORDER BY created_at) AS delta
FROM orders;

Output:

أول طلب لكل عميل يأخذ NULL في prev_order_at لأنه لا يوجد قبله شيء. هذا متوقع وغالباً مفيد للفلترة.

`SUM() OVER (...)` — مجاميع تراكمية

أضف ORDER BY داخل OVER لتحوّل المجموع من إجمالي إلى تراكمي حتى الصف الحالي:

query.sql

CREATE TABLE daily_revenue (
  day TEXT,
  amount_halalas INTEGER
);

INSERT INTO daily_revenue VALUES
  ('2026-04-01', 12000),
  ('2026-04-02', 8000),
  ('2026-04-03', 15000),
  ('2026-04-04', 5000);

SELECT
  day,
  amount_halalas,
  SUM(amount_halalas) OVER (ORDER BY day) AS running_total
FROM daily_revenue;

Output:

day	amount	running_total
2026-04-01	12000	12000
2026-04-02	8000	20000
2026-04-03	15000	35000
2026-04-04	5000	40000

نفس الفكرة مع AVG، COUNT، أو MAX. أضف PARTITION BY لإعادة العد في كل مجموعة (مثلاً تراكمي شهري لكل قناع بيع).

مثال مركّب: ترتيب أكبر طلب لكل عميل

query.sql

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  total_halalas INTEGER
);

INSERT INTO orders VALUES
  (1, 1, 5000),
  (2, 1, 9000),
  (3, 1, 3000),
  (4, 2, 4000),
  (5, 2, 12000),
  (6, 2, 6000);

WITH ranked AS (
  SELECT
    id,
    customer_id,
    total_halalas,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_halalas DESC) AS r
  FROM orders
)
SELECT id, customer_id, total_halalas
FROM ranked
WHERE r = 1;

Output:

النتيجة: أكبر طلب لكل عميل في صف واحد — نمط شائع جداً للوحات التقارير.

متى لا تستخدم النافذة

إذا كنت تريد طي الصفوف فعلاً، GROUP BY أوضح وأسرع.
إذا كان الترتيب لا معنى له (مثلاً ROW_NUMBER() بدون ORDER BY يعطي ترتيباً غير محدد) — سيعمل لكن النتيجة متذبذبة بين التشغيلات.

التحدي

من الجدول التالي، اعرض آخر طلب (الأحدث) لكل عميل في صف واحد. استخدم ROW_NUMBER داخل CTE.

تحدي — Challenge

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  created_at TEXT
);

INSERT INTO orders VALUES
  (1, 1, '2026-01-05'),
  (2, 1, '2026-03-20'),
  (3, 2, '2026-02-08'),
  (4, 2, '2026-04-01');

-- آخر طلب لكل عميل — Last order per customer

دوال النوافذ