准备好，我们将要学习的是 SQL 中最强大、最神奇，也最能体现 SQL 分析能力的特性之一——窗口函数 (Window Functions)。

在第 10 章，我们学习了聚合函数（如 SUM, COUNT），它们会将多行数据“压缩”成一行结果。比如，SUM(sales) 会返回一个总销售额的数值。

但如果我想提出这样的问题呢？

• “我想看每个员工的薪水，同时还想看他所在部门的平均薪水是多少？”
• “我想按销售额对所有销售员进行排名。”
• “我想看每个月的销售额，以及与上个月相比的增长率。”

这些问题，用传统的聚合函数 + GROUP BY 很难（甚至不可能）优雅地解决。因为 GROUP BY 会把原始的行给“压扁”，而我们希望在保留原始行的同时，进行聚合或排序计算。

窗口函数就是为了解决这类问题而生的。它允许我们对一组与当前行相关的行（这个“组”就被称为“窗口”）进行计算，但不改变原始查询返回的行数。

15.1 窗口函数入门：OVER()子句

窗口函数的核心标志就是 OVER() 子句。一个函数后面只要跟了 OVER()，它就从一个普通函数或聚合函数，变身为一个窗口函数。

基本语法:

SELECT
    column1,
    function_name() OVER (
        [PARTITION BY partition_expression, ...]
        [ORDER BY sort_expression [ASC | DESC], ...]
        [frame_clause]
    ) AS new_column
FROM table_name;

OVER() 括号里的三个部分是可选的，也是窗口函数强大能力的来源：

• PARTITION BY: 分区。它把数据行分成不同的“桶”或“组”（即窗口）。窗口函数将独立地在每个分区内进行计算。这有点像 GROUP BY，但它不压缩行。
• ORDER BY: 排序。它定义了在每个分区内部，行的计算顺序。这对于排名、计算行间差异等至关重要。
• frame_clause: 窗口帧。它更精确地定义了在当前行周围，哪些行被包含进计算范围（比如“当前行和它前面的两行”）。这个比较高级，我们暂时先不深入。

准备工作：创建销售数据表

为了演示窗口函数，我们需要一个更适合分析的表。

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    sale_amount NUMERIC(10, 2),
    sale_date DATE
);

INSERT INTO sales (sale_id, employee_name, department, sale_amount, sale_date) VALUES
(1, '张三', '电子产品', 1500.00, '2023-01-05'),
(2, '李四', '电子产品', 2200.50, '2023-01-15'),
(3, '王五', '家居用品', 800.00, '2023-01-20'),
(4, '张三', '电子产品', 1800.00, '2023-02-05'),
(5, '赵六', '家居用品', 950.50, '2023-02-10'),
(6, '李四', '电子产品', 2500.00, '2023-02-25'),
(7, '王五', '家居用品', 750.00, '2023-03-05'),
(8, '张三', '服装', 500.00, '2023-03-10');

15.2 聚合窗口函数：在保留行的同时进行聚合

问题：我想看每一笔销售记录，同时想知道该销售员的总销售额，以及他所在部门的总销售额。

SELECT
    employee_name,
    department,
    sale_amount,
    -- 计算每个员工的总销售额
    SUM(sale_amount) OVER (PARTITION BY employee_name) AS employee_total_sales,
    -- 计算每个部门的总销售额
    SUM(sale_amount) OVER (PARTITION BY department) AS department_total_sales,
    -- 计算全公司的总销售额 (OVER() 为空，表示窗口是所有行)
    SUM(sale_amount) OVER () AS company_total_sales
FROM
    sales;

执行过程揭秘 :

• SUM(...) OVER (PARTITION BY employee_name):PARTITION BY employee_name 把数据按员工姓名分成了几个区：“张三”区、“李四”区、“王五”区…对于“张三”区的每一行，SUM(sale_amount) 都会计算这个区内所有 sale_amount 的总和 (1500 + 1800 + 500 = 3800)。对于“李四”区的每一行，计算结果都是 2200.50 + 2500 = 4700.50。
• SUM(...) OVER (PARTITION BY department) 的逻辑完全一样，只是分区键换成了 department。
• SUM(...) OVER (): OVER() 为空，代表窗口包含查询结果的所有行。所以每一行显示的都是公司总销售额。

