SQL Interview Prep · درس

المتوسطات المتحركة عبر نافذة منزلقة

حساب متوسطات متحركة لعدد N من الفترات باستخدام BETWEEN PRECEDING وCURRENT ROW

المتوسطات المتحركة عبر نافذة منزلقة درس مجاني في SQL Interview Prep على CoddyKit. هذا هو الدرس 3 من أصل 4. يمكنك قراءة الدرس كاملاً أدناه مجاناً — ثم تمرن عليه مباشرة في المتصفح باستخدام محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7. هذا الدرس جزء من مسار التعلم في SQL Interview Prep، وتقدمك يتزامن عبر الويب وتطبيق CoddyKit. تتضمن دورة SQL Interview Prep 4 دروس في المجموع.

سؤال المتوسط المتحرك

يُطرح هذا السؤال باستمرار على المحللين: "احسب متوسطًا متحركًا للإيرادات على مدى 7 أيام." يعمل المتوسط المتحرك (أو المتداول) على تنعيم البيانات اليومية كثيرة التذبذب، إذ يحسب متوسط كل نقطة مع جيرانها القريبين.

الإجابة المناسبة لمقابلة العمل هي استخدام AVG ضمن نافذة مع إطار منزلق محدد صراحةً. وتتمثل المهارة الأساسية في اختيار حدود الإطار بحيث تنزلق النافذة بصورة صحيحة على امتداد الترتيب.

النمط الأساسي

المتوسط المتحرك هو AVG(value) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW). تنزلق النافذة بحيث تغطي، عند كل صف، الصف الحالي والصفوف n السابقة.

بالنسبة إلى نافذة مدتها 7 أيام على صفوف يومية، تعود 6 صفوف إلى الخلف بالإضافة إلى الصف الحالي، فيكون المجموع 7 صفوف. ويُعد الخطأ في العد من أكثر الأخطاء الشائعة التي يكتشفها المحاورون.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

حساب حجم النافذة بشكل صحيح

يساوي حجم النافذة preceding + 1 (إذ تمثل +1 الصف الحالي). لذلك:

نافذة من 3 صفوف: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
نافذة من 7 صفوف: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
نافذة من 30 صفًا: ROWS BETWEEN 29 PRECEDING AND CURRENT ROW

اذكر هذه العملية الحسابية بصوت عالٍ في مقابلة العمل كي يرى أعضاء اللجنة أنك تتصرف بوعي ولا تخمّن.

النوافذ المتمركزة مقابل النوافذ الخلفية

المثال السابق هو متوسط خلفي؛ فهو ينظر إلى الخلف فقط، ولذلك يكون سببيًا وآمنًا للوحات معلومات التنبؤ.

أما المتوسط المتمركز فينظر في الاتجاهين، مثل ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING لنافذة متمركزة من 7 صفوف. تعمل النوافذ المتمركزة على تنعيم البيانات بصورة أكثر تماثلًا، لكن لا يمكن حسابها للصفوف الأحدث في الوقت الفعلي. اذكر أي النوعين يتطلبه سياق الاستخدام.

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AS centered_avg_7
FROM daily_sales;

تأثير حافة النافذة

في بداية البيانات تمامًا، لا تكون النافذة الكاملة متاحة بعد. ففي الصف الأول من متوسط خلفي مدته 7 أيام، يتوفر صف واحد فقط، ولذلك تحسب AVG متوسط هذه القيمة وحدها.

يعني ذلك أن الصفوف المبكرة تعرض متوسطًا خلال فترة تهيئة باستخدام عدد أقل من الصفوف. ويسأل المحاورون عن كيفية التعامل مع ذلك. هناك خياران: قبول النافذة الجزئية، أو إخفاء الصفوف المبكرة باشتراط توفر العدد الكامل.

إخفاء النوافذ الجزئية

إذا أراد قطاع الأعمال ظهور NULL إلى أن تتوفر نافذة كاملة، فاستخدم COUNT(*) على الإطار نفسه، وأخفِ النوافذ القصيرة باستخدام تعبير CASE.

هذه لمسة متقنة توضح أنك فكرت في صحة النتائج عند الحدود، وهو تفصيل يتجاهله كثير من المرشحين.

SELECT
  sale_date,
  CASE WHEN COUNT(*) OVER w = 7
       THEN AVG(amount) OVER w
  END AS moving_avg_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

إعادة استخدام إطار باستخدام WINDOW

استخدم الاستعلام السابق جملة WINDOW مسماة. وعندما يظهر الإطار نفسه عدة مرات، فإن تعريفه مرة واحدة بصيغة WINDOW w AS (...) ثم الإشارة إليه باستخدام OVER w يحافظ على مبدأ DRY ويجعل الاستعلام سهل القراءة.

يدعم Postgres وMySQL 8 وSQL Server النوافذ المسماة. واستخدام إحداها في مقابلة العمل يدل على إلمام يتجاوز نسخ دوال النوافذ ولصقها.

SELECT
  sale_date,
  AVG(amount) OVER w AS avg_7d,
  SUM(amount) OVER w AS sum_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

مشكلة أيام التقويم مقابل الصفوف

هناك فخ خفي: إذا كان الجدول يتضمن أيامًا مفقودة، فإن ROWS 6 PRECEDING يمتد عبر آخر 7 صفوف مسجلة، وقد يغطي أكثر من 7 أيام تقويمية.

