SQL窗口函数解决了什么
传统SQL做复杂分析（排名、累计、同比、占比）需要多重嵌套子查询，性能差且难维护。窗口函数让这些分析变成一行SQL，性能高、可读性强。本教程从基础到复杂实战完整讲解。
第一步：理解窗口函数
窗口函数对一组相关行（窗口）进行计算，结果不会合并行。这是与聚合函数的关键区别：
- 聚合函数——SUM/AVG等把多行合并成一行
- 窗口函数——对每行计算一个汇总值，保留原行
语法：
函数名() OVER (
[PARTITION BY 列]
[ORDER BY 列]
[ROWS/RANGE 子句]
)
三大组件：
- PARTITION BY——分组（类似GROUP BY但不合并）
- ORDER BY——窗口内排序
- ROWS/RANGE——窗口范围（前后几行）
第二步：排名函数
三种排名函数应用场景不同：
ROW_NUMBER()
SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) as rn
FROM students;
1,2,3,4,5连续排名，不并列。适合分页、每组取第一条。
RANK()
SELECT name, score, RANK() OVER (ORDER BY score DESC) as rk
FROM students;
1,2,2,4,5并列后跳跃。适合排名显示（销售排名、考试排名）。
DENSE_RANK()
SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) as drk
FROM students;
1,2,2,3,4并列后连续。适合等级评定（A/B/C级）。
分组内排名
SELECT name, class, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) as class_rank
FROM students;
每个班级单独排名，不是全校排名。
第三步：累计与移动计算
累计求和
SELECT date, amount, SUM(amount) OVER (ORDER BY date) as cumulative
FROM orders;
累计销售额，看业务增长趋势。
移动平均
SELECT date, amount, AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as ma7
FROM orders;
7天移动平均线，看趋势更平滑。
移动求和
SELECT date, amount, SUM(amount) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as sum_30d
FROM orders;
30天累计销售。
窗口范围详解
- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW——当前行+前2行
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW——从分区开始到当前
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING——从当前到分区结束
- RANGE BETWEEN——按值范围而非物理行
第四步：占比与对比
占总销售比例
SELECT product, sales, sales * 100.0 / SUM(sales) OVER () as pct
FROM product_sales;
组内占比
SELECT category, product, sales, sales * 100.0 / SUM(sales) OVER (PARTITION BY category) as cat_pct
FROM product_sales;
同比环比
SELECT
month, sales,
sales – LAG(sales) OVER (ORDER BY month) as mom_change,
(sales – LAG(sales) OVER (ORDER BY month)) * 100.0 / LAG(sales) OVER (ORDER BY month) as mom_pct
FROM monthly_sales;
LAG/LEAD函数取上N行/下N行的值，是同比环比的关键。
第五步：复杂实战案例
案例1：用户留存分析
WITH cohorts AS (
SELECT user_id, DATE_TRUNC(‘month’, signup_date) as cohort
FROM users
),
user_activity AS (
SELECT user_id, DATE_TRUNC(‘month’, event_date) as active_month
FROM events
)
SELECT
c.cohort,
a.active_month,
COUNT(DISTINCT a.user_id) as active_users,
COUNT(DISTINCT a.user_id) * 100.0 / COUNT(DISTINCT c.user_id) as retention_pct
FROM cohorts c
LEFT JOIN user_activity a ON c.user_id = a.user_id
GROUP BY c.cohort, a.active_month;
案例2：用户RFM分层
SELECT user_id,
NTILE(5) OVER (ORDER BY recency DESC) as R_score,
NTILE(5) OVER (ORDER BY frequency DESC) as F_score,
NTILE(5) OVER (ORDER BY monetary DESC) as M_score
FROM user_rfm;
NTILE把用户分成5层，组合RFM分数做用户分层。
案例3：连续登录天数
WITH dates AS (
SELECT user_id, login_date,
login_date – INTERVAL (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) DAY as grp
FROM logins
)
SELECT user_id, MIN(login_date) as streak_start, MAX(login_date) as streak_end,
COUNT(*) as streak_days
FROM dates
GROUP BY user_id, grp;
经典的Gaps and Islands算法识别连续登录。
性能优化
- 索引——窗口函数的ORDER BY和PARTITION BY列建索引
- 避免大分区——PARTITION BY的列值不能太多（不超过1000）
- 减少计算——能用简单聚合的不要用窗口函数
- 分批计算——超大数据集考虑分批计算
常见数据库支持
- PostgreSQL——完整支持
- MySQL 8.0+——完整支持
- SQL Server——完整支持
- Oracle——完整支持
- SQLite——3.25+部分支持
- ClickHouse/Snowflake/BigQuery——云数仓都支持
常见问题与误区
- 窗口过大性能差——按时间或类别分区避免全表窗口
- NULL值处理——PARTITION BY/ORDER BY的NULL值默认排首位
- 结果非预期——窗口函数是结果列，不是过滤条件，WHERE要用子查询
- 过度使用——简单聚合能解决的不要用窗口函数
效率数据
实测：用户留存分析，传统嵌套子查询约5-8分钟，窗口函数约30秒。销售同比环比，传统需要3层子查询+自连接，窗口函数1层完成，可读性提升10倍。适合场景：业务报表、用户分析、销售分析、金融分析、数据探索。