SQL 窗口函数是一组强大的工具，它允许你在数据库中进行复杂的数据分析和计算，而不需要将数据集聚合成单个值。窗口函数通过在数据的“窗口”上执行计算来工作，这个“窗口”是数据的一部分，它定义了在计算每行时要考虑的行集。

OVER 子句

OVER 子句是窗口函数的核心，它定义了窗口的大小和行的范围。OVER 子句可以与多个函数一起使用，包括聚合函数（如 SUM() 和 AVG()）和排名函数（如 ROW_NUMBER()、RANK() 和 DENSE_RANK()）。

排名函数

排名函数用于为数据集中的行分配一个排名。以下是一些常见的排名函数：

• ROW_NUMBER(): 为每一行分配一个唯一的连续整数。
• RANK(): 为每一行分配一个排名，相同值的行会有相同的排名，并且排名之间会有间隙。
• DENSE_RANK(): 与 RANK() 类似，但排名之间没有间隙。

示例数据表

假设我们有一个 Sales 数据表，其中包含以下列和数据：

SaleID	EmployeeName	SaleAmount	SaleDate
1	John	500	2023-01-01
2	Jane	300	2023-01-02
3	John	200	2023-01-02
4	Jane	400	2023-01-03
5	John	600	2023-01-03

表结构与数据脚本

SQL
-- 创建表
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    SaleAmount INT,
    SaleDate DATE
);

-- 插入数据
INSERT INTO Sales (SaleID, EmployeeName, SaleAmount, SaleDate)
VALUES (1, 'John', 500, '2023-01-01'),
       (2, 'Jane', 300, '2023-01-02'),
       (3, 'John', 200, '2023-01-02'),
       (4, 'Jane', 400, '2023-01-03'),
       (5, 'John', 600, '2023-01-03');

使用窗口函数

1. ROW_NUMBER()

SQL
SELECT SaleID, EmployeeName, SaleAmount, SaleDate,
       ROW_NUMBER() OVER (ORDER BY SaleAmount DESC) AS RowNum
FROM Sales;

这个查询将为 Sales 表中的每一行分配一个行号，基于 SaleAmount 降序排列。

结果可能如下：

SaleID	EmployeeName	SaleAmount	SaleDate	RowNum
5	John	600	2023-01-03	1
1	John	500	2023-01-01	2
4	Jane	400	2023-01-03	3
2	Jane	300	2023-01-02	4
3	John	200	2023-01-02	5

2. RANK() 和 DENSE_RANK()

SQL
SELECT SaleID, EmployeeName, SaleAmount, SaleDate,
       RANK() OVER (ORDER BY SaleAmount DESC) AS Rank,
       DENSE_RANK() OVER (ORDER BY SaleAmount DESC) AS DenseRank
FROM Sales;

这个查询将根据 SaleAmount 降序排列为每一行分配一个排名。如果两行的 SaleAmount 相同，它们将获得相同的排名，但 RANK() 会在排名数字之间留下间隙，而 DENSE_RANK() 不会。

RANK()函数用于计算结果集中每一行的排名，排名相同的行将具有相同的排名值，而下一个排名将被跳过。例如，如果有两个行具有相同的排名，则下一个排名将是排名的数量加上2。这种情况下，排名不是连续的。

DENSE_RANK()函数也用于计算结果集中每一行的排名，但是排名相同的行将具有相同的排名值，并且下一个排名将按顺序递增。这意味着排名是连续的，没有跳过。

结果可能如下：

SaleID	EmployeeName	SaleAmount	SaleDate	Rank	DenseRank
5	John	600	2023-01-03	1	1
1	John	500	2023-01-01	2	2
4	Jane	400	2023-01-03	3	3
2	Jane	300	2023-01-02	4	4
3	John	200	2023-01-02	5	5

3. 聚合窗口函数

SQL
SELECT SaleID, EmployeeName, SaleAmount, SaleDate,
       SUM(SaleAmount) OVER (ORDER BY SaleAmount RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Sales;

在这个例子中，我们计算了截至当前行（包括当前行）的销售总额的累计总和。RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 指定了窗口的范围，从分区的第一行到当前行。

在RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW中，UNBOUNDED PRECEDING表示窗口的起始行是无限制的，而CURRENT ROW表示窗口的结束行是当前行。

这意味着在窗口函数计算时，会将从起始行（第一行）到当前行的所有行作为窗口的范围。这样可以确保窗口函数计算的结果只考虑当前行及其之前的行。

结果可能如下：

总结

窗口函数提供了一种强大的方法来执行复杂的数据分析，而不需要对数据进行分组或创建多个查询。通过熟练使用 OVER 子句和排名函数，你可以在 SQL 中执行高级的数据操作，从而获得深入的洞察和有用的结果。