提示：利用HIve在实际开发中，难免需要计算连续登录N天的用户列表，进行进一步的用户分析或者消息推送触达。因可基于不同场景完成相关的计算，本文利用样本数据完成计算过程的演示。

文章目录

前言一、Hive 是什么？二、N天连续登录用户计算1.样本数据2.实现逻辑 三 计算连续7天的用户列表四 按照连续登录天数分组查看用户分布总结

---

前言

无

---

无

一、Hive 是什么？

具体Hive是什么，能干啥，本文不做过多阐述，具体可以查看 Aapache Hive 的官方用户文档。

二、N天连续登录用户计算

1.样本数据

假设有表：tmp_user_login 分区表记录用户每天的登录信息，有字段user_id,last_day，分区字段为：day.

2.实现逻辑

1： 先获取用户每天的登录信息，并通过row_number() over() 函数登录日期排序。 代码如下：

select user_id, last_day, row_number() over( partition by devid order by last_day) rank_num from tmp_user_login where day between date1 and date2

2: 计算用户不同登录日期之间的天数差。 代码如下：

select userid, last_day, date_sub(last_day,rank_num) as last_num_day from （ select user_id, last_day, row_number() over( partition by devid order by last_day) rank_num from tmp_user_login where day between date1 and date2 ） a

3:对 user_id, last_num_day做分组查询即可获取 代码如下：

select user_id, datediff(max(last_day),min(last_day)) as diff_days, -- 连续登录天数 COLLECT_set(last_day) -- 连续登录天数的有序数组 from （ select userid, last_day, date_sub(last_day,rank_num) as last_num_day from （ select user_id, last_day, row_number() over( partition by devid order by last_day) rank_num from tmp_user_login where day between date1 and date2 ） a ）tmp group by user_id,last_num_day

4 第3步的代码计算时，如原始数据的分区数据过多，可以把上述查询结果写入临时表，以便后续基于此进行多种情况统计。 写临时表代码如下：

drop table if tmp_continue_login_user_status; create temporary table tmp_continue_login_user_status as select user_id, datediff(max(last_day),min(last_day)) as diff_days, -- 连续登录天数 COLLECT_set(last_day) -- 连续登录天数的有序数组 from （ select userid, last_day, date_sub(last_day,rank_num) as last_num_day from （ select user_id, last_day, row_number() over( partition by devid order by last_day) rank_num from tmp_user_login where day between date1 and date2 ） a ）tmp group by user_id,last_num_day

三 计算连续7天的用户列表

计算连续7天的登录用户的代码如下：

select user_id from tmp_continue_login_user_status where diff_days=6

四 按照连续登录天数分组查看用户分布

代码如下：

select diff_days, -- 连续登录天数 users, -- 连续登录天数对应的用户数 (sum(users) over()) as total_users, -- 总体用户数 (sum(devices) over( order by diff_days desc )) as aggregate_users, --小于当前天数的累计用户数 devices/(sum(users) over()) as diff_days_rate, -- 连续登录天数对应的用户占用户用户的比列 (sum(devices) over( order by diff_days desc ))/(sum(users) over()) as aggregate_users_rate -- 小于等于当前天数 连续登录的用户数累计占比 from ( select diff_days, count(distinct user_id) as users from tmp_continue_login_user_status group by diff_days ) tmp order by diff_days desc

总结

Hive SQL 书写并不复杂，只是需要耐心和不断学习的毅力。复杂的SQL 是通过简单SQL慢慢不断调试和添加复杂查询完成的。