SQL 窗口函数在复杂报表中的应用

dbaai

SQL 窗口函数在复杂报表中的应用

一、具体的问题

做业务报表时，很多统计需求用普通的 GROUP BY 根本表达不出来，或者要绕一大圈自关联、子查询才能勉强实现。下面几类几乎每个 DBA 和数据分析师都会撞上的场景：

• 每个部门里销售额排前 3 的员工是谁（既要看个人业绩，又离不开部门整体排名）；
  
• 按月份累加的累计销售额，以及和上个月、去年同期的对比；
  
• 计算每笔订单在"所属客户"里的金额占比，或者"当前行占分组总和的百分比"；
  
• 不丢明细的前提下，给每一行标上它在分组内的排名、行号。
  

这些需求共同点是：既要保留原始明细行，又要基于某个分组做聚合或排序计算。GROUP BY 会把行"压扁"成一行，明细直接丢失；而窗口函数（OVER 子句）恰恰能在不折叠行的情况下完成这类计算，这正是它存在的意义。

二、核心原理

窗口函数的骨架是 函数名(...) OVER (PARTITION BY 分组列 ORDER BY 排序列 窗口框架)。

• PARTITION BY：决定"按谁分组"，作用类似 GROUP BY，但不会产生分组汇总行，只是把计算范围框在组内。
  
• ORDER BY：决定组内行的计算顺序，对排名类、累计类函数至关重要。
  
• 窗口框架（ROWS / RANGE）：进一步限定"当前行往前/往后看几行"，累计求和就靠它。
  

常用函数分两类：

• 排名类：ROW_NUMBER() 给组内每行唯一连续序号；RANK() 并列跳号（1,1,3）；DENSE_RANK() 并列不跳号（1,1,2）；NTILE(n) 把组切成 n 份便于分桶。
  
• 聚合窗口：SUM()/AVG()/COUNT() OVER(...) 在保留明细的同时算分组聚合；LAG(col, n) / LEAD(col, n) 取前/后第 n 行的值，用来做环比、同比、差值最方便。
  

一个常见误区：以为窗口函数里写了 ORDER BY 就自动变成"累计到当前行"。其实默认框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（确实从开头累计到当前行），但如果你想要"近 3 行滑动平均"，必须显式写 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW，否则结果会差很多。

三、实例参考（动手步骤）

下面用一张销售明细表演示 4 个最常被点名的报表需求，全部可照抄运行（以 MySQL 8 / PostgreSQL 语法为准，SQL Server 同理）。

先建表灌数据：

2. CREATE TABLE sales (
3.   id     INT PRIMARY KEY,
4.   dept   VARCHAR(20),
5.   emp    VARCHAR(20),
6.   mth    CHAR(7),        -- 如 '2026-01'
7.   amount DECIMAL(12,2)
8. );
9. INSERT INTO sales VALUES
10. (1,'华北','张三','2026-01',1200),(2,'华北','李四','2026-01',900),
11. (3,'华北','王五','2026-01',1500),(4,'华东','赵六','2026-01',2000),
12. (5,'华东','钱七','2026-01',1100),(6,'华北','张三','2026-02',1300),
13. (7,'华东','赵六','2026-02',1800);

复制代码

需求 1：每个部门销售额 Top 3 员工（并列也保留）：

2. SELECT dept, emp, amount,
3.        DENSE_RANK() OVER (PARTITION BY dept ORDER BY amount DESC) AS rk
4. FROM sales
5. WHERE mth = '2026-01'
6. QUALIFY rk <= 3;          -- MySQL 8.0.31+/PG 用 QUALIFY；老版本外套一层子查询 WHERE rk<=3

复制代码

需求 2：按员工逐月累计销售额：

2. SELECT emp, mth, amount,
3.        SUM(amount) OVER (PARTITION BY emp ORDER BY mth
4.                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amt
5. FROM sales;

复制代码

需求 3：本月 vs 上月环比（用 LAG 取上一期）：

2. SELECT emp, mth, amount,
3.        LAG(amount, 1) OVER (PARTITION BY emp ORDER BY mth) AS prev_amt,
4.        ROUND((amount - LAG(amount,1) OVER (PARTITION BY emp ORDER BY mth))
5.              / LAG(amount,1) OVER (PARTITION BY emp ORDER BY mth) * 100, 1) AS mom_pct
6. FROM sales;

复制代码

需求 4：每笔金额占"所属部门当月总额"的比例：

2. SELECT dept, emp, mth, amount,
3.        ROUND(amount / SUM(amount) OVER (PARTITION BY dept, mth) * 100, 1) AS pct_of_dept
4. FROM sales;

复制代码

前后对比：同样的需求，用 GROUP BY + 自关联至少要写 2~3 层子查询、还容易丢明细；窗口函数一条 SQL 既留明细又出指标，可读性和性能都更好。

四、实操检查清单

• 确认数据库版本支持窗口函数：MySQL 8.0+、SQL Server 2008+、PostgreSQL 8.4+、Oracle 8i+；老版本需升级或绕路。
  
• 排名需求先想清楚要 ROW_NUMBER（唯一序号）、RANK（跳号）还是 DENSE_RANK（不跳号），别混用。
  
• 凡是"累计/滑动"类，务必显式写窗口框架 ROWS/RANGE BETWEEN ...，不要依赖默认行为。
  
• PARTITION BY 的列和 ORDER BY 的列要想清楚：分组错了，所有指标全盘错。
  
• 取"前/后行"用 LAG/LEAD 代替自关联，代码更短、执行计划更优。
  
• 需要"每组取前 N 行"时优先用 QUALIFY（新版）或外层 WHERE rk<=N（老版），避免在 WHERE 里直接引用窗口函数别名。
  
• 上线前用 EXPLAIN 看执行计划，确认窗口函数没有触发意外的全表排序放大。