跳到主要内容

SQL 窗口函数基础

SQL窗口函数（Window Functions）是一种强大的工具，允许你在不改变查询结果集的情况下，对数据进行分组、排序和计算。与聚合函数不同，窗口函数不会将多行合并为一行，而是为每一行返回一个计算结果。这使得窗口函数非常适合用于排名、累积计算、移动平均等场景。

什么是窗口函数？​

窗口函数是一种特殊的SQL函数，它可以在一个“窗口”（即一组相关的行）上执行计算。这个窗口可以是一个分区（partition）、一个排序（order）或一个范围（range）。窗口函数的关键特点是，它不会减少查询结果的行数，而是为每一行返回一个计算结果。

基本语法​

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

sql
<窗口函数> OVER (
    [PARTITION BY <列名>]
    [ORDER BY <列名>]
    [ROWS BETWEEN <起始行> AND <结束行>]
)
  • PARTITION BY：将数据分成多个分区，窗口函数会在每个分区内独立计算。
  • ORDER BY：指定分区内的排序方式。
  • ROWS BETWEEN：定义窗口的范围，可以是当前行的前后几行，或者整个分区。

常见的窗口函数​

以下是一些常见的窗口函数：

  1. ROW_NUMBER()：为每一行分配一个唯一的序号。
  2. RANK()：为每一行分配一个排名，相同值的行会得到相同的排名，后续排名会跳过。
  3. DENSE_RANK()：与RANK()类似，但不会跳过后续排名。
  4. SUM()、AVG()、MIN()、MAX()：在窗口内计算聚合值。
  5. LEAD() 和 LAG()：访问当前行的前一行或后一行的数据。

示例：使用窗口函数​

假设我们有一个销售数据表 sales，结构如下：

sql
CREATE TABLE sales (
    id INT,
    salesperson VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10, 2)
);

示例1：计算每个销售人员的累计销售额​

sql
SELECT 
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS cumulative_sales
FROM 
    sales;

输出：

salespersonsale_dateamountcumulative_sales
Alice2023-01-01100.00100.00
Alice2023-01-02150.00250.00
Bob2023-01-01200.00200.00
Bob2023-01-03300.00500.00

在这个例子中，SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) 计算了每个销售人员的累计销售额。

示例2：计算每个销售人员的排名​

sql
SELECT 
    salesperson,
    sale_date,
    amount,
    RANK() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS sales_rank
FROM 
    sales;

输出：

salespersonsale_dateamountsales_rank
Alice2023-01-02150.001
Alice2023-01-01100.002
Bob2023-01-03300.001
Bob2023-01-01200.002

在这个例子中，RANK() 函数根据销售额对每个销售人员进行排名。

实际应用场景​

场景1：计算移动平均​

假设你想计算每个销售人员的7天移动平均销售额：

sql
SELECT 
    salesperson,
    sale_date,
    amount,
    AVG(amount) OVER (
        PARTITION BY salesperson 
        ORDER BY sale_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM 
    sales;

场景2：查找每个销售人员的最大销售额​

sql
SELECT 
    salesperson,
    sale_date,
    amount,
    MAX(amount) OVER (PARTITION BY salesperson) AS max_sale
FROM 
    sales;

总结​

SQL窗口函数是一种强大的工具，可以帮助你在不改变查询结果集的情况下，对数据进行复杂的计算和分析。通过PARTITION BY、ORDER BY和ROWS BETWEEN等子句，你可以灵活地定义窗口的范围和排序方式，从而实现排名、累积计算、移动平均等功能。

提示

提示：窗口函数在处理时间序列数据、排名和累积计算时非常有用。熟练掌握窗口函数可以大大提升你的SQL数据分析能力。

附加资源与练习​

  1. 练习1：在sales表中，使用DENSE_RANK()函数为每个销售人员的销售额进行排名，并观察与RANK()的区别。
  2. 练习2：尝试使用LEAD()和LAG()函数，查找每个销售人员的下一个和前一个销售额。
  3. 资源：参考SQL窗口函数官方文档以了解更多高级用法。

通过不断练习和探索，你将能够更好地掌握SQL窗口函数，并在实际工作中灵活运用它们。