面试经典sql(大数据)：同时在线人数

前情描述

为了方便理解，我们还是以游戏行业来讲述这个案例。在游戏行业中，我们会记录用户每天第一次登录的时间以及退出的时间，比如下面的表格式：

表中有三个字段：user_id,login_time,exit_time。用户id:user_id，每天进入的时间：login_time，每天退出的时间：exit_time， 每个用户每天一条记录。

+v同q：1594007516 一起讨论技术问题。将我6年大数据一线项目（广告，小说，移动数仓）分享给你。适合刚毕业或参加完培训没有实际工作经验的人员。

现在为了方便广告投放，我们想知道每天不同时点最大的在线人数

SQL

直接上SQL：

select date(login_time) as login_date, hour(login_time) as login_hour, max(online_user_cnt) as online_user_cnt_max from ( select user_id, login_time, sum(index1) over(order by login_time asc ) as online_user_cnt from ( select user_id, login_time, 1 as index1 from login_data union all select user_id, exit_time, -1 as index1 from login_data )a )b group by date(login_time) , hour(login_time)

通过上面的SQL我们就得出了一段时间内每天每个小时最大的在线人数，结果如下：

表二：

然后可以在Excel中可以使用透视表看这段时间平均每天小时最大的在线量。也可以分不同的月份看每个月的最大量分布。（什么样的行业会是在凌晨人多呢？...)

结果：图一

星星详析

第一部分：

select user_id, login_time, 1 as index1 from login_data union all select user_id, exit_time, -1 as index1 from login_data

把原始数据中的user_id和登录时间(login_time)取出来，然后给一个index，每个用户的登录时都给一个1，然后union all 结束时间，给结束时间(exit_time)时一个-1，这样我们就能在这个子查询的外层以sum()和开窗函数(over)配合。

第二部分:

select user_id, login_time, sum(index1) over(order by login_time asc ) as online_user_cnt from ( select user_id, login_time, 1 as index1 from login_data union all select user_id, exit_time, -1 as index1 from login_data )a

利用开窗函数和sum()一起使用的特性，只要我们不在over中分组(partition by)，那么sum的时候，就是从第一个行到当前行的累加，并且以登录时间升序，这样，每次遇到登录时间的时候就能加1，遇到退出时间就能减1了，如下：

表三：

这样我们就得到每个时点的到秒级的当前用户量

第三部分：

select date(login_time) as login_date, hour(login_time) as login_hour, max(online_user_cnt) as online_user_cnt_max from ( select user_id, login_time, sum(index1) over(order by login_time asc ) as online_user_cnt from ( select user_id, login_time, 1 as index1 from login_data union all select user_id, exit_time, -1 as index1 from login_data )a )b group by date(login_time) , hour(login_time)

通过第二部分我们得到秒级的用户登录量，然后我们求出每小时最大的值，就是以天和小时进行分组，然后取最大值就能得到我们每小时最大在线量了。当然我们也可以直接以天进行分组，得到每天的最大量，因为我们已经求到了秒级的最大量了。

写在最后

当我们求出了每天每小时最大的量后，即表2，我在Excel可以看一段时间的平均每个小时最大量，如图1，我也能对比看不同月份最大量的分布，有些特定行业在不同的月份可能表现不一样，如下：

图2：

数据出来后就是大家自己处理了，目的不一样，角度也不一样了

总结

通过以上分析，想必大家以后遇到类似问题应该不会手足无措，SQL没有速成的路径，唯有结合业务场景多练习才能熟练掌握，同时解题时尝试用不同方式实现，有利于知识的巩固和提高。

一起讨论大数据前沿问题，一起进步！