English 中文 العربية 日本語 Português

PostgreSQL: دوال النوافذ في PostgreSQL: دليل مفصل

آخر تحديث: 2026-08-26

1. ما ستتعلمه

2. القصة

Charlie هو محلل نمو في منصة SaaS. مدير المنتج طلب منه إنتاج تقرير احتفاظ المستخدمين:

  1. عدد المستخدمين الجدد اليومي
  2. معدل الاحتفاظ في اليوم 7
  3. معدل الاحتفاظ في اليوم 30
  4. رتبة تسجيل كل مستخدم (الشخص رقم N الذي سجل في نفس اليوم)

النهج التقليدي يحتاج عدة عبارات SQL بالإضافة إلى جداول مؤقتة. بعد تعلم دوال النوافذ، يحصل Charlie على جميع الإحصائيات في عبارة SQL واحدة — لا GROUP BY لطي الصفوف؛ كل صف يحتفظ ببياناته الأصلية مع النتيجة المحسوبة.

3. المفهوم: أساسيات دوال النوافذ

(1) ما هي دالة النوافذ

دالة النوافذ تحسب عبر مجموعة من الصفوف المرتبطة ("النافذة") لكنها لا تطوي الصفوف — كل صف يحصل على نتيجة. هذا هو أكبر اختلاف لها عن دوال التجميع.

الميزة دالة التجميع دالة النوافذ
عدد الصفوف صفوف متعددة → صف واحد عدد الصفوف لا يتغير
الصيغة SUM(col) SUM(col) OVER (...)
GROUP BY مطلوبة غير مطلوبة
تحتفظ بالأعمدة الأصلية لا نعم
الاستخدام النموذجي إحصائيات ملخصة الترتيب، الإزاحة، المجاميع التراكمية

(2) هيكل عبارة OVER

SQL 
function_name() OVER (
  [PARTITION BY expr]
  [ORDER BY expr [ASC|DESC] [NULLS FIRST|NULLS LAST]]
  [frame_clause]
)
100% 
flowchart TD
    A[OVER] --> B[PARTITION BY]
    B --> C[ORDER BY]
    C --> D[عبارة الإطار]
    D --> E{ROWS / RANGE / GROUPS}
    E --> F[ROWS BETWEEN ... AND ...]
    E --> G[RANGE BETWEEN ... AND ...]
    E --> H[GROUPS BETWEEN ... AND ...]
    B -.->|اختياري| C
    C -.->|اختياري| D
    style A fill:#e1f5fe
    style B fill:#fff9c4
    style C fill:#fff9c4
    style D fill:#c8e6c9

(1) ▶ مثال

SQL 
SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER () AS total_all
FROM orders;
TEXT 📖 للعرض فقط 
 order_id | customer_id | amount | total_all
----------+-------------+--------+-----------
      101 |           1 |  15000 |   750000
      102 |           1 |   8000 |   750000
      201 |           2 |  25000 |   750000

كل صف يعيد SUM الجدول بالكامل، وعدد الصفوف لم يتغير.

4. المفهوم: PARTITION BY و ORDER BY

(1) PARTITION BY للتجزئة

PARTITION BY تقسم البيانات إلى "أجزاء" مستقلة، ودالة النوافذ تحسب داخل كل جزء بشكل منفصل.

العبارة التأثير التشبيه
PARTITION BY col تجزئة حسب العمود مثل GROUP BY، لكن بدون طي
بدون PARTITION BY الجدول بالكامل جزء واحد مثل GROUP BY بدون عمود تجميع

(2) ▶ مثال

SQL 
SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders
ORDER BY customer_id, order_id;
TEXT 📖 للعرض فقط 
 order_id | customer_id | amount | customer_total
----------+-------------+--------+----------------
      101 |           1 |  15000 |          23000
      102 |           1 |   8000 |          23000
      201 |           2 |  25000 |          25000
      301 |           3 |  12000 |          57000
      302 |           3 |  45000 |          57000

(2) ORDER BY للترتيب

ORDER BY تحدد ترتيب الصفوف داخل الجزء وهي أساسية لدوال الترتيب والإزاحة.

السيناريو يحتاج ORDER BY؟ السبب
ROW_NUMBER / RANK مطلوبة الترتيب يعتمد على الترتيب
LAG / LEAD مطلوبة الصفوف السابقة/اللاحقة تعتمد على الترتيب
SUM() OVER (PARTITION BY) اختيارية بدون ترتيب، تحسب إجمالي الجزء
FIRST_VALUE / LAST_VALUE مطلوبة القيمة الأولى/الأخيرة تعتمد على الترتيب

(3) ▶ مثال

SQL 
SELECT
  order_id,
  customer_id,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn_desc
