窗口函数

窗口函数是什么

窗口函数对一组行执行计算，但不像 GROUP BY 那样把多行折叠成一行。它保留每一行的粒度，把计算结果作为新列追加上去。

你可以理解为：既能看到每一行的明细，又能同时拿到分组的统计值。

核心语法

SELECT
    column1,
    <函数>(expr) OVER (
        [PARTITION BY col_a]
        [ORDER BY col_b DESC]
        [ROWS BETWEEN ... AND ...]
    ) AS new_col
FROM table_name;

三个关键子句：

• PARTITION BY — 分组。窗口函数在每组内独立计算。省略则整张表算一组。
• ORDER BY — 组内排序。排名函数和位移函数必须指定，否则结果不确定。
• 窗口帧 — 精确控制计算范围。比如"从头到当前行"（累计）或"前后各一行"（滑动窗口）。

常用帧写法

注意默认行为：有 ORDER BY 时默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（累计语义）；没有 ORDER BY 时默认帧覆盖整个分区。

排名函数

三个排名函数的区别，假设某组有四个值 100、90、90、80：

面试选哪个？要求"严格 N 行"用 ROW_NUMBER，要求"包含所有并列"用 RANK，要求"N 个不同名次"用 DENSE_RANK。

还有 NTILE(n)，把分区均匀分成 n 桶并返回桶号，做分位数分组时很好用。

聚合函数做窗口函数

SUM、AVG、COUNT、MIN、MAX 都能跟 OVER() 搭配。

-- 每个员工的销售额占部门总额的比例
SELECT emp_id, dept_id, sales,
    sales * 1.0 / SUM(sales) OVER(PARTITION BY dept_id) AS pct
FROM employee_sales;

这里没写 ORDER BY，所以 SUM 是对整个分区求和。加上 ORDER BY 就变成累计：

-- 按日期累计
SELECT sale_date, amount,
    SUM(amount) OVER(ORDER BY sale_date) AS cumulative
FROM sales;

位移函数

• LAG(col, n, default) — 取前 n 行的值（默认 n=1）
• LEAD(col, n, default) — 取后 n 行的值
• FIRST_VALUE(col) — 帧内第一行的值
• LAST_VALUE(col) — 帧内最后一行的值

LAST_VALUE 的坑：默认帧到当前行为止，所以它返回的其实是当前行自己的值。想拿分区最后一行，要显式写帧：

LAST_VALUE(salary) OVER(
    PARTITION BY dept_id ORDER BY hire_date
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)

练习

SELECT * FROM (
  SELECT emp_id, name, dept_id, salary,
    DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk <= 3;

SELECT sale_date, amount,
  ROUND(AVG(amount) OVER(
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 1) AS ma7
FROM sales;

SELECT sale_date, amount,
  LAG(amount) OVER(ORDER BY sale_date) AS prev_amount,
  CASE
    WHEN LAG(amount) OVER(ORDER BY sale_date) IS NULL THEN NULL
    WHEN LAG(amount) OVER(ORDER BY sale_date) = 0 THEN NULL
    ELSE ROUND((amount * 1.0 / LAG(amount) OVER(ORDER BY sale_date) - 1) * 100, 1)
  END AS growth_pct
FROM sales;