SQL Window Functions: Frames, Ranking, and Examples SQL 窗口函数完整指南:分区、排序、窗口帧与示例

What are SQL window functions? 什么是 SQL 窗口函数?

A window definition can contain PARTITION BY , window ORDER BY , and a frame. Partitioning decides where calculations restart. Ordering defines sequence and peers. The frame decides which rows around the current row are visible to frame-sensitive calculations. Treat these as separate design choices.

窗口定义可以包含 PARTITION BY 、窗口内 ORDER BY 和窗口帧。分区决定计算在哪里重新开始,排序定义顺序和并列记录,窗口帧决定对当前行而言哪些周边记录参与计算。应把三者视为独立设计决策。

window_function(expression) OVER (
  PARTITION BY group_key
  ORDER BY sequence_key, unique_tie_breaker
  ROWS BETWEEN frame_start AND frame_end
)

Choose the right window function family 选择正确的窗口函数类别

Window functions solve different questions. Ranking functions describe position; offset functions inspect another row; value functions read values at frame boundaries; aggregate functions calculate over a window rather than collapsing a group. Choosing by question prevents complex expressions from being used where a simpler group or join is clearer.

窗口函数解决的问题不同:排名函数描述位置,偏移函数读取另一行,取值函数读取窗口帧边界的值,聚合函数则在窗口上计算而不压缩分组。根据问题选择,能够避免用复杂表达式替代更清晰的分组或连接。

Separate partition boundaries from row ordering 区分分区边界与行顺序

PARTITION BY customer_id creates an independent customer calculation. Omitting it makes the whole filtered result one partition. Window ORDER BY defines logical order inside each partition but does not guarantee final presentation order. Add a query-level ORDER BY when the delivered rows must be sorted.

PARTITION BY customer_id 为每位客户创建独立计算;省略时,整个过滤结果成为一个分区。窗口内 ORDER BY 定义分区内部逻辑顺序,但不保证最终展示顺序;需要稳定展示时还要增加查询级 ORDER BY 。

SELECT
  order_id, customer_id, order_date, amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date, order_id
  ) AS order_sequence
FROM orders
WHERE status = 'completed'
ORDER BY customer_id, order_sequence;

The unique order_id resolves equal timestamps. Without a tie-breaker, functions that need a unique sequence may choose peer order unpredictably. Peer-aware functions such as RANK intentionally treat equal ordering values together, so the business meaning of ties should determine the function.

唯一的 order_id 解决相同时间戳并列。没有破局字段时,需要唯一顺序的函数可能以不确定方式处理并列记录。 RANK 等函数会有意把相等排序值视为同组,因此应根据并列的业务含义选择函数。

Define window frames instead of trusting defaults 显式定义窗口帧,不要盲信默认值

A frame is the subset of the partition visible to the current row for frame-sensitive functions. Common units are ROWS , RANGE , and, where supported, GROUPS . ROWS counts physical result rows. RANGE considers ordering values and peers. GROUPS counts peer groups. Dialect support and restrictions vary.

窗口帧是在当前行位置上对帧敏感函数可见的分区子集。常见单位包括 ROWS 、 RANGE ,以及部分平台支持的 GROUPS 。 ROWS 按物理结果行计算, RANGE 考虑排序值和并列, GROUPS 按并列组计算;方言支持与限制不同。

