做数据开发这些年我发现自己面试别人或者带新人的时候窗口函数是绕不开的一道坎。很多同学map join、动态分区、自定义UDF都玩得挺溜一碰窗口函数就开始犯迷糊row_number和rank到底差在哪为什么有的SQL要写rows between窗口函数和group by用起来效果明明差不多到底什么时候该用哪个窗口函数是Hive SQL里性价比最高的知识点没有之一。一次搞懂TopN、累计统计、同比环比、连续登录、数据去重这些问题全都能顺手解决。这篇文章我打算从执行逻辑讲到实战案例再补上那些文档里不太会写的坑希望能帮你在30分钟内建立起一个完整的心智模型。1. 窗口函数到底是个啥——先搞懂它的执行逻辑1.1 一句话理解逐行计算行数一个不丢很多教程上来就列函数清单其实没解决最根本的问题窗口函数和普通聚合函数group by到底有啥本质区别。我习惯用一个比方解释group by就像全校按班级集合每个班最后只能派一个代表汇报情况出来的是班级总数窗口函数则是每个同学都站在队伍里低头能看见自己和前后几个人每个人都能根据“视线范围内”的信息算出一个结果但队伍还是那个队伍一个都没少。这个“行数不变”是窗口函数最核心的特征。所以窗口函数不需要像group by那样把select里的非聚合字段全部塞进group by列表里。你完全可以select每个员工的姓名同时select该员工所在部门的平均工资这俩可以同时出现在同一行结果里。在写报表SQL、做数据探查的时候这种“明细和汇总并存”的能力特别香这也是为什么窗口函数在数据分析场景里几乎是刚需。1.2 语法骨架三大件要分清角色窗口函数的语法看起来就一行但拆开看其实有三个关键部分函数名() over ( partition by 分区字段 order by 排序字段 rows/range between ... -- 窗口子句可选 )partition by负责把数据分成一个个“窗口”不写的话整个结果集就是一个大窗口。order by决定窗口内的排序这一点对于排序类函数和累计类函数来说是灵魂。窗口子句则进一步控制“每一行到底能看多大范围”这块内容相对进阶我在第3章会专门展开。这里有一个新手极易忽略的点partition by不是group by。它不会把多行数据压成一行只是给每一行标记一个“组号”计算时只允许你在自己组内看数据。很多人在select里写了窗口函数又想加个group by结果把自己绕晕了核心就是没想明白这俩是完全不同的执行逻辑。1.3 执行顺序窗口函数为什么不能出现在where里要想彻底不踩坑必须知道SQL的执行顺序。一个完整的查询大概是这么走的先from取表然后where过滤再到group by分组、having过滤分组接着select里才轮到窗口函数干活最后才是order by排序。这就解释了为什么where里面不能写row_number()1这种条件——窗口函数在where之后才计算你想过滤只能在外面再套一层子查询。Order by阶段能用窗口函数的别名也是因为select已经算完了。这段执行顺序的知识看起来简单但后面排查很多诡异SQL报错时你一定会感谢此刻记住了它。2. 核心窗口函数盘点——哪个场景用哪个2.1 排序三兄弟row_number、rank、dense_rank三个函数放一起说因为它们长得像功能也像但结果有细微差别。很多老鸟可能觉得这很简单但实际上真能一次说对的还是少数。函数编号规则典型场景row_number()顺序编号1、2、3、4值相同也不并列给每一行标号取最新一条分页rank()相同值并列编号跳跃例如1、1、3排行榜允许并列且占用名额dense_rank()相同值并列编号不跳跃例如1、1、2取前N名并列不算额外名额我举一个业务例子。城市维度分析网约车司机数据按完成订单数排序select city_id, driver_id, order_cnt, row_number() over(partition by city_id order by order_cnt desc) as rn_sort, rank() over(partition by city_id order by order_cnt desc) as rk_sort, dense_rank() over(partition by city_id order by order_cnt desc) as dr_sort from dwd_driver_order_cnt如果司机A和司机B订单数相同都是100单那么他们的rn_sort会分别是1和2而rk_sort和dr_sort都会是1。区别在于第三名rank会直接跳到3dense_rank就是2。做业务报表时“并列算不算名额”会直接影响名额数量这通常是产品需求定的SQL层面就靠选rank还是dense_rank来解决。热词里那个“hive给每一行标号”本质上就是row_number的活。ETL清洗时想过滤出每个订单的最新状态row_number() over(partition by order_id order by event_time desc) 1这套写法是最高频的比distinct可控得多后面实战章节我会再演示完整SQL。2.2 前后行函数lag、leadlag和lead用来访问同一窗口中“前面第N行”或“后面第N行”的值。语法都是lag(字段, 偏移量, 默认值)lag往上找前一行lead往下找后一行。第三个参数可以不写不写的话越界时返回NULL。最典型的场景就是算同比环比。统计网约车平台每天的订单量要算日环比增长率select stat_date, order_cnt, lag(order_cnt, 1) over(order by stat_date) as prev_cnt, round((order_cnt - lag(order_cnt, 1) over(order by stat_date)) / lag(order_cnt, 1) over(order by stat_date) * 100, 2) as mom_rate from dws_order_daily第一行的prev_cnt是NULL因为往前没有数据了这时候业务上通常会用nvl(prev_cnt, 0)兜底或者在where里过滤掉。注意lag的偏移量不一定非得是1想看周环比就写7月环比就看业务定义的跨度这个参数很灵活。2.3 聚合窗口函数sum、avg、count、max、min聚合函数加上over之后身份就变了不再是一锤子买卖的汇总而是每个计算点都能看到一个动态范围的聚合结果。我见过最多误解的就是这里——很多人以为sum(amount) over(partition by user_id)跟group by user_id的sum一样其实不完全一样窗口版会让每一个用户的行都保留下来每行都附带这个用户的总金额。更高级的是配合order by产生累计效果select order_date, daily_amount, sum(daily_amount) over(order by order_date) as cum_amount from dws_order_daily这个SQL的意思是从分区起点到当前行一路累加。没有手动写窗口子句时order by会触发默认窗口“从分区起点到当前行”也就是累计求和。如果不写order by那窗口就是整个分区每行显示的都是同一个总数。理解这三档差异后面写移动平均、累计占比就都不难了。2.4 相对位置与分布函数ntile、cume_dist、percent_rank这几个函数使用频率没有前几位高但在特定场景里很好用。ntile(n)把窗口内所有数据按顺序尽量平均地切成n片返回当前行所在桶的编号。举个实际例子网约车平台要把司机按订单量分成等级段前20%是一等中间60%是二等后20%是三等用ntile(5)就特别直观每个桶占20%。cume_dist返回小于等于当前值的数据行数占总行数的比例percent_rank则用公式(rank - 1) / (总行数 - 1)计算相对排名。两者在计算收入分位数、衡量指标在群体中的位置时很有用。不过说实话大部分业务场景下用ntile就够覆盖了cume_dist和percent_rank更多是面试和算法特征工程里会用到理解概念即可。3. 窗口子句的精髓——rows和range这层窗户纸要捅破3.1 窗口子句到底在控制什么窗口子句的完整写法是这样的rows between 起点 and 终点起点和终点可以是unbounded preceding分区起点、n preceding往前n行、current row当前行、n following往后n行、unbounded following分区终点。三个高频组合我先列出来都是实际业务里最常见的rows between unbounded preceding and current row从分区起点到当前行这是累计逻辑。rows between n preceding and current row只看当前行和前n行这是移动计算逻辑。rows between current row and unbounded following从当前行到分区终点用于“未来累计”。我的一次真实经历跑网约车订单数据想看每个司机最近3单的平均金额。按order_time排序后写avg(amount) over(partition by driver_id order by order_time rows between 2 preceding and current row)直接得到滑动平均。如果没有rows子句默认的累计窗口会把前面所有订单都算进去数值完全不对。当时就是被这个细节坑过一次从此长记性了。3.2 rows和range物理行偏移还是数值范围偏移rows和range是窗口子句的两种模式很多人在这一块犯迷糊。我这样说应该好理解rows是按物理位置来划窗口说“前2行”就真的只看前面两行range是按order by字段的数值范围来划窗口你写range between 100 preceding and current row意思不是往前100行而是“当前行的排序字段值减去100到当前值”这个范围内的所有行。举个例子分析某商品的价格变化order by价格想统计“价格在当前价格正负5元以内的订单有多少单”。用range就很简单count(1) over(order by price range between 5 preceding and current row)price字段当前值是100的话窗口会包含价格在95到100之间的所有行不管物理上隔着多少行。如果用rows那就只会看物理上相邻的那几行跟价格差异一点关系都没有。业务含义完全不同写的时候一定想清楚你要的是“距离”还是“行数”。3.3 不写窗口子句时的默认规则默认规则是很多人写错SQL而不自知的重灾区。我总结成一句话有order by没写窗口子句默认是range between unbounded preceding and current row从分区起点到当前行并且值相等的行也会被算到一起没有order by默认整个分区。这个“值相等的行也会被算到一起”是range模式特有的行为在计算累计值时会有微妙差异。再提醒一点排序函数row_number、rank这些不允许写窗口子句因为标号逻辑必须基于完整分区进行不存在“只给前两行标号”这种操作。如果你的SQL写了row_number() over(order by x rows between ...)Hive会直接报语法错误这不是版本问题是语义上就不允许。4. 实战场景拆解——从网约车数据看窗口函数怎么干活4.1 给每一行标号row_number做数据清洗去重网约车项目里明细表经常出现重复数据。比如订单状态快照表每天全量同步同一订单id会有多条历史状态记录。业务上要取每个订单的最新状态直接group by然后取max(status)不够好因为你还想要状态对应的事件时间、更新人等字段。用row_number就是标准解法select order_id, driver_id, order_status, update_time from ( select order_id, driver_id, order_status, update_time, row_number() over(partition by order_id order by update_time desc) as rn from ods_order_status_snapshot ) t where rn 1这个SQL的思路是先给每个订单内部的记录按更新时间倒序编号最新的那条编号是1然后外层过滤rn1。相比distinct这种方式对“去重后保留哪一条”有完全的控制权。如果你要的不是最新一条而是最早一条只要把order by改成升序就行。4.2 分组TopN每个城市订单量前三的司机TopN是面试和实际业务里的常客。统计每个城市订单量排名前三的司机select city_name, driver_name, order_cnt from ( select city_name, driver_name, order_cnt, dense_rank() over(partition by city_name order by order_cnt desc) as rk from ( select city_id, driver_id, count(1) as order_cnt from dwd_order_detail where dt 2024-06-01 group by city_id, driver_id ) driver_cnt join dim_city on dim_city.city_id driver_cnt.city_id ) t where rk 3这里有一个细节内层先用group by把每个司机每天的订单数算出来外层再用dense_rank排名。为什么用dense_rank而不是rank因为业务要求“前三名”如果第三名并列两个人用rank的话并列第三都会被卡掉名额就少了用dense_rank则不会出现名次空洞。具体用哪个一定先跟业务对齐。4.3 连续登录天数经典连续N天问题连续登录问题在数据分析岗笔试里出现频率极高。核心思路是用日期减去行号连续的日期减完行号后会落在同一个值上然后按这个值分组计数。直接看例子select user_id, date_sub(login_date, rn) as group_id, count(1) as continuous_days from ( select user_id, login_date, row_number() over(partition by user_id order by login_date) as rn from dwd_user_login ) t group by user_id, date_sub(login_date, rn)假设某用户1号、2号、3号连续登录然后是5号登录。1号的rn是11号减1天是上个月的最后一天2号rn是22号减2天也是同一天3号rn是3同样得到同一个group_id所以这三天被归为一组。5号rn是45号减4天得到的日期就不一样了会被分到另一组。这样一来每个组的天数就代表一段连续登录的长度。4.4 滑动窗口与移动平均按小时接单量趋势处理时间序列数据时原始数据经常波动剧烈。比如网约车平台看每个小时的接单量早高峰和午休之间忽高忽低直接看折线图很难判断趋势。这时候移动平均就派上用场了它能平滑掉短期抖动让趋势线更清晰select hour_slot, order_cnt, round(avg(order_cnt) over(order by hour_slot rows between 4 preceding and current row), 2) as moving_avg_5h from ( select hour_slot, count(1) as order_cnt from dwd_order_detail where dt 2024-06-01 group by hour_slot ) t窗口子句rows between 4 preceding and current row表示每个计算点只看“当前小时和前面4个小时”这就是5小时的移动平均。注意小时是离散值如果用range来做可能会出现预期之外的窗口大小这里用rows更加可控。5. 高频踩坑与排查建议——这些坑我基本都踩过5.1 窗口函数不能出现在where里这个错栽倒过不少人前面执行顺序那里说过窗口函数在select阶段才生效where阶段它还不存在。所以where row_number() over(...) 1这种写法Hive直接报错。解决办法是子查询包一层。-- 错误写法 select * from t where row_number() over(partition by uid order by dt desc) 1 -- 正确写法 select * from ( select *, row_number() over(partition by uid order by dt desc) as rn from t ) tmp where rn 1类似的问题还有在where里使用sum(amount) over(...)原理一样不再赘述。5.2 窗口函数和group by混用时的执行顺序问题一个很常见的场景想统计每天的总金额同时看每天金额占全月的比例。新手容易写成select order_date, sum(amount) as daily_amount, sum(amount) / sum(amount) over() as daily_ratio from dwd_order_detail group by order_date这里有个值得注意的点sum(amount) over()是在group by执行完之后才计算的所以它算的是所有分组结果的总和不是明细表的总和。大多数业务场景下这俩结果一致但如果分组前有过滤条件严格来说语义有差异。更要紧的是在select里同时用聚合和窗口函数时如果你没意识到这俩处于不同执行阶段写复杂SQL时很容易结果正确但逻辑理解错误。我的建议是凡是窗口函数和group by同时出现先在脑子里过一遍执行顺序再用explain验证。5.3 partition by字段选择不当引发的性能问题窗口函数在数据量大的表上跑得慢常见原因有三个。第一partiton by的字段取值太分散会导致每个reduce处理的数据量差异巨大也就是数据倾斜。比如直接用订单id做partition by某个大客户订单量是别人的几百倍那个reduce就要拖后腿。解决办法是看业务上能不能换字段或者在源头把数据先做一层聚合。第二窗口函数配全局order by时要注意order by的位置。为了排序Hive可能把数据集中到少数reducer上这时候并行度上不去。如果只是分区内排序比如partition by city_id order by order_cnt desc每个分区独立排序压力分散得多。第三窗口函数经常需要把同一分区的数据拉到同一个reducer如果数据量极大建议先用group by预聚合缩小数据规模再套窗口函数。5.4 顺手聊聊hive优化小文件和flink sink hive表数据不入表热词里有两个跟Hive日常运维强相关的问题我顺便说说自己排查这类问题的思路。一个是hive小文件过多另一个是flink sink hive表数据不入表。小文件问题不是窗口函数直接造成的但窗口函数做数据清洗后如果频繁insert overwrite配合动态分区写入很容易产生大量小文件。我自己常用的优化手段一是跑完数据后用distribute by rand()重新打散减少reducer输出文件数二是打开hive.merge.mapfiles和hive.merge.mapredfiles参数让Hive在写入时自动合并小文件。还有一招用窗口函数给数据按某个key标号然后按标号取模决定写入哪个reducer这本质上也是一种数据重分布策略。flink sink hive表数据不入表我遇到过的排查顺序一般是先看flink任务日志有没有报错被吞掉再查hive表是不是分区表、sink时有没有指定分区最后看hive metastore的元数据有没有刷新。很多时候数据其实写进去了但在Hive端查不到是因为msck repair table没有执行、分区信息没同步。这时候可以写一个窗口函数统计预期的行数和实际写入行数做一个快速校验定位到底是写丢了还是没提交成功。排查思路远大于具体命令方向对了问题很快就水落石出。5.5 删除带乱码的分区时要注意什么还有一个实际运维里很隐蔽的坑分区字段值如果因为编码问题变成了乱码比如dt2024-06-01??在做partition by时会匹配不上导致窗口函数结果不对。这种分区看起来没多少数据但删又删不掉相当烦人。我的处理方案是先用SQL查一下所有分区找到乱码的partition字段值然后手写alter语句把乱码分区数据挪走或直接删掉alter table dwd_order_detail drop partition(dt2024-06-01??);如果数据还有用可以先把数据insert到正常分区再drop乱码分区。这类问题看似跟窗口函数无关但实际上分区键乱掉之后所有基于分区字段的统计都会出问题排查时容易怀疑人生。养成定期检查分区元数据的习惯能省很多事。6. 排查工具箱explain、小数据验证、逐步拆分遇到窗口函数相关SQL跑不出来或者结果不对我一般按三个步骤来。第一步explain看执行计划重点看reduce阶段对窗口函数的处理逻辑确认数据是不是被错误地拉到了一个节点。第二步用小数据集快速验证比如在where里加个limit 1000或者过滤某一天的数据手工算一遍期望结果跟SQL输出对比。窗口函数的结果可预期性很强手算几行就能判断问题出在窗口定义还是数据本身。第三步如果SQL太长从内往外拆先单独跑子查询确认每个子查询的输出都符合预期再逐层往外扩展。窗口函数本身不难难的是多层嵌套时上下文关系太绕拆开看就会清晰很多。7. 一点个人经验跟窗口函数打了这么多年交道我最深的体会是不要先背语法要先回答一个问题——我要的是“逐行计算”还是“逐组汇总”。想明白这个函数选择自然就出来了。要逐行标号用row_number要逐行看前后值用lag/lead要逐行算累计移动值用聚合函数加over要分组排名用rank/dense_rank。窗口子句那部分建议动手写几个例子把rows和range的语义吃透这是区分新手和老手的分水岭。最后分享一个我的练习方法拿一张自己最熟悉的业务表把平时用group by写的报表SQL全部改成窗口函数版本。比如“每个城市的日单量”用窗口函数写一遍“每个司机的累计收入”写一遍你会发现写着写着就通透了。窗口函数的坑一踩一个准但只要你理解执行顺序和窗口边界这两个核心点绝大多数问题都能迎刃而解。