SQL实战：06交叉日期打折问题求解

概述

最近刷题时遇到一些比较有意思的题目，就决定记录下来，并将解题过程一一拆解。此文中要记录的是交叉打折日期问题的求解。

题目：交叉打折问题求解

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

表logs

示例数据如下：

计算每个品牌总的打折销售天数，注意其中的交叉日期：
比如 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字段升序进行处理，并使用滑动窗口统计出窗口中到当前行之前的最大的结束日期，使用MAX(edt) OVER(PARTITION BY brand ORDER BY stt ASC, edt ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING )

• 第二步：拆分出交叉部分，通过比较当前行的开始日期stt和上一步骤中统计出来的当前行之前的最大结束日期prev_max_edt的大小：

  • 如果当前活动的开始日期比之前的最大的结束日期大，则说明两次活动之间没有交叉；

  • 如果当前活动的开始日期比之前活动最大结束日期小，则说明二者具有交叉，那么这次活动的开始日期要重置为上一次最大日期的后一天，这样在计算活动时长时才不会有交叉

• 第三步：计算每次活动的持续天数，在第二步将每次活动都处理成了没有交叉日期的数据之后，就可以利用结束日期减去开始日期，统计出每次活动的持续时长。在这一步的处理结果中可能会得到负数，这表示这次活动的结束日期比“开始日期”小，这说明当前活动被包含在了上一次活动期间。

• 第四步：分组统计最后结果，第三步中已经得出了每次活动的持续时长，最后就只需要按照分组统计，将每个品牌的活动时长相加即可。需要注意的是，如果活动的时长为负数(第三步已解释)，则不参与计算

第一步：使用滑动窗口统计当前活动前的最大结束日期

WITH temp_001 AS (
    SELECT brand
        ,stt
        ,edt
        ,MAX(edt) OVER(PARTITION BY brand ORDER BY stt,edt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) AS prev_max_edt
    FROM logs
)

输出如下：

步骤二：拆分出交叉部分

通过比较当前行的开始日期stt和上一步骤中统计出来的当前行之前的最大结束日期prev_max_edt的大小：

• 如果当前活动的开始日期比之前的最大的结束日期大，则说明两次活动之间没有交叉；

• 如果当前活动的开始日期比之前活动最大结束日期小，则说明二者具有交叉，那么这次活动的开始日期要重置为上一次最大日期的后一天，这样在计算活动时长时才不会有交叉

SQL实现：

temp_002 AS (
    SELECT brand
        , IF(prev_max_dt IS NULL, stt, IF(stt>prev_max_dt, stt, DATE_ADD(max_edt, INTERVAL 1 DAY ))) AS stt
        , edt
    FROM temp_001
)

输出结果：

步骤三：计算每次活动的持续天数

temp_003 AS (
    SELECT brand
        ,stt
        ,edt
        ,DATE_DIFF(edt,stt) AS days
    FROM temp_002
)

输出结果

步骤四：分组统计最终结果

SELECT brand
    ,SUM(IF(days>0, days, 0 )) AS ttl_days
FROM temp_003
GROUP BY brand
;

输出结果

完整SQL

WITH temp_001 AS (
    SELECT brand
        ,stt
        ,edt
        ,MAX(edt) OVER(PARTITION BY brand ORDER BY stt,edt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) AS prev_max_edt
    FROM logs
)
,temp_002 AS (
    SELECT brand
        , IF(prev_max_dt IS NULL, stt, IF(stt>prev_max_dt, stt, DATE_ADD(max_edt, INTERVAL 1 DAY ))) AS stt
        , edt
    FROM temp_001
)
,temp_003 AS (
    SELECT brand
        ,stt
        ,edt
        ,DATE_DIFF(edt,stt) AS days
    FROM temp_002
)
SELECT brand
    ,SUM(IF(days>0, days, 0 )) AS ttl_days
FROM temp_003
GROUP BY brand
;