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

某公司网站每日访问量达到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 all
    select 
        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_time
    from 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。