Advanced SQL: Window Function

A window aggregate function is an easy way to perform an aggregate calculation without changing the query to an aggregate query. This means that you can calculate an aggregate over the entire dataset while keeping the detail in the results.

You can also perform calculations on smaller windows, or partitions, of the data. It is similar to an aggregate function however, a window function does not cause rows to become grouped into a single output row, the rows retain their separate identities.

What are the components of a window function?

The basic syntax is: function_name() OVER (PARTITION BY ... ORDER BY ...)

→ Sum(): A window function starts with either an aggregate or window function. It determines how to aggregate the rows.

→ Over(): It defines the set of rows to operate on.

→ Partition by(): It is similar to group by. Each row remains in the result set.

→ Order by(): This defines how to sort the data within the groups.

E.g. Running Total using Window Functions:

Create a running total of standard_amt_usd (in the orders table) over order time.

Explanation:

• standard_amt_usd: This column represents the amount being added for each new row.

  
  

• SUM(standard_amt_usd) OVER (ORDER BY occurred_at): This is the window function that calculates the running total. The SUM function sums up the values in the standard_amt_usd column, and the OVER clause specifies the window or set of rows to consider.

  
  

• ORDER BY occurred_at: This part orders the rows by occurred_at, ensuring that the running total is calculated in the correct order.

The result will be a table with two columns:

• The first column shows the standard_amt_usd for each order.

• The second column shows the cumulative running total up to that point in time.

This approach keeps the order and details of each row intact while also providing the cumulative total.

Key Benefits:

1. Access to Full Dataset: Unlike regular aggregate functions, window functions allow you to access individual row details while performing calculations across multiple rows.

2. Versatility: You can use them for a wide range of calculations, such as running totals, moving averages, rankings, and more.

3. No Data Loss: You retain all the original rows and columns, providing a more comprehensive view of the data.