连续登陆天数计算

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)

2. 对会员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