Summe der Arbeitstage mit Datumsbereichen aus mehreren Datensätzen (überlappend)

Question

Angenommen, es gibt Datensätze wie folgt:

Employee_id, work_start_date, work_end_date

1, 01-jan-2014, 07-jan-2014
1, 03-jan-2014, 12-jan-2014
1, 23-jan-2014, 25-jan-2014
2, 15-jan-2014, 25-jan-2014
2, 07-jan-2014, 15-jan-2014
2, 09-jan-2014, 12-jan-2014

Die Anforderung besteht darin, eine SQL-SELECT-Anweisung zu schreiben, die die Arbeitstage nach employee_id gruppiert zusammenfasst, aber die überlappenden Zeiträume ausschließt (das bedeutet - sie nur einmal in die Berechnung einbezieht).

Die gewünschte Ausgabe wäre:

Employee_id, worked_days

1, 13
2, 18

Die Berechnungen für Arbeitstage im Datumsumfang erfolgen wie folgt: Wenn work_start_date = 5 und work_end_date = 9 ist, dann worked_days = 4 (9 - 5).

Ich könnte eine PL/SQL-Funktion schreiben, die dies löst (manuelles Durchlaufen der Datensätze und Durchführen der Berechnung), aber ich bin sicher, dass es mit SQL für bessere Leistung erledigt werden kann.

Kann mir jemand bitte in die richtige Richtung weisen?

Danke!

Answer 1

Dies ist eine leicht modifizierte Abfrage von einer ähnlichen Frage:
Summe der Werte berechnen, die mit überlappenden Datumsbereichen verbunden sind

SELECT "Employee_id",
       SUM( "work_end_date" - "work_start_date" )
FROM(
  SELECT "Employee_id",
         "work_start_date" ,
         lead( "work_start_date" ) 
             over (Partition by "Employee_id"
                  Order by "Employee_id", "work_start_date" ) 
         As "work_end_date"
  FROM (
     SELECT "Employee_id", "work_start_date"
     FROM Table1
     UNION
     SELECT "Employee_id","work_end_date"
     FROM Table1
  ) x
) x
WHERE EXISTS (
   SELECT 1 FROM Table1 t
   WHERE t."work_start_date" > x."work_end_date"
     AND t."work_end_date" > x."work_start_date"
      OR t."work_start_date" = x."work_start_date"
     AND t."work_end_date" =  x."work_end_date"
)
GROUP BY "Employee_id"
;

Demo: http://sqlfiddle.com/#!4/4fcce/2

Answer 2

Dies ist ein schwieriges Problem. Zum Beispiel können Sie lag() nicht verwenden, weil der überlappende Zeitraum nicht der "vorherige" sein kann. Oder verschiedene Zeiträume können am selben Tag beginnen oder enden.

Die Idee ist, die Zeiträume neu aufzubauen. Wie macht man das? Finden Sie die Datensätze, in denen die Zeiträume beginnen - das heißt, es gibt keine Überlappung mit einem anderen. Verwenden Sie dies dann als Flagge und zählen Sie diese Flagge kumulativ, um überlappende Gruppen zu zählen. Danach ist es einfach, die Arbeitstage zu aggregieren:

with ps as (
      select e.*,
             (case when exists (select 1
                                from emps e2
                                where e2.employee_id = e.employee_id and
                                      e2.work_start_date <= e.work_start_date and
                                      e2.work_end_date >= e.work_end_date
                         )
                   then 0 else 1
            ) as IsPeriodStart
      from emps e
     )
select employee_id, sum(work_end_date - work_start_date) as Days_Worked
from (select employee_id, min(work_start_date) as work_start_date,
             max(work_end_date) as work_end_date
      from (select ps.*,
                   sum(IsPeriod_Start) over (partition by employee_id
                                             order by work_start_date
                                            ) as grp
            from ps 
           ) ps
      group by employee_id, grp
     ) ps
group by employee_id;

Answer 3

Datums_tbl-Typ

create or replace package RG_TYPE is
  type datums_tbl is table of date;
end;

Funktion (Ergebnis als Tabelle mit den Daten zwischen 2 Parametern)

create or replace function daten
(
    p_von datum,
    p_bis datum
) return rg_type.datums_tbl pipelined
is
  l_idx datum:=p_von;
begin
  loop
    if l_idx>nvl(p_bis,p_von) then
      exit;
    end if;
    pipe row(l_idx);
    l_idx:=l_idx+1;
  end loop;
  return;
end;

SQL:

select mitarbeiter_id,sum(c)
from
  (select e.mitarbeiter_id,d.column_value,count(distinct w.mitarbeiter_id) as c
  from   (select distinct mitarbeiter_id from arbeitet) e,
         table(daten((select min(arbeits_start_datum) as a from arbeitet),(select max(arbeits_end_datum) as b from arbeitet))) d,
         arbeitet w
  where e.mitarbeiter_id=w.mitarbeiter_id
        and d.column_value>=w.arbeits_start_datum
        and d.column_value