SQL Interview Prep · درس

التعرف على مسألة الفجوات والجزر

تحديد النمط في المسألة الوصفية والفكرة الأساسية للتجميع

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

النمط الذي يختبره المحاورون

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

الجزيرة هي سلسلة قصوى من الصفوف المتجاورة وفق قاعدة معينة (أعداد صحيحة متتالية، أو تواريخ متتالية، أو الحالة نفسها مكررة).
الفجوة هي المساحة المفقودة بين جزيرتين.

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

مسائل لفظية تخفي جزيرة

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

«اعثر على كل فترة كان فيها المستخدم مشتركًا باستمرار.»
«كم عدد الأيام المتتالية التي ظل فيها الخادم يعمل؟»
«ما نطاقات المعرّفات المفقودة من هذا الجدول؟»
«ادمج الصفوف المتجاورة ذات الحالة نفسها في صف واحد.»

كل واحدة من هذه المسائل لها البنية نفسها: جمّع الصفوف المتجاورة، ثم أبلغ عن بداية هذه المجموعات أو نهايتها أو غيابها. وبمجرد أن تربط الكلمات بالجزر، تصبح كتابة SQL مباشرة.

الفكرة الأساسية: إنشاء مفتاح مجموعة

إليك الحيلة كاملة في جملة واحدة: إذا تمكنت من إسناد مفتاح مجموعة متطابق إلى كل صف في الجزيرة نفسها، فسيؤدي GROUP BY البسيط إلى اختزال كل جزيرة في صف ملخص واحد.

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

SELECT
  grp,
  MIN(value) AS island_start,
  MAX(value) AS island_end,
  COUNT(*)   AS island_length
FROM rows_with_group_key
GROUP BY grp
ORDER BY island_start;

مجموعة بيانات ملموسة

لنثبت الفكرة بالبيانات. تخيل جدول logins يتتبع أرقام الأيام التي سجّل فيها المستخدم الدخول:

الأيام الموجودة: 1، 2، 3، 7، 8، 10

بالعين المجردة، الجزر هي {1,2,3} و{7,8} و{10}. والفجوات هي الأيام من 4 إلى 6 واليوم 9. مهمتك في المقابلة هي أن تجعل قاعدة البيانات ترى هذه الجزر الثلاث من دون أن تشير إليها يدويًا. احتفظ بمجموعة البيانات الصغيرة هذه في ذهنك أثناء استكشاف كل تقنية.

CREATE TABLE logins (day_no INT);
INSERT INTO logins VALUES (1),(2),(3),(7),(8),(10);

لماذا تفشل الأساليب الساذجة

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

تحتاج إلى اكتشاف بداية كل جزيرة ونهايتها، ما يعني تنفيذ مرورين أو عمليتي ربط.
تحتاج الصفوف الطرفية (الأول والأخير تمامًا) إلى معالجة خاصة.
لا يمكن تعميمه على سؤال «أعطني طول كل سلسلة» من دون إضافة آليات أخرى.

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

النموذج الذهني لاكتشاف الفجوات

إحدى الصياغات المتينة هي: تبدأ جزيرة جديدة كلما لم يكن الصف الحالي متجاورًا مع الصف السابق. استخدم LAG للرجوع صفًا واحدًا ومقارنته.

إذا كانت قيمة day_no - LAG(day_no) أكبر من 1 (أو NULL للصف الأول)، فهذا الصف يبدأ جزيرة جديدة. نضع علامة بقيمة 1 في هذه الحالة، وإلا فالقيمة 0. لنرَ شكل هذه العلامات لبياناتنا.

SELECT
  day_no,
  CASE
    WHEN day_no - LAG(day_no) OVER (ORDER BY day_no) = 1 THEN 0
    ELSE 1
  END AS is_new_island
FROM logins
ORDER BY day_no;

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

العلامات الناتجة من الخطوة السابقة هي 1، 0، 0، 1، 0، 1 للأيام 1،2،3،7،8،10. لاحظ أن المجموع التراكمي لهذه العلامات ينتج رقمًا يبقى ثابتًا داخل الجزيرة ويزداد عند كل جزيرة جديدة: 1،1،1،2،2،3.

هذا المجموع التراكمي هو مفتاح المجموعة الذي أنشأناه. نضع استعلام العلامات داخل CTE، ثم نحسب مجموعها باستخدام دالة نافذة أخرى:

WITH flagged AS (
  SELECT
    day_no,
    CASE WHEN day_no - LAG(day_no) OVER (ORDER BY day_no) = 1
         THEN 0 ELSE 1 END AS is_new_island
  FROM logins
)
SELECT
  day_no,
  SUM(is_new_island) OVER (ORDER BY day_no) AS grp
FROM flagged;

إكمال المثال التطبيقي

