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

概述

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

题目：交叉打折问题求解

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

表logs

字段名	数据类型
brand	string
stt	date
edt	date

示例数据如下：

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

计算每个品牌总的打折销售天数，注意其中的交叉日期：
比如 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_edtFROM logs
)

输出如下：

brand	stt	edt	prev_max_edt
oppo	2021-06-05	2021-06-09	NULL
oppo	2021-06-11	2021-06-21	2021-06-09
vivo	2021-06-05	2021-06-15	NULL
vivo	2021-06-09	2021-06-21	2021-06-15
redmi	2021-06-05	2021-06-21	NULL
redmi	2021-06-09	2021-06-15	2021-06-21
redmi	2021-06-17	2021-06-26	2021-06-21
huawei	2021-06-05	2021-06-26	NULL
huawei	2021-06-09	2021-06-15	2021-06-26
huawei	2021-06-17	2021-06-21	2021-06-26

步骤二：拆分出交叉部分

通过比较当前行的开始日期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, edtFROM temp_001
)

输出结果：

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-16	2021-06-21
redmi	2021-06-05	2021-06-21
redmi	2021-06-22	2021-06-15
redmi	2021-06-22	2021-06-26
huawei	2021-06-05	2021-06-26
huawei	2021-06-27	2021-06-15
huawei	2021-06-27	2021-06-21

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

temp_003 AS (SELECT brand,stt,edt,DATE_DIFF(edt,stt) AS daysFROM temp_002
)

输出结果

brand	stt	edt	days
oppo	2021-06-05	2021-06-09	4
oppo	2021-06-11	2021-06-21	10
vivo	2021-06-05	2021-06-15	10
vivo	2021-06-16	2021-06-21	5
redmi	2021-06-05	2021-06-21	16
redmi	2021-06-22	2021-06-15	-7
redmi	2021-06-22	2021-06-26	4
huawei	2021-06-05	2021-06-26	21
huawei	2021-06-27	2021-06-15	-12
huawei	2021-06-27	2021-06-21	-6

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

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

输出结果

brand	ttl_days
huawei	21
oppo	14
redmi	20
vivo	15

完整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_edtFROM 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, edtFROM temp_001
)
,temp_003 AS (SELECT brand,stt,edt,DATE_DIFF(edt,stt) AS daysFROM temp_002
)
SELECT brand,SUM(IF(days>0, days, 0 )) AS ttl_days
FROM temp_003
GROUP BY brand
;