按时间间隔求访问序号

Impala作业题

业务背景

现有收集到用户的页面点击行为日志数据，数据格式如下：

用户id, 点击时间

user_id click_time
A,2020-05-15 01:30:00
A,2020-05-15 01:35:00
A,2020-05-15 02:00:00
A,2020-05-15 03:00:10
A,2020-05-15 03:05:00
B,2020-05-15 02:03:00
B,2020-05-15 02:29:40
B,2020-05-15 04:00:00

业务：

会话概念：用户的一次会话含义是指用户进入系统开始到用户离开算作一次会话，离开或者重新开始一次会话的概念是指用户的两次行为事件差值大于30分钟，

比如以A用户为例：

第一次会话

A,2020-05-15 01:30:00
A,2020-05-15 01:35:00
A,2020-05-15 02:00:00

第二次会话

A,2020-05-15 03:00:10
A,2020-05-15 03:05:00

判断条件是只要两次时间差值大于30分钟就属于两次会话。

需求

对用户的日志数据打上会话内序号，如下

A,2020-05-15 01:30:00,1
A,2020-05-15 01:35:00,2
A,2020-05-15 02:00:00,3
A,2020-05-15 03:00:10,1
A,2020-05-15 03:05:00,2
B,2020-05-15 02:03:00,1
B,2020-05-15 02:29:40,2
B,2020-05-15 04:00:00,1

实现

在Hive中完成数据加载

--创建表
drop table if exists user_clicklog;
create table user_clicklog ( 
        user_id string, 
        click_time string
        )
row format delimited fields terminated by ",";

--加载数据
load data local inpath '/root/impala_data/clicklog.dat' into table user_clicklog; 

使用Impala sql完成指标统计…
思路：
1.计算出当前的时间与上一条记录时间之间分钟差值，按照user_id进行分 组，时间排序
2.当差值大于30时，意味需要进入新的分区，使用groupboolean字段区分，大于30为1需要分区，小于30为0，按照user_id进行分组，时间排序
3.利用对groupboolean求和得到新的分区字段groupfeild，按照user_id进行分组，时间排序
4.用user_id和groupfeild进行分区，使用row_number排序，得到需要的分区内排序

with tmp as (
    select user_id,click_time,(unix_timestamp(click_time)-nvl(unix_timestamp(lag(click_time) over(partition by user_id order by unix_timestamp(click_time))),0))/60 intervals
    from user_clicklog
)

select user_id,click_time,
        row_number() over(partition by user_id,groupfeild order by unix_timestamp(click_time)) ordernumber
    from(
        select user_id,click_time,
                sum(groupboolean) over(partition by user_id order by unix_timestamp(click_time)) groupfeild
            from (
                select user_id,click_time,
                    case when intervals>30 then 1 else 0  end groupboolean
                from tmp
        )tmp1
    )tmp2 order by unix_timestamp(click_time)

impala中执行结果