mysql开窗函数rownumber_over partition by开窗函数

什么是开窗函数？

开窗函数是一种用于分析数据的强大功能，MySQL从8.0版本开始支持。开窗函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。开窗函数有以下几种类型：

– 聚合开窗函数：sum,avg,count,max,min等，用于计算基于组的某种聚合值。

– 排名开窗函数：row_number,dense_rank,rank等，用于对数据进行排序和排名。

– 值分布开窗函数：first_value,last_value,nth_value等，用于获取数据中的特定值。

– 导航开窗函数：lead,lag等，用于获取数据中的前后值。

1.sum()

sum开窗函数是一种聚合开窗函数，用于计算基于组的某种聚合值，它和聚合函数的不同之处是：sum开窗函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。sum开窗函数的语法如下：

sum(列名) over (partition by 分组列 order by 排序列)

这表示对分组列进行分组，然后按照排序列进行排序，最后对每个分组内的列名进行求和，并返回每一行的累计和。例如，假设有一个成绩表score，有三个字段：学生s_id，课程c_id，成绩s_score。如果想要计算每个学生在每门课程上的累计成绩，可以使用以下语句：

select s_id, c_id, s_score, sum(s_score) over (partition by s_id order by c_id) as total_score from score;

这样就可以得到类似如下的结果：

2.avg()

avg开窗函数是一种聚合开窗函数，用于计算基于组的某种聚合值，它和聚合函数的不同之处是：avg开窗函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。avg开窗函数的语法如下：

avg(列名) over (partition by 分组列 order by 排序列) 

这表示对分组列进行分组，然后按照排序列进行排序，最后对每个分组内的列名进行求平均值，并返回每一行的累计平均值。例如，假设有一个成绩表score，有三个字段：学生s_id，课程c_id，成绩s_score。如果想要计算每个学生在每门课程上的累计平均成绩，可以使用以下语句：

select s_id, c_id, s_score, avg(s_score) over (partition by s_id order by c_id) as avg_score from score; 

这样就可以得到类似如下的结果：

3.count()

count开窗函数是一种聚合开窗函数，用于计算基于组的某种聚合值，它和聚合函数的不同之处是：count开窗函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。count开窗函数的语法如下：

count(列名) over (partition by 分组列 order by 排序列) 

这表示对分组列进行分组，然后按照排序列进行排序，最后对每个分组内的列名进行计数，并返回每一行的累计计数。例如，假设有一个成绩表score，有三个字段：学生s_id，课程c_id，成绩s_score。如果想要计算每个学生在每门课程上的累计参与人数，可以使用以下语句：

select s_id, c_id, s_score, count(s_score) over (partition by c_id order by s_score) as count_score from score; 

这样就可以得到类似如下的结果：

4.max()

max开窗函数是一种聚合开窗函数，用于计算基于组的某种聚合值，它和聚合函数的不同之处是：max开窗函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。max开窗函数的语法如下：

max(列名) over (partition by 分组列 order by 排序列) 

这表示对分组列进行分组，然后按照排序列进行排序，最后对每个分组内的列名进行求最大值，并返回每一行的累计最大值。例如，假设有一个成绩表score，有三个字段：学生s_id，课程c_id，成绩s_score。如果想要计算每个学生在每门课程上的累计最高成绩，可以使用以下语句：

select s_id, c_id, s_score, max(s_score) over (partition by c_id order by s_score) as max_score from score; 

这样就可以得到类似如下的结果：

5.min()

min开窗函数是一种聚合开窗函数，用于计算基于组的某种聚合值，它和聚合函数的不同之处是：min开窗函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。min开窗函数的语法如下：

min(列名) over (partition by 分组列 order by 排序列) 

这表示对分组列进行分组，然后按照排序列进行排序，最后对每个分组内的列名进行求最小值，并返回每一行的累计最小值。例如，假设有一个成绩表score，有三个字段：学生s_id，课程c_id，成绩s_score。如果想要计算每个学生在每门课程上的累计最低成绩，可以使用以下语句：

select s_id, c_id, s_score, min(s_score) over (partition by c_id order by s_score) as min_score from score;

这样就可以得到类似如下的结果：

6.row_number()

row_number开窗函数是一种排序开窗函数，用于为每一行分配一个序号，从1开始递增。row_number开窗函数的语法如下：

row_number() over (partition by 分组列 order by 排序列) 

这表示对分组列进行分组，然后按照排序列进行排序，最后对每个分组内的行进行编号。例如，假设有一个员工表employee，有三个字段：部门d_id，姓名e_name，薪水e_salary。如果想要计算每个部门的员工薪水排名，可以使用以下语句：

select d_id, e_name, e_salary, row_number() over (partition by d_id order by e_salary desc) as rank from employee; 

这样就可以得到类似如下的结果：

7.dense_rank()

dense_rank开窗函数是一种排序开窗函数，用于为每一行分配一个排名，而排名值没有间隙。dense_rank开窗函数的语法如下：

dense_rank() over (partition by 分组列 order by 排序列) 

这表示对分组列进行分组，然后按照排序列进行排序，最后对每个分组内的行进行排名。如果分区有两个或更多具有相同排名值的行，则为这些行中的每一行分配相同的排名。与rank开窗函数不同，dense_rank开窗函数始终返回连续的排名值。例如，假设有一个商品表product，有三个字段：类别p_type，名称p_name，价格p_price。如果想要计算每个类别的商品价格排名，可以使用以下语句：

select p_type, p_name, p_price, dense_rank() over (partition by p_type order by p_price desc) as rank from product; 

这样就可以得到类似如下的结果：

8.rank()

rank开窗函数是一种排序开窗函数，用于为每一行分配一个排名，而排名值有间隙。rank开窗函数的语法如下：

