窗口函数实战：5个场景替代子查询与自连接，优化SQL写法

SQL中的窗口函数（Window Function）是强大的分析工具，相比于传统的自连接、子查询等复杂写法，它能以更简洁的代码、更高的执行效率和更清晰的逻辑解决许多常见问题。以下，我们将通过五个典型的业务场景，对比传统写法与窗口函数写法，直观展示其优势。

本文示例基于 MySQL 8.0+ 和 PostgreSQL 通用语法。首先创建并插入测试数据：

-- 创建员工表
CREATE TABLE 员工表 (
  员工ID INT PRIMARY KEY,
  员工姓名 VARCHAR(20),
  部门 VARCHAR(20),
  工资 DECIMAL(10,2),
  入职日期 DATE,
  绩效评分 INT -- 1-5分
);

-- 插入测试数据
INSERT INTO 员工表 VALUES
(1, '张三', '研发部', 8000.00, '2020-01-10', 4),
(2, '李四', '研发部', 9500.00, '2019-03-15', 5),
(3, '王五', '研发部', 8000.00, '2021-05-20', 3),
(4, '赵六', '市场部', 7000.00, '2020-07-08', 4),
(5, '钱七', '市场部', 8500.00, '2018-11-30', 5),
(6, '孙八', '人事部', 6500.00, '2022-02-18', 4),
(7, '周九', '人事部', 7500.00, '2021-09-05', 5);

场景1：替代自连接计算相邻行数据（如环比）

需求：计算每个员工的工资与同部门内上一个入职同事的工资差值。

传统写法：自连接 + 日期筛选

-- 传统自连接写法，缺点：需要处理NULL、性能差、逻辑复杂
SELECT 
  e1.员工姓名,
  e1.部门,
  e1.工资,
  e2.工资 AS 上一入职同事工资,
  e1.工资 - COALESCE(e2.工资, 0) AS 工资差值
FROM 员工表 e1
LEFT JOIN 员工表 e2 
  ON e1.部门 = e2.部门  -- 同部门
  AND e2.入职日期 = (  -- 找到e1之前最近入职的员工
    SELECT MAX(入职日期) 
    FROM 员工表 e3 
    WHERE e3.部门 = e1.部门 AND e3.入职日期 < e1.入职日期
  )
ORDER BY e1.部门, e1.入职日期;

窗口函数写法：使用 LAG()

SELECT 
  员工姓名,
  部门,
  工资,
  -- LAG(列名, 偏移量, 默认值)：获取分区内当前行之前第N行的数据
  -- PARTITION BY 部门：按部门分区
  -- ORDER BY 入职日期：按入职日期排序
  LAG(工资, 1, 0) OVER (PARTITION BY 部门 ORDER BY 入职日期) AS 上一入职同事工资,
  工资 - LAG(工资, 1, 0) OVER (PARTITION BY 部门 ORDER BY 入职日期) AS 工资差值
FROM 员工表
ORDER BY 部门, 入职日期;

执行结果

员工姓名	部门	工资	上一入职同事工资	工资差值
钱七	市场部	8500.00	0.00	8500.00
赵六	市场部	7000.00	8500.00	-1500.00
李四	研发部	9500.00	0.00	9500.00
张三	研发部	8000.00	9500.00	-1500.00
王五	研发部	8000.00	8000.00	0.00
周九	人事部	7500.00	0.00	7500.00
孙八	人事部	6500.00	7500.00	-1000.00

场景2：替代子查询计算累计值

需求：按入职日期排序，计算每个员工所在部门的累计工资总和。

传统写法：子查询 + 聚合

-- 传统子查询写法，缺点：多次扫描表、性能差、嵌套深
SELECT 
  e1.员工姓名,
  e1.部门,
  e1.工资,
  e1.入职日期,
  (
    SELECT SUM(e2.工资) 
    FROM 员工表 e2 
    WHERE e2.部门 = e1.部门 
      AND e2.入职日期 <= e1.入职日期
  ) AS 部门累计工资
FROM 员工表 e1
ORDER BY e1.部门, e1.入职日期;

窗口函数写法：SUM() 窗口聚合

SELECT 
  员工姓名,
  部门,
  工资,
  入职日期,
  -- SUM(列名) OVER (分区 + 排序)：默认计算从分区开始到当前行的累计和
  -- ORDER BY 入职日期：默认窗口范围是“PARTITION内从第一行到当前行”
  SUM(工资) OVER (
    PARTITION BY 部门 
    ORDER BY 入职日期 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 显式指定累计范围
  ) AS 部门累计工资
FROM 员工表
ORDER BY 部门, 入职日期;

执行结果

员工姓名	部门	工资	入职日期	部门累计工资
钱七	市场部	8500.00	2018-11-30	8500.00
赵六	市场部	7000.00	2020-07-08	15500.00
李四	研发部	9500.00	2019-03-15	9500.00
张三	研发部	8000.00	2020-01-10	17500.00
王五	研发部	8000.00	2021-05-20	25500.00
周九	人事部	7500.00	2021-09-05	7500.00
孙八	人事部	6500.00	2022-02-18	14000.00

场景3：替代笛卡尔积实现 Top N 查询

需求：找出每个部门绩效评分最高的前2名员工（绩效相同则按工资降序）。

传统写法：笛卡尔积 + COUNT()

-- 传统写法，缺点：笛卡尔积导致数据膨胀、性能极差、易出错
SELECT 
  e1.员工姓名,
  e1.部门,
  e1.绩效评分,
  e1.工资
