hiveSql 实现计算用户的最大连续登录天数 & 创建断连后的连续序号

HiveSQL题——用户连续登陆 阅读详情

1.需求一:

计算每个用户在一段时间内的最大连续登录天数

1.1.已有数据

usernamelogintime
A2021-10-01
A2021-10-02
A2021-10-03
A2021-10-05
A2021-10-06
A2021-10-09
B2021-10-01
B2021-10-02
B2021-10-03
B2021-10-04

1.2.期望结果

usernamemax_login_days
A3
B4

1.3.分析

1.连续登陆,就是在连续登陆的期间内,后一天和前一天的差值为1,不能为大于1的值,直到间断。可以设置一列序号,如果是连续的话,这列序号也是会随着日期同步增长的,那么日期减去这个序号,应该都是一个确定的日期。

1.4.实现

select
    username,max(max_login_days) as max_login_days
from
    (select
        username,diff_day,count(1) as max_login_days
    from
        (select
            username,login_time
            ,substr(DATEADD(to_date(login_time,'yyyy-mm-dd'),-irank,'dd'),1,10) as diff_day -- 将用户的登录日期与排序序号做差计算一个日期
        from
            (select
                username,login_time
                ,ROW_NUMBER() OVER(PARTITION BY username ORDER BY login_time) AS irank 
            from tb_user_login
            ) t1
        ) t2
    group by username,diff_day
    ) t3
group by username;

2.需求二

用户浏览产品线连续排序序号,若中间插入其他产品线再出现原产品线则从1开始再计数

2.1.已有数据

typehourusername
ask9liu
ask10liu
blog11liu
d12liu
d13liu
d14liu
ask15liu
d16liu

2.2.期望结果

typehourusernameirank
ask9liu1
ask10liu2
blog11liu1
d12liu1
d13liu2
d14liu3
ask15liu1
d16liu1

2.3.分析

这里只想了仅一个用户情况,可以看到期望结果中该用户在9点到16点分别浏览了多个产品线,按照浏览时间排序连续产品线的序号期望是连续的,被别的产品线打断后再出现则再次从1开始计数。逆向思维想,如果能完成上述排序,则在按照hour排序的前提下,相邻的相同的产品线type能有个相同的值。如果有这样的一个列irank_tmp,再将type和该列一起row_number开窗即可。
例如:

typehourusernameirank_tmp
ask9liu0
ask10liu0
blog11liu1
d12liu2
d13liu2
d14liu2
ask15liu3
d16liu4

2.4.实现方式一

with tmp as (
select 
'ask'as type,9 as pt ,'liu'as user 
union all 
select 
'ask'as type,10 as pt ,'liu'as user 
union all 
select 
'blog'as type,11 as pt ,'liu'as user 
union all 
select 
'd'as type,12 as pt ,'liu'as user 
union all 
select 
'd'as type,13 as pt ,'liu'as user 
union all 
select 
'd'as type,14 as pt ,'liu'as user 
union all 
select 
'ask'as type,15 as pt ,'liu'as user 
union all 
select 
'd'as type,16 as pt ,'liu'as user 
)
,
tmp1 as 
(
    select
        type,pt,user
        ,ROW_NUMBER() OVER(PARTITION BY user ORDER BY pt) AS irank 
    from tmp
)
select
    type,pt,user,irank,
    ROW_NUMBER() OVER(PARTITION BY type,flag ORDER BY flag) AS con_rank 
from(
    select
        a.type,a.pt,a.user,a.irank, case when a.irank is not null and b.irank is null and c.irank is null then a.irank else 0 end as flag
    from tmp1 a
    left join tmp1 b
    on a.irank = b.irank + 1 and a.type = b.type
    left join tmp1 c
    on a.irank = c.irank - 1 and a.type = c.type
) t;

方式一的做法比较麻烦,思路是将数据按照用户的浏览时间排序后记录序号,错位自连接两次,一次往上错位,一次往下错位。用前三行示例:

  1. 按照时间排序后是
typehourusernameirank_tmp
ask9liu1
ask10liu2
blog11liu3
  1. 错位两次后
typehourusernamea.irank_tmpb.irank1c.irank2
ask9liu1\N2
ask10liu21\N
blog11liu3\N\N
  1. 分析错位数据
    只有是没有连续产品线的数据的b.irank1 和 c.irank2是空值,a.irank_tmp不为空。此时只用将这种情况的数赋为原来的时间排序的序号即irank_tmp,其他都为0即可。可得到:
