题目分析
1.已知table1
,请用 hivesql
分别实现 table2
和 table3
table1
为用户活跃信息表,其中active_dt
为活跃日期。
usr_id|active_dt
A |20200101
A |20200102
A |20200103
A |20200105
A |20200106
A |20200109
- 请用
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
- 请用
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
- 简单总结一下,对于实际问题的应用,往往是用人单位所期望应聘者拥有的,技术能力其实并不是首要,熟悉业务,能根据业务结合技术提供支撑,才是真正的不变应万变。正如那句很经典的话:技术好的人才,市场上一捞一大把,但是真正懂业务的,却少之又少。 所以希望大家,能够在提升技术硬实力的同时,也不忘积累业务知识!共勉