أضف الآن GROUP BY النهائي فوق مفتاح المجموعة. كل قيمة مختلفة من grp تمثل جزيرة واحدة، ونُبلغ عن حدودها وحجمها:

والنتيجة هي الجزر الثلاث نفسها التي حددناها بالعين المجردة: 1-3 (بطول 3)، و7-8 (بطول 2)، و10-10 (بطول 1). هذه الوصفة المؤلفة من ثلاث طبقات (العلامة، والمجموع التراكمي، والتجميع) هي أساس معظم إجابات الفجوات والجزر التي ستكتبها.

WITH flagged AS (
  SELECT day_no,
    CASE WHEN day_no - LAG(day_no) OVER (ORDER BY day_no) = 1
         THEN 0 ELSE 1 END AS is_new
  FROM logins
),
keyed AS (
  SELECT day_no,
    SUM(is_new) OVER (ORDER BY day_no) AS grp
  FROM flagged
)
SELECT grp, MIN(day_no) AS start_day,
       MAX(day_no) AS end_day, COUNT(*) AS len
FROM keyed GROUP BY grp ORDER BY start_day;

التجاور خاص بالمجال

الجزء الوحيد الذي يتغير بين المسائل هو تعريف التجاور. ويُعد تحديد قاعدة التجاور الصحيحة نصف عملية التعرف على المسألة:

الأعداد الصحيحة: تكون متجاورة عندما يكون الفرق مساويًا تمامًا لـ 1.
أيام التقويم: تكون متجاورة عندما يكون أحد التاريخين هو اليوم التالي للآخر (date = prev + INTERVAL '1 day').
فترات الحالة: تكون متجاورة عندما لا تتغير قيمة الحالة عن الصف السابق.

البنية نفسها، لكن المقارنة داخل CASE مختلفة. وتحديد قاعدة التجاور المنطبقة هو السؤال التوضيحي الذي ينبغي أن تطرحه بصوت مسموع في المقابلة.

أسئلة توضيحية ينبغي طرحها

قبل كتابة أي سطر من SQL، اكسب نقاطًا بتوضيح النطاق. ومن الأسئلة التوضيحية الجيدة في مسائل الفجوات والجزر:

«هل أتعامل مع البيانات لكل مستخدم أم على مستوى شامل؟» (يحدد ذلك ما إذا كنت ستضيف PARTITION BY user_id.)
«هل يمكن أن توجد قيم مكررة في اليوم نفسه، وهل تؤدي إلى قطع السلسلة أم تمديدها؟»
«هل تريد الجزر أم الفجوات أم كليهما؟»
«هل يُضمن أن التسلسل مرتب، أم ينبغي أن أرتبه بنفسي؟»

إن طرح هذه الأسئلة يوضح أنك تعاملت مع هذا النوع من المسائل من قبل وتفهم حالاته الحدية.

جزر لكل مجموعة باستخدام PARTITION BY

تكون بيانات المقابلات الواقعية مجمعة في الغالب، مثل بيانات تسجيل الدخول لكل مستخدم. والحل آلي: أضف PARTITION BY user_id إلى كل دالة نافذة حتى لا تمتد الجزر عبر المستخدمين.

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

SELECT
  user_id, day_no,
  CASE WHEN day_no - LAG(day_no)
         OVER (PARTITION BY user_id ORDER BY day_no) = 1
       THEN 0 ELSE 1 END AS is_new
FROM logins;

تحقق سريع

اختبر حدسك في التعرف على النمط.

مراجعة: التعرّف على البنية

يمكنك الآن تحديد مسألة الفجوات والجزر من صياغتها المخفية وتسميـة الاستراتيجية المناسبة:

الكلمات المحفزة: متتالٍ، مستمر، غير منقطع، سلسلة، نطاقات مفقودة، دمج المتجاور.
الفكرة الأساسية: إسناد مفتاح مجموعة متطابق إلى كل صف في السلسلة نفسها، ثم تطبيق GROUP BY عليه.
الوصفة: ضع علامة على الجزر الجديدة باستخدام LAG، ثم احسب المجموع التراكمي للعلامات لإنشاء مفتاح، وبعد ذلك نفّذ التجميع.
التجاور خاص بالمجال (أعداد صحيحة أو تواريخ أو حالة لم تتغير).
أضف PARTITION BY للتحليل لكل مجموعة، ووضّح النطاق قبل كتابة الشيفرة.

سننتقل بعد ذلك إلى تحسين أكثر أساليب إنشاء المفتاح أناقة: حيلة الفرق بين رقم الصف والقيمة.

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

هل درس «التعرف على مسألة الفجوات والجزر» مجاني؟

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

ماذا ستتعلم في «التعرف على مسألة الفجوات والجزر»؟

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

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

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

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

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

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

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

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

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