开窗函数入门：最易懂的概念与案例

小橙编码日志
97 阅读6分钟

由浅入深学习开窗函数

开窗函数（Window Function）是SQL中强大的分析工具，与聚合函数（如SUM、COUNT）不同，它不会将多行数据聚合为一行，而是为每行数据计算一个基于“窗口”（指定范围的行集合）的结果，保留原始行的所有信息。本文从基础概念到高级用法，再结合实战案例，带你全面掌握开窗函数。

image.png

一、基础认知：开窗函数 vs 聚合函数

先通过一个简单对比理解核心差异：

1. 聚合函数（GROUP BY）

-- 计算每个部门的平均工资（聚合后仅保留部门维度）
SELECT dept_id, AVG(salary) AS avg_sal
FROM employee
GROUP BY dept_id;

结果会按部门分组，每行只显示部门和平均工资，丢失了员工的个体信息。

2. 开窗函数

-- 计算每个员工所属部门的平均工资（保留员工所有信息）
SELECT 
  emp_id, emp_name, dept_id, salary,
  AVG(salary) OVER (PARTITION BY dept_id) AS avg_dept_sal
FROM employee;

结果中每行保留员工的ID、姓名、部门、工资，同时新增“所属部门平均工资”列——这就是开窗函数的核心价值：保留明细，同时计算聚合维度的指标。

二、开窗函数的基本语法

开窗函数的通用结构：

<函数名>(<参数>) OVER (
  [PARTITION BY <分区列>]  -- 可选：按哪些列分组（类似GROUP BY）
  [ORDER BY <排序列> [ASC/DESC]]  -- 可选：窗口内的行排序
  [ROWS/RANGE <窗口范围>]  -- 可选：定义窗口的物理/逻辑范围
) AS <别名>

核心组件解析

组件作用示例
PARTITION BY分区：将数据划分为多个独立的“窗口”，函数仅在分区内计算PARTITION BY dept_id（按部门分区）
ORDER BY排序：指定分区内行的顺序（仅对需要排序的函数生效，如ROW_NUMBER）ORDER BY salary DESC（按工资降序）
ROWS/RANGE窗口范围：限定分区内参与计算的行范围（物理/逻辑范围）ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING（当前行±1行）

三、入门：常用开窗函数分类

开窗函数分为三类，从易到难掌握：

1. 聚合类开窗函数

将普通聚合函数（SUM/AVG/COUNT/MIN/MAX）结合OVER子句使用，是最基础的开窗函数。

示例1：无分区、无排序（全局窗口）
-- 计算所有员工的平均工资（每行都显示全局平均）
SELECT 
  emp_id, salary,
  AVG(salary) OVER () AS global_avg_sal
FROM employee;
示例2：按分区聚合
-- 计算每个部门的工资总和、员工数、最高/最低工资
SELECT 
  emp_id, dept_id, salary,
  SUM(salary) OVER (PARTITION BY dept_id) AS dept_sal_sum,
  COUNT(*) OVER (PARTITION BY dept_id) AS dept_emp_count,
  MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_sal
FROM employee;

2. 排序类开窗函数

用于对分区内的行进行排序并生成序号，是最常用的开窗函数之一。

函数作用特点
ROW_NUMBER()生成连续序号即使值相同，序号也不同（1,2,3...）
RANK()生成排名相同值并列，后续序号跳跃（1,2,2,4...）
DENSE_RANK()生成密集排名相同值并列，后续序号不跳跃（1,2,2,3...）
示例：给每个部门的员工按工资降序排名
SELECT 
  emp_id, dept_id, salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
  RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk,
  DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS drk
FROM employee;

结果示例（部门ID=1）：

emp_iddept_idsalaryrnrkdrk
101120000111
102118000222
103118000322
104115000443

3. 偏移类开窗函数

用于获取分区内指定位置的行数据，解决“取上一行/下一行/第N行”的问题。

