等待下一个秋

  • Spark
  • Flink
  • Hive
  • 数据仓库
  • ClickHouse
  • 收徒弟
  • 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日 3579点热度 0人点赞 0条评论

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

[TOC]

时间滑动计算

今天遇到一个需求大致是这样的,我们有一个业务涉及到用户打卡,用户可以一天多次打卡,我们希望计算出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 大数据
最后更新:2023年2月9日

等待下一个秋

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

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

文章评论

取消回复

等待下一个秋

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

搜一搜
微信
最新 热点 随机
最新 热点 随机
ChatGPT可以做什么 ClickHouse 自定义分区键 ClickHouse数据副本引擎 ClickHouse ReplacingMergeTree引擎 ClickHouse MergeTree引擎 clickhouse简介
如何扩展NumPy 我有故事,你有酒吗? 黑客与画家 9.设计者的品味 数仓建模—宽表的设计 wordpress安装markdown插件——WP Githuber MD – WordPress Markdown Editor 使用NumPy的C-API
标签聚合
挣钱 Redis 大数据 书籍 数据仓库 Flink Java 算法 Python Hive R语言 mysql
文章归档
  • 2023年2月
  • 2022年12月
  • 2022年11月
  • 2022年9月
  • 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号