用hql求每天同时最大在线人数

最近划水刷京东面试题，遇到一个看似简单的手写hql，大概题意如下：

给定一个表event_log,字段有date,timestamp，event_name(只有login、logout两种)，user_id。用hql求每天同时最大在线人数

细细琢磨，此题不简单！

难度在于求的是同时在线的人数，而不是每天在线人数。

开搞！

1. 登录服务器，编辑用来测试的数据：

vim /opt/test.txt

2021-02-23      1614211111      a       login
2021-02-23      1614211122      b       login
2021-02-23      1614211123      c       login
2021-02-23      1614211133      a       logout
2021-02-23      1614211134      c       logout
2021-02-23      1614211134      d       login
2021-02-23      1614211100      f       login

• 在hive中建表：

CREATE TABLE event_log(`date` DATE,`timestamp` BIGINT, user_id STRING,event_name STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;

• 加载数据：

load data local inpath "/opt/test.txt" into table event_log;

•  查询是否加载成功：

select * from event_log;

event_log.date  event_log.timestamp     event_log.user_id       event_log.event_name
2021-02-23      1614211111      a       login
2021-02-23      1614211122      b       login
2021-02-23      1614211123      c       login
2021-02-23      1614211133      a       logout
2021-02-23      1614211134      c       logout
2021-02-23      1614211134      d       login
2021-02-23      1614211100      f       login

•  执行代码，得出结果：

SELECT 
    dt,
    MAX(ec) AS maxec
FROM
    (
    SELECT 
        `date` AS `dt`,
        `timestamp` AS tm,
        SUM(CASE WHEN event_name = 'login' THEN 1 ELSE -1 END) over (PARTITION BY `date` ORDER BY `timestamp` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ec 
    FROM 
        event_log
    )tmp
GROUP BY dt;

Spark job[5] finished successfully in 2.03 second(s)
OK
dt      maxec
2021-02-23      4

 思路：

1. 题目给出按照天进行统计，时间粒度为天。利用窗口函数，则PARTITION BY `date`。
2. 记录一个在线人数的marker，登入一次记+1，登出一次-1，求截至某个事件发生时候的marker的代数和，即为事件发生时刻的在线人数。窗口中按照PARTITION中的时间戳进行排序，用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 统计PARTITION 中截至事件发生的时间戳的在线人数。
3. 最后，对数据按照日期分组，求出每天中的最大值。

over！