请教：T-SQL窗口函数中的“window descriptor”指什么？

Hey there! Let's break down this core term from Ben Itzik's book—once you connect it to how window functions work, it'll click right away.

At its simplest, a window descriptor is the full set of rules you define in the OVER() clause that tells SQL exactly which subset of rows (the "window") a window function should operate on. It's like a precise instruction manual for SQL to calculate your desired result, rather than running the function against the entire table or a basic grouped aggregate.

A window descriptor typically includes up to three key components:

• Partitioning (PARTITION BY): Splits your full dataset into independent, logical groups. The window function runs separately for each group. For example, PARTITION BY CustomerID means you'll calculate metrics (like total order value) per customer, not across all customers.
• Ordering (ORDER BY): Defines the sequence of rows within each partition. This is critical for functions that depend on row position, like ROW_NUMBER(), LAG(), or running totals. Without it, functions like ROW_NUMBER() would return arbitrary, unordered row numbers.
• Frame specification: A more granular rule that narrows down the rows within a partition to a specific range relative to the current row. For example, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW tells SQL to include every row from the start of the partition up to the current row (perfect for running sums). Sometimes this is implied by the ORDER BY clause, but you can explicitly define it to fine-tune the window.

Example to make it concrete #

Take this window function definition:

SUM(OrderTotal) OVER(
  PARTITION BY CustomerID
  ORDER BY OrderDate
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS Rolling3OrderTotal

Everything inside the OVER() parentheses is the window descriptor. It tells SQL:

1. Split orders into groups by each customer
2. Sort each customer's orders by date
3. Calculate the sum of order totals using only the current order plus the two most recent prior orders for that customer

Ben Itzik uses this term to encapsulate all these rules because together they "describe" exactly what the window of rows looks like for each calculation. It's the blueprint that makes window functions so powerful for granular, row-level analytics.

内容的提问来源于stack exchange，提问作者Thor