有一张促销活动表 promotion,它记录了各品牌进行促销活动的开始时间和结束时间,同一个品牌在某段时间内可能会参与多次促销活动,即同一个品牌的活动时间范围可能会重叠。为了减少干扰,我们只保留了 brand、start_date、end_date 这几个字段。promotion 现有的记录如下:
brand start_date end_date
------ ---------- ------------
A 2018-09-01 2018-09-05
A 2018-09-03 2018-09-06
A 2018-09-09 2018-09-15
B 2018-08-04 2018-08-15
B 2018-08-04 2018-08-05
C 2018-08-15 2018-08-21
C 2018-09-02 2018-09-12
要求:统计各个品牌实际举行促销活动的天数,重叠的日期仅计算一次。 比如品牌A,在 2018-09-01 ~ 2018-09-05 和 2018-09-03 ~ 2018-09-06 这段时间内总共做了两次活动,不过在 2018-09-03 ~ 2018-09-05 这段时间内出现了重叠,所以品牌A 在 2018-09-01 ~ 2018-09-06 这段里总共做了 6 天的活动。
最终输出的结果:
brand days
------ --------
A 13
B 12
C 18
思路:枚举出各品牌举行促销活动的所有日期,再根据品牌分组统计不重复日期的活动天数。
大致有两种方式实现日期的枚举,一种是使用递归,另外一种是借助数字辅助表。
递归方式的实现脚本:
WITH recursive t0 (brand, start_date, end_date) AS
(SELECT
brand,
start_date,
end_date
FROM
promotion
UNION ALL
SELECT
brand,
DATE_ADD(start_date, INTERVAL 1 DAY) AS start_date,
end_date
FROM
t0
WHERE start_date < end_date)
SELECT
brand,
COUNT(DISTINCT start_date) AS days
FROM
t0
GROUP BY brand
digit 是数字辅助表,它只有一个字段 id,digit 存储了从 1 ~ 1000 的自然数。
借助数字辅助表的实现脚本:
WITH t0 AS
(SELECT
brand,
start_date,
end_date,
DATE_ADD(start_date, INTERVAL d.id - 1 DAY) AS p_date
FROM
promotion p,
digit d
WHERE d.id <= DATEDIFF(p.end_date, p.start_date) + 1)
SELECT
brand,
COUNT(DISTINCT p_date) AS days
FROM
t0
GROUP BY brand