A window partition divides rows into independent groups before a window function runs. Without PARTITION BY, the entire relation is one partition; rows retain their separate identities, while the function computes a new value for each row. section 4.5 section 4.5.1
Partitioning is useful when a result must be calculated within each category rather than across the dataset. Ranking, averages, sums, minimums, and maximums can all operate inside each partition, optionally with an ORDER BY clause. section 4.5.1
Top readings per system
To return the two highest readings for every system, partition by system_id and rank power values within each group: 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 ASC; The same pattern can add each system’s overall average without collapsing daily rows: avg(kWh) OVER (PARTITION BY system_id) AS average_per_system.
Listing 4.16 Applying a partition to a window
WITH ranked_readings AS (
SELECT *,
dense_rank()
OVER ( #1
PARTITION BY system_id #2
ORDER BY power DESC
) AS rnk
FROM readings
)
SELECT * FROM ranked_readings WHERE rnk <= 2
ORDER BY system_id, rnk ASC;
Inspect the complete per-system top-n query, including ranking, filtering, and final ordering. section 4.5.1
