数仓面试——日期交叉问题
Hi, 我是小萝卜算子
一、简介
日期交叉去重问题，是一个经典sql，本文以一个电脑品牌促销的例子从不同的角度来看待解析这个问题，有更好方法的同学，欢迎私下交流...
二、表结构
CREATE TABLE `computer_promotion`(
`brand` string COMMENT '用户主键',
`start_date` string COMMENT '开始日期',
`end_date` string COMMENT '结束日期')
三、表数据
brand start_date end_date
lenovo 2022-02-03 2022-02-07
lenovo 2022-02-10 2022-02-23
asus 2022-02-08 2022-02-24
asus 2022-02-13 2022-02-17
asus 2022-02-15 2022-02-28
dell 2022-02-04 2022-02-17
dell 2022-02-07 2022-02-21
hp 2022-02-06 2022-02-26
hp 2022-02-08 2022-02-19
hp 2022-02-15 2022-02-23
四、需求： 根据表数据求出每种电脑品牌促销的天数
方法一：根据开始和结束时间拆解促销日期（开始时间为正，结束时间为负）
select
brand,
sum(days) total_days
from
(SELECT
brand,
base_line,
max(dt) max_dt,
datediff(max(dt), base_line) + 1 days
FROM
(
SELECT
brand,
dt,
max(base_flag) over (PARTITION BY brand ORDER BY dt) base_line
FROM
(
SELECT
brand,
dt,
CASE
WHEN sum(flag) over (
PARTITION BY brand
ORDER BY
concat(dt, flag) DESC
) = 0 THEN
dt
ELSE
"0"
END base_flag
FROM
(
SELECT
brand,
start_date dt,
1 AS flag
FROM
computer_promotion
UNION ALL
SELECT
brand,
end_date dt,
- 1 AS flag
FROM
computer_promotion
) tmp
) tmp
) tmp
GROUP BY
brand,
base_line) tmp
group by
brand ;详解：
1：根据开始可结束时间拆分，开始时间为正，结束时间为负，标记字段为flag
2：利用sum窗口函数，累加flag，sum（flag）=0 则打折日期结束或者与下一段打折日期断开，记为当前日期
3：根据步骤二的结果，利用窗口函数max，找出连续打折日期的分隔基准线
4：根据品牌和基准线分组，计算出每段的打折天数
5：根据品牌分组，计算出每个品牌总的打折天数
方法二：根据促销开始时间排序，手工修改下次促销的开始时间
SELECT
brand,
sum(
datediff(end_date, start_date) + 1
) total_days
FROM
(
SELECT
brand,
CASE
WHEN start_date <= max_edd THEN
date_add (max_edd, 1)
ELSE
start_date
END start_date,
end_date
FROM
(
SELECT
brand,
start_date,
end_date,
max(end_date) over (
partition by brand
order by
start_date
rows between
UNBOUNDED PRECEDING
and 1 PRECEDING) max_edd
FROM
computer_promotion
) tmp
) tmp
WHERE
end_date > start_date
GROUP BY
brand;详解：
1：采用max窗口函数，根据开始日期正序，获得此次促销记录之前最大的促销结束日期
2：比较此次促销开始日期与步骤一获得的结束日期，如果开始日期比结束日期小或者相等，那么以步骤一获得的日期加一天作为此次促销的开始日期，反之，记当前记录的开始日期为本次促销的开始日期
3：过滤掉开始日期大于结束日期的数据，并且根据品牌分组，对每条记录的结束和开始日期求日期差+1，然后求sum， 获得最终结果
方法三：展开促销活动的每一天，然后去重
SELECT
brand,
count(1) cnt
FROM
(
SELECT
brand,
curr_date
FROM
(
SELECT
brand,
start_date,
end_date,
col_idx,
date_add (start_date, col_idx) curr_date
FROM
computer_promotion lateral VIEW posexplode (
split (
space(
datediff(end_date, start_date) + 1
),
" (?!$)"
)
) tbl_idx AS col_idx,
col_val
) tmp
GROUP BY
brand,
curr_date
) tmp
GROUP BY
brand详解：
1：利用posexplode的序列，展开促销的开始和结束日期
2：根据品牌和日期去重
3：根据品牌分组，count获得最终的促销天数
五、拓展
1：本文从3个方向去解析交叉日期去重，以后碰到交叉问题，都可以迎刃而解
2：方法一是直接拆分开始结束日期，然后打上一个flag标记，获得连续的日期，想法比较新颖
3：方法二中， hive低版本不支持使用max窗口函数，求之前记录的最大值，同学们可以变换一下思维，即可实现
4：方法二比较巧妙的利用了窗口函数max，规避了日期交叉
5：对类似问题，如果数据量小的话，个人更倾向于方法三，简单明了，易于理解
六、想一想
方法一中第一次排序，为什么要concat(dt,flag)，而后面的排序仅仅使用dt就行了呢
方法二中，hive低版本可以先获得前一条记录的促销结束时间，然后用max窗口函数获得此字段结束时间的最大值，然后跟当前开始日期比较
方法三split的正则表达式为什么要写成" (?!$)"
腾讯云开发者
