SQL间隔连续问题

间隔连续问题

1. 数据结构：某游戏公司记录的用户每日登录数据

    表名：game_user

    字段名：id（用户id）、dt（日期）

2. 需求：

① 创建表

② 计算每个用户最大的连续登录天数，可以间隔一天。如：如果一个用户在1,3,5,6登录游戏，则视为连续6天登录。

3. 数据准备：

创建文件game_user.txt

[at@hadoop102 ~]$ vim /opt/module/hive/datas/game_user.txt

1001 2022-05-01 23:21:33

1003 2022-05-02 23:21:33

1002 2022-05-01 23:21:33

1003 2022-05-01 23:21:33

1001 2022-05-03 23:21:33

1003 2022-05-04 23:21:33

1002 2022-05-01 23:21:33

1001 2022-05-05 23:21:33

1001 2022-05-01 23:21:33

1002 2022-05-06 23:21:33

1001 2022-05-06 23:21:33

1001 2022-05-07 23:21:33

4. 答案：

① 创建表

hive(default)>

create table game_user(

        id  bigint,

        dt  string

)

row format delimited 

fields terminated by '\t';

② 计算每个用户最大的连续登录天数，可以间隔一天

select id, datediff(max_dt, min_dt)
from
(
select id, max(dt) as max_dt, min(lag_dt) as min_dt
from
(
    select id, dt, lag_dt, datediff(dt, lag_dt) as date_diff, if(datediff(dt, lag_dt) > 2, 1, 0) as flag 
    from
    (
        select id, dt, 
        lag(dt, 1, '1970-01-01') over (partition by id order by dt asc) as lag_dt
        from (
            select id, date_format(dt, 'yyyy-MM-dd') as dt
            from game_user
        ) tb1
    ) tb2
)tb3

where flag = 0
group by id
) tb4;