SQL 窗口函数

窗口函数允许用户在与当前行相关联的多个表行上执行计算。它类似于聚合函数，因为也用于计算。窗口函数与任何其他常规聚合函数的唯一区别在于，当实现时，窗口函数不会将行分组为单个输出行。所执行操作的行的身份保持不同。该函数可以轻松处理查询结果的当前行以外的行。

让我们以计算运行总数使用窗口函数的示例为例。首先，我们将创建一个用于执行SQL窗口函数的表。这里我们将使用Student表并实现这个操作。

表如下：

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 窗口函数

实现以下SQL查询以对表中的S_Age列进行聚合。

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子句中使用窗口函数。窗口函数的一些示例包括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(): 它为分区中的每一行分配一个排名。它的工作原理与RANK()函数类似，即每个分区中的第一行将被分配为排名1，并且如果两行具有相同的值，则它们将具有相同的排名。RANK()函数和DENSE_RANK()函数之间的区别在于，如果两行在DENSE_RANK中具有相同的排名，则下一行将被分配为连续的下一个数字作为下一条记录的排名。这意味着不会跳过任何排名。

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

注意：在SQL中实现排名窗口函数时，用户需要实现ORDER BY子句。

例如，

根据S_Age计算Student表中学生的行号、排名和密集排名。

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 窗口函数

使用分区子句实现排名窗口函数

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 窗口函数