SQL题目分析：打折日期交叉问题--计算品牌总优惠天数

在电商平台的数据分析中，处理品牌促销活动的日期交叉问题是一个挑战。本文将介绍几种高级SQL技巧，用于准确计算每个品牌的总优惠天数，即使在存在日期交叉的情况下。

问题背景

我们有一个促销活动表 shop_discount，记录了不同品牌的促销开始日期和结束日期。目标是计算每个品牌的总优惠天数，同时确保同一天内的多个优惠活动只计算一次。

如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期
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 天。

示例数据

1.建表

create table shop_discount(brand string,stt string,edt string
);

2.导入数据

INSERT INTO shop_discount (brand, stt, edt) VALUES
('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');

3.查询数据是否导入成功

select * from shop_discount;

高级SQL技巧

方法1：使用开窗进行连续区间划分及合并

这种方法通过识别每个品牌的连续促销区间来计算总天数。

SELECT brand,SUM(days) AS promotion_day_count
FROM (SELECT brand, DATEDIFF(MAX(edt), MIN(stt)) + 1 AS days FROM (SELECT brand, stt, edt,SUM(is_new_start) OVER (PARTITION BY brand ORDER BY stt) AS interval_idFROM (SELECT brand, stt, edt,IF(stt > COALESCE(LAG_END_DATE, '1970-01-01'), 1, 0) AS is_new_startFROM (SELECT brand, stt, edt,MAX(edt) OVER (PARTITION BY brand ORDER BY stt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS LAG_END_DATEFROM shop_discount) t1) t2) t3 GROUP BY brand, interval_id
) t4 
GROUP BY brand;

方法2：使用开窗求出没有活动的天数

这种方法通过计算每个促销区间之间的空白天数来调整总天数。

SELECT brand,DATEDIFF(MAX(edt), MIN(stt)) - SUM(no_promo_days) + 1 AS promotion_day_count
FROM (SELECT brand, stt, edt,IF(stt > LAG_END_DATE, DATEDIFF(stt, LAG_END_DATE) - 1, 0) AS no_promo_daysFROM (SELECT brand, stt, edt,MAX(edt) OVER (PARTITION BY brand ORDER BY stt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS LAG_END_DATEFROM shop_discount) t1
) t2 
GROUP BY brand;

方法3：使用开窗去除区间之间重复的部分

这种方法通过确保每个促销日期只被计算一次来计算总天数。

SELECT brand, SUM(DATEDIFF(edt, start_date) + 1) AS promotion_day_count
FROM (SELECT brand, MAX_END_DATE,IF(MAX_END_DATE IS NULL OR stt > MAX_END_DATE, stt, DATE_ADD(MAX_END_DATE, 1)) AS start_date, edtFROM (SELECT brand, stt, edt,MAX(edt) OVER (PARTITION BY brand ORDER BY stt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS MAX_END_DATEFROM shop_discount) t1
) t2 
WHERE edt > start_date 
GROUP BY brand;

方法4：使用UDTF生成所有活动日期然后去重

这种方法通过生成所有可能的促销日期并去除重复来计算总天数。

SELECT brand,COUNT(DISTINCT promo_date) AS promotion_day_count
FROM (SELECT brand, DATE_ADD(stt, pos) AS promo_date FROM shop_discountLATERAL VIEW POSEXPLODE(SPLIT(REPEAT(',', DATEDIFF(edt, stt)), ',')) tmp AS pos, element
) t1 
GROUP BY brand;