我們先來看看下面的對話，早上上班開完早會的一個聊天：

對話

前情描述

為了方便理解，我們還是以游戲行業(yè)來講述這個案例。在游戲行業(yè)中，我們會記錄用戶每天第一次登錄的時間以及退出的時間，比如下面的表格式：
表一：

表中有三個字段：user_id,login_time,exit_time。用戶id:user_id，每天進(jìn)入的時間：login_time，每天退出的時間：exit_time， 每個用戶每天一條記錄。

• 現(xiàn)在為了方便廣告投放，我們想知道每天不同時點(diǎn)最大的在線人數(shù)

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我們就得出了一段時間內(nèi)每天每個小時最大的在線人數(shù)，結(jié)果如下：
表二：

然后可以在Excel中可以使用透視表看這段時間平均每天小時最大的在線量。也可以分不同的月份看每個月的最大量分布。（什么樣的行業(yè)會是在凌晨人多呢？)
結(jié)果：圖一

圖一

星星詳析

第一部分

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

把原始數(shù)據(jù)中的user_id和登錄時間(login_time)取出來，然后給一個index，每個用戶的登錄時都給一個1，然后union all 結(jié)束時間，給結(jié)束時間(exit_time)時一個-1，這樣我們就能在這個子查詢的外層以sum()和開窗函數(shù)(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

利用開窗函數(shù)和sum()一起使用的特性，只要我們不在over中分組(partition by)，那么sum的時候，就是從第一個行到當(dāng)前行的累加，并且以登錄時間升序，這樣，每次遇到登錄時間的時候就能加1，遇到退出時間就能減1了，如下：
表三

表三

這樣我們就得到每個時點(diǎn)的到秒級的當(dāng)前用戶量

第三部分

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)

通過第二部分我們得到秒級的用戶登錄量，然后我們求出每小時最大的值，就是以天和小時進(jìn)行分組，然后取最大值就能得到我們每小時最大在線量了。
當(dāng)然我們也可以直接以天進(jìn)行分組，得到每天的最大量，因?yàn)槲覀円呀?jīng)求到了秒級的最大量了。

寫在最后

當(dāng)我們求出了每天每小時最大的量后，即表2，我在Excel可以看一段時間的平均每個小時最大量，如圖1，我也能對比看不同月份最大量的分布，有些特定行業(yè)在不同的月份可能表現(xiàn)不一樣，如下：
圖2

圖2

數(shù)據(jù)出來后就是大家自己處理了，目的不一樣，角度也不一樣了