Window Functions in PostgreSQL

1. Introduction

Window functions in PostgreSQL allow you to perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions that return a single value for a group of rows, window functions provide a value for each row, enabling more complex queries.

2. Key Concepts

• Partitioning: Divides the result set into subsets to which the window function is applied.
• Ordering: Specifies the order in which rows are processed within each partition.
• Frame Specification: Defines a subset of the current partition to calculate the function over.

3. Syntax

The basic syntax for window functions is:

function_name() OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_specification]
)

4. Examples

4.1 Row Numbering

To assign a unique sequential integer to rows within a partition:

SELECT 
    employee_id, 
    department_id, 
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date) AS row_num
FROM employees;

4.2 Calculating Running Total

To calculate a running total of salaries:

SELECT 
    employee_id, 
    salary, 
    SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

5. Best Practices

• Use window functions for analytical queries where you need row-level calculations.
• Always specify an ORDER BY clause to ensure deterministic results.
• Be cautious with performance: complex window functions can impact query speed.

6. FAQ