大数据面试题——场景题

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

思路：利用窗口函数Lag

详细思路：
1.选出当天访问次数达到100次的用户（即当天有100及以上条数据的用户）：根据用户ID分组，count
2.在每个 用户ID小组内（步骤1已进行分组）按 访问时间进行升序排序
3.计算time-lag(time,100)，若time-lag(time,100)<=5601000(毫秒)，即为满足条件的用户，筛选出。

HQL语句书写
select t.id from{
select
id,
time-lag(time,100,time-5601000-1) over(partition by id order by time) as time_length
from log
group by id
} t
group by id
having min(time_length)<=5601000

注释：time-lag(time,100,time-5601000-1) 中100表示取前100行的数据，若无前100行的数据，则取默认值time-5601000-1。当去默认值time-5601000-1时，time-（time-5601000-1）为5min零1毫秒，大于5min，不满足条件，后续过滤掉。
ps：该HQL只表示大致思路，有优化空间。

拓展思考：该方式为离线分析，若要实时分析，改如何实现？

---

补充知识点：
LAG和LEAD函数
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，
第二个参数为往上第n行（可选，默认为1），
第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，
第二个参数为往下第n行（可选，默认为1），
第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）