函数作用示例
LAG(col, n, default)获取当前行的上n行的col值（n默认1，default为无数据时的默认值）LAG(salary, 1, 0)（上一行工资，无则0）
LEAD(col, n, default)获取当前行的下n行的col值LEAD(salary, 1, 0)（下一行工资，无则0）
FIRST_VALUE(col)获取分区内排序后的第一行col值FIRST_VALUE(salary)（分区内最高工资）
LAST_VALUE(col)获取分区内排序后的最后一行col值LAST_VALUE(salary)（分区内最低工资）
示例：获取员工的上/下一位同事的工资
SELECT 
  emp_id, dept_id, salary,
  LAG(salary, 1) OVER (PARTITION BY dept_id ORDER BY emp_id) AS prev_sal,
  LEAD(salary, 1) OVER (PARTITION BY dept_id ORDER BY emp_id) AS next_sal
FROM employee;

四、实战案例：开窗函数的典型应用

以下案例基于常见业务场景，使用employee（员工表）和order（订单表），表结构如下：

员工表（employee）

字段名类型说明
emp_idINT员工ID
emp_nameVARCHAR员工姓名
dept_idINT部门ID
salaryDECIMAL工资
hire_dateDATE入职日期

订单表（order）

字段名类型说明
order_idINT订单ID
user_idINT用户ID
order_timeDATETIME下单时间
amountDECIMAL订单金额

案例1：Top N问题（取每个部门工资最高的2名员工）

-- 步骤1：给每个部门的员工按工资降序编号
WITH emp_rank AS (
  SELECT 
    emp_id, emp_name, dept_id, salary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employee
)
-- 步骤2：筛选编号≤2的员工
SELECT * FROM emp_rank WHERE rn <= 2;

案例2：同比/环比分析（计算每月订单金额的环比增长率）

-- 步骤1：按月份汇总订单金额
WITH month_amount AS (
  SELECT 
    DATE_FORMAT(order_time, '%Y-%m') AS month,
    SUM(amount) AS total_amount
  FROM `order`
  GROUP BY DATE_FORMAT(order_time, '%Y-%m')
),
-- 步骤2：获取上月订单金额
month_lag AS (
  SELECT 
    month, total_amount,
    LAG(total_amount, 1) OVER (ORDER BY month) AS last_month_amount
  FROM month_amount
)
-- 步骤3：计算环比增长率（保留2位小数）
SELECT 
  month, total_amount, last_month_amount,
  ROUND((total_amount - last_month_amount) / last_month_amount * 100, 2) AS mom_growth
FROM month_lag;

案例3：累计求和（计算用户累计下单金额）

SELECT 
  user_id, order_id, order_time, amount,
  -- 按用户分区，按下单时间排序，累计金额
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_time 
                  ) AS cum_amount
FROM `order`
ORDER BY user_id, order_time;

案例4：删除重复数据（保留每个用户最早的订单）

-- 步骤1：给每个用户的订单按时间排序
WITH order_rank AS (
  SELECT 
    order_id, user_id, order_time, amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time) AS rn
  FROM `order`
)
-- 步骤2：删除rn>1的重复订单（保留rn=1的最早订单）
DELETE FROM `order` 
WHERE order_id IN (SELECT order_id FROM order_rank WHERE rn > 1);

五、总结

开窗函数的核心是“保留明细+分区计算”，相比传统的GROUP BY和子查询，能大幅简化代码、提升效率。学习路径建议：

  1. 先掌握聚合类开窗函数（SUM/AVG+PARTITION BY），理解分区的概念；

  2. 再掌握排序类函数（ROW_NUMBER/RANK），解决Top N、排名问题；

  3. 接着学习偏移类函数（LAG/LEAD），处理行与行之间的关联；

开窗函数是SQL进阶的必备技能，熟练掌握后能高效解决各类分析型需求，尤其在数据仓库、报表开发、业务分析场景中应用广泛。

avatar
文章
阅读
粉丝