MySQL窗口函数太实用了，再也不怕复杂SQL查询了

内容分享11小时前发布

一、窗口函数核心优势

避免数据折叠：传统 GROUP BY 会合并行，窗口函数可在保留所有数据的同时计算聚合结果。
简化多表关联：无需频繁自连接或子查询，直接在单表中完成排名、Top N、累计求和等需求。
灵活分区计算：支持按指定字段分区（PARTITION BY），同一分区内独立计算，适配多维度分析场景。

二、高频实用窗口函数分类

1. 排名类（常用场景：数据排名、去重排名）

ROW_NUMBER ()：无重复排名，即使值一样也按顺序编号（1,2,3,4）。
RANK ()：有间隔排名，一样值同排名，后续排名跳过间隔（1,2,2,4）。
DENSE_RANK ()：无间隔排名，一样值同排名，后续排名连续（1,2,2,3）。

2. 聚合类（常用场景：累计统计、同比环比）

SUM () OVER (…)：累计求和（如累计销售额、累计用户数）。
AVG () OVER (…)：滑动平均（如近 7 天平均订单量）。
MAX ()/MIN () OVER (…)：分区内最值（如各部门最高薪资）。

3. 偏移类（常用场景：上下行数据对比）

LAG (col, n)：获取当前行向上第 n 行的 col 字段值（如前一天销售额）。
LEAD (col, n)：获取当前行向下第 n 行的 col 字段值（如下一天销售额）。

MySQL窗口函数太实用了，再也不怕复杂SQL查询了

三、经典使用示例

假设已存在以下通用测试表（实际业务需替换为自身表结构）：

sql

-- 员工表（部门、薪资、入职时间）
CREATE TABLE employees (
  emp_id INT PRIMARY KEY AUTO_INCREMENT,
  dept_id INT,
  emp_name VARCHAR(50),
  salary DECIMAL(10,2),
  hire_date DATE,
  dept_name VARCHAR(50) -- 简化设计，实际可能存于部门表
);

-- 销售表（日期、产品、区域、销售额）
CREATE TABLE sales (
  sale_id INT PRIMARY KEY AUTO_INCREMENT,
  sale_date DATE,
  product_id INT,
  product_name VARCHAR(50),
  region VARCHAR(50),
  amount DECIMAL(10,2)
);

-- 订单表（用户、订单时间、金额、状态）
CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  order_date DATETIME,
  order_amount DECIMAL(10,2),
  status VARCHAR(20) -- 已支付/未支付/退款
);

场景 1：各部门员工薪资排名（支持并列）

需求

按部门分组，同一部门内按薪资降序排名，允许并列（如 2 人薪资一样则均为第 2 名）。

SQL

SELECT
  emp_id,
  dept_name,
  emp_name,
  salary,
  -- DENSE_RANK()：无间隔并列排名（1,2,2,3）；RANK()：有间隔（1,2,2,4）
  DENSE_RANK() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY dept_name, salary_rank;

场景 2：获取各区域月度销售额 Top3 产品

需求

按区域 + 月份分组，统计每个分组内销售额前 3 的产品（销售额一样则并列，最多返回 3 条）。

SQL

SELECT
  region,
  DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
  product_name,
  amount AS monthly_sales,
  rank_num
FROM (
  SELECT
    region,
    sale_date,
    product_name,
    SUM(amount) AS amount, -- 先统计月度销售额
    -- 按区域+月份分区，销售额降序排名
    ROW_NUMBER() OVER (
      PARTITION BY region, DATE_FORMAT(sale_date, '%Y-%m') 
      ORDER BY SUM(amount) DESC
    ) AS rank_num
  FROM sales
  GROUP BY region, DATE_FORMAT(sale_date, '%Y-%m'), product_name
) AS temp
WHERE rank_num <= 3; -- 筛选Top3

场景 3：累计销售额统计（按日期递增）

需求

按日期排序，计算每日销售额及截至当日的累计销售额（支持按区域分组累计）。

SQL

