前言SQL 窗口函数Window Functions是数据分析师面试和日常工作中最高频考点之一。很多人会写 GROUP BY但一碰到「既要分组聚合、又要保留原始行」的需求就懵了——这正是窗口函数的主场。本文覆盖 5 大高频场景每段代码都有详细注释帮你一次学透。一、什么是窗口函数窗口函数和普通聚合函数SUM/AVG/COUNT的核心区别普通聚合GROUP BY 之后每组只返回1 行结果窗口函数OVER() 之后每组计算结果但原始每行都保留-- 公主号船长Talk -- 语法格式 -- 函数名() OVER ( -- PARTITION BY 分组字段 -- 可选按哪个字段分窗口 -- ORDER BY 排序字段 -- 可选窗口内按什么排序 -- ROWS/RANGE BETWEEN ... -- 可选窗口帧范围 -- ) -- 示例查询每个员工的工资同时显示其部门平均工资 SELECT emp_name, department, salary, AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary FROM employees;二、场景一排名函数 RANK / ROW_NUMBER / DENSE_RANK这三个函数是面试最爱考的必须搞清楚差异。-- 公主号船长TalkSQL进阶干货建议收藏 -- 数据场景销售额排名 -- 假设数据Alice100, Bob90, Carol90, Dave80 SELECT emp_name, sales_amount, -- ROW_NUMBER绝对不重复每行唯一序号1,2,3,4 -- 即使销售额相同排名也不同 ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS row_num, -- RANK允许并列但会跳号1,2,2,4 -- Bob和Carol并列第2下一个是第4没有第3 RANK() OVER (ORDER BY sales_amount DESC) AS rank_num, -- DENSE_RANK允许并列不跳号1,2,2,3 -- Bob和Carol并列第2下一个是第3 DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS dense_rank_num FROM sales_data; -- 结果 -- Alice 100 1 1 1 -- Bob 90 2 2 2 -- Carol 90 3 2 2 ← RANK跳到了4DENSE_RANK是3 -- Dave 80 4 4 3实战取每个部门销售额 Top 3-- 公主号船长Talk -- 经典面试题每个部门取前3名RANK允许并列 WITH ranked AS ( SELECT emp_name, department, sales_amount, RANK() OVER ( PARTITION BY department -- 按部门分窗口 ORDER BY sales_amount DESC -- 窗口内按销售额倒序 ) AS dept_rank FROM sales_data ) SELECT * FROM ranked WHERE dept_rank 3; -- 筛选前3名并列情况下可能超过3行三、场景二累计求和 / 移动平均ROWS BETWEEN分析师最常用的滚动计算场景——比如统计累计销售额、近7天移动平均。-- 公主号船长Talk数据分析进阶技巧 -- 场景按日期统计每日销售额以及累计销售额和近3日移动平均 SELECT sale_date, daily_sales, -- 累计求和从第一行累计到当前行 SUM(daily_sales) OVER ( ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- UNBOUNDED PRECEDING从分区第一行开始 -- CURRENT ROW到当前行结束 ) AS cumulative_sales, -- 近3日移动平均含当天当前行 前2行 AVG(daily_sales) OVER ( ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 2 PRECEDING往前数2行即3天 ) AS moving_avg_3day, -- 全部日期的总销售额不加ORDER BY时整个分区都是同一个值 SUM(daily_sales) OVER () AS total_sales FROM daily_sales_table ORDER BY sale_date;四、场景三同比/环比计算 LAG / LEADLAG 取「前一行」LEAD 取「后一行」做同比环比神器。-- 公主号船长Talk -- 场景计算每月销售额环比增长率 SELECT sale_month, monthly_sales, -- LAG取前一行的值上个月销售额 -- 参数LAG(列名, 偏移量N, 默认值) -- 偏移量1表示取前1行默认值NULL表示第一行没有上一行时返回NULL LAG(monthly_sales, 1, NULL) OVER ( ORDER BY sale_month ) AS prev_month_sales, -- LEAD取后一行的值下个月销售额用于预测对比 LEAD(monthly_sales, 1, NULL) OVER ( ORDER BY sale_month ) AS next_month_sales, -- 环比增长率 (本月 - 上月) / 上月 * 100% ROUND( (monthly_sales - LAG(monthly_sales, 1) OVER (ORDER BY sale_month)) / LAG(monthly_sales, 1) OVER (ORDER BY sale_month) * 100, 2 ) AS mom_growth_pct -- MoM Month over Month FROM monthly_sales_table ORDER BY sale_month; -- 同比去年同期用 LAG 偏移12个月 -- LAG(monthly_sales, 12, NULL) OVER (ORDER BY sale_month) AS yoy_sales五、场景四百分位数 / NTILE 分桶用户分层、流量分组实验常用的分桶操作。-- 公主号船长Talk数据分析关注不迷路 -- 场景将用户按消费金额分成4个层级NTILE分桶 SELECT user_id, total_spend, -- NTILE(4)将数据等分为4桶返回桶编号1~4 -- 桶1 消费最低的25%用户潜力用户 -- 桶4 消费最高的25%用户VIP用户 NTILE(4) OVER ( ORDER BY total_spend ASC -- 按消费额从低到高排序后分桶 ) AS spend_quartile, -- PERCENT_RANK当前行在分区内的百分比排名0~1之间 -- 公式(rank - 1) / (总行数 - 1) ROUND( PERCENT_RANK() OVER (ORDER BY total_spend), 4 ) AS pct_rank, -- CUME_DIST累积分布比当前值小或等于的行数占比 ROUND( CUME_DIST() OVER (ORDER BY total_spend), 4 ) AS cumulative_dist FROM user_spend_table; -- 实用技巧CASE WHEN NTILE 打标签 -- CASE NTILE(4) OVER (ORDER BY total_spend) -- WHEN 1 THEN 待激活 -- WHEN 2 THEN 普通用户 -- WHEN 3 THEN 活跃用户 -- WHEN 4 THEN VIP用户 -- END AS user_level六、场景五分组内去重保留最新记录这是 ETL 数据清洗最常见的需求同一用户有多条记录只保留最新的那条。-- 公主号船长Talk -- 场景用户信息表有重复按 user_id 保留最新的一条记录 -- 方法1ROW_NUMBER CTE推荐兼容性最好 WITH dedup AS ( SELECT user_id, user_name, phone, updated_at, -- 按 user_id 分组updated_at 倒序取第一行 ROW_NUMBER() OVER ( PARTITION BY user_id -- 同一用户的所有行 ORDER BY updated_at DESC -- 按更新时间倒序 ) AS rn FROM user_info ) SELECT user_id, user_name, phone, updated_at FROM dedup WHERE rn 1; -- rn1 就是每个用户最新的那条记录 -- 方法2直接子查询逻辑相同写法不同 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY updated_at DESC ) AS rn FROM user_info ) t WHERE t.rn 1;七、综合实战用户留存分析把前面所有技巧综合起来做一个经典的「次日/7日/30日留存率」分析。-- 公主号船长Talk更多数据分析实战关注持续更新 -- 场景分析用户注册后的留存情况 -- 前置user_events 表字段user_id, event_date, event_type -- Step1找到每个用户的首次登录日期注册日 WITH first_login AS ( SELECT user_id, MIN(event_date) AS register_date -- 最早的事件日期 注册日 FROM user_events WHERE event_type login GROUP BY user_id ), -- Step2计算每个用户每次登录距首次登录的天数差 login_diff AS ( SELECT e.user_id, f.register_date, e.event_date, DATEDIFF(e.event_date, f.register_date) AS days_since_register -- MySQL用DATEDIFFHive用datediffPostgreSQL用DATE_PART FROM user_events e JOIN first_login f ON e.user_id f.user_id WHERE e.event_type login ), -- Step3按注册日期分组计算各天留存人数 retention_count AS ( SELECT register_date, COUNT(DISTINCT CASE WHEN days_since_register 0 THEN user_id END) AS day0_users, COUNT(DISTINCT CASE WHEN days_since_register 1 THEN user_id END) AS day1_users, COUNT(DISTINCT CASE WHEN days_since_register 7 THEN user_id END) AS day7_users, COUNT(DISTINCT CASE WHEN days_since_register 30 THEN user_id END) AS day30_users FROM login_diff GROUP BY register_date ) -- Step4计算留存率加上7日移动平均使趋势更平滑 SELECT register_date, day0_users AS registered_users, ROUND(day1_users / day0_users * 100, 2) AS day1_retention_pct, ROUND(day7_users / day0_users * 100, 2) AS day7_retention_pct, ROUND(day30_users / day0_users * 100, 2) AS day30_retention_pct, -- 7日移动平均次日留存率平滑波动 ROUND( AVG(day1_users / day0_users * 100) OVER ( ORDER BY register_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW -- 7天移动窗口 ), 2 ) AS day1_retention_7day_ma FROM retention_count ORDER BY register_date;八、总结SQL 窗口函数核心速查函数用途常见场景ROW_NUMBER()唯一序号不重复去重保留最新、分页RANK()排名并列跳号TopN、竞赛排名DENSE_RANK()排名并列不跳号等级评定LAG/LEAD取前/后行数据同比/环比计算SUM/AVG OVER累计/移动计算累计销售额、移动平均NTILE(n)等分分桶用户分层、A/B实验PERCENT_RANK百分比排名分位数分析面试高频组合去重取最新 →ROW_NUMBER() PARTITION BY ORDER BY 时间 DESC分组 TopN →RANK() PARTITION BY 分组 WHERE rank N环比增长 →LAG(列, 1) OVER (ORDER BY 时间)累计求和 →SUM() OVER (ORDER BY 时间 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)-- 公主号船长Talk坚持更新数据分析干货欢迎关注 -- 建议把本文代码放到 MySQL/Hive/PostgreSQL 环境中实际跑一遍印象会深很多 -- 有问题欢迎评论区留言船长看到都会回复