题解 | #每篇文章同一时刻最大在看人数#

--mySQL

with
    log as (
        select
            uid,
            artical_id,
            in_time as dt,
            1 is_in
        from
            tb_user_log
        where
            artical_id != '0'
            and artical_id != 0
        union
        select
            uid,
            artical_id,
            out_time as dt,
            -1 is_in
        from
            tb_user_log
        where
            artical_id != '0'
            and artical_id != 0
    )
select
    artical_id,
    max(uv) max_uv
from
    (
        select
            artical_id,
            dt,
            sum(is_in) over (
                partition by
                    artical_id
                order by
                    dt asc,
                    is_in desc
            ) uv
        from
            log
    ) t
group by
    1
order by
    2 desc;

--SQL SERVER

with
    log as (
        SELECT
            uid,
            artical_id,
            in_time as dt,
            1 as is_in
        FROM
            tb_user_log
        where
            artical_id <> 0
        union
        SELECT
            uid,
            artical_id,
            out_time as dt,
            -1 as is_in
        FROM
            tb_user_log
        where
            artical_id <> 0
    )
select
    t1.artical_id,
    MAX(t1.is_in_num) as max_uv
from
    (
        select
            t.artical_id,
            t.dt,
            SUM(t.is_in) over (
                partition by
                    t.artical_id
                order by
                    t.dt asc,
                    t.is_in desc
            ) as is_in_num
        from
            log t
    ) t1
group by
    t1.artical_id
order by
    MAX(t1.is_in_num) desc