快捷搜索:  汽车  科技

sql如何求年龄(SQL案例--游戏行业同时在线人数原来这么取...)

sql如何求年龄(SQL案例--游戏行业同时在线人数原来这么取...)2013/1/1 0:272013/1/1 0:25login_timeexit_time206229224471933-l

我们先来看看下面的对话,早上上班开完早会的一个聊天:

sql如何求年龄(SQL案例--游戏行业同时在线人数原来这么取...)(1)

前情描述

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

表一:

user_id

login_time

exit_time

206229224471933-l

2013/1/1 0:25

2013/1/1 0:27

237986483538894-l

2013/1/1 0:40

2013/1/1 0:42

307568672598035-l

2013/1/1 0:55

2013/1/1 0:56

...

...

...

237992837122033-l

2013/1/1 1:39

2013/1/1 23:58

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

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

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我们就得出了一段时间内每天每个小时最大的在线人数,结果如下:

表二:

sql如何求年龄(SQL案例--游戏行业同时在线人数原来这么取...)(2)

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

结果:图一

sql如何求年龄(SQL案例--游戏行业同时在线人数原来这么取...)(3)

星星详细

第一部分:

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了,如下:

表三:

sql如何求年龄(SQL案例--游戏行业同时在线人数原来这么取...)(4)

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

第三部分:

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如何求年龄(SQL案例--游戏行业同时在线人数原来这么取...)(5)

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

猜您喜欢: