SQL求最高在线人数以及最高峰时间段

需求分析

数据为主播ID,sdt表示开播时间，edt表示下播时间。

 

求：

• （1）该平台某一天主播同时在线人数最高为多少？

• （2）出现最高峰的时间段是哪个时间?

• 建表

 注：我用的oracle实现的

DROP TABLE "STARPOWER"."PLAY";
CREATE TABLE "STARPOWER"."PLAY" (
  "ID" VARCHAR2(100 BYTE),
  "SDT" DATE,
  "EDT" DATE
)
TABLESPACE "STARPOWER"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON TABLE "STARPOWER"."PLAY" IS '测试表';

-- ----------------------------
-- Records of PLAY
-- ----------------------------
INSERT INTO "STARPOWER"."PLAY" VALUES ('1001', TO_DATE('2021-06-14 12:12:12', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-06-14 18:12:12', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "STARPOWER"."PLAY" VALUES ('1003', TO_DATE('2021-06-14 13:12:12', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-06-14 16:12:12', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "STARPOWER"."PLAY" VALUES ('1004', TO_DATE('2021-06-14 13:15:12', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-06-14 20:12:12', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "STARPOWER"."PLAY" VALUES ('1002', TO_DATE('2021-06-14 15:12:12', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-06-14 16:12:12', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "STARPOWER"."PLAY" VALUES ('1005', TO_DATE('2021-06-14 15:18:12', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-06-14 20:12:12', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "STARPOWER"."PLAY" VALUES ('1001', TO_DATE('2021-06-14 20:12:12', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-06-14 23:12:12', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "STARPOWER"."PLAY" VALUES ('1006', TO_DATE('2021-06-14 21:12:12', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-06-14 23:15:12', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "STARPOWER"."PLAY" VALUES ('1007', TO_DATE('2021-06-14 22:12:12', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-06-14 23:10:12', 'SYYYY-MM-DD HH24:MI:SS'));

数据分析

本题如果直接从SQL本身很难下手，无从做起，不妨我们换个思路，假定我们拿到的是一条数据，现在用java程序怎么做？其实就是一个累加器的思想（如SPARK的累加器）。首先我们需要将这样一条记录进行拆分，分成不同的记录或数据流进入累加器，然后给每条记录进行标记，如果开播的话该条记录记为1，下播的话记为-1，此时的数据流按照时间顺序依次进入累加器，然后在累加器中进行叠加，其中累计的结果最大时候就是所求的结果。其实本质是利用累加器思想，但进入累加器的数据是按时间排好序的时序流数据（数据进入按时间先后顺序进入）。

上述思路总结如下：

• （1）将数据切分（按起始时间和结束时间）

• （2）数据进行标签，开播的记录为记为1，下播的记录记为-1用于累加

• （2）将数据按时间进行排序

• （3）数据进入累加器进行累加

• （4）获取累加器中当前累加值最大的数值

有了以上思路后，我们将其转换为SQL求解思路。

SELECT
	max_cur_cnt,
	dt AS start_time,
	lead_dt AS end_time 
FROM
	(
	SELECT
		id,
		dt,
		flag,
		cur_cnt,
		max_cur_cnt,
		lead ( dt, 1, dt ) OVER ( ORDER BY dt ) lead_dt 
	FROM
		(
		SELECT
			id,
			dt,
			flag,
			cur_cnt,
			max( cur_cnt ) OVER ( ) AS max_cur_cnt 
		FROM
			(
			SELECT
				id,
				dt,
				flag,
				sum( flag ) OVER ( ORDER BY dt ) AS cur_cnt 
			FROM
				( SELECT id, sdt dt, 1 flag FROM play 
				  UNION 
				  SELECT id, edt dt,- 1 flag FROM play ) t --开播时间和下播时间
			) m --按照时间排序，进入累加器进行累加（按时间排序是累加的关键）sum over
		) n --求出最大在线人数
	) p --lead函数求出在线人数最大的时间段(lead向后取N，lag向前取N)
WHERE
	CUR_CNT = MAX_CUR_CNT

结果如下

参考：https://mp.weixin.qq.com/s/3VYbmT2h5lkTzGr7X86COQ