hiveSql 跨N天最大连续统计

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中,可以使用窗口函数来实现7的数据统。具体实现方式如下: 1. 使用lead函数获取当前日期的前6数据: ```sql SELECT lead(date,6,-1) OVER (ORDER BY date) AS previous_days FROM your_table; ``` 2. 使用date_sub函数算7前的日期: ```sql SELECT date_sub(cast(b.date as date), 6) AS start_date, b.date AS end_date FROM your_table b; ``` 请注意,以上代码仅为示例代码,需要根据实际情况进行调整。其中,your_table是你的数据表名称,date是日期字段名称。 引用中提到,如果需要统连续登录7用户个数,可以根据需要将lead函数中的数字进行调整,例如统连续登录n用户个数,将数字6改为n-1,并将date_sub函数中的数字6也改为n-1。 另外,引用中提到,对于7、4周、6个月等数据的统,需要明确取值范围,如包含今或不包含今、包含本周或不包含本周等。根据具体需求,可以调整窗口函数的参数和日期算的方式来满足要求。 同时,还可以使用常用的函数如nvl函数来处理空值。nvl函数的语法是NVL(表达式1, 表达式2),如果表达式1为空,则返回表达式2的值,否则返回表达式1的值。该函数可以用于将空值(null)转换为实际值。 请注意,以上仅为示例答案,具体实现方法还需要根据实际情况进行调整和完善。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *3* [Hive 求7、4周、6个月的数据(1.日期)](https://blog.youkuaiyun.com/weixin_43753599/article/details/124480858)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] - *2* [hive 算连续7登录的用户](https://blog.youkuaiyun.com/chimchim66/article/details/108307054)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

打赏作者

@nanami

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

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

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

打赏作者

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

抵扣说明:

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

余额充值