Practical window function patterns in PostgreSQL

Introduction

Window functions help you analyze data by looking at rows related to the current row without grouping them. They let you calculate running totals, ranks, and moving averages easily.

When you want to rank items like top salespeople without losing detail about each sale.

When you need a running total of sales over time to see trends.

When you want to compare each row to others in the same group, like finding the difference from the previous month.

When you want to assign row numbers to results for pagination or ordering.

When you want to calculate averages or sums over a sliding window of rows.

Syntax

PostgreSQL

SELECT column1, column2, 
       window_function() OVER (PARTITION BY columnX ORDER BY columnY ROWS BETWEEN ... ) AS alias
FROM table_name;

OVER() defines the window for the function.

PARTITION BY divides rows into groups to apply the function separately.

Examples

This ranks employees by salary within each department.

PostgreSQL

SELECT employee_id, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

This calculates a running total of order amounts ordered by date.

PostgreSQL

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

This gets the previous sale amount for each product by date.

PostgreSQL

SELECT product_id, sale_date, amount,
       LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS previous_sale
FROM sales;

Sample Program

This example creates a sales table, inserts sample data, and calculates a running total of sales amount for each product ordered by sale date.

PostgreSQL

CREATE TABLE sales (
  sale_id SERIAL PRIMARY KEY,
  product_id INT,
  sale_date DATE,
  amount NUMERIC
);

INSERT INTO sales (product_id, sale_date, amount) VALUES
(1, '2024-01-01', 100),
(1, '2024-01-05', 150),
(1, '2024-01-10', 200),
(2, '2024-01-03', 300),
(2, '2024-01-07', 100);

SELECT product_id, sale_date, amount,
       SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales
ORDER BY product_id, sale_date;

Important Notes

Window functions do not reduce the number of rows returned, unlike GROUP BY.

Ordering inside OVER() is important for functions like ROW_NUMBER(), RANK(), and running totals.

Use PARTITION BY to reset calculations for each group, like per department or product.

Summary

Window functions let you perform calculations across related rows without grouping.

Common patterns include ranking, running totals, and comparing current row to previous rows.

Use PARTITION BY and ORDER BY inside OVER() to control how the window function works.