mysql查询最值所在行， mysql8 窗口函数介绍

z086961063

于 2023-03-31 16:27:32 发布

阅读量349

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/z086961063/article/details/129879724

版权

mysql8 窗口函数介绍

MySQL 8.0 中引入了窗口函数。窗口函数允许在查询结果中的每一行上执行聚合函数，而不用将查询结果分组。
窗口函数的语法如下：

function_name([argument_list]) OVER (
    [PARTITION BY partition_expression, ... ]
    [ORDER BY order_expression [ASC|DESC], ... ]
    [ROWS frame_specification]
)

其中，function_name 为聚合函数的名称，可以是 SUM、AVG、MAX、MIN 等等。argument_list 为聚合函数的参数列表。
PARTITION BY 子句用于指定窗口的分组方式。与传统的分组聚合不同的是，窗口函数的分组并不会导致结果集的分组，而是用于将同一组内的行作为一个整体进行计算。
ORDER BY 子句用于指定窗口函数的排序方式。
ROWS 子句用于指定窗口函数计算的行范围。常用的行范围包括 UNBOUNDED PRECEDING（窗口开始处到当前行）、CURRENT ROW（当前行）、UNBOUNDED FOLLOWING（当前行到窗口结束处）等等。

窗口函数的查询逻辑

窗口函数的查询逻辑与传统的分组聚合有所不同。传统的分组聚合是将数据按照分组字段进行分组，然后对每个分组进行聚合计算，最后将结果返回。
而窗口函数的查询逻辑是：首先对每一行进行计算，然后根据 PARTITION BY 子句指定的分组方式，将同一组内的行作为一个整体进行计算，最后将结果返回。因此，窗口函数的结果集的行数与源表的行数相同，只是每一行的值被计算。

窗口函数与传统的分组聚合有以下区别

分组方式不同。传统的分组聚合是按照分组字段进行分组，而窗口函数则是按照 PARTITION BY 子句指定的分组方式进行分组。

结果集不同。传统的分组聚合会将结果集合并为一个汇总结果，而窗口函数的结果集与源表的行数相同，只是每一行的值被计算。

计算方式不同。传统的分组聚合是对每个分组进行聚合计算，而窗口函数则是对每一行进行计算，然后根据分组方式进行整体计算。

使用窗口函数查询最大值所在行的场景

在传统的分组聚合中，要查询最大值所在行，通常需要使用子查询或者自连接的方式进行查询，而使用窗口函数则可以更加简单地实现。
例如，假设我们有一个 score 表，包含学生的姓名和成绩。要查询每个学生的最高成绩及对应的科目，可以使用以下 SQL 语句：

SELECT name, subject, score
FROM (
    SELECT name, subject, score, 
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY score DESC) as rn
    FROM score
) t
WHERE rn = 1;

以上 SQL 语句中，ROW_NUMBER 函数用于为每个学生的成绩按照降序排列，并为每个成绩分配一个排名。然后通过在外层查询中过滤出排名为 1 的成绩，即每个学生的最高成绩。
相比于传统的分组聚合方式，使用窗口函数可以更加简单地实现这个查询，并且在处理大数据量时也更加高效。

窗口函数的优势

窗口函数可以在一次查询中使用多种分组进行聚合计算。
在传统的分组聚合中，一次查询必须按照分组字段进行分组，才能进行聚合计算。而窗口函数可以在不分组的情况下进行聚合计算，而且可以为每个聚合函数指定不同的分组。
例如，假设我们有一个 employees 表，包含员工的姓名、部门和薪水。要查询每个员工的薪水与部门平均薪水的差距，可以使用以下 SQL 语句：

SELECT name, department,subDepart, salary, 
		#员工与部门平均薪资查
       salary - AVG(salary) OVER (PARTITION BY department) as diff,
       #员工与子部门平均薪资查
       salary - AVG(salary) OVER (PARTITION BY department,subDepart) as subDiff
FROM employees;

以上 SQL 语句中，AVG 函数用于计算每个部门的平均薪水，然后使用窗口函数在每个部门内计算每个员工的薪水与部门平均薪水的差距。由于使用了窗口函数，可以在一次查询中进行两种分组。

