前情描述
为了方便理解,我们还是以游戏行业来讲述这个案例。在游戏行业中,我们会记录用户每天第一次登录的时间以及退出的时间,比如下面的表格式:
表中有三个字段: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没有速成的路径,唯有结合业务场景多练习才能熟练掌握,同时解题时尝试用不同方式实现,有利于知识的巩固和提高。
一起讨论大数据前沿问题,一起进步!