SQL Aggregation Window Functions

SQL aggregation window functions are powerful tools for performing calculations across a set of rows related to the current row within a query result. They allow you to perform aggregate functions (e.g., sum, average, count) while still retaining individual row-level data. Window functions are distinct from regular aggregate functions in that they do not cause rows to become grouped into a single output row.

Here are some common SQL aggregation window functions:

ROW_NUMBER():

• Assigns a unique sequential integer to each row within the partition defined by the OVER() clause.

• Useful for generating row numbers or ranking rows within a partition.

SELECT

    ROW_NUMBER() OVER (ORDER BY column_name) AS row_num,

    column1, column2

FROM

    table_name;

RANK() and DENSE_RANK():

• Assigns a rank to each row based on the ordering specified in the OVER() clause. RANK() leaves gaps in rank values for tied rows, while DENSE_RANK() does not.

• Useful for ranking rows based on a particular criterion.

SELECT

    RANK() OVER (ORDER BY column_name) AS rank,

    column1, column2

FROM

    table_name;

NTILE():

• Divides the result set into a specified number of equally sized groups (tiles) and assigns a bucket number to each row.

• Useful for percentile analysis or partitioning data into equal-sized groups.

SELECT

    NTILE(n) OVER (ORDER BY column_name) AS bucket,

    column1, column2

FROM

    table_name;

LEAD() and LAG():

• Allow access to data from subsequent rows (LEAD) or preceding rows (LAG) within the same result set.

• Useful for calculating differences or trends between current and neighboring rows.

SELECT

    column_name,

    LEAD(column_name) OVER (ORDER BY ordering_column) AS next_value,

    LAG(column_name) OVER (ORDER BY ordering_column) AS prev_value

FROM

    table_name;

SUM(), AVG(), COUNT(), MIN(), MAX() with window frames:

• These are standard aggregate functions, but when used with window frames, they calculate aggregates over a specific subset of rows defined by the frame clause.

• Useful for calculating running totals, moving averages, or cumulative aggregates.

SELECT

    column_name,

    SUM(column_name) OVER (ORDER BY ordering_column ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS running_sum

FROM

    table_name;

Window functions provide powerful capabilities for analytical queries in SQL, enabling complex calculations and analyses without sacrificing row-level details. They are widely supported in modern relational database management systems like PostgreSQL, SQL Server, Oracle, and others.