ولحساب متوسط حقيقي مدته 7 أيام تقويمية ويراعي الفجوات، استخدم RANGE مع إزاحة زمنية، أو اربط أولًا بسلسلة تواريخ كاملة بحيث يحتوي كل يوم على صف. إن توضيح هذا الفرق هو بالضبط الفكرة المتعلقة بـ ROWS مقابل RANGE من الدرس السابق.

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS calendar_avg_7d
FROM daily_sales;

المتوسطات المتحركة لكل مجموعة

مثل الإجماليات التراكمية تمامًا، تحتاج المتوسطات المتحركة عادةً إلى إعادة البدء لكل كيان. أضف PARTITION BY كي يحصل كل منتج أو متجر على نافذته المتحركة الخاصة، من دون أن تمتد إلى مجموعات أخرى.

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

SELECT
  product_id,
  sale_date,
  AVG(amount) OVER (
    PARTITION BY product_id
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS product_ma_7d
FROM daily_sales;

التنعيم أثناء العمل

لماذا نستخدمه؟ فالإيرادات اليومية متذبذبة؛ ويكشف المتوسط المتحرك الاتجاه العام. ومن الإجابات الشائعة للمحللين إقران القيمة الخام بخطها المنعّم كي تعرض لوحة المعلومات الإشارة والضوضاء معًا.

يمكنك أيضًا مقارنة متوسط متحرك قصير بآخر طويل (مثل 7 أيام مقابل 30 يومًا) لاكتشاف الزخم، وهو المكافئ في SQL لتقاطع المتوسطات المتحركة.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  AS ma_7,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30
FROM daily_sales;

قائمة التحقق للمقابلة

للإجابة بإتقان عن سؤال المتوسط المتحرك، تناول النقاط التالية:

AVG OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW).
حجم النافذة = عدد الصفوف السابقة + 1.
الاختيار بين النافذة الخلفية والمتمركزة.
فترة تهيئة النافذة الجزئية وكيفية إخفائها.
الاختيار بين ROWS وRANGE عند فقدان بعض الأيام.
استخدام PARTITION BY لإعادة البدء لكل كيان.

تحقق سريع

اختر إطار المتوسط المتحرك الخلفي لمدة 7 أيام على صفوف يومية.

مراجعة: المتوسطات المتحركة

المتوسط المتحرك هو AVG(value) OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW)، حيث يساوي حجم النافذة عدد الصفوف السابقة زائد واحد. اختر نافذة خلفية أو متمركزة بما يتناسب مع سياق الاستخدام، وتعامل مع صفوف التهيئة باستخدام شرط COUNT، وانتقل إلى فاصل RANGE عندما تكون الفجوات التقويمية مهمة.

أعد البدء لكل كيان باستخدام PARTITION BY، وأعد استخدام الإطارات باستخدام WINDOW مسماة. بعد ذلك سنحوّل المجاميع التراكمية إلى نسب مئوية باستخدام الحصة التراكمية من الإجمالي.

الأسئلة الشائعة

هل درس «المتوسطات المتحركة عبر نافذة منزلقة» مجاني؟

نعم — نص درس «المتوسطات المتحركة عبر نافذة منزلقة» كامل متاح مجاناً هنا على الويب. لتمرينه بشكل تفاعلي (محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7) وفتح باقي دورة SQL Interview Prep، انتقل إلى CoddyKit PRO. تتضمن دورة SQL Interview Prep 4 دروس في المجموع.

ماذا ستتعلم في «المتوسطات المتحركة عبر نافذة منزلقة»؟

حساب متوسطات متحركة لعدد N من الفترات باستخدام BETWEEN PRECEDING وCURRENT ROW تتمرن على SQL Interview Prep مع أكواد عملية تشغلها مباشرة في المتصفح، ومدرس ذكاء اصطناعي متاح 24/7 يجيب على أسئلتك أثناء عملك.

هل أحتاج إلى خبرة سابقة لأبدأ SQL Interview Prep؟

لا تُشترط خبرة سابقة. SQL Interview Prep على CoddyKit منظم للمبتدئين حتى المتقدمين، لذا يمكنك البدء من هنا أو من البداية والتقدم بسرعتك الخاصة. هذا هو الدرس 3 من أصل 4.

كم من الوقت يستغرق درس «المتوسطات المتحركة عبر نافذة منزلقة»؟

معظم دروس CoddyKit تستغرق حوالي 5–10 دقائق. كل منها موجز وتفاعلي، لذا تحرز تقدماً مستمراً وتستأنف من حيث توقفت عبر الويب والتطبيق.

هل يمكنني كتابة وتشغيل أكواد في درس SQL Interview Prep هذا؟

نعم. كل درس في SQL Interview Prep يتضمن محرر أكواد مدمج، لذا تكتب وتشغل أكواداً حقيقية مباشرة في متصفحك وتحصل على تعليقات فورية من الذكاء الاصطناعي — بدون إعداد محلي.

جميع الدروس في هذه الدورة

المجاميع التراكمية باستخدام إطارات النوافذ
تأطير ROWS مقابل RANGE
المتوسطات المتحركة عبر نافذة منزلقة
التوزيع التراكمي والنسبة من الإجمالي

← العودة إلى SQL Interview Prep