top of page

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:


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.




0 views0 comments

Recent Posts

See All

Comments


bottom of page