في هذا الدرس 6تصفّح المواضيع
تقوم استعلامات التجميع التقليدية عبر GROUP BY بطيّ ودمج جميع صفوف المجموعة في سطر تلخيصي واحد، مما يخفي تفاصيل السجلات الفردية. أما دوال النوافذ التحليلية (Window Functions) في MySQL 8، والتي تتميز بوجود جملة OVER (...)، فتحسب المجاميع والترتيب عبر مجموعة من الصفوف مع الاحتفاظ بكل صف فردي في النتيجة:
- عرض التفصيل والإجمالي جنباً إلى جنب: يمكنك عرض قيمة كل طلب بجانب الإجمالي العام لجميع الطلبات أو حساب النسبة المئوية لكل صف في مسح واحد.
- النافذة الشاملة الكلية: عند ترك قوسي جملة
OVERفارغين تشمل النافذة التحليلية جميع صفوف النتيجة كإطار واحد.
-- Show each order alongside grand total and percentage share
SELECT
order_id,
user_id,
total_amount,
SUM(total_amount) OVER () AS grand_total,
ROUND(
total_amount * 100.0 / SUM(total_amount) OVER (), 2
) AS pct_of_total
FROM orders WHERE status = 'paid';داخل قوسي جملة OVER (...)، يمكنك التحكم في تقسيم النافذة وترتيب صفوفها عبر عبارتين رئيسيتين:
- التقسيم بـ
PARTITION BY: يقسم الصفوف إلى مجموعات فرعية مستقلة (مثل كل قسم أو كل عميل على حدة) بحيث تبدأ حسابات النافذة من جديد عند حدود كل مجموعة دون دمج الصفوف. - الترتيب الداخلي بـ
ORDER BY: يحدد تسلسل الصفوف داخل كل قسم؛ وعند إضافةORDER BYداخل دالة مثلSUM(...) OVER (PARTITION BY ... ORDER BY ...)يتحول المجموع تلقائياً إلى مجموع تراكمي جاري (Running Total) من أول صف في القسم حتى الصف الحالي.
-- Compute per-customer running cumulative spend across order dates
SELECT
user_id,
order_id,
order_date,
total_amount,
SUM(total_amount) OVER (
PARTITION BY user_id
ORDER BY order_date, order_id
) AS running_customer_spend
FROM orders;يوفر MySQL 8 ثلاث دوال أساسية لترتيب الصفوف تمنح كل سجل رقماً تسلسلياً يبدأ من 1 وفق ترتيب جملة ORDER BY داخل النافذة، وتختلف فيما بينها في طريقة التعامل مع القيم المتعادلة:
- دالة
ROW_NUMBER: تمنح كل صف رقماً متسلسلاً فريداً (1, 2, 3, 4) حتى لو تساوت القيمتان. - دالة
RANK: تمنح القيم المتساوية نفس المركز، لكنها تقفز عن الأرقام التالية بعدد مرات التعادل (مثل1, 1, 3). - دالة
DENSE_RANK: تمنح القيم المتساوية نفس المركز مع عدم ترك أي فجوات في التسلسل الرقمي (مثل1, 1, 2, 3).
-- Compare ROW_NUMBER, RANK, and DENSE_RANK per department
SELECT
department_id, emp_name, salary,
ROW_NUMBER() OVER w AS row_num,
RANK() OVER w AS rnk,
DENSE_RANK() OVER w AS dense_rnk
FROM employees
WINDOW w AS (
PARTITION BY department_id ORDER BY salary DESC
);نظراً لأن دوال النوافذ تُنفَّذ منطقياً بعد جملة WHERE، لا يسمح SQL بكتابة دالة نافذة مباشرة داخل WHERE. ولحل مسألة استخراج أعلى N سجلات في كل فئة (مثل أغلى 3 منتجات داخل كل قسم)، ندمج تعبير WITH مع دالة الترتيب:
- الخطوة الأولى (داخل CTE): نحسب عمود الترتيب
DENSE_RANK() OVER (PARTITION BY category_id ORDER BY unit_price DESC) AS price_rank. - الخطوة الثانية (في الاستعلام الخارجي): نصفي مخرجات التعبير الجدولي بكتابة
WHERE price_rank <= 3.
-- Fetch top 2 products per category via CTE + DENSE_RANK
WITH ranked_products AS (
SELECT product_id, category_id, product_title, unit_price,
DENSE_RANK() OVER (
PARTITION BY category_id ORDER BY unit_price DESC
) AS price_rank
FROM products
)
SELECT category_id, product_title, unit_price, price_rank
FROM ranked_products WHERE price_rank <= 2;لمقارنة قيمة الصف الحالي بقيمة الصف السابق أو الصف التالي زمنياً (مثل حساب نمو الإيرادات الشهري أو الفارق الزمني بين طلبين متتاليين لنفس العميل)، يوفر MySQL 8 دالتي الإزاحة الموضعية LAG وLEAD:
- دالة
LAG(expr, offset, default_val): تقرأ قيمة العمود من الصف السابق بمقدار خطواتoffset(الافتراضي 1)، وتُرجع القيمة البديلةdefault_valعند أول صف في القسم بدلاً منNULL. - دالة
LEAD(expr, offset, default_val): تقرأ قيمة العمود من الصف التالي داخل الترتيب المحدد للنافذة.
-- Compare each month revenue with previous and next month
SELECT
report_month, revenue,
LAG(revenue, 1, 0.00) OVER (
ORDER BY report_month
) AS prev_month_rev,
LEAD(revenue, 1, 0.00) OVER (
ORDER BY report_month
) AS next_month_rev
FROM monthly_sales;يقارن الجدول التالي بين أهم دوال وصيغ النوافذ التحليلية في MySQL 8:
| دالة / صيغة النافذة | نمط الكتابة في SQL | سلوك التعادل والحدود | الاستخدام الهندسي الأمثل |
|---|---|---|---|
دالة تجميع مع OVER | SUM(col) OVER (PARTITION BY p ORDER BY o) | تحتفظ بجميع الصفوف التفصيلية | حساب النسب المئوية والمجاميع التراكمية الجارية |
دالة ROW_NUMBER | ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) | أرقام فريدة متتالية (1, 2, 3) | إزالة التكرار واختيار أحدث سجل وحيد لكل عميل |
RANK و DENSE_RANK | DENSE_RANK() OVER (PARTITION BY p ORDER BY o) | DENSE_RANK لا تترك فجوات | لوحات الصدارة واستخراج أعلى N سجلات بكل فئة |
LAG و LEAD | LAG(col, 1, 0) OVER (PARTITION BY p ORDER BY o) | تُرجع القيمة الافتراضية عند الأطراف | حساب فروق النمو الشهري والمقارنة بالصف السابق والتالي |
ملخص الدرس: أتقنّا في هذا الدرس استخدام جملة OVER (PARTITION BY ... ORDER BY ...) لحساب المجاميع والنسب التراكمية دون طي الصفوف، وقارنّا بين دوال الترتيب ROW_NUMBER وRANK وDENSE_RANK، وبنينا نمط أعلى السجلات لكل فئة، وقارنّا الصفوف المتجاورة عبر LAG وLEAD.
الخطوة التالية والدرس القادم: في الدرس القادم نبدأ وحدة العروض والفهرسة بدراسة «العروض الافتراضية CREATE VIEW والعروض القابلة للتحديث في MySQL».
ملخص الدرس
حصاد الدرسمراجعة مركّزة لأهم المفاهيم والقواعد الهندسية التي أتقنتها في هذا الدرس قبل التطبيق العملي
ماذا تعلّمت في هذا الدرس؟
تمنحك دوال النوافذ مع OVER وPARTITION BY وDENSE_RANK وLAG تحليلات متقدمة ومجاميع تراكمية دقيقة مع الاحتفاظ بكامل تفاصيل الصفوف. الخطوة التالية: انتقل إلى درس «العروض الافتراضية CREATE VIEW والعروض القابلة للتحديث في MySQL».
سجّل الدخول لحفظ تقدمك
تابع إنجاز المواضيع والمصطلحات والمهام، وكمّل من آخر خطوة على أي جهاز.
المصطلحات5
راجع المعنى، اشرحه لنفسك، ثم علّم البطاقة كمفهومة.
- 01 Window Functions دوال النوافذ
- تعلّم دوال النوافذ التحليلية OVER و PARTITION BY والترتيب في MySQL بأسلوب MySQL 8 الحديث مع أمثلة استعلامات SQL وتطبيقات تفاعلية.
- للمراجعة
- 02 Window OVER vs Grouping نافذة OVER مقابل التجميع
- المفهوم الهندسي والصياغة العملية الخاصة بقسم نافذة OVER مقابل التجميع في MySQL 8.
- للمراجعة
- 03 Partitioning and Ordering Windows تقسيم النافذة بـ PARTITION BY
- المفهوم الهندسي والصياغة العملية الخاصة بقسم تقسيم النافذة بـ PARTITION BY في MySQL 8.
- للمراجعة
- 04 Ranking Rows with DENSE RANK ترتيب الصفوف بدوال الترتيب
- المفهوم الهندسي والصياغة العملية الخاصة بقسم ترتيب الصفوف بدوال الترتيب في MySQL 8.
- للمراجعة
- 05 Top N Per Category Pattern استخراج أعلى السجلات بكل فئة
- المفهوم الهندسي والصياغة العملية الخاصة بقسم استخراج أعلى السجلات بكل فئة في MySQL 8.
- للمراجعة
لا توجد بطاقات تطابق البحث والتصفية.
اقرأ البطاقات بحرية. سجّل الدخول لحفظ تقدم مراجعتك.
الاختصارات3
راجع المعنى، اشرحه لنفسك، ثم علّم البطاقة كمفهومة.
- 01 OVER OVER
- SQL Window Specification Clause
- الجملة التي تحدد إطار النافذة التحليلية وتمنع طي الصفوف التفصيلية.
- للمراجعة
- 02 PARTITION BY PARTITION BY
- Window Group Partition Subclause
- تقسّم النافذة التحليلية إلى مجموعات فرعية مستقلة دون دمج الصفوف كما يفعل GROUP BY.
- للمراجعة
- 03 LAG / LEAD LAG / LEAD
- Positional Row Offset Window Functions
- دالتا نوافذ لقراءة قيم الصفوف السابقة (LAG) أو اللاحقة (LEAD) داخل نفس القسم المرتب.
- للمراجعة
لا توجد بطاقات تطابق البحث والتصفية.
اقرأ البطاقات بحرية. سجّل الدخول لحفظ تقدم مراجعتك.
مهامك قبل الاختبار
خطوة صغيرة كل مرة. وإنجاز تقدر ترجع له.
-
نفّذ أمثلة SQL المشروحة في قسم نافذة OVER مقابل التجميع داخل قاعدة بياناتك المحلية وتحقق من مجموعة النتائج.
-
بدّل بين الحالات التفاعلية في قسم تقسيم النافذة بـ PARTITION BY وقارن كيف ينفّذ محرك MySQL 8 كل استعلام.
تأشير المهمة إقرار منك بإتمامها، وليس تصحيحًا آليًا للكود. يُحفظ تقدمك في حسابك.
اختبر فهمك.
ووصلها للإتقان.
كل سؤال خطوة لقدّام. اختار، اكتب كود، وشوف نتيجتك فورًا.
أكمل المهام، واجمع كل الإجابات الصحيحة. أقل من 100%؟ راجع وأعد المحاولة.
الأسئلة متاحة للاطلاع. سجّل الدخول للتفاعل وحفظ تقدمك.
لمحة عن التحدي5 أسئلة تنتظرك ＋
ما هو الفرق الجوهري بين التجميع التقليدي بـ GROUP BY ودوال النوافذ التحليلية التي تستخدم OVER (...) في MySQL 8؟
ركّز على مصير الصفوف التفصيلية في النتيجة النهائية.
ماذا تحسب الصيغة SUM(total_amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id) لكل عميل؟
تذكّر أثر إضافة ORDER BY داخل نافذة دالة الجمع SUM المقسمة حسب العميل.
إذا تعادل ثلاثة موظفين في المركز الأول (1) بالراتب داخل قسم معين، فما هو رقم المركز الذي تمنحه دالة DENSE_RANK للموظف الذي يليهم مباشرة؟
تذكّر سلوك دالة DENSE_RANK التي لا تترك فجوات رقمية بعد التعادل.
لأن دوال النوافذ تُحسَب منطقياً بعد جملة WHERE، فإن التصفية بشرط مثل price_rank <= 3 تتطلب وضع استعلام النافذة داخل تعبير WITH أو جدول مشتق أولاً.
تحقق من سبب استخدام CTE عند تصفية نتائج دوال الترتيب بشرط WHERE.
أي دالة نافذة تقرأ قيمة عمود من الصف السابق مباشرةً داخل القسم المرتب دون الحاجة لعمل ربط ذاتي للجدول؟
اختر الدالة المخصصة للرجوع إلى الصف السابق.