用HiveQL计算连续天数问题的方法

昨晚简书服务器可能出了什么bug，文章发布出去都会变成完全空白。没办法，只能断更一天，放在今天发了，大概能达到十万字里程碑了吧。

在日常工作中，可能经常会接到业务方类似这样的需求：

• 统计今年每个用户最长连续签到的天数；
• 统计最近一个月连续有回帖超过5天的话题；
• 统计本季度中连续3天以上单日销量超过100的商品。

这种“连续天数”问题看似简单，但实际上对思维能力和编写复杂SQL语句的能力要求比较高。下面以我们曾经接到的一个需求为例，提出解决办法。

有以下简化的日历记录表：

create table user_calendar_record (
  user_id bigint comment '用户ID',
  event_type int comment '记录类型',
  event_data string comment '记录数据',
  upload_time string comment '上传时间'
  del_status int comment '删除状态'
) partitioned by (
  pt_date string comment '记录（分区）日期'
);

现要找出4月间，每个用户类型为24的记录项。如果有用户连续一周及以上记录该项，说明TA对某方面特别重视，应当重点运营。

编写SQL的思路如下。为了避免过多嵌套，所有步骤中都先用子表表示，最后再合成完成的语句。

1. 以用户ID分组，以记录日期为排序规则，添加一列排名。由于用户每天可以记录不止一次，所以要采用dense_rank()函数，不能用rank()或row_number()。

(
  select user_id,pt_date,
  dense_rank() over(partition by user_id order by pt_date) as date_rank
  from user_calendar_record
  where pt_date >= 20190401 and pt_date <= 20190430
  and event_type = 24 and del_status = 0
) t_a;

2. 在以上添加了排名的表中，用记录日期减去排名列代表的天数，得到另一个日期。该日期实际上就是一个连续日期序列的第一天日期减去一天（读起来有点拗口，但很容易理解），用它来做标记。

(
  select user_id,pt_date,
  date_sub(pt_date, cast(date_rank as int)) as start_point
  from t_a
) t_b;

3. 以上表中的user_id和start_point为分组依据，计算每个连续日期序列的天数值。因为数据量不大，所以这里直接用了distinct。数据量大的话还是应该采用group by来代替distinct。

(
  select user_id,start_point,
  count(distinct pt_date) as day_count
  from t_b
  group by user_id,start_point
) t_c;

4. 最后就可以筛选出天数值最大值>=7的那些记录了。

select user_id,max(day_count) as max_day_count 
from t_c
group by uid
having max(day_count) >= 7;

将上面的4个步骤合起来，就是如下的完整SQL语句了：

select user_id,max(day_count) as max_day_count
from (
  select user_id,start_point,
  count(distinct pt_date) as day_count
  from (
    select user_id,pt_date,
    date_sub(pt_date, cast(date_rank as int)) as start_point
    from (
        select user_id,pt_date,
        dense_rank() over(partition by user_id order by pt_date) as date_rank
        from user_calendar_record
        where pt_date >= 20190401 and pt_date <= 20190430
        and event_type = 24 and del_status = 0
    ) t_a
  ) t_b
  group by user_id,start_point
) t_c
group by user_id
having max(day_count) >= 7;

如果还需要同时得到最大连续天数对应的起始日期怎么办呢？可以将日期计数值存成一张临时表，连续日期最大值存成另一张临时表，然后两表做join就可以得到结果了。SQL语句也就不再赘述。