SQL Interview Prep · درس

الجزر مع تغيرات التاريخ والحالة

تجميع الفترات المتتالية ذات الحالة نفسها، وهو سؤال شائع عن حالة الاشتراك

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

الجزر المحددة بتغيّر القيمة

الصيغة الأكثر ارتباطًا بالعمل من مسائل الفجوات والجزر هي تجميع الصفوف المتتالية التي تشترك في الحالة نفسها، وتحويل سجل أحداث صاخب إلى فترات حالة واضحة. ومن الصيغ الكلاسيكية للسؤال: «بالنظر إلى سجل أحداث اشتراك، أعد صفًا واحدًا لكل فترة متواصلة بقي فيها المستخدم في كل حالة».

هنا لا يعني التجاور «اختلاف القيم بمقدار 1»، بل يعني أن الحالة لم تتغير عن الصف السابق. تبدأ جزيرة جديدة في اللحظة التي تتغير فيها الحالة. وهنا تتفوق التقنية المعتمدة على LAG على حيلة رقم الصف البسيطة.

مثال الاشتراك

لنفترض وجود جدول sub_events لمستخدم واحد، مرتبًا حسب التاريخ:

2026-01-01 نشطة
2026-02-01 نشطة
2026-03-01 متوقفة مؤقتًا
2026-04-01 نشطة
2026-05-01 نشطة

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

CREATE TABLE sub_events (
  user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
 (1,'active','2026-01-01'),(1,'active','2026-02-01'),
 (1,'paused','2026-03-01'),(1,'active','2026-04-01'),
 (1,'active','2026-05-01');

وضع علامة عند تغيّر الحالة

استخدم LAG لمقارنة حالة كل صف بالحالة السابقة. عندما تختلفان (أو تكون السابقة هي NULL في الصف الأول)، تبدأ جزيرة جديدة. نضع القيمة 1 عند حدوث تغيير، و0 في غير ذلك.

رتّب ترتيبًا صارمًا حسب التاريخ ضمن المستخدم. وبالنسبة إلى بياناتنا، تكون علامات التغيير 1,0,1,1,0، وهي تحدد حدود الفترات الثلاث.

SELECT
  user_id, status, event_date,
  CASE
    WHEN status = LAG(status)
      OVER (PARTITION BY user_id ORDER BY event_date)
    THEN 0 ELSE 1
  END AS is_change
FROM sub_events;

تحويل المجموع التراكمي إلى مفتاح فترة

كما سبق، ينتج عن المجموع التراكمي لعلامات التغيير مفتاح مجموعة ثابت داخل كل فترة حالة: 1,1,2,3,3 لصفوفنا. ويمثل كل مفتاح مميز فترة متواصلة واحدة.

لن تنجح حيلة فرق رقم الصف هنا، لأن الحالة ليست رقمًا يتقدم بمقدار 1؛ فوصفة LAG مع المجموع التراكمي هي الأداة الصحيحة عندما يعني التجاور «قيمة لم تتغير».

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS is_change
  FROM sub_events
)
SELECT user_id, status, event_date,
  SUM(is_change)
    OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;

تجميع الصفوف في فترات الحالة

نفّذ الآن GROUP BY على user_id وstatus ومفتاح المجموع التراكمي، للإبلاغ عن نطاق كل فترة. إن تضمين status في GROUP BY آمن لأنه ثابت داخل الفترة، كما يتيح لك تحديده دون استخدام دالة تجميع.

والنتيجة هي ثلاثة صفوف بالضبط: نشطة من 01-01 إلى 02-01، ومتوقفة مؤقتًا في 03-01، ونشطة من 04-01 إلى 05-01.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
),
keyed AS (
  SELECT user_id, status, event_date,
    SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
  FROM flagged
)
SELECT user_id, status,
  MIN(event_date) AS period_start,
  MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

من الأحداث إلى الفترات نصف المفتوحة

نقطة دقيقة في المقابلات: يحدد تاريخ الحدث الوقت الذي بدأت فيه الحالة، وتنتهي الفترة فعليًا عند بدء الحالة التالية، وليس في تاريخ آخر حدث للحالة نفسها. وغالبًا ما تكون نهاية الفترة الصحيحة هي بداية الفترة التالية، ويُعبَّر عنها بفترة نصف مفتوحة [start, next_start).

احسب بداية الفترة التالية باستخدام LEAD على الفترات المدمجة، مع إبقاء الفترة الأخيرة مفتوحة النهاية (NULL أو 'current').