FROM orders;

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) دمج PARTITION BY + ORDER BY

جزء أولاً، ثم ترتيب؛ الترتيب يحسب بشكل مستقل داخل كل جزء.

(4) ▶ مثال

SQL 
SELECT
  order_id,
  customer_id,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY amount DESC
  ) AS rank_in_customer
FROM orders;
TEXT 📖 للعرض فقط 
 order_id | customer_id | amount | rank_in_customer
----------+-------------+--------+------------------
      102 |           1 |   8000 |                2
      101 |           1 |  15000 |                1
      201 |           2 |  25000 |                1
      302 |           3 |  45000 |                1
      301 |           3 |  12000 |                2

5. المفهوم: عبارة الإطار

(1) أنواع الإطارات الثلاثة

عبارة الإطار تقرر أي صفوف "تراها" دالة النوافذ لحسابها.

نوع الإطار الحدود مبنية على الأفضل لـ
ROWS إزاحة الصف الفعلية تحكم دقيق في الصفوف، مثلاً "آخر 3 صفوف"
RANGE إزاحة القيمة المنطقية الصفوف بنفس القيمة مجمعة، مثلاً "نفس المبلغ"
GROUPS إزاحة مجموعة نفس القيمة خاصة بـ PostgreSQL؛ تجمع حسب قيمة ORDER BY

(2) كلمات حدود الإطار المفتاحية

الكلمة المفتاحية المعنى
UNBOUNDED PRECEDING أول صف في الجزء
UNBOUNDED FOLLOWING آخر صف في الجزء
CURRENT ROW الصف الحالي
N PRECEDING N صفوف / قيم قبل
N FOLLOWING N صفوف / قيم بعد

(3) قواعد الإطار الافتراضي

لديه ORDER BY؟ الإطار الافتراضي
نعم RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
لا ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

(5) ▶ مثال

SQL 
SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_sum
FROM daily_sales;
TEXT 📖 للعرض فقط 
 order_date  | amount | running_sum
-------------+--------+-------------
 2025-01-01  |   5000 |        5000
 2025-01-02  |   8000 |       13000
 2025-01-03  |   3000 |       16000
 2025-01-04  |  12000 |       28000

(6) ▶ مثال

SQL 
SELECT
  order_date,
  amount,
  ROUND(AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ), 2) AS moving_avg_3
FROM daily_sales;

Output:

TEXT 📖 للعرض فقط 
  result  
----------
   42.50
(1 row)

(7) ▶ مثال

SQL 
SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
  ) AS sum_last_7_days
FROM daily_sales;

Output:

TEXT 📖 للعرض فقط 
  result  
----------
   42.50
(1 row)

6. المفهوم: دوال الترتيب

(1) مقارنة دوال الترتيب الأربعة

الدالة معالجة نفس القيمة المخرج متتالية؟
ROW_NUMBER تزايد صارم 1,2,3,4 نعم
RANK نفس الرتبة، تتخطى 1,1,3,4 لا
DENSE_RANK نفس الرتبة، لا تتخطى 1,1,2,3 نعم
NTILE(N) تقسيم إلى N مجموعات 1,1,2,2,3,3 —

(8) ▶ مثال

SQL 
SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK()       OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM students;
TEXT 📖 للعرض فقط 
 name   | score | rn | rnk | drnk
--------+-------+----+-----+------
 Alice  |    95 |  1 |   1 |    1
 Bob    |    90 |  2 |   2 |    2
 Charlie|    90 |  3 |   2 |    2
 Dave   |    85 |  4 |   4 |    3

(2) تجميع NTILE

NTILE(N) تقسم الصفوف المرتبة إلى N مجموعات متساوية تقريباً، شائعة الاستخدام لتحليل الرباعيات.

(9) ▶ مثال

SQL 
SELECT
  customer_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customer_summary;
TEXT 📖 للعرض فقط 
 customer_id | total_spent | quartile
-------------+-------------+----------
          12 |      500000 |        1
           5 |      350000 |        1
           8 |      280000 |        2
          19 |      150000 |        2
           3 |       90000 |        3
          22 |       60000 |        3
          41 |       25000 |        4
          15 |        5000 |        4

7. المفهوم: دوال الإزاحة والقيمة

(1) مرجع دوال الإزاحة

الدالة التأثير الاستخدام النموذجي
LAG(col, N, default) قيمة الصف رقم N قبل الحالي النمو بين الفترات
LEAD(col, N, default) قيمة الصف رقم N بعد الحالي التنبؤ، المقارنة
FIRST_VALUE(col) القيمة الأولى في النافذة مبلغ الطلب الأول
LAST_VALUE(col) القيمة الأخيرة في النافذة مبلغ الطلب الأخير
NTH_VALUE(col, N) القيمة رقم N في النافذة الطلب رقم N

