最近在拉勾网学习大数据,刚听到 hive的时候,还不以为然,觉得就是sql的使用罢了,但是听的久了,才知道,自己的认知有多么low,如果没有老师讲过的开窗函数的复杂使用,可能无法完成这道练习题:,下面我们就来聊聊关于hive延迟时间方法?接下来我们就一起去了解一下吧!

hive延迟时间方法(统计网页浏览时长)

hive延迟时间方法

最近在拉勾网学习大数据,刚听到 hive的时候,还不以为然,觉得就是sql的使用罢了,但是听的久了,才知道,自己的认知有多么low,如果没有老师讲过的开窗函数的复杂使用,可能无法完成这道练习题:

这道练习题的大致内容如下:

表结构:

id 用户ID

dt 浏览的时间

url 浏览网页的URL

样例数据如下: -------------------- ------------------- -------------------- | id| dt| browseid| -------------------- ------------------- -------------------- |934e8bee978a42c7a...|2020-05-28 17:02:00|https://www.lagou...| |934e8bee978a42c7a...|2020-05-28 17:23:00|https://www.lagou...| |934e8bee978a42c7a...|2020-05-28 17:09:00|https://www.lagou...| |934e8bee978a42c7a...|2020-05-28 17:12:00|https://www.lagou...| |934e8bee978a42c7a...|2020-05-28 17:31:00|https://www.lagou...| |934e8bee978a42c7a...|2020-05-28 17:34:00|https://www.lagou...| |934e8bee978a42c7a...|2020-05-28 18:46:00|https://www.lagou...| |934e8bee978a42c7a...|2020-05-28 18:13:00|https://www.lagou...| |934e8bee978a42c7a...|2020-05-28 18:14:00|https://www.lagou...| |934e8bee978a42c7a...|2020-05-28 18:11:00|https://www.lagou...| |934e8bee978a42c7a...|2020-05-28 18:20:00|https://www.lagou...| |934e8bee978a42c7a...|2020-05-28 18:25:00|https://www.lagou...| |934e8bee978a42c7a...|2020-05-28 18:41:00|https://www.lagou...| .................. -------------------- ------------------- -------------------- 现在要的结果是 如果两次浏览之间的间隔超过30分钟,认为是两个不同的浏览时间;再求每个id浏览时⻓、步⻓ 预期结果如下: 934e8bee978a42c7a8dbb4cfa8af0b4f 32.0 6 934e8bee978a42c7a8dbb4cfa8af0b4f 35.0 7 含义就是 用户【934e8bee978a42c7a8dbb4cfa8af0b4f 】一次浏览的时长是32分钟,看了6个网页 用户【934e8bee978a42c7a8dbb4cfa8af0b4f 】另一次浏览的时长是35分钟,看了7个网页。 这里的难点是: 如果两次浏览之间的间隔超过30分钟,认为是两个不同的浏览时间。 那么 如何给 同一个用户下面的数据 进行划分。 第一步:我们需要统计时间差,两次时间的差。这里我们需要使用的lag函数,在一行结果中显示上一行的浏览时间。 然后通过unix_timestamp函数 相减,获取时间差(单位是秒) lag(dt,1,dt) over(partition by id order by dt) as pre_dt, kk=unix_timestamp(dt)-unix_timestamp(pre_dt) -------------------------------- ------------------- ---- --- |id |dt |kk |no | -------------------------------- ------------------- ---- --- |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:02:00|0 |1 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:09:00|420 |2 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:12:00|180 |3 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:23:00|660 |4 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:31:00|480 |5 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:34:00|180 |6 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:11:00|2220|7 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:13:00|120 |8 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:14:00|60 |9 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:20:00|360 |10 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:25:00|300 |11 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:41:00|960 |12 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:46:00|300 |13 | 。。。。。。 -------------------------------- ------------------- ---- --- 通过数据,我们可以看到 no[1-6]是一组,因为这些数据中每次的浏览时间都在30min中。 而no[7-13]也是另外一组,因为这些数据中也控制在每次的浏览时间都在30min中。 而 6 与 7 的浏览时间超过了1800秒,也就是30min。 那么我们人可以知道 应该这么划分,但是如何告诉 系统 我的想法,将1-6 分成一组,将7-13分成另外一组呢。 首先:我们人可以知道 7 就是 第二组的开始,1是第一组的开始,那么,在程序里应该怎么做呢? 建立一个delta字段,对之前的kk做处理,如果 kk >1800,那么kk=0,不然就返回kk -------------------------------- ------------------- ----- --- |id |dt |delta|no | -------------------------------- ------------------- ----- --- |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:02:00|0 |1 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:09:00|420 |2 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:12:00|180 |3 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:23:00|660 |4 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:31:00|480 |5 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:34:00|180 |6 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:11:00|0 |7 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:13:00|120 |8 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:14:00|60 |9 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:20:00|360 |10 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:25:00|300 |11 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:41:00|960 |12 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:46:00|300 |13 | 。。。。。。 这样,我们可以看到每一组的开头的值,判断的标准就是 delta =0 -------------------------------- ------------------- ----- --- |id |dt |delta|no | -------------------------------- ------------------- ----- --- |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 17:02:00|0 |1 | |934e8bee978a42c7a8dbb4cfa8af0b4f|2020-05-28 18:11:00|0 |7 | ..... 我们拿到 这份每一组开头的数据后,需要添加 结束时间,这样每一行的记录中就会记录 id ,开始的时间,结束的时间 我们拿到这份数据去和 之前的原始数据 join,只要通过 dt 判断是否落在 【开始时间,结束时间】 这个区间内, 就可以拿到 这行记录 落在 哪个分区里。 思路就到这里了,完整的代码就不贴啦。。。。 完结撒花。

,