SQL Interview Prep · درس

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

استخدام حسابات التاريخ وأرقام الصفوف للعثور على سلاسل الأيام المتصلة

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

صيغة سؤال المقابلة

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

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

البيانات النموذجية

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

user_id — المستخدم الذي سجّل الدخول
login_date — قيمة من النوع DATE

بالنسبة إلى المستخدم 1، التواريخ هي 1 و2 و3 يناير، ثم توجد فجوة، ثم 6 و7 يناير. ونتوقع سلسلتين: سلسلة من 3 أيام وأخرى من يومين.

SELECT * FROM logins ORDER BY user_id, login_date;
-- user_id | login_date
--    1    | 2024-01-01
--    1    | 2024-01-02
--    1    | 2024-01-03
--    1    | 2024-01-06
--    1    | 2024-01-07

الفكرة الأساسية

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

والسبب هو أن التاريخ ورقم الصف يزدادان بمقدار 1 بالضبط في كل يوم متتالٍ، ولذلك لا يتغير الفرق بينهما. عند ظهور فجوة، يقفز التاريخ بينما لا يقفز رقم الصف، فينقطع الثبات وتبدأ مجموعة جديدة.

ملاحظة الفرق

لنتتبع ذلك يدويًا للمستخدم 1. يحسب ROW_NUMBER الأرقام 1 و2 و3 و4 و5. اطرحوا رقم الصف، بوحدة الأيام، من التاريخ ولاحظوا النتيجة.

Jan 1 − 1 = Dec 31
Jan 2 − 2 = Dec 31
Jan 3 − 3 = Dec 31
Jan 6 − 4 = Jan 2
Jan 7 − 5 = Jan 2

تشترك الأيام الثلاثة الأولى في Dec 31، بينما يشترك اليومان الأخيران في Jan 2. وتمثل قيمة الارتكاز المشتركة هذه مفتاح المجموعة لدينا.

إضافة ROW_NUMBER

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

يعيد PARTITION BY user_id تشغيل العداد لكل مستخدم، بينما يضمن ORDER BY login_date أن يتبع التسلسل ترتيب التقويم.

SELECT
  user_id,
  login_date,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS rn
FROM logins;

حساب مرساة المجموعة

اطرحوا الآن عدد أيام rn من login_date. في PostgreSQL يمكنكم طرح عدد صحيح من الأيام مباشرةً من التاريخ. وتكون النتيجة هي مرساة ثابتة تحدد كل جزيرة.

لاحظوا أنه لا يمكننا الإشارة إلى الاسم المستعار rn في جملة SELECT نفسها التي تعرّفه؛ لذلك نغلف الاستعلام السابق أولًا داخل CTE أو استعلام فرعي.

WITH numbered AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT
  user_id,
  login_date,
  login_date - rn AS grp
FROM numbered;

تجميع الجزر

بعد الحصول على المرساة، تشترك كل سلسلة متتالية في قيمة grp نفسها. جمّعوا حسب user_id وgrp، ثم استخدموا الدوال التجميعية للحصول على بداية كل سلسلة ونهايتها وطولها.

MIN(login_date) — أول يوم في السلسلة
MAX(login_date) — آخر يوم في السلسلة
COUNT(*) — عدد أيام السلسلة

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*)        AS streak_len
FROM numbered
GROUP BY user_id, login_date - rn
ORDER BY user_id, streak_start;

اختلافات اللهجات

تختلف صياغة العمليات الحسابية على التواريخ بين اللهجات. اذكروا ذلك في المقابلات لإظهار اتساع خبرتكم.

PostgreSQL: login_date - rn (طرح عدد صحيح من الأيام من التاريخ)
MySQL: DATE_SUB(login_date, INTERVAL rn DAY)
SQL Server: DATEADD(day, -rn, login_date)

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

-- SQL Server version of the anchor
DATEADD(day, -1 * rn, login_date) AS grp

لماذا لا نستخدم ربطًا ذاتيًا؟

قد يسألكم المحاوِر لماذا تجنبتم ربطًا ذاتيًا مثل l1.login_date = l2.login_date + 1. ومن الأسباب التي يمكن ذكرها:

يختبر الربط الذاتي التجاور فقط، لا السلسلة الكاملة؛ ولا يزال تجميع السلاسل المكتملة يحتاج إلى تجميع.
قد يؤدي إلى تفرع عدد الصفوف، ويصبح تعقيده O(n²) عند غياب الفهارس الجيدة.
يمر أسلوب أرقام الصفوف على البيانات المرتبة مرورًا واحدًا، وهو أكثر قابلية للتوسع بكثير.

دوال النوافذ هي الإجابة الحديثة والمتوقعة لهذه المسائل.

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

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

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

WITH days AS (
  SELECT DISTINCT user_id, login_ts::date AS login_date
  FROM raw_logins
)
SELECT * FROM days;

الحل الكامل

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

تحل هذه البنية نفسها، أي إزالة التكرارات ثم الترقيم ثم الطرح ثم التجميع، جميع مسائل «التتابع» تقريبًا التي قد تُطرح عليكم.

WITH days AS (
  SELECT DISTINCT user_id, login_ts::date AS login_date
  FROM raw_logins
),
numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM days
)
SELECT user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*)        AS streak_len
FROM numbered
GROUP BY user_id, login_date - rn
ORDER BY user_id, streak_start;

اختبار سريع

اختبروا مدى استيعابكم للحيلة الأساسية.

مراجعة

لقد تعلمتم النمط الأساسي للأيام المتتالية:

إزالة التكرارات للوصول إلى صف واحد لكل مستخدم ولكل يوم.
ROW_NUMBER مرتبًا حسب التاريخ ومقسمًا حسب المستخدم.
طرح رقم الصف من التاريخ للحصول على مرساة ثابتة لكل سلسلة.
GROUP 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 يتضمن محرر أكواد مدمج، لذا تكتب وتشغل أكواداً حقيقية مباشرة في متصفحك وتحصل على تعليقات فورية من الذكاء الاصطناعي — بدون إعداد محلي.

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

اكتشاف الأيام التقويمية المتتالية
أطول سلسلة متتالية لكل مستخدم
عدد N من الصفوف المتتالية التي تستوفي شرطًا
السلسلة النشطة الحالية حتى اليوم

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