rank() over (partition by 分组列 order by 排序列) 

这表示对分组列进行分组，然后按照排序列进行排序，最后对每个分组内的行进行排名。如果分区有两个或更多具有相同排名值的行，则为这些行中的每一行分配相同的排名。与dense_rank开窗函数不同，rank开窗函数会跳过重复的排名值。例如，假设有一个商品表product，有三个字段：类别p_type，名称p_name，价格p_price。如果想要计算每个类别的商品价格排名，可以使用以下语句：

select p_type, p_name, p_price, rank() over (partition by p_type order by p_price desc) as rank from product; 

这样就可以得到类似如下的结果：

9.first_value()

first_value开窗函数是一种返回窗口框架，分区或结果集的第一行的值的函数。first_value开窗函数的语法如下：

first_value(表达式) over ([partition by 分组列] [order by 排序列] [frame_clause]) 

这表示对分组列进行分组，然后按照排序列进行排序，最后返回每个分组内的第一行的表达式的值。如果没有指定frame_clause，则默认为range between unbounded preceding and current row，即从分区开始到当前行。例如，假设有一个员工表employee，有四个字段：部门dept_no，姓名emp_name，工资salary，小时hourly_rate。如果想要计算每个部门的最高工资和最高小时率，并显示每个员工与之的差距，可以使用以下语句：

select dept_no, emp_name, salary, hourly_rate, first_value(salary) over (partition by dept_no order by salary desc) as max_salary, first_value(hourly_rate) over (partition by dept_no order by hourly_rate desc) as max_hourly_rate, first_value(salary) over (partition by dept_no order by salary desc) - salary as salary_gap, first_value(hourly_rate) over (partition by dept_no order by hourly_rate desc) - hourly_rate as hourly_rate_gap from employee; 

这样就可以得到类似如下的结果：

10.last_value()

last_value开窗函数是一种返回窗口框架，分区或结果集的最后一行的值的函数。last_value开窗函数的语法如下：

last_value(表达式) over ([partition by 分组列] [order by 排序列] [frame_clause]) 

这表示对分组列进行分组，然后按照排序列进行排序，最后返回每个分组内的最后一行的表达式的值。如果没有指定frame_clause，则默认为range between unbounded preceding and current row，即从分区开始到当前行。例如，假设有一个学生成绩表student_scores，有四个字段：学号studentId，数学成绩math，系别departmentId，班级classId。如果想要计算每个班级的数学成绩的最大值和最小值，并显示每个学生与之的差距，可以使用以下语句：

select studentId, math, departmentId, classId, last_value(math) over (partition by classId order by math) as max_math, first_value(math) over (partition by classId order by math) as min_math, last_value(math) over (partition by classId order by math) - math as math_gap from student_scores where departmentId = 'department1'; 

这样就可以得到类似如下的结果：

11.nth_value()

nth_value开窗函数是一种返回窗口框架，分区或结果集中第N行的值的函数。nth_value开窗函数的语法如下：

nth_value(表达式, N) over ([partition by 分组列] [order by 排序列] [frame_clause]) 

这表示对分组列进行分组，然后按照排序列进行排序，最后返回每个分组内的第N行的表达式的值。如果没有指定frame_clause，则默认为range between unbounded preceding and current row，即从分区开始到当前行。例如，假设有一个学生成绩表student_scores，有四个字段：学号studentId，数学成绩math，系别departmentId，班级classId。如果想要计算每个班级的数学成绩的中位数和平均数，并显示每个学生与之的差距，可以使用以下语句：

select studentId, math, departmentId, classId, nth_value(math, count(*) over (partition by classId) / 2) over (partition by classId order by math) as median_math, avg(math) over (partition by classId) as avg_math, nth_value(math, count(*) over (partition by classId) / 2) over (partition by classId order by math) - math as median_gap, avg(math) over (partition by classId) - math as avg_gap from student_scores where departmentId = 'department1'; 

这样就可以得到类似如下的结果：

12.lead()

lead开窗函数是一种允许你向前看多行并从当前行访问行的数据的函数。lead开窗函数的语法如下：

lead(表达式, N) over ([partition by 分组列] [order by 排序列]) 

这表示对分组列进行分组，然后按照排序列进行排序，最后返回每个分组内的当前行之后的第N行的表达式的值。如果没有指定N，则默认为1，即返回下一行的值。例如，假设有一个员工工资表employee_salaries，有三个字段：员工编号empId，姓名name，工资salary。如果想要计算每个员工与其下一个员工（按照工资降序排列）的工资差距，可以使用以下语句：

select empId, name, salary, lead(salary) over (order by salary desc) as next_salary, salary - lead(salary) over (order by salary desc) as salary_gap from employee_salaries; 

这样就可以得到类似如下的结果：

13.lag()

lag开窗函数是一种允许你向后看多行并从当前行访问行的数据的函数。lag开窗函数的语法如下：

lag(表达式, N) over ([partition by 分组列] [order by 排序列]) 

这表示对分组列进行分组，然后按照排序列进行排序，最后返回每个分组内的当前行之前的第N行的表达式的值。如果没有指定N，则默认为1，即返回上一行的值。例如，假设有一个商品销售表product_sales，有四个字段：商品编号productId，商品名称productName，销售日期saleDate，销售数量saleQuantity。如果想要计算每个商品每天与前一天（按照日期升序排列）的销售数量差异，可以使用以下语句：

select productId, productName, saleDate, saleQuantity, lag(saleQuantity) over (partition by productId order by saleDate) as prev_quantity, saleQuantity - lag(saleQuantity) over (partition by productId order by saleDate) as quantity_diff from product_sales; 

这样就可以得到类似如下的结果：