A window function computes a value for each current row while looking across a defined set of rows. Unlike GROUP BY aggregation, it preserves every row. Define the window with OVER(), optionally adding PARTITION BY, ORDER BY, and a frame. section 4.5
Use window functions to:
- Rank rows globally or independently within each partition. section 4.5 section 4.5.1
- Compute running totals or aggregates such as average, sum, max, and min without reducing row count. section 1.7.3 section 4.5.1
- Compare adjacent records with lag() and lead(). section 4.5.4
- Filter computed window results with QUALIFY rather than WHERE. section 4.6
Top readings per system
Rank within each system, then keep the top two ranks: WITH ranked_readings AS ( SELECT *, dense_rank() OVER (PARTITION BY system_id ORDER BY power DESC) AS rnk FROM readings ) SELECT * FROM ranked_readings WHERE rnk <= 2 ORDER BY system_id, rnk; section 4.5.1
Compare successive prices
Order prices by date and access the previous value: SELECT valid_from, value, lag(value) OVER validity AS previous_value, value - lag(value, 1, value) OVER validity AS change FROM prices WINDOW validity AS (ORDER BY valid_from); Use a CTE before applying an aggregate to the window result, because window calls cannot be nested inside aggregate functions. section 4.5.4