SELECT
  sale_date,
  region,
  SUM(amount) AS daily_sales, -- 当日销售额
  -- 按区域分区，日期排序，累计求和（无分区则全局累计）
  SUM(SUM(amount)) OVER (
    PARTITION BY region 
    ORDER BY sale_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 默认规则，可省略
  ) AS cumulative_sales
FROM sales
GROUP BY sale_date, region
ORDER BY region, sale_date;

场景 4：计算员工薪资与部门平均薪资的差值

需求

查询每个员工的薪资，并显示其所在部门的平均薪资及薪资差值（薪资 – 平均薪资）。

SQL

SELECT
  emp_id,
  dept_name,
  emp_name,
  salary,
  -- 按部门分区，计算分区内平均薪资
  AVG(salary) OVER (PARTITION BY dept_name) AS dept_avg_salary,
  -- 计算薪资与平均薪资的差值
  salary - AVG(salary) OVER (PARTITION BY dept_name) AS salary_diff
FROM employees
ORDER BY dept_name, salary_diff DESC;

场景 5：上下行数据对比（前一天 / 后一天销售额）

需求

查询每日销售额，并显示前一天、后一天的销售额（用于计算环比增长）。

SQL

SELECT
  sale_date,
  SUM(amount) AS daily_sales,
  -- LAG(col, n)：获取向上第n行数据（n=1即前一天），无数据返回NULL
  LAG(SUM(amount), 1) OVER (ORDER BY sale_date) AS prev_day_sales,
  -- LEAD(col, n)：获取向下第n行数据（n=1即后一天）
  LEAD(SUM(amount), 1) OVER (ORDER BY sale_date) AS next_day_sales,
  -- 计算环比增长率（保留2位小数）
  ROUND(
    (SUM(amount) - LAG(SUM(amount), 1) OVER (ORDER BY sale_date)) 
    / LAG(SUM(amount), 1) OVER (ORDER BY sale_date) * 100,
    2
  ) AS mom_growth_rate -- mom=Month-on-Month，此处为日环比
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

场景 6：按入职时间计算员工在部门内的入职顺序

需求

按部门分组，计算每个员工在部门内的入职先后顺序（入职早的排名靠前）。

SQL

SELECT
  emp_id,
  dept_name,
  emp_name,
  hire_date,
  -- 按部门分区，入职日期升序排名（ROW_NUMBER确保无重复）
  ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY hire_date ASC) AS hire_order
FROM employees
ORDER BY dept_name, hire_order;

场景 7：统计每个用户的首单 / 末单信息

需求

查询每个用户的首单日期、首单金额、末单日期、末单金额。

SQL

SELECT
  user_id,
  -- 按用户分区，订单日期升序取第一个（首单）
  MIN(order_date) OVER (PARTITION BY user_id) AS first_order_date,
  FIRST_VALUE(order_amount) OVER (
    PARTITION BY user_id 
    ORDER BY order_date ASC
  ) AS first_order_amount,
  -- 按用户分区，订单日期降序取第一个（末单）
  MAX(order_date) OVER (PARTITION BY user_id) AS last_order_date,
  FIRST_VALUE(order_amount) OVER (
    PARTITION BY user_id 
    ORDER BY order_date DESC
  ) AS last_order_amount
FROM orders
GROUP BY user_id -- 去重，只保留每个用户一条记录
ORDER BY user_id;

场景 8：滑动窗口统计（近 7 天平均销售额）

需求

按日期排序，计算每日及前 6 天的平均销售额（滑动窗口大小 = 7 天）。

SQL

