Appearance
PostgreSQL 窗口函数：数据分析的超级望远镜 🔭
💡 想象一下，当你用望远镜观察星空时，既能看清每颗星星的细节，又能分析星座的整体分布。这就是窗口函数的魔力！它让你在查看每一行数据细节的同时，还能进行复杂的跨行分析，就像拥有数据分析的"超级望远镜"！
一、为什么需要窗口函数？🤔
传统 SQL 在数据分析时有两大痛点：
窗口函数完美解决了这些问题：
- 保留所有细节：不像 GROUP BY 会折叠数据
- 跨行计算：轻松实现排名、累计值等复杂计算
- 高性能：避免多层嵌套子查询
IMPORTANT
窗口函数的核心价值：在不折叠行的前提下，实现对数据集的智能分析！
二、实际业务场景案例 🌟
场景 1：部门薪资分析（人力资源）
业务背景：
公司需要分析各部门薪资分布，找出每个部门的薪资排名前3的员工进行表彰，同时计算员工薪资占部门总薪资的百分比。
sql
::: code-group
```sql [解决方案]
SELECT
employee_name,
department,
salary,
-- 部门内薪资排名
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank,
-- 薪资占部门比例
ROUND(salary * 100.0 / SUM(salary) OVER (
PARTITION BY department
), 2) AS salary_percent
FROM employees
WHERE salary_rank <= 3; -- 取各部门前三名sql
-- 需要多次查询 + 手动拼接结果
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn
FROM employees
) t WHERE rn <= 3;
-- 额外查询计算部门总薪资
SELECT dept, SUM(salary) FROM employees GROUP BY dept;:::
**示例数据**：
```sql
-- 处理前数据
employee_name | department | salary
--------------|------------|--------
Alice | IT | 80000
Bob | IT | 75000
Charlie | IT | 85000
David | HR | 65000
Eve | HR | 70000处理结果：
employee_name | department | salary | salary_rank | salary_percent
--------------|------------|--------|-------------|---------------
Charlie | IT | 85000 | 1 | 35.42
Alice | IT | 80000 | 2 | 33.33
Bob | IT | 75000 | 3 | 31.25
Eve | HR | 70000 | 1 | 51.85
David | HR | 65000 | 2 | 48.15✅ 价值：单次查询完成排名+占比计算，快速锁定高绩效员工！
场景 2：销售增长分析（电子商务）
业务背景：
电商平台需要分析月度销售额同比增长率，识别增长异常月份，要求显示当月销售额、去年同期销售额和增长率。
sql
SELECT
month,
sales,
-- 获取去年同月销售额
LAG(sales, 12) OVER (ORDER BY month) AS last_year_sales,
-- 计算同比增长率（处理除零错误）
ROUND(
(sales - COALESCE(LAG(sales, 12) OVER (ORDER BY month), 0)) * 100.0 /
NULLIF(COALESCE(LAG(sales, 12) OVER (ORDER BY month), 0), 1 -- 注意除零保护
, 2) AS yoy_growth
FROM monthly_sales;示例数据：
sql
-- 处理前数据
month | sales
--------|-------
2023-01 | 10000
2023-02 | 12000
...
2024-01 | 15000
2024-02 | 13000处理结果：
month | sales | last_year_sales | yoy_growth
--------|-------|-----------------|-----------
2024-01 | 15000 | 10000 | 50.00%
2024-02 | 13000 | 12000 | 8.33%⚠️ 注意：实际查询需增加完整除零保护：
sqlCASE WHEN NULLIF(LAG(sales,12) OVER(),0) IS NULL THEN 0 ELSE (sales - LAG(sales,12) OVER()) * 100.0 / NULLIF(LAG(sales,12) OVER(),0) END
✅ 价值：一键计算关键业务指标，快速定位异常波动！
场景 3：用户留存分析（移动应用）
业务背景：
APP 需要识别连续登录7天以上的忠诚用户，用于推送专属福利。
sql
-- 步骤1：标记连续登录组
WITH login_groups AS (
SELECT
user_id,
login_date,
login_date - INTERVAL '1 day' * ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
) AS login_group -- 核心技巧：日期序列减行号
FROM user_logins
)
-- 步骤2：统计连续登录天数
SELECT
user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS login_days
FROM login_groups
GROUP BY user_id, login_group
HAVING COUNT(*) >= 7; -- 筛选连续登录7天以上示例数据：
sql
-- 处理前数据
user_id | login_date
--------|------------
1001 | 2024-01-01
1001 | 2024-01-02
...
1001 | 2024-01-07
1002 | 2024-01-01
1002 | 2024-01-03 -- 此处断开处理结果：
user_id | start_date | end_date | login_days
--------|-------------|-------------|-----------
1001 | 2024-01-01 | 2024-01-07 | 7✅ 价值：精准识别高价值用户，提升用户留存率和活跃度！
三、核心概念解析 🧠
1. 分区（PARTITION BY） - 创建分析小组
就像把公司员工按部门分组，但不折叠任何成员！
sql
SELECT
employee_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;效果：
每个员工都能看到自己部门的平均薪资 👇
Alice | IT | 80000 | 80000
Bob | IT | 75000 | 80000 -- 同部门平均值相同
Charlie| IT | 85000 | 80000TIP
省略 PARTITION BY 时，整个表作为单一分区分析
2. 排序（ORDER BY） - 控制计算顺序
像给学生按成绩排队，再逐个发奖状 🎓
sql
SELECT
employee_name,
salary,
SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees;效果：薪资累计和像爬楼梯一样上升 👇
Grace | 55000 | 55000
Frank | 60000 | 115000 (=55000+60000)
Henry | 62000 | 177000 (=115000+62000)3. 窗口帧 - 精确控制计算范围
| 模式 | 适用场景 | 示例 |
|---|---|---|
ROWS | 精确控制行数 | ROWS BETWEEN 2 PRECEDING AND CURRENT ROW |
RANGE | 按数值范围（如日期、金额） | RANGE BETWEEN 30 PRECEDING AND CURRENT ROW |
GROUPS | 处理相同值的组 | GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING |
sql
-- 7日移动平均销售额
SELECT
date,
sales,
AVG(sales) OVER (
ORDER BY date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS ma_7days
FROM daily_sales;四、常用函数工具箱 🧰
1. 排名三剑客
sql
SELECT
product_name,
price,
ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num, -- 唯一序号
RANK() OVER (ORDER BY price DESC) AS rank, -- 排名（允许并列）
DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank -- 紧凑排名
FROM products;2. 分析函数（时间旅行者）
sql
-- 查看价格变化
SELECT
date,
price,
LAG(price, 1) OVER (ORDER BY date) AS yesterday_price, -- 昨日价格
LEAD(price, 1) OVER (ORDER BY date) AS tomorrow_price -- 明日价格
FROM stock_prices;3. 聚合函数（保留细节版）
sql
-- 实时计算累计占比
SELECT
time,
revenue,
SUM(revenue) OVER (ORDER BY time) AS running_total,
revenue * 100.0 / SUM(revenue) OVER () AS total_percent
FROM hourly_sales;五、最佳实践与避坑指南 ⚠️
✅ 最佳实践
sql
-- 1. 使用命名窗口复用配置（DRY原则）
SELECT
SUM(salary) OVER w AS dept_total,
AVG(salary) OVER w AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department);
-- 2. 为窗口列创建索引
CREATE INDEX idx_dept_salary ON employees (department, salary); -- [!code tip]
-- 3. 使用ROWS提升大表性能
-- 比RANGE更快，避免全分区扫描
AVG(price) OVER (ORDER BY date ROWS 6 PRECEDING)❌ 常见错误
sql
-- 错误1：在WHERE中使用窗口函数
SELECT name, RANK() OVER (ORDER BY score)
FROM students
WHERE RANK() OVER (ORDER BY score) <= 10; -- 语法错误！
-- 正确做法：使用子查询
SELECT * FROM (
SELECT name, RANK() OVER (ORDER BY score) AS rnk
FROM students
) WHERE rnk <= 10;
-- 错误2：忽略NULL和除零问题
SELECT sales / LAG(sales) OVER () -- 可能除零或NULL!
FROM monthly_sales;
-- 正确做法：添加保护
SELECT
sales / NULLIF(COALESCE(LAG(sales) OVER (), 1), 0)
FROM monthly_sales;性能警告
复杂窗口函数在大数据表上可能变慢！
优化方案：
- 缩小窗口范围（如
ROWS 30 PRECEDING替代整个分区） - 在子查询中预过滤数据
- 使用物化视图预计算
六、总结 🚀
掌握窗口函数，你将获得数据分析的"超能力"：
- 分区透视 👓 →
PARTITION BY分组不折叠 - 时空操控 ⏳ →
ORDER BY+LAG/LEAD穿越数据时间线 - 动态范围 🎯 → 窗口帧精确控制计算范围
- 多维分析 📊 → 排名/聚合/分析函数组合使用
💫 最终效果：单次查询完成复杂分析，性能提升 3-5 倍，代码量减少 50%！
TIP
实践建议：从简单的 OVER (PARTITION BY ... ORDER BY ...) 开始，逐步增加复杂度，很快你就能像数据分析大师一样思考！