数据库窗口函数是什么？窗口函数的类型、语法及使用详解

窗口函数是sql中用于对一组相关行进行计算的工具，与group by不同，它保留原始行并为每行返回计算结果。1. 聚合窗口函数（如sum(), avg()）用于累计计算、移动平均和分组统计；2. 排名窗口函数（如row_number(), rank()）用于top n问题、竞赛排名和数据分桶；3. 值窗口函数（如lag(), lead()）用于环比分析、数据填充和区间比较。通过partition by定义逻辑分区，order by确定行顺序，rows/range控制帧范围，实现灵活的数据分析。

数据库窗口函数，简单来说，它是一种在SQL查询中对“一组”相关行进行计算的强大工具，但与传统的GROUP BY聚合不同，它不会将这些行合并成一行，而是为每一行都返回一个计算结果。这就像你站在一扇“窗口”前，透过它看到一部分数据，并基于这部分数据进行计算，而你本身（当前行）依然在结果集中。

解决方案

窗口函数的核心魅力在于，它让我们能在保留原始行粒度的同时，执行复杂的聚合、排名或值比较操作。想象一下，你有一张员工工资表，你不仅想知道每个员工的工资，还想知道他在部门内的排名，或者他比部门平均工资高多少，甚至他比上一个入职的同事工资多多少。传统SQL可能需要多步子查询或自连接才能勉强实现，而且效率低下，逻辑复杂。窗口函数则提供了一种优雅且高效的解决方案。

它通过OVER()子句定义了一个“窗口”，这个窗口可以是你整个结果集，也可以是根据某些列（比如部门ID）划分的逻辑分区，甚至可以是这个分区内根据某个顺序（比如入职日期）限定的更小的“帧”。所有计算都在这个定义的窗口内进行，结果附加到每一行上，而不是像GROUP BY那样将多行压缩成一行。这极大地扩展了SQL的表达能力，让数据分析变得更加灵活和直观。

窗口函数与传统聚合函数有何本质区别？

这个问题，其实是理解窗口函数的关键所在。我个人在刚接触窗口函数时，也曾纠结于它和GROUP BY聚合函数之间的关系。最直观的差异在于：传统聚合函数（如SUM(), AVG(), count()等）配合GROUP BY子句使用时，会把满足分组条件的行“折叠”成一行，你最终得到的是每个组的汇总结果，原始的行细节就丢失了。比如，你想知道每个部门的总工资，select department, SUM(salary) FROM employees GROUP BY department; 结果只有部门和总工资，看不到具体员工。

而窗口函数，虽然也执行聚合操作，但它是在一个“窗口”内进行计算，并将计算结果作为新的一列附加到每一行上。它不会减少你的结果集行数。举个例子，你仍然想知道每个部门的总工资，但同时又想看到每个员工自己的工资。使用窗口函数，你可以在 SELECT name, department, salary, SUM(salary) OVER (PARTITION BY department) AS department_total_salary FROM employees; 这样，你得到了每个员工的详细信息，并且每行都附带了其所在部门的总工资。这种“保留行细节，同时进行分组计算”的能力，是传统聚合函数无法比拟的，也是它在复杂报表和分析中不可或缺的原因。它更像是一种“行级增强”而非“行级汇总”。

数据库窗口函数有哪些常见类型及应用场景？

窗口函数的类型多样，每种都有其独特的应用场景，这正是它们强大之处的体现。我通常将它们分为几大类来理解：

1. 聚合窗口函数 (Aggregate Window Functions)： 这是最常用的一类，它们和我们熟悉的聚合函数同名，如 SUM(), AVG(), COUNT(), MAX(), MIN()。但它们后面跟着OVER()子句。

   • 应用场景：
     • 累计计算：计算运行总和（Running Total），比如销售额的每日累计，或者用户注册数的每月累计。
     • 移动平均：计算某段时间内的平均值，常用于趋势分析，如股票价格的5日移动平均。
     • 分组内的统计：比如计算每个学生在班级内的平均分，同时显示每个学生的具体分数。

   -- 示例：计算每个部门员工的累计工资（按入职日期排序） SELECT     employee_name,     department,     salary,     SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) AS cumulative_department_salary FROM     employees;

2. 排名窗口函数 (Ranking Window Functions)： 这类函数用于为分区内的行分配一个排名。

   • ROW_NUMBER(): 为分区内的每一行分配一个唯一的连续整数，没有并列。
   • RANK(): 为分区内的每一行分配一个排名，如果有相同的值，它们会得到相同的排名，但下一个不同的值会跳过相应数量的排名。
   • DENSE_RANK(): 类似于RANK()，但如果有相同的值，它们会得到相同的排名，下一个不同的值会得到紧邻的下一个排名，不会跳过。
   • NTILE(n): 将分区内的行分成n个组，并为每行分配其所属组的编号。
   • 应用场景：
     • Top N 问题：找出每个部门工资最高的3名员工。
     • 竞赛排名：根据分数对选手进行排名，处理并列情况。
     • 数据分桶：将数据按某种指标分成若干等份，如将客户按消费额分成高、中、低三档。

   -- 示例：找出每个部门工资排名前三的员工 SELECT * FROM (     SELECT         employee_name,         department,         salary,         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk     FROM         employees ) AS ranked_employees WHERE rnk <= 3;

