横扫SQL面试
📌日期交叉问题
❓ “如何统计品牌优惠活动的总天数、统计每月有效的订阅用户数（Netflix 会员），避免重复计算重叠日期？”
❓ “怎样快速合并时间区间，精准计算有效天数？”
🍬🍬1.品牌优惠天数统计
现有各品牌优惠周期表（promotion_info）如下，其记录了每个品牌的每个优惠活动的周期，其中同一品牌的不同优惠活动的周期可能会有交叉。
| promotion_id | brand | start_date | end_date |
|---|---|---|---|
| 1 | oppo | 2021-06-05 | 2021-06-09 |
| 2 | oppo | 2021-06-11 | 2021-06-21 |
| 3 | vivo | 2021-06-05 | 2021-06-15 |
| 4 | vivo | 2021-06-10 | 2021-06-22 |
| 5 | redmi | 2021-06-01 | 2021-06-10 |
| 6 | redmi | 2021-06-15 | 2021-06-22 |
| 7 | huawei | 2021-06-03 | 2021-06-13 |
| 8 | huawei | 2021-06-18 | 2021-06-27 |
现要求统计每个品牌的优惠总天数，若某个品牌在同一天有多个优惠活动，则只按一天计算。期望结果如下：
| brand | promotion_day_count |
|---|---|
| vivo | 18 |
| oppo | 16 |
| redmi | 18 |
| huawei | 21 |
其实跟会话划分问题很像~
1：获取上一个活动的结束日期 ✅✅✅
| brand | start_date | end_date | prev_end |
|---|---|---|---|
| oppo | 2021-06-05 | 2021-06-09 | null |
| oppo | 2021-06-11 | 2021-06-21 | 2021-06-09 |
| vivo | 2021-06-05 | 2021-06-15 | null |
| vivo | 2021-06-10 | 2021-06-22 | 2021-06-15 |
| redmi | 2021-06-01 | 2021-06-10 | null |
| redmi | 2021-06-15 | 2021-06-22 | 2021-06-10 |
| huawei | 2021-06-03 | 2021-06-13 | null |
| huawei | 2021-06-18 | 2021-06-27 | 2021-06-13 |
2：判断是否开启新组 ✅✅✅
| brand | start_date | end_date | is_new_group | 判断逻辑说明 |
|---|---|---|---|---|
| oppo | 2021-06-05 | 2021-06-09 | 0 | 前驱为 null，默认不新开组 |
| oppo | 2021-06-11 | 2021-06-21 | 1 | 2021-06-11 > 2021-06-09+1 → 新组 |
| vivo | 2021-06-05 | 2021-06-15 | 0 | 前驱为 null |
| vivo | 2021-06-10 | 2021-06-22 | 0 | 2021-06-10 ≤ 2021-06-15+1 → 不新开组 |
| redmi | 2021-06-01 | 2021-06-10 | 0 | 前驱为 null |
| redmi | 2021-06-15 | 2021-06-22 | 1 | 2021-06-15 > 2021-06-10+1 → 新组 |
| huawei | 2021-06-03 | 2021-06-13 | 0 | 前驱为 null |
| huawei | 2021-06-18 | 2021-06-27 | 1 | 2021-06-18 > 2021-06-13+1 → 新组 |
3：分配组 ID ✅✅✅
| brand | start_date | end_date | group_id | 计算逻辑说明 |
|---|---|---|---|---|
| oppo | 2021-06-05 | 2021-06-09 | 0 | 初始组，is_new_group=0 → group_id=0 |
| oppo | 2021-06-11 | 2021-06-21 | 1 | is_new_group=1 → group_id=0+1=1 |
| vivo | 2021-06-05 | 2021-06-15 | 0 | 初始组 |
| vivo | 2021-06-10 | 2021-06-22 | 0 | is_new_group=0 → group_id 保持 0 |
| redmi | 2021-06-01 | 2021-06-10 | 0 | 初始组 |
| redmi | 2021-06-15 | 2021-06-22 | 1 | is_new_group=1 → group_id=0+1=1 |
| huawei | 2021-06-03 | 2021-06-13 | 0 | 初始组 |
| huawei | 2021-06-18 | 2021-06-27 | 1 | is_new_group=1 → group_id=0+1=1 |
4：合并区间并计算天数 ✅✅✅
| brand | merged_start | merged_end | 合并逻辑说明 |
|---|---|---|---|
| oppo | 2021-06-05 | 2021-06-09 | 组 0 的合并区间 |
| oppo | 2021-06-11 | 2021-06-21 | 组 1 的合并区间 |
| vivo | 2021-06-05 | 2021-06-22 | 组 0 的合并区间（两个活动重叠） |
| redmi | 2021-06-01 | 2021-06-10 | 组 0 的合并区间 |
| redmi | 2021-06-15 | 2021-06-22 | 组 1 的合并区间 |
| huawei | 2021-06-03 | 2021-06-13 | 组 0 的合并区间 |
| huawei | 2021-06-18 | 2021-06-27 | 组 1 的合并区间 |
最终结果：统计总天数 ✅✅✅
select brand,sum(datediff(merged_end, merged_start) + 1) as promotion_day_count
from t4
group by brand;
输出结果：
| brand | promotion_day_count | 天数计算逻辑 |
|---|---|---|
| oppo | (2021-06-09 - 2021-06-05) +1 = 5 + (2021-06-21 - 2021-06-11) +1 = 11 → 16 | 5 + 11 = 16 |
| vivo | (2021-06-22 - 2021-06-05) +1 = 18 | 单组合并 |
| redmi | (2021-06-10 - 2021-06-01) +1 = 10 + (2021-06-22 - 2021-06-15) +1 = 8 → 18 | 10 + 8 = 18 |
| huawei | (2021-06-13 - 2021-06-03) +1 = 11 + (2021-06-27 - 2021-06-18) +1 = 10 → 21 | 11 + 10 = 21 |
🍬🍬2.员工项目参与天数统计
某公司记录了员工参与不同项目的时间段，一个员工可能同时参与多个项目，不同项目的时间段可能存在重叠。现在需要计算每个员工实际参与项目的总天数（重叠日期不重复计算）。
员工项目参与表（employee_projects）结构如下：
| emp_id | emp_name | project_id | project_name | start_date | end_date |
|---|---|---|---|---|---|
| 1001 | 张三 | P001 | 电商系统 | 2023-03-01 | 2023-03-10 |
| 1001 | 张三 | P002 | 支付系统 | 2023-03-05 | 2023-03-15 |
| 1001 | 张三 | P003 | 数据分析 | 2023-03-20 | 2023-03-25 |
| 1002 | 李四 | P004 | 移动端开发 | 2023-03-01 | 2023-03-08 |
| 1002 | 李四 | P005 | 后台优化 | 2023-03-10 | 2023-03-18 |
| 1003 | 王五 | P006 | 测试平台 | 2023-03-01 | 2023-03-20 |
| 1003 | 王五 | P007 | 自动化部署 | 2023-03-15 | 2023-03-25 |
| 1003 | 王五 | P008 | 安全审计 | 2023-03-22 | 2023-03-30 |
统计每个员工实际参与项目的总天数（重叠日期不重复计算）：
| emp_id | emp_name | total_days |
|---|---|---|
| 1001 | 张三 | 21 |
| 1002 | 李四 | 17 |
| 1003 | 王五 | 30 |
套路一样滴：
1.获取前一个项目的结束日期✅✅✅
| emp_id | emp_name | start_date | end_date | prev_end |
|---|---|---|---|---|
| 1001 | 张三 | 2023-03-01 | 2023-03-10 | NULL |
| 1001 | 张三 | 2023-03-05 | 2023-03-15 | 2023-03-10 |
| 1001 | 张三 | 2023-03-20 | 2023-03-25 | 2023-03-15 |
| 1002 | 李四 | 2023-03-01 | 2023-03-08 | NULL |
| 1002 | 李四 | 2023-03-10 | 2023-03-18 | 2023-03-08 |
| 1003 | 王五 | 2023-03-01 | 2023-03-20 | NULL |
| 1003 | 王五 | 2023-03-15 | 2023-03-25 | 2023-03-20 |
| 1003 | 王五 | 2023-03-22 | 2023-03-30 | 2023-03-25 |
2.第二步：判断是否新区间 (t2)✅✅✅
| emp_id | emp_name | start_date | end_date | is_new_group |
|---|---|---|---|---|
| 1001 | 张三 | 2023-03-01 | 2023-03-10 | 0 |
| 1001 | 张三 | 2023-03-05 | 2023-03-15 | 0 |
| 1001 | 张三 | 2023-03-20 | 2023-03-25 | 1 |
| 1002 | 李四 | 2023-03-01 | 2023-03-08 | 0 |
| 1002 | 李四 | 2023-03-10 | 2023-03-18 | 1 |
| 1003 | 王五 | 2023-03-01 | 2023-03-20 | 0 |
| 1003 | 王五 | 2023-03-15 | 2023-03-25 | 0 |
| 1003 | 王五 | 2023-03-22 | 2023-03-30 | 0 |
3.第三步：分配组ID (t3)✅✅✅
| emp_id | emp_name | start_date | end_date | group_id |
|---|---|---|---|---|
| 1001 | 张三 | 2023-03-01 | 2023-03-10 | 0 |
| 1001 | 张三 | 2023-03-05 | 2023-03-15 | 0 |
| 1001 | 张三 | 2023-03-20 | 2023-03-25 | 1 |
| 1002 | 李四 | 2023-03-01 | 2023-03-08 | 0 |
| 1002 | 李四 | 2023-03-10 | 2023-03-18 | 1 |
| 1003 | 王五 | 2023-03-01 | 2023-03-20 | 0 |
| 1003 | 王五 | 2023-03-15 | 2023-03-25 | 0 |
| 1003 | 王五 | 2023-03-22 | 2023-03-30 | 0 |
4.第四步：合并区间 (t4)✅✅✅
| emp_id | emp_name | group_id | merged_start | merged_end |
|---|---|---|---|---|
| 1001 | 张三 | 0 | 2023-03-01 | 2023-03-15 |
| 1001 | 张三 | 1 | 2023-03-20 | 2023-03-25 |
| 1002 | 李四 | 0 | 2023-03-01 | 2023-03-08 |
| 1002 | 李四 | 1 | 2023-03-10 | 2023-03-18 |
| 1003 | 王五 | 0 | 2023-03-01 | 2023-03-30 |
5.第五步：最终结果✅✅✅
| emp_id | emp_name | total_days | 计算说明 |
|---|---|---|---|
| 1001 | 张三 | 15 | (3/15-3/1=14)+1=15 + (3/25-3/20=5)+1=6 → 21 |
| 1002 | 李四 | 15 | (3/8-3/1=7)+1=8 + (3/18-3/10=8)+1=9 → 17 |
| 1003 | 王五 | 30 | (3/30-3/1=29)+1=30 |
with
-- 第一步：获取每个员工前一个项目的结束日期
t1 as (selectemp_id,emp_name,start_date,end_date,-- 使用lag窗口函数获取同一个员工前一个项目的结束日期lag(end_date) over(partition by emp_id order by start_date) as prev_endfrom employee_projects
),-- 第二步：判断是否开始新的日期区间（不与前一个区间重叠）
t2 as (selectemp_id,emp_name,start_date,end_date,-- 判断逻辑：如果当前开始日期 > 前一个结束日期+1天，则是新区间casewhen start_date > coalesce(prev_end + interval 1 day, '1900-01-01') then 1else 0end as is_new_groupfrom t1
),-- 第三步：为每个连续的日期区间分配组ID
t3 as (selectemp_id,emp_name,start_date,end_date,-- 使用累加和的方式为连续的日期区间分配相同的组IDsum(is_new_group) over(partition by emp_id order by start_date) as group_idfrom t2
),-- 第四步：合并每个组内的日期区间（取最早开始日期和最晚结束日期）
t4 as (selectemp_id,emp_name,group_id,-- 合并后的开始日期（组内最早的开始日期）min(start_date) as merged_start,-- 合并后的结束日期（组内最晚的结束日期）max(end_date) as merged_endfrom t3group by emp_id, emp_name, group_id
)-- 最终结果：计算每个员工不重叠的项目参与总天数
selectemp_id,emp_name,-- 计算每个合并区间的天数并求和（datediff+1包含首尾两天）sum(datediff(merged_end, merged_start) + 1) as total_days
from t4
group by emp_id, emp_name
order by emp_id;
日期交叉 与 会话划分问题类似 大家可以看看
