原链接(我抄的)
https://kpretty.tech/archives/sql4
需求:求直播同时在线人数的最大值
原数据
select * from online_problem;
| online_problem.id | online_problem.stt | online_problem.edt |
+--------------------+----------------------+----------------------+
| 1001 | 2021-06-14 12:12:12 | 2021-06-14 18:12:12 |
| 1003 | 2021-06-14 13:12:12 | 2021-06-14 16:12:12 |
| 1004 | 2021-06-14 13:15:12 | 2021-06-14 20:12:12 |
| 1002 | 2021-06-14 15:12:12 | 2021-06-14 16:12:12 |
| 1005 | 2021-06-14 15:18:12 | 2021-06-14 20:12:12 |
| 1001 | 2021-06-14 20:12:12 | 2021-06-14 23:12:12 |
| 1006 | 2021-06-14 21:12:12 | 2021-06-14 23:15:12 |
| 1007 | 2021-06-14 22:12:12 | 2021-06-14 23:10:12 |
思路(流处理做法)
主播上线就 +1,有主播下线就 -1。那对于上述数据我们可以按上线下线时间拆分同时拓展一个字段,如果是上线就是 1,下线就是 -1,最后按照时间排序按行求和即可找出最大的直播人数
第一步
select id, stt dt, 1 flag
from online_problem t
union
select id, edt dt, -1 flag
from online_problem t;
第二步(按时间排序,累加flag到当前行)
select id,dt,sum(flag) over (order by dt) crs
from (
select id, stt dt, 1 flag
from online_problem t
union
select id, edt dt, -1 flag
from online_problem t
) t1;
第三步(最后求个max就完事了)
select max(crs)
from (
select id, dt, sum(flag) over (order by dt) crs
from (
select id, stt dt, 1 flag
from online_problem t
union
select id, edt dt, -1 flag
from online_problem t
) t1
) t2;
解释
第一个绿色框框里两个用户同时下线,所以5-2等于3;第二个绿色框框里的三个时间相同,是下播、开播、下播,这三个1是这样计算得出来的(2-1+1-1),时间相同的作为一个窗口。