FROM 员工表 e1
WHERE (
  SELECT COUNT(DISTINCT e2.绩效评分) 
  FROM 员工表 e2 
  WHERE e2.部门 = e1.部门 
    AND (e2.绩效评分 > e1.绩效评分 OR (e2.绩效评分 = e1.绩效评分 AND e2.工资 > e1.工资))
) < 2  -- 前2名
ORDER BY e1.部门, e1.绩效评分 DESC, e1.工资 DESC;

窗口函数写法：ROW_NUMBER()/RANK()

WITH 部门绩效排名 AS (
  SELECT 
    员工姓名,
    部门,
    绩效评分,
    工资,
    -- ROW_NUMBER()：连续排名（即使值相同，排名也不同）
    -- RANK()：跳跃排名（值相同则排名相同，后续排名跳过）
    -- DENSE_RANK()：密集排名（值相同则排名相同，后续排名不跳过）
    ROW_NUMBER() OVER (
      PARTITION BY 部门 
      ORDER BY 绩效评分 DESC, 工资 DESC
    ) AS 排名
  FROM 员工表
)
SELECT 员工姓名, 部门, 绩效评分, 工资, 排名
FROM 部门绩效排名
WHERE 排名 <= 2  -- 筛选前2名
ORDER BY 部门, 排名;

执行结果

员工姓名	部门	绩效评分	工资	排名
钱七	市场部	5	8500.00	1
赵六	市场部	4	7000.00	2
李四	研发部	5	9500.00	1
张三	研发部	4	8000.00	2
周九	人事部	5	7500.00	1
孙八	人事部	4	6500.00	2

场景4：替代多表连接计算占比

需求：计算每个员工工资占其所在部门总工资的比例。

传统写法：GROUP BY + 连接

-- 传统写法，缺点：需要先聚合再连接、代码冗余
SELECT 
  e1.员工姓名,
  e1.部门,
  e1.工资,
  e2.部门总工资,
  ROUND(e1.工资 / e2.部门总工资 * 100, 2) AS `工资占比(%)`
FROM 员工表 e1
JOIN (
  SELECT 部门, SUM(工资) AS 部门总工资 
  FROM 员工表 
  GROUP BY 部门
) e2 ON e1.部门 = e2.部门
ORDER BY e1.部门, e1.工资 DESC;

窗口函数写法：SUM() 窗口聚合

SELECT 
  员工姓名,
  部门,
  工资,
  -- SUM(工资) OVER (PARTITION BY 部门)：计算每个部门的总工资
  SUM(工资) OVER (PARTITION BY 部门) AS 部门总工资,
  -- 直接计算占比，无需连接
  ROUND(工资 / SUM(工资) OVER (PARTITION BY 部门) * 100, 2) AS `工资占比(%)`
FROM 员工表
ORDER BY 部门, 工资 DESC;

执行结果

员工姓名	部门	工资	部门总工资	工资占比(%)
钱七	市场部	8500.00	15500.00	54.84
赵六	市场部	7000.00	15500.00	45.16
李四	研发部	9500.00	25500.00	37.25
张三	研发部	8000.00	25500.00	31.37
王五	研发部	8000.00	25500.00	31.37
周九	人事部	7500.00	14000.00	53.57
孙八	人事部	6500.00	14000.00	46.43

场景5：替代嵌套子查询实现数据分桶（PostgreSQL）

需求：将每个部门的员工工资分为3档（高：前33%、中：中间34%、低：后33%）。

传统写法：CASE + 子查询

-- 传统写法，缺点：需要多次计算分位数、逻辑复杂
SELECT 
  e1.员工姓名,
  e1.部门,
  e1.工资,
  CASE 
    WHEN e1.工资 >= (SELECT PERCENTILE_CONT(0.67) WITHIN GROUP (ORDER BY 工资) FROM 员工表 e2 WHERE e2.部门 = e1.部门) THEN '高'
    WHEN e1.工资 >= (SELECT PERCENTILE_CONT(0.33) WITHIN GROUP (ORDER BY 工资) FROM 员工表 e2 WHERE e2.部门 = e1.部门) THEN '中'
    ELSE '低'
  END AS 工资档位
FROM 员工表 e1
ORDER BY e1.部门, e1.工资 DESC;

窗口函数写法：NTILE() 函数

WITH 工资分桶 AS (
  SELECT 
    员工姓名,
    部门,
    工资,
    NTILE(3) OVER (PARTITION BY 部门 ORDER BY 工资 DESC) AS 桶编号
  FROM 员工表
)
SELECT 
  员工姓名,
  部门,
  工资,
  桶编号,
  CASE 桶编号
    WHEN 1 THEN '高'
    WHEN 2 THEN '中'
    ELSE '低'
  END AS 工资档位
FROM 工资分桶
ORDER BY 部门, 工资 DESC;

执行结果

员工姓名	部门	工资	桶编号	工资档位
钱七	市场部	8500.00	1	高
赵六	市场部	7000.00	2	中
李四	研发部	9500.00	1	高
张三	研发部	8000.00	2	中
王五	研发部	8000.00	3	低
周九	人事部	7500.00	1	高
孙八	人事部	6500.00	2	中

总结

通过以上五个场景的对比，可以清晰地看到窗口函数的优势：逻辑简洁、易于维护，并且在大多数 数据库 引擎中拥有更好的执行性能。掌握 LAG()/LEAD()、SUM() OVER、ROW_NUMBER()、RANK() 和 NTILE() 等核心窗口函数，能显著提升复杂 SQL 查询的编写效率和可读性。如果你在学习和实践 SQL 优化的过程中有更多心得，欢迎来 云栈社区 与大家一起交流探讨。