Oracle 统计不同品牌营销天数

一、数据准备

create table marketing(
       brand varchar2(100)
     , startdate date
     , enddate date
);

insert into marketing values('华为',to_date('2018-08-04','yyyy-mm-dd'), to_date('2018-08-05','yyyy-mm-dd'));
insert into marketing values('华为',to_date('2018-08-04','yyyy-mm-dd'), to_date('2020-12-25','yyyy-mm-dd'));
insert into marketing values('小米',to_date('2018-08-15','yyyy-mm-dd'), to_date('2018-08-20','yyyy-mm-dd'));
insert into marketing values('小米',to_date('2020-01-01','yyyy-mm-dd'), to_date('2020-01-05','yyyy-mm-dd'));
insert into marketing values('苹果',to_date('2018-09-01','yyyy-mm-dd'), to_date('2018-09-05','yyyy-mm-dd'));
insert into marketing values('苹果',to_date('2018-09-03','yyyy-mm-dd'), to_date('2018-09-06','yyyy-mm-dd'));
insert into marketing values('苹果',to_date('2018-09-09','yyyy-mm-dd'), to_date('2018-09-15','yyyy-mm-dd'));
commit;

二、实现需求

该表记录了每个品牌的营销活动开始日期以及结束日期，现需要统计出每个品牌的总营销天数。

注意

  1： 苹果第一行数据的营销结束日期比第二行数据的营销开始日期要晚， 这部分有重叠的日期的要去重计算。

  2：苹果第二行数据的营销结束日期和第三行的开始日期不连续，2019-09-07以及2019-09-08 不统计到营销天数中。

三、实现思路

1. 用开窗函数lag(), 增加col_date字段, 取得向前偏移1个单位的结束日期(上一个营销活动的结束日期)

2. case when进行条件筛选:

        2.1 如果col_date为空, 说明当前营销活动为该品牌的首次活动, 直接计算enddate - startdate +1 即可

        2.2 如果当前营销活动开始时间(start_date)小于等于上一个营销活动的结束日期(col_date), 则用end_date减去col_date(不加1, col_date重复)

        2.3 其他情况(else): enddate - startdate +1

3. 对步骤2结果集group by brand, 在sum(营销天数)即为所求;

四、实现代码

select a.brand, sum(a.market_day) matket_day
from(
    select 
    f.brand
    ,case 
        when f.col_date is null then f.enddate - f.startdate +1 
        when f.startdate <= f.col_date then f.enddate - f.col_date
        else f.enddate - f.startdate + 1
    end market_day
    from(
        select t.* 
        ,lag(t.enddate,1,null) over (partition by t.brand order by t.startdate) col_date
        from marketing t
    ) f
) a
group by a.brand;