【MySQL】窗口函数原理，与where、group by关系

窗口函数原理，与where、group by关系

一、窗口函数是什么

    窗口函数（Window Function）是一种特殊的数据库查询函数，它允许你对数据集的一个子集（窗口）执行计算，而不是整个数据集。窗口函数常用于SQL查询中，用来执行复杂的分析任务，比如计算累计总和、移动平均值、排名等。

窗口函数的执行原理通常包含以下几个关键步骤：

• 定义窗口:首先，你需要定义一个窗口，它决定了窗口函数将作用的数据范围。窗口可以基于行号、分区键或排序键来定义。
• 指定窗口函数：选择一个窗口函数，如SUM()、AVG()、RANK()等，来对窗口内的数据进行计算。
• 应用排序：窗口函数通常需要一个排序顺序，这决定了数据在窗口中的排列方式。排序可以基于一个或多个列。
• 执行计算：根据定义的窗口和排序，窗口函数对窗口内的数据进行计算。
• 返回结果：窗口函数返回计算结果，通常与其他列的数据一起返回。

下面是一个简单的SQL窗口函数的例子，演示了如何使用窗口函数来计算员工工资的累计总和：

SELECT
  employee_id,
  salary,SUM(salary)OVER(ORDERBY salary)AS cumulative_salary
FROM
  employees;

在这个例子中：

• employee_id和salary是员工表中的列。

• SUM(salary) OVER (ORDER BY salary)是一个窗口函数，它计算按工资排序的累计工资总和。

• OVER (ORDER BY salary)定义了窗口的排序方式，这里是按工资排序。

    窗口函数的执行原理在不同的数据库系统中可能有所不同，但大多数数据库系统都遵循类似的步骤。窗口函数是数据库分析和报告中非常强大的工具，它们允许用户在不改变数据集结构的情况下，进行复杂的数据聚合和分析。
  

二、窗口函数与where、group by关系、执行顺序

    窗口函数与WHERE子句和GROUP BY子句在SQL查询中的作用是不同的，它们在查询处理的不同阶段执行：

• WHERE子句： WHERE子句在查询的早期阶段执行，用于过滤数据。它根据指定的条件从表中选择行，只保留满足条件的行，删除不满足条件的行。

• GROUP BY子句： GROUP BY子句在WHERE子句之后执行，用于对满足WHERE条件的数据进行分组，然后对每个组应用聚合函数（如SUM()、AVG()、COUNT()等）。

• 窗口函数： 窗口函数通常在FROM和WHERE子句之后，但在GROUP BY之前执行。它们在数据集上定义了一个窗口，然后在这个窗口上执行计算，但不会像GROUP BY那样将数据分组。窗口函数可以访问当前行以及窗口内其他行的数据。

    **窗口函数的返回值与当前行的关系取决于窗口函数的定义。窗口函数可以访问当前行以及基于ORDER BY和PARTITION BY子句定义的窗口内的其他行**。
  
    例如，一个窗口函数可能包括当前行、当前行之前的行、当前行之后的行，或者是当前行以及它周围的行，具体取决于窗口的定义。
  

三、窗口函数的返回值如何与当前行关联，返回当前行的信息

    窗口函数在执行时会考虑当前行的数据，以及根据窗口定义（包括PARTITION BY和ORDER BY子句）确定的窗口范围内的其他行的数据。对于查询结果集中的每一行，窗口函数都会计算并生成一个特定的值，这个值与该行相关联，并且会作为结果集的一部分返回。

为了更清楚地解释这个概念，让我们通过一个具体的例子来说明：

--假设有一个员工表employees，包含员工ID（employee_id）、--部门ID（department_id）和工资（salary）。SELECT
  employee_id,
  department_id,
  salary,
  RANK()OVER(PARTITIONBY department_id ORDERBY salary DESC)AS salary_rank
FROM
  employees;

在这个查询中：

• RANK()是一个窗口函数，它为每个部门内的员工按工资进行排名。

• OVER子句定义了窗口函数的作用范围和排序方式。

• PARTITION BY department_id表示窗口函数将在每个部门内部独立计算。这意味着，每个部门的员工将分别进行排名，而不是整个公司的所有员工一起排名。

• ORDER BY salary DESC表示在每个部门内，员工将根据工资降序排列，工资最高的员工将获得排名1。 salary_rank是窗口函数的结果列，它将显示每个员工在其部门内的工资排名。

    当这个查询执行时，对于结果集中的每一行（即每个员工），RANK()函数都会计算一个排名值。这个排名值是基于当前行（员工）的工资以及同一部门内其他员工的工资来确定的。例如：
  

如果某个部门有三个员工，工资分别为5000、4000和3000，那么工资最高的员工将获得排名1，工资次高的员工将获得排名2，而工资最低的员工也将获得排名2（如果使用RANK()函数；如果使用DENSE_RANK()函数，则所有员工都将获得不同的排名）。
因此，每个员工的salary_rank值都是相对于他们所在部门的其他员工的工资来确定的，并且这个值是与每个员工的行相关联的。这就是为什么说窗口函数会为每一行生成一个与之相关的输出值。

那么如何做到：这个值是与每个员工的行相关联的？

    窗口函数实现“这个值是与每个员工的行相关联的”这一特性，是通过在查询过程中为每一行生成一个特定的计算结果，并将这个结果与该行的其他数据一起返回。下面是详细的步骤解释：

• 定义窗口：在OVER()子句中使用PARTITION BY和ORDER BY来定义窗口。这决定了窗口函数作用的数据范围和顺序。
• 窗口函数计算：窗口函数根据定义的窗口对数据进行计算。计算是针对窗口内的每一行进行的，但结果与当前行相关联。
• 结果关联：窗口函数为每一行生成一个结果，这个结果是基于窗口内所有行的数据计算得出的。然后，这个结果被关联到产生该结果的当前行。
• 返回结果集：最终，查询返回一个结果集，其中每一行包含了原始数据列以及窗口函数生成的附加列（如上述例子中的salary_rank）。

以RANK()函数为例，当执行包含窗口函数的查询时，数据库会：

    根据PARTITION BY department_id将数据分成不同的分区，每个分区对应一个部门。
     在每个分区内部，根据ORDER BY salary DESC对员工按工资降序排序。
     对于每个分区中的每行（每个员工），RANK()函数会计算一个排名值，这个值是基于当前行的工资以及同一分区（同一部门）内其他行的工资计算得出的。

将计算得到的排名值作为新列（salary_rank）添加到结果集中，与当前行的其他数据（如employee_id和salary）一起返回。
这样，每个员工的salary_rank都是独立的，并且与他们自己的行相关联。即使两个员工的工资相同，他们的排名也可能不同（如果使用RANK()函数），因为RANK()函数在遇到排名相同的情况时会在下一个排名处留出空位。例如，如果两个员工的工资在部门内是最高的，他们将分别获得排名1和2，而下一个工资的员工将获得排名3。

    通过这种方式，窗口函数能够为每一行生成一个特定的、与之相关联的值，即使这些行在其他方面（如工资）可能完全相同。这是窗口函数强大功能的一部分，它允许在不改变原始数据的情况下，对数据进行复杂的分析和计算。