hivesql练习_日期交叉问题

现有各品牌优惠周期表（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 <string> (品牌)	promotion_day_count <int> (优惠天数)
vivo	17
oppo	16
redmi	22
huawei	22

 

解答一：

-- 以时间轴为水平线，如果有重叠日期，则从过去时间段的最大的结束日期截断，然后从截断处再累计天数；如果没有重叠日期，则直接统计每个时间段的天数。

-- 根据品牌分组，根据开始日期升序排序，获取从开始到上次一优惠活动的结束日期最大的日期maxEnd => tt1

-- 如果maxEnd为空,则获取本次活动的开始日期；如果本次活动的开始日期大于maxEnd，则获取本次活动的开始日期；否则，获取maxEnd的下一天；当作本次活动的开始日期。 => tt2

-- 计算本次活动的 活动天数=结束日期-开始日期 => tt3

-- 根据品牌分组，用sum统计每个品牌总的优惠总天数（如果活动天数>0，则+1统计活动的天数，否则统计为0）

with tt1 as(
  SELECT brand, start_date, end_date, max(end_date) over(partition by brand order by start_date rows between unbounded preceding and 1 preceding) maxEnd
  FROM
  promotion_info
), tt2 as(
  SELECT brand, 
  if(maxEnd is null, start_date, if(start_date > maxEnd, start_date, date_add(maxEnd, 1))) start_date,
  end_date
  FROM
  tt1
), tt3 as(
  select brand, datediff(end_date, start_date) days
  from 
  tt2
)
SELECT brand, sum(if(days > 0, days + 1, 0)) promotion_day_count
FROM
tt3
group by brand

 

解答二：

1.end_date的最大值-start_date最小值 = 总活动天数

2.总活动天数 - 期间没有活动的天数 = 实际总活动天数

with tt as(
select
  brand, start_date, end_date, 
  max(end_date) over(PARTITION by brand order by start_date rows BETWEEN UNBOUNDED PRECEDING and 1 PRECEDING) last_end_date, -- 本次活动开始时间之前所有活动中最大的活动结束日期
  max(end_date) over(partition by brand order by end_date desc) end_date_max,
  min(start_date) over(partition by brand order by start_date) start_date_min
from 
promotion_info
),tt1 as(
  SELECT
  brand, 
  -- start_date, end_date, last_end_date,
  datediff(end_date_max, start_date_min) + 1 day_count, -- 总活动天数
  if(datediff(start_date, last_end_date) <= 0, 0, datediff(start_date, last_end_date) - 1) difference --期间没有活动的天数
  FROM
  tt
)
SELECT brand, (max(day_count) - sum(difference)) promotion_day_count
FROM tt1
group by brand

tt表查询结果：