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

对话

前情描述

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

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

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

图2

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