开窗函数在离线数仓里的地位基本等同于炒菜用的盐——你可能不是天天盯着它看但少了它很多业务需求根本没法做。做数据开发这行当SQL写得好不好差别往往就体现在这些地方别人用一堆子查询绕来绕去才跑出来的结果你一个开窗函数两三行就能搞定而且执行效率还更高。这篇内容我把开窗函数在离线数仓里的实际应用从头梳理一遍包括它和group by的本质区别、三大类函数的用法对比、我在真实ETL开发中反复用到的场景以及新手最容易踩的坑。无论是刚入行的数据开发还是写了好几年SQL但一直没把窗口函数吃透的同学这份总结应该都能帮到你。1. 开窗函数到底是什么先搞清楚它和group by的区别1.1 开窗函数的核心执行逻辑很多人在学开窗函数的时候第一个困惑就是这不就是分组统计吗其实不是。group by是多进一出若干行数据经过聚合函数计算后每组只输出一行结果其他明细行直接被压缩掉了。而开窗函数是多进多出它同样按某个维度分区但每一行原始数据都会保留聚合或者排序的结果作为新的一列附加到每一行后面。我用一个例子来说明。假设有一张销售明细表记录了每个订单的金额订单id销售员订单金额1001A30001002A50001003B20001004B8000如果你想看每个销售员的总业绩用group by就是标准答案输出只有两行A和B各一行。但如果你不仅想看总业绩还想让每个订单旁边都带着所属销售员的总业绩比如做占比分析group by就做不到直接保留明细了。这时候开窗函数出场select 订单id, 销售员, 订单金额, sum(订单金额) over(partition by 销售员) as 销售员总业绩 from 订单明细表结果每一行都在同时多了一列销售员总业绩。这就是开窗函数最大的价值在不丢失明细行的前提下完成分组聚合、排序、偏移取值等操作。1.2 开窗函数的语法骨架开窗函数的语法可以拆成三块函数名() over ( partition by 分区字段 order by 排序字段 rows/range between 窗口起点 and 窗口终点 )partition by负责分区相当于给数据分组分区字段相同的行会进入同一个窗口。可以一个字段也可以多个字段组合分区。order by负责分区内的排序它不仅决定了窗口内数据的顺序还直接影响部分函数的结果比如row_number的编号顺序。rows between这一块很多人容易忽略它定义了窗口的滑动范围比如从当前行的前3行到当前行或者从分区第一行到当前行。如果不写默认是range between unbounded preceding and current row也就是说从分区起点到当前行这个默认值在计算累计值时特别常见。这个语法看似简单真正用起来的时候难点在于窗口边界怎么定和三个函数类别的选择。下面我逐个拆解。2. 三大类开窗函数用法细解开窗函数从功能上分大致可以分成三大类聚合开窗、排序开窗、取值开窗。每一类在离线数仓里都有非常明确的应用场景下面逐一讲透。2.1 聚合开窗sum、avg、count、max、min的窗口用法聚合开窗就是把sum、avg、count这些聚合函数搬到over子句里最常见的用法是累计值、移动平均、分区占比三类。累计值写法几乎固定就是sum(字段) over(partition by 分区字段 order by 排序字段)比如计算每个客户的累计消费金额select 客户id, 消费日期, 消费金额, sum(消费金额) over(partition by 客户id order by 消费日期) as 累计消费金额 from 消费明细表注意这里的默认窗口边界是从分区起点到当前行所以随着消费日期推进累计值不断累加。这个需求如果用自关联去写性能差而且代码啰嗦开窗是首选。移动平均是另一个高频用法比如计算最近7天的日均销量就需要用到rows between来限定窗口select 日期, 销量, avg(销量) over(order by 日期 rows between 6 preceding and current row) as 近7日均值 from 每日销量表这里有个细节如果分区字段是日期还需要小心数据粒度日期字段可能存在重复比如一天内有多条记录时order by 日期配合rows between会产生同一日期下的多行挤在同一窗口段的情况逻辑上是没错的但和业务想看的最近7天可能口径不一致。解决方式是在日期粒度做聚合后再开窗或者用range between interval 6 day preceding and current row。聚合开窗还有一个用途是做明细和汇总并存的加工。在做数仓明细层的时候经常会遇到既要每笔订单明细又要每个客户的总金额做占比的需求开窗函数一条SQL搞定不用join子查询。2.2 排序开窗row_number、rank、dense_rank的微妙差别排序类开窗函数是面试和实际开发都绕不开的三兄弟。它们的语法完全一样row_number() over(partition by 分组字段 order by 排序字段) rank() over(partition by 分组字段 order by 排序字段) dense_rank() over(partition by 分组字段 order by 排序字段)三者区别全在并列名次的处理上。用一个学生成绩的例子看最直观学生班级成绩A1班95B1班90C1班90D1班85row_number非并列结果就是1、2、3、4即使B和C成绩一样也会分出名次。rank并列跳号B和C并列第2下一个D就是第4名。dense_rank并列不跳号B和C并列第2下一个D是第3名。离线数仓里最常用的其实是row_number因为绝大多数场景只需要唯一序号比如取每个分组前N条记录、给事实表加自增序号、去重时保留一条。而rank和dense_rank更多用在直接展示排名的业务里比如销售排行榜、竞赛排名。实战中我习惯的做法是取TopN一律用row_number因为它生成的序号是严格唯一的不会因为排序字段并列导致取出的行数超过预期。rank和dense_rank更适合指标本身带有并列排名语义的业务场景。2.3 取值开窗lag、lead、first_value、last_value、nth_value取值类开窗函数在时间序列分析里作用极大其中最常用的是lag和lead。lag(字段, n)取当前行往前第n行的值。lead(字段, n)取当前行往后第n行的值。first_value(字段)取窗口内的第一个值。last_value(字段)取窗口内的最后一个值。nth_value(字段, n)取窗口内第n个值。lag是做同环比、增量计算、状态变化检测的核心工具。比如计算每个用户相邻两次消费的时间间隔select 用户id, 消费日期, lag(消费日期, 1) over(partition by 用户id order by 消费日期) as 上次消费日期, datediff(消费日期, lag(消费日期, 1) over(partition by 用户id order by 消费日期)) as 消费间隔天数 from 消费明细表这里有一个非常关键的点所有读者务必记住first_value和last_value的窗口边界陷阱。如果只写last_value(字段) over(partition by 分组字段 order by 排序字段)你得到的可能不是整个分区的最后一个值而是从分区起点到当前行范围内的最后一个值。因为在有order by的情况下默认窗口是range between unbounded preceding and current row并不是整个分区。要取整个分区的最后一行必须显式声明窗口边界last_value(字段) over(partition by 分组字段 order by 排序字段 rows between unbounded preceding and unbounded following)这个坑我在实际项目里至少踩过两次每次都是结果看着差不多但对账的时候发现末尾几条数据有问题。用last_value的时候养成为显式写窗口边界的好习惯能省下很多排查时间。3. 离线数仓里的四个高频实战场景如果把窗口函数只是停留在会写语法的层面那是远远不够的。在离线数仓的实际开发中下面这四个场景可以说是把开窗函数用得最淋漓尽致的地方几乎每个数仓项目都会碰到我把每一个场景的完整思路和写法都拆出来。3.1 场景一分组TopN分组TopN在离线数仓里太常见了。比如运营要看每个品类下销量最高的前5个商品就需要先按品类分组再在每个分组内按销量排序取出前5名。不用开窗函数的写法通常是用子查询自关联逻辑繁琐而且性能堪忧。用row_number就清爽得多我通常把它包成子查询再过滤排名select 品类id, 商品id, 销量 from ( select 品类id, 商品id, 销量, row_number() over(partition by 品类id order by 销量 desc) as 排名 from 商品销量汇总表 ) t where 排名 5有几个细节需要注意。第一如果你还想在结果里同时展示这个商品的销量在品类内的排名百分比可以额外加一个count() over(partition by 品类id)两列配合就能算出排名/总数的占比。第二取TopN时排序字段如果有并列值且用的是row_number每个商品都会拿到唯一的序号不存在多取行的问题所以TopN场景默认用row_number是稳妥的选择。第三如果TopN的N会动态变化比如运营昨天要前5今天要前10建议把N做成参数而不是频繁改SQL。3.2 场景二同环比同环比计算在经营分析类报表里是标配需求。所谓同比就是和去年同期比所谓环比就是和上一个周期比。用lag函数能把这类计算做得非常优雅。比如按天统计订单金额表要计算每天的环比增长率即今天比昨天增长多少百分比select 日期, 订单金额, lag(订单金额, 1) over(order by 日期) as 昨日订单金额, round((订单金额 - lag(订单金额, 1) over(order by 日期)) / lag(订单金额, 1) over(order by 日期) * 100, 2) as 环比增长率 from 每日订单汇总表如果需要算同比比如对比去年同期常见的做法是先按月份日期维度关联到去年同期的数据或者利用lag的跨度参数把日期按年维度拉通排序后取第365天前的值。第二种写法需要注意数据的连续性问题中间缺数据会导致lag取错行。更稳妥的方案是在日期维度表里显式维护去年同期日期字段再用日期关联。3.3 场景三连续登录识别连续登录识别是经典的用户行为分析题目很多公司考核数据分析师SQL功底就爱用这道题。需求一般是找出连续登录超过7天的用户。用开窗函数配合日期差值可以简短地写出非常清晰的解法核心思路三步走第一步将用户登录日期去重排序。第二步用日期字段和row_number计算一个偏移日期。第三步按偏移日期分组组内数量大于等于7的就是连续登录的用户。select 用户id, 连续登录组, count(1) as 连续登录天数 from ( select 用户id, 登录日期, date_sub(登录日期, row_number() over(partition by 用户id order by 登录日期)) as 连续登录组 from 用户登录记录表 group by 用户id, 登录日期 ) t group by 用户id, 连续登录组 having count(1) 7这里的巧妙之处在于连续日期的日期值减去递增序号得到的差值是一个常数。比如用户从第5天到第7天连续登录3天5减1等于46减2等于47减3等于4差值相等说明这些日期是连续的。中间断一天这个差值就会变。这个思路是离线数仓处理连续性问题的基本套路在新客激活、签到分析、异常登录监测里都能复用。3.4 场景四会话划分会话划分是用户行为分析里非常常见的预处理步骤。比如埋点明细表里每个用户有一串行为事件从打开App到退出算一次会话但两次操作间隔超过30分钟就要重新算一次会话。这个需求用lag和sum开窗组合可以一气呵成。第一步用lag计算每条行为和上一条行为的时间差。第二步标记出时间差大于30分钟的行为作为会话新起点。第三步用sum开窗对是否为新起点做累计求和生成会话IDselect 用户id, 行为时间, 事件类型, sum(会话新起点标记) over(partition by 用户id order by 行为时间) as 会话id from ( select 用户id, 行为时间, 事件类型, case when lag(行为时间, 1) over(partition by 用户id order by 行为时间) is null or 行为时间 - lag(行为时间, 1) over(partition by 用户id order by 行为时间) 30 then 1 else 0 end as 会话新起点标记 from 用户行为埋点表 ) t这个组合技巧把lag和累加开窗的能力衔接了起来算是我个人最喜欢的窗口函数组合之一。后面的会话内路径分析、会话时长统计、漏斗分析都是在这个基础上继续延伸的。4. 新手最容易踩的坑和我的排查套路4.1 错误在开窗函数的结果上做再聚合这个坑非常典型。很多人以为有开窗函数后就不需要group by了结果写出来类似这样的SQL-- 错误示例 select 部门id, avg(sum(薪资) over(partition by 部门id)) as 平均薪资 from 员工表开窗函数返回的结果是逐行的它不是聚合输出原则上不能直接作为外层聚合函数的输入再聚合。如果真的需要对聚合后的结果再聚合层级就得分清楚先做一次group by生成汇总结果再用子查询或者CTE继续加工。开窗和group by不是替代关系很多场景需要配合使用。4.2 错误窗口边界概念模糊前面提过凡是带order by的over子句默认窗口范围是range between unbounded preceding and current row。如果不清楚这一点写last_value时经常得到看起合理但结果错误的数据。沉淀下来的经验是除非你明确知道从分区起点到当前行正好满足业务口径否则一律显式写窗口边界。尤其在做移动窗口、累计窗口的时候边界写不对数据差得悄无声息。4.3 排序并列和稳定性的问题使用row_number做数据去重的时候很有可能因为排序字段没有涵盖到唯一粒度导致相同排序值的行每次执行得到一个随机的顺序。比如同一张表同一批数据今天跑出来保留id为1001的记录明天跑出来保留1002的记录。避免这种问题的方法很简单在order by里补上可以唯一标识行的字段让每行有一个确定的排序位置。4.4 性能优化经验窗口函数性能在离线数仓里一般不是最大的瓶颈但如果用不好也能显著拖慢任务。几条我实测下来的经验第一能在where过滤掉的数据一定提前过滤开窗是在结果集上进行计算的数据量越小越快第二分区字段尽量选择基数不是特别高的字段比如按部门而不是按员工id分区第三一个SQL里同时出现多个开窗函数时如果它们分区和排序字段一致可以考虑合并成同一条over子句复用减少shuffle次数第四大表做TopN的时候先通过group by缩窄范围再开窗比直接对明细大表开窗快不少。4.5 和distinct的冲突处理开窗函数对distinct的支持在不同引擎里行为不一致处理不当容易出现重复计数。有一个很稳的规避方式如果要去重后做计数并希望保留明细可以先在子查询里group by去重再在外层开窗做聚合统计把去重和加窗两步拆开语义清晰执行也稳定。5. 开窗函数在数仓开发里的一些使用规范建议最后想输出一些开发规范层面的经验。这些东西不是教材上写的都是我在实际项目中踩过、沉淀下来的我觉得对做数仓开发的同学来说价值并不比语法本身低。第一能用开窗函数解决的需求别用自关联。早期项目里我遇到过用三个子查询反复自关联实现每行带分组总量的写法跑起来慢不说代码维护成本也高。开窗函数在处理这类明细汇总并存的需求时是标准答案。第二开窗函数的结果列一定要有明确注释。尤其是lag、lead这类偏移函数列名最好直接带昨日上期等语义方便后面接手的人一眼看懂口径不然很容易把lag取到上一行的含义理解错。第三在写完带开窗函数的SQL后不要只看结果行数对不对一定要抽查几行做手工验证。开窗函数表面简单实际上对窗口范围、排序顺序、并列逻辑非常敏感错一处分区或者错一处排序结果就是错的。我在生产环境里见过太多看着没问题一跑数就对不上账的案例问题多数出在窗口边界或者漏掉了分区字段。第四离线数仓和即席查询对开窗函数的使用习惯略有区别。离线开发的数据量大窗口函数的分布字段选型要考虑到数据倾斜风险比如某个分区下数据量特别大会导致这个分区运行时间明显长于其他分区遇到这种情况可以尝试把分区维度细化或者先做一层聚合。第五也是我最想说的一点开窗函数不要只背语法要理解行与行之间的关系。开窗函数解决的本质问题是行与行之间的计算比如当前行和上一行的差值、当前行在分组内的排名、当前行和分区内最大值之间的差距。当你把思维从表、行、列升级到行与行之间的关系很多看似复杂的业务需求其实都能用一两个开窗函数组合出来。这是SQL能力的一个重要分水岭跨过去之后你再回看那些曾经写了很长的SQL会发现都能大幅精简。写到这里开窗函数在离线数仓里的实际应用已经覆盖得比较完整了。我个人在实际项目里的体会是窗口函数用得好不好不完全取决于掌握多少函数而取决于能不能把业务需求翻译成分区排序窗口边界这三个要素。每个复杂的分析需求先问自己分区按什么分组内按什么排窗口范围到哪里想清楚这三个问题SQL自然就出来了。