Section 1.1: Aggregate Window Functions

Aggregate window functions compute values across a set of rows, returning a single result for each row. Unlike traditional aggregate functions, which consider the entire dataset, these functions focus on a defined window of rows. Common examples include SUM(), AVG(), COUNT(), MAX(), and MIN().

Section 1.2: Non-Aggregate Window Functions

Non-aggregate window functions do not summarize data. Instead, they provide individual values for each row within a window based on the values of other rows. This category includes two main types: ranking and value window functions.

Section 2.1: Understanding the ROW_NUMBER() Function

The ROW_NUMBER() function is a unique ranking function that assigns a sequential number to each row in a partition of the result set. The partitioning is defined by the OVER() clause, which specifies how to group the rows.

Important Notes for Using ROW_NUMBER() Function:

• Always include parentheses after the function name: it should be written as ROW_NUMBER(), not just ROW_NUMBER.
• The OVER() clause is essential. It determines how rows are partitioned and ordered. Omitting it will prevent the function from operating correctly, so always include it after the ROW_NUMBER() function.

Section 2.2: Syntax of the OVER() Clause

Consider a table named “all_transactions” featuring two columns (Transaction_date, Total_revenue) and four records as illustrated below. Let's explore the possibilities of the OVER() clause and observe the results.

An empty OVER() clause treats the entire result set as one single partition, allowing the function to work on all rows without specific grouping or ordering.

SELECT

transaction_date,

total_revenue,

ROW_NUMBER() OVER() AS rnk_num

FROM

all_transactions;

Executing the ROW_NUMBER() OVER() expression will yield sequential row numbers for each record.

Section 2.3: Using PARTITION BY in OVER() Clause

Including the PARTITION BY keyword followed by a column name divides the result set into distinct partitions based on the values of that column. The window function computes the rank or row number separately for each partition.

SELECT

transaction_date,

total_revenue,

ROW_NUMBER() OVER(PARTITION BY transaction_date) AS rnk_num

FROM

all_transactions;

In this case, executing the ROW_NUMBER() function will assign sequential row numbers within each unique transaction date.

Section 2.4: Using ORDER BY in OVER() Clause

By adding the ORDER BY keyword followed by a column name and specifying DESC (descending), you can order the rows within each partition in descending order. This is particularly useful for assigning ranks or row numbers based on a specific order.

SELECT

transaction_date,

total_revenue,

ROW_NUMBER() OVER(PARTITION BY transaction_date ORDER BY total_revenue DESC) AS rnk_num

FROM

all_transactions;

This command will assign sequential row numbers based on the descending order of total revenue for each transaction date.