Aggregating a stream over sliding or tumbling time windows

Windowing (analytic) functions let you compute ranks, running totals, and row-to-row comparisons across a set of related rows without collapsing them into groups, using the OVER() clause with PARTITION BY and ORDER BY. In Fabric, you create these using Spark SQL/PySpark in notebooks or T-SQL in the Warehouse/SQL endpoint, applying functions like ROW_NUMBER, RANK, LAG/LEAD, and aggregate-over-window patterns.

1 · Learn the must-know

• 1The OVER() clause defines the window: PARTITION BY groups rows (like GROUP BY but keeps all rows), and ORDER BY defines the sequence within each partition for ranking and offset functions.
• 2ROW_NUMBER always assigns unique sequential integers even for ties, RANK skips numbers after ties, and DENSE_RANK does not skip numbers after ties: choose based on whether gaps are desired.
• 3LAG and LEAD access prior/following row values within a partition and require an ORDER BY; an optional offset and default value can be specified for rows without a match.
• 4Aggregate functions (SUM, AVG, COUNT, MIN, MAX) become window functions when combined with OVER(), enabling running totals or moving averages without a GROUP BY.
• 5Frame specifications (ROWS BETWEEN / RANGE BETWEEN, e.g., UNBOUNDED PRECEDING AND CURRENT ROW) control exactly which rows within the partition are included in the calculation for each row.
• 6In PySpark, window specs are built with the Window class (Window.partitionBy(...).orderBy(...)) and passed to functions like F.row_number().over(windowSpec); in Fabric Warehouse T-SQL, the same OVER()/PARTITION BY/ORDER BY syntax as SQL Server/Synapse applies.

2 · Check your understanding