ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 应用开发 > IT综合 > 通过MySQL实现在线直播网站源码的周报统计

通过MySQL实现在线直播网站源码的周报统计

IT综合 作者：云豹科技阿星 时间：2021-08-11 17:07:35 0 删除 编辑

 周报统计功能是在线直播网站源码最要的一部分，在线直播网站源码可以通过周报统计，查看每周的直播情况，接下来就让我们看看，怎么通过MySQL实现在线直播网站源码的统计功能。

1. 表设计

由于我这边数据量比较大，对在线直播网站源码的数据按用户分了100个区

CREATE TABLE `l_live_room` (
  `sso_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
  `erp_id` varchar(64) NOT NULL DEFAULT '0',
  `cid` varchar(64) NOT NULL DEFAULT '0',
  `package_id` int(10) unsigned NOT NULL DEFAULT '0',
  `unit_id` int(10) unsigned NOT NULL DEFAULT '0',
  `course_id` int(10) unsigned DEFAULT '0',
  `room_id` varchar(30) NOT NULL,
  `start_time` int(10) unsigned NOT NULL,
  `end_time` int(10) unsigned NOT NULL,
  `live_type` smallint(5) unsigned NOT NULL DEFAULT '0',
  `watch_time` double unsigned NOT NULL DEFAULT '0' COMMENT '直播观看时长',
  `current_position` double unsigned NOT NULL DEFAULT '0' COMMENT '回放观看时长',
  `total_length` double unsigned NOT NULL DEFAULT '0',
  `live_id` int(10) unsigned NOT NULL DEFAULT '0',
  `live_name` varchar(255) DEFAULT NULL,
  `interaction_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '1：一对多 2:一对一  3 其他',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '1 观看直播, 2观看回放, 3观看了直播和回放',
  `get_watch_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '同步watch_time的时间',
  `action_time` varchar(19) NOT NULL COMMENT '用户最近一次操作时间',
  `error` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '1 表示未获取到room_id, 2 表示未查询到',
  `update_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '最后一次写入阿里云日志的时间',
  PRIMARY KEY (`sso_id`,`erp_id`,`cid`,`package_id`,`unit_id`) USING BTREE,
  KEY `room_id` (`room_id`) USING BTREE,
  KEY `status` (`status`,`live_type`,`error`,`get_watch_time`,`update_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
PARTITION BY HASH (sso_id)
(PARTITION p0 ENGINE = InnoDB,
 PARTITION p1 ENGINE = InnoDB,
 # 省略部分按自己需要的分区数自行补全
 PARTITION p98 ENGINE = InnoDB,
 PARTITION p99 ENGINE = InnoDB);

2. 数据示例

在这里插入图片描述

3. 需求及数据说明

 1、获取在线直播网站源码近20周的周报
 2、在线直播网站源码周报结果包含直播观看时长live_watch_time, 回放观看时长playback_duration, 有效学习的课程(直播或回放观看超过50%)course_finish_total, 本周学习的课程总数course_total, 直播总数total, 观看直播的数量live_attendance, 直播有效观看的数量live_effective_attendance及直播总时长live_total_time

3、若观看直播则按直播开始时间start_time 进行分周, 若观看的是回放则按最后一次观看时间action_time进行分周
4、若当前周观看了其它周的直播, 不计在当前周的直播总数中, 但记在本周的学习课程数中
5、原始数据中若同一节课即观看了直播又观看了回放数据存放在一条记录中, 如何区分请查看status字段的说明(这样设计是为了便于统计学习的课程数, 因为在同一周中若即看了直播又看了回放只会算作学习了一节课)

4. sql实现

SELECT 
	start_time, SUBDATE(start_time, -6) AS end_time,
	SUM(LEAST(watch_time, total_time)) AS live_watch_time,
	SUM(current_position) AS playback_duration,
	SUM(IF(status != 0 AND (watch_time / total_time > 0.5 OR current_position / total_length > 0.5), 1, 0)) AS course_finish_total,
	SUM(IF(status != 0, 1, 0)) AS course_total,
	SUM(IF(status != 2, 1, 0)) AS total,
	SUM(IF(status IN (1, 3), 1, 0)) AS live_attendance,
	SUM(IF(watch_time / total_time > 0.5, 1, 0)) AS live_effective_attendance,
	SUM(total_time) AS live_total_time
FROM(
	SELECT 
		IF(status IN (0, 1) OR type=2, start_time, start_time2) AS start_time,
		IF(status=2 OR (status=3 AND type=1 AND start_time != start_time2), 0, watch_time) AS watch_time,
		IF(status=2 OR (status=3 AND type=1 AND start_time != start_time2), 0, total_time) AS total_time,
		IF(status=1 OR type=2, 0, current_position) AS current_position,
		IF(status=1 OR type=2, 0, total_length) AS total_length,
		IF(status=2 AND type=2, 0, status) AS status, 
		type
	FROM(
		SELECT
			LEFT(SUBDATE(FROM_UNIXTIME(start_time),IF(FROM_UNIXTIME(start_time,'%w')=0, 7, FROM_UNIXTIME(start_time,'%w'))-1), 10) AS start_time,
			LEFT(SUBDATE(action_time,IF(DATE_FORMAT(action_time,'%w')=0, 7, DATE_FORMAT(action_time,'%w'))-1), 10) AS start_time2,
			watch_time, end_time - start_time AS total_time, current_position, total_length, status, type
		FROM
			`l_live_room`
		LEFT JOIN
			(SELECT 1 AS type UNION SELECT 2 AS type) AS a
		ON (status IN (0, 1) AND type = 1) OR status IN (2, 3)
		WHERE
			sso_id = 123123 
			AND error != 1
			AND GREATEST(FROM_UNIXTIME(start_time), action_time) >= SUBDATE(CURDATE(),IF(DATE_FORMAT(CURDATE(),'%w')=0, 7, DATE_FORMAT(CURDATE(),'%w'))-1+133)
	) AS t
	WHERE NOT (start_time = start_time2 AND type = 2)
) AS t
WHERE start_time >= SUBDATE(CURDATE(),IF(DATE_FORMAT(CURDATE(),'%w')=0, 7, DATE_FORMAT(CURDATE(),'%w'))-1+133)
	AND start_time <= NOW()
GROUP BY start_time;

5. sql拆分解析

5.1 原始数据处理(子查询1)

SELECT
	# 将直播开始时间转换为其所在周周一的日期
	LEFT(SUBDATE(FROM_UNIXTIME(start_time),IF(FROM_UNIXTIME(start_time,'%w')=0, 7, FROM_UNIXTIME(start_time,'%w'))-1), 10) AS start_time,
	# 将最后一次观看时间转换为其所在周周一的日期
	LEFT(SUBDATE(action_time,IF(DATE_FORMAT(action_time,'%w')=0, 7, DATE_FORMAT(action_time,'%w'))-1), 10) AS start_time2,
	# total_time 直播总时长
	watch_time, end_time - start_time AS total_time, current_position, total_length, status, type
FROM
	`l_live_room`
LEFT JOIN
	# 由于直播和回放可能在不同周的统计中, 所以把所有数据复制一份
	(SELECT 1 AS type UNION SELECT 2 AS type) AS a
# 对于未观看的课程和只观看了直播的课程只保留一条数据, 
ON (status IN (0, 1) AND type = 1) OR status IN (2, 3)
WHERE
	sso_id = 123123 
	AND error != 1
	# 只获取最近20周的数据
	AND GREATEST(FROM_UNIXTIME(start_time), action_time) >= SUBDATE(CURDATE(),IF(DATE_FORMAT(CURDATE(),'%w')=0, 7, DATE_FORMAT(CURDATE(),'%w'))-1+133)

5.2 过滤多余重复数据(子查询2)

SELECT 
	# 若未学习或只观看了直播或(直播和回放不在同一周观看的其中一条)保留直播时间, 否则保留观看回放的时间
	IF(status IN (0, 1) OR type=2, start_time, start_time2) AS start_time,
	# 若直播开始时间不在当前周且在当前周观看了回放, 则将本条记录直播观看时间置为0
	IF(status=2 OR (status=3 AND type=1 AND start_time != start_time2), 0, watch_time) AS watch_time,
	# 若直播开始时间不在当前周且在当前周观看了回放, 则将本条记录直播总时长置为0, 避免影响计算本周直播总时长的计算
	IF(status=2 OR (status=3 AND type=1 AND start_time != start_time2), 0, total_time) AS total_time,
	# 若当前周只观看了直播则将回放信息置为0
	IF(status=1 OR type=2, 0, current_position) AS current_position,
	IF(status=1 OR type=2, 0, total_length) AS total_length,
	# 若只观看了回放且与直播时间不在同一周将其中一条记录(保留直播时间的那一条)status置为0, 后续会将此条记录统计到直播所在周的直播总数中, 且不会被统计到已学习课程中
	IF(status=2 AND type=2, 0, status) AS status, 
	type
FROM( 子查询1 ) AS t
# 若观看直播和回放在同一周则只保留一条数据, 不去重会导致学习课程数统计错误
WHERE NOT (start_time = start_time2 AND type = 2)

5.3 生成报告

SELECT 
	start_time, SUBDATE(start_time, -6) AS end_time, # 周报周期范围
	SUM(LEAST(watch_time, total_time)) AS live_watch_time, # 直播观看总时长
	SUM(current_position) AS playback_duration, # 回放观看总时长
	SUM(IF(status != 0 AND (watch_time / total_time > 0.5 OR current_position / total_length > 0.5), 1, 0)) AS course_finish_total, # 有效学习的课程数
	SUM(IF(status != 0, 1, 0)) AS course_total, # 学习的课程数
	SUM(IF(status != 2, 1, 0)) AS total, # 直播总数
	SUM(IF(status IN (1, 3), 1, 0)) AS live_attendance, # 观看直播的总数
	SUM(IF(watch_time / total_time > 0.5, 1, 0)) AS live_effective_attendance, # 直播有效观看数
	SUM(total_time) AS live_total_time # 直播总时长
FROM( 子查询2 ) AS t
# 限制最近20周的数据, 虽然子查询中有类似的限制, 但因为子查询是直播开始时间或回放观看时间, 所以实际数据可能超出20周的范围
WHERE start_time >= SUBDATE(CURDATE(),IF(DATE_FORMAT(CURDATE(),'%w')=0, 7, DATE_FORMAT(CURDATE(),'%w'))-1+133)
	# 此条件限制未来时间还未观看的直播被统计出来
	AND start_time <= NOW()
GROUP BY start_time

声明：本文由云豹科技转发自p1049990866博客，如有侵权请联系作者删除


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69982461/viewspace-2786473/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Redis持久化对直播卖货系统很重要
下一篇： 怎样实现直播电商源码的自定义视频数据采集推流
请登录后发表评论 登录
全部评论
云豹科技阿星

注册时间：2020-08-24

  • 博文量
    239
  • 访问量
    115598

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593