解决网友业务需求案例--使用greatest/least计算满足业务规则的日期范围天数

浏览: 1053

需求如下:

表中有ID,start_date,end_date三列,要统计某个月(前台传入的参数)内同一个id出现了几次,换句好理解的话就是:ID=1在11月份有多少天。

Clipboard Image.png

他一开始的做法是想把start 和 end都展开,如:

ID START_DATE END_DATE

1    2016/11/1  2016/11/4

转成:

ID      DATE

1    2016/11/1  

1    2016/11/2  

1    2016/11/3  

1    2016/11/4  

转换完成进行业务逻辑的过滤,再count(DATE) group by ID

业务逻辑是:

START_DATE和END_DATE一定要包含这个传入的参数,比如传入的是2016-11

那么下面这些数据就是不要的:

ID START_DATE   END_DATE

1    2016/10/1   2016/10/4

2    2016/12/1   2016/12/4

而下面这些是需要的:

ID START_DATE   END_DATE

1    2016/10/1   2016/11/4

2    2016/10/1   2016/12/4

3    2016/11/1   2016/12/4


由于需要将日期范围打成列表,咨询我怎么做,我一看这不行啊~ 

万一数据量很大,范围又不小,那这个转换后的数据量将会巨大无比。效率先不说,这么大的数据量,内存能否放得下临时转换的数据也是个问题。

考虑了下,其实他要的就是这个范围的天数而已,根本没必要转成列表再count


最终发给他的SQL如下(with是我造的数据,'2016-11'为传入参数):

with ltz as
 (select 1 as id,
         to_date('2016-11-11', 'yyyy-mm-dd') as start_date,
         to_date('2016-11-21', 'yyyy-mm-dd') as end_date
    from dual
  union all
  select 2,
         to_date('2016-10-11', 'yyyy-mm-dd') as start_date,
         to_date('2016-11-2', 'yyyy-mm-dd') as end_date
    from dual
  union all
  select 3,
         to_date('2016-11-1', 'yyyy-mm-dd') as start_date,
         to_date('2016-11-30', 'yyyy-mm-dd') as end_date
    from dual
  union all
  select 4,
         to_date('2016-10-22', 'yyyy-mm-dd') as start_date,
         to_date('2016-12-21', 'yyyy-mm-dd') as end_date
    from dual
  union all
  select 5,
         to_date('2016-09-22', 'yyyy-mm-dd') as start_date,
         to_date('2016-10-21', 'yyyy-mm-dd') as end_date
    from dual
  union all
  select 6,
         to_date('2016-09-11', 'yyyy-mm-dd') as start_date,
         to_date('2016-11-21', 'yyyy-mm-dd') as end_date
    from dual)
select t.*,
       least(end_date, last_day(to_date('2016-11', 'YYYY-MM'))) -
       greatest(start_date, trunc(to_date('2016-11', 'YYYY-MM'), 'MM')) + 1 as result
  from ltz t
 where to_date('2016-11', 'YYYY-MM') between
       to_date(to_char(start_date, 'YYYY-MM'), 'YYYY-MM') and
       to_date(to_char(end_date, 'YYYY-MM'), 'YYYY-MM');

where部分限制了业务取数规则

least函数:取最小

greatest函数:取最大

如果表里的ID不是唯一的,则最后再sum group by id一下即可

推荐 5
本文由 老头子 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

1 个评论

赞!!!!

要回复文章请先登录注册