如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期

tiang_chc

已于 2024-07-16 17:26:28 修改

阅读量547

点赞数 7

分类专栏： mysql 文章标签：数据库

于 2024-07-16 17:24:46 首次发布

本文链接：https://blog.csdn.net/weixin_42698221/article/details/140469668

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

题目：如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期
INSERT INTO test (brand, start_date,end_date) VALUES
('oppo','2021-06-05','2021-06-09'),
('oppo','2021-06-11','2021-06-21'),
('vivo','2021-06-05','2021-06-15'),
('vivo','2021-06-09','2021-06-21'),
('redmi','2021-06-05','2021-06-21'),
('redmi','2021-06-09','2021-06-15'),
('redmi','2021-06-17','2021-06-26'),
('huawei','2021-06-05','2021-06-26'),
('huawei','2021-06-09','2021-06-15'),
('huawei','2021-06-17','2021-06-21'),
('huawei','2021-06-25','2021-06-28'),
('huawei','2021-06-27','2021-06-30');

计算每个品牌总的打折销售天数，注意其中的交叉日期，比如 vivo 品牌，第一次活动时间为 2021-06-05 到 2021-06-15，
第二次活动时间为 2021-06-09 到 2021-06-21 其中 9 号到 15 号为重复天数，只统计一次，即 vivo 总打折天数为 2021-06-05 到 2021-06-21 共计 17 天。

1.使用窗口函数将窗口分区第一行到当前行的前一行获得max_end_date

2.若max_end_date>start_date,则说明存在区间交叉，应将start_date改为max_end_date+1

3.过滤出end_date大于start_date_new 的数据，并计算两个日期之间的差值+1天

4.计算最终结果，求和day_c

附代码及测试数据

CREATE TABLE test (  
    brand VARCHAR(50),  
    start_date DATE,
	  end_date DATE
);

INSERT INTO test (brand, start_date,end_date) VALUES  
('oppo','2021-06-05','2021-06-09'),
('oppo','2021-06-11','2021-06-21'),
('vivo','2021-06-05','2021-06-15'),
('vivo','2021-06-09','2021-06-21'),
('redmi','2021-06-05','2021-06-21'),
('redmi','2021-06-09','2021-06-15'),
('redmi','2021-06-17','2021-06-26'),
('huawei','2021-06-05','2021-06-26'),
('huawei','2021-06-09','2021-06-15'),
('huawei','2021-06-17','2021-06-21'),
('huawei','2021-06-25','2021-06-28'),
('huawei','2021-06-27','2021-06-30');

select brand,
sum(day_c) day_count
from
(select
    brand,
		start_date,
		end_date,
		start_date_new,
    datediff(end_date,start_date_new) + 1 as day_c
from(
    select
        brand,
				start_date,
				end_date,
			  max_end_date,
				DATE_ADD(max_end_date,INTERVAL 1 day),
        if((max_end_date is null) or (start_date > max_end_date),start_date,DATE_ADD(max_end_date,INTERVAL 1 day)) as start_date_new
    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_end_date
        from test
        ) t1     -- 使用窗口函数将窗口分区第一行到当前行的前一行获得max_end_date
    )t2   -- 若max_end_date>start_date,则说明存在区间交叉，应将start_date改为max_end_date+1
where end_date > start_date_new ) t3
group by brand