问题:
统计每个科目最大同时在线人数(按course_id排序)
思路:
1.给用户行为打标。进入则为1,出去为-1。
-- 对用户行为打标,创建uv表 with uv as( select user_id, course_id, in_datetime as dt, 1 as flag from attend_tb union all select user_id, course_id, out_datetime as dt, -1 as flag from attend_tb )
- 利用窗口函数对人数进行排序累加,统计每个时刻的在线人数。
elect course_id, course_name, num from( select course_id, -- 实时在线人数 sum(flag) over(partition by course_id order by dt) as num from uv ) t1 left join course_tb using(course_id)
- 选出最大的在线人数,并按科目排序。
-- 对t2分组和排序,筛选最大在线人数, select course_id, course_name, max(num) from t2 group by course_id,course_name order by course_id
- 合并带入结果
with uv as( select user_id, course_id, in_datetime as dt, 1 as flag from attend_tb union all select user_id, course_id, out_datetime as dt, -1 as flag from attend_tb ) select course_id, course_name, max(num) from( select course_id, course_name, num from( select course_id, -- 实时在线人数 sum(flag) over(partition by course_id order by dt) as num from uv) t1 left join course_tb using(course_id) ) t2 group by course_id,course_name order by course_id