SQL 统计实际的活动天数
有一张促销活动表 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
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2021-02-21，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读