当前位置：首页 > article >正文

【Hive sql面试题】找出连续活跃3天及以上的用户

article 2024/11/1 7:44:23

表数据如下：

在这里插入图片描述

要求：求出连续活跃三天及以上的用户

在这里插入图片描述

建表语句和插入数据如下：

create table t_useractive(
    uid   string,
    dt    string
);


insert into t_useractive
values('A','2023-10-01 10:10:20'),('A','2023-10-02 10:10:20'),('A','2023-10-03 10:16:20'),('A','2023-10-04 10:16:20'),
      ('B','2023-10-01 12:10:25'),('B','2023-10-03 12:10:25'),('B','2023-10-04 18:10:25'),('B','2023-10-05 18:10:25'),
      ('C','2023-10-01 10:20:20'),('C','2023-10-03 10:20:20'),('C','2023-10-05 12:21:24'),('C','2023-10-06 13:11:24'),
      ('D','2023-10-02 12:10:20'),('D','2023-10-03 12:10:20'),('D','2023-10-05 13:12:29'),('D','2023-10-06 15:10:20');

解题思路和答案

核心：

根据uid和年月日分组之后（目的是排除同一个用户在同一天登录了多次的情况），下一条数据登录时间与本条数据登录时间间隔为一天就代表连续登录

思路：

1、根据id和 substr(dt,1,10) 分组（排除同一个用户在同一天登录了多次的情况）

2、根据 substr(dt,1,10) 进行排名（这里排除同一个用户在同一天登录了多次的情况，所以row_number()、rank() 和 dense_rank() 都可以使用）

3、将日期和排名之后的数字相减（使用 date_sub() ），得到一个新的日期

4、如果这个新的日期出现三次及以上，就代表连续登录三天及以上（根据新的日期分组 count() ）

代码：

with t as (
    select uid,substr(dt,1,10) dt1,row_number() over (partition by uid order by substr(dt,1,10)) day 
    from t_useractive group by uid,substr(dt,1,10)
)
select uid from t group by date_sub(dt1,day),uid having count(*) >= 3;