解析SQL中的开窗函数-猿码集

1. 开窗函数简介

开窗函数是SQL中一种特殊的函数，不同于普通的聚合函数，它能够对特定的行进行聚合处理。开窗函数还具备窗口（Window）的概念，可以将被聚合的行集合限制在一个特定的窗口内，这个窗口可以是整个结果集，也可以是特定的分组结果。

开窗函数通常被用于分析性的场景，如计算当前行的排名（RANK）、计算滑动窗口的聚合值等。使用开窗函数需要在SELECT语句中使用OVER()子句来指定窗口，可以通过窗口内的ORDER BY子句、PARTITION BY子句、ROWS或RANGE子句来定义窗口的分组、排序和范围。

下面我们深入了解开窗函数的常见用法。

2. 窗口函数常见用法

2.1 ROW_NUMBER()

ROW_NUMBER()函数用于为结果集中的行分配一个唯一的序号。该序号基于指定的排序规则进行排列。在进行分页操作时，该函数非常有用。


SELECT ROW_NUMBER() OVER(ORDER BY score DESC) as rowNum, name, score
FROM student
WHERE class='A'

上述语句将会给学生按照成绩从高到低排序后，为每个学生分配一个唯一的序号。

2.2 RANK()

RANK()函数也可以用来给结果集中的行分配一个唯一的序号，但与ROW_NUMBER()函数不同的是，RANK()函数会跳过相同的排名，因此可能出现重复的排名。


SELECT RANK() OVER(ORDER BY score DESC) as rank, name, score
FROM student
WHERE class='A'

上述语句将会给学生按照成绩从高到低排序后，为每个学生分配一个排名。

2.3 DENSE_RANK()

DENSE_RANK()函数也是为结果集中的行分配排名，与RANK()函数不同的是，DENSE_RANK()函数不会跳过相同的排名，因此不会出现重复的排名。


SELECT DENSE_RANK() OVER(ORDER BY score DESC) as denseRank, name, score
FROM student
WHERE class='A'

上述语句将会给学生按照成绩从高到低排序后，为每个学生分配一个紧密的排名。

2.4 SUM()

SUM()函数用于计算结果集中特定列的值之和。使用开窗函数后，可以对特定的行进行聚合处理，而不是对整个结果集进行求和。


SELECT name, score, SUM(score) OVER(PARTITION BY class) as classSum
FROM student

上述语句将会给学生按照班级分组，计算每个班级的总分。开窗函数中使用了PARTITION BY子句来指定分组依据。

2.5 AVG()

AVG()函数用于计算结果集中特定列的值的平均值。同样，使用开窗函数后，可以对特定的行进行聚合处理。


SELECT name, score, AVG(score) OVER(PARTITION BY class) as classAvg
FROM student

上述语句将会给学生按照班级分组，计算每个班级的平均分。

3. 窗口函数进阶应用

3.1 滑动窗口

有时候，需要对一个连续的窗口内的行进行聚合处理。这时，可以使用ROWS或RANGE子句来定义窗口的范围。

假设我们现在需要计算每个学生的3次考试的总成绩。可以通过以下语句实现：


SELECT name, score,
SUM(score) OVER(
    PARTITION BY name 
    ORDER BY examDate 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as examSum 
FROM exam

上述语句将会给考试按照学生进行分组，然后对每个学生的考试成绩进行滑动窗口聚合，取该行、前一行和前两行的考试成绩计算总成绩。

3.2 比较历史数据

有时候，需要将当前行与历史行进行比较。可以使用LAG()或LEAD()函数来访问前/后一行的数据。

假设我们现在需要计算每个学生的本次考试与上次考试的分数差距。可以通过以下语句实现：


SELECT name, examDate, score, 
LAG(score) OVER(
    PARTITION BY name 
    ORDER BY examDate 
) as prevScore,
score - LAG(score) OVER(
    PARTITION BY name 
    ORDER BY examDate 
) as scoreIncrement 
FROM exam

上述语句将会给考试按照学生进行分组，然后使用LAG()函数查找每个学生上次考试的成绩，计算与本次成绩的差距。

3.3 ntile()

ntile()函数用于将结果集中的行分配到n个等值的桶中。使用开窗函数后，可以在某个分组或整个结果集中进行排名或分组操作。


SELECT name, score,
NTILE(4) OVER(ORDER BY score DESC) as quartile
FROM student

上述语句将会给学生按照成绩从高到低排序后，将其分配到4个等值的组中，并标记对应的组号。

4. 总结

开窗函数是一种非常有用的SQL语法，能够在分析性场景下很好地处理数据。本文介绍了开窗函数的几种常见用法，包括ROW_NUMBER()、RANK()、DENSE_RANK()、SUM()、AVG()、滑动窗口、比较历史数据、ntile()等。在实际工作中，可以根据具体需求在开窗函数中添加ORDER BY子句、PARTITION BY子句、ROWS或RANGE子句等来定义窗口，实现更加复杂的数据分析操作。

解析SQL中的开窗函数