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

with merge_tb_user_log as(
  select
    artical_id as artical_id,
    in_time as dt,
    1 as flag
  from
    tb_user_log
  where
    artical_id <> 0
  union all
  select
    artical_id as artical_id,
    out_time as dt,
    -1 as flag
  from
    tb_user_log
  where
    artical_id <> 0
),
caculate_merge_tb_user_log as(
  select
    artical_id,
    dt,
    sum(flag) over(
      partition by artical_id
      ORDER BY
        dt,
        flag desc
    ) per_cnt
  from
    merge_tb_user_log
)
SELECT
  artical_id,
  max(per_cnt) as max_uv
FROM
  caculate_merge_tb_user_log
GROUP BY
  artical_id
ORDER BY
  max_uv desc;

-- 开窗函数累计统计（通过order by，子行非总数）

-- 将开始时间和离开时间标记后，作为同一时间序列。

-- 累计统计,求最值