小红书大数据面试SQL-品牌营销活动天数
一、题目
有营销活动记录表，记录了每个品牌每次营销活动的开始日期和营销活动的结束日期，现需要统计出每个品牌的总营销天数。 注意： 1:苹果第一行数据的营销结束日期比第二行数据的营销开始日期要晚，这部分有重叠的日期的要去重计算。 2:苹果第二行数据的营销结束日期和第三行的开始日期不连续，2019-09-07以及2019-09-08不统计到营销天数中。 样例数据
+--------+-------------+-------------+
| brand | start_date | end_date |
+--------+-------------+-------------+
| 华为 | 2018-08-04 | 2018-08-05 |
| 华为 | 2018-08-04 | 2020-12-25 |
| 小米 | 2018-08-15 | 2018-08-20 |
| 小米 | 2020-01-01 | 2020-01-05 |
| 苹果 | 2018-09-01 | 2018-09-05 |
| 苹果 | 2018-09-03 | 2018-09-06 |
| 苹果 | 2018-09-09 | 2018-09-15 |
+--------+-------------+-------------+结果
+--------+-----------+
| brand | act_days |
+--------+-----------+
| 华为 | 875 |
| 小米 | 11 |
| 苹果 | 13 |
+--------+-----------+二、分析
本题难点在解决交叉问题，但是题目给出的是开始日期和结束日期，我们根据开始和结束日期，使用生成函数，生成活动期间每天的记录，然后根据品牌分组，对日期进行去重即可
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
1.生成每次活动每天的记录
select
brand,
start_date,
end_date,
t.pos,
t.value,
date_add(start_date,t.pos) as act_date
from t1_marketing_act
lateral view posexplode(split(space(datediff(end_date,start_date)),'')) t as pos,value；执行结果 这里由于数据太多，我们仅展示“苹果数据”
+--------+-------------+-------------+--------+----------+-------------+
| brand | start_date | end_date | t.pos | t.value | act_date |
+--------+-------------+-------------+--------+----------+-------------+
| 苹果 | 2018-09-01 | 2018-09-05 | 0 | | 2018-09-01 |
| 苹果 | 2018-09-01 | 2018-09-05 | 1 | | 2018-09-02 |
| 苹果 | 2018-09-01 | 2018-09-05 | 2 | | 2018-09-03 |
| 苹果 | 2018-09-01 | 2018-09-05 | 3 | | 2018-09-04 |
| 苹果 | 2018-09-01 | 2018-09-05 | 4 | | 2018-09-05 |
| 苹果 | 2018-09-03 | 2018-09-06 | 0 | | 2018-09-03 |
| 苹果 | 2018-09-03 | 2018-09-06 | 1 | | 2018-09-04 |
| 苹果 | 2018-09-03 | 2018-09-06 | 2 | | 2018-09-05 |
| 苹果 | 2018-09-03 | 2018-09-06 | 3 | | 2018-09-06 |
| 苹果 | 2018-09-09 | 2018-09-15 | 0 | | 2018-09-09 |
| 苹果 | 2018-09-09 | 2018-09-15 | 1 | | 2018-09-10 |
| 苹果 | 2018-09-09 | 2018-09-15 | 2 | | 2018-09-11 |
| 苹果 | 2018-09-09 | 2018-09-15 | 3 | | 2018-09-12 |
| 苹果 | 2018-09-09 | 2018-09-15 | 4 | | 2018-09-13 |
| 苹果 | 2018-09-09 | 2018-09-15 | 5 | | 2018-09-14 |
| 苹果 | 2018-09-09 | 2018-09-15 | 6 | | 2018-09-15 |
+--------+-------------+-------------+--------+----------+-------------+2.根据品牌分组，act_date进行去重统计
with t as (
select
brand,
start_date,
end_date,
t.pos,
t.value,
date_add(start_date, t.pos) as act_date
from t1_marketing_act
lateral view posexplode(split(space(datediff(end_date, start_date)), '')) t as pos, value)
select
brand,
count(distinct act_date) as act_days
from t
group by brand;执行结果
+--------+-----------+
| brand | act_days |
+--------+-----------+
| 华为 | 875 |
| 小米 | 11 |
| 苹果 | 13 |
+--------+-----------+四、建表语句和数据插入
CREATE TABLE IF NOT EXISTS t1_marketing_act (
brand STRING, --品牌
start_date STRING, -- 营销活动开始日期
end_date STRING -- 营销活动结束日期
);
insert into t1_marketing_act(brand, start_date, end_date) values
('华为','2018-08-04','2018-08-05'),
('华为','2018-08-04','2020-12-25'),
('小米','2018-08-15','2018-08-20'),
('小米','2020-01-01','2020-01-05'),
('苹果','2018-09-01','2018-09-05'),
('苹果','2018-09-03','2018-09-06'),
('苹果','2018-09-09','2018-09-15');
本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表；