大資料面試題——場景題

有如下場景：
某公司網站每日訪問量達到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）