SQL 窗口函数

概念定义

GROUP BY 把一组行折叠成一行输出；窗口函数保留全部行，并在“相关的行”上计算一个附加值——按分区、按排序、按帧。这一区别支撑了逐行排名、累计序列与邻行比较。

工作机制

OVER (PARTITION BY … ORDER BY …) 定义窗口，可选的 ROWS/RANGE 帧限定 SUM、AVG 等聚合的范围；ROW_NUMBER、RANK、DENSE_RANK 做排名，LAG、LEAD 取邻行。逻辑求值发生在分组之后、结果 ORDER BY 之前。

使用场景

财务报表的累计与环比、事件流的会话拼接、每实体保留最新行的去重、每组前 N。一次遍历取代引擎逐行执行的自连接与相关子查询。

常见误区

窗口函数不折叠行，GROUP BY 才折叠。同一 SELECT 层不能过滤窗口输出——窗口在 WHERE 之后计算，要包一层 CTE。RANGE 与 ROWS、CURRENT ROW 包含性等帧语义在各引擎间有细微差异，移植累计 SQL 时要逐项核对；无裁剪的大分区仍会把排序溢写到磁盘。

代码示例

-- 三种排序语义在同一数据上的行为差异
WITH scores AS (
  SELECT * FROM (VALUES
    ('ada', 98), ('grace', 98), ('linus', 95),
    ('margaret', 92), ('katherine', 92), ('alan', 88)
  ) AS t(player, points)
)
SELECT
  player,
  points,
  ROW_NUMBER() OVER (ORDER BY points DESC) AS row_num,
  RANK()       OVER (ORDER BY points DESC) AS rank_no,
  DENSE_RANK() OVER (ORDER BY points DESC) AS dense_no
FROM scores
ORDER BY points DESC, player;

-- 每组前 N（配额类查询的标准写法）
WITH ranked AS (
  SELECT
    region,
    salesperson,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY region ORDER BY revenue DESC
    ) AS rn
  FROM quarterly_revenue
)
SELECT region, salesperson, revenue
FROM ranked
WHERE rn <= 2
ORDER BY region, revenue DESC;

-- 固定月份的手写透视：CASE 进聚合函数
SELECT
  category,
  SUM(amount) FILTER (WHERE month = '2026-07') AS jul_revenue,
  SUM(amount) FILTER (WHERE month = '2026-08') AS aug_revenue,
  SUM(amount) FILTER (WHERE month = '2026-09') AS sep_revenue,
  SUM(amount) AS total_revenue
FROM monthly_revenue
GROUP BY category
ORDER BY total_revenue DESC;

-- 经典写法对照：CASE WHEN 的条件聚合
SELECT
  category,
  SUM(CASE WHEN month = '2026-07' THEN amount ELSE 0 END) AS jul_revenue,
  SUM(CASE WHEN month = '2026-08' THEN amount ELSE 0 END) AS aug_revenue
FROM monthly_revenue
GROUP BY category;

-- 生成动态列清单（应用层据此拼 SQL）
SELECT DISTINCT month
FROM monthly_revenue
WHERE month BETWEEN '2026-07' AND '2026-09'
ORDER BY month;