Why window functions are powerful in PostgreSQL

Introduction

Window functions let you do calculations across rows related to the current row without losing the row details. This helps you get more insights from your data easily.

When you want to calculate running totals like a bank balance over time.

When you need to rank items, such as top-selling products in each category.

When you want to compare each row to others, like finding the difference from the previous sale.

When you want to add summary info next to each row without grouping and losing details.

Syntax

PostgreSQL

SELECT column1, window_function() OVER (PARTITION BY column2 ORDER BY column3) FROM table_name;

The OVER() clause defines the window or set of rows the function works on.

PARTITION BY splits data into groups; ORDER BY defines the order inside each group.

Examples

This ranks employees by salary from highest to lowest.

PostgreSQL

SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;

This calculates a running total of order amounts for each customer over time.

PostgreSQL

SELECT order_id, order_date, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total FROM orders;

This shows the average price per category next to each product.

PostgreSQL

SELECT product_id, category, AVG(price) OVER (PARTITION BY category) AS avg_category_price FROM products;

Sample Program

This example creates a sales table, adds some sales data, and then calculates a running total of sales amount for each salesperson ordered by date.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  salesperson TEXT,
  sale_date DATE,
  amount NUMERIC
);

INSERT INTO sales (salesperson, sale_date, amount) VALUES
('Alice', '2024-01-01', 100),
('Alice', '2024-01-02', 150),
('Bob', '2024-01-01', 200),
('Bob', '2024-01-03', 50);

SELECT salesperson, sale_date, amount,
  SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM sales
ORDER BY salesperson, sale_date;

Important Notes

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

You can use many different window functions like ROW_NUMBER(), RANK(), LAG(), LEAD(), and aggregates.

Summary

Window functions let you calculate values across related rows without losing row details.

They are great for running totals, rankings, and comparisons within groups.

Using PARTITION BY and ORDER BY inside OVER() controls how the calculations are done.