执行结果 (部分):

 employee_name | department | sale_amount | employee_total_sales | department_total_sales | company_total_sales
---------------+------------+-------------+----------------------+------------------------+---------------------
 张三          | 电子产品   |     1500.00 |              3800.00 |                8000.50 |            10201.00
 李四          | 电子产品   |     2200.50 |              4700.50 |                8000.50 |            10201.00
 王五          | 家居用品   |      800.00 |              1550.00 |                2500.50 |            10201.00
 ...

看到没？我们没有丢失任何一行的原始信息，但却给每一行都附加上了强大的聚合统计数据！

15.3 排名函数：ROW_NUMBER,RANK,DENSE_RANK

排名是窗口函数最经典的应用之一。

• ROW_NUMBER(): 无论值是否相同，都分配一个连续的、不重复的排名 (1, 2, 3, 4, …)。
• RANK(): 如果值相同，排名就相同，但下一个排名会跳过空缺的数字 (1, 2, 2, 4, …)。
• DENSE_RANK(): 如果值相同，排名就相同，且下一个排名不会跳过 (1, 2, 2, 3, …)。

问题：我们想在每个部门内部，根据销售额对员工进行排名。

SELECT
    employee_name,
    department,
    sale_amount,
    -- 在每个部门内，按销售额降序排名
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rn,
    RANK()       OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS dr
FROM
    sales;

• PARTITION BY department: 保证排名是在“部门内部”进行的。
• ORDER BY sale_amount DESC: 定义了排名的依据是“销售额降序”。

执行结果 (只看电子产品部门):
假设电子产品部门有如下销售额：2500, 2200.50, 1800, 1500。
如果再加一笔 2500 的销售，结果会是：

 sale_amount | rn | rk | dr
-------------+----+----+----
     2500.00 |  1 |  1 |  1
     2500.00 |  2 |  1 |  1  -- 销售额相同
     2200.50 |  3 |  3 |  2  -- 注意 rk 跳过了2，而 dr 没有
     1800.00 |  4 |  4 |  3
     1500.00 |  5 |  5 |  4

15.4 其他有用的窗口函数

• LAG(column, offset, default): 获取当前行之前第 offset 行的 column 值。
• LEAD(column, offset, default): 获取当前行之后第 offset 行的 column 值。

问题：计算每笔销售额相比于该员工上一笔销售额的变化。

SELECT
    employee_name,
    sale_date,
    sale_amount,
    -- 获取该员工上一笔销售的金额
    LAG(sale_amount, 1, 0.00) OVER (PARTITION BY employee_name ORDER BY sale_date) AS previous_sale,
    -- 计算差值
    sale_amount - LAG(sale_amount, 1, 0.00) OVER (PARTITION BY employee_name ORDER BY sale_date) AS difference
FROM
    sales;

• PARTITION BY employee_name: 保证“上一笔”是在同一个员工内部找。
• ORDER BY sale_date: 定义了“上一笔”是按时间顺序来的。
• LAG(sale_amount, 1, 0.00): 取 sale_amount 列，往前偏移 1 行，如果前面没有行（即第一笔销售），则默认值为 0.00。

本章小结

你已经登上了 SQL 查询分析的顶峰！窗口函数是一个极其强大的工具，它打开了一个全新的数据分析维度。

• 我们理解了窗口函数的核心——OVER() 子句，以及它如何让我们在不“压扁”数据的情况下进行聚合计算。
• 学会了使用 PARTITION BY 来分区，在独立的窗口内进行计算。
• 掌握了 ROW_NUMBER, RANK, DENSE_RANK 等排名函数。
• 还学会了使用 LAG 和 LEAD 在行与行之间进行比较。

掌握了窗口函数，你就能用纯 SQL 解决许多过去可能需要用 Python 或其他编程语言才能解决的复杂分析问题。

在下一章，我们将回到数据类型这个基础但同样重要的话题上，学习一些 PostgreSQL 提供的更高级、更具特色的数据类型，如 SERIAL, UUID, JSONB 等。准备好给你的数据表装备更精良的“武器”了吗？我们下一章见！