hiveSql 跨N天最大连续统计

hiveSQL面试题三:间隔连续问题 计算每个用户最大的连续登录天数,可以间隔一天,例如:如果用户在1,3,5,6登录游戏,则视为连续登录6天。某游戏公司记录的用户每日登录数据。 阅读详情

hiveSql 跨N天最大连续统计

说明

看到标题可能没太能理解,什么叫跨N天连续?这里解释下:
跨N天连续定义为:登录相隔天数小于N
用跨2天举例(即隔一天也算连续登录)
例如 :
2022-01-01 与 2022-01-02算连续
2022-01-01 与 2022-01-03也算连续(隔了一个2022-01-02 但是也算连续登录)
2022-01-01 与 2022-01-04则不算连续了(隔了2天不算连续了)

需求

有数据如下:
表:tmp_login

id	dt
1001	2021-12-12
1001	2021-12-13
1001	2021-12-14
1001	2021-12-16
1001	2021-12-19
1001	2021-12-20
1001	2021-12-26
1001	2021-12-27
1001	2021-12-29
1002	2021-12-12
1002	2021-12-16
1002	2021-12-17

字段:
id:用户ID
dt:用户登录日期

希望计算每个用户的最大连续登录天数,这里的连续定义为小于2天。即隔一天也算连续登录
基于上述明细数据,期望获得以下数据:

idmax_days
10015
10022

1001:是2021-12-12到2021-12-16号 5天
1002:是2021-12-16到2021-12-17号 2天


到这里可以先思考下用hiveSql怎么实现

分析

对于严格每天登录,这个逻辑来计算最大连续登录天数,很容易想到,将用户登录日期排序,用登录日期减去排序序号,则按照减完之后的日期分组统计天数即可。但是现在隔一天也算连续,这种方式貌似实现不了,减完之后的日期会被分到多组中。所以最关键的点在于怎么把满足上述定义的连续的日期分到同一个组里,然后聚合组内天数即可。容易想到,在给每个用户的登录日期排序后,将相邻的两个日期做差,差值大于2的有一个断点,后续利用这个断点做日期分组。这种思路也叫做重分组思想。

有了上述思路,下面逐步完成sql编写

实现

  • 1.将用户登录日期排序,排序后相邻日期做差值
select
	id,dt,
	lag(dt,1,dt) over(partition by id order by dt) as previous_dt,
	datediff(dt,lag(dt,1,dt) over(partition by id order by dt)) as adjacent_dt_diff
from tmp_login

previous_dt:当前日期的升序排序后前一个日期
adjacent_dt_diff:当前日期 与 升序排序后前一个日期相差的天数
有结果如下:
在这里插入图片描述

  • 2.有相差天数后,可以按照需求定义小于什么差值是连续,这里是小于两天为连续。可以根据adjacent_dt_diff值来定义上述分析中的断点
select 
	id,dt,previous_dt
	,if(adjacent_dt_diff>2,1,0) as flag
from
	(select
		id,dt,
		lag(dt,1,dt) over(partition by id order by dt) as previous_dt,
		datediff(dt,lag(dt,1,dt) over(partition by id order by dt)) as adjacent_dt_diff
	from tmp_login
	) t;

flag:这里即是断点,因为用户可能会有多段满足条件的连续,每段的第一天flag即为1。断点与断点之间的日期即是满足条件的连续日期了。
结果如下:
在这里插入图片描述
上述截图可以看到,用户1001,在2021-12-16 与 2021-12-19之间是超过2天的,即不连续。2021-12-19与2021-12-20有是下一段的连续,flag在2021-12-19这条上是1。

  • 3.上述断点有了之后,怎么讲每个断点间的日期分在同一组呢?这里可以利用开窗sum() over() 累加的方式分组:
select 
	id,dt,previous_dt,adjacent_dt_diff
	,if(adjacent_dt_diff>2,1,0) as flag
    ,sum(if(adjacent_dt_diff>2,1,0)) over (partition by id order by dt) as group_flag
from
	(select
		id,dt,
		lag(dt,1,dt) over(partition by id order by dt) as previous_dt,
		datediff(dt,lag(dt,1,dt) over(partition by id order by dt)) as adjacent_dt_diff
	from tmp_login
	) t;

