PostgreSQL Window Functions

Window functions in PostgreSQL allow performing calculations across a set of table rows related to the current row, without collapsing the result set. They are powerful for analytics, ranking, running totals, moving averages, and complex reporting.

1. Basic Syntax

The basic syntax of a window function includes an aggregate or ranking function followed by an OVER() clause:

function_name([arguments]) OVER (
                [PARTITION BY partition_expression]
                [ORDER BY order_expression]
                [ROWS frame_specification]
            )

2. Common Window Functions

• ROW_NUMBER(): Assigns a unique sequential number to rows within a partition.
• RANK(): Assigns a rank with gaps for ties.
• DENSE_RANK(): Assigns a rank without gaps.
• SUM(), AVG(), MIN(), MAX(): Aggregate functions over a window.
• LEAD() / LAG(): Access data from the next or previous row.

3. ROW_NUMBER Example

SELECT 
                id, name, department_id, salary,
                ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num
            FROM employees;

4. RANK and DENSE_RANK Example

SELECT 
                id, name, department_id, salary,
                RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS rank,
                DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
            FROM employees;

5. SUM and AVG Example

SELECT 
                id, name, department_id, salary,
                SUM(salary) OVER(PARTITION BY department_id) AS total_salary,
                AVG(salary) OVER(PARTITION BY department_id) AS avg_salary
            FROM employees;

6. LEAD and LAG Example

SELECT 
                id, name, salary,
                LAG(salary, 1) OVER(ORDER BY salary) AS previous_salary,
                LEAD(salary, 1) OVER(ORDER BY salary) AS next_salary
            FROM employees;

7. Frame Specification

You can define a frame of rows relative to the current row using ROWS or RANGE in OVER().

-- Running total of salary within department
            SELECT 
                id, name, department_id, salary,
                SUM(salary) OVER(PARTITION BY department_id ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
            FROM employees;

8. Best Practices

• Use window functions for analytics without collapsing rows.
• Partition data properly to avoid incorrect aggregations.
• Combine ORDER BY and ROWS/RANGE frames for precise calculations.
• Avoid overusing window functions on large datasets without indexes, as it may affect performance.

Conclusion

PostgreSQL window functions provide powerful analytical capabilities for ranking, cumulative sums, moving averages, and reporting. They allow you to analyze data across rows while keeping the original row structure intact. In the next tutorial, we will explore Common Table Expressions (CTEs), which simplify complex queries and recursive operations.