温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

用HiveSQL计算连续天数问题的方法是什么

发布时间:2021-11-11 16:31:41 来源:亿速云 阅读:580 作者:柒染 栏目:大数据

这期内容当中小编将会给大家带来有关用HiveSQL计算连续天数问题的方法是什么,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。

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

统计今年每个用户最长连续签到的天数;

统计最近一个月连续有回帖超过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。

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

select user_id,max(day_count) as max_day_count from t_cgroup by uidhaving max(day_count) >= 7;

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

  
    
  
  
  select user_id,max(day_count) as max_day_countfrom (  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_cgroup by user_idhaving max(day_count) >= 7;
  
    
  
  
  如果还需要同时得到最大连续天数对应的起始日期怎么办呢?可以将日期计数值存成一张临时表,连续日期最大值存成另一张临时表,然后两表做join就可以得到结果了。

上述就是小编为大家分享的用HiveSQL计算连续天数问题的方法是什么了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注亿速云行业资讯频道。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI