MySql——常用窗口函数应用与解释——六月十七

窗口函数的语法基本如下：

<窗口函数> OVER (PARTITION BY <用于分组的列名> ORDER BY <用户排序的列名>)

其中，窗口函数可以是专用窗口函数或聚合函数。

1. 专用窗口函数：

   • LEAD: 获取当前行之后的指定行的值。
   • LAG: 获取当前行之前的指定行的值。
   • FIRST_VALUE: 获取窗口内第一行的值。
   • LAST_VALUE: 获取窗口内最后一行的值。
   • RANK: 计算排名，如果有相同值，则相同值的行获得相同的排名，并跳过下一个排名。
   • DENSE_RANK: 计算排名，如果有相同值，则相同值的行获得相同的排名，但不跳过下一个排名。
   • ROW_NUMBER: 给窗口内的行分配唯一的连续编号。

2. 聚合函数：

   • SUM: 计算指定列的总和。
   • AVG: 计算指定列的平均值。
   • MAX: 计算指定列的最大值。
   • MIN: 计算指定列的最小值。
   • COUNT: 计算指定列的行数。

在窗口函数的语法中，PARTITION BY子句用于指定分组条件，即按照某列的值进行分组，相同值的行将被视为一组。ORDER BY子句用于指定排序条件，即按照某列的值对窗口内的行进行排序。

通过使用窗口函数，可以在结果集中计算每个行的某种统计值或分析值，并且不会对结果集进行聚合操作。窗口函数可以应用于查询语句的SELECT列表、ORDER BY子句和HAVING子句中。

以下是一个示例，计算每个部门的平均工资，并为每个部门按工资降序排列：

SELECT
  department_id,
  employee_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS avg_salary
  --desc降序，asc升序
FROM
  employees;

在上述示例中，AVG(salary) OVER (PARTITION BY department_id ORDER BY salary DESC)是一个窗口函数，它计算每个部门的平均工资，并按照工资降序排列。对于每个行，都会计算相应部门的平均工资，并将结果作为avg_salary列返回。
假设我们有一个名为employees的表，包含以下字段：

• department_id: 部门ID
• employee_id: 员工ID
• salary: 工资

我们使用窗口函数计算每个部门的平均工资，并按照工资降序排列的效果可以如下所示：

±--------------±------------±-------±-----------+
| department_id | employee_id | salary | avg_salary |
±--------------±------------±-------±-----------+
| 1 | 102 | 6000 | 6000.0000 |
| 1 | 101 | 5000 | 5500.0000 |
| 1 | 103 | 4500 | 5166.6667 |
| 2 | 202 | 4500 | 4500.0000 |
| 2 | 201 | 4000 | 4250.0000 |
| 2 | 203 | 3900 | 4133.3333 |
| 3 | 303 | 8000 | 8000.0000 |
| 3 | 301 | 7000 | 7500.0000 |
| 3 | 302 | 6000 | 7000.0000 |
±--------------±------------±-------±-----------+

在上述表格中，avg_salary列显示了每个部门的平均工资。可以看到，每个部门的平均工资被计算并在每行中显示，不会对结果集进行聚合操作。

通过窗口函数，我们可以轻松地在每个部门内进行统计和分析，并且不会破坏原始的行级数据。这为数据分析和报表生成提供了更灵活和高效的方式。
窗口函数为MySQL提供了更灵活和强大的数据分析和统计能力，可以在单个查询中处理复杂的分析任务，而无需使用多个子查询或临时表。