Hive 面试题 | 连续登陆天数统计

题目分析

1.已知table1，请用 hivesql 分别实现 table2 和 table3
table1 为用户活跃信息表，其中active_dt 为活跃日期。

usr_id|active_dt
A     |20200101 
A     |20200102 
A     |20200103 
A     |20200105 
A     |20200106 
A     |20200109 

1. 请用hivesql 实现 table2（计算用户当月截止每天的活跃天数）

--效果图如下
usr_id|active_dt|active_days
A     |20200101 |1
A     |20200102 |2
A     |20200103 |3
A     |20200105 |4	  
A     |20200106 |5	  
A     |20200109 |6  

2. 请用hivesql 实现 table3（计算用户每天的连续累计活跃天数）

--效果图如下
usr_id|active_dt|active_days
A     |20200101 |1
A     |20200102 |2
A     |20200103 |3
A     |20200105 |1	  
A     |20200106 |2	  
A     |20200109 |1	  

---

• 首先弄清楚考查的函数是什么，这里很明显，需要用到的是时间窗口函数ROW_NUMBER() OVER()
• 其次这里还需要注意一个变化，如何体现连续天数，这是一个比较常见的问题，我们可以以这样的思路去考虑，打标签来区别连续天数和间断的天数，然后进行rank排序即可，话不多说，上代码

SQL

--table2实现sql：
select usr_id
      ,active_dt
      ,row_number() over (partition by usr_id order by active_dt asc) as active_days
 from table1;

--table3实现sql：
select usr_id
      ,active_dt
      ,row_number() over (partition by usr_id ,ind order by active_dt asc) as active_days 
 from (
       select usr_id
             ,active_dt
             ,cast(active_dt as int) - row_number() over (partition by usr_id order by active_dt asc) as ind
        from table1
      ) a;

/*过程解析：
usr_id|active_dt|active_days
A     |20200101 |1
A     |20200102 |2
A     |20200103 |3
A     |20200105 |1	  
A     |20200106 |2	  
A     |20200109 |1	  

usr_id|active_dt|ind
A     |20200101 |20200100
A     |20200102 |20200100
A     |20200103 |20200100
A     |20200105 |20200101
A     |20200106 |20200101
A     |20200109 |20200103

*/

解析与总结

• 可能很多小伙伴看完代码还是有疑惑的地方哈，没关系，我们拆解着来看，一步步读懂代码
• 对于table2 的实现，是比较基础的，也比较简单，这里不做过多赘述，我们重点来看table3 的实现：
  1.这里用到了子查询
  2.对active_dt字段进行了int类型的强制转换
  3.重复使用了时间窗口函数ROW_NUMBER() OVER()，注意这是解题的关键所在，因为重复使用了时间窗口函数，然后进行活跃日期相减，连续的日期会相减出同样的ind，为什么因为相同的usr_id会根据active_dt进行升序排序，然后会出现这样的情况
  
  4.熟悉ROW_NUMBER() OVER()函数的小伙伴们应该知道了吧，第一次的ROW_NUMBER() OVER()排序是1-6排下来的，然后每个数字都跟active_dt进行了相减，所以得到的恰好就是区别天数是否连续的一个标识ind，然后我们再进行一次，就可以得到效果图里想要的数据啦

--最终结果图：
usr_id|active_dt|active_days
A     |20200101 |1
A     |20200102 |2
A     |20200103 |3
A     |20200105 |1	  
A     |20200106 |2	  
A     |20200109 |1	  

---

• 简单总结一下，对于实际问题的应用，往往是用人单位所期望应聘者拥有的，技术能力其实并不是首要，熟悉业务，能根据业务结合技术提供支撑，才是真正的不变应万变。正如那句很经典的话：技术好的人才，市场上一捞一大把，但是真正懂业务的，却少之又少。 所以希望大家，能够在提升技术硬实力的同时，也不忘积累业务知识！共勉