(10) ▶ مثال

SQL 
SELECT
  order_date,
  daily_revenue,
  LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS prev_day,
  daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS diff,
  ROUND(
    (daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date))
    * 100.0 / NULLIF(LAG(daily_revenue, 1) OVER (ORDER BY order_date), 0),
  2) AS pct_change
FROM daily_revenue
ORDER BY order_date;

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(11) ▶ مثال

SQL 
SELECT
  month,
  revenue,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_revenue;

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) ملاحظات على FIRST_VALUE / LAST_VALUE

الإطار الافتراضي لـ LAST_VALUE هو RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW، ليس الجزء بالكامل. يجب تحديد الإطار صراحةً للحصول على آخر صف في الجزء.

الدالة الإطار الافتراضي كيفية الحصول على آخر صف في الجزء
FIRST_VALUE حتى CURRENT ROW (صحيح بالمصادفة) لا تغيير مطلوب
LAST_VALUE حتى CURRENT ROW (ليس آخر صف!) أضف ROWS BETWEEN ... AND UNBOUNDED FOLLOWING

(12) ▶ مثال

SQL 
SELECT
  order_id,
  customer_id,
  amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS first_order_amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_amount
FROM orders;

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(13) ▶ مثال

SQL 
SELECT
  order_id,
  customer_id,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_order_amount
FROM orders;

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

8. المفهوم: المجموع التراكمي والمتوسط المتحرك

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

الإطار المعنى SQL
الإطار الافتراضي من بداية الجزء إلى الصف الحالي SUM() OVER (ORDER BY col)
ROWS صريح نفس ما سبق ROWS UNBOUNDED PRECEDING
RANGE صريح صفوف نفس القيمة مجمعة RANGE UNBOUNDED PRECEDING

(14) ▶ مثال

SQL 
SELECT
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month) AS running_revenue
FROM monthly_revenue;
TEXT 📖 للعرض فقط 
  month   | revenue | running_revenue
----------+---------+----------------
 2025-01  |  500000 |         500000
 2025-02  |  620000 |        1120000
 2025-03  |  580000 |        1700000
 2025-04  |  710000 |        2410000

(2) المتوسط المتحرك

(15) ▶ مثال

SQL 
SELECT
  order_date,
  daily_revenue,
  ROUND(AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS ma_7day
FROM daily_revenue;

Output:

TEXT 📖 للعرض فقط 
  result  
----------
   42.50
(1 row)

(3) مجموع تراكمي مجزأ

(16) ▶ مثال

SQL 
SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS cumulative_spent
FROM orders
ORDER BY customer_id, order_date;

Output:

TEXT 📖 للعرض فقط 
  result  
----------
   42.50
(1 row)

9. دوال النوافذ مقابل دوال التجميع

البعد دالة التجميع دالة النوافذ
عدد الصفوف مطوية إلى صف واحد عدد الصفوف الأصلي محفوظ
الصيغة SUM(col) SUM(col) OVER(...)
GROUP BY مطلوبة غير مطلوبة
حساب لكل صف بعد واحد نوافذ مختلفة متعددة ممكنة
الأداء عادة أسرع تحتاج فرز + تجزئة، أبطأ قليلاً
الأفضل لـ تقارير ملخصة الترتيب، الإزاحة، المجاميع التراكمية

(17) ▶ مثال

نمط دالة التجميع:

SQL 
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

Output:

TEXT 📖 للعرض فقط 
  result  
----------
   42.50
(1 row)

نمط دالة النوافذ (يحتفظ بالصفوف الأصلية):

SQL 
SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

10. مثال شامل

تحليل احتفاظ المستخدمين لـ Charlie — عد المستخدمين الجدد اليوميين ومعدلات احتفاظهم في اليوم 7 / اليوم 30:

SQL 
WITH first_login AS (
  SELECT
    user_id,
    MIN(login_date) AS first_date,
    ROW_NUMBER() OVER (PARTITION BY MIN(login_date) ORDER BY user_id) AS reg_rank
  FROM user_logins
  GROUP BY user_id
),
daily_new_users AS (
  SELECT
    first_date AS cohort_date,
    COUNT(*) AS new_users
  FROM first_login
  GROUP BY first_date
),
retention_base AS (
  SELECT
    fl.first_date AS cohort_date,
    fl.user_id,
    ul.login_date,
    ul.login_date - fl.first_date AS day_offset
  FROM first_login fl
  JOIN user_logins ul ON fl.user_id = ul.user_id
),
retention_count AS (
  SELECT
    cohort_date,
    day_offset,
    COUNT(DISTINCT user_id) AS retained_users
  FROM retention_base
  WHERE day_offset IN (0, 7, 30)
  GROUP BY cohort_date, day_offset
)
SELECT
  r.cohort_date,
  n.new_users,
  MAX(CASE WHEN r.day_offset = 0  THEN r.retained_users END) AS d0,
  MAX(CASE WHEN r.day_offset = 7  THEN r.retained_users END) AS d7,
  MAX(CASE WHEN r.day_offset = 30 THEN r.retained_users END) AS d30,
  ROUND(
    MAX(CASE WHEN r.day_offset = 7  THEN r.retained_users END) * 100.0
    / NULLIF(n.new_users, 0), 1
  ) AS day7_rate,
  ROUND(
    MAX(CASE WHEN r.day_offset = 30 THEN r.retained_users END) * 100.0
    / NULLIF(n.new_users, 0), 1
  ) AS day30_rate
FROM retention_count r
JOIN daily_new_users n ON r.cohort_date = n.cohort_date
GROUP BY r.cohort_date, n.new_users
ORDER BY r.cohort_date;

11. ترتيب التنفيذ

موقع دوال النوافذ في ترتيب تنفيذ SQL:

100% 
flowchart TD
    A[FROM] --> B[WHERE]
    B --> C[GROUP BY]
    C --> D[HAVING]
    D --> E["دوال النوافذ<br/>OVER / PARTITION / ORDER / FRAME"]
    E --> F[SELECT]
    F --> G[DISTINCT]
    G --> H[ORDER BY]
    H --> I[LIMIT]

    style E fill:#c8e6c9
    style D fill:#fff9c4
الخطوة العبارة الوصف
1 FROM تحديد مصدر البيانات
2 WHERE تصفية الصفوف
3 GROUP BY تجميع التجميع
4 HAVING تصفية المجموعات
5 دوال النوافذ حساب عبر النتيجة المصفاة
6 SELECT اختيار أعمدة المخرجات
7 DISTINCT إزالة التكرار
8 ORDER BY الترتيب النهائي
9 LIMIT تحديد الصفوف

❓ أسئلة شائعة

س هل يمكن لدوال النوافذ الظهور في عبارة WHERE؟
ج لا. دوال النوافذ تنفذ بعد WHERE، لذا WHERE لا يمكنها الإشارة إلى نتائجها. لفها في استعلام فرعي أو CTE وصفِّ هناك.
س ما الفرق بين ROW_NUMBER و RANK؟
ج ROW_NUMBER تزايد صارم (1,2,3,4)؛ RANK تعطي القيم المتساوية نفس الرتبة وتتخطى (1,1,3,4). لأفضل-1 بدون تكرار، استخدم ROW_NUMBER.
س لماذا لا تعيد LAST_VALUE آخر صف في الجزء؟
ج الإطار الافتراضي هو RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. يجب كتابة ROWS BETWEEN ... AND UNBOUNDED FOLLOWING صراحةً.
س هل يمكن لدوال نوافذ متعددة مشاركة عبارة OVER واحدة؟
ج نعم. PostgreSQL تدعم الاسم المستعار لعبارة WINDOW: WINDOW w AS (PARTITION BY ...)، ثم اكتب OVER w في دوال متعددة.
س ما الفرق بين إطاري ROWS و RANGE؟
ج ROWS تزيح بالصفوف الفعلية؛ RANGE تزيح بالقيمة المنطقية. RANGE تعامل الصفوف بنفس قيمة ORDER BY كحد واحد. معظم حالات المجموع التراكمي تستخدم ROWS.
س كيف أحسن أداء دوال النوافذ؟
ج تأكد من وجود فهارس على أعمدة PARTITION BY + ORDER BY؛ قلل عدد الأجزاء؛ تجنب حسابات الإطار المعقدة على الأجزاء الكبيرة؛ صفِّ أولاً بـ CTE، ثم احسب النافذة.

📖 ملخص

📝 تمارين

  1. ⭐ استخدم ROW_NUMBER لاستعلام أفضل طلبين بأعلى مبلغ لكل عميل.

  2. ⭐ استخدم LAG لحساب فرق المبلغ بين الطلبات المتجاورة لكل عميل.

  3. ⭐⭐ احسب المتوسط المتحرك لـ 7 أيام للإيراد اليومي لكل منطقة.

  4. ⭐⭐ استخدم DENSE_RANK و NTILE(4) لتقسيم العملاء إلى 4 مستويات حسب إجمالي الإنفاق.

  5. ⭐⭐⭐ اكتب عبارة SQL واحدة تحسب معدل الاحتفاظ في اليوم 1 / اليوم 7 / اليوم 30 للمستخدمين الجدد اليوميين، باستخدام دوال النوافذ بدلاً من self-joins.

Web-Tutorial.com

فريق Web-Tutorial التقني

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

100%