等待下一个秋

  • 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年11月4日 2751点热度 0人点赞 0条评论

Hive系列文章

  1. Hive表的基本操作
  2. Hive中的集合数据类型
  3. Hive动态分区详解
  4. hive中orc格式表的数据导入
  5. Java通过jdbc连接hive
  6. 通过HiveServer2访问Hive
  7. SpringBoot连接Hive实现自助取数
  8. hive关联hbase表
  9. Hive udf 使用方法
  10. Hive基于UDF进行文本分词
  11. Hive窗口函数row number的用法
  12. 数据仓库之拉链表

强哥说他发现了财富密码,最近搞了一套股票算法,其中有一点涉及到股票连续涨停天数的计算方法,我们都知道股票周末是不开市的,这里有个断层,需要一点技巧。我问是不是时间序列,他说我瞎扯,我也知道自己是瞎扯。问他方法,他竟然不告诉我,这么多年的兄弟情谊算个屁。真当我没他聪明吗,哼!

靠人不如靠自己,我决定连夜研究一下在Hive里面计算最大连续天数的计算方法。

一、背景

在网站平台类业务需求中用户的「最大登陆天数」,需求比较普遍。

原始数据:

u0001 2019-10-10
u0001 2019-10-11
u0001 2019-10-12
u0001 2019-10-14
u0001 2019-10-15
u0001 2019-10-17
u0001 2019-10-18
u0001 2019-10-19
u0001 2019-10-20
u0002 2019-10-20

说明:数据是简化版,两列分别是user_id,log_in_date。现实情况需要从采集数据经过去重,转换得到以上形式数据。

我们先建表并且将数据导入Hive:

create table test.user_log_1 (user_id string, log_in_date string) row format delimited fields terminated by ' ';

load data local inpath '/var/lib/hadoop-hdfs/data/user_log.txt' into table test.user_log_1 ;

查看一下数据:

hive> select * from test.user_log_1 ;
OK
u0001   2019-10-10
u0001   2019-10-11
u0001   2019-10-12
u0001   2019-10-14
u0001   2019-10-15
u0001   2019-10-17
u0001   2019-10-18
u0001   2019-10-19
u0001   2019-10-20
u0002   2019-10-20
Time taken: 0.076 seconds, Fetched: 10 row(s)

二、算法

核心是按访问时间排序,登陆时间列减去排序后的序列号,得到一个日期值,按这个值分组计数即可。

1. 第一步:排序

按照user_id分组,并且按照日期log_in_date排序:

select user_id, log_in_date, row_number() over(partition by user_id order by log_in_date) as rank from test.user_log_1;

结果:

u0001   2019-10-10  1
u0001   2019-10-11  2
u0001   2019-10-12  3
u0001   2019-10-14  4
u0001   2019-10-15  5
u0001   2019-10-17  6
u0001   2019-10-18  7
u0001   2019-10-19  8
u0001   2019-10-20  9

u0002   2019-10-20  1

这里可以看出,u0001这个用户最大连续登录天数是4天,使用后面计算方法计算后可以验证。

2. 第二步:第二列与第三列做日期差值

可以看出规律,日期小的,行号也小;如果将日期跟行号做差值,连续登录的差值应该是一样的。

select user_id, date_sub(log_in_date, rank) dts from (select user_id, log_in_date, row_number() over(partition by user_id order by log_in_date) as rank from test.user_log_1)m;

结果:

u0001   2019-10-09
u0001   2019-10-09
u0001   2019-10-09
u0001   2019-10-10
u0001   2019-10-10
u0001   2019-10-11
u0001   2019-10-11
u0001   2019-10-11
u0001   2019-10-11
u0002   2019-10-19

显然可以看出,最大连续连续登录是4次。

3. 第三步:按第二列分组求和

select user_id, dts, count(1) num from (select user_id, date_sub(log_in_date, rank) dts from (select user_id, log_in_date, row_number() over(partition by user_id order by log_in_date) as rank from test.user_log_1)m)m2 group by user_id, dts;

结果:

u0001   2019-10-09  3
u0001   2019-10-10  2
u0001   2019-10-11  4
u0002   2019-10-19  1

4. 第四步:求最大次数

已经算出了,每个用户连续登录天数序列,接下取每个用户最大登录天数最大值即可:

select user_id, max(num) from (select user_id, dts, count(1) num from (select user_id, date_sub(log_in_date, rank) dts from (select user_id, log_in_date, row_number() over(partition by user_id order by log_in_date) as rank from test.user_log_1)m)m2 group by user_id, dts)m3 group by user_id;

结果跟我们的预期是一致的,用户u0001最大登录天数是4。

u0001   4
u0002   1

三、扩展(股票最大涨停天数)

我们知道股票市场,比如咱们的A股,周末是不开盘的,那么一只股票如果上周五涨停,本周一接着涨停,这算是连续2天涨停,使用上面这种方法是不行的,使用lead函数试试:

select user_id, log_in_date, lead(log_in_date) over(partition by user_id order by log_in_date) end_date from test.user_log_1;

结果

u0001   2019-10-10  2019-10-11
u0001   2019-10-11  2019-10-12
u0001   2019-10-12  2019-10-14
u0001   2019-10-14  2019-10-15
u0001   2019-10-15  2019-10-17
u0001   2019-10-17  2019-10-18
u0001   2019-10-18  2019-10-19
u0001   2019-10-19  2019-10-20
u0001   2019-10-20  NULL
u0002   2019-10-20  NULL

哈哈,是不是有思路了。

思路:上面结果一共有3列,第一列是uid,通过lead函数,后面两列都是日期,那么两列日期都取值周一到周五之间,也就是说数据里面只有工作日日期,没有周末的数据,可以提前过滤使得数据满足,既然要连续,那么:

  1. 如果第三列的日期,减去第二列的日期,差值等于1,显然是连续的;
  2. 如果第三列的日期,减去第二列的日期,差值等于3,但是第三列日期是星期一,那么也算是连续了;

以上两种条件综合,就能计算出股票的最大连续涨停天数了,你学废了吗。

猜你喜欢
HDFS的快照讲解
Hadoop 数据迁移用法详解
Hbase修复工具Hbck
数仓建模分层理论
一文搞懂Hive的数据存储与压缩
大数据组件重点学习这几个

标签: Hive 大数据
最后更新:2022年8月29日

等待下一个秋

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

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

文章评论

取消回复

等待下一个秋

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

搜一搜
微信
最新 热点 随机
最新 热点 随机
ChatGPT可以做什么 ClickHouse 自定义分区键 ClickHouse数据副本引擎 ClickHouse ReplacingMergeTree引擎 ClickHouse MergeTree引擎 clickhouse简介
基于梯度下降法的——线性回归拟合 HashTable详解 美国总统选举实时结果 美国总统竞选人2020最新数据 接下来的规划 MySQL OR运算符使用实例优先级介绍 结构化数组
标签聚合
算法 mysql Java 挣钱 书籍 大数据 Python 数据仓库 Hive Flink Redis R语言
文章归档
  • 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号