1 nike 20180901 20180905 2 nike 20180903 20180906 3 nike 20180909 20180915 4 oppo 20180804 20180805 5 oppo 20180804 20180815 6 vivo 20180815 20180821 7 vivo 20180902 20180912 8 vivo 20180903 20180910 CREATE TABLE business( id int, brand string, startdate string, enddate string) row format delimited fields terminated by '\t'; 1.将上一行的enddate放置在下一行,并格式化时间(格式化为yyyy-MM-dd,方便后期求时间差) select brand, from_unixtime(unix_timestamp(startdate,'yyyymmdd'),'yyyy-mm-dd') startdate, from_unixtime(unix_timestamp(enddate,'yyyymmdd'),'yyyy-mm-dd') enddate, from_unixtime(unix_timestamp(lag(enddate,1,'19700101') over(partition by brand order by startdate),'yyyymmdd'),'yyyy-mm-dd') lagenddate from business;t1 2.计算每一行开始跟下一行结束时间,如果当前行开始时间比上一行结束时间大,用当前行开始时间 否则,用上一行结束时间加一天代替当前行的开始日期,同时判断当前行结束时间跟上一行结束时 间大小,防止出现数据完全包裹的情况。 select brand, if(startdate>lagenddate,startdate,date_add(lagenddate,1)) startdate, if(enddate>lagenddate,enddate,lagenddate) enddate from t1;t2 3.按照品牌计算打折时间 select brand, sum(datediff(enddate,startdate)+1) from t2 group by brand; 最终SQL: select brand, sum(datediff(enddate,startdate)+1) from (select brand, if(startdate>lagenddate,startdate,date_add(lagenddate,1)) startdate, if(enddate>lagenddate,enddate,lagenddate) enddate from (select brand, from_unixtime(unix_timestamp(startdate,'yyyymmdd'),'yyyy-mm-dd') startdate, from_unixtime(unix_timestamp(enddate,'yyyymmdd'),'yyyy-mm-dd') enddate, from_unixtime(unix_timestamp(lag(enddate,1,'19700101') over(partition by brand order by startdate),'yyyymmdd'),'yyyy-mm-dd') lagenddate from business)t1)t2 group by brand;