WITH periods AS (
  -- output of the previous collapse step
  SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
  LEAD(period_start)
    OVER (PARTITION BY user_id ORDER BY period_start)
    AS period_end_exclusive
FROM periods;

التعامل مع الحالات المتكررة المتتالية

ماذا لو احتوى السجل على صفوف زائدة مثل active, active, active من دون أي تغيير بينها؟ تكون علامة التغيير مساوية لـ 0 في الصفوف المتكررة، لذلك يحافظ المجموع التراكمي تلقائيًا على وجودها في جزيرة واحدة. وهذا هو السلوك المطلوب: تُدمج الحالات المتطابقة المتتالية في فترة واحدة.

يُعد هذا الإزالة الطبيعية لتكرار الصفوف ميزة أساسية لأسلوب علامة التغيير، ومن المفيد الإشارة إليها أمام المحاوِر.

متى ينبغي للفجوات الزمنية أن تنهي فترة

أحيانًا لا تكفي «الحالة نفسها»؛ إذ ينبغي لفجوة زمنية كبيرة أن تنهي الفترة حتى لو كانت الحالة متطابقة. فمثلًا، قد يُعد ظهور الحالة active في يناير ثم ظهورها مجددًا بعد ستة أشهر من الصمت فترتين منفصلتين.

وسّع علامة التغيير بإضافة شرط ثانٍ: ابدأ جزيرة جديدة عندما تتغير الحالة أو يتجاوز الزمن منذ الحدث السابق حدًا معينًا. وبهذا تجمع قاعدتا التجاور بطريقة واضحة.

CASE
  WHEN status = LAG(status)
         OVER (PARTITION BY user_id ORDER BY event_date)
   AND event_date - LAG(event_date)
         OVER (PARTITION BY user_id ORDER BY event_date) <= 31
  THEN 0 ELSE 1
END AS is_change

عدّ مرات تبديل الحالة

سؤال متابعة طبيعي: «كم مرة بدّل هذا المستخدم حالته؟» الإجابة هي ببساطة عدد علامات التغيير ناقص أول علامة، لأنها تشير إلى الحالة الابتدائية لا إلى عملية تبديل.

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

WITH flagged AS (
  SELECT user_id,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;

لماذا يتفوق هذا الأسلوب على عمليات الربط الذاتي هنا

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

يتعامل خط أنابيب LAG-flag-runningsum-groupby مع أي عدد من الفترات في مرور واحد ومن دون عمليات ربط. إن توضيح هذا الفرق، أي المرور الواحد الخطي مقابل الربط الذاتي التربيعي، هو بالضبط نوع التفكير المتقدم الذي يقدّره المحاوِرون.

قالب قابل لإعادة الاستخدام

احفظوا هذا القالب المؤلف من أربعة بنود؛ فهو يحل كامل عائلة مسائل جزر الحالة، ولا يتطلب سوى تغيير اختبار التجاور في CASE:

العلامة: استخدام CASE مع LAG لاكتشاف جزيرة جديدة.
المفتاح: حساب SUM تراكمي للعلامة، مع التقسيم والترتيب.
الدمج: استخدام GROUP BY على عمود التقسيم والحالة والمفتاح.
الفترة (اختياري): استخدام LEAD لتحديد نهايات الفترات نصف المفتوحة.

تصلح البنية نفسها للأعداد الصحيحة والتواريخ والحالات المتتالية؛ وما يتغير هو شرط CASE فقط.

اختبار سريع

تأكدوا من استيعابكم لقاعدة تجميع جزر الحالة.

مراجعة: جزر الحالة والتاريخ

يمكنكم الآن حل أكثر أشكال مسائل الفجوات والجزر تقدمًا:

التجاور = عدم تغير الحالة عن الصف السابق؛ وتتغير العلامة باستخدام LAG.
احسبوا علامات التغيير تراكميًا لإنشاء مفتاح مجموعة لكل فترة.
ادمجوا الصفوف باستخدام GROUP BY user_id, status, key للحصول على نطاقات الفترات.
استخدموا LEAD لنهايات الفترات نصف المفتوحة، ووسّعوا العلامة لإنهاء الفترة عند وجود فجوات زمنية كبيرة.
تُدمج الصفوف المتطابقة المتتالية تلقائيًا، ويمكن استخراج عدد التبديلات من العلامات نفسها.
يغطي قالب واحد قابل لإعادة الاستخدام الأعداد الصحيحة والتواريخ والحالات، ولا يتغير سوى CASE.

وبذلك تكتمل دورة الفجوات والجزر، وهي علامة موثوقة على مستوى متقدم في مقابلات SQL.

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

هل درس «الجزر مع تغيرات التاريخ والحالة» مجاني؟

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

ماذا ستتعلم في «الجزر مع تغيرات التاريخ والحالة»؟

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

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

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

كم من الوقت يستغرق درس «الجزر مع تغيرات التاريخ والحالة»؟

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

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

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

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

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