等待下一个秋

  • Spark
  • Flink
  • Hive
  • 数据仓库
  • 资料文档
  • Java
    • Spring
    • Mybatis
    • SpringBoot
    • 面试题
  • Python
    • Python基础
    • 爬虫
    • Numpy
    • matplotlib
    • Flask
  • 知识星球
  • 副业挣钱
  • 技术杂谈
    • Linux知识
    • Docker
    • Git教程
    • Redis教程
    • mysql
    • 前端
    • R语言
    • 机器学习
  • 其它
    • 关于我
    • 资料下载
    • 收徒弟
专注于Hadoop/Spark/Flink/Hive/数据仓库等
关注公众号:大数据技术派,获取更多学习资料。
  1. 首页
  2. Hive
  3. 正文

Hive实战—时间滑动窗口计算

2021年12月22日 712点热度 0人点赞 0条评论

关注公众号:大数据技术派,回复资料,领取1024G资料。

时间滑动计算

今天遇到一个需求大致是这样的,我们有一个业务涉及到用户打卡,用户可以一天多次打卡,我们希望计算出7天内打卡8次以上,且打卡时间分布在4天以上的时间,当然这只是个例子,我们具体解释一下这个需求

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

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

外部调用实现时间循环

我们可以先写这样的一个SQL,就计算每个人在特定时间内是否满足我们的条件,我们先计算出每个人每天的打卡次数,例如这里我们的时间限制是'20210701' 到'20210707'

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

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

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

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

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

自关联实现滑动时间窗口

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

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

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

扩展基于自然周的的滚动时间窗口计算

我们这里思考一个问题,那就是我们知道很多时候我们的计算其实是围绕着自然周的,虽然我们上面的计算不是自然周,那假设我们如果要求我们的计算是自然周呢,那这个时候我们应该怎么计算呢,其实我们数仓里有一种很表叫做时间维表,我们利用时间维表可以很方便的计算时间相关的东西,如果你没有的话建议去网上找一份,或者自己生成一份,因为使用起来很方便。

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

select
   UNION_ID,time_weeknum,count(1) as 打卡天数, sum(cnt) as 打卡次数
from(
  select
     b.union_id,ds,count(1) as cnt
  from
    ods_la_daily_record_di b
  where
    -- 驱动表的时间限制
    b.ds>='${bizdate}'
    and b.ds<='${bizdate2}'
  group by
    b.union_id,ds
) a
left join
dim_date_time b
on
  a.ds=b.time_date
group by
  --  周的标识
  UNION_ID,time_weeknum
HAVING
  -- 时间分布在4天以上
  count(1)>=4
  -- 打卡次数在8次以上
  and sum(cnt)>=8
;

这里我们就基于每个自然周算出了满足条件的人,当然我们还是要针对用户去重

总结

我们看到自关联其实可以达到滑动的效果,当然不仅仅体现在时间上,就像窗口除了时间窗口还是有基于个数的窗口,我们要在遇到类似问题的时候就可以选择这样的解决方案。

时间维表很重要,可以简化我们的计算,如果没有的话,需要创建一个。

交流群

加我微信:ddxygq,回复加群,我拉你进技术交流群。

猜你喜欢
数仓建模—指标体系
数仓建模—宽表的设计
Spark SQL知识点与实战
Hive计算最大连续登陆天数
Flink计算pv和uv的通用方法

标签: Hive 大数据
最后更新:2022年1月22日

等待下一个秋

待我代码写成,便娶你为妻!专注于Hadoop/Spark/Flink/Hive/数据仓库等,关注公众号:大数据技术派,获取更多学习资料。

打赏 点赞
< 上一篇
下一篇 >

文章评论

取消回复

等待下一个秋

待我代码写成,便娶你为妻!专注于Hadoop/Spark/Flink/Hive/数据仓库等,关注公众号:大数据技术派,获取更多学习资料。

搜一搜
微信
最新 热点 随机
最新 热点 随机
logstash同步mysql数据到elasticsearch Spring IOC 容器源码分析 elasticsearch修改字段类型 curl操作elasticsearch常用命令 Python通过orm操作mysql数据库 Python进程管理——Supervisor
Spring IOC 容器源码分析logstash同步mysql数据到elasticsearch
使用Python作为粘合剂 Matplotlib 使用 LaTeX 渲染文本 一分钟了解蒙特卡洛方法 Spark面试题(八)——Spark的Shuffle配置调优 数仓建模—OneID Spark内核解析
标签聚合
Flink Hive 挣钱 mysql Java R语言 大数据 Python Redis 算法 书籍 数据仓库
文章归档
  • 2022年7月
  • 2022年6月
  • 2022年5月
  • 2022年4月
  • 2022年3月
  • 2022年2月
  • 2022年1月
  • 2021年12月
  • 2021年11月
  • 2021年10月
  • 2021年9月
  • 2021年8月
  • 2021年6月
  • 2021年5月
  • 2021年4月
  • 2021年3月
  • 2021年2月
  • 2021年1月
  • 2020年12月
  • 2020年11月
  • 2020年10月
  • 2020年9月
  • 2020年8月
  • 2020年7月
  • 2020年5月
  • 2020年4月
  • 2020年1月
  • 2019年9月
  • 2019年8月
  • 2019年7月
  • 2019年6月
  • 2019年5月
  • 2019年4月
  • 2019年3月
  • 2019年1月
  • 2018年12月
  • 2017年5月

©2022 ikeguang.com. 保留所有权利。

鄂ICP备2020019097号-1

鄂公网安备 42032202000160号