group_flag:满足条件的分组字段
在这里插入图片描述
上述截图可以看到,每个用户id下满足条件的连续登录日期都按照group_flag分组了,接下来只需要做分组,取组内最大日期减去组内最小日期即可

  • 4.分组聚合,取组内最大最小日期天数差
select 
    id,max(days) as max_days
from
    (select 
        id,group_flag,datediff(max(dt),min(dt)) as days
    from
        (select 
            id,dt,previous_dt,adjacent_dt_diff
            ,if(adjacent_dt_diff>2,1,0) as flag
            ,sum(if(adjacent_dt_diff>2,1,0)) over (partition by id order by dt) as group_flag
        from
            (select
                id,dt,
                lag(dt,1,dt) over(partition by id order by dt) as previous_dt,
                datediff(dt,lag(dt,1,dt) over(partition by id order by dt)) as adjacent_dt_diff
            from tmp_login
            ) t
        ) t1
        group by id,group_flag
    ) t2
group by id

结果如下:
在这里插入图片描述
此处有个小的注意事项,最大日期 - 最小日期后加1。

  • 5.精简sql,完整sql如下:
select 
    id,max(days) as max_days
from
    (select 
        id,flag_sum,datediff(max(dt),min(dt))+1 as days
    from
        (select 
            id,dt,flag
            ,sum(flag) over(partition by id order by dt) as flag_sum
        from
            (select 
                id,dt,
                if(datediff(dt,lag(dt,1,dt) over(partition by id order by dt) ) > 2,1,0) as flag
            from tmp_login
            ) t
        ) t1
    group by id,flag_sum
    ) t2
group by id;

最后

喜欢的点赞、关注、收藏吧~ 感谢支持~~

hive按日期连续统计每天的新增和总量 记录一次统计需求的实现: 需求: 现有表ticket_detail(telphone,name,createddate),记录了人员新增信息,每增加一行,代表有新的人员加入。表ticket_detail示例如下: 现在要统计每天新增的人数,以及每天新增后的人员总数,并且,要去重相同的人员,通过(telphone,name)来唯一标识一个人员。 统计结果 先看统计后的结果: 实现方法 接下来是阐... 阅读详情

相关推荐

hivesql离线统计连续N次周期内发生的问题

窗口函数lead 功能:用于从当前数据中基于当前行的数据向后偏移取值 语法:lead(colName,N,defautValue) colName:取哪一列的值 N:向后偏移N行 defaultValue:如果取不到返回的默认值 我们可以基于用户的登陆信息,找到如下规律: 连续两天登陆 : 用户下次登陆时间 = 本次登陆以后的第二天 连续三天登陆 : 用户下下次登陆时间 = 本次登陆以后的第三天 …… 我们可以对用户ID进行分区,按照登陆时间进行排序,通过lead函数计算出用户下次登陆时间 通过日期函数计算

科学的N次方 612

hive连续问题

hive连续问题

sunyiyuan1213的博客 816

从二体运动到高精度轨道:STK与MATLAB联动的轨道外推算法演进史

本文探讨了轨道外推算法从二体运动到高精度轨道预报(HPOP)的演进历程,重点分析了STK与MATLAB协同仿真环境下的技术实现与应用。通过对比不同算法的精度与适用场景,展示了HPOP在卫星参数优化和轨道设计中的优势,为航天任务规划提供了高效解决方案。

weixin_29082143的博客 702

hive判断周几进行SQL统计数据

网上查询资料如下: hive返回星期几的方法:pmod(datediff('#date#', '2012年任意一个星期日的日期'), 7) 。2012-01-01刚好是星期日,大家可以记忆为: 方法:pmod(datediff('#date#', '2012-01-01'), 7) 返回值:int 说明:1、返回值为“0-6”(“0-6”分别表示“星期日-星期六”);2、需要注意pmod...

Burgess_Lee的博客 4409

PLC作为Modbus TCP客户端

PLC作为Modbus TCP客户端,Modbus Slave作为服务器

soledad_chao的博客 4380

ElasticSearch集群搭建

docker安装es集群

weixin_46562226的博客 2026

SQL如何查询用户的最长连续活跃天数?

SQL如何查询用户的最长连续活跃天数?

qq443967620的博客 1901

28、沉浸式体验技术:概念、类型与应用

