تحليل غياب الموظفين: تعقّب الغياب المتتالي

لحل هذه المشكلة، يمكنك استخدام نافذة الدالة LAG() للتحقق من حالة الحضور/الغياب للموظفين عبر الصفوف المتتالية، ومن ثم استخدام تحويل البيانات والعد الزمني لتحديد فترات الغياب المتتالية لمدة تزيد عن 3 أيام. فيما يلي استعلام SQL يوضح كيفية القيام بذلك:

sqlCopy code
WITH consecutive_absences AS (
  SELECT
    id,
    name,
    statuse,
    date_presnet,
    LAG(date_presnet, 1) OVER (PARTITION BY id ORDER BY date_presnet) AS prev_date,
    LAG(statuse, 1) OVER (PARTITION BY id ORDER BY date_presnet) AS prev_status
  FROM
    employee
), marked_absences AS (
  SELECT
    id,
    name,
    statuse,
    date_presnet,
    CASE
      WHEN statuse = 'absent' AND prev_status = 'absent' THEN 1
      ELSE 0
    END AS consecutive_absence
  FROM
    consecutive_absences
)
SELECT
  id,
  name,
  MIN(date_presnet) AS start_date,
  MAX(date_presnet) AS end_date,
  COUNT(*) AS consecutive_days
FROM
  marked_absences
WHERE
  consecutive_absence = 1
GROUP BY
  id, name, DATE_DIFF(date_presnet, prev_date, DAY)
HAVING
  consecutive_days >= 3;

هذا الاستعلام يقوم بتحديد الفترات المتتالية للغياب التي تزيد عن 3 أيام، ويعرض معلومات حول موظفين معينين وفترات غيابهم، بدءًا من تاريخ البداية وحتى تاريخ الانتهاء، مع عدد الأيام المتتالية التي كانوا غائبين خلالها.

يرجى ملاحظة أنه يجب تعديل اسم الجدول وأسماء الأعمدة وفقًا لهيكل البيانات الخاص بك.

بالطبع، سأوفّر المزيد من المعلومات لتفسير الاستعلام وكيفية عمله بالتفصيل.

الاستعلام المذكور يستخدم مفهوم نوافذ الدوال في SQL، وهي آلية تسمح لك بتطبيق الدوال التحليلية (مثل LAG و ROW_NUMBER) على مجموعة محددة من الصفوف في النتيجة. تساعد هذه الدوال في إنشاء مجموعات معينة من البيانات يمكن التعامل معها بشكل منفصل، وتسهل تحليل البيانات بطريقة تسلسلية أو تقسيمها بناءً على معايير معينة.

في الاستعلام السابق، يتم استخدام نافذة الدالة LAG() للحصول على قيمة الصف السابق في النتيجة، حيث يتم ترتيب الصفوف بواسطة تاريخ الحضور/الغياب. وباستخدام LAG()، يتم إنشاء عمودين إضافيين: واحد لتاريخ الصف السابق والآخر لحالة الحضور/الغياب في الصف السابق.

ثم، يتم تحويل البيانات في جدول النتائج باستخدام تعبير CASE في جزء marked_absences. هذا التعبير يقوم بوضع علامة على الصفوف التي تمثل فترات غياب متتالية لنفس الموظف. إذا كان الموظف غائبًا في الصف الحالي وكان غائبًا في الصف السابق، فإن العمود consecutive_absence سيكون 1، وإلا فإنه سيكون 0.

أخيرًا، يتم تجميع البيانات في جدول marked_absences مع استخدام GROUP BY، حيث يتم فصل البيانات بناءً على معرف الموظف وعدد الأيام المتتالية التي كان فيها الغياب، ويتم التحقق في النهاية باستخدام HAVING من وجود فترات غياب تزيد عن 3 أيام.

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