SUM(amount) OVER (
  PARTITION BY customer_id
  ORDER BY order_date, order_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_revenue

With duplicate ordering values, an implicit RANGE -like default can include all peers and make a running total jump by several rows. Explicit ROWS plus a unique ordering key creates row-by-row progression. For a moving seven-row average, use a bounded row frame; for seven calendar days, row count is not equivalent to time and a supported interval range or time-spine design may be required.

排序值重复时,类似 RANGE 的隐式默认帧可能一次包含全部并列记录,使累计值跳过多行。显式 ROWS 加唯一排序键可实现逐行推进。七条记录的移动平均可使用有界行帧,但七个自然日并不等于七行,可能需要平台支持的时间区间或时间骨架设计。

Apply window functions to real analytical patterns 把窗口函数用于真实分析模式

SELECT
  region_id, month_start, revenue,
  revenue / NULLIF(
    SUM(revenue) OVER (
      PARTITION BY month_start
    ), 0
  ) AS share_of_month,
  revenue - LAG(revenue) OVER (
    PARTITION BY region_id
    ORDER BY month_start
  ) AS change_from_prior_month
FROM region_monthly_revenue;

Filter window results at the correct query level 在正确查询层过滤窗口结果

Window functions are logically evaluated after the same-level WHERE , grouping, aggregation, and HAVING . Most dialects therefore cannot reference a window alias in that same level's WHERE . Compute the value in a subquery or CTE, then filter outside. Platforms supporting QUALIFY offer a direct window-result filter.

窗口函数在同层 WHERE 、分组、聚合和 HAVING 之后逻辑执行,因此多数方言不能在同层 WHERE 引用窗口别名。应在子查询或 CTE 中计算,再到外层过滤;支持 QUALIFY 的平台可以直接过滤窗口结果。

WITH ranked_products AS (
  SELECT
    category_id, product_id, revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category_id
      ORDER BY revenue DESC, product_id
    ) AS rn
  FROM product_revenue
)
SELECT *
FROM ranked_products
WHERE rn <= 2;

Filter placement changes the population. Filtering inactive products inside the CTE ranks only active products. Ranking all products and filtering activity outside asks whether the already-ranked winner happens to be active. Both are valid SQL; they answer different questions.

过滤位置会改变总体。在 CTE 内过滤非活跃产品表示只对活跃产品排名;先对全部产品排名,再在外层过滤活跃状态,则是在询问已经排名的获胜者是否活跃。两者语法都正确,但问题不同。

Reduce repeated work across multiple windows 减少多个窗口之间的重复工作

Several functions with the same partition and order can often share a named window or compatible sort. Functions using different ordering keys may require additional sort passes. A query ranking by revenue, sequencing by time, and calculating a moving average by another timestamp may therefore perform several expensive ordered operations even though each expression is short.

多个函数如果使用相同分区与排序,通常可以共享命名窗口或兼容排序;使用不同排序键的函数可能需要额外排序。一条查询如果按收入排名、按时间编号、再按另一时间戳计算移动平均,就可能执行多次昂贵有序操作,尽管每个表达式都很短。

SELECT
  account_id, event_time, amount,
  SUM(amount) OVER w AS running_amount,
  LAG(amount) OVER w AS previous_amount,
  ROW_NUMBER() OVER w AS event_number
FROM account_events
WINDOW w AS (
  PARTITION BY account_id
  ORDER BY event_time, event_id
);

Named windows improve consistency and reduce copy-paste drift, but do not guarantee one physical sort. Inspect the actual plan. Also narrow rows before large sorts when semantics allow; carrying wide text or JSON columns through window operations increases memory and spill risk.

命名窗口提高一致性并减少复制漂移,但不保证只进行一次物理排序。应检查实际计划;在语义允许时还应在大型排序前缩窄字段,携带宽文本或 JSON 列进行窗口处理会增加内存和溢写风险。

Review window-function performance with evidence 使用证据审查窗口函数性能

Performance depends on filtered row volume, partition distribution, sorting keys, existing order, memory, parallelism, indexes, and engine behavior. One huge partition can dominate memory even when average partitions are small. Nonselective joins before the window can multiply rows and make every later sort more expensive.

性能取决于过滤后行数、分区分布、排序键、已有顺序、内存、并行度、索引和引擎行为。即使平均分区很小,一个超大分区也可能主导内存;窗口前的非选择性连接会复制记录,让后续所有排序更昂贵。

The PostgreSQL window-function reference documents ranking, offsets, value functions, peer groups, and frame-sensitive behavior. Use official documentation for the deployed engine because frame features and null-handling options vary.

PostgreSQL 窗口函数官方参考记录了排名、偏移、取值函数、并列组和帧敏感行为。由于窗口帧功能和空值处理选项因平台不同,应以部署引擎的官方文档为准。

Validate window logic before production 上线前验证窗口逻辑