本文全面介绍了沉浸式体验技术的概念、类型与应用。从双边滤波器的图像处理原理切入,系统阐述了VR、AR、MR、ER及数字孪生等核心技术的特点与区别,并通过丰富案例展示了其在商业、医疗、教育、旅游、房地产、物流等多个领域的实际应用。文章还分析了技术融合趋势、硬件升级方向、应用场景拓展潜力,同时探讨了数据处理、网络延迟、隐私安全等挑战及应对策略,最后以流程图和对比表格总结技术发展脉络,展望沉浸式技术推动社会进步的广阔前景。

wind6的博客 399

Hive统计连续登录n天的用户登录信息

前言 Hadoop:2.7.7 Hive:2.3.0 本文主要练习如何使用Hive SQL统计练习登录n天的用户登录信息,主要使用窗口函数。 测试用表 表数据: 1 Alise 2020-5-12 09:25:56 2 Alise 2020-5-13 10:25:30 3 Alise 2020-5-15 05:25:05 4 Alise 2020-5-15 15:23:11 5 Alise 2020-5-16 21:06:14 6 Bob 2020-5-12 11:17:48 7 Bob 2020-5

TomAndersen的博客 1981

python实现DoIP基本通信(收发报文)

python实现DoIP基本通信(收发报文)

天赐好车的博客 899

基于深度学习YOLOv10的人脸表情识别系统(附完整资源+PySide6界面+训练代码)

本文介绍了如何基于YOLOv10实现一个人脸表情识别系统。我们从数据集准备、模型训练到PySide6界面实现进行了详细的讲解。通过该系统,用户可以实时检测和分类人脸表情,为情感分析、智能交互等应用场景提供支持。

YOLO 1054

Hive SQL经典面试题:统计连续登陆的三天及以上的用户

Hive SQL经典面试题 最近发现一道大数据面试经常会问的SQL题目:统计连续登录的三天及以上的用户(或者类似的:连续3个月充值会员用户、连续N天购买商品的用户等),下面就来记录一下解题思路。 要求输出格式: +---------+--------+-------------+-------------+--+ | uid | times | start_date | end_date | +---------+--------+-------------+-------------+

努力努力再努力!的博客 1万+

计算机组成原理(第三版)唐朔飞-第五章输入输出系统-课后习题

计算机组成原理(第三版)唐朔飞-第五章输入输出系统-课后习题

weixin_45735391的博客 6万+

连续登录hiveSQL实现

窗口和分析函数的应用 需求:统计连续N天登陆人数 table logindate userid 2019-08-28 00:03:00 tom 2019-08-28 10:00:00 frank 2019-08-28 11:00:00 jack 2019-08-29 00:03:00 tom 2019-08-29 10:00:00 frank 2019-08-30 10:00:00 tom 2019-08-30 12:00:00 jack 统计连续登陆2天的人数:tom、frank 第一种方案:

fengyu_lc的博客 588

多模态论文笔记——BLIP

本文详细介绍这几年AIGC火爆的隐藏功臣,多模态模型:BLIP。

haopinglianlian的博客 2455

ssm框架搭建

这个是一个在idea上成功跑起来的ssm框架项目,保存在这里方便以后使用直接下载

Hive sql实现查询连续n天登录的用户

