SQL打折日期交叉问题

 题目

计算每个品牌总的打折销售天数，注意其中的交叉日期，比如 vivo 品牌，
第一次活动时间为 2021-06-05 到 2021-06-15，第二次活动时间为 2021-06-09 到 2021-06-21 其中 9 号到 15号为重复天数，
只统计一次，即 vivo 总打折天数为 2021-06-05 到 2021-06-21 共计 17 天。

如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期
brand stt        edt
oppo,2021-06-05,2021-06-09
oppo,2021-06-11,2021-06-21
vivo,2021-06-05,2021-06-15
vivo,2021-06-09,2021-06-21
redmi,2021-06-05,2021-06-21
redmi,2021-06-09,2021-06-15
redmi,2021-06-17,2021-06-26
huawei,2021-06-05,2021-06-26
huawei,2021-06-09,2021-06-15
huawei,2021-06-17,2021-06-21

 建表，表名字用t_18。

create table t_18(
    brand string,
    stt string,
    edt string
)row format delimited
fields terminated by ',';

 导入数据，数据在/home/t_18.txt。

load data local inpath '/home/t_18.txt' into table t_18;

 因为有交叉日期进行不能用每一行的edt-stt然后相加。

方法一

先找出日期交叉的部分

        将开始时间与结束时间放到一列，开始时间标记为1，结束时间标记为-1。

select brand,stt t_date,1 co from t_18
union
select brand,edt,-1 co from t_18;

 

对co列进行累加作为表的第四列gs。

with t as (
    select brand,stt t_date,1 co from t_18
    union
    select brand,edt,-1 co from t_18
)
select *,sum(co) over(partition by brand order by t_date) gs from t;

 

 如果gs不等于0，表示有交叉日期。如下表活动日期就是（F-A+1）+（H-G+1），另一种表示是(B-A)+(C-B)+(D-C)+(E-D)+(F-E)+1+(H-G)+1

将下一列日期作为表的第五列e_date。如果gs=0，就用该行日期本身。

with t as (
    select brand,stt t_date,1 co from t_18
    union
    select brand,edt,-1 co from t_18
),t1 as (
    select *,sum(co) over(partition by brand order by t_date) gs from t
)
select *,`if`(gs!=0,lead(t_date,1,t_date) over (partition by brand order by t_date),t_date) e_date from t1;

然后日期相减，作为数据的第六列days。

with t as (
    select brand,stt t_date,1 co from t_18
    union
    select brand,edt,-1 co from t_18
),t1 as (
    select *,sum(co) over(partition by brand order by t_date) gs from t
),t2 as (
    select *,`if`(gs!=0,lead(t_date,1,t_date) over (partition by brand order by t_date),t_date) e_date from t1
)
select *,datediff(e_date,t_date) days from t2;

最后将days分组相加再加上gs=0的个数。

with t as (
    select brand,stt t_date,1 co from t_18
    union
    select brand,edt,-1 co from t_18
),t1 as (
    select *,sum(co) over(partition by brand order by t_date) gs from t
),t2 as (
    select *,`if`(gs!=0,lead(t_date,1,t_date) over (partition by brand order by t_date),t_date) e_date from t1
),t3 as (
    select *,datediff(e_date,t_date) days from t2
)
select brand,sum(days)+count(`if`(gs=0,1,null)) tds from t3 group by brand;

方法二

给每行添加一个该行之前中的最大结束日期字段max_date。

select brand, stt, edt,
       max(edt) over (partition by brand order by stt rows between unbounded preceding and 1 preceding ) max_date
from t_18;

 找没有统计过的时间。

条件：

        1.stt>max_date或者max_date是空,求差+1。
        2.edt<=max_date，最大时间大于等于该行的结束时间，已经计算过了。
        3.其他就是日期交叉的情况，要避免重复统计。没有重复的时间是max_date+1天到edt。

with t as (
    select brand, stt, edt,
       max(edt) over (partition by brand order by stt rows between unbounded preceding and 1 preceding ) max_date
from t_18
)
select *,
        case when edt<=max_date then 0
             when stt>max_date or max_date is null then datediff(edt,stt)+1
             else datediff(edt,max_date)
        end as days
from t;

最后将之前所得的天数累加。

with t as (
    select brand, stt, edt,
       max(edt) over (partition by brand order by stt rows between unbounded preceding and 1 preceding ) max_date
from t_18
),t1 as (
    select *,
        case when edt<=max_date then 0
             when stt>max_date or max_date is null then datediff(edt,stt)+1
             else datediff(edt,max_date)
        end as days
    from t
)
select brand,sum(days) tds from t1 group by brand;