⽤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语句也就不再赘述。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论