1. 场景:
计算某个会员历史上连续签到、登录、下单、发表评论等的天数
2. 数据源:
数据下载地址： http://pan.baidu.com/s/1o6Hj3ku
2.1 示例数据：
部分数据如下：
1000000368307 2014-03-27 20:02:36
1000000368307 2014-03-27 20:52:51
1000000368307 2014-04-05 08:45:07
1000000368307 2014-04-05 13:08:19
1000000368307 2014-04-05 11:10:09
1000000368307 2014-04-05 18:45:46
1000000368307 2014-04-16 23:47:38
1000001327827 2014-05-04 16:56:13
1000001327827 2014-05-04 08:47:54
1000000368307 2014-05-04 08:51:34
1000000368307 2014-05-04 17:56:25
1000001327827 2014-05-08 16:06:57
2.2 数据导入
create table hive_login_max(
id string,
create_time string
) COMMENT 'hive登陆日志'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';
load data local inpath '/data/tmp/tqc/hive_login_max.txt' overwrite into table tmp.hive_login_max;
3. 算法
1）数据按会员id、登陆天去重
select id, to_date(create_time) dt
from tmp.hive_login_max
group by id, to_date(create_time)
- 对会员id，天按天升序排列
select
t.*,
row_number() over(partition by id order by dt asc) cn
from (
select id,
to_date(create_time) dt
from tmp.hive_login_max
group by id, to_date(create_time)
) t
排序结果：
1000000368307 2014-03-27 1
1000000368307 2014-03-28 2
1000000368307 2014-03-29 3
1000000368307 2014-04-01 4
1000000368307 2014-04-02 5
1000000368307 2014-04-03 6
1000000368307 2014-04-06 7
3）用第二列减去第三列，求一个差值日期，并对差值日期分组计数
select
id
,date_sub(dt,cn) dts
,count(*) dcn
from (
select t.*,
row_number() over(partition by id order by dt asc) cn
from (
select
id,
to_date(create_time) dt
from tmp.hive_login_max
group by id, to_date(create_time)
) t
)s
group by id, date_sub(dt,cn)
计算结果：
1000000368307 2014-03-26 3
1000000368307 2014-03-28 3
1000000368307 2014-05-30 1
4）求最大连续登陆时间
select
id,
max(dcn) cnt
from (
select
id,
date_sub(dt,cn) dts,
count(*) dcn
from (
select
t.*,
row_number() over(partition by id order by dt asc) cn
from (
select
id ,
to_date(create_time) dt
from tmp.hive_login_max
group by id, to_date(create_time)
) t
)s
group by id, date_sub(dt,cn)
)k
group by id;
最终结果：
1000000368307 3
