MySql8.x---开窗函数

1、定义 

语法结构： ** 开窗函数|聚合函数 over([分组函数] [排序函数] [自定义窗口]) **

分组函数：partition by ...，根据指定的字段对表分组，分组字段可以有多个。省略时表示整个表为一组。

排序函数：order by ...，排序字段也可以有多个，当排序字段为多个时表示先按照第一个字段排序，当第一个字段相等确定不了顺序时再按照第二个字段排序，以此类推…

自定义窗口：mysql中的窗口类型有两种：rows和range。rows是以物理行距离为基准通过计算与当前行的物理距离计算窗口大小，range是以当前行的值为基准通过计算与当前行值的差值计算窗口大小。

窗口大小可通过between 上界 and 下界来指定，其中，窗口的上下界分别有下面几种取值：

• unbounded preceding：包含当前行及当前行之前的所有记录。
• n preceding：包含当前行及当前行之前的n-1行，实际窗口大小n。
• current row：仅包含当前行。
• unbounded following：包含当前行及当前行之后的所有记录。
• n following：包含当前行及当前行之后的n-1行，实际窗口大小n。

当窗口下界为current row时，可以不使用between and，也就是下面几种情况可简写：

1. between unbounded preceding and current row --> unbounded preceding
2. between n preceding and current row --> n preceding
3. between current row and current row --> current row

排序：

• row_number() over() 从小到大依次排序 如：1，2，3，4，5
• rank() over() 相同数据并列保存，下一个值跳值，如：1，2，2，4
• dense_rank() over() 相同数据并列保存，不存着断值，如：1，2，2，3，3，3，4

位移：

• lag(字段，往下位移行数，往下没有行时默认值) over()
• lead(字段，往上位移行数，往上没有行时默认值) over()

求和：

• sum(字段) over()

指定顺序的字段值：

• first_value(col)：取窗口中字段col的第一个值。
• last_value(col)：取窗口中字段col的最后一个值。
• nth_value(col, n)：取窗口中第n顺序的值。

2、语法

• 方式一：按照列所有行进行分组

over(partition by 列)

• 方式二：按照列排序

over(order by 列)

• 方式三：按照列1分组，按照列2排序

over(partition by 列1 order by 列2)

3、练习

源数据sql：

CREATE TABLE `student_scores` (
  `sid` INT PRIMARY KEY,
  `student_id` INT,
  `student_name` VARCHAR(50),
  `course_id` INT,
  `course_name` VARCHAR(50),
  `num` INT
);

INSERT INTO `student_scores` (`sid`, `student_id`, `student_name`, `course_id`, `course_name`, `num`) VALUES
(1, 1, 'Alice', 1, 'Math', 10),
(2, 1, 'Alice', 2, 'Physics', 9),
(5, 1, 'Alice', 4, 'Biology', 66),
(6, 2, 'Bob', 1, 'Math', 8),
(8, 2, 'Bob', 3, 'Chemistry', 68),
(9, 2, 'Bob', 4, 'Biology', 99),
(10, 3, 'Charlie', 1, 'Math', 77),
(11, 3, 'Charlie', 2, 'Physics', 66),
(12, 3, 'Charlie', 3, 'Chemistry', 87),
(13, 3, 'Charlie', 4, 'Biology', 99),
(14, 4, 'David', 1, 'Math', 79),
(15, 4, 'David', 2, 'Physics', 11),
(16, 4, 'David', 3, 'Chemistry', 67),
(17, 4, 'David', 4, 'Biology', 100),
(18, 5, 'Eve', 1, 'Math', 79),
(19, 5, 'Eve', 2, 'Physics', 11),
(20, 5, 'Eve', 3, 'Chemistry', 67),
(21, 5, 'Eve', 4, 'Biology', 100),
(22, 6, 'Frank', 1, 'Math', 9),
(23, 6, 'Frank', 2, 'Physics', 100),
(24, 6, 'Frank', 3, 'Chemistry', 67),
(25, 6, 'Frank', 4, 'Biology', 100);

# 每门学科的第一名，有并列的情况一起就一起展示
SELECT
	* 
FROM
	( SELECT *, DENSE_RANK() over ( PARTITION BY course_id ORDER BY source DESC ) AS num FROM student_scores ) xx 
WHERE
	xx.num <=1;
	
#每个人不同学科中的最高分
SELECT
	* 
FROM
	( SELECT *, DENSE_RANK() over ( PARTITION BY student_id ORDER BY source DESC ) AS num FROM student_scores ) xx 
WHERE
	xx.num <=1;


#每门学科的平均分
SELECT
	course_name,
	courseAvg	
FROM
	( SELECT *,avg(source) as courseAvg, ROW_NUMBER() over ( PARTITION BY course_id ORDER BY source DESC ) AS num FROM student_scores GROUP BY course_id ) xx ;
	
#每人课程得分高于课程平均分的数量
SELECT
	student_name,
	SUM(CASE 
            WHEN source > courseAvg THEN 1
            ELSE 0
        END) as 及格数
FROM
	( SELECT *,avg(source) over(PARTITION by course_id  ) as courseAvg FROM student_scores GROUP BY course_id,student_id ) xx 
	GROUP BY xx.student_id