公众号（阿龙学堂）：SqlBoy：经典面试题-打折日期交叉问题

一、需求

如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期

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

二、分析

题意要求求某品牌总的打折天数，有两种情景需要考虑：

情景一：两次活动的日期没有交叉。如OPPO的两次活动：

对于这种情况，我们直接分别求diff再做sum即可，即(edt-stt+1)：

情景二：两次活动的日期有交叉。如vivo的两次活动：

第一次活动时间为[2021-06-05 , 2021-06-15]，第二次活动时间为 [2021-06-09, 2021-06-21] 其中 9 号到 15 号为重复天数，只统计一次，即 vivo 总打折天数为 [2021-06-05,2021-06-21] 共计 17 天。

如果不考虑交叉，仍然按照情景一的方式计算，两段活动日期的diff分别为：11、13，加起来是24天，如何将中间重复的天数只计算一次呢？

注意观察，出现日期交叉的原因是因为第二次活动的开始时间小于第一次活动的结束时间，换句话说，第一次活动还没结束第二次活动就开始了。

换个角度思考，如果第二次活动在第一次活动结束后再开始，就不会出现日期交叉了，我们试试将第二次活动的开始时间改为第一次活动结束+1看看会是什么样：

我们再用情景一的计算方式计算出来，活动天数为：11+6=17，符合题意。

通过这样的转换，就能将交叉重复的日期只计算一次，所以到此可以总结为：在计算时，先将本次活动的起始时间改为上次活动的结束时间+1，再分别做diff再求和即可。这样我们就可以用lag()或者lead()将edt字段下移做计算。但这样真的可以吗？会存在一个问题，看个redmi例子：

会发现，有负数出现，但这个无关紧要，在做sum时会过滤掉 <0 的天数

非负数求和加起来是28天，跟真实的22天不符。

问题出在第三行，2021-06-17改为2021-06-16，其实第三行的开始时间应该改成第三行前面活动的最大结束时间+1，即改成2021-06-22即可。

所以，前面的总结需要修改一下：在计算时，先将本次活动的起始时间改为前几次活动的最大结束时间+1，再分别做diff再求和即可。

三、解法

第一步：将当前行以前的数据中最大的edt放置当前行（以 Redmi 数据为例）

select    id,    stt,    edt,    max(edt) over(partition by id order by stt rows between UNBOUNDED PRECEDING and 1 PRECEDING) maxEdtfrom test4; 记为 t1得到：redmi   2021-06-05  2021-06-21  nullredmi   2021-06-09  2021-06-15  2021-06-21redmi   2021-06-17  2021-06-26  2021-06-21

第二步：比较开始时间与移动下来的数据,如果开始时间大,则不需要操作,反之则需要将移动下来的数据加一替换当前行的开始时间，如果是第一行数据,maxEDT为null,则不需要操作。

select    id,    if(maxEdt is null,stt,if(stt>maxEdt,stt,date_add(maxEdt,1))) stt,    edtfrom t1; 记为 t2得到：redmi   2021-06-05  2021-06-21redmi   2021-06-22  2021-06-15redmi   2021-06-22  2021-06-26

第三步：将每行数据中的结束日期减去开始日期

select    id,    datediff(edt,stt) daysfrom t2; 记为 t3得到：redmi   16redmi   -4redmi   4

第四步：按照品牌分组,计算每条数据加一的总和​​​​​​​

select    id,    sum(if(days>=0,days+1,0)) daysfrom t3group by id;得到：redmi   22

第五步：最终SQL​​​​​​​

select    id,    sum(if(days>=0,days+1,0)) daysfrom (    select        id,        datediff(edt,stt) days    from (        select            id,            if(maxEdt is null,stt,if(stt>maxEdt,stt,date_add(maxEdt,1))) stt,            edt        from (            select                id,                stt,                edt,                max(edt) over(partition by id order by stt rows between UNBOUNDED PRECEDING and 1 PRECEDING) maxEdt            from test4        )t1    )t2)t3group by id;