窗口函数Window Function是 SQL 中非常强大的分析工具。它允许你在不合并行记录的前提下对一组行进行运算例如求和、平均、排序排名等并把结果作为额外的列加到每一行上。对于数据分析师和窗口函数几乎是必备技能。1. 我们为什么/什么时候用窗口函数为什么要用窗口函数传统的聚合查询GROUP BY可以回答这样的问题“每个客户的总销售额是多少”“每个部门的平均工资是多少”但它有一个明显的限制会把多行合并成一行你会失去明细数据。窗口函数的优势在于可以在保留每一条明细记录的同时计算聚合结果可以把总和、平均值、排名、累计值、移动平均等作为新列添加到每一行上通常可以避免复杂的自连接和子查询让 SQL 更简洁、更易读、也可能更高效这使得窗口函数在报表、分析、指标计算中非常实用。什么时候用窗口函数在以下场景窗口函数非常合适需要同时展示明细数据和汇总指标例如每一笔订单 该客户的总消费金额计算累计和 / 累计数量Running Total计算排名如每个类别前 N 名、行号比较单行与所在组的平均值所在组的最大值/最小值前一条记录 / 下一条记录如果你在想“我想既看到每行数据又想看到某种汇总或对比”那大概率需要窗口函数。2. 窗口函数的基本语法通用语法格式窗口函数(表达式) OVER ( [PARTITION BY 分组表达式] [ORDER BY 排序表达式] [窗口帧定义] ) AS 别名关键组成部分窗口函数(...)例如SUM(amount)、AVG(score)、ROW_NUMBER()等OVER (...)告诉数据库这是一个窗口函数PARTITION BY按什么字段分区类似GROUP BY的分组但不会合并行ORDER BY在每个分区内的排序规则对累计、排名等很重要窗口帧Frame 可选定义当前行参与计算的记录范围例如ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING示例在每一笔订单行上显示“客户总消费金额”SELECT order_id, customer_id, order_date, amount, SUM(amount) OVER (PARTITION BY customer_id) AS total_spent_per_customer FROM orders;结果特点每一行依然是一笔订单明细total_spent_per_customer会在该客户的每一笔订单上重复显示同一个总金额3. 常见类型的窗口函数3.1 聚合类窗口函数这些函数和GROUP BY里使用的聚合函数是同一套只是加上了OVERSUM()AVG()COUNT()MIN()MAX()示例工资与部门平均工资对比SELECT employee_id, department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary_in_dept FROM employees;每个员工行都会多出一列avg_salary_in_dept表示其所在部门的平均工资。3.2 排名类窗口函数用于给行编号、排序、分组排名等ROW_NUMBER()– 在每个分区内为每一行生成唯一的行号RANK()– 有并列名次时会跳号1, 2, 2, 4, …DENSE_RANK()– 有并列名次但不跳号1, 2, 2, 3, …NTILE(n)– 把分区内的行平均分成 n 份如四分位、十分位示例按工资对每个部门内部员工排名SELECT employee_id, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk FROM employees;3.3 取值类偏移/导航窗口函数用于与临近行比较LAG(expr, offset, default)– 取前面第 offset 行的值LEAD(expr, offset, default)– 取后面第 offset 行的值FIRST_VALUE(expr)– 窗口内第一行的值LAST_VALUE(expr)– 窗口内最后一行的值通常要配合窗口帧正确使用示例计算每天收入相对前一天的变化SELECT dt, revenue, LAG(revenue) OVER (ORDER BY dt) AS prev_revenue, revenue - LAG(revenue) OVER (ORDER BY dt) AS revenue_change FROM daily_revenue;3.4 带窗口帧的聚合累计值 / 移动平均通过窗口帧Frame可以精确控制参与计算的行的范围。示例每个客户的累计消费金额SELECT customer_id, order_date, amount, SUM(amount) OVER ( PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM orders;running_total表示从该客户第一笔订单到当前订单的累计消费。示例3 天移动平均值SELECT dt, metric, AVG(metric) OVER ( ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3d FROM daily_metrics;这里每一行的moving_avg_3d是当前日期及之前 2 天一共 3 天的平均值。4. 窗口函数 vs 聚合函数GROUP BY窗口函数和聚合函数都可以使用SUM、AVG等但行为完全不同。使用GROUP BY的聚合函数会把多行数据压缩成每组一行结果行数通常少于原表行数不能同时直接显示非聚合的明细列除非这些列也出现在GROUP BY中示例只看每个用户的汇总消费额不看每笔订单SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id;结果每个customer_id只保留一行。窗口函数不会减少行数每一行原始记录都会保留可以在同一个查询里同时看到明细级数据每一笔订单窗口聚合指标客户总消费、组平均值、排名等示例在每一笔订单上展示所属客户的总消费SELECT order_id, customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS total_spent_per_customer FROM orders;你同时拥有“订单级”信息和“客户级”汇总信息。小结对比特性聚合GROUP BY窗口函数OVER是否减少行数是否是否能同时展示明细和汇总不能除非通过子查询/连接等变通可以直接在同一 SELECT 中常见用途汇总报表、按组统计分析型查询、在明细上叠加各种指标和对比典型语法SELECT ..., SUM(...) ... GROUP BY ...SELECT ..., SUM(...) OVER(...) ...5. 窗口函数的典型用法5.1 把每行与组平均值进行比较例子判断员工工资高于或低于本部门平均水平SELECT employee_id, department, salary, AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary, salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_dept_avg FROM employees;这里每一行都带有部门平均工资以及“员工工资 - 部门平均”的差值。5.2 累计和 / 累计数量Running Total例子按时间顺序查看销售累计金额SELECT order_date, amount, SUM(amount) OVER ( ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_sales FROM orders;cumulative_sales表示从最早日期到当前日期为止的累计销售额。5.3 每组 Top N结合排名函数例子每个品类中按销售额排名前 3 的产品WITH product_revenue AS ( SELECT category, product_id, SUM(revenue) AS total_revenue FROM sales GROUP BY category, product_id ) SELECT * FROM ( SELECT category, product_id, total_revenue, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY total_revenue DESC ) AS rn FROM product_revenue ) t WHERE rn 3;思路先按类别、产品聚合得到total_revenue再在每个类别中按销售额排序并编号取行号 3的即为 Top 35.4 使用 LAG/LEAD 做环比、同比例子计算每个月收入的环比增长率SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue, (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) AS mom_growth FROM monthly_revenue;prev_month_revenue上个月收入mom_growth环比增长率相对上个月的变化比例6. 理解窗口函数的执行逻辑简化版思维模型可以用一个简化的逻辑来理解数据库先执行FROM、JOIN、WHERE、GROUP BY、HAVING等生成一张中间结果表然后在SELECT阶段计算各个表达式对于每一个窗口函数表达式数据库会根据PARTITION BY在中间结果表中划分分区小组在每个分区内按照ORDER BY排序按照窗口帧如果有确定当前行可见的“窗口行集合”最后对这些行应用对应的窗口函数得到结果你可以把它想象成先划分小组再排好顺序然后在这个有序的小组里对每一行“看一眼前后某个范围的行”再进行计算。