课程 7.1 · 阅读时间：约 8 分钟

本课程介绍 SQL 窗口函数，并展示它们如何在添加分析上下文的同时保持行级细节。您将学习 OVER、PARTITION BY 和 ORDER BY 如何协同工作，课程结束时您将能够在 Sakila 上构建基本的窗口查询。

高级数据分析的窗口函数

窗口函数是 SQL 中执行复杂分析计算的最强大特性之一。与将多行合并为单个结果的聚合函数不同，窗口函数允许您在与当前行相关的一组行上执行计算，同时在结果集中保留单独的行。

本课程介绍窗口函数的基本概念，并演示它们如何转变您的数据分析能力。

什么是窗口函数？

窗口函数在一组与当前行相关的表行上执行计算。这组行称为“窗口”或“窗口框架”。与常规聚合函数的关键区别在于，窗口函数不会导致行被分组为单个输出行——每行保留其身份。

可以把它想象成在扫描数据时通过一个移动窗口查看。对于每一行，您可以看到并计算与其相关的行的值，但每一行在结果中仍然单独出现。

关键特性：

• 窗口函数在由 OVER 子句定义的一组行上操作
• 它们为结果集中的每一行返回一个值
• 它们不会减少查询返回的行数
• 它们可以用于排名、聚合和分析操作

基本语法

窗口函数的一般语法为：

window_function_name(expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [window_frame_clause]
)

组成部分：

• window_function_name: 要应用的函数（例如，ROW_NUMBER、SUM、AVG）
• OVER 子句: 定义函数的行窗口
• PARTITION BY（可选）: 将结果集划分为分区（组）
• ORDER BY（可选）: 定义每个分区内的行顺序
• window_frame_clause（可选）: 进一步细化窗口中包含的行

您的第一个窗口函数：ROW_NUMBER()

让我们从最常用的窗口函数之一开始：ROW_NUMBER()。此函数为每个分区内的每一行分配一个唯一的顺序编号。

示例 1：为所有付款编号

SELECT
    payment_id,
    customer_id,
    amount,
    payment_date,
    ROW_NUMBER() OVER (ORDER BY payment_date) AS row_num
FROM
    payment
LIMIT 10;

此查询为按付款日期排序的每个付款分配一个顺序编号。OVER (ORDER BY payment_date) 子句告诉 SQL：

1. 按 payment_date 排序所有行
2. 从 1 开始分配行号

示例 2：使用 PARTITION BY 在组内编号

窗口函数的真正强大之处在于使用 PARTITION BY 为不同组创建单独的窗口：

SELECT
    customer_id,
    amount,
    payment_date,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY payment_date
    ) AS payment_number
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id, 
    payment_date;

发生的事情是：

• PARTITION BY customer_id 为每个客户创建一个单独的窗口
• 在每个客户的窗口内，行按 payment_date 排序
• ROW_NUMBER() 从每个新客户的 1 开始计数
• 这使您能够看到每个客户的第 1、2、3 次付款

可视化：

客户 1:     客户 2:     客户 3:
行 1 ----\     行 1 ----\     行 1 ----\
行 2 -----\    行 2 -----\    行 2 -----\
行 3 ------\   行 3 ------\   行 3 ------\
   ...           ...             ...

每个客户都有自己独立的行编号。

实际应用

查找最近的交易

窗口函数使得识别每个组中最近记录变得简单：

WITH numbered_payments AS (
    SELECT
        customer_id,
        amount,
        payment_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY payment_date DESC
        ) AS recency_rank
    FROM
        payment
)
SELECT
    customer_id,
    amount,
    payment_date
FROM
    numbered_payments
WHERE
    recency_rank = 1
ORDER BY
    customer_id
LIMIT 10;

此查询通过：

1. 按降序日期为每个客户编号付款
2. 过滤 recency_rank = 1（最近的）

将每行与聚合值进行比较

窗口函数还可以在保留单独行的同时执行聚合：

SELECT
    customer_id,
    amount,
    payment_date,
    SUM(amount) OVER (PARTITION BY customer_id) AS total_spent,
    AVG(amount) OVER (PARTITION BY customer_id) AS avg_payment,
    amount - AVG(amount) OVER (PARTITION BY customer_id) AS diff_from_avg
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id,
    payment_date;

对于每个付款，此查询显示：

• 单个付款金额
• 此客户在所有付款中花费的总金额
• 此客户的平均付款金额
• 此特定付款与其平均值的差异

请注意，常规聚合函数需要 GROUP BY 并合并行，但窗口函数让您在添加聚合上下文的同时保留所有细节。

窗口函数与 GROUP BY

理解两者之间的区别很重要：

GROUP BY（聚合函数）：

SELECT
    customer_id,
    COUNT(*) AS payment_count,
    SUM(amount) AS total_amount
FROM
    payment
GROUP BY
    customer_id;

结果：每个客户一行

窗口函数：

SELECT
    customer_id,
    payment_id,
    amount,
    COUNT(*) OVER (PARTITION BY customer_id) AS payment_count,
    SUM(amount) OVER (PARTITION BY customer_id) AS total_amount
FROM
    payment;

结果：每个付款行保留，聚合值作为额外列添加

常见问题

为什么使用窗口函数而不是 GROUP BY？

GROUP BY 对于汇总表很有用，但它隐藏了行级细节。窗口函数让您保留每一行，并在其旁边添加聚合上下文。

PARTITION BY 是必需的吗？

不是。如果省略它，整个结果集将成为一个分区。当您想要在所有行中进行排名或度量时，这很有用。

窗口函数可以与 WHERE 结合使用吗？

可以。WHERE 在窗口计算运行之前过滤行，因此您首先选择相关数据，然后计算窗口。

面试问题

窗口函数和聚合函数之间的主要区别是什么？

使用 GROUP BY 的聚合会减少行，而窗口函数为每一行返回一个值并保留细节。

PARTITION BY 在窗口中做什么？

它将结果集分成独立的部分，窗口函数在每个部分内单独评估。

何时使用 ROW_NUMBER？

当您需要在组内进行行编号、获取最新或第一行，或在每个类别中进行前 N 选择时使用。

关键要点

• 窗口函数 在相关行上执行计算，同时保持结果集中的所有单独行。
• OVER 子句 是必不可少的，定义了窗口函数操作的行窗口。
• PARTITION BY 将数据划分为组，窗口函数在每个组内单独应用。
• ORDER BY 在 OVER 子句中确定函数的行顺序（对像 ROW_NUMBER() 这样的函数至关重要）。
• 窗口函数非常适合排名、运行总计、移动平均以及将单个值与组聚合进行比较。
• 与 GROUP BY 不同，窗口函数不会合并行——它们将计算列添加到现有数据中。

在接下来的课程中，我们将探索更多窗口函数，如 RANK()、DENSE_RANK()、NTILE()，并深入研究窗口框架和高级分析计算。