数据:
A 2020-01-01
A 2020-01-02
A 2020-01-04
A 2020-01-05
A 2020-01-06
A 2020-01-07
A 2020-01-09
A 2020-01-10
B 2020-01-01
B 2020-01-02
B 2020-01-04
B 2020-01-05
B 2020-01-06
B 2020-01-07
B 2020-01-09
B 2020-01-10
建表:
create table time(
`id` string,
`data` string
)
row format delimited fields terminated by '\t'
第一步 : 排序 ,按时间进行排序:
select id,data,row_number()
over(partition by id order by data) rank
from time
A 2020-01-01 1
A 2020-01-02 2
A 2020-01-04 3
A 2020-01-05 4
A 2020-01-06 5
A 2020-01-07 6
A 2020-01-09 7
A 2020-01-10 8
B 2020-01-01 1
B 2020-01-02 2
B 2020-01-04 3
B 2020-01-05 4
B 2020-01-06 5
B 2020-01-07 6
B 2020-01-09 7
B 2020-01-10 8
第二步: 时间减去排序的名次,用date_sub()函数
得到:
A 2020-01-01 2019-12-31
A 2020-01-02 2019-12-31
A 2020-01-04 2020-01-01
A 2020-01-05 2020-01-01
A 2020-01-06 2020-01-01
A 2020-01-07 2020-01-01
A 2020-01-09 2020-01-02
A 2020-01-10 2020-01-02
B 2020-01-01 2019-12-31
B 2020-01-02 2019-12-31
B 2020-01-04 2020-01-01
B 2020-01-05 2020-01-01
B 2020-01-06 2020-01-01
B 2020-01-07 2020-01-01
B 2020-01-09 2020-01-02
B 2020-01-10 2020-01-02
这时候对第三列进行group by count 操作 得到连续在线天数,同时用Max(data) min(data) 就得到从那天到哪天的连续在线天数了
select id ,date_sub(data,rank) s ,min(data) min,max(data) max, count(*) from(
select id,data,row_number() over(partition by id order by data) rank from time
) t1 group by id,date_sub(data,rank);
结果:
id s min max _c4
A 2019-12-31 2020-01-01 2020-01-02 2
A 2020-01-01 2020-01-04 2020-01-07 4
A 2020-01-02 2020-01-09 2020-01-10 2
B 2019-12-31 2020-01-01 2020-01-02 2
B 2020-01-01 2020-01-04 2020-01-07 4
B 2020-01-02 2020-01-09 2020-01-10 2