SQL 窗口函数

窗口函数允许用户在与当前行相关联的多个表行中进行计算。它类似于聚合函数，因为它也用于计算。窗口函数和其他常规聚合函数之间唯一的区别在于，当实现时，窗口函数不会将行分组为单个输出行。所执行操作的行的标识仍然是不同的。该函数可以轻松处理查询结果的当前行以外的更多行。

让我们以一个例子来计算使用窗口函数的累计总和。首先，我们将创建一个表来执行SQL窗口函数。在这里，我们将使用学生表并实施该操作。

表如下所示：

S_ID	S_Name	S_Dept	S_Country	S_Age
100	Aman	Science	India	19
105	Harsh	Maths	England	20
110	Harshit	Science	India	17
200	Kavya	Social Studies	Canada	18
120	Abhay	Maths	India	21
102	Ravi	English	India	22

使用下面的查询来定义和添加数据值以创建上述表格。

CREATE TABLE Student (
    S_ID INT,
    S_Name VARCHAR(50),
    S_Dept VARCHAR(50),
    S_Country VARCHAR(50),
    S_Age INT
);

INSERT INTO Student (S_ID, S_Name, S_Dept, S_Country, S_Age)
VALUES (100, 'Aman', 'Science', 'India', 19),
       (105, 'Harsh', 'Maths', 'England', 20),
       (110, 'Harshit', 'Science', 'India', 17),
       (200, 'Kavya', 'Social Studies', 'Canada', 18),
       (120, 'Abhay', 'Maths', 'India', 21),
       (102, 'Ravi', 'English', 'India', 22);

SELECT *
FROM Student;

输出：

SQL 窗口函数

在表中的 S_Age 列上执行下面的SQL查询来执行聚合操作。

SELECT S_Age, SUM(S_Age) OVER (ORDER BY S_ID) AS running_total
FROM Student;

输出:

SQL 窗口函数

如您可以在上图中看到的那样，计算聚合的查询不需要使用GROUP BY子句来实现。

让我们通过以下示例来学习实现窗口函数的语法并理解其功能。

窗口函数语法

窗口函数在特定的行窗口上执行聚合和排名操作。该函数是通过使用OVER子句来定义记录集来实现的。它执行两个功能：

PARTITION BY子句： 将行分成分区。分区指的是满足特定条件的一组行。

ORDER BY子句： 将特定分区内的行按顺序排列。

注意：如果用户在OVER子句中没有指定分区，则ORDER BY子句将对表中的所有记录实施。

实现窗口函数的语法如下所示：

SELECT coulmn_name1, 
 window_function(cloumn_name2)
 OVER([PARTITION BY column_name1] [ORDER BY column_name3]) AS new_column
FROM table_name;

在这里，window_function指的是用户要实现的任何聚合或排名函数；column_name1是要选择的列，column_name2是要应用窗口函数的列。column_name3代表基于记录进行分区的列。new_column是新列的名称，table_name是包含数据的表的名称。

SUM函数是使用窗口函数计算聚合的第一个组件，其与任何其他聚合函数类似实现。当OVER子句与SUM函数一起使用时，它被指定为窗口函数。

SELECT S_Age, SUM(S_Age) OVER (ORDER BY S_ID) AS running_total
FROM Student;

上述查询可以转换成返回整个结果按记录开始的年龄总和。用户可以使用PARTITION BY子句将聚合的窗口限定为从完整数据库到数据库中的不同分组。查询将改为如下所示：

SELECT S_Age, SUM(S_Age) OVER (PARTITION BY S_Country ORDER BY S_ID) AS running_total
FROM Student
WHERE S_Age >= 20;

输出：

SQL 窗口函数

在上面的查询中，表根据学生的国家（S_Country）进行分组，并根据S_ID进行排序。针对所有大于或等于20的值，对S_Age列执行累计计算。

上面的查询根据学生所在的国家对其进行分组。这意味着来自不同国家的学生扮演不同的表。这意味着Sum函数将对所有分区的数据进行不同的聚合计算。

注意：在同一个查询中实现窗口函数和标准聚合是不可能的。因为用户无法在GROUP BY子句中使用Window函数。一些窗口函数的例子是SUM，COUNT和AVG。

实现聚合函数

在实现窗口函数时，用户还可以实现在任何其他场景中用户将要实现的相同聚合。这些窗口函数是SUM，COUNT和AVG。

SELECT
    S_ID,
    S_Name,
    S_Dept,
    S_Country,
    S_Age,
    SUM(S_Age) OVER (PARTITION BY S_Country ORDER BY S_ID) AS country_sum,
    COUNT(S_Age) OVER (PARTITION BY S_Country ORDER BY S_ID) AS country_count,
    AVG(S_Age) OVER (PARTITION BY S_Country ORDER BY S_ID) AS country_average
FROM Student;

输出：

SQL 窗口函数

ROW_NUMBER()

ROW_NUMBER（）是一个预定义的SQL函数，为每个记录的行分配连续的编号。它从1开始，并且后面的编号基于SQL语句中使用的ORDER BY子句进行分配。用户不需要在Row_Number（）函数中指定任何参数。

SELECT
ROW_NUMBER() OVER (
ORDER BY S_ID
) row_num,
S_Name
FROM
Student;

输出：

SQL 窗口函数

等级窗口函数

等级窗口函数包括以下函数:

RANK()

DENSE_RANK()

ROW_NUMBER()

RANK(): 该函数用于为每个分区中的所有记录分配等级。等级的分配是这样的，每个分区中的第一行的等级为1。下一条记录将被分配下一个连续的数字作为其等级。如果两行具有相同的值，则将分配相同的等级给记录。当两行具有相同的等级时，下一个等级将被跳过。

DENSE_RANK(): 它为分区中的每一行分配一个等级。它的工作方式与等级函数类似，即每个分区中的第一行将被分配等级1，并且如果两行具有相同的值，则它们将有相同的等级。RANK()和DENSE_RANK()函数之间的区别在于，如果两行在DENSE_RANK中具有相同的等级，则下一行将被分配下一个连续的数字作为下一个记录的等级。这意味着不会跳过任何等级。

ROW_NUMBER(): 它为表中的每条记录分配一个行号。连续的数字被分配给表中的所有记录。每个表中的排名从1开始。如果用户实现了分区，第一行将被分配1。同一分区中的两行不能具有相同的行号。

注意：在使用SQL实现等级窗口函数时，用户需要实现ORDER BY子句。

例如，

根据S_Age计算学生表中学生的行号、等级和密集等级。

SELECT
    S_ID,
    S_Name,
    S_Dept,
    S_Country,
    S_Age,
    RANK() OVER (ORDER BY S_Age DESC) AS age_rank,
    DENSE_RANK() OVER (ORDER BY S_Age DESC) AS age_dense_rank,
    ROW_NUMBER() OVER (ORDER BY S_Age DESC) AS age_row_number
FROM Student;

输出:

SQL 窗口函数

使用Partition By子句实现排名窗口函数

SELECT
    S_ID,
    S_Name,
    S_Dept,
    S_Country,
    S_Age,
    RANK() OVER (PARTITION BY S_Country ORDER BY S_Age DESC) AS country_age_rank,
    DENSE_RANK() OVER (PARTITION BY S_Country ORDER BY S_Age DESC) AS country_age_dense_rank,
    ROW_NUMBER() OVER (PARTITION BY S_Country ORDER BY S_Age DESC) AS country_age_row_number
FROM Student;

输出:

SQL 窗口函数