mysql实战系列--直播(含回放)周报统计

作者:神秘网友 发布时间:2020-10-23 23:45:02

mysql实战系列--直播(含回放)周报统计

mysql实战系列--直播(含回放)周报统计

文章目录

  • 1. 表设计
  • 2. 数据示例
  • 3. 需求及数据说明
  • 4. sql实现
  • 5. sql拆分解析
    • 5.1 原始数据处理(子查询1)
    • 5.2 过滤多余重复数据(子查询2)
    • 5.3 生成报告

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. 数据示例

mysql实战系列--直播(含回放)周报统计

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拆分解析

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)
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)
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

mysql实战系列--直播(含回放)周报统计相关教程

  1. asciI 字符代码表

    asciI 字符代码表 asciI 字符代码表 字符代码表 MySQL导出数值至 excell 出现换号 ,复制到文本编辑器中发现出现 LR LF 字符,查找对应编码发现是 10 13 解决方案为: REPLACE(fieldName, char(10),'') //(chr(10)+chr(13)) 字符代码表

  2. MySQL安装与初入门

    MySQL安装与初入门 简略记录了个人在Windows 10环境下安装MySQL的过程,以及一些基础的SQL语句,作为备忘录。 文章目录 title: MySQL安装与初入门 0、MySQL安装 0.1 准备工作 0.2 安装 0.3 修改root密码 0.4 MySQL图形化管理工具 1、SQL 语句 1.1 SQL操作数据

  3. 五分钟!搞懂 MySQL主从复制原理,牛批!

    五分钟!搞懂 MySQL主从复制原理,牛批! 写在前面:2020年面试必备的Java后端进阶面试题总结了一份复习指南在Github上,内容详细,图文并茂,有需要学习的朋友可以Star一下! GitHub地址:https://github.com/abel-max/Java-Study-Note/tree/master Binlog 日

  4. 如何实现MySQL主从同步和读写分离

    如何实现MySQL主从同步和读写分离 目录 前言 一、案例概述 1.1 原因 1.2 解决方案 1.3 更高级的解决方案 二、MySQL主从复制 2.1 MySQL主从复制的类型 2.2 主从复制的工作原理及过程 2.2.1 MySQL主从复制原理 2.2.2 MySQL主从复制的工作过程 三、MySQL读写分离

  5. windows 下的mysqlbinlog 数据备份

    windows 下的mysqlbinlog 数据备份 1、开启binlog日志功能 找到mysql安装目录:C:\ProgramData\MySQL\MySQL Server 5.7 编辑my.ini文件,在mysqld下面添加: 重启mysql服务: ![在这里插入图片描述](https://img-blog.csdnimg.cn/20201022142906309.png#pic_c

  6. 数据存储:MySQL之各类运算符

    数据存储:MySQL之各类运算符 算术运算符只能进行数值的加法,字符串会被当成0处理。 在除法运算和取余运算中,如果除数为0,被视为非法除数,返回结果为null。 +:加法运算 SELECT 'a' + 1 AS addition; -:减法运算 SELECT 'a' - 1 AS subtraction; *:乘法

  7. 金九银十前,狂刷100遍面试题,最后却败给了MySQL

    金九银十前,狂刷100遍面试题,最后却败给了MySQL 前言: 但凡有职场经验的兄弟都知道,大厂的面试真是一言难尽,不光看你面试时的临场发挥能力,还要分N次考你对公司业务核心技术的熟悉度。 你要没有扎实的基本功,想忽悠住面试官可太难了。你去翻翻大厂那些

  8. 《大型数据库技术》MySQL管理维护

    《大型数据库技术》MySQL管理维护 MySQL管理维护 1、MySQL的备份 1.1 自由建立实验数据库及模拟数据(可复用前面实验产生的数据库) 1.2 利用mysqldump 备份所建立数据库的所有表 1.3 在任意表中插入新的数据 2、MySQL的恢复 2.1 删除所建立的数据库 2.2 利用