1. Define the input grain. Audit joins and filters before window evaluation.
2. State the partition. Explain why calculation restarts at that business boundary.
3. Make order complete. Add stable tie-breakers where row sequence matters.
4. Specify the frame. Test peers, first rows, last rows, sparse time periods, and empty frames.
5. Check filter placement. Decide whether eligibility applies before or after the window result.
6. Compare functions. Test ROW_NUMBER, RANK, and DENSE_RANK at tie boundaries when selecting top N.
7. Profile partitions. Measure largest groups, not only averages.
8. Inspect the plan. Review sorts, spills, repeated passes, estimates, and actual rows.

1. 定义输入粒度。 在窗口计算前审计连接和过滤。
2. 声明分区。 解释为什么计算在该业务边界重新开始。
3. 让排序完整。 顺序重要时增加稳定破局字段。
4. 指定窗口帧。 测试并列、首行、末行、稀疏期间和空帧。
5. 检查过滤位置。 确定资格条件在窗口结果之前还是之后应用。
6. 比较函数。 Top N 边界存在并列时测试 ROW_NUMBER、RANK 和 DENSE_RANK。
7. 分析分区。 测量最大组,而不只看平均值。
8. 检查计划。 查看排序、溢写、重复处理、估算与实际行数。

Review window functions as a complete analytical system in production 把窗口函数作为完整分析系统进行生产评审

Production review should follow the data from source grain to final consumer rather than examining an isolated OVER clause. Record which rows enter each partition, which columns make ordering deterministic, which frame boundaries apply, and whether later joins or filters can duplicate or remove ranked rows. A technically valid window can still answer the wrong question when upstream data contains repeated events, late-arriving corrections, or multiple current records.

生产评审应从源数据粒度一直跟踪到最终使用者,而不是只看孤立的 OVER 子句。需要记录哪些记录进入每个分区、哪些字段保证排序确定、采用什么窗口帧边界,以及后续连接或过滤是否会复制或删除已排名记录。即使窗口语法完全有效,上游存在重复事件、延迟修正或多个当前记录时,仍可能回答错误问题。

Build a small adversarial fixture before testing at scale. Include two rows tied on the visible sort key, a null ordering value, a one-row partition, a partition with duplicate timestamps, and a correction arriving after the original event. For running totals, add negative adjustments and duplicate business dates. For LAG or LEAD , verify the first and last rows explicitly. For ranking, decide whether ties should share a rank, create gaps, or be broken by a stable identifier.

大规模测试前,应构建一个小型对抗性样本:包含可见排序键相同的两条记录、空排序值、仅一条记录的分区、时间戳重复的分区,以及晚于原事件到达的修正记录。累计计算还应加入负向调整和重复业务日期;使用 LAG 或 LEAD 时明确检查首尾记录;排名时则需决定并列值是共享名次、产生间隔,还是由稳定标识符打破并列。

Finally, reconcile a sample partition manually and preserve that fixture as a regression test. Compare row counts before and after every join, check that the selected "latest" row is unique, and inspect actual memory, sort, spill, and parallelism evidence. Optimization is successful only when the result remains identical for ordinary rows, ties, nulls, sparse partitions, and late corrections.

最后,应手工核对一个样本分区,并把它保留为回归测试。比较每次连接前后的行数,确认所选"最新"记录唯一,并检查实际内存、排序、溢写和并行执行证据。只有普通记录、并列值、NULL、稀疏分区和延迟修正的结果都保持一致,优化才算成功。

Document engine-specific behavior too. Default frames, null ordering, support for named windows, and availability of QUALIFY differ across systems. Keep portable business logic separate from dialect shortcuts, and link each shortcut to a tested fallback. This makes migrations and mixed-engine reviews safer without forcing every query into the least capable syntax.

还应记录数据库特有行为。不同系统的默认窗口帧、NULL 排序、命名窗口支持以及 QUALIFY 可用性并不相同。应把可移植业务逻辑与方言快捷写法分开,并为每个快捷写法提供经过测试的替代方案,从而降低迁移和混合引擎评审风险。

SQL window functions frequently asked questions SQL 窗口函数常见问题

Continue with a related guide 继续阅读相关指南

Use these closely related guides to verify the next layer of the workflow without mixing separate troubleshooting or decision intents. 可继续阅读以下紧密相关的指南,验证工作流的下一层,同时避免混淆不同的排障或决策意图。

• PARTITION BY in SQL SQL PARTITION BY
• ROW_NUMBER in SQL SQL ROW_NUMBER
• RANK in SQL SQL RANK