窗口函数可以在同一查询中进行聚合计算和普通查询。
在传统的分组聚合中，只能进行聚合计算，不能进行普通查询（传统分组后只输出一条数据，导致无法体现普通查询的数据）。而窗口函数可以在同一查询中进行聚合计算和普通查询，这样可以减少查询次数，提高查询效率。
例如，假设我们有一个 orders 表，包含订单的日期、产品和销售额。要查询每个日期的销售额、前一天的销售额和销售额的增长率，可以使用以下 SQL 语句：

SELECT date, sales, 
       LAG(sales) OVER (ORDER BY date) as prev_sales, 
       (sales - LAG(sales) OVER (ORDER BY date)) / LAG(sales) OVER (ORDER BY date) as growth_rate
FROM orders;

以上 SQL 语句中，LAG 函数用于查询前一天的销售额。然后使用窗口函数在所有订单内进行前一天销售额的查询，并在外层查询中计算销售额的增长率。由于使用了窗口函数，可以在同一查询中进行聚合计算和普通查询，避免了产生额外的查询开销。

可以方便地进行相邻行查询。在传统分组聚合查询中，如果需要查询相邻行的值进行计算，就需要使用子查询或临时表。而在窗口函数中，可以使用 LEAD() 和 LAG() 函数方便地查询前一行或后一行的值，并进行计算。
例如，查询每个日期的销售额和前一天的销售额，并计算增长率：

SELECT date, sales, 
       LAG(sales) OVER (ORDER BY date) as prev_sales, 
       (sales - LAG(sales) OVER (ORDER BY date)) / LAG(sales) OVER (ORDER BY date) as growth_rate
FROM orders;

综上所述，窗口函数具有多种优势，可以简化查询逻辑，提高查询效率，使得查询更加灵活、方便、易于理解。

常用的窗口函数及其作用和使用场景

ROW_NUMBER()
作用：为每行结果分配一个唯一的整数编号，从1开始递增。
使用场景：需要对结果集进行编号，例如查询排名、分页等场景。
例如，查询每个部门的员工薪水排名：

SELECT name, department, salary, 
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

RANK() 和 DENSE_RANK()
作用：为每行结果分配一个排名号，根据指定的排序规则进行排名。RANK() 函数会跳过相同排名，DENSE_RANK() 函数不会跳过相同排名。
使用场景：需要对结果集进行排名，例如查询销售额排名、竞赛得分等场景。
例如，查询每个产品的销售额排名：

SELECT product, sales, 
       RANK() OVER (ORDER BY sales DESC) as rank,
       DENSE_RANK() OVER (ORDER BY sales DESC) as dense_rank
FROM sales;

SUM() 和 AVG()
作用：计算指定列的总和或平均值。
使用场景：需要对结果集进行聚合计算，例如计算总销售额、平均薪水等场景。
例如，查询每个部门的总销售额和平均销售额：

SELECT department, sales, 
       SUM(sales) OVER (PARTITION BY department) as total_sales,
       AVG(sales) OVER (PARTITION BY department) as avg_sales
FROM sales;

MAX() 和 MIN()
作用：计算指定列的最大值或最小值。
使用场景：需要查询最大值或最小值，例如查询最高薪水、最小销售额等场景。
例如，查询每个部门的最高薪水和最低薪水：

SELECT name, department, salary, 
       MAX(salary) OVER (PARTITION BY department) as max_salary,
       MIN(salary) OVER (PARTITION BY department) as min_salary
FROM employees;

LEAD() 和 LAG()
作用：查询指定列在当前行前一行或后一行的值。
使用场景：需要查询相邻行的值进行计算，例如查询环比增长率、移动平均等场景。
例如，查询每个日期的销售额和前一天的销售额，并计算增长率：

SELECT date, sales, 
       LAG(sales) OVER (ORDER BY date) as prev_sales, 
       (sales - LAG(sales) OVER (ORDER BY date)) / LAG(sales) OVER (ORDER BY date) as growth_rate
FROM orders;