营销日期天数计算

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;

 

转载于:https://www.cnblogs.com/wqbin/p/11070873.html