当前位置：首页 > news >正文

大数据_SQL_5min访问达到100次的用户

news 来源：原创 2024/9/19 14:44:51

某公司网站每日访问量达到10亿级别的访问量，
每次访问记录一条数据，数据包含如下字段：用户ID，访问时间（毫秒级），访问页面。
要求使用hive求出所有在5分钟内访问次数达到100次的用户（求出用户ID即可）

假设存在如下表

table_a

用户id (uid), 访问事件 (visit_time), 访问页面 (page)

解题思路

这道题需要换一个思路求解，不要直接按照题目的思路去做。可以转化为下面这个问题，如果按照时间排序，某条日志之后的第100条日志，与该条日志的时间差在5min之内。

则可以认为5分钟内访问达到100次。

题解

一个简化的例子，5min超过3条，并且认为时间是unix_timestamp(s)

--odps sql 
--********************************************************************--
--author:sam
--create time:2024-08-08 21:31:12
--********************************************************************---- 用户id (uid),  访问事件 (visit_time), 访问页面 (page)with tmpa as (select 1 as uid,1723123977 as visit_time,'home' as page union all select 1 as uid,1723124077 as visit_time,'home2' as page union all select 1 as uid,1723124177 as visit_time,'home2' as page union all select 1 as uid,1723124277 as visit_time,'home2' as page union all select 2 as uid,1723124277 as visit_time,'home2' as page union allselect 2 as uid,1723125277 as visit_time,'home2' as page 
)-- select 
--     uid,
--     visit_time,
--     page,
--     lag(visit_time,3,0) over(partition by uid order by visit_time) as before_3_time
-- from tmpa select uid
from 
(select uid,visit_time,page,lag(visit_time,3,0) over(partition by uid order by visit_time) as before_3_timefrom tmpa 
) t1 
where (visit_time - before_3_time) <= 300
group by uid

函数LAG说明

lag(<expr>[, bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)

命令说明

返回当前行往前（朝分区头部方向）第offset行数据对应的表达式expr的值。表达式expr可以是列、列运算或者函数运算等。

参数说明

expr：必填。待计算返回结果的表达式。
offset：可选。偏移量，BIGINT类型常量，取值大于等于0。值为0时表示当前行，为1时表示前一行，以此类推。默认值为1。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后进行运算。
default：可选。当offset指定的范围越界时的缺省值，常量，默认值为NULL。需要与expr对应的数据类型相同。如果expr非常量，则基于当前行进行求值。
partition_clause及orderby_clause：详情请参见windowing_definition。