题目
求各品牌活动天数,活动之间时间会有交叉重叠,重叠部分只算一次
表和数据
DROP TABLE IF EXISTS test_promotion_info
CREATE EXTERNAL TABLE test_promotion_info
(
promotion_id STRING COMMENT '优惠活动id',
brand STRING COMMENT '优惠品牌',
start_date STRING COMMENT '优化活动开始日期',
end_date STRING COMMENT '优惠活动结束日期'
) COMMENT '各品牌活动周期表';
INSERT OVERWRITE TABLE test_promotion_info VALUES
(1, 'oppo', '2023-06-05', '2023-06-09'),
(2, 'oppo', '2023-06-11', '2023-06-21'),
(3, 'vivo', '2023-06-05', '2023-06-15'),
(4, 'vivo', '2023-06-09', '2023-06-21'),
(5, 'redmi', '2023-06-05', '2023-06-21'),
(6, 'redmi', '2023-06-09', '2023-06-15'),
(7, 'redmi', '2023-06-17', '2023-06-26'),
(8, 'huawei', '2023-06-05', '2023-06-26'),
(9, 'huawei', '2023-06-09', '2023-06-15'),
(10, 'huawei', '2023-06-17', '2023-06-21');
解题思路
- 获取当前历史活动中的最大的结束日期(截止当前活动起始日期的前一天)current_max_date
- 处理交叉日期:
- current_max_date为空,说明该品牌是第一次进行活动
- current_max_date < start_date,说明该品牌不存在交叉
- current_max_date > start_date,说明该品牌存在交叉,需要对该时间进行调整,start_date = current_max_date + 1
- 对brand进行 end_date - start_date + 1 求和
详细步骤
WITH temp1 AS (
SELECT
promotion_id,
brand,
start_date,
end_date,
max(end_date) over (partition by brand order by start_date rows between unbounded preceding and 1 preceding) as current_max_date
FROM
test_promotion_info
), temp2 AS (
SELECT
promotion_id,
brand,
start_date,
end_date,
IF(
current_max_date is null or current_max_date < start_date, start_date, date_add(current_max_date, 1)
) as cal_start_date
FROM
temp1
)
SELECT
brand,
sum(datediff(end_date, cal_start_date) + 1) AS days
FROM
temp2
WHERE
end_date > cal_start_date
GROUP BY
brand
;
最终结果
+---------+-------+
| brand | days |
+---------+-------+
| oppo | 16 |
| vivo | 17 |
| huawei | 22 |
| redmi | 22 |
+---------+-------+
数据验证
select
promotion_id,
brand,
start_date,
end_date,
cal_start_date,
IF(end_date > cal_start_date, datediff(end_date, cal_start_date) + 1, 0) as days
from
temp2 a;
+---------------+---------+-------------+-------------+-----------------+-------+
| promotion_id | brand | start_date | end_date | cal_start_date | days |
+---------------+---------+-------------+-------------+-----------------+-------+
| 1 | oppo | 2023-06-05 | 2023-06-09 | 2023-06-05 | 5 |
| 2 | oppo | 2023-06-11 | 2023-06-21 | 2023-06-11 | 11 |
| 3 | vivo | 2023-06-05 | 2023-06-15 | 2023-06-05 | 11 |
| 4 | vivo | 2023-06-09 | 2023-06-21 | 2023-06-16 | 6 |
| 8 | huawei | 2023-06-05 | 2023-06-26 | 2023-06-05 | 22 |
| 9 | huawei | 2023-06-09 | 2023-06-15 | 2023-06-27 | 0 |
| 10 | huawei | 2023-06-17 | 2023-06-21 | 2023-06-27 | 0 |
| 5 | redmi | 2023-06-05 | 2023-06-21 | 2023-06-05 | 17 |
| 6 | redmi | 2023-06-09 | 2023-06-15 | 2023-06-22 | 0 |
| 7 | redmi | 2023-06-17 | 2023-06-26 | 2023-06-22 | 5 |
+---------------+---------+-------------+-------------+-----------------+-------+