SELECT
  sale_date,
  SUM(amount) AS daily_sales,
  -- 滑动窗口：当前行及前6行（共7天），按日期排序
  AVG(SUM(amount)) OVER (
    ORDER BY sale_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS avg_7d_sales
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

说明

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW：固定窗口大小（7 行）
若需按 “日期连续 7 天”（忽略缺失日期），可替换为 RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW

场景 9：按销售额分位数分组（高 / 中 / 低客单价）

需求

将产品按销售额分为 3 组（高、中、低），每组占比约 1/3。

SQL

SELECT
  product_name,
  SUM(amount) AS total_sales,
  -- NTILE(n)：将分区内数据均匀分成n组
  NTILE(3) OVER (ORDER BY SUM(amount) DESC) AS sales_group,
  -- 转换为文字标签
  CASE 
    WHEN NTILE(3) OVER (ORDER BY SUM(amount) DESC) = 1 THEN '高销售额'
    WHEN NTILE(3) OVER (ORDER BY SUM(amount) DESC) = 2 THEN '中销售额'
    ELSE '低销售额'
  END AS sales_group_label
FROM sales
GROUP BY product_name
ORDER BY total_sales DESC;

场景 10：查询各部门薪资前 20% 的员工

需求

按部门分组，筛选出每个部门薪资排名前 20% 的员工（支持按比例筛选）。

SQL

SELECT
  emp_id,
  dept_name,
  emp_name,
  salary,
  salary_rank,
  dept_total_emp, -- 部门总人数
  -- 计算排名百分比（保留2位小数）
  ROUND(salary_rank / dept_total_emp * 100, 2) AS rank_percent
FROM (
  SELECT
    emp_id,
    dept_name,
    emp_name,
    salary,
    -- 按部门分区，薪资降序排名
    ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS salary_rank,
    -- 计算部门总人数（窗口函数无需GROUP BY）
    COUNT(*) OVER (PARTITION BY dept_name) AS dept_total_emp
  FROM employees
) AS temp
-- 筛选前20%（排名≤总人数×20%）
WHERE salary_rank <= CEIL(dept_total_emp * 0.2)
ORDER BY dept_name, salary_rank;

说明

CEIL()：向上取整（避免因人数不是 5 的倍数导致漏选）
若需 “前 20% 及并列”，可将 ROW_NUMBER() 改为 DENSE_RANK()

关键函数速查表

函数类型	常用函数	核心作用
排名类	ROW_NUMBER()	无重复排名（1,2,3,4）
	RANK()	有间隔并列排名（1,2,2,4）
	DENSE_RANK()	无间隔并列排名（1,2,2,3）
	NTILE(n)	均匀分 n 组
聚合类	SUM()/AVG()/MAX()/MIN()	分区内聚合（支持累计 / 滑动）
偏移类	LAG(col, n)	取向上第 n 行数据
	LEAD(col, n)	取向下第 n 行数据
首尾类	FIRST_VALUE(col)	取分区内排序后第一行数据
	LAST_VALUE(col)	取分区内排序后最后一行数据

注意事项

窗口函数不能用在 WHERE 子句中（需嵌套子查询 / CTE 筛选）；
PARTITION BY 可选（不写则全局计算），ORDER BY 对排名 / 累计类函数必填；
聚合类窗口函数需配合 GROUP BY 时，内层先聚合，外层再用窗口函数；
滑动窗口的 ROWS（按行数）和 RANGE（按值范围）需根据需求选择。

直接复制上述 SQL，替换表名、字段名和业务参数（如日期格式、Top N 数量），即可快速落地到实际业务中！

© 版权声明

文章版权归作者所有，未经允许请勿转载。

相关文章

SAP FI 总账凭证全生命周期管理：从创建到归档的实战指南

SAP FI 总账凭证全生命周期管理：从创建到归档的实战指南

2周前

000

关于使用Git出现“git Failed to connect to 127.0.0.1 port xxxx: Connection refused”的问题解决方案

关于使用Git出现“git Failed to connect to 127.0.0.1 port xxxx: Connection refused”的问题解决方案

2周前

000

超级详细的zabbix环境搭建和测试（看我的教程，大神你也可以）

超级详细的zabbix环境搭建和测试（看我的教程，大神你也可以）

2周前

200

网页基础HTML+CSS（阶段末尾总结）

网页基础HTML+CSS（阶段末尾总结）

1个月前

100

3 条评论

您必须登录才能参与评论！

立即登录

航航子的游戏日常读者

收藏了，感谢分享

19小时前无记录

登录以回复
北京亿利达读者

好文章

19小时前无记录

登录以回复
我肯定会发财噢投稿者

这个好

19小时前无记录

登录以回复