3 数据分析 开窗函数

数据分析 MySQL开窗函数

1 开窗函数介绍

开窗函数是在满足某种条件的记录集合上执行的特殊函数。
MySQL在8.0的版本中增加了对开窗函数的支持。

MySQL中使用开窗函数的场景
单表中需要在满足某些条件的记录集内部做一些函数操作，而且不是简单的表连接和聚合可以轻松实现的。

开窗函数和普通聚合函数的区别：
聚合函数是将多条记录聚合为一条；
开窗函数不影响原数据及结构，只是新增开窗函数对应的列。

2 开窗函数使用方法

2.1 开窗函数语法

开窗函数语法：函数名(列) over (选项)

选项包括 partition by 列 order by 列

窗口函数是基于所有满足条件的行进行计算的。
over是关键字，用来指定函数执行的窗口范围，如果over后面的括号中为空，则说明窗口包含所有满足where条件的行。

使用开窗函数相当于增加了一列数据，可以再对这列开窗函数产生的数据进行条件判断。

2.2 选项

1. partition by 子句
   窗口按照那些字段进行分组，窗口函数在不同的分组上分别执行。

2. order by 子句
   按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号。

3. 指定窗口范围
   通常使用BETWEEN frame_start AND frame_end语法来表示行范围，其中frame_start和frame_end可以支持如下关键字：
   CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用
   UNBOUNDED PRECEDING 边界是分区中的第一行
   UNBOUNDED FOLLOWING 边界是分区中的最后一行
   expr PRECEDING 边界是当前行减去expr的值
   expr FOLLOWING 边界是当前行加上expr的值

例如
rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范围是当前行、前一行、后一行一共三行记录。
rows UNBOUNDED FOLLOWING 窗口范围是当前行到分区中的最后一行。
rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范围是当前分区中所有行。

2.3 开窗函数

开窗函数分为如下几类

1. 序号函数
   row_number() / rank() / dense_rank()
2. 分布函数
   percent_rank() / cume_dist()
3. 前后函数
   lag() / lead()
4. 头尾函数
   first_val() / last_val()
5. 其他
   nth_value() / nfile()
6. 聚合函数作为窗口函数
   在窗口中每条记录动态应用聚合函数(sum/avg/max/min/count)，可以动态计算在指定的窗口范围内的各种聚合函数值。

3 例子

3.1 排名函数

3.1.1 准备数据

CREATE TABLE class (
  cid int(11) NOT NULL AUTO_INCREMENT,
  caption varchar(32) NOT NULL,
  PRIMARY KEY (cid)
) ENGINE=InnoDB CHARSET=utf8;

INSERT INTO class VALUES
(1, '三年二班'), 
(2, '三年三班'), 
(3, '一年二班'), 
(4, '二年九班');

