企业官网建设流程全解析

开窗函数在离线数仓里的地位，基本等同于炒菜用的盐——你可能不是天天盯着它看，但少了它，很多业务需求根本没法做。做数据开发这行当，SQL写得好不好，差别往往就体现在这些地方：别人用一堆子查询绕来绕去才跑出来的结果，你一个开窗函数两三行就能搞定，而且执行效率还更高。这篇内容我把开窗函数在离线数仓里的实际应用从头梳理一遍，包括它和group by的本质区别、三大类函数的用法对比、我在真实ETL开发中反复用到的场景，以及新手最容易踩的坑。无论是刚入行的数据开发，还是写了好几年SQL但一直没把窗口函数吃透的同学，这份总结应该都能帮到你。

1. 开窗函数到底是什么：先搞清楚它和group by的区别

1.1 开窗函数的核心执行逻辑

很多人在学开窗函数的时候，第一个困惑就是：这不就是分组统计吗？其实不是。group by是"多进一出"，若干行数据经过聚合函数计算后，每组只输出一行结果，其他明细行直接被压缩掉了。而开窗函数是"多进多出"，它同样按某个维度分区，但每一行原始数据都会保留，聚合或者排序的结果作为新的一列附加到每一行后面。

我用一个例子来说明。假设有一张销售明细表，记录了每个订单的金额：

如果你想看每个销售员的总业绩，用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 排序字段)

三者区别全在"并列名次"的处理上。用一个学生成绩的例子看最直观：

• row_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等于4，6减2等于4，7减3等于4，差值相等说明这些日期是连续的。中间断一天，这个差值就会变。这个思路是离线数仓处理"连续性"问题的基本套路，在新客激活、签到分析、异常登录监测里都能复用。

3.4 场景四：会话划分

会话划分是用户行为分析里非常常见的预处理步骤。比如埋点明细表里每个用户有一串行为事件，从打开App到退出算一次会话，但两次操作间隔超过30分钟就要重新算一次会话。这个需求用lag和sum开窗组合，可以一气呵成。

第一步，用lag计算每条行为和上一条行为的时间差。第二步，标记出时间差大于30分钟的行为，作为会话新起点。第三步，用sum开窗对"是否为新起点"做累计求和，生成会话ID：

select 用户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自然就出来了。