impala练习——对用户的日志数据打上会话内序号

需求

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

-- 第一次会话
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
-- 结果：对用户的日志数据打上会话内序号
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

准备

-- 数据
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
-- HIVE中
-- 使用数据库
use impala_test;
-- 如果表存在删除
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/clicklog.dat' into table user_clicklog;
-- 检查数据加载是否成功
select * from user_clicklog;
-- IMPALA中
-- 全量刷新hive数据库
invalidate metadata;
-- 检查数据
[jarvis02:21000] > select * from user_clicklog;
Query: select * from user_clicklog
+---------+---------------------+
| 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 |
+---------+---------------------+

解答

-- 1. 添加flag
-- flag ：上一条数据时间 减 当前数据时间 大于30分钟的 标记为1 反之为0
select user_id, click_time,
if(nvl((unix_timestamp(click_time) - unix_timestamp(lag(click_time) over(partition by user_id order by click_time)))/60,0)>30,1,0) flag
from user_clicklog;
-- 2. 添加gid
-- gid：将flag逐一相加，成分组id
select user_id, click_time,
sum(flag) over(partition by user_id order by click_time rows between unbounded preceding and current row) gid
from (
	select user_id, click_time,
	if(nvl((unix_timestamp(click_time) - unix_timestamp(lag(click_time) over(partition by user_id order by click_time)))/60,0)>30,1,0) flag
	from user_clicklog
)t1;
-- 3. 根据id和gid 进行分组 添加排序
select user_id, click_time,
row_number() over(partition by user_id,gid order by click_time) rowNum
from (
  select user_id, click_time,
  sum(flag) over(partition by user_id order by click_time rows between unbounded preceding and current row) gid
  from (
    select user_id, click_time,
    if(nvl((unix_timestamp(click_time) - unix_timestamp(lag(click_time) over(partition by user_id order by click_time)))/60,0)>30,1,0) flag
    from user_clicklog
  )t1
)t2;

结果

[jarvis02:21000] > select user_id, click_time,
                 > row_number() over(partition by user_id,gid order by click_time) rowNum
                 > from (
                 >   select user_id, click_time,
                 >   sum(flag) over(partition by user_id order by click_time rows between unbounded preceding and current row) gid
                 >   from (
                 >     select user_id, click_time,
                 >     if(nvl((unix_timestamp(click_time) - unix_timestamp(lag(click_time) over(partition by user_id order by click_time)))/60,0)>30,1,0) flag
                 >     from user_clicklog
                 >   )t1
                 > )t2;
Query: select user_id, click_time,
row_number() over(partition by user_id,gid order by click_time) rowNum
from (
  select user_id, click_time,
  sum(flag) over(partition by user_id order by click_time rows between unbounded preceding and current row) gid
  from (
    select user_id, click_time,
    if(nvl((unix_timestamp(click_time) - unix_timestamp(lag(click_time) over(partition by user_id order by click_time)))/60,0)>30,1,0) flag
    from user_clicklog
  )t1
)t2
+---------+---------------------+--------+
| user_id | click_time          | rownum |
+---------+---------------------+--------+
| 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      |
+---------+---------------------+--------+
Fetched 8 row(s) in 1.27s

备注

-- refresh 后要加表名（未测）
refresh impala_test;
-- 结果

[jarvis02:21000] > refresh impala_test;
Query: refresh impala_test
ERROR: AnalysisException: Table does not exist: impala_test.impala_test

-- 当impala中没有这个表时候无效
refresh impala_test.user_clicklog;

[jarvis02:21000] > refresh impala_test.user_clicklog;
Query: refresh impala_test.user_clicklog
ERROR: AnalysisException: Table does not exist: impala_test.user_clicklog