mysql疯狂练习——04

 1.请你统计直播开始时（19：00），各科目的在线人数，以上例子的输出结果为（按照course_id升序排序）

drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL,
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);
INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

drop table if exists attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL,
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb VALUES(101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb VALUES(102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb VALUES(104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb VALUES(101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb VALUES(102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb VALUES(104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb VALUES(107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb VALUES(100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb VALUES(102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb VALUES(108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');

-- 请你统计直播开始时（19：00），各科目的在线人数，以上例子的输出结果为（按照course_id升序排序）
select a.course_id,course_name,
    count(1) as online_num
from attend_tb a
join course_tb ct on a.course_id = ct.course_id
where date_format(in_datetime,'%H:%i') <= '19:00:00'
group by a.course_id,course_name
;

-- date_format（） 获取时间函数（差点没想起来怎么拼）

2.请你统计每个科目的平均观看时长（观看时长定义为离开直播间的时间与进入直播间的时间之差，单位是分钟），输出结果按平均观看时长降序排序，结果保留两位小数

drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL,
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);

INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

drop table if exists attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL,
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb VALUES(101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb VALUES(102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb VALUES(104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb VALUES(101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb VALUES(102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb VALUES(104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb VALUES(107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb VALUES(100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb VALUES(102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb VALUES(108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');

-- 请你统计每个科目的平均观看时长
-- （观看时长定义为离开直播间的时间与进入直播间的时间之差，单位是分钟），输出结果按平均观看时长降序排序，结果保留两位小数
select course_name,round(avg(timestampdiff(minute ,in_datetime,out_datetime)),2) as avg_Len
from attend_tb a
join course_tb ct on a.course_id = ct.course_id
group by course_name
order by avg_Len desc
;

3.请你统计每个科目的出勤率（attend_rate(%)，结果保留两位小数）， 出勤率=出勤（在线时长10分钟及以上）人数 / 报名人数，输出结果按course_id升序排序

drop table if exists course_tb;
CREATE TABLE course_tb
(
    course_id       int(10)  NOT NULL,
    course_name     char(10) NOT NULL,
    course_datetime char(30) NOT NULL
);

INSERT INTO course_tb
VALUES (1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb
VALUES (2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb
VALUES (3, 'R', '2021-12-3 19:00-21:00');

drop table if exists behavior_tb;
CREATE TABLE behavior_tb
(
    user_id   int(10) NOT NULL,
    if_vw     int(10) NOT NULL,
    if_fav    int(10) NOT NULL,
    if_sign   int(10) NOT NULL,
    course_id int(10) NOT NULL
);

INSERT INTO behavior_tb
VALUES (100, 1, 1, 1, 1);
INSERT INTO behavior_tb
VALUES (100, 1, 1, 1, 2);
INSERT INTO behavior_tb
VALUES (100, 1, 1, 1, 3);
INSERT INTO behavior_tb
VALUES (101, 1, 1, 1, 1);
INSERT INTO behavior_tb
VALUES (101, 1, 1, 1, 2);
INSERT INTO behavior_tb
VALUES (101, 1, 0, 0, 3);
INSERT INTO behavior_tb
VALUES (102, 1, 1, 1, 1);
INSERT INTO behavior_tb
VALUES (102, 1, 1, 1, 2);
INSERT INTO behavior_tb
VALUES (102, 1, 1, 1, 3);
INSERT INTO behavior_tb
VALUES (103, 1, 1, 0, 1);
INSERT INTO behavior_tb
VALUES (103, 1, 0, 0, 2);
INSERT INTO behavior_tb
VALUES (103, 1, 0, 0, 3);
INSERT INTO behavior_tb
VALUES (104, 1, 1, 1, 1);
INSERT INTO behavior_tb
VALUES (104, 1, 1, 1, 2);
INSERT INTO behavior_tb
VALUES (104, 1, 1, 0, 3);
INSERT INTO behavior_tb
VALUES (105, 1, 0, 0, 1);
INSERT INTO behavior_tb
VALUES (106, 1, 0, 0, 1);
INSERT INTO behavior_tb
VALUES (107, 1, 0, 0, 1);
INSERT INTO behavior_tb
VALUES (107, 1, 1, 1, 2);
INSERT INTO behavior_tb
VALUES (108, 1, 1, 1, 3);

drop table if exists attend_tb;
CREATE TABLE attend_tb
(
    user_id      int(10)  NOT NULL,
    course_id    int(10)  NOT NULL,
    in_datetime  datetime NOT NULL,
    out_datetime datetime NOT NULL
);
INSERT INTO attend_tb
VALUES (100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb
VALUES (100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb
VALUES (101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb
VALUES (102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb
VALUES (104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb
VALUES (101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb
VALUES (102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb
VALUES (104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb
VALUES (107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb
VALUES (100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb
VALUES (102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb
VALUES (108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');

# 请你统计每个科目的出勤率（attend_rate(%)，结果保留两位小数），
#  出勤率=出勤（在线时长10分钟及以上）人数 / 报名人数，输出结果按course_id升序排序
select a.course_id, course_name, round((count(distinct user_id) / cnt1) * 100, 2) as "attend_rate(%)"
from attend_tb a
         join (select distinct course_id, count(distinct user_id) as cnt1
               from behavior_tb
               where if_sign = '1'
               group by course_id) b on a.course_id = b.course_id
         join course_tb ct on a.course_id = ct.course_id
where timestampdiff(minute, in_datetime, out_datetime) >= 10
group by course_id, course_name
order by course_id
;




SELECT course_id, COUNT(DISTINCT user_id) application_cnt
FROM behavior_tb
WHERE if_sign = 1
GROUP BY course_id

4.请你统计每个科目最大同时在线人数（按course_id排序）（同时在线问题）

drop table if exists course_tb;
CREATE TABLE course_tb
(
    course_id       int(10)  NOT NULL,
    course_name     char(10) NOT NULL,
    course_datetime char(30) NOT NULL
);
INSERT INTO course_tb
VALUES (1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb
VALUES (2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb
VALUES (3, 'R', '2021-12-3 19:00-21:00');

drop table if exists attend_tb;
CREATE TABLE attend_tb
(
    user_id      int(10)  NOT NULL,
    course_id    int(10)  NOT NULL,
    in_datetime  datetime NOT NULL,
    out_datetime datetime NOT NULL
);
INSERT INTO attend_tb
VALUES (100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb
VALUES (100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb
VALUES (101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb
VALUES (102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb
VALUES (104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb
VALUES (101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb
VALUES (102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb
VALUES (104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb
VALUES (107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb
VALUES (100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb
VALUES (102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb
VALUES (108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');

-- 请你统计每个科目最大同时在线人数（按course_id排序）
-- 取用户进入直播间，并赋值uv为1；取用户离开直播间，并赋值uv为-1
SELECT course_id, user_id, in_datetime dt, 1 AS uv
FROM attend_tb
UNION ALL
SELECT course_id, user_id, out_datetime dt, -1 AS uv
FROM attend_tb;

-- 使用窗口函数计算直播间的瞬时用户数
SELECT course_id, course_name,SUM(uv) OVER (PARTITION BY course_id ORDER BY dt,uv DESC) uv_cnt
FROM (SELECT course_id, user_id, in_datetime dt, 1 AS uv
      FROM attend_tb
      UNION ALL
      SELECT course_id, user_id, out_datetime dt, -1 AS uv
      FROM attend_tb) uv_tb
         JOIN course_tb USING (course_id);

-- 取各个科目直播间的瞬时最大值并按照course_id排序
SELECT course_id,course_name,MAX(uv_cnt)max_num
FROM(
	SELECT course_id,course_name,SUM(uv)OVER(PARTITION BY course_id ORDER BY dt,uv DESC) uv_cnt
	FROM (SELECT course_id,user_id,in_datetime dt,1 AS uv FROM attend_tb
		UNION ALL
		SELECT course_id,user_id,out_datetime dt,-1 AS uv FROM attend_tb)uv_tb
	JOIN course_tb USING(course_id)
)t1 GROUP BY course_id,course_name ORDER BY course_id;


/*
 求同时在线题目：
 解题思路，分三步走：
首先，取用户进入直播间，并赋值uv为1；取用户离开直播间，并赋值uv为-1；（开始时间赋值为1，结束时间赋值为-1）
然后使用窗口函数计算直播间的瞬时用户数；（sum（赋值）over（）求出每个时间点的人数）
最后，取各个科目直播间的瞬时最大值，并按照course_id排序
 */