create table marking (brand string ,startdate string ,enddate string );
insert into marking VALUES('华为','2018-08-04','2018-08-05');
insert into marking VALUES('华为','2018-08-04','2020-12-25');
insert into marking VALUES('小米','2018-08-15','2018-08-20');
insert into marking VALUES('小米','2020-01-01','2020-01-05');
insert into marking VALUES('苹果','2018-09-01','2018-09-05');
insert into marking VALUES('苹果','2018-09-03','2018-09-06');
insert into marking VALUES('苹果','2018-09-09','2018-09-15');
t1:
select brand, startdate, enddate ,min(startdate)over(partition by brand ) min_date ,max(enddate)over(partition by brand) max_date,max(enddate)over(partition by brand order by startdate asc rows between unbounded preceding and 1 preceding ) last_date from marking ;
t2:
select brand, startdate, enddate,datediff(max_date ,min_date) as all_date ,if(datediff(startdate,last_date )<=0,0,datediff(startdate,last_date)-1) as differ_date
from t1
t3:
select brand,startdate,enddate,max(all_date)-sum(differ_date ) as tianshu from t2;
select brand,startdate,enddate,max(all_date)-sum(differ_date ) as tianshu from (
select brand,
startdate,
enddate,
min_date,
max_date,datediff(max_date ,min_date)+1 all_date ,if(datediff(startdate,last_date )<=0,0,datediff(startdate,last_date)-1) differ_date
from (
select brand, startdate, enddate ,min(startdate)over(partition by brand ) min_date ,max(enddate)over(partition by brand) max_date,max(enddate)over(partition by brand order by startdate asc rows between unbounded preceding and 1 preceding ) last_date
from marking
) t1
) t2 ;