A function evaluated OVER a window defined by PARTITION BY, ORDER BY and optionally a frame; unlike GROUP BY aggregation it doesn't collapse rows, but adds to each one a value worked out from its related rows. Examples are RANK, ROW_NUMBER, LAG and running totals.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains Window function in context, with comparison tables and the common traps.
Terms in this definition
- OVER
Gives a T-SQL window function its window: PARTITION BY, ORDER BY and, if wanted, a ROWS or RANGE frame. Rankings and running totals can then be worked out while every row is kept.
- WINDOW
Returns rows from a sorted, optionally partitioned table, either at fixed positions (ABS) or relative to the current row (REL). Running totals plus moving averages are common uses.
- Azure AI Search partition
Storage and I/O unit of a search service, each holding part of the indexes. Adding partitions increases storage and indexing throughput; the number of search units equals replicas multiplied by partitions.
- ORDER BY
Sorts what a SQL query returns on one or more columns. Leave it out and rows arrive in no guaranteed sequence.
- GROUP BY
Collapses rows sharing the same values of chosen expressions into one row per bucket, with aggregate functions evaluated for each bucket. Writing
GROUP BY ALLpicks every expression in the select list that isn't an aggregate. - COLLAPSE
Moves a calculation up one level of the axis hierarchy in a visual, which is how you'd get a value as a share of its parent. This DAX function works in visual calculations and nowhere else.
- RELATED
Fetches a column value from the lookup table, that is the one side of a many-to-one relationship, for whichever row is being evaluated. It therefore needs to run inside an iterator or a calculated column, where row context exists.
- RANK
A window function, found in T-SQL (used with OVER) and in DAX, that gives each row's position within its partition. In DAX it accepts ORDERBY and PARTITIONBY, skips ranks after ties unless DENSE is chosen, and returns blank for total rows.
Related terms
- NTILE
A ranking window function that divides each partition's sorted rows into a stated number of numbered buckets. If the rows can't be shared out equally, the earlier buckets get the extra rows.
- OFFSET
A DAX window function that moves a given number of rows back (negative values) or forward (positive) from the current row in a sorted table, optionally split into partitions. It is commonly used to fetch the prior or next period's value.
- QUALIFY
A SQL clause, available from Databricks Runtime 10.4 LTS, that filters rows by a window function's result with no subquery needed, such as retaining only
ROW_NUMBER() = 1for each key. - ROWNUMBER
A DAX window function that gives the current row a unique position within a sorted, optionally partitioned table. Where the sort and partition columns leave a tie, it brings in more columns to settle it, and errors if there are none; RANK, by contrast, lets tied rows share a position.