用个排序的窗口函数即可实现: select distinct user_id from ( select user_id , continue_date , count(login_date) as continue_day_cnt from ( select user_id , login_date ...

chenzhh25的博客 3541

IMSL CNL 7.0.0 (x86-64)Crack

IMSL CNL 7.0.0 (x86-64)Crack intel的算法库破解文件,这是C++版本的破解文件

HiveSQL 语法详解与常用 SQL 写法实战

用于创建、修改和删除数据库和表。HiveSQL 凭借其类 SQL 的语法、强大的批处理能力和与 Hadoop 生态的深度集成,成为大数据离线分析的主流工具之一。掌握其核心语法和常用写法,不仅能高效完成 ETL 任务,还能支撑复杂的报表和数据分析需求。如何使用 DDL 创建和管理表如何编写高效的 SELECT 查询如何利用 JOIN、GROUP BY、窗口函数解决实际问题常见的数据分析模式与优化技巧。

weixin_40266856的博客 1万+

NTscan扫描工具

ntscan是一个扫描器,可以扫描没有密码和弱密码的共享,先到配置文件中填写猜测的用户名和密码,扫描过程中右侧出现的是扫描日志。

计算机控制技术及应用

《计算机控制技术及应用》通过总结作者的科研成果和吸收国内外的先进理论、方法和技术,以计算机控制系统的网络化、开放化、智能化和集成化发展趋势为主线索,主要介绍计算机控制系统的基本概念和计算机控制系统的典型形式,计算机控制系统的数学描述与基本分析方法,以及数字PID控制器、最少拍控制系统、达林算法、状态变量设计法等设计方法及其各种改进算法。重点突出计算机控制系统输入输出通道接口的设计方法和典型控制系统的设计案例与实现方法等。

hiveSQL实现连续问题

hive中连续问题

mrgui008的博客 447

HiveSQL 工作实战总结

记录一些工作中有意思的统计指标,做过一些简化方便大家阅读,记录如有错误,欢迎在评论区提问讨论~

fx67ll的博客 1120

从学生成绩到用户行为:HiveSQL数据分析7大经典案例详解

本文详细解析了HiveSQL数据分析的7大经典案例,涵盖学生成绩统计、用户行为分析、电商场景等多个业务领域。通过实战代码演示,帮助读者掌握HiveSQL的核心技巧,特别适合准备面试的数据从业者提升实战能力。案例包括多维度筛选、连续登录识别、商品TopN排名等典型场景,展现HiveSQL处理海量数据的强大能力。

weixin_29251567的博客 216

HiveSQL、SparkSQL数据倾斜解决思路及示例

数据倾斜常见特征 同一个stage的task中,有个别的task执行时间明显比其他的要长得多,整体stage临近结束但一直卡着很长一段时间。 整体任务数据量不大,经常OOM(内存溢出)。即使通过参数增大了内存,已经比一般的任务要大得多了,而且减少了每个task处理的数据量,依然无济于事。 起因 shuffle阶段key的分布不均,单个task读取数据过多,导致执行时间变长,甚至撑爆内存。 HiveSQL或SparkSQL会引起shuffle的操作:reduce join、group by reduce

SKY_02的博客 1216

【hive】SQL之连续登录

在日常工作进行数据的ETL或者面试时,经常遇到类似的问题,比如"统计连续N天交易额超过100万的店铺"、"统计连续登录天数超过3天的用户"等。对于这类问题,思路基本都是一样的。本文将介绍常用的两种解决方案。如何是连续的应该和需要是一样的,如果有跳变(断层)说明不是连续。以"统计连续登录天数超过3天的用户"为需求。

weixin_50573352的博客 827

hivesql sql如何限制group by uid

我整理的一些关于【数据分析】的项目学习资料(附讲解~~)和大家一起分享、学习一下:https://d.51cto.com/Hpqqk2项目方案:使用 HiveSQL 限制 Group By UID 背景 在大数据分析的过程中,尤其是在使用 HiveSQL 进行数据处理时,经常需要对数据进行分组操作。而在某些场景下,我们希...

weixin_42010161的博客 157

高频sql

hive-高频sql

Axin的博客 2175

HiveSQl 常见的优化手段07

分文件的, 在创建表的时候, 指定分桶字段, 并设置分多少个桶, 在添加数据的时候, hive会根据设置分桶字段, 将数据划分到N个桶(文件)中, 默认情况采用HASH分桶方案 , 分多少个桶, 取决于建表的时候, 设置分桶数量, 分了多少个桶最终翻译的MR也就会运行多少个reduce程序(HIVE的分桶本质上就是MR的分区操作)如何构建一个分桶表呢?create table 表名(字段 类型 ,......) [comment '表的描述信息']

2202_75300139的博客 1519

Hive SQL实现近N周的数据统计查询

需要用HIVE SQL去实现该功能,而HIVE SQL并没有PostgreSQL那样例如通过函数to_char((to_date('202550', 'YYYWW') - INTERVAL '5 weeks'), 'yyyyww'))就可以实现202550和往前5周的202545周的查询(这里的50和45分别表示2025年的50周和45周)。这里会有一个问题,2025年第一周,即202501,往前两周,分别是202452和202451,如果用指定周202501直接减2,得到的并不是202451。

朱季谦 755
上一篇: hiveSql 各时段观看直播人数
下一篇: hiveSql 相互关注问题
@nanami
博客等级 码龄9年 4247粉丝 53原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

@nanami

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值