SQL必学必会之窗口函数

1.窗口函数基础

窗口函数的基本语法如下：

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

其中，partition by用于对表进行分组，而不会减少原表的行数。例如，按班级分组可以使用 partition by 班级。

order by则用于对分组后的结果进行排序，可以选择升序（asc）或降序（desc）。例如，order by 成绩 desc 表示按成绩降序排列。

窗口函数的优势在于它们能够同时实现分组和排序的功能，而不像 group by 子句那样减少表的行数。这意味着你可以在不失去原始数据的情况下执行聚合和排名操作。

2.窗口函数示例

示例：统计每个班级的人数

让我们看一个示例，假设我们要统计每个班级的学生人数。使用窗口函数，我们可以轻松实现这一目标：

SELECT *,
       COUNT(*) OVER (PARTITION BY 班级) AS 班级人数
FROM 学生表

在这个示例中，COUNT(*) 函数作为窗口函数，使用 PARTITION BY 班级 实现了按班级分组并计算每个班级的学生人数，同时原始表的行数依旧不变，而每行数据则增加了一列新列`班级人数`。

3.窗口函数的三种排序方式

窗口函数有多种类型，包括 rank()、dense_rank() 和 row_number()。它们在处理并列名次时的行为不同：

rank()

函数会占用下一名次的位置，如果有并列名次的行。例如，如果前3名是并列的名次，结果将是 1，1，1，4。

dense_rank()

函数不会占用下一名次的位置，如果有并列名次的行。例如，前3名是并列的名次，结果是 1，1，1，2。

row_number()

函数不考虑并列名次，排名是正常的。例如，前3名是并列的名次，排名是正常的 1，2，3，4。

窗口函数还可以与聚合函数一起使用，以在结果中执行聚合操作，如总和、平均、计数、最大和最小值。以下是示例：

SELECT *,
       SUM(成绩) OVER (ORDER BY 学号) AS 当前总分,
       AVG(成绩) OVER (ORDER BY 学号) AS 当前平均分,
       COUNT(成绩) OVER (ORDER BY 学号) AS 当前人数,
       MAX(成绩) OVER (ORDER BY 学号) AS 最高分,
       MIN(成绩) OVER (ORDER BY 学号) AS 最低分
FROM 班级表

在此示例中，我们使用窗口函数将聚合函数应用于成绩，并按学号排序，以计算每个学生的当前总分、平均分、人数、最高分和最低分。

4.结论

窗口函数是 SQL 查询中强大的工具，它们允许我们在不减少原始数据行数的情况下执行分组、排序和聚合操作。通过了解窗口函数的基本语法和应用，您可以更灵活地分析数据和生成报告。无论是数据分析师还是数据库开发人员，掌握窗口函数都是一个有用的技能，可以大大简化复杂查询的编写和理解。