SQL题目2：关于计日历表中工作天数的方法

SQL大数据开发 2021-04-19

351

今天在群里看到一位小伙伴提问关于写自定义函数的问题，当时是在语句块的if语句后面使用了exists ，导致编译不通过。后来询问之后是这样的一个需求，有一张日历表，表中存放着公司的休息日，大概就是这样:

大概表结构是这样，现在是让传入一个开始时间和结束时间，求这两个时间之内的工作天数：

比如传入开始时间为 2020-01-02 结束时间为 2020-01-28 则最终得到的时间为21天

开始他的想法是用循环，将 2020-01-02-2020-01-28 这27天逐一传入判断表中是否包含，代码如下，原理也很简单每次判断表中有没有此数据，有的话就是休息日，i不加1 ，没有的话就是休息日 i+1,这样循环27次后，就可以得到我们想要的数据了。

create or replace function gzrts(dt_begin in date, dt_end in date)
  return number as
  i number := 0;
  j number := 0;
  s date := dt_begin;
  a number;
begin
  while s <= dt_end   loop
    select count(1)
      into a
      from t1 m
     where m.begin_date <= s
       and m.end_date >= s;
    if a = 1 then
      j := j + 1;
    else
      i :=i + 1;
    end if;
    s := s + 1;
  end loop;
  return i;
end;

可以看到我们查询后的数据也是21

上面是第一种方案，然后我就有一种预感，这种逻辑应该是可以通过一条sql直接查询出来的，后来的方案是这样

select max(end_date) - min(begin_date) - sum(cnt) + 1 as cnt
-- cnt为黑色方框 也就是休息日的长度
  from (select begin_date, end_date, end_date - begin_date + 1 as cnt
          from t1
         where begin_date <= date '2020-01-28'
           and end_date >= date '2020-01-02'
        union all
        select date '2020-01-02' as begin_date,
               date '2020-01-28' as end_date,
               0    as cnt
          from dual)

大家猛的一看可能会有点猛，当时是想到了一种一维坐标，一个完整的日历就是一条线，然后再这条线上分布着许多休息日，如图所示，黑色的方框就是休息日，两条黄色的线就是我们的开始日期和截止日期，中间橙色的部分就是我们需要得工作日，目前我们已经知道的是上面的黑色和黄色线。要求出来下面的橙色部分，通过观察可以看出来，使用上面部分的最大值减去最小值，然后再减去黑色方框部分，就可以得到下面的橙色的长度。

但是有一点，就是开始的线和截止的线如果落在了黑色方框上，我们求的时候就需要单独将其标记，明显不符合我们的通用查询需求，所以在这个地方我们让开始线上的黑色部分的前半部分也显示出来，假如结束线也落在了黑色方框。则让结束线的后面部分也显示出来，这个就得到了我们需要的条件

传入的起始时间<end_date(表中的结束时间)
传入的结束时间>begin_date(表中的结束时间)
-- 这里需要大家思考一下，只有这样的条件才能让最大值和最小值固定

上述是第一步，第二步就是让开始时间早于黑色方框的部分也显示出来，这里是没有这一部分。也让结束时间晚于黑色方框的部分显示出来，在这里就是2020-01-25到2020-01-28这一段时间，所以我们将上面的查询结果并上我们输入的开始时间和结束时间

 union all
        select date '2020-01-02' as begin_date,
               date '2020-01-28' as end_date,
               0    as cnt
          from dual
--- 在这里，上面代码的cnt是黑色方框的长度，这一部分的cnt是为了防止代码报错

第三步就是将全部长度（最大值减去最小值）-休息日的长度（黑色方框长度之和）+1（临界值1），就得到了最终的代码。这一段代码可以直接用于查询，也可以将其封装在一个函数中用于调用，代码如下

create or replace function  gzrts1(dt_begin in date, dt_end in date)
  return number as
  i number := 0;
begin
  select max(end_date) - min(begin_date) - sum(cnt) + 1
    into i
    from (select begin_date, end_date, end_date - begin_date + 1 as cnt
            from t1
           where begin_date <= dt_end
             and end_date >= dt_begin
          union all
          select dt_begin as begin_date, dt_end as end_date, 0 as cnt
            from dual);
  return i;
end;