oracle 合并日期区间，取天数

最新推荐文章于 2022-05-30 15:24:37 发布

weixin_34163553

最新推荐文章于 2022-05-30 15:24:37 发布

阅读量186

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/179750/blog/810461

版权

2019独角兽企业重金招聘Python工程师标准>>>

在群里有人提了一个问题，不同日期区间包含的合并，取天数，如: 2015-01-01 2015-04-01 2015-03-01 2015-08-01

这里取的天数是2015-08-01-2015-01-01 下面是几个实现算法
###　1

with t as (

  select 'aa' as user_id, 1 as orderid,to_date('2012-01-01','yyyy-mm-dd') as begin_date,to_date('2012-06-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 2 as orderid,to_date('2012-03-01','yyyy-mm-dd') as begin_date,to_date('2012-09-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 3 as orderid,to_date('2012-03-01','yyyy-mm-dd') as begin_date,to_date('2013-12-25','yyyy-mm-dd') as end_date from dual
  union all  
  select 'aa' as user_id, 4 as orderid,to_date('2012-12-01','yyyy-mm-dd') as begin_date,to_date('2013-12-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 5 as orderid,to_date('2014-12-01','yyyy-mm-dd') as begin_date,to_date('2014-12-05','yyyy-mm-dd') as end_date from dual
  union all  
  select 'bb' as user_id, 1 as orderid,to_date('2012-06-01','yyyy-mm-dd') as begin_date,to_date('2012-08-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 2 as orderid,to_date('2012-09-01','yyyy-mm-dd') as begin_date,to_date('2013-06-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 3 as orderid,to_date('2013-03-01','yyyy-mm-dd') as begin_date,to_date('2013-12-01','yyyy-mm-dd') as end_date from dual


  /*select 'aa' as user_id, 1 as orderid,to_date('2012-01-01','yyyy-mm-dd') as begin_date,to_date('2012-06-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 2 as orderid,to_date('2012-03-01','yyyy-mm-dd') as begin_date,to_date('2012-09-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 3 as orderid,to_date('2012-03-01','yyyy-mm-dd') as begin_date,to_date('2013-12-25','yyyy-mm-dd') as end_date from dual
  union all  
  select 'aa' as user_id, 4 as orderid,to_date('2012-12-01','yyyy-mm-dd') as begin_date,to_date('2013-12-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 5 as orderid,to_date('2012-12-01','yyyy-mm-dd') as begin_date,to_date('2013-12-24','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 6 as orderid,to_date('2013-12-01','yyyy-mm-dd') as begin_date,to_date('2013-12-30','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 1 as orderid,to_date('2012-06-01','yyyy-mm-dd') as begin_date,to_date('2012-08-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 2 as orderid,to_date('2012-09-01','yyyy-mm-dd') as begin_date,to_date('2013-06-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 3 as orderid,to_date('2013-03-01','yyyy-mm-dd') as begin_date,to_date('2013-12-01','yyyy-mm-dd') as end_date from dual
*/),
d as (
SELECT min_date + level -1 as is_date
FROM (select min(begin_date) min_date,max(end_date) max_date from t ) 
CONNECT BY level < max_date - min_date+1
)
select user_id,count(distinct is_date) from (
select * from t left join d on (is_date between begin_date and end_date)
) group by user_id

2

with ta as (
 select 'aa' as u_id, 1 as oid,to_date('2015-01-01','yyyy-mm-dd') as bd,to_date('2015-06-01','yyyy-mm-dd') as ed from dual
  union all
  select 'aa' as user_id, 2 as orderid,to_date('2015-03-01','yyyy-mm-dd') as begin_date,to_date('2015-09-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 3 as orderid,to_date('2015-12-01','yyyy-mm-dd') as begin_date,to_date('2016-12-25','yyyy-mm-dd') as end_date from dual
  union all  
  select 'aa' as user_id, 4 as orderid,to_date('2015-12-01','yyyy-mm-dd') as begin_date,to_date('2016-12-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 5 as orderid,to_date('2015-12-01','yyyy-mm-dd') as begin_date,to_date('2016-12-24','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 6 as orderid,to_date('2016-12-01','yyyy-mm-dd') as begin_date,to_date('2016-12-30','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 1 as orderid,to_date('2015-06-01','yyyy-mm-dd') as begin_date,to_date('2015-08-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 2 as orderid,to_date('2015-09-01','yyyy-mm-dd') as begin_date,to_date('2016-06-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 3 as orderid,to_date('2016-03-01','yyyy-mm-dd') as begin_date,to_date('2016-12-01','yyyy-mm-dd') as end_date from dual
)
,t as (
select u_id,bd,max(oid) oid,max(ed) ed from ta group by u_id,bd
)
select uid2,sum(mbe - mbd + 1) n from (
select nnn,uid2,min(bd2) mbd,max(ed2) mbe from (
select connect_by_root (uid2 || '-' || oid2) nnn,t.* from (
select * from (
select row_number() over(partition by uid2,oid2,bd2,ed2 order by oid2,p  NULLS LAST ) rn,t.uid2,t.oid2,t.bd2,t.ed2,t.p,t.n from (
select case when t2.bd between t1.bd + 1 and t1.ed then t1.bd end p, 
case when t2.bd between t1.bd + 1 and t1.ed then t1.oid   end n, 
 t1.u_id uid1,t1.oid oid1,t1.bd bd1,t1.ed ed1,t2.u_id uid2,t2.oid oid2,t2.bd bd2,t2.ed ed2 from t t1,t t2
where t1.u_id = t2.u_id and t1.oid <> t2.oid 
) t 
) where rn = 1
) t connect by  prior (uid2 || '-' || oid2) =  (uid2 || '-' || n) start with n is null 
) group by nnn,uid2
)group by uid2

3



with t as (
  select 'aa' as user_id, 1 as orderid,to_date('2015-01-01','yyyy-mm-dd') as begin_date,to_date('2015-06-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 2 as orderid,to_date('2015-03-01','yyyy-mm-dd') as begin_date,to_date('2015-09-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 3 as orderid,to_date('2015-12-01','yyyy-mm-dd') as begin_date,to_date('2016-12-25','yyyy-mm-dd') as end_date from dual
  union all  
  select 'aa' as user_id, 4 as orderid,to_date('2015-12-01','yyyy-mm-dd') as begin_date,to_date('2016-12-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 5 as orderid,to_date('2015-12-01','yyyy-mm-dd') as begin_date,to_date('2016-12-24','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 6 as orderid,to_date('2016-12-01','yyyy-mm-dd') as begin_date,to_date('2016-12-30','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 1 as orderid,to_date('2015-06-01','yyyy-mm-dd') as begin_date,to_date('2015-08-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 2 as orderid,to_date('2015-09-01','yyyy-mm-dd') as begin_date,to_date('2016-06-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 3 as orderid,to_date('2016-03-01','yyyy-mm-dd') as begin_date,to_date('2016-12-01','yyyy-mm-dd') as end_date from dual
)
,DIM_DATE as (
SELECT min_date + level -1 as DAY_DT
FROM (select min(begin_date) min_date,max(end_date) max_date from t ) 
CONNECT BY level <= max_date - min_date+1
)
--SELECT A.DAY_DT,T.USER_ID FROM  DIM_DATE A INNER  JOIN  T ON A.DAY_DT>=T.BEGIN_DATE AND A.DAY_DT<= T.END_DATE WHERE T.USER_ID = 'aa'
SELECT T.USER_ID,COUNT(distinct A.DAY_DT) FROM DIM_DATE A inner  
JOIN  T ON A.DAY_DT>=T.BEGIN_DATE AND A.DAY_DT<= T.END_DATE
GROUP BY T.USER_ID
;

4

with t as (
  select 'aa' as user_id, 1 as orderid,to_date('2012-01-01','yyyy-mm-dd') as begin_date,to_date('2012-06-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 2 as orderid,to_date('2012-03-01','yyyy-mm-dd') as begin_date,to_date('2012-09-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 3 as orderid,to_date('2012-12-01','yyyy-mm-dd') as begin_date,to_date('2013-12-25','yyyy-mm-dd') as end_date from dual
  union all  
  select 'aa' as user_id, 4 as orderid,to_date('2012-12-01','yyyy-mm-dd') as begin_date,to_date('2013-12-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 5 as orderid,to_date('2012-12-01','yyyy-mm-dd') as begin_date,to_date('2013-12-24','yyyy-mm-dd') as end_date from dual
  union all
  select 'aa' as user_id, 6 as orderid,to_date('2013-12-01','yyyy-mm-dd') as begin_date,to_date('2013-12-30','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 1 as orderid,to_date('2012-06-01','yyyy-mm-dd') as begin_date,to_date('2012-08-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 2 as orderid,to_date('2012-09-01','yyyy-mm-dd') as begin_date,to_date('2013-06-01','yyyy-mm-dd') as end_date from dual
  union all
  select 'bb' as user_id, 3 as orderid,to_date('2013-03-01','yyyy-mm-dd') as begin_date,to_date('2013-12-01','yyyy-mm-dd') as end_date from dual
),
t2 as(select t.*,  max(end_date)over(partition by user_id order by orderid) as t_max_date
 from t ),
t3 as (select * from t2
minus 
select * from t2 where end_date<t_max_date
)

select user_id,sum(true_end_date-begin_date) from (
select t4.*,case when nvl(lead_begin_date,end_date)> end_date then end_date else nvl(lead_begin_date,end_date) end as true_end_date from (
select t3.*,lead(begin_date)over(partition by user_id order by orderid) as lead_begin_date from t3
) t4
) group by user_id

转载于:https://my.oschina.net/u/179750/blog/810461