做销售数据看板的时候我接到一个挺纠结的需求同一张销售明细表既要按人算“从入职第一天到昨天的累计业绩”又要算“最近30天的日均表现”。第一反应是两条 SQL 分开跑后来发现数据量一大扫两次表实在肉疼就想办法在一条 SQL 里用窗口函数搞定。结果写着写着就撞上了 Full Range 和 Limit Range 这两个概念——一个窗口从分区第一行一直铺到当前行另一个只老老实实圈住最近 N 行。这两者对计算结果的影响天差地别尤其是当排序字段有重复值、数据有断档时稍不注意结果就是错的。这篇就把我在项目里从踩坑到梳理清楚的全过程记录下来给同样被窗口函数的“范围”搞晕的朋友做个参考。1. 一个报表需求引发的“范围”思考累计值与近N日均值1.1 业务需求同一张表里同时要“全部”和“最近N”当时的需求来自运营侧他们要做一个销售效能看板核心指标有两个一是每个销售从开始到每天的累计业绩用来观察成长曲线二是近30天的日均成交金额用来横向对比当前状态。这两个指标本质上是两种完全不同的“范围”累计业绩关心的是“从头到现在”的全量区间而近30日均值只关心当前时间点往前推30天的局部区间。明明是在同一张销售明细表上做聚合但一个要全量、一个要局部。如果按最朴素的方式写累计业绩可以用GROUP BY emp_id, order_date之后自连接搞定近30天均值就得用关联子查询逐行算。两条 SQL 各扫一遍表代码写出来也绕。更要命的是这张表的核心是每日汇总后的销售流水一个月下来几十万行两条 SQL 在生产库上跑响应时间直接拉满。这时候窗口函数就成了最自然的解法一个窗口直接开到“分区起点到当前行”另一个窗口只开“当前行往前29行”。前者就是 Full Range 的全量累积窗口后者就是 Limit Range 的限制窗口。窗口函数的妙处在于它不需要显式GROUP BY每一行都能带着聚合结果输出正好符合看板需要的“明细行 指标列”格式。1.2 第一版SQL两条SQL的笨办法先说笨办法方便对比后面的窗口函数写法。累计业绩最经典的是自连接SELECT a.emp_id, a.order_date, SUM(b.amount) AS cumulative_amount FROM daily_sales a JOIN daily_sales b ON a.emp_id b.emp_id AND b.order_date a.order_date GROUP BY a.emp_id, a.order_date;这个写法逻辑没问题但它是典型的 O(n²) 复杂度每个销售每天的累计都要回头扫描该销售的所有历史行明细数据一旦过万执行计划立刻变得难看。近30天均值就更麻烦了SELECT a.emp_id, a.order_date, ( SELECT AVG(b.amount) FROM daily_sales b WHERE b.emp_id a.emp_id AND b.order_date a.order_date AND b.order_date a.order_date - INTERVAL 30 DAY ) AS avg_30d_amount FROM daily_sales a;关联子查询逐行执行等于每条明细都触发一次子查询性能完全不可控。我在本地测试环境模拟了50万行数据后这个写法的查询耗时直接到了十几秒放生产上根本没法交差。1.3 窗口函数解决的思路窗口函数改写之后思路一下就清晰了。累计业绩对应的窗口是SUM(amount) OVER ( PARTITION BY emp_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )近30天均值对应的窗口是AVG(amount) OVER ( PARTITION BY emp_id ORDER BY order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW )同一个PARTITION BY同一个ORDER BY区别只在最后那一段ROWS BETWEEN的边界定义。一个从UNBOUNDED PRECEDING全范围起点开始一个从29 PRECEDING限制范围起点开始。这也是“Full Range”和“Limit Range”这两个词在我这儿落地的具体场景前者让窗口延伸到分区顶部后者把窗口限制在离当前行一定距离的位置。2. 窗口范围的核心区别ROWS按行、RANGE按值2.1 窗口的三要素分组、排序、边界要理解 Full Range 和 Limit Range先得把窗口函数的结构拆清楚。一个窗口定义通常由三段组成PARTITION BY决定把数据分成几个独立的分区ORDER BY决定分区内每行的排序位置窗口边界frame决定当前行在计算时能看到哪些行。边界这块SQL 标准里提供了ROWS和RANGE两种单位。很多人写了很久窗口函数却没用对过这两个词因为它们在最常见的无边界写法只写ORDER BY下结果完全一样只有在像我们这种“全范围 vs 限制范围”的对比需求下差异才会暴露出来。2.2 ROWS模式物理行偏移按行数说话ROWS模式好理解它就是“数行数”。比如ROWS BETWEEN 3 PRECEDING AND CURRENT ROW意思是以当前行为基准往前数3行到当前行结束一共4行参与计算。这个“往前数3行”是物理上的位置偏移完全忽略这4行里的 ORDER BY 值是什么。举一个简单的例子假设日期列里有两天是重复的order_dateamount2024-01-011002024-01-012002024-01-02300如果用ROWS BETWEEN 1 PRECEDING AND CURRENT ROW求SUM(amount)第2行第二个 2024-01-01看第1、2行结果是 300。第3行2024-01-02看第2、3行结果是 500。ROWS不关心 2024-01-01 出现了两次它只按物理位置机械地往前进。这个特性让ROWS模式的行为非常可预期也是我生产环境里默认首选它的原因。2.3 RANGE模式逻辑值区间排序值说话RANGE模式则完全不是一回事。它不看行数看排序字段的值。默认情况下RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW中的CURRENT ROW指的不是“当前这一行”而是“所有和当前行 ORDER BY 值相等的行”。还是上面那个例子用RANGE BETWEEN 1 PRECEDING AND CURRENT ROW求SUM(amount)。先在脑子里确定这个窗口的CURRENT ROW会把所有 order_date 相同的行都拉进来同时往前取“值相差1”的所有日期行。由于 2024-01-01 和 2024-01-02 只差1天窗口在第3行时会囊括第1、2、3行结果是 600而不是ROWS模式下的 500。这就是RANGE的本质它打开的是一个基于排序值的逻辑区间而不是物理行的集合。排序字段如果有大量重复值RANGE窗口会“膨胀”把同值的行一次性全部包进来。这个行为在某些计算里是好事比如按日期做自然滑动均值但在你不小心把日期作为粒度时就会变成坑。2.4 全范围与限制范围在语义上的本质把两个模式结合起来看Full Range 和 Limit Range 的语义差异就很清晰了。Full Range 的典型写法是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW或者RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。它的起点固定是分区第一行终点是当前行或当前行同值行因此天然适合做累计、全局占比、运行总量这些“从开始到现在”的业务指标。它的特点是结果会随行号单调变化后一行的结果一定包含前一行的结果。Limit Range 则把起点限制在离当前行一定距离的位置典型写法是ROWS BETWEEN N PRECEDING AND CURRENT ROW。它只关心最近 N 行内的局部信息适合做移动平均、滑动求和、短期波动分析。它的特点是窗口大小固定在数据完整的情况下不会因为分区总行数的变化而膨胀。还有一点容易忽略两者的终点也可以定义成FOLLOWING。比如ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING就是取当前行前后各2行像一扇在数据上滑动的“舱门”。但实际项目里用PRECEDING CURRENT ROW的组合最多因为业务上“当前及历史”是最常见的分析视角。3. 不同数据库下全范围与限制范围的语法与行为对照3.1 主流数据库的支持情况窗口函数经过这么多年的普及主流数据库基本都支持了但“支持”和“支持得好”是两回事。我在 MySQL、PostgreSQL、SQL Server、ClickHouse 几个引擎里都跑过这组需求差异性不小。数据库ROWS支持RANGE支持值区间日期INTERVAL窗口备注MySQL 8.0完整支持但边界表达式有限支持部分INTERVAL写法语法较严格生产环境常用性能中规中矩PostgreSQL完整完整支持RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW日期滑动窗口最方便SQL Server完整支持但性能表现一般不支持INTERVAL直接写在RANGE里复杂窗口建议用ROWSClickHouse完整支持有限部分版本对RANGEframe处理较弱不推荐用RANGE做时间窗口OLAP场景下用ROWS更稳这个表是经验总结不是官方支持矩阵的完整描述具体版本行为建议以实测为准。但方向很清楚如果业务明确要按时间值做滑动窗口PostgreSQL 最顺手如果是在 MySQL 或者 ClickHouse 里优先用 ROWS 加固定行数避免在 RANGE 上钻牛角尖。3.2 MySQL 8.0的实测行为MySQL 8.0 开始完整支持窗口函数我生产环境里跑得最多的就是它。实测下来ROWS BETWEEN ... PRECEDING AND CURRENT ROW的写法非常稳定只要分区内数据没有物理排序上的意外结果和手工核算完全一致。MySQL 对 RANGE 的支持也没有缺席但用起来要小心。比如RANGE BETWEEN 1 PRECEDING AND CURRENT ROW这种数值边界是可以用的可一旦排序字段是日期时间写RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW这种语法不同小版本的处理细节就有差异。我在 8.0.28 上试过一次能出结果但优化器对这类窗口的缓冲区处理明显不如 ROWS 模式成熟数据量大了之后内存占用涨得很快。所以在 MySQL 上我的默认建议是能用 ROWS 说清楚的需求不要硬用 RANGE。3.3 PostgreSQL的RANGE时间窗口PostgreSQL 是窗口函数支持最完整的数据库之一尤其是它的 RANGE 时间窗口做“近7天累计”“近30天均值”这类业务写起来非常自然SELECT emp_id, order_date, SUM(amount) OVER ( PARTITION BY emp_id ORDER BY order_date RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW ) AS last_30d_sum FROM daily_sales;这段 SQL 的含义不需要你去数行数它直接按日期值划范围当前行日期往前推30天到当前行日期为止的所有行都参与计算。哪怕数据中间有断档哪怕某一天有多条记录它都能正确处理“值区间”上的聚合这是 ROWS 做不到的。PostgreSQL 能这么灵活是因为它的 RANGE 边界表达式允许跟随排序值的类型。排序字段是date边界就能写INTERVAL排序字段是数字边界就能写数字。这个设计让 RANGE 真正变成了“值区间窗口”而不是简单地把 ROWS 换个名字。3.4 轻量库和OLAP引擎的差异SQLite 从 3.28 版本开始也支持窗口函数但它的 RANGE 支持比较简陋对RANGE BETWEEN的边界类型限制很多我一般只把它当验证工具用生产环境不指望它。OLAP 引擎这边Doris、StarRocks、ClickHouse 都支持窗口函数但 RANGE frame 在不同版本上的完善度参差不齐。我在 ClickHouse 上跑近30天均值时直接用ROWS BETWEEN 29 PRECEDING AND CURRENT ROW代替 RANGE虽然对缺勤数据会有偏差但性能稳定、结果可预期。选引擎的底层逻辑是窗口函数越复杂越考验优化器对窗口状态的管理能力。OLAP 引擎天生为大数据量设计但对 RANGE 这种需要动态判断值区间的 frame它的优化能力反而不如传统数据库扎实。如果需要高吞吐的滑动窗口计算ROWS 配合物化好的明细表仍然是性价比最高的方案。4. 完整实操从建表到三类业务的SQL写法与结果验证4.1 建表与造数为了能复现我把当时的测试场景简化了一下建一张每日销售汇总表表结构如下CREATE TABLE daily_sales ( emp_id VARCHAR(20) COMMENT 销售工号, order_date DATE COMMENT 订单日期, amount DECIMAL(10, 2) COMMENT 当日成交金额 );测试数据造了8个销售、60天的记录中间刻意留了一些缺勤日期也安排了少量同一天多笔记录的情况。目的是让 Full Range 和 Limit Range 的差异在真实数据里暴露出来而不是在“完美数据”上自我安慰。造数我习惯用存储过程或者临时表批量生成这里不贴完整脚本核心是用递归 CTE 生成60天以内的所有日期再对每个销售按概率随机写入金额。测试数据量控制在 480 行左右8人×60天足够人工核对也足够暴露问题。4.2 场景一累计业绩Full Range第一个指标是累计业绩。目标是每个销售每天显示“从第一天到今天”的累计成交金额。SELECT emp_id, order_date, amount AS day_amount, SUM(amount) OVER ( PARTITION BY emp_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount FROM daily_sales ORDER BY emp_id, order_date;这个SUM(...) OVER (...)没有写窗口边界省略时的默认行为我们要清楚如果只写ORDER BYMySQL 的默认 frame 是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW这是“全范围”没错但因为 RANGE 的同值问题可能会有隐患。所以我习惯显式写ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW把行为钉死。执行结果里第一行累计值等于当天的amount后面每一行的累计值等于上一行累计值加当天amount。这个指标就是 Full Range 最标准的应用从分区起点一路累积到当前行。4.3 场景二近30天移动平均Limit Range第二个指标是近30天移动平均。这里有个细节ROWS BETWEEN 29 PRECEDING AND CURRENT ROW表示“包含当前行在内的最近30行”如果按自然日每天一行那正好是30天但如果中间有缺勤实际覆盖的日历天数会少于30天。SELECT emp_id, order_date, amount AS day_amount, AVG(amount) OVER ( PARTITION BY emp_id ORDER BY order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS moving_avg_30d FROM daily_sales ORDER BY emp_id, order_date;这里必须强调一个容易踩坑的点如果业务要求“自然日近30天”那应该用 RANGE INTERVAL 而不是 ROWS 固定行数。如果业务能接受“最近30笔”这种业务定义ROWS 才是最稳定的选择。我当时的需求其实是“自然日近30天”所以我最终在 PostgreSQL 测试库里用了RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW在 MySQL 生产库里则先把缺勤日期用cross join日期维表补全再用 ROWS 计算保证两种口径一致。4.4 场景三分组占比与TopN全范围与限制范围的综合用法第三个需求更有意思既要算每个销售当前业绩在全公司的占比又要筛出每个销售近30天表现最好的日子。前者是明显的 Full Range 场景需要使用不带 ORDER BY 的整个分区作为窗口SELECT emp_id, order_date, amount, amount / SUM(amount) OVER (PARTITION BY emp_id) AS emp_total_ratio FROM daily_sales;这里没有ORDER BY窗口默认是整个分区我把它理解成“全范围无排序版”每个销售的累计总额作为分母每行金额与之相除得到贡献占比。这类需求在窗口函数里很常见也是 Full Range 的另一种体现——窗口不随行移动而是覆盖全分区。再看近30天表现最好的一天这个场景要用ROW_NUMBER()在限制范围内排序SELECT emp_id, order_date, amount FROM ( SELECT emp_id, order_date, amount, ROW_NUMBER() OVER ( PARTITION BY emp_id ORDER BY amount DESC ) AS rn FROM daily_sales ) t WHERE rn 1;ROWNUMBER 窗口内没有显式指定 frame这种排序类函数天然只能在当前行生效不需要考虑窗口边界。但它的意义在于提醒我们窗口函数家族里聚合类函数SUM/AVG/COUNT需要关注 Full Range 和 Limit Range而排名类函数ROW_NUMBER/RANK/DENSE_RANK的窗口范围和聚合类不是一回事使用时要区分场景。4.5 结果验证的思路写完 SQL 不是终点验证才见真功夫。我的验证方法是取一个销售、取一段日期在 Excel 里手工拉出累计和移动平均和 SQL 结果抽样比对。重点看三类位置分区第一行边界情况、数据断档后的第一行、同日多记录的那几行。这三类位置最容易暴露 ROWS 和 RANGE 的差异。拿emp_id E001前5天的数据举例人工核对第4行的累计值 第1~4天金额之和第4行的移动均值 第1~4天金额的平均如果窗口是ROWS BETWEEN 3 PRECEDING AND CURRENT ROW且第4天之前不足3天则只取前4行。窗口函数的行为一旦理解透这一步抽样核对很快但无论如何不能跳。5. 性能与陷阱全范围窗口常见的四类坑5.1 坑一RANGE会把并列值一网打尽这是窗口函数最经典的坑。ORDER BY order_date如果同一天有多条记录默认 frame 是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW此时CURRENT ROW会把所有同一天的行都包含进来。结果就是同一天的第二行数据累计值会突然跳过中间过程直接跳到当天全部行都算完的状态。我当时就遇到过这个 bug看板上同一天的两笔订单第一笔的累计额显示正常第二笔的累计额直接把当天两笔数据都算进去了导致后面的所有累计都超过手工核对值。解决办法是显式写ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW用物理行边界代替默认值边界。这是一个线上事故级别的坑务必重视。5.2 坑二COUNT在窗口函数中的NULL行为写窗口函数时很多人默认COUNT(*)和COUNT(column)是一回事但在窗口函数里它们的行为差异会被放大。COUNT(*)统计窗口内所有行COUNT(column)只统计 column 非 NULL 的行。如果做移动平均时用了COUNT(amount)而 amount 存在 NULL分母就会悄悄变小平均值被抬高。更隐蔽的是如果窗口是ROWS BETWEEN 29 PRECEDING AND CURRENT ROW但分区前面只有10行COUNT(*)返回的是10而不是30。这会导致“移动平均数”在最开始几天呈现一种假象从第1天到第29天平均值实际上还是样本不足的均值严格来说应该显示为 NULL 或者标注样本不足。业务同学不知道这个细节就会把前期的均值当成真实水平来读。稳妥做法是把前期不足窗口大小的行单独标记出来或者业务口径接受样本不足时的方案。5.3 坑三窗口计算顺序和WHERE/HAVING的先后关系很多人以为WHERE可以过滤掉异常数据后再参与窗口计算但实际上WHERE是在窗口函数计算之前完成的HAVING是在窗口函数计算之后完成的。举个具体例子如果业务要求“先剔除金额小于0的退款单再算累计”你不能直接在 SQL 里写WHERE amount 0然后SUM(amount) OVER (...)因为WHERE已经把退款单物理删除了窗口函数根本看不到被过滤的行累计值自然也不会把它们排除在外。正确姿势是把窗口函数包进子查询先算原始窗口再在外面用WHERE过滤或者反过来先过滤再算口径会完全不同。这个顺序问题非常基础但我在评审代码时不止一次看到有人在这里翻车。写窗口函数前先问自己一句过滤条件到底应该发生在窗口计算前还是窗口计算后如果答案是“计算后”就要用子查询包一层。5.4 坑四大表上全范围窗口的性能Full Range 窗口的语义决定了它必须维护一个“看到分区顶部到当前行”的累计状态这在执行计划里往往意味着一份较大的内存排序。如果每个分区的行数很大比如一个销售有几万条小时级流水即使底层索引再好窗口函数的排序和状态维护也会成为瓶颈。我做过一次压测500万行明细按销售分区算累计业绩最慢的时候跑了近40秒。后来改为先按emp_id, order_date做物化汇总把数据压到50万行再跑同一个窗口函数耗时降到3秒以内。经验是窗口函数之前先把数据粒度压到业务真正需要的粒度再让窗口函数在尽量小的数据集上执行。Full Range 窗口对分区内的行数敏感对分区总数不敏感所以核心优化思路是减少每个分区的行数而不是减少分区数量。6. 没有窗口函数怎么办自连接、子查询与Pandas替换6.1 自连接实现累计值虽然窗口函数已经普及但总有一些老系统、老集群因为版本问题用不上或者优化器对窗口支持太差。这时候还是要会几招替代方案。累计值用自连接是最容易理解的SELECT a.emp_id, a.order_date, SUM(b.amount) AS cumulative_amount FROM daily_sales a JOIN daily_sales b ON a.emp_id b.emp_id AND b.order_date a.order_date GROUP BY a.emp_id, a.order_date;这条 SQL 和窗口函数结果一致但性能差距巨大。窗口函数是内建的状态累积机制自连接是笛卡尔式的行匹配数据量一大就爆炸。我的使用建议是只在数据量不大几千行或者临时排查问题时用生产环境不要把这个当长期方案。6.2 标量子查询实现移动平均移动平均用关联子查询实现逻辑上和 RANGE 时间窗口最接近SELECT a.emp_id, a.order_date, ( SELECT AVG(b.amount) FROM daily_sales b WHERE b.emp_id a.emp_id AND b.order_date a.order_date AND b.order_date a.order_date - INTERVAL 30 DAY ) AS moving_avg_30d FROM daily_sales a;这个写法能正确表达“自然日近30天”比 ROWS 限行数更贴近业务。缺点是逐行触发子查询性能同样堪忧。我在小数据量报表几千行里用过勉强能接受一旦明细超过几万行就必须回到窗口函数。6.3 Pandas的transform与rolling如果数据仓库链路不允许跑复杂 SQL或者数据已经在数仓外面用 Pandas 也能轻松实现同样的逻辑。累计业绩对应groupby之后cumsumimport pandas as pd df pd.read_csv(daily_sales.csv) df[cumulative_amount] df.groupby(emp_id)[amount].cumsum()这里cumsum()是分组内从第一行到当前行的累加和 Full Range 一个语义。移动均值对应rollingdf[moving_avg_30d] ( df.groupby(emp_id)[amount] .rolling(window30, min_periods1) .mean() .reset_index(level0, dropTrue) )Pandas 的rolling(window30)默认是左开右闭窗口按行数限制范围等价于ROWS BETWEEN 29 PRECEDING AND CURRENT ROW。如果要做自然日窗口可以把时间列设为索引再用rolling(30D)按时间频率滑动。这个特性在处理不规则时间序列时非常有用我在数据分析和临时报告里经常用它做快速验证。6.4 方案选择的一般准则结合这几个场景我给团队定的选型准则是第一优先是数据库原生窗口函数能用 ROWS 表达清楚就绝不用 RANGE如果业务语义强制要求值区间比如自然日近30天先看数据库对 RANGE INTERVAL 的支持程度PostgreSQL 优先MySQL 次之如果数据库版本太老或者优化器太弱再退回到物化表加自连接只有数据已经脱离数仓做离线分析时才用 Pandas 的 rolling 方案。这部分说到底窗口函数不是炫技是为了让 SQL 更短、执行计划更优、口径更统一。替代方案都是“业务能用但性能欠缺”的兜底不该成为默认选择。写到这里正好把 Full Range 与 Limit Range 的核心框架讲完了。我个人的体会是写窗口函数之前先问自己一句我要的窗口是按物理行数圈定还是按排序字段的具体值圈定想明白这句话至少能避开一半的累计计算陷阱。实际项目里如果排序字段可能有重复值无脑选 ROWS 准没错如果业务明确是按日期值做自然滑动窗口再考虑 RANGE但一定先在小数据集上核对一遍同值行的处理逻辑。最后分享一个调试技巧遇到窗口结果不对时别急着看整个表先拉出一个小分区的十几行手工算一遍累计再和 SQL 结果比对再去看执行计划——大部分问题出在你对窗口边界的理解上而不是 SQL 语法上。