SQL 窗口函数在复杂报表中的应用
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 同理）。
先建表灌数据：
CREATE TABLE sales (
id INT PRIMARY KEY,
dept VARCHAR(20),
emp VARCHAR(20),
mth CHAR(7), -- 如 '2026-01'
amount DECIMAL(12,2)
);
INSERT INTO sales VALUES
(1,'华北','张三','2026-01',1200),(2,'华北','李四','2026-01',900),
(3,'华北','王五','2026-01',1500),(4,'华东','赵六','2026-01',2000),
(5,'华东','钱七','2026-01',1100),(6,'华北','张三','2026-02',1300),
(7,'华东','赵六','2026-02',1800);
需求 1：每个部门销售额 Top 3 员工（并列也保留）：
SELECT dept, emp, amount,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY amount DESC) AS rk
FROM sales
WHERE mth = '2026-01'
QUALIFY rk <= 3; -- MySQL 8.0.31+/PG 用 QUALIFY；老版本外套一层子查询 WHERE rk<=3
需求 2：按员工逐月累计销售额：
SELECT emp, mth, amount,
SUM(amount) OVER (PARTITION BY emp ORDER BY mth
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amt
FROM sales;
需求 3：本月 vs 上月环比（用 LAG 取上一期）：
SELECT emp, mth, amount,
LAG(amount, 1) OVER (PARTITION BY emp ORDER BY mth) AS prev_amt,
ROUND((amount - LAG(amount,1) OVER (PARTITION BY emp ORDER BY mth))
/ LAG(amount,1) OVER (PARTITION BY emp ORDER BY mth) * 100, 1) AS mom_pct
FROM sales;
需求 4：每笔金额占"所属部门当月总额"的比例：
SELECT dept, emp, mth, amount,
ROUND(amount / SUM(amount) OVER (PARTITION BY dept, mth) * 100, 1) AS pct_of_dept
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 看执行计划，确认窗口函数没有触发意外的全表排序放大。
页:
[1]