题目-打折日期交叉问题
现有各品牌优惠周期表(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 |
现要求统计每个品牌的优惠总天数,若某个品牌在同一天有多个优惠活动,则只按一天计算。期望结果如下:
brand |
promotion_day_count |
---|---|
vivo | 17 |
oppo | 16 |
redmi | 22 |
huawei | 22 |
思路:
redmi 2021-06-05 2021-06-21
redmi 2021-06-09 2021-06-15
redmi 2021-06-17 2021-06-26
1) 将当前行以前的数据中最大的end_date放置到当前行
redmi 2021-06-05 2021-06-21 null
redmi 2021-06-09 2021-06-15 2021-06-21
redmi 2021-06-17 2021-06-26 2021-06-21
2) 比较start_date 与 移动下来的数据, 如果开始时间大, 则不需要操作, 反之则将移动下来的数据加一替换当前行的开始时间
redmi 2021-06-05 2021-06-21
redmi 2021-06-22 2021-06-15
redmi 2021-06-22 2021-06-26
3) 将每行的结束时间减去开始时间
redmi 16
redmi -7
redmi 4
4) 按照品牌分组,计算每条数据加一后的总和
redmi 22
SELECT
brand,
sum(if(subdate>0,subdate+1,0)) as promotion_day_count
FROM
(
SELECT
brand,
datediff(end_date,new_start_date) as subdate
FROM
(
SELECT
brand,
start_date,
end_date,
if(maxedt is NULL,start_date,if(maxedt<start_date,start_date,date_add(maxedt,1))) as new_start_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) maxedt
FROM promotion_info
)t1
)t2
)t3
GROUP by brand
标签:redmi,06,21,22,--,题解,2021,sql,date
From: https://www.cnblogs.com/songxr/p/16950287.html