★ FEATURED ARTICLE 忘了从什么时候开始面试问窗口函数几乎成了数据分析岗的标配工作中写复杂报表也绕不开它。但说实话我见过不少做了两三年取数需求的同事窗口函数还停留在ROW_NUMBER()排序的阶段碰到分组内TopN同比环比连续登录天数这类需求第一反应还是自连接、子查询SQL写得又臭又长性能还差。这篇文章我打算把窗口函数从头到尾梳理一遍——它到底解决了什么问题、哪些函数最常用、每个函数在什么场景下该用以及我在实际业务里踩过的那些坑。适合刚接触窗口函数想系统学一遍的新人也适合日常取数但总在几个函数上打转的初级分析师。1. 窗口函数解决的三个核心问题1.1 为什么普通聚合函数做不到的事窗口函数能搞定先说个最简单的例子。有一张订单表orders字段是region区域、sales_amount销售额我想查每个区域的销售额以及每个订单占全区域销售额的百分比。用普通聚合函数写出来长这样SELECT region, SUM(sales_amount) AS region_total FROM orders GROUP BY region;这没问题。但如果需求变成每个订单保留原始行同时在旁边加一列显示该区域的总销售额普通分组聚合就废了——GROUP BY会把多行压缩成一行你拿不到订单明细。这时候就得靠窗口函数SELECT order_id, region, sales_amount, SUM(sales_amount) OVER(PARTITION BY region) AS region_total FROM orders;OVER()关键字就是窗口函数和普通聚合函数最直观的区别普通聚合把多行揉成一行窗口函数让每行保留原样同时把计算结果带在每一行旁边。用生活类比来说普通聚合是全校按班级汇总成绩单每班一行窗口函数是每个学生手里贴着自己班级的平均分学生信息和班级统计信息同时在一条记录里展示。这个能力看起来简单但在数据分析里非常关键——因为它让明细汇总的同屏展示成为可能。1.2 窗口函数的执行顺序先WHERE还是先开窗很多人会把窗口函数和GROUP BY混用然后报错根本原因是不清楚SQL执行顺序。简单画个执行顺序FROM/JOIN取数WHERE过滤GROUP BY分组HAVING过滤分组窗口函数计算发生在SELECT阶段ORDER BY排序这里有个关键点窗口函数在WHERE之后执行所以你不能在WHERE里直接对窗口函数的结果做过滤。想过滤窗口计算结果需要套一层子查询或者WITH临时表。比如我想查比同区域平均销售额高的订单SELECT * FROM ( SELECT order_id, region, sales_amount, AVG(sales_amount) OVER(PARTITION BY region) AS avg_region FROM orders ) t WHERE sales_amount avg_region;我刚入行的时候写过WHERE sales_amount AVG(...) OVER(...)直接报错后来才知道窗口函数在WHERE之后才轮到算。1.3 什么场景下你才真正需要窗口函数这里给你三个我实际工作中最常见的判断信号保留明细的同时要附带聚合值比如订单明细旁边带上店铺月销售额、用户订单总额。分组内做排名或取TopN比如每个品类下销售额前3的SKU每个用户最近一次下单时间。跨行计算比如计算前一天销售额的差值、累计到当天的销售额、行与行之间的时间间隔。如果你看到的需求有这三类信号里的任何一个优先考虑窗口函数而不是自连接或子查询。窗口函数写起来更直观而且多数数据库对窗口函数的执行优化比自关联要好。2. 五大类窗口函数用法、语法与典型业务场景2.1 聚合窗口函数SUM、AVG、COUNT、MAX、MIN这组函数就是聚合函数加了个OVER()语义完全一致。区别在于窗口函数可以加PARTITION BY做分组也可以不加——不加就是全表作为一个窗口。基础用法示例SELECT order_id, region, sales_amount, SUM(sales_amount) OVER(PARTITION BY region ORDER BY order_date) AS region_cum_sum, AVG(sales_amount) OVER(PARTITION BY region) AS region_avg, COUNT(*) OVER(PARTITION BY region) AS region_order_cnt FROM orders;注意上面这个例子region_cum_sum那列我加了ORDER BY order_date这时候算的是组内截至当前行的累计值不是整个组的总和。这是聚合窗口函数最容易被忽视的行为——ORDER BY放进OVER()里会改变计算范围。提示SUM(...) OVER(PARTITION BY region)不带ORDER BY算的是全组总和SUM(...) OVER(PARTITION BY region ORDER BY order_date)算的是从组内第一行到当前行的累计和。两者业务含义完全不同写之前想清楚。聚合窗口函数在业务里最常见的用途是算占比和累计值。比如算各品类销售额贡献占比SELECT category, sales_amount, sales_amount / SUM(sales_amount) OVER() AS pct_of_total FROM products;OVER()不带任何参数表示对所有行开窗也就是全局总和。这个写法比子查询算总和再关联要清爽得多。2.2 排序窗口函数 ROW_NUMBER、RANK、DENSE_RANK这三个函数放在一起说因为它们的语法完全一样只是排名规则不同函数排名规则示例成绩90, 85, 85, 80ROW_NUMBER()连续编号同值随机排1, 2, 3, 4RANK()同值同排名下个排名跳号1, 2, 2, 4DENSE_RANK()同值同排名下个排名不跳号1, 2, 2, 3先记住它们的区别再对应到业务ROW_NUMBER()取每组第一名时用。因为即使分数相同结果也只有一行适合每个分类取一个代表。RANK()适合并列冠军场景比如竞赛排名、成绩排名。DENSE_RANK()适合名次连续的榜单比如积分排行榜不想因为并列导致后面名次空缺。实际看一个每个品类销售额前三的SKU的例子SELECT category, sku_id, sales_amount, ROW_NUMBER() OVER(PARTITION BY category ORDER BY sales_amount DESC) AS rn FROM sku_sales QUALIFY rn 3;如果你用的数据库不支持QUALIFY比如MySQL就包一层子查询SELECT * FROM ( SELECT category, sku_id, sales_amount, ROW_NUMBER() OVER(PARTITION BY category ORDER BY sales_amount DESC) AS rn FROM sku_sales ) t WHERE rn 3;这里提醒一句取TopN默认用ROW_NUMBER()而不是RANK()。业务上前三名通常是按名次取三条记录如果三个人并列第一RANK()会把并列的都取出来导致超过3条。2.3 偏移函数 LAG、LEAD——行与行之间的前后脚LAG()取上一行的值LEAD()取下一行的值。这对函数在分析时间序列数据时几乎天天用比自连接效率高很多。基本语法LAG(column, offset, default_value) OVER(PARTITION BY ... ORDER BY ...)offset默认是1表示上一行default_value是取不到时用的默认值通常写0或者NULL。实际业务里最典型的用法是算环比SELECT order_date, SUM(sales_amount) AS daily_sales, LAG(SUM(sales_amount), 1, 0) OVER(ORDER BY order_date) AS prev_day_sales, SUM(sales_amount) - LAG(SUM(sales_amount), 1, 0) OVER(ORDER BY order_date) AS day_over_day_diff FROM orders GROUP BY order_date ORDER BY order_date;这段SQL先按天聚合销售额再用LAG拿到前一天销售额算出差值。如果用自连接你得先聚合生成临时表再JOIN自己一天写法繁琐还容易错。还有一个高频需求每个用户相邻两次下单的间隔天数。这个用LAG写非常漂亮SELECT user_id, order_date, LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date) AS prev_order_date, DATEDIFF(order_date, LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date)) AS days_between FROM orders;第一行prev_order_date是NULLdays_between也是NULL这正好表示这是该用户的首次下单。记得这种数据在后续处理时要过滤NULL。2.4 首尾取值与访问函数FIRST_VALUE、LAST_VALUE、NTH_VALUE这几个函数用于在窗口内取第一行、最后一行、第N行的值。其中FIRST_VALUE比较常用LAST_VALUE和NTH_VALUE使用时要小心一个经典坑——默认窗口边界问题。演示一下SELECT user_id, order_date, FIRST_VALUE(order_date) OVER(PARTITION BY user_id ORDER BY order_date) AS first_order_date, LAST_VALUE(order_date) OVER(PARTITION BY user_id ORDER BY order_date) AS last_order_date FROM orders;猜猜last_order_date是什么很多人以为是该用户最后一单的日期但实际结果是当前行对应的订单日期。原因是OVER(PARTITION BY user_id ORDER BY order_date)默认的窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW——从分组起点到当前行而不是整个分组。所以LAST_VALUE看到的最后一行是当前行自己。要做对必须显式声明窗口边界LAST_VALUE(order_date) OVER( PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_order_date这个ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING把窗口扩到整个分组结果才对。我建议能用FIRST_VALUE和LAG解决的问题不要用LAST_VALUE这个函数默认行为确实容易翻车。2.5 分组序号函数 NTILE 辅助定位函数NTILE(n)把分组内有序数据切成n份每份尽量等量。它很适合做分层分析比如把用户按消费金额分成高、中、低三档SELECT user_id, total_spent, NTILE(3) OVER(ORDER BY total_spent DESC) AS tier FROM ( SELECT user_id, SUM(sales_amount) AS total_spent FROM orders GROUP BY user_id ) t;tier 1是消费最高的那批用户tier 3是消费最低的。这里NTILE(3)的3代表切成3层比例基本是 33.3% / 33.3% / 33.3%。这个方法比CASE WHEN手写阈值要公允得多——手写阈值是拍脑袋定边界NTILE是看数据自身分布切分。不过要注意NTILE切分是按记录数均分的切出来的组人数几乎相等不代表金额边界均等要说清楚你在做的是人数等分不是金额等分。顺带提一下还有一个很冷门但偶尔好用的CUME_DIST()它算的是当前行值在窗口内的小于等于比例取值0到1。想快速看某个值排在什么分位可以偶尔用一下日常需求用得不多。3. 窗口帧Frame机制决定你的计算范围3.1 三种窗口边界与默认行为窗口函数计算时真正决定对哪些行求和的是窗口帧frame。帧就是窗口内进一步圈定的行范围。可以显式指定帧范围语法如下ROWS BETWEEN 起点 AND 终点起点和终点可选的选项包括UNBOUNDED PRECEDING分组内第一行N PRECEDING往前第N行CURRENT ROW当前行N FOLLOWING往后第N行UNBOUNDED FOLLOWING分组内最后一行最常见的组合SUM(sales_amount) OVER( PARTITION BY region ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount这就是从分组第一天累加到当前日期的累计金额。3.2 最后N天的滚动计算滚动N天求和是窗口帧最经典的应用。比如最近7天下单金额可以这样写SELECT order_date, daily_sales, SUM(daily_sales) OVER( ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS rolling_7d_sales FROM daily_sales_table;这里有个细节要注意ROWS BETWEEN 6 PRECEDING AND CURRENT ROW是按物理行数往前数6行如果中间有缺失日期它算的是最近6行而不是最近6天。如果要严格按前6个自然日来算得改用RANGE或先补全日期序列。RANGE和ROWS的区别ROWS按行数数RANGE按列值范围算。比如RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW会按日期字段取前6天内所有行即使有缺失也只有那几天的数据。但RANGE对数据类型和数据库支持有要求MySQL对RANGE帧只支持部分写法使用时先查一下版本支持情况。3.3 聚合窗口函数加不加ORDER BY的坑说个我真实踩过的坑。最开始我用聚合窗口函数时没太注意ORDER BY的影响写过这样一个SQLAVG(price) OVER(PARTITION BY category) AS avg_category -- 全组平均这个是全组平均没问题。但如果你写了AVG(price) OVER(PARTITION BY category ORDER BY price) AS avg_to_current它就从全组平均变成组内从第一行到当前行按price排序的平均值。同样一行函数只是多排了个序语义完全变了。业务侧如果没意识到这一点拿着这个数做对比会得出完全错误的结论。我的经验是只有明确想做累计或滚动计算时才在聚合窗口函数里加ORDER BY否则一律不加。4. 窗口函数在真实场景中的经典实战4.1 场景一每个品类销量Top 3商品需求从商品销售明细表里取每个品类销量前三的SKU。步骤拆解按category分组按sales_volume降序排序。用ROW_NUMBER()标记组内排名。外层过滤排名≤3。SELECT category, sku_id, sales_volume FROM ( SELECT category, sku_id, sales_volume, ROW_NUMBER() OVER( PARTITION BY category ORDER BY sales_volume DESC ) AS rn FROM sku_daily_sales WHERE stat_date 2025-01-01 ) t WHERE rn 3;这段逻辑就是分组排名取前N的标准范式写作顺序也是固定的先开窗标号再包一层过滤。记住这个结构类似需求都能套。如果数据库支持QUALIFY如 Databricks、Hive、Snowflake可以少包一层直接在WHERE后面写QUALIFY rn 3。MySQL和PostgreSQL不支持统一用子查询写法比较稳妥。4.2 场景二用户行为序列中的最近一次和首次次需求找出每个用户最近一次下单的订单信息。这在用户召回和流失分析里很常见。方法是用ROW_NUMBER()按用户分组、按下单时间倒序取排名为1SELECT user_id, order_id, order_date FROM ( SELECT user_id, order_id, order_date, ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY order_date DESC ) AS rn FROM orders ) t WHERE rn 1;每个用户首次下单则把ORDER BY order_date DESC改成ASC即可。这两个需求的本质都是分组内按时间取极值所在行。如果你用GROUP BY user_id加MAX(order_date)你得到的是日期拿不到那一行订单的完整信息还得再关联原表。窗口函数一步到位。4.3 场景三连续登录天数的判断需求找出连续登录超过3天的用户。这个题面试常问表面是日期差问题本质是分组问题。思路是这样的每个用户每天登录去重得到user_id和login_date。用ROW_NUMBER()按用户分组、按日期排序得到序号rn。关键一步用login_date减去rn天得到date_group。如果用户连续登录date_group会相同一旦中断date_group会变化。WITH user_daily AS ( SELECT DISTINCT user_id, login_date FROM login_log ), with_rn AS ( SELECT user_id, login_date, ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY login_date ) AS rn FROM user_daily ), date_groups AS ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS date_group FROM with_rn ) SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS consecutive_days FROM date_groups GROUP BY user_id, date_group HAVING COUNT(*) 3;这个思路的关键在于login_date每天加1号数rn也每天加1两者相减的结果就锁定了连续区间。中间断一天数就对不上了。我第一次看到这个解法时眼前一亮——它把连续性这种模糊概念转化为分组键的确定性判断。4.4 场景四同比环比与移动平均数据周报里最常见的指标就是环比昨天“同比去年同周“过去7天移动平均”。前面提过环比用LAG这里补一个移动平均的写法SELECT order_date, daily_sales, AVG(daily_sales) OVER( ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS avg_7d FROM daily_sales_table;移动平均在平滑短期波动时非常有用。比如某天销售额骤降一眼看日数据会以为是业务问题但看7日移动平均就会发现只是常规波动。分析师给老板汇报时指标背后加一个移动平均列往往能减少很多误判。同比的话就要考虑日期偏移了一般是在日期维度表里关联去年同期的数据或者用LAG配合固定周期偏移量。比如周维度数据LAG(..., 52)取去年同周日维度数据LAG(..., 365)取去年同期。注意闰年时用LAG(..., 364)会偏离一天这一块我处理时会额外加日期校验免得对不上。5. 使用窗口函数的五个高频踩坑点5.1 在WHERE里引用窗口函数直接报错开头提过窗口函数在WHERE之后才计算所以WHERE rn 1这种写法会报unknown column错误。正确姿势是子查询或QUALIFY。记住一句话窗口函数的结果只能用在SELECT、ORDER BY以及套一层的外部查询里。5.2 PARTITION BY和GROUP BY混用窗口函数自带分组能力不需要再写GROUP BY。反过来如果外层用了GROUP BY窗口函数的PARTITION BY逻辑就容易和分组冲突很可能得到你预期之外的行数。我见过有人这样写SELECT category, SUM(sales_amount) OVER(PARTITION BY category) FROM sku_sales GROUP BY category;这实际能得到每个分类的汇总值但窗口函数部分很可能多此一举。更干净的写法是直接用GROUP BY category接SUM()。窗口函数和GROUP BY不要混用在同一个查询里除非你非常清楚自己在做什么。5.3 ORDER BY对帧范围的隐式改变聚合窗口函数加不加ORDER BY决定了是全组聚合还是累计到当前行。排序窗口函数本身必须带ORDER BY不带会报错。建议动手写之前先想清楚我要的是整体值还是从开始到现在的值想清楚再决定要不要加ORDER BY。5.4 对NULL值的处理窗口函数计算时COUNT(col)会忽略NULLCOUNT(*)不会。SUM遇到NULL按0处理AVG的分母只算非NULL的行数。这些语义和普通聚合完全一致但在窗口场景更容易被忽视。比如按用户分组计算客单价订单金额字段有少量NULLAVG(sales_amount)不会报错但它把NULL那单排除在分母之外算出的客单价可能偏高。如果业务希望NULL按0算需要COALESCE(sales_amount, 0)后再求均值。5.5 性能隐患窗口函数不是银弹窗口函数比自连接简洁但不等于任何场景都更快。当数据量大、开窗维度多、排序字段复杂时窗口函数会导致大量排序和临时计算。你按user_id开窗并ORDER BY order_date数据库得先按这个排序构建窗口多组大窗口的排序成本比预聚合高不少。如果窗口函数跑在大宽表上超时我的处理思路是先在子查询里把字段裁掉只留分组键、排序列、计算列。能用LAG不要用自连接但LAG连续多次跨大窗口时看看能不能改成两次聚合。大数据集上取TopN优先考虑ROW_NUMBER配合过滤而不是保留全部排序结果再外层截断。6. 窗口函数的进阶技巧与我的个人体会6.1 用窗口函数做去重ROW_NUMBER的隐藏用法业务表里经常有重复记录DISTINCT只能去重却不知道保留哪条。这时候ROW_NUMBER()就有了独特价值WITH deduplicated AS ( SELECT *, ROW_NUMBER() OVER( PARTITION BY user_id, order_date ORDER BY created_at DESC ) AS rn FROM orders ) SELECT * FROM deduplicated WHERE rn 1;这句的效果是同一用户同一天有多条订单时保留created_at最新的那条。比MAX(created_at)分组再关联的写法简洁很多也是我日常清理数仓重复数据最常用的手段。6.2 用多个窗口函数组合完成复杂指标窗口函数可以叠加使用。比如既要用户首次下单日期又想要用户订单环比增长可以一条SQL里写多个OVER()SELECT user_id, order_date, MIN(order_date) OVER(PARTITION BY user_id) AS first_order_date, LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date) AS prev_order_date, DATEDIFF(order_date, LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date)) AS gap_days FROM orders;这个查询同时展示该用户首单时间、上一次下单时间、两次下单间隔。几个字段放在一屏后续可以直接用于分析用户的复购周期。6.3 我对窗口函数学习路线的建议如果让我给新人排一个学习顺序我会这样排先把SUM() OVER(PARTITION BY ...)跑熟理解明细带汇总的感觉。再练ROW_NUMBER()和RANK()掌握分组排名TopN的标准写法。然后上LAG/LEAD做时间序列的环比、同比。最后尝试NTILE、帧范围的滚动计算以及把这些函数组合成更复杂的分析。每一步都要对着真实业务题练只看语法书学不会。我当时就是拿线上报表需求反复改SQL错了再调慢慢才把窗口函数的执行顺序和帧范围这些抽象概念吃透。如果你正被某个窗口函数搞得头大建议先别急着搜窗口函数大全回到需求本身问三个问题我要不要保留明细行我是在组内排位还是跨行取值我想算的是累计值还是整体值把这三个问题回答清楚80%的窗口函数场景都能想明白怎么下笔。
阅读完成 · 觉得有帮助？