typehourusernameirank_tmpirank
ask9liu10
ask10liu20
blog11liu33
d12liu40
d13liu50
d14liu60
ask15liu77
d16liu88
  1. 此时只要按照type和irank分组做开窗row_number即可获得预期数据形式

2.5.实现方式二

with tmp as (
select 
'ask'as type,9 as pt ,'liu'as user 
union all 
select 
'ask'as type,10 as pt ,'liu'as user 
union all 
select 
'blog'as type,11 as pt ,'liu'as user 
union all 
select 
'd'as type,12 as pt ,'liu'as user 
union all 
select 
'd'as type,13 as pt ,'liu'as user 
union all 
select 
'd'as type,14 as pt ,'liu'as user 
union all 
select 
'ask'as type,15 as pt ,'liu'as user 
union all 
select 
'd'as type,16 as pt ,'liu'as user 
)
,
tmp1 as 
(
    select
        type,pt,user
        ,ROW_NUMBER() OVER(PARTITION BY user ORDER BY pt) AS irank 
    from tmp
)
select
    type,pt,user,
    ROW_NUMBER() OVER(PARTITION BY type,irank1-irank2 ORDER BY irank1-irank2) AS irank 
from(
    select
        type,pt,user
        ,ROW_NUMBER() OVER(PARTITION BY user ORDER BY pt) AS irank1
        ,ROW_NUMBER() OVER(PARTITION BY user,type ORDER BY pt) AS irank2
    from tmp
    ) t;

方式二的做法比较灵巧,思路是将数据按照用户的浏览时间排序后记录序号,再将数据按照type和时间做升序排序记录序号,再将这两个序号做差,即可得到步骤1.3分析中的irank_tmp。示例:

typehourusernameirank_ptirank_typeirank_pt - irank_type
ask9liu110
ask10liu220
blog11liu312
d12liu413
d13liu523
d14liu633
ask15liu734
d16liu844

此时还是将type与这个差值分组做row_number即可得到期望结果

hive,登录日志,计算每个人连续登录的最大天数 代码】hive,登录日志,计算每个人连续登录的最大天数。 阅读详情

相关推荐

Hive-最大连续登录天数、最大连续登录天数的最大登录总次数

目录 基本思路 案例展示 数据准备 需求实现 步骤1: 步骤2: 步骤3: 将步骤1、步骤2、步骤3合并为一整条SQL 基本思路 步骤1:首先拿到原始数据,并对uid和dt分组,顺带求出每个uid在每天登录的总次数并保留,并使每个uid下的dt保持顺序排序并留下排序序号。 步骤2:在步骤1的基础上用dt减去排序序号的天数得到一个时间(sub_dt),因为我们已经按照顺序排序了所以只要得到的时间相同,则表示这两个时间是连续的。接下来只需要对uid和sub_dt进行分组,求coun.

weixin_42937781的博客 1745

hive中连续N天登录问题、topN问题、拉链表实现

hive中连续N天登录、topN问题、拉链表

weixin_44870066的博客 3731

hive案例 SQL题 连续登录的天数 连续命中次数 编号函数 row_numbew rank dense_rank

