通过HIVE SQL获取每个用户的最大一连登录时常

样本数据导入：

1. drop table if exists user_login;
2. create table user_login
3. ( `user_id` bigint
4. ,login_date string
5. )
6. ;
9. insert into table user_login
10. values
11. (1,'2025-04-01')
12. ,(1,'2025-04-02')
13. ,(1,'2025-04-03')
14. ,(1,'2025-04-05')
15. ,(1,'2025-04-06')
16. ,(2,'2025-04-01')
17. ,(2,'2025-04-02')
18. ;在这里插入代码片

复制代码

idlogin_date12025-04-0112025-04-0212025-04-0312025-04-0512025-04-0622025-04-0122025-04-02

1. WITH ranked_logins AS (
2.     SELECT
3.         user_id,
4.         login_date,
5.         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
6.     FROM (
7.         SELECT DISTINCT user_id, login_date
8.         FROM user_login
9.     ) AS distinct_logins
10. ),
11. grouped_logins AS (
12.     SELECT
13.         user_id,
14.         login_date,
15.         rn,
16.         DATE_SUB(login_date,rn) AS grp
17.     FROM ranked_logins
18. ),
19. streaks AS (
20.     SELECT
21.         user_id,
22.         grp,
23.         COUNT(*) AS streak_length
24.     FROM grouped_logins
25.     GROUP BY user_id, grp
26. )
27. SELECT
28.     user_id,
29.     MAX(streak_length) AS max_streak
30. FROM streaks
31. GROUP BY user_id;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。