输入表:描述了每个id存在的时段
id | startdate | enddate |
4172752725 | 2017-07-31 | 2017-08-02 |
4172778959 | 2017-08-01 | 2017-08-02 |
4172779968 | 2017-07-31 | 2017-08-01 |
4172781986 | 2017-08-09 | 2017-08-10 |
4172945444 | 2017-08-03 | 2017-08-04 |
4173014056 | 2017-08-04 | 2017-08-06 |
需求描述:要id和date一一对应的表,这样可以方便的求出每个日期上有哪些id存在。注意,对于每个id,enddate上不算这个id存在。
with testdata as (
select 4172752725 as id, '2017-07-31' as startdate, '2017-08-02' as enddate
union all
select 4172778959 as id, '2017-08-01' as startdate, '2017-08-02' as enddate
union all
select 4172779968 as id, '2017-07-31' as startdate, '2017-08-01' as enddate
union all
select 4172781986 as id, '2017-08-09' as startdate, '2017-08-10' as enddate
union all
select 4172945444 as id, '2017-08-03' as startdate, '2017-08-04' as enddate
union all
select 4173014056 as id, '2017-08-04' as startdate, '2017-08-06' as enddate
)
select z.id
,date_add(z.startdate,z.pos) as checkindate
from (
select id
,startdate
,posexplode(split(repeat('d',datediff(enddate,startdate) - 1),'d'))
from testdata
) z
order by z.id asc
;
输出表:
id | checkindate |
4172752725 | 2017-07-31 |
4172752725 | 2017-08-01 |
4172778959 | 2017-08-01 |
4172779968 | 2017-07-31 |
4172781986 | 2017-08-09 |
4172945444 | 2017-08-03 |
4173014056 | 2017-08-04 |
4173014056 | 2017-08-05 |
输出一些中间结果以帮助理解:
select id
,startdate
,enddate
,datediff(enddate,startdate)
,datediff(enddate,startdate)-1
,repeat('d',datediff(enddate,startdate) - 1)
,split(repeat('d',datediff(enddate,startdate) - 1),'d')
,posexplode(split(repeat('d',datediff(enddate,startdate) - 1),'d'))
from testdata
order by id asc
id | startdate | enddate | datediff | datediff-1 | repeat | split | pos | col |
4172752725 | 2017-07-31 | 2017-08-02 | 2 | 1 | d | ["",""] | 0 | |
4172752725 | 2017-07-31 | 2017-08-02 | 2 | 1 | d | ["",""] | 1 | |
4172778959 | 2017-08-01 | 2017-08-02 | 1 | 0 | [""] | 0 | ||
4172779968 | 2017-07-31 | 2017-08-01 | 1 | 0 | [""] | 0 | ||
4172781986 | 2017-08-09 | 2017-08-10 | 1 | 0 | [""] | 0 | ||
4172945444 | 2017-08-03 | 2017-08-04 | 1 | 0 | [""] | 0 | ||
4173014056 | 2017-08-04 | 2017-08-06 | 2 | 1 | d | ["",""] | 0 | |
4173014056 | 2017-08-04 | 2017-08-06 | 2 | 1 | d | ["",""] | 1 |