连续登录的天数 数据如下: name login_date a,2020-11-01 a,2020-11-05 a,2020-11-04 b,2020-11-04 b,2020-11-05 a,2020-11-03 a,2020-11-02 b,2020-11-08 a,2020-11-07 b,2020-11-01 b,2020-11-02 a,2020-11-06 b,2020-11-06 1、创建表,导入数据 create table login( name string , login_date

小丑八怪的博客 755

HIVE实战处理(一)统计用户连续登录天数

1、根据附件创建临时表 drop table temp.tmp_test_room; create table temp.tmp_test_room ( roomid string, pt_month string , pt_day string )ro...

sheep8521的博客 4546

一文理解近红外光谱数据建模步骤的细节(细节知识总结)

上一篇文章,向入门的同志大体介绍了近红外光谱数据建模的内容,依据上文其实已经可以基本完成数据建模的工作,但是很多工作不够细致,本文便是分享其中的一些技术细节。

weixin_46207310的博客 5574

为什么RingMo能成为遥感领域的首个生成式自监督基础模型?

RingMo作为遥感领域首个生成式自监督基础模型,通过创新的图像掩码建模技术解决了传统方法在遥感图像处理中的挑战。该模型利用两百万张多元数据集进行预训练,在小目标检测、场景分类等任务上表现卓越,显著提升了性能指标。RingMo的技术突破为遥感图像解译提供了高效解决方案,降低了标注成本并增强了模型泛化能力。

ll5678的博客 692

hiveSql 跨N天最大连续统计

hiveSql 跨N天最大连续统计

i_mycode的博客 1108

告别懵圈!用CANoe 5640手把手带你跑通SomeIP TC8测试(附脚本思路)

本文详细解析了基于CANoe 5640的SomeIP TC8测试全流程,从环境搭建到结果分析,特别针对车载以太网测试中的SomeIP协议测试难点提供实战指南。通过典型测试用例SOMEIP_TEST_61,手把手教你配置CANoe工程、编写CAPL脚本,并解决常见问题,助力开发者高效完成协议验证。

weixin_30791095的博客 365

HiveSQL求用户连续到店购买天数以及与上次到店购买间隔天数

HSQL-需求:求用户连续到店购买天数以及与上次到店购买间隔天数 Step 1:对原始数据进行聚合/去重 Step 2:对用户进行分组排序 Step 3:日期与序号进行减法运算 Step 4:获取开始连续的日期以及连续天数 Step 5:使用Hive中lead函数 Step 1:对数据进行聚合/去重 重点是理解解题思路 1.对数据按天进行初步聚合或去重(因为一个用户可能在某天有多次购买行为) select id,order_date from TABLE_NAME where d='10' group by

是故事啊~关注我~ 1927

Mixly米思齐触碰点亮RGB灯

通过Mixly米思齐实现触碰点亮RGB灯,并且实现红绿蓝灯循环切换 一、硬件准备 arduino uno R3 、USB 数据线、3公触摸开关、4公RGB灯、模块连接线若干条 二、实物接线 三、利用米思齐进行可视化编程 四、相关代码 volatile int B; volatile int G; volatile int R; volatile int times; void setup()...

weixin_45247087的博客 6988

QGIS v3.28 高效加载OSM在线地图的三种实用方法

本文详细介绍了在QGIS v3.28中高效加载OSM在线地图的三种实用方法:通过XYZ Tiles直接连接、使用QuickMapServices插件以及配置WMS/WTMS服务。针对每种方法提供了具体操作步骤、常见问题解决方案和性能优化建议,帮助用户根据需求选择最适合的方案,提升GIS工作效率。

weixin_29233333的博客 436

HiveSQL实战:5个高频面试题解析与优化技巧(附完整代码)

本文深入解析HiveSQL实战中的5个高频面试题,包括全科优等生筛选、连续登录问题、行列转换、留存分析和TopN问题,提供完整代码和优化技巧。通过极值判定法、窗口函数、CASE WHEN与LATERAL VIEW等核心技术的应用,帮助读者掌握HiveSQL的高效查询与性能优化,提升数据开发面试竞争力。

weixin_30687051的博客 263

Windows下用Anaconda安装d2l库避坑指南(附PermissionError解决方案)

本文详细介绍了在Windows系统下使用Anaconda安装d2l库的完整指南,特别针对常见的PermissionError问题提供了多种解决方案。从环境准备到库安装,再到VSCode配置和数据路径处理,帮助读者顺利搭建深度学习环境并运行《动手学深度学习》的代码示例。

3a9bq4r8t2y的博客 226

(课堂笔记)SQL 临时表、视图、正则表达式(补充:真正的临时表)

本文总结了SQL中临时表(CTE)、视图和正则表达式的核心知识点。临时表部分重点讲解WITH子句的编写规范、使用优势(逻辑拆分、提高可读性)和连续登录天数计算案例,强调引用顺序和必须包含最终SELECT的注意事项。视图部分对比普通视图与物化视图的存储特性,说明视图在封装逻辑和安全性方面的作用。正则表达式部分详解Oracle四大核心函数(REGEXP_LIKE/SUBSTR/REPLACE/INSTR)的用法,包含字符串匹配、截取、替换和位置查找的典型示例,特别强调正则模式符号的应用技巧。

木木的学习笔记 416

使用MATLAB和Simulink来设计并仿真一个智能家居火灾报警系统

智能家居火灾报警系统旨在通过烟雾传感器、温度传感器等设备监测家庭环境,一旦检测到异常情况(如烟雾或温度骤升),立即触发警报并采取相应措施。在这个教程中,我们将使用MATLAB和Simulink来设计并仿真一个智能家居火灾报警系统,包括数据采集、处理以及响应机制。

amy_mhd的博客 163

VIVADO IP核之FIR插值器多相滤波仿真

网络上有许多文章介绍FIR低通滤波器的使用,包括仿真。关于FIR低通滤波器的使用,我之前的文章已经介绍过了,本文将继续深入介绍FIR插值器多相滤波的使用方法,并将FIR插值多相滤波的结果与MATLAB仿真计算的结果比较,验证了FIR插值器多相滤波使用正确。

m0_66360845的博客 3385

游戏行业BI工程师校招笔试题全解析:从SQL取数到业务分析框架

在数据驱动业务决策的时代,数据分析已成为企业精细化运营的核心能力。BI工程师作为连接数据与业务的桥梁,不仅需要扎实的SQL功底和数据仓库知识,更要具备将复杂数据转化为业务洞察的结构化思维。以游戏行业为例,用户生命周期管理、留存分析与付费转化评估等场景,对分析师的归因能力和业务理解提出了更高要求。从搜狐畅游等大厂的校招笔试题入手,可以窥见这类岗位的真实能力模型:SQL取数只是入场券,真正的分水岭在于能否通过假设驱动、用户分层、对比基线等方法,从玩家行为数据中定位问题并输出可执行的建议。本文系统拆解游戏行业BI

weixin_30595035的博客 342

IMSL CNL 7.0.0 (x86-64)Crack

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

Hive SQL:用户最长连续登录天数

3.通过聚合函数max()获取每个uid最大的登录天数;

sinat_26269257的博客 664

精雕软件(JDpaint) 5.5 绿色版

精雕软件(JDpaint) 5.5 绿色版精雕软件(JDpaint) 5.5 绿色版精雕软件(JDpaint) 5.5 绿色版

文档:亲测可用解决vnc灰屏,黑屏,鼠标大黑叉情况

主要解决vnc-viewer灰屏,黑屏,鼠标大黑叉等现象,详细见文档。

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。现实情况需要从

zjjcchina的博客 2714

数学建模模型总结

这是本人在两年内准备参加数学建模竞赛搜集到的模型资料,已经经过了本人的筛选和整理,希望能够对准备参加数学建模竞赛的学弟学妹们提供一些帮助!

hive表中连续N天问题的实现

用hive的开窗函数实现连续n天登录问题实现

weixin_44870066的博客 3289

Hive sql : 查询连续登录天数

查询连续登录天数1.问题描述2.在Hive中建表3.查询最大连续登录天数 1.问题描述 目前有两列数据,分别是用户ID和用户登录的时间,现需要统计用户连续登录的最大天数,中间如有断开,则不算连续,如下图示例。 2.在Hive中建表 在Hive中进行操作,首先启动Hadoop集群环境,进入到Hadoop安装目录, sbin/start-dfs.sh ,jps查看Hadoop集群有无正常启动 正常启动后,输入hive启动hive。使用之前建立的test数据库,没有可以自行创建一个数据库,在test数据库下新

NotFound_error的博客 2321

hive/hql/sql 计算最长连续登录天数

hivesql最长连续登录天数

向北的博客 2667

一场pandas与SQL的巅峰大战(二)

点击上方“超哥的杂货铺”,轻松关注工作中除了MySQL,也经常会使用Hive SQL,相比之下,后者有更为强大和丰富的函数。本文将延续上一篇文章的风格和思路,继续对比Pandas与SQL,一方面是对上文的补充,另一方面也继续深入学习一下两种工具。方便起见,本文采用hive环境运行SQL,使用jupyter lab运行pandas。在公众号后台回复“对比二”可以获取本文的PDF版本以及全部的数据和代...

hitchenghengchao的博客 830

Hive SQL经典面试题之间隔连续登录问题

间隔连续登录问题

m0_46503302的博客 1237

Hive窗口函数应用案例1:连续N天登录用户统计

方案一实现如下: -- 连续登录统计 /* login.txt A,2021-03-22 B,2021-03-22 C,2021-03-22 A,2021-03-23 C,2021-03-23 A,2021-03-24 B,2021-03-24 */ create table login ( user_id string, login_time string ) row format delimited fields terminated by ','; load data ..

m0_58535137的博客 755

hive之连续登录问题

目录1、开窗函数的格式2、窗口范围图例连续登录问题:限制时间段内登录次数问题: 1、开窗函数的格式 FUNCTION_NAME([argument_list]) OVER ( [PARTITION BY window_partition,…] [ORDER BY window_ordering, … [ASC|DESC]]) [ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] ); FUNCTION_NAME:函数名称。如row_numbe

weixin_43597208的博客 5004
上一篇: 2021-06-06 每日一题(整数反转)
下一篇: 常用类及方法(持续更新)
@nanami
博客等级 码龄9年 4247粉丝 53原创
评论 3
成就一亿技术人!
拼手气红包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、付费专栏及课程。

余额充值