大数据hive篇--时间滑动窗口

在路上的小y

已于 2022-09-27 09:58:22 修改

阅读量901

点赞数

分类专栏：大数据文章标签： hive 大数据数据仓库

于 2022-09-26 14:21:55 首次发布

原文链接：https://blog.csdn.net/king14bhhb/article/details/119006330

版权

大数据专栏收录该内容

33 篇文章 8 订阅

订阅专栏

文章目录

时间滑动计算

需求大致是这样的，我们有一个业务涉及到用户打卡，用户可以一天多次打卡，我们希望计算出7天内打卡8次以上，且打卡时间分布在4天以上的时间

具体解释一下这个需求

用户一天可以打卡多次，所以要求打卡必须分布在4天以上；
7天不是一个自然周，而是某一天和接下来的6天，也就是说时间是是滑动的，窗口大小是7，步长是1，说白了就是窗口计算；

其实说到这里你就想到了窗口函数，虽然这是一个窗口；但是hive却没有相应的窗口函数可以计算，接下来看一下怎么实现这个逻辑

外部调用实现时间循环
我们可以先写这样的一个SQL，就计算每个人在特定时间内是否满足我们的条件，我们先计算出每个人每天的打卡次数，例如这里我们的时间限制是’20210701’ 到’20210707’

select
   union_id,
   to_date(ds,'yyyymmdd') as dt,
   count(1) as cnt
from
  ods_la_daily_record_di
where
  -- 驱动表的时间限制
  ds>='${begin_bizdate}' and ds<= '${end_bizdate}'
group by
  union_id, ds

然后我们再判断这个时间端内，用户的打卡情况是否满足我们的条件

select
  t1.union_id,
  count(1) as '打卡天数',
  sum(t1.cnt) as '打卡次数'
from
(
	select
	   union_id,
	   to_date(ds,'yyyymmdd') as dt,
	   count(1) as cnt
	from
	  ods_la_daily_record_di
	where
	  -- 驱动表的时间限制
	  ds>='${begin_bizdate}' and ds<= '${end_bizdate}'
	group by
	  union_id, ds
) as t1
group by
  t1.union_id
having
  -- 时间分布在4天以上
  count(1)>=4
  -- 打卡次数在8次以上
  and sum(t1.cnt)>=8
;

这样我们就算出来我们需要的数据，接下来我们只需要用其他语言调用这个SQL，传入不同的时间参数就可以了，利用编程语言实现时间的滑动，例如第一次传入’20210701-20210707’ 第二次传入’20210702-20210708’ 以此传入即可。

虽然可以实现，但是不好，因为我们还需要其他语言的调用，其实我们知道在SQL里面的关联其实就是通过循环实现的，那我们即然能通过循环实现这个需求，我们能不能通过关联实现这个需求呢

自关联实现滑动时间窗口
其实我们只要让用户某一天的数据和他接下来的6天的数据关联，然后按照这一天的数据进行汇总然后判断时候满足我们的条件即可，如果满足了条件，那么用户这一天的数据就是满足我们的需求的，也就是说这个用户是满足我们的需求的。

with tmp as(
    -- 每个人每天打卡的次数
	select
	  union_id,
	  to_date(ds,'yyyymmdd') as dt,
	  count(1) as cnt
	from
	  ods_la_daily_record_di
	where
	  -- 驱动表的时间限制
	  ds>='${begin_bizdate}' and ds<= '${end_bizdate}'
	group by
	  union_id, ds
)
select
  t1.union_id
from (
	-- 满足条件的(用户-天)
	select
	  t1.union_id,
	  t1.dt,
	  sum(t2.cnt) as `打卡次数`,
	  count(1) as `打卡天数`
	from tmp as t1
	inner join tmp as t2
	on t1.union_id=t2.union_id
	  and DATEDIFF(t2.dt,t1.dt)>=0 and DATEDIFF(t2.dt,t1.dt)<=6
	group by
	  t1.union_id,t1.dt
	having
	  -- 次数限制
	  sum(t2.cnt)>=8
	  -- 天数限制
	  and count(1)>=4
)
group by
  -- 对用户去重
  t1.union_id
;

这里有一个问题需要注意一下，那就是我们满足条件sum(b.cnt)>=8 and count(1)>=4 的是用户某一天的数据，也就是说我们的维度是union_id - 天，所以我们需要对这个数据按照用户为度进行去重。

扩展基于自然周的的滚动时间窗口计算
我们这里思考一个问题，那就是我们知道很多时候我们的计算其实是围绕着自然周的，虽然我们上面的计算不是自然周，那假设我们如果要求我们的计算是自然周呢，那这个时候我们应该怎么计算呢？

其实我们数仓里有一种很表叫做时间维表，我们利用时间维表可以很方便的计算时间相关的东西，如果你没有的话建议去网上找一份，或者自己生成一份，因为使用起来很方便。

因为这个表的字段很多，这里我们截取了一部分放到这里了，下面我们看一下怎么使用时间维表进行计算。

select
  t1.union_id,
  t2.time_weeknum,
  count(1) as `打卡天数`,
  sum(t1.cnt) as `打卡次数`
from(
	select
	   union_id,
	   to_date(ds,'yyyymmdd') as dt,
	   count(1) as cnt
	from
	  ods_la_daily_record_di
	where
	  -- 驱动表的时间限制
	  ds>='${begin_bizdate}' and ds<= '${end_bizdate}'
	group by
	  union_id, ds
) as t1
left join dim_date_time as t2
on t1.ds = t2.time_date
group by
  t1.union_id,
  --  周的标识
  t2.time_weeknum
having
  -- 时间分布在4天以上
  count(1)>=4
  -- 打卡次数在8次以上
  and sum(t1.cnt)>=8
;