【MySQL】窗口函数原理，与where、group by关系
一、窗口函数是什么窗口函数（Window Function）是一种特殊的数据库查询函数，它允许你对数据集的一个子集（窗口）实行盘算，而不是整个数据集。窗口函数常用于SQL查询中，用来实行复杂的分析任务，比如盘算累计总和、移动平均值、排名等。
窗口函数的实行原理通常包含以下几个关键步骤：
[*] 定义窗口:起首，你需要定义一个窗口，它决定了窗口函数将作用的数据范围。窗口可以基于行号、分区键或排序键来定义。
[*] 指定窗口函数：选择一个窗口函数，如SUM()、AVG()、RANK()等，来对窗口内的数据举行盘算。
[*] 应用排序：窗口函数通常需要一个排序顺序，这决定了数据在窗口中的排列方式。排序可以基于一个或多个列。
[*] 实行盘算：根据定义的窗口和排序，窗口函数对窗口内的数据举行盘算。
[*] 返回结果：窗口函数返回盘算结果，通常与其他列的数据一起返回。
下面是一个简朴的SQL窗口函数的例子，演示了如何利用窗口函数来盘算员工工资的累计总和：
SELECT
employee_id,
salary,
SUM(salary) OVER (ORDER BY 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 (PARTITION BY department_id ORDER BY 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。
通过这种方式，窗口函数能够为每一行生成一个特定的、与之相干联的值，纵然这些行在其他方面（如工资）可能完全相同。这是窗口函数强盛功能的一部分，它允许在不改变原始数据的情况下，对数据举行复杂的分析和盘算。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]