多个记录中日期范围重叠的工作日总和

Question

多个记录中日期范围重叠的工作日总和

3

假设存在以下记录：

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

要求编写一个SQL查询语句，按employee_id分组汇总工作日，但排除重叠期间（即仅计算一次）。

所需输出如下：

Employee_id, worked_days

1, 13
2, 18

计算日期范围内的工作日是这样做的：如果开始工作日期是5，结束工作日期是9，则工作日为4（9-5）。

我可以编写一个PL / SQL函数来手动迭代记录并进行计算，但我相信可以使用SQL以获得更好的性能。

有人可以指点一下我吗？

谢谢！

- frenzy

1

员工1不应该是15号吗？1月1日至12日加上1月23日至25日是15天。 - Gordon Linoff

谢谢Gordon。这个可以有多种解释方式（包括/排除），所以我明确了计算的方法。 - frenzy

3个回答

1

这是一个棘手的问题。例如，您不能使用lag()，因为重叠期可能不是“上一个”期。或者，不同的期可以在同一天开始或结束。

解决方法是重构这些期。如何做到这一点？找到期开始的记录——也就是说，没有与任何其他记录重叠。然后将其用作标志，并累计计算此标志以计算重叠组。然后从那里进行工作日聚合：

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;

- Gordon Linoff

1

date_tbl类型

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

函数（以两个参数之间的日期为表格形式的结果）

create or replace function dates
(
    p_from date,
    p_to date
) return rg_type.date_tbl pipelined
is
  l_idx date:=p_from;
begin
  loop
    if l_idx>nvl(p_to,p_from) then
      exit;
    end if;
    pipe row(l_idx);
    l_idx:=l_idx+1;
  end loop;
  return;
end;

SQL：

select employee_id,sum(c)
from
  (select e.employee_id,d.column_value,count(distinct w.employee_id) as c
  from   (select distinct employee_id from works) e,
         table(dates((select min(work_start_date) as a from works),(select max(work_end_date) as b from works))) d,
         works w
  where e.employee_id=w.employee_id
        and d.column_value>=w.work_start_date
        and d.column_value<w.work_end_date
  group by e.employee_id,d.column_value) Sub
group by employee_id  
order by 1,2

- xnagyg

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- krokodilko · Accepted Answer

这是一个稍微修改过的问题，与类似问题有关：
计算与重叠日期范围相关联的值的总和

该问题涉及IT技术，需要计算一些日期范围内关联值的总和。以上链接提供了类似的解决方案。

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