摘要：“我想看每个员工的薪水，同时还想看他所在部门的平均薪水是多少？”“我想按销售额对所有销售员进行排名。”“我想看每个月的销售额，以及与上个月相比的增长率。”
准备好，我们将要学习的是 SQL 中最强大、最神奇，也最能体现 SQL 分析能力的特性之一——窗口函数 (Window functions)。
在第 10 章，我们学习了聚合函数（如 SUM, COUNT），它们会将多行数据“压缩”成一行结果。比如，SUM(sales) 会返回一个总销售额的数值。
但如果我想提出这样的问题呢？
“我想看每个员工的薪水，同时还想看他所在部门的平均薪水是多少？”“我想按销售额对所有销售员进行排名。”“我想看每个月的销售额，以及与上个月相比的增长率。”这些问题，用传统的聚合函数 + GROUP BY 很难（甚至不可能）优雅地解决。因为 GROUP BY 会把原始的行给“压扁”，而我们希望在保留原始行的同时，进行聚合或排序计算。
窗口函数就是为了解决这类问题而生的。它允许我们对一组与当前行相关的行（这个“组”就被称为“窗口”）进行计算，但不改变原始查询返回的行数。
窗口函数的核心标志就是 OVER 子句。一个函数后面只要跟了 OVER，它就从一个普通函数或聚合函数，变身为一个窗口函数。
基本语法:
SELECT column1, function_name OVER ( [PARTITION BY partition_expression, ...] [ORDER BY sort_expression [ASC | DESC], ...] [frame_clause] ) AS new_columnFROM 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');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_salesFROM 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 ...看到没？我们没有丢失任何一行的原始信息，但却给每一行都附加上了强大的聚合统计数据！
排名是窗口函数最经典的应用之一。
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 drFROM sales;PARTITION BY department: 保证排名是在“部门内部”进行的。ORDER BY sale_amount DESC: 定义了排名的依据是“销售额降序”。执行结果 (只看电子产品部门):
假设电子产品部门有如下销售额：2500, 2200.50, 1800, 1500。
如果再加一笔 2500 的销售，结果会是：
问题：计算每笔销售额相比于该员工上一笔销售额的变化。
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 differenceFROM 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 等。准备好给你的数据表装备更精良的“武器”了吗？我们下一章见！✨
来源：linux运维菜