一段时间内的所有日期

背景: 求活动实际进行天数

一品牌进行多场营销活动,多场活动起止时间上有重合,求该品牌进行活动的天数,如果活动时间有重复,只能算一次 。
使用工具是: hive sql

表结构及假数据

1. 表结构是:

create table if not exists tmp_marketing (
  brand string comment '品牌',
  startdate string comment '开始时间',
  enddate string comment '结束时间'
) comment '品牌活动时间记录';

2. 表中假数据:

brand	startdate	enddate
苹果	2018-09-01	2018-09-05
苹果	2018-09-03	2018-09-06
苹果	2018-09-09	2018-09-15
mi	2018-09-09	2018-09-15
mi	2018-09-01	2018-09-05
mi	2018-09-06	2018-09-08

3. 想要达到的效果

brand	天数
mi	15
苹果	13

方法一

先上代码,配上注释,自行服用哦 ~

with grp_starts as ( -- 该条记录时间与上一条记录对比,标记是否有重复日期
  select brand, startdate, enddate,
  case when startdate > max(enddate) over( partition by brand order by startdate, enddate rows between unbounded preceding and 1 preceding ) 
  then 1 else 0 end grp_start
  from tmp_marketing
)
, grps as (--将有重复部分进行聚合
  select brand, startdate, enddate,
  sum(grp_start) over( partition by brand order by startdate, enddate
    rows between unbounded preceding and current row
  ) grp
  from grp_starts
)
select --按照品牌聚合,天数, 得到结果
brand
,sum(diffdates) as `天数`
from
(--得到有重合部分的最小开始时间和最大结束时间,算出时间差,就是活动跨天数
select brand,grp,
min(startdate) start_date,
max(enddate) end_date,
datediff(max(enddate),min(startdate)) +1 as diffdates
from grps
group by brand, grp)
group by brand;

引子

看到本文标题, 就知道方法一,并不是结束, 那么如果知道每个活动的起止时间经历的所有日期, 去重之后,直接聚合, 不是快速且愉快的得到所需结果, 那么有没有这样的函数呢? 扭头,看到了 sequence
来看看这个用法:

SELECT sequence(start_dt, end_dt, INTERVAL 1 DAY) AS date_list
FROM (SELECT date('2021-01-01') AS start_dt, date('2021-01-05') AS end_dt) date_range

date_list
[2021-01-01, 2021-01-02, 2021-01-03, 2021-01-04, 2021-01-05]

方法二

一样的, show code ,配上注释,自行服用哦 ~

SELECT --聚合每个品牌的营销天数
brand
,count(1) as  `天数`
FROM
(SELECT --爆炸函数,得到每个活动的起止时间经历的所有日期,并且去重
brand
,dt
FROM (SELECT
    brand
    ,date(startdate) AS start_dt
    ,date(enddate) AS end_dt 
    from tmp_marketing ) date_range
LATERAL VIEW EXPLODE(sequence(start_dt, end_dt)) date_view AS dt
GROUP BY brand
,dt ) tt
GROUP BY brand

以上.