3. 值窗口函数 (Value Window Functions)： 这类函数用于获取当前行在分区内的其他行的值。

   • LAG(expression, offset, default): 获取当前行之前指定偏移量（offset）的行的expression值。
   • LEAD(expression, offset, default): 获取当前行之后指定偏移量（offset）的行的expression值。
   • FIRST_VALUE(expression): 获取分区内第一行的expression值。
   • LAST_VALUE(expression): 获取分区内最后一行的expression值。
   • 应用场景：
     • 环比/同比分析：比较当前月份与上个月份的销售额差异。
     • 数据填充：用前一个有效值填充空值。
     • 区间比较：比较当前记录与分区内首尾记录的差异。

   -- 示例：计算每个月销售额与上个月的环比增长 SELECT     sale_month,     monthly_sales,     LAG(monthly_sales, 1, 0) OVER (ORDER BY sale_month) AS previous_month_sales,     (monthly_sales - LAG(monthly_sales, 1, 0) OVER (ORDER BY sale_month)) AS sales_growth FROM     sales_data;

这些只是冰山一角，实际应用中，它们可以组合使用，解决更复杂的业务问题。

如何理解并使用窗口函数的PARTITION BY、ORDER BY和ROWS/RANGE子句？

理解OVER()子句内部的这几个组件，是掌握窗口函数精髓的关键。它们共同定义了“窗口”的范围和顺序，决定了计算如何进行。

1. PARTITION BY子句： 这是定义“窗口”的第一步。它将你的数据集逻辑上分割成若干个独立的、不重叠的子集（即“分区”）。每个分区内的计算都是独立的，互不影响。你可以把它想象成在GROUP BY中进行分组，但区别在于，PARTITION BY并不会减少行数。

   • 作用：确定计算的“边界”。例如，PARTITION BY department意味着所有后续的窗口函数计算都只会在同一个部门内部进行。
   • 缺失情况：如果省略PARTITION BY，那么整个结果集将被视为一个单一的“窗口”，所有计算都针对整个结果集进行。

2. ORDER BY子句： 在PARTITION BY划分好的每个分区内部，ORDER BY子句规定了行的处理顺序。这对于依赖顺序的窗口函数（如排名函数、累计函数、LAG/LEAD）至关重要。

   • 作用：确定计算的“顺序”。例如，在计算累计销售额时，你需要按日期进行排序；在排名时，你需要按分数进行排序。
   • 缺失情况：如果省略ORDER BY，并且没有指定帧（ROWS/RANGE），那么窗口函数的行为可能会变得不确定，因为数据库可能会以任意顺序处理分区内的行。对于某些聚合函数，这可能不是问题（如COUNT()），但对于排名或依赖顺序的函数，这会导致错误或不期望的结果。

3. ROWS或RANGE子句（帧规范）： 这是窗口函数中最灵活也最容易让人困惑的部分。它在PARTITION BY和ORDER BY确定的分区内部，进一步定义了一个更小的“帧”（Frame），也就是当前行计算所涉及的行集。这个帧是动态的，它会随着当前行的移动而移动。

   • ROWS：基于物理行数来定义帧。
     • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 从分区开始到当前行（这是ORDER BY存在时的默认帧，用于累计和）。
     • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 包含当前行、前一行和后一行（用于移动平均）。
     • ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: 整个分区（如果ORDER BY存在，通常用于计算分区总和，与PARTITION BY单独使用效果类似）。
   • RANGE：基于逻辑值范围来定义帧。它通常用于数值或日期类型，帧内的行是那些在ORDER BY列上与当前行值相差在指定范围内的行。如果存在重复值，RANGE会将所有相同值的行都包含在帧内。
     • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 类似ROWS，但会包含所有与当前行ORDER BY值相同的行。
     • RANGE BETWEEN INTERVAL ‘7’ DAY PRECEDING AND CURRENT ROW: 包含当前行以及其前7天内的所有行。
   • 作用：精确控制计算的“范围”。它让你可以实现复杂的滑动窗口计算，比如计算过去7天的平均值，或者某个特定值范围内的统计。
   • 注意事项：RANGE通常要求ORDER BY子句中只有一个表达式。如果ORDER BY省略，且没有指定帧，那么默认帧是ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，这意味着整个分区。

理解这三者的协同作用，是编写高效、准确窗口函数的关键。它们共同构建了窗口的“边界”、“顺序”和“计算范围”，让SQL查询能够以极高的灵活性处理复杂的数据分析需求。