SQL窗口函数
语法与窗口框架
-- 基本语法
function_name() OVER (
[PARTITION BY col1, col2] -- 可选：分组
[ORDER BY col3 DESC] -- 可选：组内排序
[ROWS|RANGE BETWEEN ... AND ...] -- 可选：定义框架
)
-- 框架边界：
-- UNBOUNDED PRECEDING — 分区起始
-- N PRECEDING — 当前行之前 N 行/范围
-- CURRENT ROW — 当前行
-- N FOLLOWING — 当前行之后 N 行/范围
-- UNBOUNDED FOLLOWING — 分区结束
-- 命名窗口（多个函数复用）
SELECT dept, salary,
SUM(salary) OVER w AS dept_sum,
AVG(salary) OVER w AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY dept);
函数参考
| 函数 | 说明 | 备注 |
|---|---|---|
| ROW_NUMBER() | 唯一连续整数（1,2,3...） | 无并列，始终唯一 |
| RANK() | 有间隔的排名（1,1,3...） | 并列后跳过编号 |
| DENSE_RANK() | 无间隔的排名（1,1,2...） | 不跳过编号 |
| NTILE(n) | 分成 n 桶（1到n） | 适合分位数计算 |
| LAG(col, n, 默认值) | 前 n 行的值 | n 默认为 1 |
| LEAD(col, n, 默认值) | 后 n 行的值 | n 默认为 1 |
| FIRST_VALUE(col) | 框架内第一个值 | 需要明确框架 |
| LAST_VALUE(col) | 框架内最后一个值 | 需 ... AND UNBOUNDED FOLLOWING |
| SUM/AVG/COUNT/MIN/MAX | 滚动/移动聚合 | 框架控制包含范围 |
实用示例
-- 分组 Top N（每个部门薪资前3）
WITH ranked AS (
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
)
SELECT name, dept, salary FROM ranked WHERE rn <= 3;
-- 同比增长
SELECT year, revenue,
LAG(revenue) OVER (ORDER BY year) AS prev_year,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY year))
/ NULLIF(LAG(revenue) OVER (ORDER BY year), 0), 1) AS yoy_pct
FROM annual_revenue;
-- 7日移动平均
SELECT date, daily_sales,
ROUND(AVG(daily_sales) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 2) AS moving_avg_7d
FROM daily_sales;
-- 用户累计消费（分区重置）
SELECT user_id, order_date, amount,
SUM(amount) OVER (
PARTITION BY user_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS lifetime_value
FROM orders;