需求如下:
表中有ID,start_date,end_date三列,要统计某个月(前台传入的参数)内同一个id出现了几次,换句好理解的话就是:ID=1在11月份有多少天。
他一开始的做法是想把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一下即可