CREATE TABLE teacher(
  tid int(11) NOT NULL AUTO_INCREMENT,
  tname varchar(32) NOT NULL,
  PRIMARY KEY (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO teacher VALUES
(1, '张磊老师'), 
(2, '李平老师'), 
(3, '刘海燕老师'), 
(4, '朱云海老师'), 
(5, '李杰老师');

CREATE TABLE course(
  cid int(11) NOT NULL AUTO_INCREMENT,
  cname varchar(32) NOT NULL,
  teacher_id int(11) NOT NULL,
  PRIMARY KEY (cid),
  KEY fk_course_teacher (teacher_id),
  CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO course VALUES
(1, '生物', 1), 
(2, '物理', 2), 
(3, '体育', 3), 
(4, '美术', 2);

CREATE TABLE student(
  sid int(11) NOT NULL AUTO_INCREMENT,
  gender char(1) NOT NULL,
  class_id int(11) NOT NULL,
  sname varchar(32) NOT NULL,
  PRIMARY KEY (sid),
  KEY fk_class (class_id),
  CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO student VALUES
(1, '男', 1, '理解'), 
(2, '女', 1, '钢蛋'), 
(3, '男', 1, '张三'), 
(4, '男', 1, '张一'), 
(5, '女', 1, '张二'), 
(6, '男', 1, '张四'), 
(7, '女', 2, '铁锤'), 
(8, '男', 2, '李三'), 
(9, '男', 2, '李一'), 
(10, '女', 2, '李二'), 
(11, '男', 2, '李四'), 
(12, '女', 3, '如花'), 
(13, '男', 3, '刘三'), 
(14, '男', 3, '刘一'), 
(15, '女', 3, '刘二'), 
(16, '男', 3, '刘四');

CREATE TABLE score (
  sid int(11) NOT NULL AUTO_INCREMENT,
  student_id int(11) NOT NULL,
  course_id int(11) NOT NULL,
  num int(11) NOT NULL,
  PRIMARY KEY (sid),
  KEY fk_score_student (student_id),
  KEY fk_score_course (course_id),
  CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),
  CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO score VALUES
(1, 1, 1, 10),
(2, 1, 2, 9),
(5, 1, 4, 66),
(6, 2, 1, 8),
(8, 2, 3, 68),
(9, 2, 4, 99),
(10, 3, 1, 77),
(11, 3, 2, 66),
(12, 3, 3, 87),
(13, 3, 4, 99),
(14, 4, 1, 79),
(15, 4, 2, 11),
(16, 4, 3, 67),
(17, 4, 4, 100),
(18, 5, 1, 79),
(19, 5, 2, 11),
(20, 5, 3, 67),
(21, 5, 4, 100),
(22, 6, 1, 9),
(23, 6, 2, 100),
(24, 6, 3, 67),
(25, 6, 4, 100),
(26, 7, 1, 9),
(27, 7, 2, 100),
(28, 7, 3, 67),
(29, 7, 4, 88),
(30, 8, 1, 9),
(31, 8, 2, 100),
(32, 8, 3, 67),
(33, 8, 4, 88),
(34, 9, 1, 91),
(35, 9, 2, 88),
(36, 9, 3, 67),
(37, 9, 4, 22),
(38, 10, 1, 90),
(39, 10, 2, 77),
(40, 10, 3, 43),
(41, 10, 4, 87),
(42, 11, 1, 90),
(43, 11, 2, 77),
(44, 11, 3, 43),
(45, 11, 4, 87),
(46, 12, 1, 90),
(47, 12, 2, 77),
(48, 12, 3, 43),
(49, 12, 4, 87),
(52, 13, 3, 87);

3.1.2 查询各科成绩前三名的学生以及成绩信息

row_number() / rank() / dense_rank() / ntitle(num)

根据课程进行分组，然后对每组内的成绩进行排序。

SELECT
	score.sid,
	student.sname,
	student.gender,
	course.cname,
	score.num,
	row_number() over ( PARTITION BY course.cname ORDER BY num DESC ) AS row_number排名,
	rank() over ( PARTITION BY course.cname ORDER BY num DESC ) AS rank排名,
	dense_rank() over ( PARTITION BY course.cname ORDER BY num DESC ) AS dense_rank排名,
	ntile( 6 ) over ( PARTITION BY course.cname ORDER BY num DESC ) AS ntile排名 
FROM
	score
	JOIN student ON score.student_id = student.sid
	LEFT JOIN course ON score.course_id = course.cid

row_number 行号
对于同组内相同的成绩并没有做特殊处理，仅生成连续的序号。

rank 排名
值相同时排名相同，排名跳跃。

dense_rank 密集排名
值相同时排名相同，但是排名不跳跃。

ntile 分组排名
ntile会先根据指定字段(课程名称)进行分组，将每个组的总记录数按照传入ntile()的数字进行进一步分组，例如ntile(6) ，体育课程总共12条记录，等划分成6份，每份两条记录，排序结果为112233445566。

统计各科成绩前三，如果允许并列排名，使用 dense_rank 比较合适。

SELECT
	* 
FROM
	(
	SELECT
		score.sid,
		student.sname,
		student.gender,
		course.cname,
		score.num,
		dense_rank() over ( PARTITION BY course.cname ORDER BY num DESC ) AS dense_rank排名 
	FROM
		score
		JOIN student ON score.student_id = student.sid
		LEFT JOIN course ON score.course_id = course.cid 
	) AS temp_rank 
WHERE
	temp_rank.dense_rank排名 <= 3;

3.2 前后函数

3.2.1 准备数据

CREATE TABLE lag_table ( id INT PRIMARY KEY, uid INT NOT NULL, login_time datetime NOT NULL );
INSERT INTO lag_table
VALUES
	( 1, 1, "2020-4-10 12:02:00" ),
	( 2, 1, "2020-4-10 12:03:23" ),
	( 3, 1, "2020-4-10 12:03:59" ),
	( 4, 1, "2020-4-10 12:06:34" ),
	( 5, 2, "2020-4-10 13:00:00" ),
	( 6, 2, "2020-4-10 13:02:00" ),
	( 7, 2, "2020-4-10 13:02:45" )

3.2.2 判断登录违规

同一用户如果在2分钟内重新登录，则判断为违规，统计哪些用户有违规行为，并计算违规次数。

lag(col,n)
用于统计窗口内往上第n行值

lead(col,n)
用于统计窗口内往下第n行值

lag和lead可用于同列中相邻行的数据相减操作。

1. 查询

SELECT
	id,
	uid,
	login_time,
	lead( login_time, 1 ) over ( PARTITION BY uid ORDER BY login_time ) AS lead_time 
FROM
	lag_table;

2. 相减操作

SELECT
	*,
	format(相差秒数/ 60, 3 ) AS 相差分钟数 
FROM
	(
	SELECT
		id,
		uid,
		login_time,
		lead( login_time, 1 ) over ( PARTITION BY uid ORDER BY login_time ) AS lead_time,
		TIMESTAMPDIFF(
			SECOND,
			login_time,(
			lead( login_time, 1 ) over ( PARTITION BY uid ORDER BY login_time ))) AS 相差秒数 
	FROM
	lag_table 
	) AS e

3. 增加条件

SELECT
	uid,
	count( 1 ) 作弊次数 
FROM
	(
	SELECT
		id,
		uid,
		login_time,
		lead( login_time, 1 ) over ( PARTITION BY uid ORDER BY login_time ) lead_time,
		TIMESTAMPDIFF(
			SECOND,
			login_time,(
			lead( login_time, 1 ) over ( PARTITION BY uid ORDER BY login_time ))) 相差秒数 
	FROM
		lag_table 
	) AS e
WHERE
	format( result.相差秒数 / 60, 3 ) <= 2 
GROUP BY
	uid;

3.3 first_value / last_value

3.3.1 介绍

first_value(column)
分组内进行排序后，截止到当前行，取第一个值。

last_value(column)
分组内进行排序后，截止到当前行，取最后一个值。

窗口函数范围
rows beteween XXX and XXX
unbounded 无限制的
preceding 分区的当前记录的向前偏移量
current 当前
following 分区的当前记录的向后偏移量

first_value 和 last_value 的默认统计范围是 rows between unbounded preceding and current row，取分组后从组内首行到当前行之间的数据。
rows between unbounded preceding and unbounded following 指定统计范围为分组后组内所有数据。

3.3.2 案例

1. 找出一天中有多次更新的商品ID；
2. 对于一天内有多次更新的商品，只保留当天最后一条记录。

使用 LAST_VALUE + ORDER BY insert_time ASC
或 FIRST_VALUE + ORDER BY insert_time DESC

SELECT
	* 
FROM
	(
	SELECT
		*,
		LAST_VALUE( insert_time ) over ( PARTITION BY item_id, insert_date ORDER BY insert_time ASC rows BETWEEN unbounded preceding AND unbounded following ) AS last_time 
	FROM
		taobao_data 
	) AS e 
WHERE
	last_time = insert_time

使用rank

SELECT
	* 
FROM
	(
	SELECT
		*,
		rank() over ( PARTITION BY item_id, insert_date ORDER BY insert_time DESC rows BETWEEN unbounded preceding AND unbounded following ) AS time_rank 
	FROM
		taobao_data 
	) AS e 
WHERE
	time_rank = 1

使用max

SELECT
	* 
FROM
	(
	SELECT
		*,
		max( insert_time ) over ( PARTITION BY item_id, insert_date ORDER BY insert_time rows BETWEEN unbounded preceding AND unbounded following ) AS max_time 
	FROM
		taobao_data 
	) AS e 
WHERE
	max_time = insert_time